更多精彩内容,欢迎观看:
《阿里云认证的解析与实战-数据仓库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. 优化原则
• 合理选择分布列、分区列,避免数据倾斜导致长尾任务提升写入效率。
• 配置较高的资源类型及较多的实例个数。
• 基于一级分区设置数据的生命周期。
• 适当设置较大的分区数,提升磁盘应用率。
• 选择合适的数据同步策略:同步工具、方法、方式以及适当并发数。