《阿里云认证的解析与实战-数据仓库ACP认证》——云原生数据仓库AnalyticDB MySQL版解析与实践(下)——三、SQL优化与慢查询解决(下)

本文涉及的产品
全局流量管理 GTM,标准版 1个月
云原生数据仓库AnalyticDB MySQL版,基础版 8ACU 100GB 1个月
公共DNS(含HTTPDNS解析),每月1000万次HTTP解析
简介: 《阿里云认证的解析与实战-数据仓库ACP认证》——云原生数据仓库AnalyticDB MySQL版解析与实践(下)——三、SQL优化与慢查询解决(下)

更多精彩内容,欢迎观看:

《阿里云认证的解析与实战-数据仓库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:515MB93.68%,WallTime4.34s43.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的值为889KB3.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;

 

image.png

 

如图执行分组查询,如果C、D的组合值非常多,采用数据的局部PARTIAL聚合,由于C、D组合规模比较大,导致中间结果也比较大,然后在做最后的聚合时会达不到聚合的效果。

数据根据分组字段进行节点间的数据重分布,执行最终FINAL聚合,会避免中间结果数据的膨胀,减少中间结果的压力、网络堵塞等情况

 

7. 优化原则

 

合理选择分布列、分区列,避免数据倾斜导致长尾任务提升写入效率

配置较高的资源类型及较多的实例个数

基于一级分区设置数据的生命周期

适当设置较大的分区数,提升磁盘应用率

选择合适的数据同步策略:同步工具、方法、方式以及适当并发数。

相关实践学习
AnalyticDB MySQL海量数据秒级分析体验
快速上手AnalyticDB MySQL,玩转SQL开发等功能!本教程介绍如何在AnalyticDB MySQL中,一键加载内置数据集,并基于自动生成的查询脚本,运行复杂查询语句,秒级生成查询结果。
阿里云云原生数据仓库AnalyticDB MySQL版 使用教程
云原生数据仓库AnalyticDB MySQL版是一种支持高并发低延时查询的新一代云原生数据仓库,高度兼容MySQL协议以及SQL:92、SQL:99、SQL:2003标准,可以对海量数据进行即时的多维分析透视和业务探索,快速构建企业云上数据仓库。 了解产品 https://www.aliyun.com/product/ApsaraDB/ads
相关文章
|
3月前
|
存储 关系型数据库 MySQL
MySQL——数据库备份上传到阿里云OSS存储
MySQL——数据库备份上传到阿里云OSS存储
146 0
|
4月前
|
人工智能 关系型数据库 MySQL
基于阿里云的PolarDB MySQL版实现AI增强数据管理
本文将介绍如何利用阿里云的PolarDB MySQL版结合AI技术,实现数据管理的自动化和智能化。
271 0
|
4月前
|
SQL Cloud Native 调度
云原生数据仓库使用问题之如何修改历史数据清理的SQL
阿里云AnalyticDB提供了全面的数据导入、查询分析、数据管理、运维监控等功能,并通过扩展功能支持与AI平台集成、跨地域复制与联邦查询等高级应用场景,为企业构建实时、高效、可扩展的数据仓库解决方案。以下是对AnalyticDB产品使用合集的概述,包括数据导入、查询分析、数据管理、运维监控、扩展功能等方面。
|
4月前
|
存储 数据挖掘 BI
数据仓库深度解析与实时数仓应用案例探析
随着数据量的不断增长和数据应用的广泛深入,数据治理和隐私保护将成为数据仓库建设的重要议题。企业需要建立完善的数据治理体系,确保数据的准确性、一致性和完整性;同时加强隐私保护机制建设,确保敏感数据的安全性和合规性。
460 55
|
3月前
|
存储 机器学习/深度学习 数据采集
深入解析大数据核心概念:数据平台、数据中台、数据湖与数据仓库的异同与应用
深入解析大数据核心概念:数据平台、数据中台、数据湖与数据仓库的异同与应用
|
3月前
|
关系型数据库 MySQL 网络安全
阿里云安装Mysql
阿里云安装Mysql
185 1
|
3月前
|
存储 运维 Cloud Native
"Flink+Paimon:阿里云大数据云原生运维数仓的创新实践,引领实时数据处理新纪元"
【8月更文挑战第2天】Flink+Paimon在阿里云大数据云原生运维数仓的实践
267 3
|
4月前
|
SQL Cloud Native 关系型数据库
云原生数据仓库使用问题之控制JDBC方式请求的SQL大小限制的参数是什么
阿里云AnalyticDB提供了全面的数据导入、查询分析、数据管理、运维监控等功能,并通过扩展功能支持与AI平台集成、跨地域复制与联邦查询等高级应用场景,为企业构建实时、高效、可扩展的数据仓库解决方案。以下是对AnalyticDB产品使用合集的概述,包括数据导入、查询分析、数据管理、运维监控、扩展功能等方面。
|
4月前
|
SQL 监控 Cloud Native
云原生数据仓库使用问题之怎么查看正在执行的SQL语句
阿里云AnalyticDB提供了全面的数据导入、查询分析、数据管理、运维监控等功能,并通过扩展功能支持与AI平台集成、跨地域复制与联邦查询等高级应用场景,为企业构建实时、高效、可扩展的数据仓库解决方案。以下是对AnalyticDB产品使用合集的概述,包括数据导入、查询分析、数据管理、运维监控、扩展功能等方面。
|
4月前
|
存储 消息中间件 数据挖掘
数据仓库的深度探索与实时数仓应用案例解析
大数据技术的发展,使得数据仓库能够支持大量和复杂数据类型(如文本、图像、视频、音频等)。数据湖作为一种新的数据存储架构,强调原始数据的全面保留和灵活访问,与数据仓库形成互补,共同支持企业的数据分析需求。

推荐镜像

更多