文章目录
MRR 优化效果测试
Multi-Range Read Optimization (MRR) 多范围读优化。
不开启,Mysql 以二级索引的顺序通过主键ID回表访问聚集索引获取数据,该情况下,对于主键的访问具有随机性,检索数据较多时产生大量随机IO,性能较差。
开启后,将通过二级索引获取到的主键ID在内存中排序,以排序后的主键ID顺序回表访问聚集索引,极大的降低了随机IO的次数。
-
该优化用于对机械硬盘随机IO的优化,因固态硬盘随机IO效率很高,所以
固态场景下效果不明显
。 -
该优化对于缓存读基本无效,主要用于硬盘读场景
0.缓存清理
my.cnf 设置如下参数,并重启数据库
innodb_buffer_pool_dump_at_shutdown=OFF
innodb_buffer_pool_load_at_startup=OFF
1. 案例
drop table t1;
create table t1(id int auto_increment primary key,name varchar(20),uid int,addr varchar(20));
drop procedure prc_t1;
delimiter //
create procedure prc_t1()
begin
declare i int default 0;
declare j int default 0;
while i<400 do
while j < 10000 do
INSERT into t1(name,uid,addr) VALUES('cym',floor(rand()*1000),'testtesttesttesttest');
set j=j+1;
end while;
commit;
set j=0;
set i=i+1;
end while;
end
//
delimiter ;
set sql_log_bin=0;
set autocommit=0;
call prc_t1;
alter table t1 add key(uid);
2. 检查
[pro]root@cym 12:43:11>desc t1;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | | NULL | |
| uid | int | YES | MUL | NULL | |
| addr | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
[pro]root@cym 12:43:56>show index from t1;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| t1 | 0 | PRIMARY | 1 | id | A | 3662208 | NULL | NULL | | BTREE | | | YES | NULL |
| t1 | 1 | uid | 1 | uid | A | 1001 | NULL | NULL | YES | BTREE | | | YES | NULL |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
[pro]root@cym 10:45:49>select count(1) from t1 where uid between 1 and 50;
+----------+
| count(1) |
+----------+
| 199475 |
+----------+
3. 测试
清空事件信息并重启数据库
3.1 optimizer_switch=‘mrr=off’
-- 重启数据库
systemctl restart mysqld3306
-- 检查是否有缓存
select count(1) from information_schema.innodb_buffer_page_lru WHERE table_name like '`cym`.`t1`';
+----------+
| count(1) |
+----------+
| 0 |
+----------+
-- 收集统计信息
analyze table t1;
-- 调整优化规则
set @@optimizer_switch='mrr=off';
-- 查看执行计划
desc select * from t1 where uid between 1 and 50;
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| 1 | SIMPLE | t1 | NULL | ALL | uid | NULL | NULL | NULL | 3986466 | 10.05 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
-- 执行查询
select * from t1 where uid between 1 and 50;
199475 rows in set (1.22 sec)
-- 再次查看执行计划
+----+-------------+-------+------------+-------+---------------+------+---------+------+--------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+------+---------+------+--------+----------+-----------------------+
| 1 | SIMPLE | t1 | NULL | range | uid | uid | 5 | NULL | 400514 | 100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+------+---------+------+--------+----------+-----------------------+
3.2 optimizer_switch=‘mrr=on,mrr_cost_based=on’
-- 重启数据库
systemctl restart mysqld3306
-- 检查是否有缓存
select count(1) from information_schema.innodb_buffer_page_lru WHERE table_name like '`cym`.`t1`';
+----------+
| count(1) |
+----------+
| 0 |
+----------+
-- 收集统计信息
analyze table t1;
-- 调整优化规则
set @@optimizer_switch='mrr=off';
-- 查看执行计划
desc select * from t1 where uid between 1 and 50;
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| 1 | SIMPLE | t1 | NULL | ALL | uid | NULL | NULL | NULL | 3986466 | 10.05 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
-- 执行查询
select * from t1 where uid between 1 and 50;
199475 rows in set (1.23 sec)
-- 再次查看执行计划
desc select * from t1 where uid between 1 and 50;
+----+-------------+-------+------------+-------+---------------+------+---------+------+--------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+------+---------+------+--------+----------+-----------------------+
| 1 | SIMPLE | t1 | NULL | range | uid | uid | 5 | NULL | 400514 | 100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+------+---------+------+--------+----------+-----------------------+
3.3 optimizer_switch=‘mrr=on,mrr_cost_based=off’
-- 重启数据库
systemctl restart mysqld3306
-- 检查是否有缓存
select count(1) from information_schema.innodb_buffer_page_lru WHERE table_name like '`cym`.`t1`';
+----------+
| count(1) |
+----------+
| 0 |
+----------+
-- 收集统计信息
analyze table t1;
-- 调整优化规则
set @@optimizer_switch='mrr=off';
-- 查看执行计划
desc select * from t1 where uid between 1 and 50;
+----+-------------+-------+------------+-------+---------------+------+---------+------+--------+----------+----------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+------+---------+------+--------+----------+----------------------------------+
| 1 | SIMPLE | t1 | NULL | range | uid | uid | 5 | NULL | 400514 | 100.00 | Using index condition; Using MRR |
+----+-------------+-------+------------+-------+---------------+------+---------+------+--------+----------+----------------------------------+
-- 执行查询
select * from t1 where uid between 1 and 50;
199475 rows in set (0.87 sec)
-- 再次查看执行计划
desc select * from t1 where uid between 1 and 50;
+----+-------------+-------+------------+-------+---------------+------+---------+------+--------+----------+----------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+------+---------+------+--------+----------+----------------------------------+
| 1 | SIMPLE | t1 | NULL | range | uid | uid | 5 | NULL | 400514 | 100.00 | Using index condition; Using MRR |
+----+-------------+-------+------------+-------+---------------+------+---------+------+--------+----------+----------------------------------+
4. 查看执行事件ID
performance_schema 记录mysql启动后的信息,数据库重启即消失。
启用performance_schema的信息收集,参考<<Performance Scheme 分析SQL慢在哪里>>
[pro]root@cym 12:51:18>SELECT EVENT_ID, TRUNCATE(TIMER_WAIT/1000000000000,6) as Duration, SQL_TEXT FROM performance_schema.events_statements_history_long WHERE SQL_TEXT like '%uid between 1 and 50%';
+----------+----------+--------------------------------------------------+
| EVENT_ID | Duration | SQL_TEXT |
+----------+----------+--------------------------------------------------+
| 55 | 0.0007 | desc select * from t1 where uid between 1 and 50 |
| 74 | 0.8734 | select * from t1 where uid between 1 and 50 |
+----------+----------+--------------------------------------------------+
5. 查看执行分析
SELECT NESTING_EVENT_ID,event_name AS Stage, TRUNCATE(TIMER_WAIT/1000000000000,6) AS Duration FROM performance_schema.events_stages_history_long WHERE NESTING_EVENT_ID=74;
+------------------+------------------------------------------------+----------+
| NESTING_EVENT_ID | Stage | Duration |
+------------------+------------------------------------------------+----------+
| 74 | stage/sql/starting | 0.0000 |
| 74 | stage/sql/Executing hook on transaction begin. | 0.0000 |
| 74 | stage/sql/starting | 0.0000 |
| 74 | stage/sql/checking permissions | 0.0000 |
| 74 | stage/sql/Opening tables | 0.0000 |
| 74 | stage/sql/init | 0.0000 |
| 74 | stage/sql/System lock | 0.0000 |
| 74 | stage/sql/optimizing | 0.0000 |
| 74 | stage/sql/statistics | 0.0000 |
| 74 | stage/sql/preparing | 0.0000 |
| 74 | stage/sql/executing | 0.8731 |
| 74 | stage/sql/end | 0.0000 |
| 74 | stage/sql/query end | 0.0000 |
| 74 | stage/sql/waiting for handler commit | 0.0000 |
| 74 | stage/sql/closing tables | 0.0000 |
| 74 | stage/sql/freeing items | 0.0000 |
| 74 | stage/sql/cleaning up | 0.0000 |
+------------------+------------------------------------------------+----------+
6. 总结
三种优化参数配置,对应的执行效率如下
optimizer_switch='mrr=off' 199475 rows in set (1.22 sec) Extra:Using where
optimizer_switch='mrr=on,mrr_cost_based=on' 199475 rows in set (1.23 sec) Extra:Using where
optimizer_switch='mrr=on,mrr_cost_based=off' 199475 rows in set (0.87 sec) Extra:Using index condition; Using MRR
第三种效率最高,而且执行计划准确,故应配置 optimizer_switch=‘mrr=on,mrr_cost_based=off’