概念引入
- MRR(Multi-Range Read)
处理思路:空间换时间,化随机读为顺序读,优化通过二级索引检索回表的性能问题
MySQL中,索引是B+ tree,在叶子节点中,数据是逻辑有序的,如主键索引中,是按照主键列有序排列,而二级索引中,是按照索引列进行有序排列,而二级索引的叶子节点存储的是索引列和主键值,索引列是有序的,此时主键值却不一定是有序的,往往是无序的,此时通过索引列定位到主键值,然后回表往往是离散的读取数据。MRR中就是先在内存中分配read_rnd_buffer空间,先把二级索引的索引列和主键值放入到read_rnd_buffer中(放不下就会分多次放入,清空一批之后放入下一批),然后按照主键值排序,这个时候再去回表,IO相对会有序。
注意:MRR需要足够的数据支撑才行,如果只是少量数据回表,则完全没必要使用MRR
- Simple Nested-loop Join:只是理论上的一种关联方式,关联的表均没有索引,类似于笛卡儿积,MySQL不会采取这个关联方式
- Block Nested-loop Join:将驱动表存放到join_buffer中,然后拿着整个join_buffer的数据去与被驱动表进行比较。
BNLJ是对SNLJ的一种优化,BNLJ比较的过程是在内存中执行,匹配速度会相对快点,另外个人理解,A和B两人口袋里各有一些扑克牌(N个和M个),两个人扑克牌做匹对。SNLJ就是A从口袋里一个一个地取出扑克到B的口袋中去匹对,而BNLJ可以理解为取出一把扑克去B的口袋中匹配,可以充分利用CPU的使用情况。但是总体的匹对量是一致均为N*M。
- Index Nested-loop Join:驱动表取出一条数据,然后通过被驱动表的索引进行数据匹配,可以理解为是在SNLJ的基础上做了一层索引的优化。
- Batched key access:将驱动表放入join_buffer内,并按照索引列进行排序(调用了MRR的接口),参考MRR的思路,随机IO转顺序IO,MRR 能够提升性能的核心在于,这条查询语句在索引 a 上做的是一个范围查询(也就是说,这是一个多值查询),可以得到足够多的主键 id。在驱动表上做多条数据的cache,是为了产生较多的匹配数据,从而在被驱动表中,可以使用MRR,随机读可以转为顺序读,数据足够多的情况下,MRR才有优势。
BKA是对NLJ的一个优化,通过join_buffer和MRR进行优化
这四者的关系大致如下:
如何开启MRR和BKA
example:MRR
###创建表T
create table t1(id int primary key, a int,create_time timestamp, index(a));
drop procedure idata;
delimiter ;;
create procedure idata()
begin
declare i int;
set i=1;
while(i<=10000)do
insert into t1 values(i, 100001-i, now());
set i=i+1;
end while;
end;;
delimiter ;
call idata();
mysql> explain select * from t1 where a > 100 and a < 500;
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | t1 | NULL | range | a | a | 5 | NULL | 1 | 100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
mysql> set optimizer_switch="mrr_cost_based=off";
Query OK, 0 rows affected (0.00 sec)
mysql> explain select * from t1 where a > 100 and a < 500;
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+----------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+----------------------------------+
| 1 | SIMPLE | t1 | NULL | range | a | a | 5 | NULL | 1 | 100.00 | Using index condition; Using MRR |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+----------------------------------+
1 row in set, 1 warning (0.00 sec)
example:BKA
set optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';