阿里云分布式关系型数据库 DRDS执行计划管理

基本概念

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

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

工作流程概览

如图所示，当 DRDS 收到一条查询 SQL 时，会经历以下流程：

对查询 SQL 进行参数化处理，将所有参数替换为占位符（?）
以参数化的 SQL 作为 Key，查找 Plan Cache 中是否有缓存；如果没有，则调用优化器进行优化
如果该 SQL 是简单查询，则直接执行，跳过 Plan Management 相关步骤
如果该 SQL 是复杂查询，则使用 Baseline 中固化的执行计划；如果有多个，则选择代价较低的那个

执行计划缓存

DRDS 默认开启 Plan Cache 功能。EXPLAIN 结果中的 HitCache 一项就表示当前 SQL 是否命中 Plan Cache。

开启 Plan Cache 后，DRDS 会对 SQL 做参数化处理，参数化会将 SQL 中的常量用占位符 ? 替换，并构建出相应的参数列表。在执行计划中也可以看到 LogicalView 算子的 SQL 中含有 ?。

执行计划管理

对于复杂 SQL，经过 Plan Cache 之后，还会经过 Plan Management 流程。

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

SQL 模版和最优的执行计划并非一一对应的，很大程度上，这会受到具体参数的影响：可能在某一组参数下，执行计划 A 是最优的，而另一组参数下，执行计划 B 更优。

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

计划选择

当 Plan Cache 中的执行计划走进 Plan Management 时，SPM 会操作一个流程判断该执行计划是否是已知的，是已知的话是否是 Cost 最小的；不是已知的话是否需要执行一下以判断该执行计划的优化程度。

运维指令

DRDS 提供了丰富的指令集用于管理执行计划，语法如下：

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 信息刷新到内存并使其生效
BASELINE LOAD_PLAN：将系统表中指定的 Plan 信息刷新到内存并使其生效
BASELINE LIST:列出当前所有的 Baseline 信息
BASELINE PERSIST: 将指定的 Baseline 落盘
BASELINE PERSIST_PLAN: 将指定的 Plan 落盘
BASELINE CLEAR：内存中清理某个 Baseline
BASELINE CLEAR_PLAN：内存中清理某个 Plan
BASELINE DELETE：磁盘中删除某个 Baseline
BASELINE DELETE_PLAN：磁盘中删除某个 Plan

执行计划调优实战

数据发生变化或 DRDS 优化器引擎升级后，针对同一条 SQL，有可能会出现更好的执行计划。SPM 在自动演化时会将 CBO 优化自动发现的更优执行计划加入到 SQL 的 Baseline 中；除此以外，用户也可以通过 SPM 的指令主动优化执行计划。

例如以下的 SQL：

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

正常 EXPLAIN 发现该 SQL 生成的执行计划使用的是 Hash Join，并且 Baseline List 出来的 Baseline 中，该 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 引导 DRDS 生成符合预期的执行计划（关于 HINT）。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。但是这并不会对 Baseline 造成变动，如果想以后每次遇到这条 SQL 都使用上面的计划，还需要将其加入到 Baseline 中。

可以采用 Plan Management 的 Baseline Add 指令为该 SQL 增加一个执行计划。这样就会同时有两套执行计划存在于该 SQL 的 Baseline 中，CBO 优化器会根据 Cost 选择一个执行计划执行。

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 的 Baseline 中。此时 EXPLAIN 这条 SQL，发现随 SQL 中 p_name LIKE ? 条件变化，DRDS 会选择不同的执行计划。

如果想让 DRDS 固定使用上述的执行计划（而非在两个中挑选一个），可以采用 Baseline Fix 指令强制 DRDS 走指定的执行计划。


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)

原创文章，作者：网友投稿，如若转载，请注明出处：https://www.cloudads.cn/archives/33886.html