MySQL优化之对RAND()的优化方法
众所周知，在MySQL中，如果直接 ORDER BY RAND() 的话，效率非常差，因为会多次执行。事实上，如果等值查询也是用 RAND() 的话也如此，我们先来看看下面这几个SQL的不同执行计划和执行耗时。
首先，看下建表DDL，这是一个没有显式自增主键的InnoDB表：
[yejr@imysql]> show create table t_innodb_random\G *************************** 1. row *************************** Table: t_innodb_random Create Table: CREATE TABLE `t_innodb_random` ( `id` int(10) unsigned NOT NULL, `user` varchar(64) NOT NULL DEFAULT '', KEY `idx_id` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1
往这个表里灌入一些测试数据，至少10万以上， id 字段也是乱序的。
[yejr@imysql]> select count(*) from t_innodb_random\G *************************** 1. row *************************** count(*): 393216
1、常量等值检索：
[yejr@imysql]> explain select id from t_innodb_random where id = 13412\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t_innodb_random type: ref possible_keys: idx_id key: idx_id key_len: 4 ref: const rows: 1 Extra: Using index[yejr@imysql]> select id from t_innodb_random where id = 13412; 1 row in set (0.00 sec)
可以看到执行计划很不错，是常量等值查询，速度非常快。
2、使用RAND()函数乘以常量，求得随机数后检索：
[yejr@imysql]> explain select id from t_innodb_random where id = round(rand()*13241324)\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t_innodb_random type: index possible_keys: NULL key: idx_id key_len: 4 ref: NULL rows: 393345 Extra: Using where; Using index[yejr@imysql]> select id from t_innodb_random where id = round(rand()*13241324)\G Empty set (0.26 sec)
可以看到执行计划很糟糕，虽然是只扫描索引，但是做了全索引扫描，效率非常差。因为WHERE条件中包含了RAND()，使得MySQL把它当做变量来处理，无法用常量等值的方式查询，效率很低。
我们把常量改成取t_innodb_random表的最大id值，再乘以RAND()求得随机数后检索看看什么情况：
[yejr@imysql]> explain select id from t_innodb_random where id = round(rand()*(select max(id) from t_innodb_random))\G *************************** 1. row *************************** id: 1 select_type: PRIMARY table: t_innodb_random type: index possible_keys: NULL key: idx_id key_len: 4 ref: NULL rows: 393345 Extra: Using where; Using index *************************** 2. row *************************** id: 2 select_type: SUBQUERY table: NULL type: NULL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: NULL Extra: Select tables optimized away[yejr@imysql]> select id from t_innodb_random where id = round(rand()*(select max(id) from t_innodb_random))\G Empty set (0.27 sec)
可以看到，执行计划依然是全索引扫描，执行耗时也基本相当。
3、改造成普通子查询模式 ，这里有两次子查询
[yejr@imysql]> explain select id from t_innodb_random where id = (select round(rand()*(select max(id) from t_innodb_random)) as nid)\G *************************** 1. row *************************** id: 1 select_type: PRIMARY table: t_innodb_random type: index possible_keys: NULL key: idx_id key_len: 4 ref: NULL rows: 393345 Extra: Using where; Using index *************************** 2. row *************************** id: 3 select_type: SUBQUERY table: NULL type: NULL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: NULL Extra: Select tables optimized away[yejr@imysql]> select id from t_innodb_random where id = (select round(rand()*(select max(id) from t_innodb_random)) as nid)\G Empty set (0.27 sec)
可以看到，执行计划也不好，执行耗时较慢。
4、改造成JOIN关联查询，不过最大值还是用常量表示
[yejr@imysql]> explain select id from t_innodb_random t1 join (select round(rand()*13241324) as id2) as t2 where t1.id = t2.id2\G *************************** 1. row *************************** id: 1 select_type: PRIMARY table: <derived2> type: system possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 1 Extra: *************************** 2. row *************************** id: 1 select_type: PRIMARY table: t1 type: ref possible_keys: idx_id key: idx_id key_len: 4 ref: const rows: 1 Extra: Using where; Using index *************************** 3. row *************************** id: 2 select_type: DERIVED table: NULL type: NULL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: NULL Extra: No tables used[yejr@imysql]> select id from t_innodb_random t1 join (select round(rand()*13241324) as id2) as t2 where t1.id = t2.id2\G Empty set (0.00 sec)
这时候执行计划就非常完美了，和最开始的常量等值查询是一样的了，执行耗时也非常之快。
这种方法虽然很好，但是有可能查询不到记录，改造范围查找，但结果LIMIT 1就可以了：
[yejr@imysql]> explain select id from t_innodb_random where id > (select round(rand()*(select max(id) from t_innodb_random)) as nid) limit 1\G *************************** 1. row *************************** id: 1 select_type: PRIMARY table: t_innodb_random type: index possible_keys: NULL key: idx_id key_len: 4 ref: NULL rows: 393345 Extra: Using where; Using index *************************** 2. row *************************** id: 3 select_type: SUBQUERY table: NULL type: NULL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: NULL Extra: Select tables optimized away[yejr@imysql]> select id from t_innodb_random where id > (select round(rand()*(select max(id) from t_innodb_random)) as nid) limit 1\G *************************** 1. row *************************** id: 1301 1 row in set (0.00 sec)
可以看到，虽然执行计划也是全索引扫描，但是因为有了LIMIT 1，只需要找到一条记录，即可终止扫描，所以效率还是很快的。
小结：
从数据库中随机取一条记录时，可以把RAND()生成随机数放在JOIN子查询中以提高效率。
5、再来看看用ORDRR BY RAND()方式一次取得多个随机值的方式：
[yejr@imysql]> explain select id from t_innodb_random order by rand() limit 1000\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t_innodb_random type: index possible_keys: NULL key: idx_id key_len: 4 ref: NULL rows: 393345 Extra: Using index; Using temporary; Using filesort[yejr@imysql]> select id from t_innodb_random order by rand() limit 1000; 1000 rows in set (0.41 sec)
全索引扫描，生成排序临时表，太差太慢了。
6、把随机数放在子查询里看看：
[yejr@imysql]> explain select id from t_innodb_random where id > (select rand() * (select max(id) from t_innodb_random) as nid) limit 1000\G *************************** 1. row *************************** id: 1 select_type: PRIMARY table: t_innodb_random type: index possible_keys: NULL key: idx_id key_len: 4 ref: NULL rows: 393345 Extra: Using where; Using index *************************** 2. row *************************** id: 3 select_type: SUBQUERY table: NULL type: NULL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: NULL Extra: Select tables optimized away[yejr@imysql]> select id from t_innodb_random where id > (select rand() * (select max(id) from t_innodb_random) as nid) limit 1000\G 1000 rows in set (0.04 sec)
嗯，提速了不少，这个看起来还不赖：）
7、仿照上面的方法，改成JOIN和随机数子查询关联
[yejr@imysql]> explain select id from t_innodb_random t1 join (select rand() * (select max(id) from t_innodb_random) as nid) t2 on t1.id > t2.nid limit 1000\G *************************** 1. row *************************** id: 1 select_type: PRIMARY table: <derived2> type: system possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 1 Extra: *************************** 2. row *************************** id: 1 select_type: PRIMARY table: t1 type: range possible_keys: idx_id key: idx_id key_len: 4 ref: NULL rows: 196672 Extra: Using where; Using index *************************** 3. row *************************** id: 2 select_type: DERIVED table: NULL type: NULL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: NULL Extra: No tables used *************************** 4. row *************************** id: 3 select_type: SUBQUERY table: NULL type: NULL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: NULL Extra: Select tables optimized away[yejr@imysql]> select id from t_innodb_random t1 join (select rand() * (select max(id) from t_innodb_random) as nid) t2 on t1.id > t2.nid limit 1000\G 1000 rows in set (0.00 sec)
可以看到，全索引检索，发现符合记录的条件后，直接取得1000行，这个方法是最快的。
综上，想从MySQL数据库中随机取一条或者N条记录时，最好把RAND()生成随机数放在JOIN子查询中以提高效率。
上面说了那么多的废话，最后简单说下，就是把下面这个SQL：
SELECT id FROM table ORDER BY RAND() LIMIT n;
改造成下面这个：
SELECT id FROM table t1 JOIN (SELECT RAND() * (SELECT MAX(id) FROM table) AS nid) t2 ON t1.id > t2.nid LIMIT n;
就可以享受在SQL中直接取得随机数了，不用再在程序中构造一串随机数去检索了。
-
本文向大家介绍Mysql优化之Zabbix分区优化，包括了Mysql优化之Zabbix分区优化的使用技巧和注意事项，需要的朋友参考一下 使用zabbix最大的瓶颈在于数据库，维护好zabbix的数据存储，告警，就能很好地应用zabbix去构建监控系统。目前zabbix的数据主要存储在history和trends的2个表中，随着时间的推移，这两个表变得非常大，性能会非常差，影响监控的使用。对MySQ
-
本文向大家介绍mysql性能优化之索引优化，包括了mysql性能优化之索引优化的使用技巧和注意事项，需要的朋友参考一下 作为免费又高效的数据库，mysql基本是首选。良好的安全连接，自带查询解析、sql语句优化，使用读写锁（细化到行）、事物隔离和多版本并发控制提高并发，完备的事务日志记录，强大的存储引擎提供高效查询（表记录可达百万级），如果是InnoDB，还可在崩溃后进行完整的恢复，优点非常多
-
下面说的优化基于 MySQL 5.6，理论上 5.5 之后的都算适用，具体还是要看官网 服务状态查询 查看当前数据库的状态，常用的有： 查看系统状态：SHOW STATUS; 查看刚刚执行 SQL 是否有警告信息：SHOW WARNINGS; 查看刚刚执行 SQL 是否有错误信息：SHOW ERRORS; 查看已经连接的所有线程状况：SHOW PROCESSLIST; 查看当前连接数量：SHOW
-
在整体的系统运行过程中，数据库服务器 MySQL 的压力是最大的，不仅占用很多的内存和 cpu 资源，而且占用着大部分的磁盘 io 资源，连 PHP 的官方都在声称，说 PHP 脚本 80% 的时间都在等待 MySQL 查询返回的结果。由此可见，提高系统的负载能力，降低 MySQL 的资源消耗迫在眉睫。 常见优化方法： 1、页面缓存 1、页面缓存功能是降低MySQL的资源消耗的（PHPCMS V9
-
问题内容： 我想优化查询，以便进行调查。 我的大多数慢查询都包含。我找不到解决此问题的真正解决方案。在MySQLPerformanceBlog上有一个可能的解决方案，但我认为这还不够。在优化不佳（或频繁更新，用户管理）的表上，该表不起作用，或者我需要运行两个或多个查询才能选择生成的随机行。 这个问题有解决方案吗？ 一个虚拟的例子： 问题答案： 尝试这个： （由于是即时的），此方法特别有效，但即使这
-
本文向大家介绍Mysql优化技巧之Limit查询的优化分析，包括了Mysql优化技巧之Limit查询的优化分析的使用技巧和注意事项，需要的朋友参考一下 前言 在实际业务中对于分页来说是一个比较常见的业务需求。那么就会使用到limit查询，当我们在使用Limit查询的时候，在数据比较小、或者只查询前面一部分数据的时候效率是很高的。但是当数据量大的时候，或者查询offset数量比较大的时候，如：lim
-
本文向大家介绍Mysql慢查询优化方法及优化原则，包括了Mysql慢查询优化方法及优化原则的使用技巧和注意事项，需要的朋友参考一下 1、日期大小的比较，传到xml中的日期格式要符合'yyyy-MM-dd'，这样才能走索引，如：'yyyy'改为'yyyy-MM-dd'，'yyyy-MM'改为'yyyy-MM-dd'【这样MYSQL会转换为日期类型】 2、条件语句中无论是等于、还是大于小于，WHERE
-
本文向大家介绍优化mysql之key_buffer_size设置，包括了优化mysql之key_buffer_size设置的使用技巧和注意事项，需要的朋友参考一下 key_buffer_size key_buffer_size指定索引缓冲区的大小，它决定索引处理的速度，尤其是索引读的速度。通过检查状态值Key_read_requests和Key_reads，可以知道key_buffer_size设