首页 / MYSQL / MySQL Index--BAK和MRR演示

MySQL Index--BAK和MRR演示

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了MySQL Index--BAK和MRR演示，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含3750字，纯文字阅读大概需要6分钟。

内容图文

搭建测试环境演示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: 5ref: 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: 5ref: demodb.tb1.C1
         rows: 1
     filtered: 100.00
        Extra: Using join buffer (Batched Key Access)
2 rows inset, 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` isnot 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 isnot full or tb1_index_row is last row:
        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.dispose()

tb1_bka_buffer=new buffer(@@join_buffer_size)
tb1_tb2_join_result=[]
for each tb1_data_row(id,c1,c2) in tb1_search_result:
    if tb1_bka_buffer isnot full or tb1_data_row is last row:
        tb1_bka_buffer.append(tb1_data_row(id,c1,c2))
    else：
        ## using bka        tb1_bka_buffer.sort_by(c1)
        for tb1_data_row(id,c1,c2) in tb1_bka_buffer:
            for tb2_index_row(c2,id) in (range scan tb2.idx_c2 where c2=tb1_data_row.c1)
                tb2_data_row(id,c1,c2)=(search index tb2.priamry_key with id=tb2_index_row.id)
                join_row=(tb1_data_row(id,c1,c2),tb2_data_row(id,c1,c2))
                tb1_tb2_join_result.append(join_row)
tb1_bka_buffer.dispose()
return tb1_tb2_join_result

PS1：MRR特性使用的buffer大小受限于参数read_rnd_buffer_size，而BKA使用的buffer大小受限于join_buffer_size

原文：https://www.cnblogs.com/gaogao67/p/12172876.html

内容总结

以上是互联网集市为您收集整理的MySQL Index--BAK和MRR演示全部内容，希望文章能够帮你解决MySQL Index--BAK和MRR演示所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

扫描二维码推送至手机访问。

本文链接：https://qyyshop.com/info/1307821.html

来源：【匿名】