PolarDB-X 1.0-用户指南-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)中固化的执行计划;如果有多个,则选择代价最低的那个。

p162947.png

执行计划缓存

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

p162950.png

执行计划管理

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

执行计划缓存和执行计划管理都是采用参数化后的SQL作为Key来执行计划。执行计划缓存中会缓存所有SQL的执行计划,而执行计划管理仅对复杂查询SQL进行处理。

由于受到具体参数的影响,SQL模版和最优的执行计划并非一一对应的。

在执行计划管理中,每一条SQL对应一个基线,每个基线中包含一个或多个执行计划。实际使用中,会根据当时的参数选择其中代价最小的执行计划来执行。

计划选择

当执行计划缓存中的执行计划走进执行计划管理时,SPM会操作一个流程判断该执行计划是否是已知的,是已知的话,是否代价是最小的;不是已知的话,是否需要执行一下以判断该执行计划的优化程度。

p162952.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 <HINT> <Select Statement>:将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的指令主动优化执行计划。

例如以下的SQL:

SELECT *
FROM lineitem JOIN part ON l_partkey=p_partkey
WHERE p_name LIKE '%green%';

正常EXPLAIN发现该SQL生成的执行计划使用的是Hash Join,并且在Baseline List的基线中,该SQL仅有这一个执行计划:

mysql> explain select * from lineitem join part on l_partkey=p_partkey where p_name like '%geen%';
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| LOGICAL PLAN                                                                                                                                                                                                                                                                                                                                                   |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Gather(parallel=true)                                                                                                                                                                                                                                                                                                                                          |
|   ParallelHashJoin(condition="l_partkey = p_partkey", type="inner")                                                                                                                                                                                                                                                                                            |
|     LogicalView(tables="[00-03].lineitem", shardCount=4, sql="SELECT `l_orderkey`, `l_partkey`, `l_suppkey`, `l_linenumber`, `l_quantity`, `l_extendedprice`, `l_discount`, `l_tax`, `l_returnflag`, `l_linestatus`, `l_shipdate`, `l_commitdate`, `l_receiptdate`, `l_shipinstruct`, `l_shipmode`, `l_comment` FROM `lineitem` AS `lineitem`", parallel=true) |
|     LogicalView(tables="[00-03].part", shardCount=4, sql="SELECT `p_partkey`, `p_name`, `p_mfgr`, `p_brand`, `p_type`, `p_size`, `p_container`, `p_retailprice`, `p_comment` FROM `part` AS `part` WHERE (`p_name` LIKE ?)", parallel=true)                                                                                                                    |
| HitCache:true                                                                                                                                                                                                                                                                                                                                                  |
|                                                                                                                                                                                                                                                                                                                                                                |
|                                                                                                                                                                                                                                                                                                                                                                |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
7 rows in set (0.06 sec)
mysql> baseline list;
+-------------+--------------------------------------------------------------------------------+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------+----------+
| BASELINE_ID | PARAMETERIZED_SQL                                                              | PLAN_ID    | EXTERNALIZED_PLAN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      | FIXED | ACCEPTED |
+-------------+--------------------------------------------------------------------------------+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------+----------+
|  -399023558 | SELECT *
FROM lineitem
    JOIN part ON l_partkey = p_partkey
WHERE p_name LIKE ? | -935671684 |
Gather(parallel=true)
  ParallelHashJoin(condition="l_partkey = p_partkey", type="inner")
    LogicalView(tables="[00-03].lineitem", shardCount=4, sql="SELECT `l_orderkey`, `l_partkey`, `l_suppkey`, `l_linenumber`, `l_quantity`, `l_extendedprice`, `l_discount`, `l_tax`, `l_returnflag`, `l_linestatus`, `l_shipdate`, `l_commitdate`, `l_receiptdate`, `l_shipinstruct`, `l_shipmode`, `l_comment` FROM `lineitem` AS `lineitem`", parallel=true)
    LogicalView(tables="[00-03].part", shardCount=4, sql="SELECT `p_partkey`, `p_name`, `p_mfgr`, `p_brand`, `p_type`, `p_size`, `p_container`, `p_retailprice`, `p_comment` FROM `part` AS `part` WHERE (`p_name` LIKE ?)", parallel=true)
 |     0 |        1 |
+-------------+--------------------------------------------------------------------------------+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------+----------+
1 row in set (0.02 sec)

假如这个SQL在某些条件下采用BKA Join(Lookup Join)会有更好的性能,那么首先需要想办法利用HINT引导PolarDB-X生成符合预期的执行计划。BKA Join的HINT格式为:

/*+TDDL:BKA_JOIN(lineitem, part)*/

通过EXPLAIN [HINT] [SQL]观察出来的执行计划是否符合预期:

