MySQL · 答疑解惑 · MySQL 优化器 range 的代价计算-阿里云开发者社区

开发者社区> db匠> 正文

MySQL · 答疑解惑 · MySQL 优化器 range 的代价计算

简介: 本文我们从一个索引选择的问题出发,来研究一下 MySQL 中 range 代价的计算过程,进而分析这种计算过程中存在的问题。 问题现象 第一种情况:situation_unique_key_id mysql> show create table cpa_order\G ***********
+关注继续查看

本文我们从一个索引选择的问题出发,来研究一下 MySQL 中 range 代价的计算过程,进而分析这种计算过程中存在的问题。

问题现象

第一种情况:situation_unique_key_id

mysql> show create table cpa_order\G
*************************** 1. row ***************************
       Table: cpa_order
Create Table: CREATE TABLE `cpa_order` (
  `cpa_order_id` bigint(20) unsigned NOT NULL,
  ...
  `settle_date` date DEFAULT NULL COMMENT,
  `id` bigint(20) NOT NULL,
  PRIMARY KEY (`cpa_order_id`),
  UNIQUE KEY `id` (`id`),
  KEY `cpao_settle_date_id` (`settle_date`,`id`),
) ENGINE=InnoDB DEFAULT CHARSET=gbk
1 row in set (0.00 sec)

mysql> explain select * from cpa_order where settle_date='2015-11-05' and id > 15 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: cpa_order
         type: ref
possible_keys: id,cpao_settle_date_id
          key: cpao_settle_date_id
      key_len: 4
          ref: const
         rows: 7
        Extra: Using index condition
1 row in set (0.00 sec)

SQL 语句执行过程可以看出,当 id 为 unique key 的时候,key_len= 4, 不难发现联合索引只使用了字段 cpao_settle_date_id ,而 id 并没有使用;

第二种情况:situation_without_key_id

mysql> alter table cpa_order drop index id;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select * from cpa_order where settle_date='2015-11-05' and id > 15 \G (我们称之为 situation_without_key_id)
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: cpa_order
         type: range
possible_keys: cpao_settle_date_id
          key: cpao_settle_date_id
      key_len: 12
          ref: NULL
         rows: 3
        Extra: Using index condition
1 row in set (0.00 sec)

第三种情况: situation_plain_key_id

mysql> explain select * from cpa_order where settle_date='2015-11-05' and id > 15 \G (我们称之为 situation_plain_key_id)
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: cpa_order
         type: range
possible_keys: cpao_settle_date_id,id
          key: cpao_settle_date_id
      key_len: 12
          ref: NULL
         rows: 3
        Extra: Using index condition
1 row in set (0.01 sec)

以上的两个 SQL 语句在使用索引 cpao_settle_date_id 的时候两个字段都使用到了,因此过滤性应该更好,我们将上面的3种情况分别称之为 situation_unique_key_id,situation_without_key_id,situation_plain_key_id,以方便我们分析问题。

为什么在 id 为 unique 的时候联合索引只使用了其中的一个字段而没有字段 id ?

原因分析

MySQL 有一个很好的东东叫 optimizer trace,它提供了 MySQL 执行计划生成的各个阶段的详细信息,其中索引部分的分析更是详细,但是由于 optimizer trace 的东西比较多,我们在分析的时候只将本文相关的内容进行展开,optimizer trace 的详细使用

打开并使用 optimizer_trace 功能,观察situation_unique_key_id 的代价生成过程的:

mysql> set optimizer_trace="enabled=on";
Query OK, 0 rows affected (0.00 sec)

mysql> select * from cpa_order where settle_date='2015-11-05' and id > 15 \G
3 rows in set (0.00 sec)

mysql> select * from information_schema.OPTIMIZER_TRACE\G

在 range 代价计算后,优化器会选择一个代价较小的 index 生成一个 read_plan 缓存起来,根据下面的代价计算过程可以看到,索引在代价计算过程中虽然是相等的,但先入为主,选择的其实是 id 这个索引。

