ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > MySQL > 【Mysql】MySQL · 答疑解惑 · MySQL 优化器 range 的代价计算

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

MySQL 作者：小亮520cl 时间：2016-04-22 15:05:27 0 删除 编辑

原文地址：http://mysql.taobao.org/monthly/2015/11/07/
本文我们从一个索引选择的问题出发，来研究一下 MySQL 中 range 代价的计算过程，进而分析这种计算过程中存在的问题。

问题现象

第一种情况：situation_unique_key_id

  1. 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,
      `name` varchar(10) DEFAULT NULL,
      `settle_date` date DEFAULT NULL,
      `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)


    ERROR: 
    No query specified
  2. 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: 1
            Extra: Using where; Using index
    1 row in set (0.00 sec)


第二种情况：situation_without_key_id

  1. mysql> alter table cpa_order drop index id;
  2. 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: range
    possible_keys: cpao_settle_date_id
              key: cpao_settle_date_id
          key_len: 12
              ref: NULL
             rows: 1
            Extra: Using index condition
    1 row in set (0.00 sec)


    ERROR: 
    No query specified


第三种情况: situation_plain_key_id

  1. ：mysql> explain select * from cpa_order where settle_date='2015-11-05' and id > 15 \G （我们称之为 situation_plain_key_id） *************************** 1. row *************************** id: 1 
  2. select_type: SIMPLE  
  3. table: cpa_order  
  4. type: range 
  5. possible_keys: cpao_settle_date_id,id  
  6. key: cpao_settle_date_id  
  7. key_len: 12  
  8. ref: NULL rows: 3  
  9. Extra: Using index condition 
  10. 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 ?

原因分析

  1. 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;
            Empty set (0.00 sec)


            mysql> select * from information_schema.OPTIMIZER_TRACE\G;


1.首先分析situation_without_key_id的过程
    1. 删除id的唯一索引：
    2. mysql> alter table cpa_order drop index id;
    3. 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;
      Empty set (0.00 sec)


      mysql> select * from information_schema.OPTIMIZER_TRACE\G;
      *************************** 1. row ***************************
                                  QUERY: select * from cpa_order where settle_date='2015-11-05' and id > 15
                                  TRACE: {
        "steps": [
          {
            "join_preparation": {
              "select#": 1,
              "steps": [
                {
                  "expanded_query": "/* select#1 */ select `cpa_order`.`cpa_order_id` AS `cpa_order_id`,`cpa_order`.`name` AS `name`,`cpa_order`.`settle_date` AS `settle_date`,`cpa_order`.`id` AS `id` from `cpa_order` where ((`cpa_order`.`settle_date` = '2015-11-05') and (`cpa_order`.`id` > 15))"
                }
              ]
            }
          },
          {
            "join_optimization": {
              "select#": 1,
              "steps": [
                {
                  "condition_processing": {
                    "condition": "WHERE",
                    "original_condition": "((`cpa_order`.`settle_date` = '2015-11-05') and (`cpa_order`.`id` > 15))",
                    "steps": [
                      {
                        "transformation": "equality_propagation",
                        "resulting_condition": "((`cpa_order`.`id` > 15) and multiple equal('2015-11-05', `cpa_order`.`settle_date`))"
                      },
                      {
                        "transformation": "constant_propagation",
                        "resulting_condition": "((`cpa_order`.`id` > 15) and multiple equal('2015-11-05', `cpa_order`.`settle_date`))"
                      },
                      {
                        "transformation": "trivial_condition_removal",
                        "resulting_condition": "((`cpa_order`.`id` > 15) and multiple equal('2015-11-05', `cpa_order`.`settle_date`))"
                      }
                    ]
                  }
                },
                {
                  "table_dependencies": [
                    {
                      "table": "`cpa_order`",
                      "row_may_be_null": false,
                      "map_bit": 0,
                      "depends_on_map_bits": [
                      ]
                    }
                  ]
                },
                {
                  "ref_optimizer_key_uses": [
                    {
                      "table": "`cpa_order`",
                      "field": "settle_date",
                      "equals": "'2015-11-05'",
                      "null_rejecting": false
                    }
                  ]
                },
                {
                  "rows_estimation": [
                    {
                      "table": "`cpa_order`",
                      "range_analysis": {
                        "table_scan": {
                          "rows": 1,
                          "cost": 3.3
                        },
                        "potential_range_indices": [
                          {
                            "index": "PRIMARY",
                            "usable": false,
                            "cause": "not_applicable"
                          },
                          {
                            "index": "cpao_settle_date_id",
                            "usable": true,
                            "key_parts": [
                              "settle_date",
                              "id",
                              "cpa_order_id"
                            ]
                          }
                        ],
                        "setup_range_conditions": [
                        ],
                        "group_index_range": {
                          "chosen": false,
                          "cause": "not_group_by_or_distinct"
                        },





    8.                   "analyzing_range_alternatives": {                             -------range代价计算过程
                          "range_scan_alternatives": [
                            {
                              "index": "cpao_settle_date_id",                    -----range计算使用的是这个索引
                              "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": 1,
                              "cost": 2.21,
                              "chosen": true
                            }
                          ],



    11.                     "analyzing_roworder_intersect": {
                            "usable": false,
                            "cause": "too_few_roworder_scans"
                          }
                        },
                        "chosen_range_access_summary": {
                          "range_access_plan": {
                            "type": "range_scan",
                            "index": "cpao_settle_date_id",
                            "rows": 1,
                            "ranges": [
                              "2015-11-05 <= settle_date <= 2015-11-05 AND 15 < id"
                            ]
                          },
                          "rows_for_plan": 1,
                          "cost_for_plan": 2.21,
                          "chosen": true
                        }
                      }
                    }
                  ]
                },
                {




    15.             "considered_execution_plans": [                                             ----索引选择过程
                    {
                      "plan_prefix": [
                      ],
                      "table": "`cpa_order`",
                      "best_access_path": {
                        "considered_access_paths": [
                          {
                            "access_type": "ref",
                            "index": "cpao_settle_date_id",                            -----ref使用的是cpao_settle_date_id这个索引，rang与ref使用的是相同的索引
                            "rows": 1, 
                            "cost": 1.2,
                            "chosen": true
                          },
                          {
                            "access_type": "range",                
                            "rows": 1,
                            "cost": 2.41,
                            "chosen": false
                          }
                        ]
                      },
                      "cost_for_plan": 1.2,
                      "rows_for_plan": 1,
                      "chosen": true
                    }
                  ]
                },

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




    3.           {
                  "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"
                        }
                      }
                    ],
    4. ###############在计算的结尾处优化器做了个优化，就是把 id 字段也考虑了进来我们根据 attached_conditions_computation 的提示找到了如下代码:
      1. 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;
              }




    7.               "attached_conditions_summary": [
                      {
                        "table": "`cpa_order`",
                        "attached": "((`cpa_order`.`settle_date` = '2015-11-05') and (`cpa_order`.`id` > 15))"
                      }
                    ]
                  }
                },
                {
                  "refine_plan": [
                    {
                      "table": "`cpa_order`",
                      "pushed_index_condition": "((`cpa_order`.`settle_date` = '2015-11-05') and (`cpa_order`.`id` > 15))",
                      "table_condition_attached": null,
                      "access_type": "range"
                    }
                  ]
                }
              ]
            }
          },
          {
            "join_execution": {
              "select#": 1,
              "steps": [
              ]
            }
          }
        ]
      }
      MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0
                INSUFFICIENT_PRIVILEGES: 0
      1 row in set (0.00 sec)


      ERROR: 
      No query specified


    结合注释，我们可以这样理解：

    • ref 与 range 使用的是相同的索引；
    • 当前 table 选择的索引采用的是ref；
    • ref key 的使用的长度小于 range 的长度，则优先使用 range。
    因此，在 situation_without_key_id 时，三个条件都满足，所以使用了 range 中的联合索引,那为什么 situation_unique_key_id 没有使用 id 呢，


2分析situation_unique_key_id 的过程
  1. 加上id的唯一索引后
  2. mysql> select * from information_schema.OPTIMIZER_TRACE\G;
    *************************** 1. row ***************************
                                QUERY: select * from cpa_order where settle_date='2015-11-05' and id > 15
                                TRACE: {
      "steps": [
        {
          "join_preparation": {
            "select#": 1,
            "steps": [
              {
                "expanded_query": "/* select#1 */ select `cpa_order`.`cpa_order_id` AS `cpa_order_id`,`cpa_order`.`name` AS `name`,`cpa_order`.`settle_date` AS `settle_date`,`cpa_order`.`id` AS `id` from `cpa_order` where ((`cpa_order`.`settle_date` = '2015-11-05') and (`cpa_order`.`id` > 15))"
              }
            ]
          }
        },
        {
          "join_optimization": {
            "select#": 1,
            "steps": [
              {
                "condition_processing": {
                  "condition": "WHERE",
                  "original_condition": "((`cpa_order`.`settle_date` = '2015-11-05') and (`cpa_order`.`id` > 15))",
                  "steps": [
                    {
                      "transformation": "equality_propagation",
                      "resulting_condition": "((`cpa_order`.`id` > 15) and multiple equal('2015-11-05', `cpa_order`.`settle_date`))"
                    },
                    {
                      "transformation": "constant_propagation",
                      "resulting_condition": "((`cpa_order`.`id` > 15) and multiple equal('2015-11-05', `cpa_order`.`settle_date`))"
                    },
                    {
                      "transformation": "trivial_condition_removal",
                      "resulting_condition": "((`cpa_order`.`id` > 15) and multiple equal('2015-11-05', `cpa_order`.`settle_date`))"
                    }
                  ]
                }
              },
              {
                "table_dependencies": [
                  {
                    "table": "`cpa_order`",
                    "row_may_be_null": false,
                    "map_bit": 0,
                    "depends_on_map_bits": [
                    ]
                  }
                ]
              },
              {
                "ref_optimizer_key_uses": [
                  {
                    "table": "`cpa_order`",
                    "field": "settle_date",
                    "equals": "'2015-11-05'",
                    "null_rejecting": false
                  }
                ]
              },
              {
                "rows_estimation": [
                  {
                    "table": "`cpa_order`",
                    "range_analysis": {
                      "table_scan": {
                        "rows": 1,
                        "cost": 3.3
                      },
                      "potential_range_indices": [
                        {
                          "index": "PRIMARY",
                          "usable": false,
                          "cause": "not_applicable"
                        },
                        {
                          "index": "id",
                          "usable": true,
                          "key_parts": [
                            "id"
                          ]
                        },
                        {
                          "index": "cpao_settle_date_id",
                          "usable": true,
                          "key_parts": [
                            "settle_date",
                            "id",
                            "cpa_order_id"
                          ]
                        }
                      ],
                      "setup_range_conditions": [
                      ],
                      "group_index_range": {
                        "chosen": false,
                        "cause": "not_group_by_or_distinct"
                      },




  6.                   "analyzing_range_alternatives": {
                        "range_scan_alternatives": [
                          {
                            "index": "id",                                      ---range代价计算过程，使用的是id这个索引
                            "ranges": [
                              "15 < id"
                            ],
                            "index_dives_for_eq_ranges": true,
                            "rowid_ordered": false,
                            "using_mrr": false,
                            "index_only": false,
                            "rows": 1,
                            "cost": 2.21,
                            "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": 1,
                            "cost": 2.21,
                            "chosen": false,
                            "cause": "cost"
                          }
                        ],






  12.                     "analyzing_roworder_intersect": {
                          "usable": false,
                          "cause": "too_few_roworder_scans"
                        }
                      },
                      "chosen_range_access_summary": {
                        "range_access_plan": {
                          "type": "range_scan",
                          "index": "id",
                          "rows": 1,
                          "ranges": [
                            "15 < id"
                          ]
                        },
                        "rows_for_plan": 1,
                        "cost_for_plan": 2.21,
                        "chosen": true
                      }
                    }
                  }
                ]
              },
              {




  16.             "considered_execution_plans": [
                  {
                    "plan_prefix": [
                    ],
                    "table": "`cpa_order`",
                    "best_access_path": {
                      "considered_access_paths": [
                        {
                          "access_type": "ref",
                          "index": "cpao_settle_date_id",                        -----ref索引的选择，使用的是cpao_settle_date_id这个索引
                          "rows": 1,
                          "cost": 1.2,
                          "chosen": true
                        },
                        {
                          "access_type": "range",
                          "rows": 1,
                          "cost": 2.41,
                          "chosen": false
                        }
                      ]
                    },
                    "cost_for_plan": 1.2,
                    "rows_for_plan": 1,
                    "chosen": true
                  }
                ]
              },
              {





  21.             "attaching_conditions_to_tables": {
                  "original_condition": "((`cpa_order`.`settle_date` = '2015-11-05') and (`cpa_order`.`id` > 15))",
                  "attached_conditions_computation": [
                  ],
                  "attached_conditions_summary": [
                    {
                      "table": "`cpa_order`",
                      "attached": "(`cpa_order`.`id` > 15)"
                    }
                  ]
                }
              },
              {
                "refine_plan": [
                  {
                    "table": "`cpa_order`",
                    "pushed_index_condition": "(`cpa_order`.`id` > 15)",
                    "table_condition_attached": null
                  }
                ]
              }
            ]
          }
        },
        {
          "join_execution": {
            "select#": 1,
            "steps": [
            ]
          }
        }
      ]
    }
    MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0
              INSUFFICIENT_PRIVILEGES: 0
    1 row in set (0.00 sec)


    ERROR: 
    No query specified
  22. 总和分析过程一的三个条件发现：在range 的代价计算过程中使用的是 id 这个索引，导致 unique id 这个索引与联合索引 cpao_settle_date_id 并不是同样的索引，不满足第一个条件，因此不进行优化。


3分析situation_plain_key_id 的过程
有了上面的分析，我们观察 situation_plain_key_id 的代价及生成过程，situation_plain_key_id 在 range 的代价计算过程中选择的是 cpao_settle_date_id 索引，计算过程是将后者的计算结果与前者进行比较，因此即使相等，也是先入为主，其optimizer_trace和过程一situation_without_key_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 的选项、含义、作用、以及在内核中是如何实现的，达到一起学习的目的。



来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/29096438/viewspace-2086097/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： 【Mysql】MySQL · 特性分析 · Index Condition Pushdown (ICP)
下一篇： 【Mysql】mysql开启审计功能
请登录后发表评论 登录
全部评论
小亮520cl
专家姓名：陈亮
毕业以后专业任职数据库工程师职位，itpub一直作为自己的笔记记录的地方,blog写的不详细，草书笔记，仅供参考！

注册时间：2013-09-12

  • 博文量
    529
  • 访问量
    1126987

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593