mysql 优化器的执行过程与分析

开启 OPTIMIZER_TRACE

set optimizer_trace="enabled=on"; --开启trace查看优化器的结果

set end_markers_in_json=on; --增加注释

select id,name,city,monthsalary,gender from customers1s where city="长沙" and gender=0 and monthsalary>=99; --需要查看的查询语句

select * from information_schema.optimizer_trace; --查看结果

OPTIMIZER_TRACE结果

QUERY： 查询语句
TRACE：QUERY 字段对应语句的跟踪信息
MISSING_BYTES_BEYOND_MAX_MEM_SIZE：跟踪信息过长时，被截断的跟踪信息的字节数。
INSUFFICIENT_PRIVILEGES：执行跟踪语句的用户是否有查看对象的权限。当不具有权限时，该列信息为1且TRACE字段为空，一般在调用带有SQL SECURITY DEFINER的视图或者是存储过程的情况下， 会出现此问题。

{
  "steps": [
    {
      "join_preparation": {
        "select#": 1,
        "steps": [
          {
            "expanded_query": "/* select#1 */ select `customers1s`.`id` AS `id`,`customers1s`.`name` AS `name`,`customers1s`.`city` AS `city`,`customers1s`.`monthsalary` AS `monthsalary`,`customers1s`.`gender` AS `gender` from `customers1s` where ((`customers1s`.`city` = '长沙') and (`customers1s`.`gender` = 0) and (`customers1s`.`monthsalary` >= 99))"
          }
        ] /* steps */
      } /* join_preparation */
    },
    {
      "join_optimization": {
        "select#": 1,
        "steps": [
          {
            "condition_processing": {
              "condition": "WHERE",
              "original_condition": "((`customers1s`.`city` = '长沙') and (`customers1s`.`gender` = 0) and (`customers1s`.`monthsalary` >= 99))",
              "steps": [
                {
                  "transformation": "equality_propagation",
                  "resulting_condition": "((`customers1s`.`city` = '长沙') and (`customers1s`.`monthsalary` >= 99) and multiple equal(0, `customers1s`.`gender`))"
                },
                {
                  "transformation": "constant_propagation",
                  "resulting_condition": "((`customers1s`.`city` = '长沙') and (`customers1s`.`monthsalary` >= 99) and multiple equal(0, `customers1s`.`gender`))"
                },
                {
                  "transformation": "trivial_condition_removal",
                  "resulting_condition": "((`customers1s`.`city` = '长沙') and (`customers1s`.`monthsalary` >= 99.00) and multiple equal(0, `customers1s`.`gender`))"
                }
              ] /* steps */
            } /* condition_processing */
          },
          {
            "substitute_generated_columns": {
            } /* substitute_generated_columns */
          },
          {
            "table_dependencies": [
              {
                "table": "`customers1s`",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": [
                ] /* depends_on_map_bits */
              }
            ] /* table_dependencies */
          },
          {
            "ref_optimizer_key_uses": [
              {
                "table": "`customers1s`",
                "field": "gender",
                "equals": "0",
                "null_rejecting": false
              },
              {
                "table": "`customers1s`",
                "field": "city",
                "equals": "'长沙'",
                "null_rejecting": false
              }
            ] /* ref_optimizer_key_uses */
          },
          {
            "rows_estimation": [
              {
                "table": "`customers1s`",
                "range_analysis": {
                  "table_scan": {
                    "rows": 4877972,
                    "cost": 517170
                  } /* table_scan */,
                  "potential_range_indexes": [
                    {
                      "index": "PRIMARY",
                      "usable": false,
                      "cause": "not_applicable"
                    },
                    {
                      "index": "idx_gender_city_name_monthsalary_yearbonus",
                      "usable": true,
                      "key_parts": [
                        "gender",
                        "city",
                        "name",
                        "monthsalary",
                        "yearbonus",
                        "id"
                      ] /* key_parts */
                    },
                    {
                      "index": "idx_name_photo",
                      "usable": false,
                      "cause": "not_applicable"
                    }
                  ] /* potential_range_indexes */,
                  "best_covering_index_scan": {
                    "index": "idx_gender_city_name_monthsalary_yearbonus",
                    "cost": 581772,
                    "chosen": false,
                    "cause": "cost"
                  } /* best_covering_index_scan */,
                  "setup_range_conditions": [
                  ] /* setup_range_conditions */,
                  "group_index_range": {
                    "chosen": false,
                    "cause": "not_group_by_or_distinct"
                  } /* group_index_range */,
                  "skip_scan_range": {
                    "potential_skip_scan_indexes": [
                      {
                        "index": "idx_gender_city_name_monthsalary_yearbonus",
                        "tree_travel_cost": 1.15,
                        "num_groups": 330120,
                        "rows": 474476,
                        "cost": 839589
                      }
                    ] /* potential_skip_scan_indexes */
                  } /* skip_scan_range */,
                  "best_skip_scan_summary": {
                    "type": "skip_scan",
                    "index": "idx_gender_city_name_monthsalary_yearbonus",
                    "key_parts_used_for_access": [
                      "gender",
                      "city",
                      "name",
                      "monthsalary"
                    ] /* key_parts_used_for_access */,
                    "prefix ranges": [
                      "0 <= gender <= 0 AND 长沙 <= city <= 长沙"
                    ] /* prefix ranges */,
                    "range": [
                      "99.00 <= monthsalary"
                    ] /* range */,
                    "chosen": false,
                    "cause": "cost"
                  } /* best_skip_scan_summary */,
                  "analyzing_range_alternatives": {
                    "range_scan_alternatives": [
                      {
                        "index": "idx_gender_city_name_monthsalary_yearbonus",
                        "ranges": [
                          "0 <= gender <= 0 AND 长沙 <= city <= 长沙"
                        ] /* ranges */,
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": false,
                        "using_mrr": false,
                        "index_only": true,
                        "rows": 474476,
                        "cost": 56589,
                        "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": "idx_gender_city_name_monthsalary_yearbonus",
                      "rows": 474476,
                      "ranges": [
                        "0 <= gender <= 0 AND 长沙 <= city <= 长沙"
                      ] /* ranges */
                    } /* range_access_plan */,
                    "rows_for_plan": 474476,
                    "cost_for_plan": 56589,
                    "chosen": true
                  } /* chosen_range_access_summary */
                } /* range_analysis */
              }
            ] /* rows_estimation */
          },
          {
            "considered_execution_plans": [
              {
                "plan_prefix": [
                ] /* plan_prefix */,
                "table": "`customers1s`",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "access_type": "ref",
                      "index": "idx_gender_city_name_monthsalary_yearbonus",
                      "rows": 474476,
                      "cost": 56589,
                      "chosen": true
                    },
                    {
                      "access_type": "range",
                      "range_details": {
                        "used_index": "idx_gender_city_name_monthsalary_yearbonus"
                      } /* range_details */,
                      "chosen": false,
                      "cause": "heuristic_index_cheaper"
                    }
                  ] /* considered_access_paths */
                } /* best_access_path */,
                "condition_filtering_pct": 100,
                "rows_for_plan": 474476,
                "cost_for_plan": 56589,
                "chosen": true
              }
            ] /* considered_execution_plans */
          },
          {
            "attaching_conditions_to_tables": {
              "original_condition": "((`customers1s`.`gender` = 0) and (`customers1s`.`city` = '长沙') and (`customers1s`.`monthsalary` >= 99.00))",
              "attached_conditions_computation": [
              ] /* attached_conditions_computation */,
              "attached_conditions_summary": [
                {
                  "table": "`customers1s`",
                  "attached": "((`customers1s`.`gender` = 0) and (`customers1s`.`city` = '长沙') and (`customers1s`.`monthsalary` >= 99.00))"
                }
              ] /* attached_conditions_summary */
            } /* attaching_conditions_to_tables */
          },
          {
            "finalizing_table_conditions": [
              {
                "table": "`customers1s`",
                "original_table_condition": "((`customers1s`.`gender` = 0) and (`customers1s`.`city` = '长沙') and (`customers1s`.`monthsalary` >= 99.00))",
                "final_table_condition   ": "(`customers1s`.`monthsalary` >= 99.00)"
              }
            ] /* finalizing_table_conditions */
          },
          {
            "refine_plan": [
              {
                "table": "`customers1s`"
              }
            ] /* refine_plan */
          }
        ] /* steps */
      } /* join_optimization */
    },
    {
      "join_execution": {
        "select#": 1,
        "steps": [
        ] /* steps */
      } /* join_execution */
    }
  ] /* steps */
}

