mysql> use test;
Database changed
mysql> select count(*) from test;
+----------+
| count(*) |
+----------+
| 2097152 |
+----------+
1 row in set (0.00 sec)
mysql> select * from test ORDER BY RAND() LIMIT 1;
+--------+------+
| a | b |
+--------+------+
| 765909 | |
+--------+------+
1 row in set (8.78 sec) --用了将近9s
引Mysql文档：
You cannot use a column with RAND() values in an ORDER BY clause, because ORDER BY would evaluate the column multiple times.也就是 order by rand()会去扫描多次，造成性能的下降。
看看下面的实现方式：
先取这个表的最大值*rand()，取出这个随机值后，在进行比对。
mysql> select * from test
-> where a >= (select floor(rand() * (select max(a) from test))) order by a limit 1;
+-------+------+
| a | b |
+-------+------+
| 99275 | |
+-------+------+
1 row in set (0.00 sec) --在0.00s左右
实际上很多实现方式稍微改一改，带来的性能是很可观的。