range 部分的代价计算过程:

"range_scan_alternatives": [
                      {
                        "index": "id",
                        "ranges": [
                          "15 < id"
                        ],
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": false,
                        "using_mrr": false,
                        "index_only": false,
                        "rows": 3,
                        "cost": 4.61,
                        "chosen": true
                      },
                      {
                        "index": "cpao_settle_date_id",
                        "ranges": [
                          "2015-11-05 <= settle_date <= 2015-11-05 AND 15 < id"
                        ],
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": false,
                        "using_mrr": false,
                        "index_only": false,
                        "rows": 3,
                        "cost": 4.61,
                        "chosen": false,
                        "cause": "cost"
                      }

表的索引选择过程,主要是 ref & range 的索引方式的选择:

"considered_execution_plans": [
              {
                "plan_prefix": [
                ],
                "table": "`cpa_order`",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "access_type": "ref",
                      "index": "cpao_settle_date_id",
                      "rows": 7,
                      "cost": 3.4,
                      "chosen": true
                    },
                    {
                      "access_type": "range",
                      "rows": 3,
                      "cost": 5.21,
                      "chosen": false
                    }
                  ]
                },
                "cost_for_plan": 3.4,
                "rows_for_plan": 7,
                "chosen": true
              }
            ]

可以看到优化器在比较 ref & range 的代价的时候,ref 的代价更小,所以选择的是ref,到这里我们觉得选择 ref 是“合理”的,但是当我们想到联合索引的作用时,我们应该觉得这是“不正常的”,至少这不应该是最终的索引选择方式。

观察 situation_without_key_id 的代价及生成过程,其 optimizer_trace 如下:

range 部分的代价计算过程:

                  "analyzing_range_alternatives": {
                    "range_scan_alternatives": [
                      {
                        "index": "cpao_settle_date_id",
                        "ranges": [
                          "2015-11-05 <= settle_date <= 2015-11-05 AND 15 < id"
                        ],
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": false,
                        "using_mrr": false,
                        "index_only": false,
                        "rows": 3,
                        "cost": 4.61,
                        "chosen": true
                      }
                    ],
                    "analyzing_roworder_intersect": {
                      "usable": false,
                      "cause": "too_few_roworder_scans"
                    }
                  },

表的索引选择过程:

            "considered_execution_plans": [
              {
                "plan_prefix": [
                ],
                "table": "`cpa_order`",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "access_type": "ref",
                      "index": "cpao_settle_date_id",
                      "rows": 7,
                      "cost": 3.4,
                      "chosen": true
                    },
                    {
                      "access_type": "range",
                      "rows": 3,
                      "cost": 5.21,
                      "chosen": false
                    }
                  ]
                },
                "cost_for_plan": 3.4,
                "rows_for_plan": 7,
                "chosen": true
              }
            ]

可以看到,由于 where 条件中只有 cpao_settle_date_id & id 部分,索引选择的仍是ref, 其代价的计算结果与 situation_unique_key_id 中的代价是一致的,但是在 optimizer_trace 后面发现了如下的优化:

