ITPub博客

首页 > 数据库 > MySQL > MYSQL sql执行过程的一些跟踪分析(二.mysql优化器追踪分析)

MYSQL sql执行过程的一些跟踪分析(二.mysql优化器追踪分析)

原创 MySQL 作者:darren__chan 时间:2019-01-28 21:05:12 0 删除 编辑
以下 left join语句,d表与s表关联,当where条件在d.deptid上时,s表无法走索引。因此通过开启trace方式做一些追踪。
root@(none) 09:20:20>explain SELECT * FROM SSS.DEPARTMENT d LEFT JOIN ppp.shop s ON d.DEPTID = s.DEPTID WHERE d.DEPTID = '00001111';
+----+-------------+-------+------------+-------+----------------------------+---------+---------+-------+--------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys              | key     | key_len | ref   | rows   | filtered | Extra       |
+----+-------------+-------+------------+-------+----------------------------+---------+---------+-------+--------+----------+-------------+
|  1 | SIMPLE      | d     | NULL       | const | PRIMARY,INDEX_DEPARTMENT_5 | PRIMARY | 130     | const |      1 |   100.00 | NULL        |
|  1 | SIMPLE      | s     | NULL       | ALL   | NULL                       | NULL    | NULL    | NULL  | 978629 |   100.00 | Using where |
+----+-------------+-------+------------+-------+----------------------------+---------+---------+-------+--------+----------+-------------+


开启optimizer_trace:

set optimizer_trace='enabled=on';

 set optimizer_trace_max_mem_size=1000000;

 

 set end_markers_in_json=on;

 

 执行语句

 

 select * from information_schema.optimizer_trace\G;

