mysql随机提取记录数的方法

rfsdw

通常情况下我们使用如下的语句随机返回一条记录数
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: 2014-08-18 14:27:23
modifytime: 2014-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: 2014-08-18 14:25:23
modifytime: 2014-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班   | 2014-08-18 14:25:36 | 2014-08-18 14:25:36 | 448629 |
+----------+------------+---------------------+---------------------+--------+
1 row in set (0.20 sec)

--The End --