mysql开启优化器追踪

最新推荐文章于 2024-03-10 14:53:42 发布

HealCool沉浮

最新推荐文章于 2024-03-10 14:53:42 发布

阅读量573

点赞数

本文链接：https://blog.csdn.net/qq_32527449/article/details/115602136

版权

开启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,pppTel,ppp.s.IntroduceASIntroduce,ppp.s.IndustryASIndustry,ppp.s.AddressASAddress,ppp.s.Shop360imageASShop360image,ppp.s.DomainASDomain,ppp.s.OrganidASOrganid,ppp.s.DeptidASDeptid,ppp.s.BrandidsASBrandids,ppp.s.ExtdataASExtdata,ppp.s.RankingASRanking,ppp.s.IsdeleteASIsdelete,ppp.s.DistrictASDistrict,ppp.s.CityASCity,ppp.s.ProvinceASProvince,ppp.s.PhoneASPhone,ppp.s.WatermarkimageASWatermarkimage,ppp.s.DrawingimageASDrawingimage,ppp.s.ContactuserASContactuser,ppp.s.PanoloadingimageASPanoloadingimage,ppp.s.LngandlatASLngandlat,ppp.s.CreatetimeASCreatetime,ppp.s.ShoptypeASShoptype,ppp.s.StatusASStatus,ppp.s.StylecategoryidASStylecategoryid,ppp.s.TurnontimeASTurnontimefromSSS.departmentdleft joinppp.shopson((SSS.d.Deptid= convert(ppp.s.Deptidusing 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表看起来做了驱动表。

追踪优化器过程：

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表全表扫描的方式。后面将继续分析。

转字：http://blog.itpub.net/29863023/viewspace-2565095/，

HealCool沉浮

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
mysql开启优化器追踪

以下 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’;±—±------------±------±-----------±------±-------
复制链接

扫一扫