MySQL Index--BAK和MRR演示
搭建测试环境演示BKA和MRR特性
建表语句：
## 创建测试表tb1和tb2 CREATE TABLE `tb1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `c1` int(11) DEFAULT NULL, `c2` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_c1` (`c1`) ) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8； CREATE TABLE `tb2` ( `id` int(11) NOT NULL AUTO_INCREMENT, `c1` int(11) DEFAULT NULL, `c2` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_c2` (`c2`) ) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8; ## 向测试表tb1和tb2插入30万数据 ## 表tb1001的id值自增 INSERT INTO tb1(c1,c2) SELECT id,id FROM tb1001; INSERT INTO tb1(c1,c2) SELECT id,id FROM tb1001;
测试SQL:
SELECT * FROM tb1 INNER JOIN tb2 ON tb1.c1 = tb2.C2 WHERE tb1.c1>100 AND tb1.c1<200
对应执行计划：
*************************** 1. row *************************** id: 1 select_type: SIMPLE table: tb1 partitions: NULL type: range possible_keys: IDX_C1 key: IDX_C1 key_len: 5 ref: NULL rows: 99 filtered: 100.00 Extra: Using index condition; Using MRR *************************** 2. row *************************** id: 1 select_type: SIMPLE table: tb2 partitions: NULL type: ref possible_keys: IDX_C2 key: IDX_C2 key_len: 5 ref: demodb.tb1.C1 rows: 1 filtered: 100.00 Extra: Using join buffer (Batched Key Access) 2 rows in set, 1 warning (0.00 sec)
对应执行计划(JSON)：
{ "query_block": { "select_id": 1, "cost_info": { "query_cost": "258.44" }, "nested_loop": [ { "table": { "table_name": "tb1", "access_type": "range", "possible_keys": [ "IDX_C1" ], "key": "IDX_C1", "used_key_parts": [ "C1" ], "key_length": "5", "rows_examined_per_scan": 99, "rows_produced_per_join": 99, "filtered": "100.00", "index_condition": "(((`demodb`.`tb1`.`C1` > 100) and (`demodb`.`tb1`.`C1` < 200)) and (`demodb`.`tb1`.`C1` is not null))", "using_MRR": true, "cost_info": { "read_cost": "119.81", "eval_cost": "19.80", "prefix_cost": "139.61", "data_read_per_join": "1K" }, "used_columns": [ "ID", "C1", "C2" ] } }, { "table": { "table_name": "tb2", "access_type": "ref", "possible_keys": [ "IDX_C2" ], "key": "IDX_C2", "used_key_parts": [ "C2" ], "key_length": "5", "ref": [ "demodb.tb1.C1" ], "rows_examined_per_scan": 1, "rows_produced_per_join": 99, "filtered": "100.00", "using_join_buffer": "Batched Key Access", "cost_info": { "read_cost": "99.02", "eval_cost": "19.80", "prefix_cost": "258.44", "data_read_per_join": "1K" }, "used_columns": [ "ID", "C1", "C2" ] } } ] } }
执行计划伪代码(个人理解)：
c1_condition=((`demodb`.`tb1`.`C1` > 100) and (`demodb`.`tb1`.`C1` < 200) and (`demodb`.`tb1`.`C1` is not null)) tb1_mrr_buffer=new buffer(@@read_rnd_buffer_size) tb1_search_result=[] for each tb1_index_row(c1,id) in(range scan tb1.idx_c1 with c1_condition): if tb1_mrr_buffer is not full: tb1_mrr_buffer.append(tb1_index_row(c1,id)) else： ## using mrr tb1_mrr_buffer.sort_by(id) for tb1_index_row(id,c1) in tb1_mrr_buffer: tb1_data_row(id,c1,c2)=(search index tb1.priamry_key with id=tb1_index_row.id) tb1_search_result.append(tb1_data_row) tb1_mrr_buffer.sort_by(id) for tb1_index_row(id,c1) in tb1_mrr_buffer: tb1_data_row(id,c1,c2)=(search index tb1.priamry_key with id=tb1_index_row.id) tb1_search_result.append(tb1_data_row) tb1_mrr_buffer.dispose() tb2_bka_buffer=new buffer(@@join_buffer_size) tb2_join_result=[] for each tb1_data_row(id,c1,c2) in tb1_search_result: for tb2_index_row(c2,id) in (range scan tb2.idx_c2 where c2=tb1_data_row.c1) if tb2_bka_buffer is not full: tb2_bka_buffer.append(tb1_data_row(id,c1,c2),tb2_index_row(c2,id)) else： ## using bka tb2_bka_buffer.sort_by(tb2_index_row.id) tb2_data_row(id,c1,c2)=(search index tb2.priamry_key with id=tb2_index_row.id) tb2_join_result.append((tb1_data_row(id,c1,c2),tb2_index_row(c2,id))) tb2_bka_buffer.sort_by(tb2_index_row.id) tb2_data_row(id,c1,c2)=(search index tb2.priamry_key with id=tb2_index_row.id) tb2_join_result.append((tb1_data_row(id,c1,c2),tb2_index_row(c2,id))) tb2_bka_buffer.dispose() return tb1_tb2_join_result
PS1：MRR特性使用的buffer大小受限于参数read_rnd_buffer_size，而BKA使用的buffer大小受限于join_buffer_size
关于BKA和MRR算法个人理解：
1、MRR针对单表操作，将"循环索引键查找"改为"索引键缓存==>索引键排序==>索引键查找"，降低随机IO操作，提升查询性能。
2、BKA针对JOIN操作，循环"关联外表"记录对"关联内表"做MRR操作，因此BKA依赖于MRR。
论证测试1：
## SQL语句 SELECT tb1.* FROM tb1 WHERE tb1.c1>100 AND tb1.c1<200; ## 执行计划 +----+-------------+-------+------------+-------+---------------+--------+---------+------+------+----------+----------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+--------+---------+------+------+----------+----------------------------------+ | 1 | SIMPLE | tb1 | NULL | range | IDX_C1 | IDX_C1 | 5 | NULL | 99 | 100.00 | Using index condition; Using MRR | +----+-------------+-------+------------+-------+---------------+--------+---------+------+------+----------+----------------------------------+
上面查询通过MRR来优化tb1上对ID的随机查找。
论证测试2：
## SQL语句
SELECT tb1.* FROM tb1 INNER JOIN tb2 ON tb1.c2 = tb2.c2 WHERE tb1.c1>100 AND tb1.c1<200; ## 执行计划： +----+-------------+-------+------------+--------+---------------+---------+---------+---------------+------+----------+-----------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+--------+---------------+---------+---------+---------------+------+----------+-----------------------------------------------+ | 1 | SIMPLE | tb1 | NULL | range | IDX_C1 | IDX_C1 | 5 | NULL | 99 | 100.00 | Using index condition; Using where; Using MRR | | 1 | SIMPLE | tb2 | NULL | eq_ref | PRIMARY | PRIMARY | 4 | demodb.tb1.C2 | 1 | 100.00 | Using index | +----+-------------+-------+------------+--------+---------------+---------+---------+---------------+------+----------+-----------------------------------------------+
由于SELECT子句中仅包含tb1的列，无需对tb2做回表查找，执行计划显示查询未使用BKA特性，因此推断上面测试中使用到的BKA特性主要用于优化表tb2的回表查询，而不是用于优化tb2上idx_c2上的索引查找。
