01、目标
了解查询优化器
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-hjxLR4OV-1629391403875)(查询计划.assets/image-20210723212750676.png)]
02、查询优化器
查询优化器的作用：就是根据你执行的SQL语句，进行分析，它自动的去通过他里面提供的算法和结构。进行对你的sql执行和预判。选择一个最优的方法，进行一个执行和处理。
能不能通过一些方式，来查看SQL在执行执行过程中到底是怎么确定的执行的顺序，和是否命中到了索引呢？
03：分析
查询优化器的任务是找到执行SQL查询的最佳计划，是MYSQL数据库的一个核心功能模块。
根据表、列、索引的详细信息以及SQL语句中的条件，很多方面来让SQL高效的执行。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-DITGGYib-1629391403878)(查询计划.assets/kuangstudyd9bd04ec-bfd0-4327-a5c4-b2ff1cd2494b.png)]
04、执行计划原理分析
优化器选择执行效率最高的查询的一组操作称之为：查询执行计划，也称之为Explain计划。分析Explain计划可以发现一些导致SQL低下的原因
参考官网：https://dev.mysql.com/doc/internals/en/optimizer-tracing-typical-usage.html
比如有 一个SQL语句：
SELECT * FROM kss_user WHERE userid > 5 AND email='xuchengfeifei@163.com';
这条SQL语句在MYSQL内部的优化器中，到底是先执行userid还是先过滤email呢？
这个的话完全由MYSQL的优化器来决定。我们开发者不要理所当然的先执行userid在执行email。这是错误的认知。
05、面向场景分析：重点：记住下来
面试场景1分析：select * from table where a = 1 and b = 2 and c= 3 问。a,b,c先执行谁？
如果你回答：a,b,c / b,c,a 可能都是错的也可能都是对的？飞哥不是很矛盾吗？
答案：你写的这个SQL语句，我不能直接告诉你的他们之间的执行顺序，必须要通过查询计划才能够确定他们的执行顺序。完全由MYSQL的优化器来决定。我们开发者不要理所当然的先执行a在执行b执行c。这是错误的认知。
举个列子
SELECT * FROM kss_user WHERE userid > 5 AND email='xuchengfeifei@163.com';
开启sql执行的查询计划的分析
SET optimizer_trace="enabled=on";
SELECT * FROM kss_user WHERE userid > 5 AND email='xuchengfeifei@163.com';
SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
SET optimizer_trace="enabled=off";
的到结果
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-AjjXwh4A-1629391403881)(查询计划.assets/image-20210723214703107.png)]
{
"steps": [
{
"join_preparation": {
"select#": 1,
"steps": [
{
"expanded_query": "/* select#1 */ select `kss_user`.`id` AS `id`,`kss_user`.`nickname` AS `nickname`,`kss_user`.`password` AS `password`,`kss_user`.`age` AS `age`,`kss_user`.`male` AS `male`,`kss_user`.`user_intro` AS `user_intro`,`kss_user`.`create_time` AS `create_time`,`kss_user`.`update_time` AS `update_time`,`kss_user`.`active` AS `active`,`kss_user`.`update_count` AS `update_count`,`kss_user`.`version` AS `version` from `kss_user` where ((`kss_user`.`id` > 5) and (`kss_user`.`nickname` = 'xuchengfeifei@163.com'))"
}
]
}
},
{
"join_optimization": {
"select#": 1,
"steps": [
{
"condition_processing": {
"condition": "WHERE",
"original_condition": "((`kss_user`.`id` > 5) and (`kss_user`.`nickname` = 'xuchengfeifei@163.com'))",
"steps": [
{
"transformation": "equality_propagation",
"resulting_condition": "((`kss_user`.`id` > 5) and (`kss_user`.`nickname` = 'xuchengfeifei@163.com'))"
},
{
"transformation": "constant_propagation",
"resulting_condition": "((`kss_user`.`id` > 5) and (`kss_user`.`nickname` = 'xuchengfeifei@163.com'))"
},
{
"transformation": "trivial_condition_removal",
"resulting_condition": "((`kss_user`.`id` > 5) and (`kss_user`.`nickname` = 'xuchengfeifei@163.com'))"
}
]
}
},
{
"substitute_generated_columns": {
}
},
{
"table_dependencies": [
{
"table": "`kss_user`",
"row_may_be_null": false,
"map_bit": 0,
"depends_on_map_bits": [
]
}
]
},
{
"ref_optimizer_key_uses": [
{
"table": "`kss_user`",
"field": "nickname",
"equals": "'xuchengfeifei@163.com'",
"null_rejecting": false
}
]
},
{
"rows_estimation": [
{
"table": "`kss_user`",
"range_analysis": {
"table_scan": {
"rows": 36,
"cost": 10.3
},
"potential_range_indexes": [
{
"index": "PRIMARY",
"usable": true,
"key_parts": [
"id"
]
},
{
"index": "nickname_age_male_index",
"usable": true,
"key_parts": [
"nickname",
"age",
"male",
"id"
]
}
],
"setup_range_conditions": [
],
"group_index_range": {
"chosen": false,
"cause": "not_group_by_or_distinct"
},
"analyzing_range_alternatives": {
"range_scan_alternatives": [
{
"index": "PRIMARY",
"ranges": [
"5 < id"
],
"index_dives_for_eq_ranges": true,
"rowid_ordered": true,
"using_mrr": false,
"index_only": false,
"rows": 31,
"cost": 7.2668,
"chosen": true
},
{
"index": "nickname_age_male_index",
"ranges": [
"xuchengfeifei@163.com <= nickname <= xuchengfeifei@163.com"
],
"index_dives_for_eq_ranges": true,
"rowid_ordered": false,
"using_mrr": false,
"index_only": false,
"rows": 1,
"cost": 2.21,
"chosen": true
}
],
"analyzing_roworder_intersect": {
"usable": false,
"cause": "too_few_roworder_scans"
}
},
"chosen_range_access_summary": {
"range_access_plan": {
"type": "range_scan",
"index": "nickname_age_male_index",
"rows": 1,
"ranges": [
"xuchengfeifei@163.com <= nickname <= xuchengfeifei@163.com"
]
},
"rows_for_plan": 1,
"cost_for_plan": 2.21,
"chosen": true
}
}
}
]
},
{
"considered_execution_plans": [
{
"plan_prefix": [
],
"table": "`kss_user`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "nickname_age_male_index",
"rows": 1,
"cost": 1.2,
"chosen": true
},
{
"access_type": "range",
"range_details": {
"used_index": "nickname_age_male_index"
},
"chosen": false,
"cause": "heuristic_index_cheaper"
}
]
},
"condition_filtering_pct": 100,
"rows_for_plan": 1,
"cost_for_plan": 1.2,
"chosen": true
}
]
},
{
"attaching_conditions_to_tables": {
"original_condition": "((`kss_user`.`id` > 5) and (`kss_user`.`nickname` = 'xuchengfeifei@163.com'))",
"attached_conditions_computation": [
],
"attached_conditions_summary": [
{
"table": "`kss_user`",
"attached": "(`kss_user`.`id` > 5)"
}
]
}
},
{
"refine_plan": [
{
"table": "`kss_user`",
"pushed_index_condition": "(`kss_user`.`id` > 5)",
"table_condition_attached": null
}
]
}
]
}
},
{
"join_execution": {
"select#": 1,
"steps": [
]
}
}
]
}
面试题场景2分析：SELECT * from employee e,department d,customer c where e.dep_id = d.id and e.cus_id = c.id;
如果你回答：e,d,c /d,c,e 可能都是错的也可能都是对的？飞哥不是很矛盾吗？
答案：你写的这个SQL语句，我不能直接告诉你的他们之间的执行顺序，必须要通过查询计划或者执行Expain执行分析才能够确定他们的执行顺序。完全由MYSQL的优化器来决定。我们开发者不要理所当然的先执行e在执行d执行c。这是错误的认知。
{
"steps": [
{
"join_preparation": {
"select#": 1,
"steps": [
{
"expanded_query": "/* select#1 */ select `a`.`id` AS `id`,`a`.`name` AS `name`,`a`.`dep_id` AS `dep_id`,`a`.`age` AS `age`,`a`.`salary` AS `salary`,`a`.`cus_id` AS `cus_id`,`b`.`id` AS `id`,`b`.`deptName` AS `deptName`,`b`.`address` AS `address`,`c`.`id` AS `id`,`c`.`name` AS `name` from `employee` `a` join `department` `b` join `customer` `c` where ((`a`.`dep_id` = `b`.`id`) and (`a`.`cus_id` = `c`.`id`))"
}
]
}
},
{
"join_optimization": {
"select#": 1,
"steps": [
{
"condition_processing": {
"condition": "WHERE",
"original_condition": "((`a`.`dep_id` = `b`.`id`) and (`a`.`cus_id` = `c`.`id`))",
"steps": [
{
"transformation": "equality_propagation",
"resulting_condition": "(multiple equal(`a`.`dep_id`, `b`.`id`) and multiple equal(`a`.`cus_id`, `c`.`id`))"
},
{
"transformation": "constant_propagation",
"resulting_condition": "(multiple equal(`a`.`dep_id`, `b`.`id`) and multiple equal(`a`.`cus_id`, `c`.`id`))"
},
{
"transformation": "trivial_condition_removal",
"resulting_condition": "(multiple equal(`a`.`dep_id`, `b`.`id`) and multiple equal(`a`.`cus_id`, `c`.`id`))"
}
]
}
},
{
"substitute_generated_columns": {
}
},
{
"table_dependencies": [
{
"table": "`employee` `a`",
"row_may_be_null": false,
"map_bit": 0,
"depends_on_map_bits": [
]
},
{
"table": "`department` `b`",
"row_may_be_null": false,
"map_bit": 1,
"depends_on_map_bits": [
]
},
{
"table": "`customer` `c`",
"row_may_be_null": false,
"map_bit": 2,
"depends_on_map_bits": [
]
}
]
},
{
"ref_optimizer_key_uses": [
{
"table": "`department` `b`",
"field": "id",
"equals": "`a`.`dep_id`",
"null_rejecting": true
},
{
"table": "`customer` `c`",
"field": "id",
"equals": "`a`.`cus_id`",
"null_rejecting": true
}
]
},
{
"rows_estimation": [
{
"table": "`employee` `a`",
"table_scan": {
"rows": 8,
"cost": 1
}
},
{
"table": "`department` `b`",
"table_scan": {
"rows": 5,
"cost": 1
}
},
{
"table": "`customer` `c`",
"table_scan": {
"rows": 1,
"cost": 1
}
}
]
},
{
"considered_execution_plans": [
{
"plan_prefix": [
],
"table": "`employee` `a`",
"best_access_path": {
"considered_access_paths": [
{
"rows_to_scan": 8,
"access_type": "scan",
"resulting_rows": 8,
"cost": 2.6,
"chosen": true
}
]
},
"condition_filtering_pct": 100,
"rows_for_plan": 8,
"cost_for_plan": 2.6,
"rest_of_plan": [
{
"plan_prefix": [
"`employee` `a`"
],
"table": "`customer` `c`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "eq_ref",
"index": "PRIMARY",
"rows": 1,
"cost": 9.6,
"chosen": true,
"cause": "clustered_pk_chosen_by_heuristics"
},
{
"rows_to_scan": 1,
"access_type": "scan",
"using_join_cache": true,
"buffers_needed": 1,
"resulting_rows": 1,
"cost": 2.6025,
"chosen": true
}
]
},
"condition_filtering_pct": 100,
"rows_for_plan": 8,
"cost_for_plan": 5.2025,
"rest_of_plan": [
{
"plan_prefix": [
"`employee` `a`",
"`customer` `c`"
],
"table": "`department` `b`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "eq_ref",
"index": "PRIMARY",
"rows": 1,
"cost": 9.6,
"chosen": true,
"cause": "clustered_pk_chosen_by_heuristics"
},
{
"rows_to_scan": 5,
"access_type": "scan",
"using_join_cache": true,
"buffers_needed": 1,
"resulting_rows": 5,
"cost": 9.0261,
"chosen": true
}
]
},
"condition_filtering_pct": 100,
"rows_for_plan": 40,
"cost_for_plan": 14.229,
"chosen": true
}
]
},
{
"plan_prefix": [
"`employee` `a`"
],
"table": "`department` `b`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "eq_ref",
"index": "PRIMARY",
"rows": 1,
"cost": 9.6,
"chosen": true,
"cause": "clustered_pk_chosen_by_heuristics"
},
{
"rows_to_scan": 5,
"access_type": "scan",
"using_join_cache": true,
"buffers_needed": 1,
"resulting_rows": 5,
"cost": 9.0025,
"chosen": true
}
]
},
"condition_filtering_pct": 20,
"rows_for_plan": 8,
"cost_for_plan": 11.603,
"pruned_by_heuristic": true
}
]
},
{
"plan_prefix": [
],
"table": "`customer` `c`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "PRIMARY",
"usable": false,
"chosen": false
},
{
"rows_to_scan": 1,
"access_type": "scan",
"resulting_rows": 1,
"cost": 1.2,
"chosen": true
}
]
},
"condition_filtering_pct": 100,
"rows_for_plan": 1,
"cost_for_plan": 1.2,
"rest_of_plan": [
{
"plan_prefix": [
"`customer` `c`"
],
"table": "`employee` `a`",
"best_access_path": {
"considered_access_paths": [
{
"rows_to_scan": 8,
"access_type": "scan",
"using_join_cache": true,
"buffers_needed": 1,
"resulting_rows": 8,
"cost": 2.6029,
"chosen": true
}
]
},
"condition_filtering_pct": 12.5,
"rows_for_plan": 1,
"cost_for_plan": 3.8029,
"rest_of_plan": [
{
"plan_prefix": [
"`customer` `c`",
"`employee` `a`"
],
"table": "`department` `b`",
"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": 2,
"rows": 5,
"chosen": false,
"cause": "cost"
}
]
},
"condition_filtering_pct": 100,
"rows_for_plan": 1,
"cost_for_plan": 5.0029,
"chosen": true
}
]
},
{
"plan_prefix": [
"`customer` `c`"
],
"table": "`department` `b`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "PRIMARY",
"usable": false,
"chosen": false
},
{
"rows_to_scan": 5,
"access_type": "scan",
"using_join_cache": true,
"buffers_needed": 1,
"resulting_rows": 5,
"cost": 2.0029,
"chosen": true
}
]
},
"condition_filtering_pct": 100,
"rows_for_plan": 5,
"cost_for_plan": 3.2029,
"rest_of_plan": [
{
"plan_prefix": [
"`customer` `c`",
"`department` `b`"
],
"table": "`employee` `a`",
"best_access_path": {
"considered_access_paths": [
{
"rows_to_scan": 8,
"access_type": "scan",
"using_join_cache": true,
"buffers_needed": 1,
"resulting_rows": 8,
"cost": 9.0189,
"chosen": true
}
]
},
"condition_filtering_pct": 100,
"rows_for_plan": 40,
"cost_for_plan": 12.222,
"pruned_by_cost": true
}
]
}
]
},
{
"plan_prefix": [
],
"table": "`department` `b`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "PRIMARY",
"usable": false,
"chosen": false
},
{
"rows_to_scan": 5,
"access_type": "scan",
"resulting_rows": 5,
"cost": 2,
"chosen": true
}
]
},
"condition_filtering_pct": 100,
"rows_for_plan": 5,
"cost_for_plan": 2,
"pruned_by_heuristic": true
}
]
},
{
"attaching_conditions_to_tables": {
"original_condition": "((`a`.`cus_id` = `c`.`id`) and (`b`.`id` = `a`.`dep_id`))",
"attached_conditions_computation": [
],
"attached_conditions_summary": [
{
"table": "`customer` `c`",
"attached": null
},
{
"table": "`employee` `a`",
"attached": "((`a`.`cus_id` = `c`.`id`) and (`a`.`dep_id` is not null))"
},
{
"table": "`department` `b`",
"attached": null
}
]
}
},
{
"refine_plan": [
{
"table": "`customer` `c`"
},
{
"table": "`employee` `a`",
"unknown_key_1": {
"constant_condition_in_bnl": "(`a`.`dep_id` is not null)"
}
},
{
"table": "`department` `b`"
}
]
}
]
}
},
{
"join_execution": {
"select#": 1,
"steps": [
]
}
}
]
}
总结
- 覆盖索引（记住）
- 查询计划，就是告诉后续sql执行过程中的一个过程，json数据。
- 执行计划 explain，只不过是把查询计划的日志进行具象化的分析的过程
- 重点：以后在面试这种，多表查询现后顺序的时候，知道该回答了吗？
310
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
