需求:
随机读取表的一条记录
数据准备:
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: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 15995114
Extra: Using temporary; Using filesort
1 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: index
possible_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: index
possible_keys: NULL
key: PRIMARY
key_len: 4
ref: NULL
rows: 15995114
Extra: Using index
2 rows in set (0.00 sec)
结论:using index
PS:
mysql函数 写道
floor:函数只返回整数部分,小数部分舍弃。
round:函数四舍五入,大于0.5的部分进位。