mysql> explain /*+TDDL:bka_join(lineitem, part)*/ select * from lineitem join part on l_partkey=p_partkey where p_name like '%geen%';
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| LOGICAL PLAN                                                                                                                                                                                                                                                                                                                                                   |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Gather(parallel=true)                                                                                                                                                                                                                                                                                                                                          |
|   ParallelBKAJoin(condition="l_partkey = p_partkey", type="inner")                                                                                                                                                                                                                                                                                             |
|     LogicalView(tables="[00-03].lineitem", shardCount=4, sql="SELECT `l_orderkey`, `l_partkey`, `l_suppkey`, `l_linenumber`, `l_quantity`, `l_extendedprice`, `l_discount`, `l_tax`, `l_returnflag`, `l_linestatus`, `l_shipdate`, `l_commitdate`, `l_receiptdate`, `l_shipinstruct`, `l_shipmode`, `l_comment` FROM `lineitem` AS `lineitem`", parallel=true) |
|     Gather(concurrent=true)                                                                                                                                                                                                                                                                                                                                    |
|       LogicalView(tables="[00-03].part", shardCount=4, sql="SELECT `p_partkey`, `p_name`, `p_mfgr`, `p_brand`, `p_type`, `p_size`, `p_container`, `p_retailprice`, `p_comment` FROM `part` AS `part` WHERE (`p_name` LIKE ?)")                                                                                                                                 |
| HitCache:false                                                                                                                                                                                                                                                                                                                                                 |
|                                                                                                                                                                                                                                                                                                                                                                |
|                                                                                                                                                                                                                                                                                                                                                                |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
8 rows in set (0.14 sec)

注意此时由于Hint的干预,Join的算法已修正为BKA Join。但是这并不会对基线造成变动,如果想以后每次遇到这条SQL都使用上面的计划,还需要将其加入到基线中。

可以采用执行计划管理的Baseline Add指令为该SQL增加一个执行计划。这样就会同时有两套执行计划存在于该SQL的基线中,CBO优化器会根据代价选择一个执行计划执行。

mysql> baseline add sql /*+TDDL:bka_join(lineitem, part)*/ select * from lineitem join part on l_partkey=p_partkey where p_name like '%geen%';
+-------------+--------+
| BASELINE_ID | STATUS |
+-------------+--------+
|  -399023558 | OK     |
+-------------+--------+
1 row in set (0.09 sec)
mysql> baseline list;
+-------------+--------------------------------------------------------------------------------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------+----------+
| BASELINE_ID | PARAMETERIZED_SQL                                                              | PLAN_ID     | EXTERNALIZED_PLAN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    | FIXED | ACCEPTED |
+-------------+--------------------------------------------------------------------------------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------+----------+
|  -399023558 | SELECT *
FROM lineitem
    JOIN part ON l_partkey = p_partkey
WHERE p_name LIKE ? | -1024543942 |
Gather(parallel=true)
  ParallelBKAJoin(condition="l_partkey = p_partkey", type="inner")
    LogicalView(tables="[00-03].lineitem", shardCount=4, sql="SELECT `l_orderkey`, `l_partkey`, `l_suppkey`, `l_linenumber`, `l_quantity`, `l_extendedprice`, `l_discount`, `l_tax`, `l_returnflag`, `l_linestatus`, `l_shipdate`, `l_commitdate`, `l_receiptdate`, `l_shipinstruct`, `l_shipmode`, `l_comment` FROM `lineitem` AS `lineitem`", parallel=true)
    Gather(concurrent=true)
      LogicalView(tables="[00-03].part", shardCount=4, sql="SELECT `p_partkey`, `p_name`, `p_mfgr`, `p_brand`, `p_type`, `p_size`, `p_container`, `p_retailprice`, `p_comment` FROM `part` AS `part` WHERE (`p_name` LIKE ?)")
 |     0 |        1 |
|  -399023558 | SELECT *
FROM lineitem
    JOIN part ON l_partkey = p_partkey
WHERE p_name LIKE ? |  -935671684 |
Gather(parallel=true)
  ParallelHashJoin(condition="l_partkey = p_partkey", type="inner")
    LogicalView(tables="[00-03].lineitem", shardCount=4, sql="SELECT `l_orderkey`, `l_partkey`, `l_suppkey`, `l_linenumber`, `l_quantity`, `l_extendedprice`, `l_discount`, `l_tax`, `l_returnflag`, `l_linestatus`, `l_shipdate`, `l_commitdate`, `l_receiptdate`, `l_shipinstruct`, `l_shipmode`, `l_comment` FROM `lineitem` AS `lineitem`", parallel=true)
    LogicalView(tables="[00-03].part", shardCount=4, sql="SELECT `p_partkey`, `p_name`, `p_mfgr`, `p_brand`, `p_type`, `p_size`, `p_container`, `p_retailprice`, `p_comment` FROM `part` AS `part` WHERE (`p_name` LIKE ?)", parallel=true)
               |     0 |        1 |
