【7天玩转MySQL】华为云RDS for MySQL学习笔记 Day04
【7天玩转MySQL】华为云RDS for MySQL学习笔记 Day04
第四天的内容主要是：Select查询
目标：了解MySQL的查询语句的支持范围，以及如何获取查询语句的执行计划、一些基本查询语句的优化点和注意事项等。
下面进入正题：
一、查询基本概念
1、查询SQL结构
1.1 select查询结构
select查询语句结构主要如下所示：
重点注意事项：Select 查询SQL语句可以只包含部分成员
1.2 select查询结构实例
2、子查询
Derived table
子查询出现在 Target Table 中，比如：select * from (select sum(a) from t1) T
Scalar Operand
子查询出现在 select expr 中，比如： select (select 1) from t1
Comparison
子查询出现在 where 、 having 条件中，比如： select * from t1 where id IN (select id from t2)
关联子查询
子查询中引用了外部查询的字段，比如： select * from t1 where id IN (select id from t2 where t2.a=t1.b)
非关联子查询
子查询没有引用外部查询的字段
3、多表join
Inner Join
查询所有满足 join 条件的记录。
Outer Join
查询所有满足 join 条件的记录，且输出至少一张表的全部记录， Left/Right Join 。
Semi Join
查询是否有至少一条满足 join 条件的记录，对 IN 、 =ANY 、 EXISTS 子查询的一种优化手段。
Anti Join
查询是否不存在满足 join 条件的记录，是对 NOT IN 、 != ALL 、 NOT EXIST 子查询的一种优化手段。
二、查询执行策略
1、表访问方式
表访问方式主要包括：
全表扫描
主键索引上扫描
索引扫描
二级索引上扫描
索引Range
索引上范围扫描
<、>、<=、>=
索引ref查询
索引上同一个value查询
=
2、order by
索引扫描
索引顺序按 Order by 字段有序，可直接通过扫描索引得到有序结果
条件：order by 字段满足索引上最左匹配
如“ order by a, b” ，索引 idx_abc(a ， b ， c) 可以扫描使用
filesort 排序
查询结果通过 filesort 进行外部排序
不满足索引扫描条件时使用
3、Group by
索引扫描
通过 Loose Scan 的方式扫描索引去重
条件：order by 字段满足索引上最左匹配
如“order by a, b” ，索引 idx_abc(a ， b ， c) 可以扫描使用
临时表分组
查询结果保存在一张临时表中，并通过 group by 字段更新 aggregation 字段。
不满足索引扫描条件时使用
4、derived table
Merge
子查询直接合入外部的查询语句，消除 Derived table
条件：非关联简单子查询
如: select * from (select a, b from t1) A order by A.b 可直接改 merge 成： select a, b from t1 order by b
物化
子查询的结果保持在一张临时表中，供外部查询继续使用。
条件：非关联子查询
如: select sum(col_sum) from ( select sum(a) as col_sum from t1 group by b ) A
5、子查询
物化
子查询的结果保持在一张临时表中，供外部查询继续使用。
条件：非关联子查询
如：select * from t1 where a > (select max(b) from t2)
SemiJoin
子查询改写成 semi join 执行
条件：关联子查询，且是 IN 、 EXISTS 、 =ANY 等查询条件
如 select * from t1 where a IN (select b from t2) , 会改写成 select t1.* from t1 semi join t2 on t1.a=t2.b
嵌套执行
内部子查询嵌套在外部查询执行过程中执行，效率最差
上面两个条件都不满足：
如：select * from t1 where a > (select max(b) from t2 where t2.b = t1.c)
6、多表join
Nest Loop Join （NL）
相当于两层 for 循环执行，外表的每条记录去内部做扫描查询匹配
效率最差
Block Nest Loop Join （BNL）
外表的记录存放在 Join Cache 缓存中，内表循环时每条记录去 Join Cache 中查找匹配记录
Join Cache 的目的是为了减少内表的遍历次数
Block Key Access Join （BKA）
外表的记录存放在 Join Cache 缓存中，然后 Join Cache 中每条记录通过索引查询内部中匹配的记录
Hash Join
外表作为一张 build 表创建 hash map
内部作为 probe 表在 hash map 中查找匹配记录
三、执行计划分析
1、Explain 分析
1.1 Explain简介
基本用途
用来展示SQL查询语句的执行计划信息，帮助分析和改进查询语句的执行效率
使用方式
Explain [format=json/tree] query statement
输出内容
Explain
1.2 Explain输出内容
select type
SIMPLE 、PRIMARY 、 UNION 、 SUBQUERY 、 DEPENDENT SUBQUERY 、 DERIVED 、 DEPENDENT、DERIVED
Type
system 、 const 、 eq_ref 、 ref 、 fulltext 、 index 、 range 、 ALL
possible_keys
SQL 语句命中的所有索引
Key
SQL 语句使用的索引
Rows
预估的输出行数
Filtered
过滤条件过滤数据的比例
1.3 Explain实践
1）表访问
全表扫描
主键ref
主键range
覆盖索引扫描
2）Order by
索引排序
FileSort 排序
3）Group by
索引排序
临时表分组
4）Derived table
System table
物化临时表
merge
5）子查询
subquery
dependent subquery
semijoin (first match)
物化子查询
6）多表join
Nested loop join
BNL join
BKA join
2、Optimize trace 分析
2.1 Optimize trace作用
用来分析优化器生成执行计划的详细过程，往往用例分析生产错误执行计划的原因，从而给索引优化提供参考建议。
打开方式
Set session optimizer_trace="enabled=on";
set session optimizer_trace_max_mem_size=1000000;
Execute Your Query statement SQL;
Select * From Information_Schema.Optimizer_Trace [into outfile“out.txt"];
Set session optimizer_trace="enabled=off";
2.2 Optimize trace 分析
首先要了解SQL查询的执行过程：
SQL查询执行阶段：
SQL查询执行阶段分析：
2.3 Optimize trace 使用
# 开启跟踪,默认是关闭的 SET optimizer_trace="enabled=on"; #执行你的sql语句 select .... #查看trace信息 SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE; # 执行完你需要的sql语句就可以关闭trace了 SET optimizer_trace="enabled=off";
支持Optimize trace的SQL语句有：
1.SELECT; 2.INSERT or REPLACE (with VALUES or SELECT); 3.UPDATE/DELETE and their multi-table variants; 4.all the previous ones prefixed by EXPLAIN; 5.SET (unless it manipulates the optimizer_trace system variable); 6.DO; 7.DECLARE/CASE/IF/RETURN (stored routines language elements); 8.CALL.
具体Optimize trace 跟踪的json格式信息如下：
select * from test1,test2 where test1.id=test2.id and test1.id>4999900 | {
"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": [
]
}
}
]
}
- 点赞
- 收藏
- 关注作者
评论（0）