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

本文涉及的产品
公共DNS(含HTTPDNS解析),每月1000万次HTTP解析
全局流量管理 GTM,标准版 1个月
云解析 DNS,旗舰版 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
相关文章
|
1月前
|
SQL 关系型数据库 MySQL
MySQL进阶突击系列(07) 她气鼓鼓递来一条SQL | 怎么看执行计划、SQL怎么优化?
在日常研发工作当中,系统性能优化,从大的方面来看主要涉及基础平台优化、业务系统性能优化、数据库优化。面对数据库优化,除了DBA在集群性能、服务器调优需要投入精力,我们研发需要负责业务SQL执行优化。当业务数据量达到一定规模后,SQL执行效率可能就会出现瓶颈,影响系统业务响应。掌握如何判断SQL执行慢、以及如何分析SQL执行计划、优化SQL的技能,在工作中解决SQL性能问题显得非常关键。
|
1月前
|
SQL 存储 关系型数据库
MySQL原理简介—1.SQL的执行流程
本文介绍了MySQL驱动、数据库连接池及SQL执行流程的关键组件和作用。主要内容包括:MySQL驱动用于建立Java系统与数据库的网络连接;数据库连接池提高多线程并发访问效率;MySQL中的连接池维护多个数据库连接并进行权限验证;网络连接由线程处理,监听请求并读取数据;SQL接口负责执行SQL语句;查询解析器将SQL语句解析为可执行逻辑;查询优化器选择最优查询路径;存储引擎接口负责实际的数据操作;执行器根据优化后的执行计划调用存储引擎接口完成SQL语句的执行。整个流程确保了高效、安全地处理SQL请求。
198 76
|
8天前
|
数据采集 JSON 数据可视化
JSON数据解析实战:从嵌套结构到结构化表格
在信息爆炸的时代,从杂乱数据中提取精准知识图谱是数据侦探的挑战。本文以Google Scholar为例,解析嵌套JSON数据,提取文献信息并转换为结构化表格,通过Graphviz制作技术关系图谱,揭示文献间的隐秘联系。代码涵盖代理IP、请求头设置、JSON解析及可视化,提供完整实战案例。
JSON数据解析实战:从嵌套结构到结构化表格
|
15天前
|
SQL 关系型数据库 MySQL
基于SQL Server / MySQL进行百万条数据过滤优化方案
对百万级别数据进行高效过滤查询,需要综合使用索引、查询优化、表分区、统计信息和视图等技术手段。通过合理的数据库设计和查询优化,可以显著提升查询性能,确保系统的高效稳定运行。
34 9
|
14天前
|
SQL 存储 缓存
MySQL的架构与SQL语句执行过程
MySQL架构分为Server层和存储引擎层,具有高度灵活性和可扩展性。Server层包括连接器、查询缓存(MySQL 8.0已移除)、分析器、优化器和执行器,负责处理SQL语句;存储引擎层负责数据的存储和读取,常见引擎有InnoDB、MyISAM和Memory。SQL执行过程涉及连接、解析、优化、执行和结果返回等步骤,本文详细讲解了一条SQL语句的完整执行过程。
34 3
|
21天前
|
SQL Oracle 关系型数据库
【YashanDB 知识库】如何将 mysql 含有 group by 的 SQL 转换成崖山支持的 SQL
在崖山数据库中执行某些 SQL 语句时出现报错(YAS-04316 not a single-group group function),而这些语句在 MySQL 中能成功执行。原因是崖山遵循 SQL-92 标准,不允许选择列表中包含未在 GROUP BY 子句中指定的非聚合列,而 MySQL 默认允许这种操作。解决办法包括:使用聚合函数处理非聚合列或拆分查询为两层,先进行 GROUP BY 再排序。总结来说,SQL-92 更严格,确保数据一致性,MySQL 在 5.7 及以上版本也默认遵循此标准。
|
1月前
|
SQL 存储 关系型数据库
MySQL原理简介—10.SQL语句和执行计划
本文介绍了MySQL执行计划的相关概念及其优化方法。首先解释了什么是执行计划,它是SQL语句在查询时如何检索、筛选和排序数据的过程。接着详细描述了执行计划中常见的访问类型,如const、ref、range、index和all等,并分析了它们的性能特点。文中还探讨了多表关联查询的原理及优化策略,包括驱动表和被驱动表的选择。此外,文章讨论了全表扫描和索引的成本计算方法,以及MySQL如何通过成本估算选择最优执行计划。最后,介绍了explain命令的各个参数含义,帮助理解查询优化器的工作机制。通过这些内容,读者可以更好地理解和优化SQL查询性能。
|
2月前
|
运维 Shell 数据库
Python执行Shell命令并获取结果:深入解析与实战
通过以上内容,开发者可以在实际项目中灵活应用Python执行Shell命令,实现各种自动化任务,提高开发和运维效率。
80 20
|
14天前
|
数据可视化 测试技术 API
GraphQL开发工具选型指南:Apipost高效调试与文档生成实战解析
本文深入解析了GraphQL开发工具Apipost在高效调试与文档生成方面的优势,对比同类工具Apifox,突出其可视化界面、实时调试及自动化文档生成等特性。Apipost通过智能代码补全、错误提示等功能简化复杂Query编写,支持一键生成标准化文档,显著提升开发效率和团队协作效果,尤其适合中大型团队应对复杂业务场景。
|
2月前
|
人工智能 关系型数据库 MySQL
AnalyticDB MySQL版:云原生离在线一体化数据仓库支持实时业务决策
AnalyticDB MySQL版是阿里云推出的云原生离在线一体化数据仓库,支持实时业务决策。产品定位为兼具数据库应用性和大数据处理能力的数仓,适用于大规模数据分析场景。核心技术包括混合负载、异构加速、智能弹性与硬件优化及AI集成,支持流批一体架构和物化视图等功能,帮助用户实现高效、低成本的数据处理与分析。通过存算分离和智能调度,AnalyticDB MySQL可在复杂查询和突发流量下提供卓越性能,并结合AI技术提升数据价值挖掘能力。
70 16

热门文章

最新文章

推荐镜像

更多