开发者学堂课程【数据仓库 ACP 认证课程：【视频】云原生数据仓库 Analyticdb MYSQL 版-解析与实践-3】学习笔记，与课程紧密联系，让用户快速学习知识。
课程地址：https://developer.aliyun.com/learning/course/928/detail/14625
【视频】云原生数据仓库 Analyticdb MYSQL 版-解析与实践-3
4. 典型慢查询
典型慢查询包括以下三种：消耗内存的慢查询、消耗CPU的慢查询、消耗磁盘I/O的慢查询。也就是说不同的操作，可能消耗的资源不同。
（1）消耗内存的慢查询
①Stage中有GROUP BY操作，且GROUP BY后面不同值的字段数量非常多，此时GROUP BY需要在内存中缓存这些不同值的字段，所以会消耗大量的内存。
②Stage中有JOIN操作，join大多数采用的是哈希Join，会把其中的一个小表来构建一个哈希表，来加快Join预算的过程，如果这个小表非常大，对应构建的哈希表也就非常大，消耗内存就会多。
③Stage中有SORT操作，排序也是需要在内存中完成。
④Stage中有窗口函数操作，常见的窗口函数也需要在内存中完成，也属于消耗内存的慢查询。
（2）消耗CPU的慢查询
①过滤条件没有下推到存储层，所以在存储层获取的数据量就很大，此时在计算层完成数据的过滤，这个时候就需要CPU。
②Join条件中带有过滤操作，此时adb首先会对表进行join操作，然后对join的结果进行过滤操作，进行数据的去除，这个过程是没有索引可以进行使用的，只能依靠cpu对数据进行逐条过滤。从而消耗CPU。
③Join时没有指定Join条件，相当于cross join，相当于两个表做笛卡尔乘积
（3）消耗磁盘I/O的慢查询
①过滤条件的数据筛选率较低，通过过滤条件，只去除了有限的数据，此时需要在存储层进行大量的数据提取转给数据层，此时会消耗大量的磁盘I/O。
②过滤条件下没有下推，导致对源表进行了全表扫描。正常情况下，需要在存储层利用索引来完成数据的过滤，但是某种条件下，可能还没有到存储层，此时就需要加载整个表的数据，此时会耗大量的磁盘I/O。
③过滤条件下推，但是过滤条件设置的范围较大，仍然有大量数据被扫描，也就是条件效果不明显，也会造成大量数据。
④需要扫描的分区较多，数据在adb mysql当中是分片分区的，在一个片内部可以分成多个分区，通过多个分区实现数据的并行处理，如果分区个数太多，需要并行处理，此时需要扫描更多的分区。
下推指的是过滤条件。adb mysql有计算层，有存储层，数据存储在存储层里，过滤的过程是可以通过在存储层的数据上进行处理。如果一个条件直接在存储层完成了数据的过滤，就可以被称之为完成了下推。如果在计算层才完成数据的过滤，就可以说，没有完成数据的下推。
5. 慢诊断查询与优化
adb mysql提供了非常方便的工具，来帮助诊断和优化慢查询。
（1）发现慢查询
用户要定位慢查询，首先需要找到慢查询，ADB的用户控制台提供了【甘特图】和【查询列表】两种形式支持在多个维度上进行检索，帮助用户快速定位慢查询，支持最近两周的全量查询检索和分析。
（2）甘特图
甘特图以图形的方式形象的展示了查询在ADB实例上的执行顺序，每个色块表示了一条查询，色块左侧为查询的提交时间，色块右侧为查询的结束时间，色块的相对长度表示了某条查询的执行时间。
（3）查询列表
·支持按数据库名、用户名、客户端段IP、耗时、消耗内存以及扫描量等10余项未读进行检索和查询。
·支持模糊检索和精确检索
·支持字符串类型的检索条件
·支持数值类型的检索条件
（4）sql自诊断
将专家经验以规则的形式体现在执行计划中，对于adb mysql的初次接触者，即可以根据诊断结果确定查询实行过程中的性能瓶颈点，也可以根据诊断结果学习到adb mysql执行计划中需要关注的重点算子。
（5）诊断结果分层
Query级别诊断结果
Stage级别诊断结果
Opreator（算子）级别诊断结果
如下图所示，可以看到，左边是一个查询，这个查询包括4个Stage，每个Stage的资源消耗，数据量等情况都可以在右面的图中看到。
也可以点击每一个Stage，查看具体每个算子的情况。
（6）常见慢查询问题
返回客户端的数据量较大
JOIN存在数据膨胀
查询生成的Stage个数较大
查询读取的数据量较大
数据倾斜
Stage输入数据倾斜
Stage输出数据倾斜
在不同的Stage之间，有数据传输的过程，在上游Stage给下游输出数据的时候，是否能够保证均匀的分配给下游stage的每一个task当中，如果出现数据倾斜，就会造成下游Stage当中效率低下，从而出现长尾效应。
6. Left join 优化改写为 right join
Join分为内连接与外连接，外连接分为左外连接与右外链接。Left join是实践中常用的一种表关联方式，通常采用Hash Join。Hash Join默认以右表做build，基于右表构建Hash表。而Left join不会做左右表的重新排序，在右表数据量很大时会造成执行慢、消耗过多内存资源等多个问题。
一共有两个表分别为Nation表以及customer表。Nation为左表是一个25行的小表，customer为右表是一个15000000行的大表，基于此构建的Hash表会占用大内存。如表所示，通过explain analyze查看一条包含left join的SQL的执行计划。
将left join改写为right join后nation为左表，customer为右表。用小表构建Hash表显著降低内存空间。
通常针对外连接不会进行左右表重新排序，可以将Left拖拽至内部，即设置一个开关用来转换左右表。
原语句及问题 |
新语句及效果 |
|
Explain analyze Select Count（*） From Nation t1 Left join customer t2 on t1.n_nationkey=t2.c_nationkey |
Select Count（*） From customer t2 right join nation t1 on t1.n_nationkey=t2.c_nationkey |
/*+LEFT_TO_RIGHT_ENABLED=True,CASCADES_OPTIMIZER_ENABLED= False*/ Select Count（*） From Nation t1 Left join customer t2 on t1.n_nationkey=t2.c_nationkey |
PeakMemory:515MB(93.68%),WallTime:4.34s(43.05%):PeakMemory的占比高达93.68%，可以判断left join为整个SQL的性能瓶颈。 |
调整后PeakMemory的值为889KB（3.31%），从515MB降为889KB，已经不是计算热点。 |
|
7. 不下推过滤条件
通过过滤条件下推，可以显著减少IO，提高性能。但在某些场景中，使用索引过滤数据不一定能得到较好的性能，甚至会影响整体性能。过滤条件不推动功能，可以在查询级别或实例级别暂时屏蔽某些字段的过滤条件下推能力，带来更好整体查询收益。
不下推过滤数据即不建议使用索引过滤数据的条件包含三种：
（1）数据唯一值少。此时使用索引效果不明显。例如性别分为男、女，每一个查询需要读取一半的数据。因此不建议使用索引过滤数据。
（2）磁盘IO压力大。很多操作进行IO操作，如果继续索引下推数据会造成更大压力对查询产生性能影响。
（3）同时有多个条件，增加过滤复杂度。无形中增加存储功能的计算压力。不建议使用数据下推，可以将数据拉取至计算层完成数据过滤。
（4）如果不想使用数据下推，可以采取以下方式。
/*+filter_not_pushdown_columns=[$(database).${tableName}:${(col1Name}|${col2Name}]*/
/*+filter_not_pushdown_columns=[test01.table01:id|product]*/
大部分推荐使用过滤条件下推，某些条件下推可能不会有好的效果。不下推对磁盘IO带有压力。
8. 分组聚合查询优化
执行分组查询，例如GROUP BY C,D；C,D不同值相差较大、组合值较多。采用第一种方式，分别在不同的节点部分聚集，聚集结果由于C,D组合规模大，导致中间组合数据量较大。
第二阶段数据最终聚合，没有数据聚合、查询的效果，不需要局部聚集，直接将ABCD分发至对应节点，避免中间数据膨胀导致传输压力。
9. 优化原则
（1）合理选择分布列、分区列，避免数据倾斜导致长尾任务提升写入效率；分布式架构中表数据会分布在多个节点上，保证不同节点数据均匀分布，避免出现长尾效应。
（2）配置较高的资源类型及较多的实例个数，保证计算资源及存储资源丰富。
（3）基于一级分区设置数据的生命周期，可以控制数据规模，避免无用数据以及历史数据无限制增长。
（4）适当设置较大的分区数，提升磁盘应用率；不同的分区可以并行提取数据，可以提取性能、提升磁盘利用率。
（5）选择合适的数据同步策略∶同步工具、方法、方式以及适当并发数。
三、数据更新
云原生数据仓库融合了数据库以及大数据技术，针对传统数仓产品自身不支持数据更新，但是作为云上数据产品，支持、提供丰富的数据更新操作。
1. 批量打包方式写入数据
虽然支持数据更新，但与传统的事务性数据库以及面向生产业务的数据库不同。既是生产数据库又是数据仓库是不现实的。主要语句为INSERT INTO和REPLACE INTO。
INSERT INTO用于向表中插入数据，遇到主键重复时会自动忽略当前写入数据，不做更新，作用等同于INSERT IGNORE INTO;
REPLACE INTO用于实时覆盖写入数据。写入数据时，根据主键判断待写入的数据是否已经存在于表中，如果已经存在，则先删除该行数据，然后插入新的数据；如果不存在，则直接插入新数据。
√ 通过每条INSERT或者REPLACE语句写入的数据行数大于1000行，但写入的总数据量不宜太大，不超过16MB。
√通过批量打包方式写入数据时，单个批次的写入延迟相对会高一些。
√ 写入报错时，需要做重试确保数据被写入，重试导致的数据重复可以通过表的主键来消除。
2.更新数据
AnalyticDB for MySQL提供多种数据更新方式，推荐策略为：
（1）更新频率、基于主键。REPLACE INTO。将表中整行数据替换，具有更高效率。
（2）更新频率低、基于主键。REPLACE INTO、UPDATE，不太建议使用REPLACE INTO，因为UPDATE替换某字段的值，涉及数据对齐、查找，效率低。
（3）更新频率低、基于任意条件。只能使用UPDATE。
综合上述内容，REPLACE INTO具有更高的执行效率。