分析结果：

join_preparation

join_preparation段落展示了准备阶段的执行过程。

{
	"join_preparation": {
	"select#": 1,
	"steps": [
	{
	-- 对比下原始语句，可以知道，这一步做了个格式化。
		`emp_no`,`salaries`.`salary` AS `salary`,`salaries`.`from_date` AS `from_date`,`salaries`.`to_date` AS `to_date` from `salaries` where ((`salaries`.`from_date` = '1986-06-26') and (`salaries`.`to_date` = '1987-06-26'))"
	}
	]
	/* steps */
	}
	/* join_preparation */
}

join_optimization join_optimization展示了优化阶段的执行过程，是分析OPTIMIZER TRACE的
重点。这段内容超级长，而且分了好多步骤，不妨按照步骤逐段分析：

condition_processing 该段用来做条件处理，主要对WHERE条件进行优化处理。

"condition_processing": {
"condition": "WHERE",
"original_condition": "((`salaries`.`from_date` = '1986-06-26') and (`salaries`.`to_date`
= '1987-06-26'))",
"steps": [
{
"transformation": "equality_propagation",
"resulting_condition": "(multiple equal('1986-06-26', `salaries`.`from_date`) and
multiple equal('1987-06-26', `salaries`.`to_date`))"
},
{
"transformation": "constant_propagation",
"resulting_condition": "(multiple equal('1986-06-26', `salaries`.`from_date`) and
multiple equal('1987-06-26', `salaries`.`to_date`))"
},
{
"transformation": "trivial_condition_removal",
"resulting_condition": "(multiple equal(DATE'1986-06-26', `salaries`.`from_date`) and
multiple equal(DATE'1987-06-26', `salaries`.`to_date`))"
}
] /* steps */
} /* condition_processing */

