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

本文涉及的产品
云解析 DNS,旗舰版 1个月
公共DNS(含HTTPDNS解析),每月1000万次HTTP解析
云原生数据仓库AnalyticDB MySQL版,基础版 8ACU 100GB 1个月
简介: 《阿里云认证的解析与实战-数据仓库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
相关文章
|
20天前
|
存储 分布式计算 物联网
美的楼宇科技基于阿里云 EMR Serverless Spark 构建 LakeHouse 湖仓数据平台
美的楼宇科技基于阿里云 EMR Serverless Spark 建设 IoT 数据平台,实现了数据与 AI 技术的有效融合,解决了美的楼宇科技设备数据量庞大且持续增长、数据半结构化、数据价值缺乏深度挖掘的痛点问题。并结合 EMR Serverless StarRocks 搭建了 Lakehouse 平台,最终实现不同场景下整体性能提升50%以上,同时综合成本下降30%。
|
11天前
|
SQL 存储 OLAP
阿里云 EMR Serverless StarRocks3.x,极速统一的湖仓新范式
阿里云 EMR Serverless StarRocks3.x,极速统一的湖仓新范式
|
2月前
|
人工智能 关系型数据库 MySQL
AnalyticDB MySQL版:云原生离在线一体化数据仓库支持实时业务决策
AnalyticDB MySQL版是阿里云推出的云原生离在线一体化数据仓库,支持实时业务决策。产品定位为兼具数据库应用性和大数据处理能力的数仓,适用于大规模数据分析场景。核心技术包括混合负载、异构加速、智能弹性与硬件优化及AI集成,支持流批一体架构和物化视图等功能,帮助用户实现高效、低成本的数据处理与分析。通过存算分离和智能调度,AnalyticDB MySQL可在复杂查询和突发流量下提供卓越性能,并结合AI技术提升数据价值挖掘能力。
69 16
|
5月前
|
人工智能 自然语言处理 关系型数据库
阿里云云原生数据仓库 AnalyticDB PostgreSQL 版已完成和开源LLMOps平台Dify官方集成
近日,阿里云云原生数据仓库 AnalyticDB PostgreSQL 版已完成和开源LLMOps平台Dify官方集成。
|
5月前
|
人工智能 分布式计算 数据管理
阿里云位居 IDC MarketScape 中国实时湖仓评估领导者类别
国际数据公司( IDC )首次发布了《IDC MarketScape: 中国实时湖仓市场 2024 年厂商评估》,阿里云在首次报告发布即位居领导者类别。
|
5月前
|
SQL 分布式计算 数据挖掘
加速数据分析:阿里云Hologres在实时数仓中的应用实践
【10月更文挑战第9天】随着大数据技术的发展,企业对于数据处理和分析的需求日益增长。特别是在面对海量数据时,如何快速、准确地进行数据查询和分析成为了关键问题。阿里云Hologres作为一个高性能的实时交互式分析服务,为解决这些问题提供了强大的支持。本文将深入探讨Hologres的特点及其在实时数仓中的应用,并通过具体的代码示例来展示其实际应用。
355 0
|
6月前
|
运维 数据挖掘 OLAP
阿里云Hologres:一站式轻量级OLAP分析平台的全面评测
在数据驱动决策的今天,企业对高效、灵活的数据分析平台的需求日益增长。阿里云的Hologres,作为一站式实时数仓引擎,提供了强大的OLAP(在线分析处理)分析能力。本文将对Hologres进行深入评测,探讨其在多源集成、性能、易用性以及成本效益方面的表现。
309 7
|
6月前
|
关系型数据库 MySQL 网络安全
5-10Can't connect to MySQL server on 'sh-cynosl-grp-fcs50xoa.sql.tencentcdb.com' (110)")
5-10Can't connect to MySQL server on 'sh-cynosl-grp-fcs50xoa.sql.tencentcdb.com' (110)")
|
8月前
|
SQL 存储 监控
SQL Server的并行实施如何优化?
【7月更文挑战第23天】SQL Server的并行实施如何优化?
198 13
|
8月前
|
SQL
解锁 SQL Server 2022的时间序列数据功能
【7月更文挑战第14天】要解锁SQL Server 2022的时间序列数据功能,可使用`generate_series`函数生成整数序列,例如:`SELECT value FROM generate_series(1, 10)。此外,`date_bucket`函数能按指定间隔(如周)对日期时间值分组,这些工具结合窗口函数和其他时间日期函数,能高效处理和分析时间序列数据。更多信息请参考官方文档和技术资料。
119 9

热门文章

最新文章

推荐镜像

更多