如何从Mysql快速查找一条数据

前言

可能许多小伙伴在面试中会遇到这么样的一道面试题：如何以最高效率随机在 Mysql 数据表中查找一条数据 实际上，这个题包含了两个点 在 Mysql 数据表随机查找一条数据 保证效率最高

实战

我们平时常用的英语App，大部分都有 随机显示一个单词或者多个单词的功能，为了便于理解，我设计了以下代码，并往表里加了 30000 行数据。

CREATE TABLE `words` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `word` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

delimiter ;;
create procedure idata()
begin
  declare i int;
  set i=0;
  while i<30000 do
    insert into words(word) values(concat(char(97+(i div 1000)), char(97+(i % 1000 div 100)), char(97+(i % 100 div 10)), char(97+(i % 10))));
    set i=i+1;
  end while;
end;;
delimiter ;

call idata();

方法一

随机查找，你可能会想到最原始最直观的方法，使用 Mysql 的 rand() 方法

select * from words order by rand() limit 1;

使用 explain 查看执行计划，可以发现使用 rand() ，Extra字段显示 Using temporary，表示的是需要使用临时表；Using filesort，表示的是需要执行排序操作。因此这个 Extra 的意思就是，需要临时表，并且需要在临时表上排序。

我们的 Mysql 使用 InnoDB存储引擎，对于 InnoDB 的数据表来说，执行全字段排序会减少磁盘访问，因此会被优先选择。但是，对于内存表，回表过程只是简单地根据数据行的位置，直接访问内存得到数据，根本不会导致多访问磁盘。优化器没有了这一层顾虑，那么它会优先考虑的，就是用于排序的行越少越好了，所以，MySQL这时就会选择 rowid 排序，排序临时表。

也就是说，order by rand() 使用了内存临时表，内存临时表排序的时候使用了 rowid 排序方法（Using temporary 和 Using filesort），查询的执行代价往往是比较大的。所以，在设计的时候，我们需要尽量避开这种写法。

方法二

思路大概如下：

取得这个表的主键 id 的最大值 M 和最小值 N ; 首先查询出数据表的所有记录数

select count(*) as num_rows from words;

用随机函数生成一个最大值到最小值之间的数 X = (M-N)*rand() + N; 取不小于X的第一个 ID 的行。

然后在 [0, num_rows] 范围中随机

select * from words limit [0, num_rows随机一个数X], 1;

方法三

那有没有可能，一句 sql 就完成 方法二 的查询，那当然是有的，SQL 如下：

SELECT * 
 FROM  `words` AS t1 JOIN (SELECT ROUND(RAND() * ((SELECT MAX(id) FROM  `words`)-(SELECT MIN(id) FROM `words`))+(SELECT MIN(id) FROM `words`))  AS id) AS t2 
WHERE t1.id >= t2.id 
ORDER BY t1.id LIMIT 1;

当然，上述的 sql 有一个问题，如果要快速随机查询多条数据，修改上述的语句 limit n，n 代表 n 条数据如修改成 limit 5，那么就会产生连续的 5 条记录。解决办法只能是每次查询一条，查询 5。次。

当然，上述的问题可以使用以下方法解决。

SELECT * FROM `words` 
WHERE id >= (SELECT floor( RAND() * ((SELECT MAX(id) FROM  `words`)-(SELECT MIN(id) FROM `words`)) + (SELECT MIN(id) FROM  `words`))) 
ORDER BY id LIMIT 5;

参考博客：

https://jishuin.proginn.com/p/763bfbd308d8