其中：

condition：优化对象类型。WHERE条件句或者是HAVING条件句
original_condition：优化前的原始语句
steps：主要包括三步，分别是quality_propagation（等值条件句转换），
constant_propagation（常量条件句转换），trivial_condition_removal（无效条件移除的转换）
transformation：转换类型句 resulting_condition：转换之后的结果输出

substitute_generated_columns substitute_generated_columns用于替换虚拟生成列

"substitute_generated_columns": {
} /* substitute_generated_columns */

table_dependencies 分析表之间的依赖关系

{
	"table_dependencies": [
	{
		"table": "`salaries`",
		"row_may_be_null": false,
		"map_bit": 0,
		"depends_on_map_bits": [
		] /* depends_on_map_bits */
	}
	] /* table_dependencies */
}

其中：

table：涉及的表名，如果有别名，也会展示出来
row_may_be_null：行是否可能为NULL，这里是指JOIN操作之后，这张表里的数据是不是可能为
NULL。如果语句中使用了LEFT JOIN，则后一张表的row_may_be_null会显示为true
map_bit：表的映射编号，从0开始递增
depends_on_map_bits：依赖的映射表。主要是当使用STRAIGHT_JOIN强行控制连接顺序或者LEFT
JOIN/RIGHT JOIN有顺序差别时，会在depends_on_map_bits中展示前置表的map_bit值。

ref_optimizer_key_uses 列出所有可用的ref类型的索引。如果使用了组合索引的多个部分（例如本例，用到了index(from_date, to_date) 的多列索引），则会在ref_optimizer_key_uses下列 出多个元素，每个元素中会列出ref使用的索引及对应值。

