更多精彩内容，欢迎观看：
《阿里云认证的解析与实战-数据仓库ACP认证》——云原生数据仓库AnalyticDB MySQL版解析与实践（下）——三、SQL优化与慢查询解决（上）：https://developer.aliyun.com/article/1222969?spm=a2c6h.13148508.setting.21.77f14f0es0kEi9
4. 典型慢查询
1) 消耗内存的慢查询
• Stage中有GROUP BY操作，如果GROUP BY后面有大量不同值，首先GROUP BY会在内存中缓存这些不同值，会消耗大量内存。
• Stage中有Join操作，在AnalyticDB MySQL中，Join多数情况下使用HASH，Join将其中一个小表构建HASH表加快运算过程，如果小表也比较大，对应构建的HASH表也会比较大，消耗大量内存。
• Stage中有SORT操作，排序需要在内存中运行。
• Stage中有窗口函数操作，比如常见的RANK、DENSE_RANK、ROW_NUMBER需要在内存中完成。
2) 消耗CPU的慢查询
• 过滤条件没有下推到存储层，会消化大量CPU。
• Join条件中带有过滤操作。
• Join时没有指定Join条件。
3) 消耗磁盘I/O的慢查询
• 过滤条件的数据筛选率较低。
• 正常情况下存储层利用索引进行数据的过滤，如果过滤条件没有下推，导致对源表进行了全表扫描，会消化大量的IO。
• 过滤条件下推，但是过滤条件。设置的范围较大，仍然有大量数据被扫描。
• 需要扫描的分区较多。
5. 慢查询诊断与优化
1) 发现慢查询
用户要定位慢查询，首先需要找到慢查询。ADB的用户控制台提供了“甘特图”和“查询列表”两种形式支持在多个维度上进行检索，帮助用户快速定位慢查询，支持最近两周的全量查询检索和分析。
2) 甘特图
甘特图以图形化的方式，形象的展示了查询在ADB实例上的执行顺序，每个色块表示了一条查询，色块左侧为查询的提交时间，色块右侧为查询的结束时间，色块的相对长度表示了某条查询的执行时间。
3) 查询列表
• 支持按数据库名、用户名、客户端段IP、耗时、消耗内存以及扫描量等10余项维度进行检索和查询。
• 支持模糊检索和精确检索。
• 支持字符串类型的检索条件。
• 支持数值类型的检索条件。
4) SQL自诊断
将专家经验以规则的形式体现在执行计划中，对于ADB的初次接触者，即可以根据诊断结果确定查询执行过程中的性能瓶颈点，也可以根据诊断结果学习到ADB执行计划中需要关注的重点算子。
5) 诊断结果分层
• Query级别诊断结果。
• Stage级别诊断结果。
• Operator（算子）级别诊断结果。
6) 常见慢查询问题
• 返回客户端的数据量较大。
• JOIN存在数据膨胀。
• 查询生成的Stage个数较大。
• 查询读取的数据量较大。
• 数据倾斜。
• Stage输入数据倾斜。
• Stage输出输出倾斜。
6. 诊断与优化示例
1) Left join优化改写为right join
Jion有内连接、外连接，外连接又有左外连接和右外连接。Left join是实践中常用的一种表关联方式，由于Hash Join实现会以右表做build，且left Join不会做左右表的重新排序，在右表数据量很大时会造成执行慢、消耗过多内存资源等多个问题。
示例
nation是一个25行的小表，customer是一个15000000行的大表，通过explain analyze查看一条包含left join的SQL的执行计划。
• 原语句
select count(*) from nation t1 left join customer t2 on t1.n_nationkey=t2.cnationkey |
• 问题
nation是左表，customer是右表，通过explain analyze分析，PeakMemory：515MB（93.68%），WallTime：4.34s（43.05%），PeakMemory的占比高达93.68%，可以判断，left join为整个SQL的性能瓶颈。
• 解决方案
将lift join 改成right join，新语句：
select count(*) from Customert2 right join nationtion t1.n nationkey=t2.cnationkey |
• 优化方案
可以将LEFT_TO_RIGHT_ENABLED设置为true，即两个表可以做相互转化，新语句和效果如下：
/*+LEFT_TO_RIGHT_ENABLED=true，CASCADES_OPTIMIZER_ENABLED=false*/ select count() from nation t1 left join customer t2 on t1.n_nationkey =t2.cnationkey |
调整后PeakMemory的值为889KB（3.31%），从515MB下降到889KB，已经不是计算热点。
2) 过滤条件不下推
• 在某些场景中，使用索引过滤数据不一定能得到较好的性能，甚至会影响整体性能。
• 过滤条件不下推功能，可以在查询级别或实例级别暂时蔽掉某些字段的过滤条件下推能力，带来更好整体查询收益。
不建议使用索引过滤数据条件的情况有以下三种：
• 数据唯一值少。
• 磁盘IO压力大。
• 同时有多个条件下推。
设置过滤条件不下推：
/*+filter_not_pushdown_columns=[${database}.${tableName}:${col1Name}|${col2Name}]*/ /*+filter_not_pushdown_columns=[test01.table01:id|product]*/ |
3) 分组聚合查询优化
语句
SELECT sum(A), max(B) FROM tb1 GROUP BY C, D; |
• 如图执行分组查询，如果C、D的组合值非常多，采用数据的局部（PARTIAL）聚合，由于C、D组合规模比较大，导致中间结果也比较大，然后在做最后的聚合时会达不到聚合的效果。
• 而数据根据分组字段进行节点间的数据重分布，执行最终（FINAL）聚合，会避免中间结果数据的膨胀，减少中间结果的压力、网络堵塞等情况。
7. 优化原则
• 合理选择分布列、分区列，避免数据倾斜导致长尾任务提升写入效率。
• 配置较高的资源类型及较多的实例个数。
• 基于一级分区设置数据的生命周期。
• 适当设置较大的分区数，提升磁盘应用率。
• 选择合适的数据同步策略：同步工具、方法、方式以及适当并发数。