"attaching_conditions_to_tables": {
              "original_condition": "((`cpa_order`.`settle_date` = '2015-11-05') and (`cpa_order`.`id` > 15))",
              "attached_conditions_computation": [
                {
                  "access_type_changed": {
                    "table": "`cpa_order`",
                    "index": "cpao_settle_date_id",
                    "old_type": "ref",
                    "new_type": "range",
                    "cause": "uses_more_keyparts"
                  }
                }

这里我们不难看出,在计算的结尾处优化器做了个优化,就是把 id 字段也考虑了进来,我们根据 attached_conditions_computation 的提示找到了如下代码:

      if (tab->type == JT_REF &&                                  // 1)
          !tab->ref.depend_map &&                                 // 2)
          tab->quick &&                                           // 3)
          (uint) tab->ref.key == tab->quick->index &&             // 4)
          tab->ref.key_length < tab->quick->max_used_key_length)  // 5)
      {
        tab->type=JT_ALL;
        use_quick_range=1;
        tab->use_quick=QS_RANGE;
        tab->ref.key= -1;
        tab->ref.key_parts=0;
      }

结合注释,我们可以这样理解:

  • ref 与 range 使用的是相同的索引;
  • 当前 table 选择的索引采用的是ref;
  • ref key 的使用的长度小于 range 的长度,则优先使用 range。

因此,在 situation_without_key_id 时,三个条件都满足,所以使用了 range 中的联合索引,那为什么 situation_unique_key_id 没有使用 id 呢,原因是在range 的代价计算过程中使用的是 id 这个索引,导致 unique id 这个索引与联合索引 cpao_settle_date_id 并不是同样的索引,不满足第一个条件,因此不进行优化。

有了上面的分析,我们观察 situation_plain_key_id 的代价及生成过程,situation_plain_key_id 在 range 的代价计算过程中选择的是 cpao_settle_date_id 索引,计算过程是将后者的计算结果与前者进行比较,因此即使相等,也是先入为主,其optimizer_trace如下:

range 部分的代价计算过程:

                    "range_scan_alternatives": [
                      {
                        "index": "cpao_settle_date_id",
                        "ranges": [
                          "2015-11-05 <= settle_date <= 2015-11-05 AND 15 < id"
                        ],
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": false,
                        "using_mrr": false,
                        "index_only": false,
                        "rows": 3,
                        "cost": 4.61,
                        "chosen": true
                      },
                      {
                        "index": "id",
                        "ranges": [
                          "15 < id"
                        ],
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": false,
                        "using_mrr": false,
                        "index_only": false,
                        "rows": 3,
                        "cost": 4.61,
                        "chosen": false,
                        "cause": "cost"
                      }
                    ]

表的索引选择过程:

            "considered_execution_plans": [
              {
                "plan_prefix": [
                ],
                "table": "`cpa_order`",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "access_type": "ref",
                      "index": "cpao_settle_date_id",
                      "rows": 7,
                      "cost": 3.4,
                      "chosen": true
                    },
                    {
                      "access_type": "range",
                      "rows": 3,
                      "cost": 5.21,
                      "chosen": false
                    }
                  ]
                },
                "cost_for_plan": 3.4,
                "rows_for_plan": 7,
                "chosen": true
              }
            ]

结合上面的分析我们发现,ref & range 选择都是索引 cpao_settle_date_id,因此在最后的选择阶段也会进行索引的优化,与开头的问题表现相符。

range 代价计算过程

优化器在索引选择的过程中会将where 条件、join 条件等信息进行收集,对于非等值的索引会放到 possible keys 中,进行 range 部分的代价计算,对于等值相关字段的索引会进行 ref 部分的代价计算,如果是单表,其主要过程如下:

  • 调用 get_key_scans_params 从已知的索引中选择一个代价最小的 read_plan,利用 read_plan 生成一个读表的计划,缓存至 tab->quick 中;

  • 在 best_access_path 中计算:
    1. 全表的代价
    2. 如果有覆盖索引则计算覆盖索引的代价
    3. 如果有quick,则利用一些校验值计算上一步产生的 range 的代价

    然后取其中最小的值用做当前表的代价;

  • 在 make_join_select 中对已经生成的执行计划进行较正,如 situation_plain_key_id 的优化部分。

多表的计算过程更为复杂,不在此描述。

问题解答

为什么在 id 为 unique 的时候联合索引只使用了其中的一个字段而没有字段 id ?

由于 situation_unique_key_id 中在计算 range 的过程中使用的是索引 id 而不是 cpao_settle_date_id,因此不符合最后优化的条件,因此只使用了 cpao_settle_date_id 的前一部分而没有使用 id,这是优化器在实现过程中的问题。

range 代价计算过程可能引起的问题