{
	"ref_optimizer_key_uses": [
	{
		"table": "`salaries`",
		"field": "from_date",
		"equals": "DATE'1986-06-26'",
		"null_rejecting": false
	},
	{
		"table": "`salaries`",
		"field": "to_date",
		"equals": "DATE'1987-06-26'",
		"null_rejecting": false
	}
	] /* ref_optimizer_key_uses */
}

rows_estimation 顾名思义，用于估算需要扫描的记录数。

{
	"rows_estimation": [
	{
		"table": "`salaries`",
		"range_analysis": {
			"table_scan": {
			"rows": 2838216,
			"cost": 286799
			} /* table_scan */,
			"potential_range_indexes": [
			{
				"index": "PRIMARY",
				"usable": false,
				"cause": "not_applicable"
			},
			{
				"index": "salaries_from_date_to_date_index",
				"usable": true,
				"key_parts": [
				"from_date",
				"to_date",
				"emp_no"
				] /* key_parts */
			}
		] /* potential_range_indexes */,
		"setup_range_conditions": [
		] /* setup_range_conditions */,
		"group_index_range": {
			"chosen": false,
			"cause": "not_group_by_or_distinct"
		} /* group_index_range */,
		"skip_scan_range": {
		"potential_skip_scan_indexes": [
	{
"index": "salaries_from_date_to_date_index",
"usable": false,
"cause": "query_references_nonkey_column"
}
] /* potential_skip_scan_indexes */
} /* skip_scan_range */,
"analyzing_range_alternatives": {
"range_scan_alternatives": [
{
"index": "salaries_from_date_to_date_index",
"ranges": [
"0xda840f <= from_date <= 0xda840f AND 0xda860f <= to_date <= 0xda860f"
] /* ranges */,
"index_dives_for_eq_ranges": true,
"rowid_ordered": true,
"using_mrr": false,
"index_only": false,
"rows": 86,
"cost": 50.909,
"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": "salaries_from_date_to_date_index",
"rows": 86,
"ranges": [
"0xda840f <= from_date <= 0xda840f AND 0xda860f <= to_date <= 0xda860f"
] /* ranges */
} /* range_access_plan */,
"rows_for_plan": 86,
"cost_for_plan": 50.909,
"chosen": true
} /* chosen_range_access_summary */
} /* range_analysis */
}
] /* rows_estimation */
}

其中：

table：表名
range_analysis：table_scan：如果全表扫描的话，需要扫描多少行（row，2838216），以及需要的代价（cost，286799）
potential_range_indexes：列出表中所有的索引并分析其是否可用。如果不可用的话，会列出不可用的原因是什么；如果可用会列出索引中可用的字段；
setup_range_conditions：如果有可下推的条件，则带条件考虑范围查询
group_index_range：当使用了GROUP BY或DISTINCT时，是否有合适的索引可用。当未使用GROUP
BY或DISTINCT时，会显示chosen=false,cause=not_group_by_or_distinct；如使用了GROUP BY或DISTINCT，但是多表查询时，会显示chosen=false，cause =not_single_table。其他情况下会尝试分析可用的索引（potential_group_range_indexes）并计算对应的扫描行数及其所需代价
skip_scan_range：是否使用了skip scan


TIPS skip_scan_range是MySQL 8.0的新特性，感兴趣的可详见https://blog.csdn.net/weixin_43970890/article/details/89494915


analyzing_range_alternatives：分析各个索引的使用成本
range_scan_alternatives：range扫描分析
index：索引名
ranges：range扫描的条件范围
index_dives_for_eq_ranges：是否使用了index dive，该值会被参数
eq_range_index_dive_limit变量值影响。
rowid_ordered：该range扫描的结果集是否根据PK值进行排序
using_mrr：是否使用了mrr
index_only：表示是否使用了覆盖索引
rows：扫描的行数
cost：索引的使用成本
chosen：表示是否使用了该索引
analyzing_roworder_intersect：分析是否使用了索引合并（index merge），如果未使用，会在cause中展示原因；如果使用了索引合并，会在该部分展示索引合并的代价。
chosen_range_access_summary：在前一个步骤中分析了各类索引使用的方法及代价，得出了一定的中间结果之后，在summary阶段汇总前一阶段的中间结果确认最后的方案


range_access_plan：range扫描最终选择的执行计划。
type：展示执行计划的type，如果使用了索引合并，则会显示index_roworder_intersect
index：索引名
rows：扫描的行数
ranges：range扫描的条件范围
rows_for_plan：该执行计划的扫描行数
cost_for_plan：该执行计划的执行代价
chosen：是否选择该执行计划

considered_execution_plans 负责对比各可行计划的开销，并选择相对最优的执行计划。

{
	"considered_execution_plans": [
	{
		"plan_prefix": [
		] /* plan_prefix */,
		"table": "`salaries`",
		"best_access_path": {
			"considered_access_paths": [
			{
				"access_type": "ref",
				"index": "salaries_from_date_to_date_index",
				"rows": 86,
				"cost": 50.412,
				"chosen": true
			},
			{
				"access_type": "range",
				"range_details": {
					"used_index": "salaries_from_date_to_date_index"
				} /* range_details */,
				"chosen": false,
				"cause": "heuristic_index_cheaper"
			}
			] /* considered_access_paths */
		} /* best_access_path */,
			"condition_filtering_pct": 100,
			"rows_for_plan": 86,
			"cost_for_plan": 50.412,
			"chosen": true
		}
	] /* considered_execution_plans */
}

其中：

plan_prefix：当前计划的前置执行计划。 table：涉及的表名，如果有别名，也会展示出来
best_access_path：通过对比considered_access_paths，选择一个最优的访问路径
considered_access_paths：当前考虑的访问路径
access_type：使用索引的方式，可参考explain中的type字段 index：索引 rows：行数 cost：开销
chosen：是否选用这种执行路径 condition_filtering_pct：类似于explain的filtered列，是一个估算值
rows_for_plan：执行计划最终的扫描行数，由considered_access_paths.rows X
condition_filtering_pct计算获得。
cost_for_plan：执行计划的代价，由considered_access_paths.cost相加获得
chosen：是否选择了该执行计划 attaching_conditions_to_tables
基于considered_execution_plans中选择的执行计划，改造原有where条件，并针对表增加适当的附加条件，以便于单表数据的筛选。

TIPS 这部分条件的增加主要是为了便于ICP（索引条件下推），但ICP是否开启并不影响这部 分内容的构造。
ICP参考文档：https://www.cnblogs.com/Terry-Wu/p/9273177.html

{
		"attaching_conditions_to_tables": {
			"original_condition": "((`salaries`.`to_date` = DATE'1987-06-26') and
			(`salaries`.`from_date` = DATE'1986-06-26'))",
			"attached_conditions_computation": [
			] /* attached_conditions_computation */,
			"attached_conditions_summary": [
			{
				"table": "`salaries`",
				"attached": "((`salaries`.`to_date` = DATE'1987-06-26') and (`salaries`.`from_date`
				= DATE'1986-06-26'))"
			}
		] /* attached_conditions_summary */
	} /* attaching_conditions_to_tables */
}

其中：

original_condition：原始的条件语句
attached_conditions_computation：使用启发式算法计算已使用的索引，如果已使用的索引的访问类型是ref，则计算用range能否使用组合索引中更多的列，如果可以，则用range的方式替换ref。
attached_conditions_summary：附加之后的情况汇总
table：表名
attached：附加的条件或原语句中能直接下推给单表筛选的条件。

finalizing_table_conditions 最终的、经过优化后的表条件。

{
	"finalizing_table_conditions": [
	{
		"table": "`salaries`",
		"original_table_condition": "((`salaries`.`to_date` = DATE'1987-06-26') and
		(`salaries`.`from_date` = DATE'1986-06-26'))",
		"final_table_condition ": null
	}
	] /* finalizing_table_conditions */
}

refine_plan 改善执行计划：

{
	"refine_plan": [
	{
		"table": "`salaries`"
	}
	] /* refine_plan */
}

其中：

table：表名及别名

join_execution join_execution段落展示了执行阶段的执行过程。

"join_execution": {
"select#": 1,
"steps": [
] /* steps */
}