通过一条简单的SQL 来理解MYSQL的解析SQL的过程

(因为打赏账号，所以作者署名必须是 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>"

}

}

]

}

}

]

}