+-------------+--------------------------------------------------------------------------------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------+----------+
2 rows in set (0.03 sec)

通过以上Baseline List指令展示出来的结果,可以看到基于BKA_JOIN的执行计划已增加到该 SQL的基线中。此时EXPLAIN这条SQL,发现随SQL中p_name LIKE ? 条件变化,PolarDB-X会选择不同的执行计划。如果想让PolarDB-X固定使用上述的执行计划(而非在两个中挑选一个),可以采用Baseline Fix指令强制PolarDB-X走指定的执行计划。

mysql> baseline fix sql /*+TDDL:bka_join(lineitem, part)*/ select * from lineitem join part on l_partkey=p_partkey where p_name like '%geen%';
+-------------+--------+
| BASELINE_ID | STATUS |
+-------------+--------+
|  -399023558 | OK     |
+-------------+--------+
1 row in set (0.07 sec)
mysql> baseline list\G
*************************** 1. row ***************************
      BASELINE_ID: -399023558
PARAMETERIZED_SQL: SELECT *
FROM lineitem
    JOIN part ON l_partkey = p_partkey
WHERE p_name LIKE ?
          PLAN_ID: -1024543942
EXTERNALIZED_PLAN:
Gather(parallel=true)
  ParallelBKAJoin(condition="l_partkey = p_partkey", type="inner")
    LogicalView(tables="[00-03].lineitem", shardCount=4, sql="SELECT `l_orderkey`, `l_partkey`, `l_suppkey`, `l_linenumber`, `l_quantity`, `l_extendedprice`, `l_discount`, `l_tax`, `l_returnflag`, `l_linestatus`, `l_shipdate`, `l_commitdate`, `l_receiptdate`, `l_shipinstruct`, `l_shipmode`, `l_comment` FROM `lineitem` AS `lineitem`", parallel=true)
    Gather(concurrent=true)
      LogicalView(tables="[00-03].part", shardCount=4, sql="SELECT `p_partkey`, `p_name`, `p_mfgr`, `p_brand`, `p_type`, `p_size`, `p_container`, `p_retailprice`, `p_comment` FROM `part` AS `part` WHERE (`p_name` LIKE ?)")
            FIXED: 1
         ACCEPTED: 1
*************************** 2. row ***************************
      BASELINE_ID: -399023558
PARAMETERIZED_SQL: SELECT *
FROM lineitem
    JOIN part ON l_partkey = p_partkey
WHERE p_name LIKE ?
          PLAN_ID: -935671684
EXTERNALIZED_PLAN:
Gather(parallel=true)
  ParallelHashJoin(condition="l_partkey = p_partkey", type="inner")
    LogicalView(tables="[00-03].lineitem", shardCount=4, sql="SELECT `l_orderkey`, `l_partkey`, `l_suppkey`, `l_linenumber`, `l_quantity`, `l_extendedprice`, `l_discount`, `l_tax`, `l_returnflag`, `l_linestatus`, `l_shipdate`, `l_commitdate`, `l_receiptdate`, `l_shipinstruct`, `l_shipmode`, `l_comment` FROM `lineitem` AS `lineitem`", parallel=true)
    LogicalView(tables="[00-03].part", shardCount=4, sql="SELECT `p_partkey`, `p_name`, `p_mfgr`, `p_brand`, `p_type`, `p_size`, `p_container`, `p_retailprice`, `p_comment` FROM `part` AS `part` WHERE (`p_name` LIKE ?)", parallel=true)
            FIXED: 0
         ACCEPTED: 1
2 rows in set (0.01 sec)

Baseline Fix指令执行完后,可以看到BKA Join执行计划的Fix状态位已被置为1。此时就算不加HINT,任意条件下Explain这条SQL,都一定会采用这个执行计划。

