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

本文涉及的产品
云解析 DNS,旗舰版 1个月
全局流量管理 GTM,标准版 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天前
|
运维 Kubernetes Cloud Native
云原生技术入门及实践
【10月更文挑战第39天】在数字化浪潮的推动下,云原生技术应运而生,它不仅仅是一种技术趋势,更是企业数字化转型的关键。本文将带你走进云原生的世界,从基础概念到实际操作,一步步揭示云原生的魅力和价值。通过实例分析,我们将深入探讨如何利用云原生技术提升业务灵活性、降低成本并加速创新。无论你是云原生技术的初学者还是希望深化理解的开发者,这篇文章都将为你提供宝贵的知识和启示。
|
3天前
|
Cloud Native 安全 API
云原生架构下的微服务治理策略与实践####
—透过云原生的棱镜,探索微服务架构下的挑战与应对之道 本文旨在探讨云原生环境下,微服务架构所面临的关键挑战及有效的治理策略。随着云计算技术的深入发展,越来越多的企业选择采用云原生架构来构建和部署其应用程序,以期获得更高的灵活性、可扩展性和效率。然而,微服务架构的复杂性也带来了服务发现、负载均衡、故障恢复等一系列治理难题。本文将深入分析这些问题,并提出一套基于云原生技术栈的微服务治理框架,包括服务网格的应用、API网关的集成、以及动态配置管理等关键方面,旨在为企业实现高效、稳定的微服务架构提供参考路径。 ####
20 5
|
4天前
|
负载均衡 监控 Cloud Native
云原生架构下的微服务治理策略与实践####
在数字化转型浪潮中,企业纷纷拥抱云计算,而云原生架构作为其核心技术支撑,正引领着一场深刻的技术变革。本文聚焦于云原生环境下微服务架构的治理策略与实践,探讨如何通过精细化的服务管理、动态的流量调度、高效的故障恢复机制以及持续的监控优化,构建弹性、可靠且易于维护的分布式系统。我们将深入剖析微服务治理的核心要素,结合具体案例,揭示其在提升系统稳定性、扩展性和敏捷性方面的关键作用,为读者提供一套切实可行的云原生微服务治理指南。 ####
|
4天前
|
消息中间件 缓存 Cloud Native
云原生架构下的性能优化实践与挑战####
随着企业数字化转型的加速,云原生架构以其高度解耦、弹性伸缩和快速迭代的特性,成为现代软件开发的首选模式。本文深入探讨了云原生环境下性能优化的关键策略与面临的主要挑战,通过案例分析,揭示了如何有效利用容器化、微服务、动态调度等技术手段提升应用性能,同时指出了在复杂云环境中确保系统稳定性和高效性的难题,为开发者和架构师提供了实战指南。 ####
17 3
|
4天前
|
运维 Kubernetes Cloud Native
深入理解云原生架构:从理论到实践
【10月更文挑战第38天】本文将引导读者深入探索云原生技术的核心概念,以及如何将这些概念应用于实际的软件开发和运维中。我们将从云原生的基本定义出发,逐步展开其背后的设计哲学、关键技术组件,并以一个具体的代码示例来演示云原生应用的构建过程。无论你是云原生技术的初学者,还是希望深化理解的开发者,这篇文章都将为你提供有价值的见解和实操指南。
|
5天前
|
Cloud Native 持续交付 云计算
云原生技术入门与实践
【10月更文挑战第37天】本文旨在为初学者提供云原生技术的基础知识和实践指南。我们将从云原生的概念出发,探讨其在现代软件开发中的重要性,并介绍相关的核心技术。通过实际的代码示例,我们展示了如何在云平台上部署和管理应用,以及如何利用云原生架构提高系统的可伸缩性、弹性和可靠性。无论你是云原生领域的新手,还是希望深化理解的开发者,这篇文章都将为你打开一扇通往云原生世界的大门。
|
3天前
|
弹性计算 Kubernetes Cloud Native
云原生技术的实践与思考
云原生技术的实践与思考
16 2
|
4天前
|
Kubernetes Cloud Native 持续交付
云原生技术在现代应用架构中的实践与思考
【10月更文挑战第38天】随着云计算的不断成熟和演进,云原生(Cloud-Native)已成为推动企业数字化转型的重要力量。本文从云原生的基本概念出发,深入探讨了其在现代应用架构中的实际应用,并结合代码示例,展示了云原生技术如何优化资源管理、提升系统弹性和加速开发流程。通过分析云原生的优势与面临的挑战,本文旨在为读者提供一份云原生转型的指南和启示。
17 3
|
4天前
|
存储 Cloud Native 持续交付
云原生入门:从理论到实践
【10月更文挑战第38天】云原生技术正在重塑软件开发和运维的面貌。本文将带你走进云原生的世界,理解其核心理念,并探索如何将这些理念应用于实际项目中。我们将一起学习容器化、微服务架构、持续集成与持续部署(CI/CD)等关键概念,并通过代码示例加深理解。无论你是云原生新手还是希望深化知识的开发者,这篇文章都将为你提供宝贵的知识和启示。
14 3
|
4天前
|
运维 Kubernetes Cloud Native
云原生技术在现代应用架构中的实践与挑战####
本文深入探讨了云原生技术的核心概念、关键技术组件及其在实际项目中的应用案例,分析了企业在向云原生转型过程中面临的主要挑战及应对策略。不同于传统摘要的概述性质,本摘要强调通过具体实例揭示云原生技术如何促进应用的灵活性、可扩展性和高效运维,同时指出实践中需注意的技术债务、安全合规等问题,为读者提供一幅云原生技术实践的全景视图。 ####

推荐镜像

更多