云原生数据仓库 AnalyticDB MySQL 版 _解析与实践3|学习笔记（二）

开发者学堂课程【数据仓库 ACP 认证课程：快速学习云原生数据仓库 AnalyticDB MySQL 版 _解析与实践3】学习笔记，与课程紧密联系，让用户快速学习知识。

课程地址：https://developer.aliyun.com/learning/course/928/detail/14625

云原生数据仓库 AnalyticDB MySQL 版_解析与实践3

一、SQL优化和慢查询解决方案

analyticDB MySQL 定义为针对于千万条数据的毫秒级查询引擎，是一个实时数仓产品，所以对于 sql 的优化及慢查询具有丰富的方案。

1. 慢查询诊断与优化：查询流程和执行计划

对于 MySQL 而言 SQL 语言是完成用户和系统内部存储数据之间的交互最基本的工具，在执行阶段，analyticDB MySQL 执行结构切分为多个 Stage 来执行对，一个stage 就是执行计划中某一部分的物理实体。一个查询可分为多个步骤，每一个步骤被称为 stage。

如：map 对原始数据作 map 运算过程中，后续可做 reduce，一个 map 或一个reduce 即可理解为一个 stage

举例：分组聚合查询

分组聚合查询的处理流程，Controller 节点会把查询的逻辑执行计划（plan）分片下发到执行计划任务的各个节点上。

对于 analyticDB MySQL 而言一个可分为多个 stage，一个 stage 内部又由多个task 组成，task 由算子 aggrerate 具体执行，总体查询划分为三个阶段：先执行stage2 再然后 stage1 之后的 stage0

Stage2 由4个 task 组成，分布在4个节点上并行地进行执行

在每一个 test 里包括三个基本的算子，首先是 tablescan 表示扫描，然后是 filter表示过滤，aggregate 表示区部地聚集。

Stage1 与stage2 存在数据呈分布与传输的过程称之为 remote exchange 远程数据交换，将stage2 的结果传入 stage1

传输方式有多种，在 stage1 中存在2个 task ，remote exchange 是将上一个 stage的结果传入，并完成两个结果的聚合

Stage2 由4个 task 组成，并执行数据的扫描，过滤以及局部聚合等操作

Stage1 由2个 task 组成，并执行最终的聚合操作

Stage0 由1个 task 组成，负责汇总

2. 算子

一个算子对应数据处理的基本逻辑，一个算子负责完成一个基本的数据处理逻辑，一组算子按照执行计划完成数据的一组处理规则。具体算子介绍如下：

Aggregation：通过sum（） count（） avg（）等函数对数据进行聚合或分组聚合操作。

Distinctlimit：对 SQL 语句中的 DISTINCT LIMIT 操作。

Filter：使用存储层数据的索引进行过滤，如果存储层没有索引，需要在计算层使用算子进行过滤。

Remote exchange ：用来表示上游向下游 stage 传输数据时所用的方法，包括：broadcast、repartition、gather。

Jion ：对应 SQL 语句中的 jion 操作。

Project：对应 SQL 语句中对待定字段的投影操作，如：case when then 控制流，conccat（）函数等。

Stageoutput ：用于将当前 stage 处理后的数据通过网络传输到下游stage的节点。

Sort ：应 SQL 语句中对 ORDER BY 子句的操作，执行 ORDER BY 字段的排序，

Tablescan ：用于从数据源读取数据，如果需要过滤数据，那麽数据由底层数据源使用索引高效完成。

Topn ：对应 SQL 语句中 ORDER BY LIMIT,M,N 查询。

3. 影响查询性能的因素

（1）集群规则

• 不同集群规格的 cpu 核数、内存大小和数据存储介质等属性不同，处理子任务的能力也就不同，需要结合业务查询特征来选择集群规格。
• 以 Join 或分组聚合为主的业务查询会消耗较多的 CPU 和内存资源。
• 扫描数据和简单分组聚合操作的查询会消耗较多的磁盘 I/O 资源。