我们已经了解了 range 代价计算的过程,可以发现可能会有以下问题:

  • 当多个索引得到的代价是相同的,由于先入为主,只能缓存第一个,所以会有索引出错的问题;
  • 每一次计算 range 的代价都会将缓存清空,如 order by limit 操作,这样有可能将之前的索引清空且走错索引,详情见 bug#78993

小结

当执行计划出错的时候,我们可以有效的利用 optimizer_trace 来进行初步的分析,大部分还是有解的。另外由于执行计划的内容比较多,从本篇起,小编会尽量将优化器相关的东西给大家介绍一下,主要包括 optimizer_swith 的选项、含义、作用、以及在内核中是如何实现的,达到一起学习的目的。

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
双十一会场体验 SSR 优化 - 走向更复杂的渲染架构
今年(2020)我们在不改变现有架构,不改变业务的前提下,在会场上使用了 SSR 技术,将秒开率提高到了新的高度(82.6%);也观察到在用户体验得到优化的同时,业务指标如 UV 点击率等也有小幅的增长(视不同业务场景有不同的提升,最大可达 5%),带来了不错的业务价值。
889 0
让Elasticsearch飞起来!——性能优化实践干货
0、题记 Elasticsearch性能优化的最终目的:用户体验爽。 关于爽的定义——著名产品人梁宁曾经说过“人在满足时候的状态叫做愉悦,人不被满足就会难受,就会开始寻求。如果这个人在寻求中,能立刻得到即时满足,这种感觉就是爽!”。 Elasticsearch的爽点就是:快、准、全! 关于Elasticsearch性能优化,阿里、腾讯、京东、携程、滴滴、58等都有过很多深入的实践总结,都是非常好的参考。本文换一个思路,基于Elasticsearch的爽点,进行性能优化相关探讨。
24 0
《Oracle高性能SQL引擎剖析:SQL优化与调优机制详解》一2.4 执行计划各个操作的含义
本节书摘来自华章出版社《Oracle高性能SQL引擎剖析:SQL优化与调优机制详解》一 书中的第2章,第2.4节,作者:黄玮,更多章节内容可以访问云栖社区“华章计算机”公众号查看。
1643 0
MySQL优化
MySQL优化
1207 0
PHPpraffa也有了,一个PHP版本的阿里云函数计算与API网关的开发框架
发布了Python版本的函数计算与API网关的开发框架后,一直觉得对不起PHP,因为公司一直是用PHP的,我这弄了个Python,实在不该,对了,(Python版本说明点这里。 PHPpraffa是什么? PHPpraffa 是praffa的PHP版本。
1159 0
SQL Server优化器特性-隐式谓词
原文:SQL Server优化器特性-隐式谓词 我们都知道,一条SQL语句提交给优化器会产生相应的执行计划然后执行输出结果,但他的执行计划是如何产生的呢?这可能是关系型数据库最复杂的部分了.这里我为大家介绍一个有关SQL Server优化器的特性-隐式谓词,并简单介绍在此特性下如何根据场景控制优化器的行为.
779 0
+关注
db匠
rds内核团队秘密研发的全自动卖萌机. 追加特效: 发数据库内核月报. 月报传送: http://mysql.taobao.org/monthly/
497
文章
0
问答
来源圈子
更多
阿里云数据库:帮用户承担一切数据库风险,给您何止是安心!支持关系型数据库:MySQL、SQL Server、PostgreSQL、PPAS(完美兼容Oracle)、自研PB级数据存储的分布式数据库Petadata、自研金融级云数据库OceanBase支持NoSQL数据库:MongoDB、Redis、Memcache更有褚霸、丁奇、德哥、彭立勋、玄惭、叶翔等顶尖数据库专家服务。
+ 订阅
文章排行榜
最热
最新
相关电子书
更多
《2021云上架构与运维峰会演讲合集》
立即下载
《零基础CSS入门教程》
立即下载
《零基础HTML入门教程》
立即下载