通常情况下我们使用如下的语句随机返回一条记录数
SELECT * FROM tb_dic_class ORDER BY RAND() LIMIT 1,测试发现这个方法并不是最优化的，下面列举了一个效率更好的例子:
1.表记录数
mysql> SELECT count(1) FROM tb_dic_class;
+----------+
| count(1) |
+----------+
| 1000000 |
+----------+
1 row in set (0.37 sec)
2.常用的返回随机记录的方法
mysql>
SELECT * FROM tb_dic_class ORDER BY RAND() LIMIT 1 \G;
*************************** 1. row ***************************
class_id: 839335
class_name: 839335班
createtime: -08-18 14:27:23
modifytime: -08-18 14:27:23
1 row in set (8.69 sec)
ERROR:
No query specified
3.效率更好的方法
mysql> SELECT * FROM tb_dic_class AS t1
-> JOIN (SELECT ROUND(RAND() *
-> (SELECT MAX(class_id) FROM tb_dic_class)) AS id) AS t2 WHERE t1.class_id >= t2.id ORDER BY t1.class_id ASC LIMIT 1 \G;
*************************** 1. row ***************************
class_id: 400243
class_name: 400243班
createtime: -08-18 14:25:23
modifytime: -08-18 14:25:23
id: 400243
1 row in set (1.18 sec)
ERROR:
No query specified
4.下面这个查询效果更佳
mysql> SELECT *
-> FROM tb_dic_class AS t1 JOIN (SELECT ROUND(RAND() * ((SELECT MAX(class_id) FROM tb_dic_class)-(SELECT MIN(class_id) FROM tb_dic_class))+(SELECT MIN(class_id) FROM tb_dic_class)) AS id) AS t2
-> WHERE t1.class_id >= t2.id
-> ORDER BY t1.class_id LIMIT 1;
+----------+------------+---------------------+---------------------+--------+
| class_id | class_name | createtime | modifytime | id |
+----------+------------+---------------------+---------------------+--------+
| 448629 | 448629班 | -08-18 14:25:36 | -08-18 14:25:36 | 448629 |
+----------+------------+---------------------+---------------------+--------+
1 row in set (0.20 sec)
--The End --
