sql层
- sql层接口接收sql语句
- 判断sql语句类型
- dml update select insert
- ddl alter
- rep主从相关
- 状态查询，参数查询 show status等
- 如果为query，判断是否开启了缓存，如果开启并命中，返回数据
- 没有命中缓存，使用解析器对sql解析，解析成令牌（词法分析和语法分析）
- 语法分析，根据令牌进行组装，组装成数据结构，生成抽象语法树
- 优化器，根据得到的解析树选择合适的执行计划
- 获取表结构(字段，字段类型，索引，存储位置)获取要查询表的信息，join的话就是多个表
- 根据解析树进行条件过滤，会过滤掉一些没有意义的查询如1=1，对常量表达式转化成常量，调整查询结构，分析索引信息
- 判断查询计划
- 如何执行性能是最优方案，方案并不一定正确
- 查询缓存，8.0开始逐步删除查询缓存，8.1正式删除，因为查询缓存存在问题
优化器
sql优化的原则
- sql优化主要是快速定位到查询的sql语句位置
- 优化包括增删改查，主要针对查询
优化器作用
- 确认查询表，多对多会选择合适的表做查询依赖
- 扫描查询字段，确定查询顺序，join等
- 重写where语句，去掉无意义的查询操作，更改限制条件，缩小查询范围
- 判断索引使用，索引是否覆盖了所有字段，索引是否在where语句上使用，group order上使用
- 多表下判断join类型，然后尝试简化子查询，合并视图
优化过程
{
"steps": [
{
"join_preparation": { --连接准备
"select#": 1, --join准备的第一步
"steps": [ --解析成编号，解析数据库和表
{
"expanded_query": "/* select#1 */ select `test1`.`id` AS `id`,`test1`.`k` AS `k`,`test1`.`c` AS `c`,`test1`.`pad` AS `pad`,`test2`.`id` AS `id`,`test2`.`k` AS `k`,`test2`.`c` AS `c`,`test2`.`pad` AS `pad` from `test1` join `test2` where ((`test1`.`id` = `test2`.`id`) and (`test1`.`id` > 4999900))"
}
]
}
},
{
"join_optimization": { --join优化
"select#": 1,
"steps": [
{
"condition_processing": { --where条件
"condition": "WHERE",
"original_condition": "((`test1`.`id` = `test2`.`id`) and (`test1`.`id` > 4999900))",
"steps": [ --优化的步骤
{
"transformation": "equality_propagation", --等值优化
"resulting_condition": "((`test1`.`id` > 4999900) and multiple equal(`test1`.`id`, `test2`.`id`))" --把test.id>4999900放到前面，test1.id=test2.id使用多等值连接
},
{
"transformation": "constant_propagation", --常量优化
"resulting_condition": "((`test1`.`id` > 4999900) and multiple equal(`test1`.`id`, `test2`.`id`))"
},
{
"transformation": "trivial_condition_removal", --琐碎的条件排除
"resulting_condition": "((`test1`.`id` > 4999900) and multiple equal(`test1`.`id`, `test2`.`id`))"
}
]
}
},
{
"table_dependencies": [ --表依赖
{
"table": "`test1`", --表名
"row_may_be_null": false, --是否有null值，flase是没有
"map_bit": 0,
"depends_on_map_bits": [
]
},
{
"table": "`test2`",
"row_may_be_null": false,
"map_bit": 1,
"depends_on_map_bits": [
]
}
]
},
{
"ref_optimizer_key_uses": [ --相关优化索引使用
{
"table": "`test1`",
"field": "id", --索引字段
"equals": "`test2`.`id`", --连接的等值字段
"null_rejecting": false
},
{
"table": "`test2`",
"field": "id",
"equals": "`test1`.`id`",
"null_rejecting": false
}
]
},
{
"rows_estimation": [ --行评估
{
"table": "`test1`",
"range_analysis": { --范围分析
"table_scan": {
"rows": 4804854, --4804854行数据
"cost": 1.03e6 --花费1.03e6
},
"potential_range_indices": [ --可能的范围指数
{
"index": "PRIMARY",
"usable": true, --可使用的索引
"key_parts": [
"id"
] --可使用的索引字段
},
{
"index": "k_1",
"usable": false, --不能使用的索引
"cause": "not_applicable" --不被应用
}
],
"setup_range_conditions": [ --设置范围条件
],
"group_index_range": { --组范围索引
"chosen": false, --不选择的
"cause": "not_single_table" --goup by不是一个表的，所以不选择
},
"analyzing_range_alternatives": { --分析每个索引做范围扫描的花费
"range_scan_alternatives": [ --范围扫描花费
{
"index": "PRIMARY",
"ranges": [
"4999900 < id"
],
"index_dives_for_eq_ranges": true, --索引驱动等值范围扫描
"rowid_ordered": true, --rowid是顺序的
"using_mrr": false, --不能使用mrr，因为是主键
"index_only": false,
"rows": 99, --过滤出来99行
"cost": 21.434, --花费21.434
"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": 99,
"ranges": [
"4999900 < id"
]
},
"rows_for_plan": 99,
"cost_for_plan": 21.434,
"chosen": true
}
}
},
{
"table": "`test2`",
"range_analysis": {
"table_scan": {
"rows": 4804854,
"cost": 1.03e6
},
"potential_range_indices": [
{
"index": "PRIMARY",
"usable": true,
"key_parts": [
"id"
]
},
{
"index": "k_2",
"usable": false,
"cause": "not_applicable"
}
],
"setup_range_conditions": [
],
"group_index_range": {
"chosen": false,
"cause": "not_single_table"
},
"analyzing_range_alternatives": {
"range_scan_alternatives": [
{
"index": "PRIMARY",
"ranges": [
"4999900 < id"
],
"index_dives_for_eq_ranges": true,
"rowid_ordered": true,
"using_mrr": false,
"index_only": false,
"rows": 99,
"cost": 21.433,
"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": 99,
"ranges": [
"4999900 < id"
]
},
"rows_for_plan": 99,
"cost_for_plan": 21.433,
"chosen": true
}
}
}
]
},
{
"considered_execution_plans": [ --决定执行计划
{
"plan_prefix": [ --计划前
],
"table": "`test1`", --test1表的执行计划
"best_access_path": { --最好的访问路径
"considered_access_paths": [ --决定的访问路径
{
"access_type": "ref", --访问类型是ref
"index": "PRIMARY", --使用的索引是主键
"usable": false,
"chosen": false
},
{
"access_type": "range",
"rows": 99,
"cost": 41.234,
"chosen": true
}
]
},
"cost_for_plan": 41.234,
"rows_for_plan": 99,
"rest_of_plan": [
{
"plan_prefix": [
"`test1`"
],
"table": "`test2`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "PRIMARY",
"rows": 1,
"cost": 99.2,
"chosen": true
},
{
"access_type": "range",
"cause": "heuristic_index_cheaper",
"chosen": false
}
]
},
"cost_for_plan": 160.03,
"rows_for_plan": 99,
"chosen": true
}
]
},
{
"plan_prefix": [
],
"table": "`test2`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "PRIMARY",
"usable": false,
"chosen": false
},
{
"access_type": "range",
"rows": 99,
"cost": 41.233,
"chosen": true
}
]
},
"cost_for_plan": 41.233,
"rows_for_plan": 99,
"rest_of_plan": [
{
"plan_prefix": [
"`test2`"
],
"table": "`test1`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "PRIMARY",
"rows": 1,
"cost": 99.2,
"chosen": true
},
{
"access_type": "range",
"cause": "heuristic_index_cheaper",
"chosen": false
}
]
},
"cost_for_plan": 160.03,
"rows_for_plan": 99,
"pruned_by_cost": true
}
]
}
]
},
{
"attaching_conditions_to_tables": {
"original_condition": "((`test2`.`id` = `test1`.`id`) and (`test1`.`id` > 4999900))",
"attached_conditions_computation": [
],
"attached_conditions_summary": [
{
"table": "`test1`",
"attached": "(`test1`.`id` > 4999900)"
},
{
"table": "`test2`",
"attached": null
}
]
}
},
{
"refine_plan": [
{
"table": "`test1`",
"access_type": "range"
},
{
"table": "`test2`"
}
]
}
]
}
},
{
"join_execution": {
"select#": 1,
"steps": [
]
}
}
]
}
网友评论