需求：

随机读取表的一条记录

数据准备：

1.表结构

mysql> desc tb_random;+-------+--------------+------+-----+---------+----------------+| Field | Type         | Null | Key | Default | Extra          |+-------+--------------+------+-----+---------+----------------+| id    | int(11)      | NO   | PRI | NULL    | auto_increment || name  | varchar(200) | YES  |     | NULL    |                |+-------+--------------+------+-----+---------+----------------+

数据量：百万

实现：

1.基于rand()

SELECT * FROM tb_random   ORDER BY rand() LIMIT 1;

2.基于随机id取值，借助rand()和FLOOR()

SELECT * FROM tb_random WHERE id >= (SELECT FLOOR( MAX(id) * RAND()) FROM tb_random ) ORDER BY id LIMIT 1;

性能可以通过explain来看执行计划：

explain SELECT * FROM tb_random   ORDER BY rand() LIMIT 1 \G;

结果：

*************************** 1. row ***************************           id: 1  select_type: SIMPLE        table: tb_random         type: ALLpossible_keys: NULL          key: NULL      key_len: NULL          ref: NULL         rows: 15995114        Extra: Using temporary; Using filesort1 row in set (0.00 sec)

结论：没有采用索引

第二种方案：

explain SELECT * FROM tb_random WHERE id >= (SELECT FLOOR( MAX(id) * RAND()) FROM tb_random ) ORDER BY id LIMIT 1 \G;

结果：

*************************** 1. row ***************************           id: 1  select_type: PRIMARY        table: tb_random         type: indexpossible_keys: NULL          key: PRIMARY      key_len: 4          ref: NULL         rows: 1        Extra: Using where*************************** 2. row ***************************           id: 2  select_type: UNCACHEABLE SUBQUERY        table: tb_random         type: indexpossible_keys: NULL          key: PRIMARY      key_len: 4          ref: NULL         rows: 15995114        Extra: Using index2 rows in set (0.00 sec)

结论：using index

PS：