root@(none) 09:39:58> select * from information_schema.optimizer_trace\G;
*************************** 1. row ***************************
                            QUERY: SELECT * FROM SSS.DEPARTMENT d LEFT JOIN ppp.shop s ON d.DEPTID = s.DEPTID WHERE d.DEPTID = '00001111'
                            TRACE: {
  "steps": [
  
  #准备阶段
    {
      "join_preparation": {
        "select#": 1,
        "steps": [
          {  
          #expanded_query,解析查询语句,"*" 转换成字段,left join on 处转化成on((`SSS`.`d`.`Deptid` = convert(`ppp`.`s`.`Deptid` using utf8mb4))))
            "expanded_query": "/* select#1 */ select `SSS`.`d`.`Organid` AS `Organid`,。。。`s`.`Status` AS `Status`,`ppp`.`s`.`Stylecategoryid` AS `Stylecategoryid`,`ppp`.`s`.`Turnontime` AS `Turnontime` from (`SSS`.`department` `d` left join `ppp`.`shop` `s` on((`SSS`.`d`.`Deptid` = convert(`ppp`.`s`.`Deptid` using utf8mb4)))) where (`SSS`.`d`.`Deptid` = '00001111')"
          },
          {
          #转化成的nested join语句:
            "transformations_to_nested_joins": {
              "transformations": [
                "parenthesis_removal"
              ] /* transformations */,
              "expanded_query": "/* select#1 */ select `SSS`.`d`.`Organid`。。。 `SSS`.`d`.`Guidecode` AS `Guidecode`,`SSS`.`d`.`Createdate` AS `Createdate`,`SSS`.`d`.`Plateformuser` AS `Plateformuser`,`SSS`.`d`.`Plateformdept` AS `Plateformdept`,`SSS`.`d`.`Agentuser` AS `Agentuser`,`SSS`.`d`.`Agentdept` AS `Agentdept`,`SSS`.`d`.`Shopstatus` AS `Shopstatus`,`SSS`.`d`.`Deptshortname` AS `Deptshortname`,`SSS`.`d`.`Storetype` AS `Storetype`,`SSS`.`d`.`Depttype` AS `Depttype`,`ppp`.`s`.`Shopid` AS `Shopid`,`ppp`.`s`.`Objectid` AS `Objectid`,`ppp`.`s`.`Shopname` AS `Shopname`,`ppp`Tel`,`ppp`.`s`.`Introduce` AS `Introduce`,`ppp`.`s`.`Industry` AS `Industry`,`ppp`.`s`.`Address` AS `Address`,`ppp`.`s`.`Shop360image` AS `Shop360image`,`ppp`.`s`.`Domain` AS `Domain`,`ppp`.`s`.`Organid` AS `Organid`,`ppp`.`s`.`Deptid` AS `Deptid`,`ppp`.`s`.`Brandids` AS `Brandids`,`ppp`.`s`.`Extdata` AS `Extdata`,`ppp`.`s`.`Ranking` AS `Ranking`,`ppp`.`s`.`Isdelete` AS `Isdelete`,`ppp`.`s`.`District` AS `District`,`ppp`.`s`.`City` AS `City`,`ppp`.`s`.`Province` AS `Province`,`ppp`.`s`.`Phone` AS `Phone`,`ppp`.`s`.`Watermarkimage` AS `Watermarkimage`,`ppp`.`s`.`Drawingimage` AS `Drawingimage`,`ppp`.`s`.`Contactuser` AS `Contactuser`,`ppp`.`s`.`Panoloadingimage` AS `Panoloadingimage`,`ppp`.`s`.`Lngandlat` AS `Lngandlat`,`ppp`.`s`.`Createtime` AS `Createtime`,`ppp`.`s`.`Shoptype` AS `Shoptype`,`ppp`.`s`.`Status` AS `Status`,`ppp`.`s`.`Stylecategoryid` AS `Stylecategoryid`,`ppp`.`s`.`Turnontime` AS `Turnontime` from `SSS`.`department` `d` left join `ppp`.`shop` `s` on((`SSS`.`d`.`Deptid` = convert(`ppp`.`s`.`Deptid` using utf8mb4))) where (`SSS`.`d`.`Deptid` = '00001111')"
            } /* transformations_to_nested_joins */
          }
        ] /* steps */
      } /* join_preparation */
    },#准备阶段结束
    
    
    {
    #优化阶段:
      "join_optimization": {
        "select#": 1,
        "steps": [
          { 
          #处理where条件部分,化简条件:
            "condition_processing": {
              "condition": "WHERE",
              "original_condition": "(`SSS`.`d`.`Deptid` = '00001111')",---原始条件
              "steps": [
                {
                  "transformation": "equality_propagation", ----等式处理
                  "resulting_condition": "(`SSS`.`d`.`Deptid` = '00001111')"
                },
                {
                  "transformation": "constant_propagation",-----常量处理
                  "resulting_condition": "(`SSS`.`d`.`Deptid` = '00001111')"
                },
                {
                  "transformation": "trivial_condition_removal",----去除多余无关的条件处理
                  "resulting_condition": "(`SSS`.`d`.`Deptid` = '00001111')"
                }
              ] /* steps */
            } /* condition_processing */
          },#结束,因为这里已经够简化了,所以三次处理后都是同样的。
          
          {
          #替代产生的字段
            "substitute_generated_columns": {
            } /* substitute_generated_columns */
          },
          
          {
          #表依赖关系检查
            "table_dependencies": [
              {
                "table": "`SSS`.`department` `d`", ------表d
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": [
                ] /* depends_on_map_bits */
              },
              {
                "table": "`ppp`.`shop` `s`", --------表s
                "row_may_be_null": true,
                "map_bit": 1,
                "depends_on_map_bits": [
                  0
                ] /* depends_on_map_bits */
              }
            ] /* table_dependencies */
          }, #表依赖关系检查结束
          
          
          {#找出可使用索引的字段:
            "ref_optimizer_key_uses": [
              {
                "table": "`SSS`.`department` `d`",
                "field": "Deptid", ----------可用的是Deptid
                "equals": "'00001111'",
                "null_rejecting": false ---
              },
              {
                "table": "`SSS`.`department` `d`",
                "field": "Deptid",
                "equals": "'00001111'",
                "null_rejecting": false
              }
            ] /* ref_optimizer_key_uses */
          },
          
          {#评估每个表单表访问行数及相应代价。
            "rows_estimation": [
              {
                "table": "`SSS`.`department` `d`",
                "rows": 1,   ---返回1行
                "cost": 1,   ---代价为1
                "table_type": "const",  ---d表使用的方式是const,即根据主键索引获取
                "empty": false
              },
              {
                "table": "`ppp`.`shop` `s`",
                "table_scan": { -------s表直接使用全表扫描
                  "rows": 978662, ------扫描978662行
                  "cost": 8109    ------代价为8109
                } /* table_scan */
              }
            ] /* rows_estimation */
          },
          
          
          {#评估执行计划,这里考虑两表连接
            "considered_execution_plans": [
              {
                "plan_prefix": [------------------执行计划的前缀,这里是d表,因为是left join 我认为指的应该是驱动表的意思
                  "`SSS`.`department` `d`"
                ] /* plan_prefix */,
                "table": "`ppp`.`shop` `s`",
                "best_access_path": {-------最优访问路径
                  "considered_access_paths": [考虑的访问路径
                    {
                      "rows_to_scan": 978662,---扫描978662行
                      "access_type": "scan",--------全表扫描的方式
                      "resulting_rows": 978662,
                      "cost": 203841,----------使用代价
                      "chosen": true-------选中
                    }
                  ] /* considered_access_paths */
                } /* best_access_path */,
                "condition_filtering_pct": 100,条件过滤率100%,指的是这里与上一个表进行行过滤的行数
                "rows_for_plan": 978662,------执行计划的扫描行数978662
                "cost_for_plan": 203841,-------执行计划的cost203841
                "chosen": true---------选中
              }
            ] /* considered_execution_plans */
          },
          
          
          {#检查带常量表的条件
            "condition_on_constant_tables": "('00001111' = '00001111')",
            "condition_value": true
          },
          
          
          {   #将常量条件作用到表,这里主要是将d表的中的deptid条件作用到s表的deptid
            "attaching_conditions_to_tables": {
              "original_condition": "('00001111' = '00001111')",
              "attached_conditions_computation": [
              ] /* attached_conditions_computation */,
              "attached_conditions_summary": [
                {
                  "table": "`ppp`.`shop` `s`",
                  "attached": "<if>(is_not_null_compl(s), ('00001111' = convert(`ppp`.`s`.`Deptid` using utf8mb4)), true)"
                }
              ] /* attached_conditions_summary */
            } /* attaching_conditions_to_tables */
          },
          
          
          {
            "refine_plan": [
              {
                "table": "`ppp`.`shop` `s`"
              }
            ] /* refine_plan */
          }
        ] /* steps */
      } /* join_optimization */
    },
    
    {
      "join_execution": {
        "select#": 1,
        "steps": [
        ] /* steps */
      } /* join_execution */
    }
    
  ] /* steps */
}
MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0
          INSUFFICIENT_PRIVILEGES: 0
