mysql 随机取一条记录优化
表结构：CREATE TABLE test_rand(id int(11) AUTO_INCREMENT PRIMARY KEY,c2 varchar(30) DEFAULT NULL, c3 date DEFAULT NULL);
?
插入800W条记录；
?
随机取一条记录：(id已建立索引)
SELECT * FROM test_rand order by rand() limit 1;
mysql> select * from test_rand order by rand() limit 1;
+---------+--------------------+------------+
| id ? ? ?| c2 ? ? ? ? ? ? ? ? | c3 ? ? ? ? |
+---------+--------------------+------------+
| 1399866 | testing partitions | 1996-10-21 |
+---------+--------------------+------------+
1 row in set (19.13 sec)
?
第一次优化：
SELECT FLOOR(MAX(id)*RAND()) FROM test_rand
+-----------------------+
| FLOOR(MAX(id)*RAND()) |
+-----------------------+
| ? ? ? ? ? ? ? ?446373 |
+-----------------------+
1 row in set (2.20 sec)
