(因为打赏账号，所以作者署名必须是 carol11, 实际作者还是 Austin Liu)
——————————————————————————————
有的时候理解平时习而为常的一件事情，到时有很多的意外的发现，今天就从一条MYSQL的查询语句入手，看看我们还能挖掘点什么？
首先一个SQL 语句，会经历二个阶段， 1 解析， 2 执行计划生成
select * from employees as e
left join salaries as s on e.emp_no = s.emp_no
where e.emp_no = '10009' order by s.to_date limit 1;
看似没有什么，来我们看一下解析后的这条语句的执行过程（想看的请移到文章末尾，太长了）
估计看到的都觉得，怎么这个处理的过程这么长。是的，就是我们平时不觉得的一条普通的语句，其实经理一个“漫长的”过程，在能提取结果。
那我们来一段段的看，到底发生了什么。并且可以让我们理解那些 “文章” 中告诉我们的SQL 语句执行的顺序到底对不对。
1 Join_preparation, 是的一条语句如果你要做JOIN 的操作，从内部SQL的解析来看，是这样的，他的第一步就是做 JOIN
顺便说一句，那些写JOIN SQL的语句的 人er们，请别用*了，你看你写*是方便了，SQL 第一步就会将你的这些 * 解析为每个字段，用那个就写那个，并且标清楚你要访问那个表的字段，这样是对解析是很有好处的。
在做完了，transformations_to_nested_joins 后，我们看看语句又到了哪一步, where 条件，这里显示的是我们的where条件是一个等值的并且是固定的方式进行的查询， 这其中包含，等值优化，常量优化，细节条件排查
下面标志清楚JOIN 的字段之间的关联性， 并且很清楚的标志清楚依赖的两个表，并且也告知了 JOIN 的方式 NESTED_LOOP 的方式，从 employees 中选一条，与salaries 的所有记录进行一对一的比对。salaries 有 2835359 条记录，并且走的是 salaries 表的主键，（emp_no from_date）
下面就开始展示计划了，从下面的信息中，我们语句判断查询的方式走主键，并且走EQ 方式，不使用 MYSQL的 mrr （不知道什么是MRR 的可以百度一下）
rows 中显示过滤后的行数，以及cost。
选择访问的范围
ORDER BY
而通过下面的截图我们可以看到，做一个join 要包含创建一个 tmp_table,位置在内存中，并且给这块的内存的行评估是要放置 1118481行数据
后面我们还有一个order by要处理，通过 filesort 的方式，升序，并且也使用刚才执行计划生成的内存 TMP_TABLE
以上的信息获得是通过 MYSQL optimizer_trace 功能来获取的，具体的获取方式如下，（由于这样操作会消耗系统性能，强烈不建议默认开启，并且在生产系统上禁用，仅仅为分析问题使用）
打开优化trace
SET optimizer_trace="enabled=on";
查看获取记录
SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
关闭trace
SET optimizer_trace="enabled=off";
——————————————————————————
| select * from employees as e left join salaries as s on e.emp_no = s.emp_no where e.emp_no = '10009' order by s.to_date limit 1 | {
"steps": [
{
"join_preparation": {
"select#": 1,
"steps": [
{
"expanded_query": "/* select#1 */ select `e`.`emp_no` AS `emp_no`,`e`.`birth_date` AS `birth_date`,`e`.`first_name` AS `first_name`,`e`.`last_name` AS `last_name`,`e`.`gender` AS `gender`,`e`.`hire_date` AS `hire_date`,`s`.`emp_no` AS `emp_no`,`s`.`salary` AS `salary`,`s`.`from_date` AS `from_date`,`s`.`to_date` AS `to_date` from (`employees` `e` left join `salaries` `s` on((`e`.`emp_no` = `s`.`emp_no`))) where (`e`.`emp_no` = '10009') order by `s`.`to_date` limit 1"
},
{
"transformations_to_nested_joins": {
"transformations": [
"parenthesis_removal"
],
"expanded_query": "/* select#1 */ select `e`.`emp_no` AS `emp_no`,`e`.`birth_date` AS `birth_date`,`e`.`first_name` AS `first_name`,`e`.`last_name` AS `last_name`,`e`.`gender` AS `gender`,`e`.`hire_date` AS `hire_date`,`s`.`emp_no` AS `emp_no`,`s`.`salary` AS `salary`,`s`.`from_date` AS `from_date`,`s`.`to_date` AS `to_date` from `employees` `e` left join `salaries` `s` on((`e`.`emp_no` = `s`.`emp_no`)) where (`e`.`emp_no` = '10009') order by `s`.`to_date` limit 1"
}
}
]
}
},
{
"join_optimization": {
"select#": 1,
"steps": [
{
"condition_processing": {
"condition": "WHERE",
"original_condition": "(`e`.`emp_no` = '10009')",
"steps": [
{
"transformation": "equality_propagation",
"resulting_condition": "(`e`.`emp_no` = '10009')"
},
{
"transformation": "constant_propagation",
"resulting_condition": "(`e`.`emp_no` = '10009')"
},
{
"transformation": "trivial_condition_removal",
"resulting_condition": "(`e`.`emp_no` = '10009')"
}
]
}
},
{
"substitute_generated_columns": {
}
},
{
"table_dependencies": [
{
"table": "`employees` `e`",
"row_may_be_null": false,
"map_bit": 0,
"depends_on_map_bits": [
]
},
{
"table": "`salaries` `s`",
"row_may_be_null": true,
"map_bit": 1,
"depends_on_map_bits": [
0
]
}
]
},
{
"ref_optimizer_key_uses": [
{
"table": "`employees` `e`",
"field": "emp_no",
"equals": "'10009'",
"null_rejecting": false
},
{
"table": "`salaries` `s`",
"field": "emp_no",
"equals": "`e`.`emp_no`",
"null_rejecting": false
}
]
},
{
"rows_estimation": [
{
"table": "`employees` `e`",
"rows": 1,
"cost": 1,
"table_type": "const",
"empty": false
},
{
"table": "`salaries` `s`",
"range_analysis": {
"table_scan": {
"rows": 2835359,
"cost": 574517
},
"potential_range_indexes": [
{
"index": "PRIMARY",
"usable": true,
"key_parts": [
"emp_no",
"from_date"
]
}
],
"setup_range_conditions": [
],
"group_index_range": {
"chosen": false,
"cause": "not_single_table"
},
"analyzing_range_alternatives": {
"range_scan_alternatives": [
{
"index": "PRIMARY",
"ranges": [
"10009 <= emp_no <= 10009"
],
"index_dives_for_eq_ranges": true,
"rowid_ordered": true,
"using_mrr": false,
"index_only": false,
"rows": 18,
"cost": 4.6314,
"chosen": true
}
],
"analyzing_roworder_intersect": {
"usable": false,
"cause": "too_few_roworder_scans"
}
},
"chosen_range_access_summary": {
"range_access_plan": {
"type": "range_scan",
"index": "PRIMARY",
"rows": 18,
"ranges": [
"10009 <= emp_no <= 10009"
]
},
"rows_for_plan": 18,
"cost_for_plan": 4.6314,
"chosen": true
}
}
}
]
},
{
"considered_execution_plans": [
{
"plan_prefix": [
"`employees` `e`"
],
"table": "`salaries` `s`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "PRIMARY",
"rows": 18,
"cost": 4.6214,
"chosen": true
},
{
"access_type": "range",
"range_details": {
"used_index": "PRIMARY"
},
"chosen": false,
"cause": "heuristic_index_cheaper"
}
]
},
"condition_filtering_pct": 100,
"rows_for_plan": 18,
"cost_for_plan": 4.6214,
"chosen": true
}
]
},
{
"condition_on_constant_tables": "('10009' = '10009')",
"condition_value": true
},
{
"attaching_conditions_to_tables": {
"original_condition": "('10009' = '10009')",
"attached_conditions_computation": [
],
"attached_conditions_summary": [
{
"table": "`salaries` `s`",
"attached": null
}
]
}
},
{
"clause_processing": {
"clause": "ORDER BY",
"original_clause": "`s`.`to_date`",
"items": [
{
"item": "`s`.`to_date`"
}
],
"resulting_clause_is_simple": false,
"resulting_clause": "`s`.`to_date`"
}
},
{
"refine_plan": [
{
"table": "`salaries` `s`"
}
]
}
]
}
},
{
"join_execution": {
"select#": 1,
"steps": [
{
"creating_tmp_table": {
"tmp_table_info": {
"table": "intermediate_tmp_table",
"row_length": 15,
"key_length": 0,
"unique_constraint": false,
"location": "memory (heap)",
"row_limit_estimate": 1118481
}
}
},
{
"filesort_information": [
{
"direction": "asc",
"table": "intermediate_tmp_table",
"field": "to_date"
}
],
"filesort_priority_queue_optimization": {
"limit": 1,
"rows_estimate": 28,
"row_size": 12,
"memory_available": 8388608,
"chosen": true
},
"filesort_execution": [
],
"filesort_summary": {
"rows": 2,
"examined_rows": 18,
"number_of_tmp_files": 0,
"sort_buffer_size": 40,
"sort_mode": "<sort_key, rowid>"
}
}
]
}
}
]
}