1 row in set (0.00 sec)


以上优化器的主要步骤:

1.join_preparation :准备阶段,包查询语句转换,转换成嵌套循环语句等

   expanded_query

   transformations_to_nested_joins

2.join_optimization :优化阶段,包括以下主要阶段

  condition_processing :处理where条件部分,主要包括等式处理、常量处理、多余条件处理

  table_dependencies :表依赖检查

  ref_optimizer_key_uses :评估可用的索引

  rows_estimation        :评估访问单表的方式,及扫描的行数与代价

  considered_execution_plans :评估最终可使用的执行计划

  condition_on_constant_tables :检查带常量表的条件

  attaching_conditions_to_tables :将常量条件作用到表

  refine_plan 改进计划,不理解

3.join_execution  :执行阶段



通过以上可以看错,当优化器一开始对优化器进行评估时就直接选择了全表扫描的方式,即是说此时优化器直接忽视了s表已有的索引IND_SHOP_DEPTID。


我们将以下的d.DEPTID = '00001111' 换成s.DEPTID = '00001111',发现其可以选择了索引,此时s表看起来做了驱动表。

SELECT * FROM SSS.DEPARTMENT d LEFT JOIN ppp.shop s ON d.DEPTID = s.DEPTID WHERE s.DEPTID = '00001111';
root@SSS 04:28:39>explain  SELECT * FROM SSS.DEPARTMENT d LEFT JOIN ppp.shop s ON d.DEPTID = s.DEPTID WHERE s.DEPTID = '00001111';
+----+-------------+-------+------------+--------+----------------------------+-----------------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type   | possible_keys              | key             | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+--------+----------------------------+-----------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | s     | NULL       | ref    | IND_SHOP_DEPTID            | IND_SHOP_DEPTID | 99      | const |    1 |   100.00 | NULL        |
|  1 | SIMPLE      | d     | NULL       | eq_ref | PRIMARY,INDEX_DEPARTMENT_5 | PRIMARY         | 130     | func  |    1 |   100.00 | Using where |
+----+-------------+-------+------------+--------+----------------------------+-----------------+---------+-------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)


追踪优化器过程:

1.在ref_optimizer_key_uses 过程找到s表可以通过"'00001111'"走索引,并且通过"Deptid"等值访问

2.在rows_estimation过程中s表选择IND_SHOP_DEPTID的cost最低。