（2）节点数量

analyticDB MySQL 版本使用了分布式数据处理架构，一条查询会被分解成多个Stage 在不同的节点上并行执行。所以如果集群中的节点数量越多，analyticDB MySQL 版处理查询的能力也会越强。您可以根据实际的业务需求来决定集群节点的购买数量，更多详情，可以参考创建集群。

（3）数据分布特征

• 由于使用了分布式数据处理架构，具备将一条查询分解到多个节点上并行执行的能力。
• 充分利用多节点来并行处理查询，还取决于数据在储存节点上的分布特征。
• 如果数据能够均匀分布在储存节点上，多个子任务在处理数据时，就能几乎同时结束任务。
• 数据分布不均匀，子任务在处理数据是会存在时间上的长尾，从而影响最终的查询效果。

（4）数据量大小

• 在处理查询时，通常不会讲处理过程中的临时结果暂时写到磁盘里，而是尽量在内存中将所有数据处理掉。
• 如果查询需要处理的数据量较大，就可能会长时间占用大量的资源，导致整理查询效率降低，进而影响最终的查询效果。
• 表储存的数据量较大，在执行索引过滤、明细数据读取等操作时会出现争抢磁盘 I/O 资源，导致查询变慢。

（5）查询并发度

• 能同时处理的查询数量也会存在上限。如果查询的并发度过高，集群节点资源已达到瓶颈，那么后台的查询会出现较长时间的排队，影响整体查询效果。

（6）查询复杂度

• 查询的负责度不同造成的压力也不同。
• 如果查询中过滤条件过于复杂，会在数据过滤时对储存节点造成一定压力；如果查询中 Join 算子过多，数据可能需要在不同节点间进行多次的网络传输，造成网络阻塞；如果查询中分组字段过多，也会占用较多的内存资源。

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 拖拽至内部，即设置一个开关用来转换左右表。

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 具有更高的执行效率。

3.删除数据/分区/表

AnalyticDB for MySQL 提供多种数据删除方式，主要语句为 DELETE 与TRUNCATE。推荐策略为∶

• 数据删除频率低、基于主键为条件的删除，可通过 DELETE FROM WHERE PK='xx' 删除数据。
• 数据删除频率低、基于任意条件的删除，可通过 DELETE 删除数据。

如果数据删除频率低一般都用DELETE完成，删除大量数据可以通过 TRUNCATE 完成，并且 TRUNCATE 可以删除表中某一分区。

•  通过 TRUNCATE TABLE db_name.table_name PARTITION partition_name 册除指定二级分区。
•  通过 TRUNCATE TABLE db_name.table_name 删除指定表（包括所有二级分区）数据。

4.批量导入数据

AnalyticDB for MySQL 支持多种数据源，实现数据导入导出。批量导入适用于大数据量导入的场景，导入过程中可以查询旧数据，导入操作完成后一键切换新数据。导入失败时，支持回滚新数据，不影响查询旧数据。

•  从大数据系统、大存储环境导入数据，采用 INSERT OVERWRITE INTO SELECT方式处理，如从 MaxCompute、OSS 导入数据到 AnalyticDB for MySQL；
• 批量导入单个表时，导入任务会在系统中串行执行；批量导入多个表时，导入任务会在系统中并行执行，默认并行执行2个任务；

1. 实时导入数据

•  AnalyticDB for MySQL 实时导入适用于小数据量导入的场景。
• l从业务系统实时导入数据时采用 INSERTINTO SELECT FROM，把一个表的数据导入另一个表中。
• 实时导入任务使用 AnalyticDB for MySQL 中的资源，如果查询数据期间需要导入数据，建议在 QPS（Query Per Second，每秒查询率）较低时进行导入操作。导入会耗费资源，如果在用户查询高峰期导入数据，会对正常业务带来影响。