MySQL中，使用RAND()查询数据量比较大的优化

时间：2014-4-1 作者：smarteng 分类： 数据库相关

随机找出2万条纪录，

方案一：

最直接的语句是：

SELECT *

FROM moni_role

ORDER BY RAND()

LIMIT 20000;

mysql> EXPLAIN SELECT FROM moni_role ORDER BY RAND() LIMIT 20000;

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

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

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

| 1 | SIMPLE | moni_role | ALL | NULL | NULL | NULL | NULL | 1344687 | Using temporary; Using filesort |

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

mysql> SELECT FROM moni_role ORDER BY RAND() LIMIT 20000;

...此处省去结果

20000 rows in set (57.16 sec)

这条语句花了将近一分钟，尝试优化后的语句。

方案二：

借助子查询，在子查询里覆盖索引

SELECT

FROM moni_role

WHERE id IN (

    SELECT id FROM moni_role

    ORDER BY RAND()

)

LIMIT 20000;

mysql> EXPLAIN SELECT FROM moni_role WHERE id IN ( SELECT id FROM moni_role ORDER BY RAND() ) LIMIT 20000;

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

| id | select_type        | table     | type            | possible_keys | key     | key_len | ref | rows    | Extra       |

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

| 1 | PRIMARY            | moni_role | ALL             | NULL          | NULL    | NULL    | NULL | 1344687 | Using where |

| 2 | DEPENDENT SUBQUERY | moni_role | unique_subquery | PRIMARY       | PRIMARY | 4       | func |       1 | Using index |

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

mysql> SELECT * FROM moni_role WHERE id IN ( SELECT id FROM moni_role ORDER BY RAND() ) LIMIT 20000;

...此处省去结果

20000 rows in set (0.33 sec)

可以看到优化后的语句只花了330毫秒，性能提高了200倍左右。

上一篇:nginx、php-fpm默认配置与性能–TCP socket还是unix domain socket 下一篇:让你提升命令行效率的 Bash 快捷键