3.在considered_execution_plans过程选择IND_SHOP_DEPTID的访问路径,并访问方式是ref。

  {
            "ref_optimizer_key_uses": [
              {
                "table": "`SSS`.`department` `d`",
                "field": "Deptid",
                "equals": "convert(`ppp`.`s`.`Deptid` using utf8mb4)",
                "null_rejecting": false
              },
              {
                "table": "`SSS`.`department` `d`",
                "field": "Deptid",
                "equals": "convert(`ppp`.`s`.`Deptid` using utf8mb4)",
                "null_rejecting": false
              },
              {
                "table": "`ppp`.`shop` `s`",
                "field": "Deptid",
                "equals": "'00001111'",
                "null_rejecting": false
              }
            ] /* ref_optimizer_key_uses */
          },
          {
            "rows_estimation": [
              {
                "table": "`SSS`.`department` `d`",
                "table_scan": {
                  "rows": 911858,
                  "cost": 7212
                } /* table_scan */
              },
              {
                "table": "`ppp`.`shop` `s`",
                "range_analysis": {
                  "table_scan": {
                    "rows": 959814,
                    "cost": 200074
                  } /* table_scan */,
                  "potential_range_indexes": [
                    {
                      "index": "PRIMARY",
                      "usable": false,
                      "cause": "not_applicable"
                    },
                    {
                      "index": "IND_SHOP_DEPTID",
                      "usable": true,
                      "key_parts": [
                        "Deptid",
                        "Shopid"
                      ] /* key_parts */
                    },
                    {
                      "index": "IND_SHOP_DOMAIN",
                      "usable": false,
                      "cause": "not_applicable"
                    }
                  ] /* potential_range_indexes */,
                  "setup_range_conditions": [
                  ] /* setup_range_conditions */,
                  "group_index_range": {
                    "chosen": false,
                    "cause": "not_single_table"
                  } /* group_index_range */,
                  "analyzing_range_alternatives": {
                    "range_scan_alternatives": [
                      {
                        "index": "IND_SHOP_DEPTID",
                        "ranges": [
                          "00001111 <= Deptid <= 00001111"
                        ] /* ranges */,
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": true,
                        "using_mrr": false,
                        "index_only": false,
                        "rows": 1,
                        "cost": 2.21,
                        "chosen": true
                      }
                    ] /* range_scan_alternatives */,
                    "analyzing_roworder_intersect": {
                      "usable": false,
                      "cause": "too_few_roworder_scans"
                    } /* analyzing_roworder_intersect */
                  } /* analyzing_range_alternatives */,
                  "chosen_range_access_summary": {
                    "range_access_plan": {
                      "type": "range_scan",
                      "index": "IND_SHOP_DEPTID",
                      "rows": 1,
                      "ranges": [
                        "00001111 <= Deptid <= 00001111"
                      ] /* ranges */
                    } /* range_access_plan */,
                    "rows_for_plan": 1,
                    "cost_for_plan": 2.21,
                    "chosen": true
                  } /* chosen_range_access_summary */
                } /* range_analysis */
              }
            ] /* rows_estimation */
          },
          {
            "considered_execution_plans": [
              {
                "plan_prefix": [
                ] /* plan_prefix */,
                "table": "`ppp`.`shop` `s`",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "access_type": "ref",
                      "index": "IND_SHOP_DEPTID",
                      "rows": 1,
                      "cost": 1.2,
                      "chosen": true
                    },
                    {
                      "access_type": "range",
                      "range_details": {
                        "used_index": "IND_SHOP_DEPTID"
                      } /* range_details */,
                      "chosen": false,
                      "cause": "heuristic_index_cheaper"
                    }
                  ] /* considered_access_paths */
                } /* best_access_path */,
                "condition_filtering_pct": 100,
                "rows_for_plan": 1,
                "cost_for_plan": 1.2,
                "rest_of_plan": [
                  {
                    "plan_prefix": [
                      "`ppp`.`shop` `s`"
                    ] /* plan_prefix */,
                    "table": "`SSS`.`department` `d`",
                    "best_access_path": {
                      "considered_access_paths": [
                        {
                          "access_type": "eq_ref",
                          "index": "PRIMARY",
                          "rows": 1,
                          "cost": 1.2,
                          "chosen": true,
                          "cause": "clustered_pk_chosen_by_heuristics"
                        },
                        {
                          "access_type": "scan",
                          "cost": 189584,
                          "rows": 911858,
                          "chosen": false,
                          "cause": "cost"
                        }
                      ] /* considered_access_paths */
                    } /* best_access_path */,
                    "added_to_eq_ref_extension": true,
                    "condition_filtering_pct": 100,
                    "rows_for_plan": 1,
                    "cost_for_plan": 2.4,
                    "chosen": true
                  }
                ] /* rest_of_plan */
              }
            ] /* considered_execution_plans */


至此,仍然无法搞清楚为何上一条sql当优化器一开始对优化器进行评估时就直接选择了对s表全表扫描的方式。后面将继续分析。


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

请登录后发表评论 登录
全部评论
不乱于心,不困于情。 不畏将来,不念过往。

注册时间:2014-09-09

  • 博文量
    129
  • 访问量
    937448