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)

再次优化：

mysql> select FLOOR(id*rand()) from test_rand where id=(select MAX(id) from test

_rand);

+------------------+

| FLOOR(id*rand()) |

+------------------+

|          5225551 |

+------------------+

1 row in set (0.00 sec)