SQL调优指南—SQL调优进阶—执行计划管理

本文涉及的产品
云原生数据库 PolarDB 分布式版,标准版 2核8GB
简介: 本文介绍如何管理执行计划,将重复或者复杂查询的执行计划长久地保存下来。

背景信息

对于每一条SQL,优化器都会生成相应执行计划。但是很多情况下,应用请求的SQL都是重复的(仅参数不同),参数化之后的SQL完全相同。这时,可以按照参数化之后的SQL构造一个缓存,将除了参数以外的各种信息(比如执行计划)缓存起来,称为执行计划缓存(Plan Cache)。

另一方面,对于较复杂的查询(例如涉及到多个表的Join),为了使其执行计划能保持相对稳定,不因为版本升级等原因发生变化。执行计划管理(Plan Management)为每个SQL记录一组执行计划,该执行计划会被持久化地保存,即使版本升级也会保留。

工作流程概览

当PolarDB-X收到一条查询SQL时,会经历以下流程:

  1. 对查询SQL进行参数化处理,将所有参数替换为占位符
  2. 以参数化的SQL作为Key,查找执行计划缓存中是否有缓存;如果没有,则调用优化器进行优化。
  3. 如果该SQL是简单查询,则直接执行,跳过执行计划管理相关步骤。
  4. 如果该SQL是复杂查询,则使用基线(Baseline)中固化的执行计划;如果有多个,则选择代价最低的那个。111111.png

执行计划缓存

PolarDB-X默认开启执行计划缓存功能。EXPLAIN结果中的HitCache表示当前SQL是否命中执行计划缓存。开启执行计划缓存后,PolarDB-X会对SQL做参数化处理,参数化会将SQL中的常量用占位符?替换,并构建出相应的参数列表。在执行计划中也可以看到LogicalView算子的SQL中含有?。555.png

执行计划管理

对于复杂SQL,经过执行计划缓存之后,还会经过执行计划管理流程。

执行计划缓存和执行计划管理都是采用参数化后的SQL作为Key来执行计划。执行计划缓存中会缓存所有SQL的执行计划,而执行计划管理仅对复杂查询SQL进行处理。由于受到具体参数的影响,SQL模版和最优的执行计划并非一一对应的。

在执行计划管理中,每一条SQL对应一个基线,每个基线中包含一个或多个执行计划。实际使用中,会根据当时的参数选择其中代价最小的执行计划来执行。当执行计划缓存中的执行计划走进执行计划管理时,SPM会操作一个流程判断该执行计划是否是已知的,是已知的话,是否代价是最小的;不是已知的话,是否需要执行一下以判断该执行计划的优化程度。99.png

运维指令

PolarDB-X提供了丰富的指令集用于管理执行计划,语法如下:


BASELINE (LOAD|PERSIST|CLEAR|VALIDATE|LIST|DELETE) [Signed Integer,Signed Integer....]
BASELINE (ADD|FIX) SQL (HINT Select Statemtnt)
  • BASELINE (ADD|FIX) SQL  :将SQL以HINT修复过后的执行计划记录固定下来。BASELINE LOAD:将系统表中指定的基线信息刷新到内存并使其生效。BASELINE LOAD_PLAN:将系统表中指定的执行计划信息刷新到内存并使其生效。BASELINE LIST:列出当前所有的基线信息。BASELINE PERSIST:将指定的基线落盘。BASELINE PERSIST_PLAN:将指定的执行计划落盘。BASELINE CLEAR:内存中清理某个基线。BASELINE CLEAR_PLAN:内存中清理某个执行计划。BASELINE DELETE:磁盘中删除某个基线。BASELINE DELETE_PLAN:磁盘中删除某个执行计划。执行计划调优实战数据发生变化或PolarDB-X优化器引擎升级后,针对同一条SQL,有可能会出现更好的执行计划。SPM在自动演化时会将CBO优化自动发现的更优执行计划加入到SQL的基线中。除此以外,您也可以通过SPM的指令主动优化执行计划。正常EXPLAIN发现该SQL生成的执行计划使用的是Hash Join,并且在Baseline List的基线中,该SQL仅有这一个执行计划:假如这个SQL在某些条件下采用BKA Join(Lookup Join)会有更好的性能,那么首先需要想办法利用HINT引导PolarDB-X生成符合预期的执行计划。BKA Join的HINT格式为:通过EXPLAIN [HINT] [SQL]观察出来的执行计划是否符合预期:此时由于Hint的干预,Join的算法已修正为BKA Join。但是这并不会对基线造成变动,如果想以后每次遇到这条SQL都使用上面的计划,还需要将其加入到基线中。可以采用执行计划管理的Baseline Add指令为该SQL增加一个执行计划。这样就会同时有两套执行计划存在于该SQL的基线中,CBO优化器会根据代价选择一个执行计划执行。通过以上Baseline List指令展示出来的结果,可以看到基于BKA_JOIN的执行计划已增加到该 SQL的基线中。此时EXPLAIN这条SQL,发现随SQL中p_name LIKE ? 条件变化,PolarDB-X会选择不同的执行计划。如果想让PolarDB-X固定使用上述的执行计划(而非在两个中挑选一个),可以采用Baseline Fix指令强制PolarDB-X走指定的执行计划。Baseline Fix指令执行完后,可以看到BKA Join执行计划的Fix状态位已被置为1。此时就算不加HINT,任意条件下Explain这条SQL,都一定会采用这个执行计划。