mysql> explain select * from lineitem join part on l_partkey=p_partkey where p_name like '%green%';
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| LOGICAL PLAN                                                                                                                                                                                                                                                                                                                                                   |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Gather(parallel=true)                                                                                                                                                                                                                                                                                                                                          |
|   ParallelBKAJoin(condition="l_partkey = p_partkey", type="inner")                                                                                                                                                                                                                                                                                             |
|     LogicalView(tables="[00-03].lineitem", shardCount=4, sql="SELECT `l_orderkey`, `l_partkey`, `l_suppkey`, `l_linenumber`, `l_quantity`, `l_extendedprice`, `l_discount`, `l_tax`, `l_returnflag`, `l_linestatus`, `l_shipdate`, `l_commitdate`, `l_receiptdate`, `l_shipinstruct`, `l_shipmode`, `l_comment` FROM `lineitem` AS `lineitem`", parallel=true) |
|     Gather(concurrent=true)                                                                                                                                                                                                                                                                                                                                    |
|       LogicalView(tables="[00-03].part", shardCount=4, sql="SELECT `p_partkey`, `p_name`, `p_mfgr`, `p_brand`, `p_type`, `p_size`, `p_container`, `p_retailprice`, `p_comment` FROM `part` AS `part` WHERE (`p_name` LIKE ?)")                                                                                                                                 |
| HitCache:true                                                                                                                                                                                                                                                                                                                                                  |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
8 rows in set (0.01 sec)
相关实践学习
快速体验PolarDB开源数据库
本实验环境已内置PostgreSQL数据库以及PolarDB开源数据库:PolarDB PostgreSQL版和PolarDB分布式版,支持一键拉起使用,方便各位开发者学习使用。
相关文章
|
5月前
|
SQL 数据库 开发者
MSSQL性能调优实战:索引策略、SQL优化与并发管理深度剖析
在Microsoft SQL Server(MSSQL)的性能调优过程中,索引策略、SQL查询优化以及并发管理是关键的三大支柱
|
6月前
|
SQL PHP
thinkphp之进阶sql语法,持续更新
thinkphp之进阶sql语法,持续更新
39 0
|
5月前
|
关系型数据库 分布式数据库 数据库
PolarDB产品使用问题之将RDS切换到PolarDB-X 2.0时,代码层的SQL该如何改动
PolarDB产品使用合集涵盖了从创建与管理、数据管理、性能优化与诊断、安全与合规到生态与集成、运维与支持等全方位的功能和服务,旨在帮助企业轻松构建高可用、高性能且易于管理的数据库环境,满足不同业务场景的需求。用户可以通过阿里云控制台、API、SDK等方式便捷地使用这些功能,实现数据库的高效运维与持续优化。
|
5月前
|
SQL 数据采集 数据管理
SQL数据:探索、管理与优化的全面解析
在信息化时代,数据成为企业核心资产。本文探讨SQL在数据探索、管理与优化中的作用:使用DESC、SELECT了解数据集;评估数据质量;发现数据特征。管理方面,涵盖数据存储、检索、更新与维护。优化则涉及索引、查询及数据库设计,确保高性能和效率。掌握SQL能有效挖掘数据价值,支持企业决策与创新。
104 1
|
5月前
|
SQL 安全 Go
SQL注入不可怕,XSS也不难防!Python Web安全进阶教程,让你安心做开发!
【7月更文挑战第26天】在 Web 开发中, SQL 注入与 XSS 攻击常令人担忧, 但掌握正确防御策略可化解风险. 对抗 SQL 注入的核心是避免直接拼接用户输入至 SQL 语句. 使用 Python 的参数化查询 (如 sqlite3 库) 和 ORM 框架 (如 Django, SQLAlchemy) 可有效防范. 防范 XSS 攻击需严格过滤及转义用户输入. 利用 Django 模板引擎自动转义功能, 或手动转义及设置内容安全策略 (CSP) 来增强防护. 掌握这些技巧, 让你在 Python Web 开发中更加安心. 安全是个持续学习的过程, 不断提升才能有效保护应用.
59 1
|
5月前
|
SQL 监控 数据库
MSSQL性能调优实战:索引策略优化、SQL查询重写与高效并发管理的具体技巧
在Microsoft SQL Server(MSSQL)的性能调优过程中,索引策略的优化、SQL查询的重写以及高效并发管理是关键环节
|
5月前
|
SQL 运维 监控
MSSQL性能调优实战:索引策略优化、SQL查询重写与智能锁管理
在Microsoft SQL Server(MSSQL)的运维中,性能调优是确保数据库高效运行、满足业务需求的关键环节
|
5月前
|
SQL 运维 监控
MSSQL性能调优深度解析:索引优化策略、SQL查询优化技巧与高效并发管理实践
在Microsoft SQL Server(MSSQL)的运维与优化领域,性能调优是确保数据库高效运行、满足业务需求的关键环节
|
5月前
|
SQL 存储 监控
MSSQL性能调优深度解析:索引策略优化、SQL语句精炼与并发管理技巧
在Microsoft SQL Server(MSSQL)的性能调优领域,索引策略的优化、SQL语句的精炼以及高效的并发管理技巧是提升数据库性能不可或缺的三大方面
|
5月前
|
SQL 运维 监控
MSSQL性能调优深度探索:索引策略、SQL优化技巧与高效并发管理
在Microsoft SQL Server(MSSQL)的运维与优化领域,性能调优是确保数据库高效、稳定运行的核心任务

相关产品

  • 云原生分布式数据库 PolarDB-X