相关实践学习
快速体验PolarDB开源数据库
本实验环境已内置PostgreSQL数据库以及PolarDB开源数据库:PolarDB PostgreSQL版和PolarDB分布式版,支持一键拉起使用,方便各位开发者学习使用。
相关文章
|
3天前
|
SQL 运维 监控
SQL查询太慢?实战讲解YashanDB SQL调优思路
本文是Meetup第十期“调优实战专场”的第二篇技术文章,上一篇《高效查询秘诀,解码YashanDB优化器分组查询优化手段》中,我们揭秘了YashanDB分组查询优化秘诀,本文将通过一个案例,助你快速上手YashanDB慢日志功能,精准定位“慢SQL”后进行优化。
|
12天前
|
SQL
【YashanDB 知识库】使用 leading hint 调整 SQL 执行计划后报错 YAS-04522 invalid hint leading
在 YashanDB 的所有版本中,使用 leading hint 调整 SQL 执行计划时可能出现“YAS-04522 invalid hint leading”错误,导致 SQL 无法正常执行。原因是 YashanDB 优化器的 Bug。解决方法为避免使用 leading hint。可通过创建测试表 a、b、c 并执行特定 SQL 语句来验证问题是否存在。
|
1月前
|
SQL 存储 关系型数据库
MySQL原理简介—10.SQL语句和执行计划
本文介绍了MySQL执行计划的相关概念及其优化方法。首先解释了什么是执行计划,它是SQL语句在查询时如何检索、筛选和排序数据的过程。接着详细描述了执行计划中常见的访问类型,如const、ref、range、index和all等,并分析了它们的性能特点。文中还探讨了多表关联查询的原理及优化策略,包括驱动表和被驱动表的选择。此外,文章讨论了全表扫描和索引的成本计算方法,以及MySQL如何通过成本估算选择最优执行计划。最后,介绍了explain命令的各个参数含义,帮助理解查询优化器的工作机制。通过这些内容,读者可以更好地理解和优化SQL查询性能。
|
1月前
|
SQL 关系型数据库 MySQL
MySQL进阶突击系列(07) 她气鼓鼓递来一条SQL | 怎么看执行计划、SQL怎么优化?
在日常研发工作当中,系统性能优化,从大的方面来看主要涉及基础平台优化、业务系统性能优化、数据库优化。面对数据库优化,除了DBA在集群性能、服务器调优需要投入精力,我们研发需要负责业务SQL执行优化。当业务数据量达到一定规模后,SQL执行效率可能就会出现瓶颈,影响系统业务响应。掌握如何判断SQL执行慢、以及如何分析SQL执行计划、优化SQL的技能,在工作中解决SQL性能问题显得非常关键。
|
5月前
|
SQL Oracle 关系型数据库
Oracle SQL:了解执行计划和性能调优
Oracle SQL:了解执行计划和性能调优
141 1
|
6月前
|
存储 SQL 关系型数据库
【MySQL调优】如何进行MySQL调优?从参数、数据建模、索引、SQL语句等方向,三万字详细解读MySQL的性能优化方案(2024版)
MySQL调优主要分为三个步骤:监控报警、排查慢SQL、MySQL调优。 排查慢SQL:开启慢查询日志 、找出最慢的几条SQL、分析查询计划 。 MySQL调优: 基础优化:缓存优化、硬件优化、参数优化、定期清理垃圾、使用合适的存储引擎、读写分离、分库分表; 表设计优化:数据类型优化、冷热数据分表等。 索引优化:考虑索引失效的11个场景、遵循索引设计原则、连接查询优化、排序优化、深分页查询优化、覆盖索引、索引下推、用普通索引等。 SQL优化。
921 15
【MySQL调优】如何进行MySQL调优?从参数、数据建模、索引、SQL语句等方向,三万字详细解读MySQL的性能优化方案(2024版)
|
6月前
|
SQL 分布式计算 数据库
SQL调优总结
数据库表的规范化和反规范化设计,设计合适的字段数据类型……
61 8
|
7月前
|
Java XML Maven
跨越时代的飞跃:Struts 2 升级秘籍——从旧版本无缝迁移到最新版,焕发应用新生!
【8月更文挑战第31天】随着软件技术的发展,Struts 2 框架也在不断更新。本文通过具体案例指导开发者如何从旧版平滑升级到 Struts 2.6.x。首先更新 `pom.xml` 中的依赖版本,并执行 `mvn clean install`。接着检查 `struts.xml` 配置,确保符合新版本要求,调整包扫描器等设置。审查 Action 类及其注解,检查配置文件中的弃用项及插件。更新自定义拦截器实现,并验证日志配置。最后,通过一系列测试确保升级后的系统正常运行。通过这些步骤,可以顺利完成 Struts 2 的版本升级,提升应用的安全性和性能。
1324 0
|
7月前
|
Java 开发者 前端开发
Struts 2、Spring MVC、Play Framework 上演巅峰之战,Web 开发的未来何去何从?
【8月更文挑战第31天】在Web应用开发中,Struts 2框架因强大功能和灵活配置备受青睐,但开发者常遇配置错误、类型转换失败、标签属性设置不当及异常处理等问题。本文通过实例解析常见难题与解决方案,如配置文件中遗漏`result`元素致页面跳转失败、日期格式不匹配需自定义转换器、`<s:checkbox>`标签缺少`label`属性致显示不全及Action中未捕获异常影响用户体验等,助您有效应对挑战。
128 0
|
7月前
|
SQL 监控 关系型数据库
SQL性能监控与调优工具的神奇之处:如何用最佳实践选择最适合你的那一个,让你的数据库飞起来?
【8月更文挑战第31天】在现代软件开发中,数据库性能监控与调优对应用稳定性至关重要。本文对比了数据库内置工具、第三方工具及云服务工具等几种常用SQL性能监控与调优工具,并通过示例代码展示了如何利用MySQL的EXPLAIN功能分析查询性能。选择最适合的工具需综合考虑功能需求、数据库类型及成本预算等因素。遵循了解工具功能、试用工具及定期维护工具等最佳实践,可帮助开发者更高效地管理和优化数据库性能,迎接未来软件开发中的挑战与机遇。
92 0