1. 星速云首页
  2. 阿里云文档

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

基本概念

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

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

工作流程概览

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

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

x

执行计划缓存

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

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

x

执行计划管理

对于复杂 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 最小的;不是已知的话是否需要执行一下以判断该执行计划的优化程度。

x

运维指令

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

  1. BASELINE (LOAD|PERSIST|CLEAR|VALIDATE|LIST|DELETE) [Signed Integer,Signed Integer....]
  2. 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:

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

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

  1. mysql> explain select * from lineitem join part on l_partkey=p_partkey where p_name like '%geen%';
  2. +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  3. | LOGICAL PLAN |
  4. +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  5. | Gather(parallel=true) |
  6. | ParallelHashJoin(condition="l_partkey = p_partkey", type="inner") |
  7. | 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) |
  8. | 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) |
  9. | HitCache:true |
  10. | |
  11. | |
  12. +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  13. 7 rows in set (0.06 sec)
  14. mysql> baseline list;
  15. +-------------+--------------------------------------------------------------------------------+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------+----------+
  16. | BASELINE_ID | PARAMETERIZED_SQL | PLAN_ID | EXTERNALIZED_PLAN | FIXED | ACCEPTED |
  17. +-------------+--------------------------------------------------------------------------------+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------+----------+
  18. | -399023558 | SELECT *
  19. FROM lineitem
  20. JOIN part ON l_partkey = p_partkey
  21. WHERE p_name LIKE ? | -935671684 |
  22. Gather(parallel=true)
  23. ParallelHashJoin(condition="l_partkey = p_partkey", type="inner")
  24. 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)
  25. 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)
  26. | 0 | 1 |
  27. +-------------+--------------------------------------------------------------------------------+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------+----------+
  28. 1 row in set (0.02 sec)

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

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

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

  1. mysql> explain /*+TDDL:bka_join(lineitem, part)*/ select * from lineitem join part on l_partkey=p_partkey where p_name like '%geen%';
  2. +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  3. | LOGICAL PLAN |
  4. +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  5. | Gather(parallel=true) |
  6. | ParallelBKAJoin(condition="l_partkey = p_partkey", type="inner") |
  7. | 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) |
  8. | Gather(concurrent=true) |
  9. | 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 ?)") |
  10. | HitCache:false |
  11. | |
  12. | |
  13. +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  14. 8 rows in set (0.14 sec)

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

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

  1. mysql> baseline add sql /*+TDDL:bka_join(lineitem, part)*/ select * from lineitem join part on l_partkey=p_partkey where p_name like '%geen%';
  2. +-------------+--------+
  3. | BASELINE_ID | STATUS |
  4. +-------------+--------+
  5. | -399023558 | OK |
  6. +-------------+--------+
  7. 1 row in set (0.09 sec)
  8. mysql> baseline list;
  9. +-------------+--------------------------------------------------------------------------------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------+----------+
  10. | BASELINE_ID | PARAMETERIZED_SQL | PLAN_ID | EXTERNALIZED_PLAN | FIXED | ACCEPTED |
  11. +-------------+--------------------------------------------------------------------------------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------+----------+
  12. | -399023558 | SELECT *
  13. FROM lineitem
  14. JOIN part ON l_partkey = p_partkey
  15. WHERE p_name LIKE ? | -1024543942 |
  16. Gather(parallel=true)
  17. ParallelBKAJoin(condition="l_partkey = p_partkey", type="inner")
  18. 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)
  19. Gather(concurrent=true)
  20. 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 ?)")
  21. | 0 | 1 |
  22. | -399023558 | SELECT *
  23. FROM lineitem
  24. JOIN part ON l_partkey = p_partkey
  25. WHERE p_name LIKE ? | -935671684 |
  26. Gather(parallel=true)
  27. ParallelHashJoin(condition="l_partkey = p_partkey", type="inner")
  28. 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)
  29. 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)
  30. | 0 | 1 |
  31. +-------------+--------------------------------------------------------------------------------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------+----------+
  32. 2 rows in set (0.03 sec)

通过以上 Baseline List 指令展示出来的结果,可以看到基于 BKA_JOIN 的执行计划已增加到该 SQL 的 Baseline 中。此时 EXPLAIN 这条 SQL,发现随 SQL 中 p_name LIKE ? 条件变化,DRDS 会选择不同的执行计划。

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

  1. mysql> baseline fix sql /*+TDDL:bka_join(lineitem, part)*/ select * from lineitem join part on l_partkey=p_partkey where p_name like '%geen%';
  2. +-------------+--------+
  3. | BASELINE_ID | STATUS |
  4. +-------------+--------+
  5. | -399023558 | OK |
  6. +-------------+--------+
  7. 1 row in set (0.07 sec)
  8. mysql> baseline list\G
  9. *************************** 1. row ***************************
  10. BASELINE_ID: -399023558
  11. PARAMETERIZED_SQL: SELECT *
  12. FROM lineitem
  13. JOIN part ON l_partkey = p_partkey
  14. WHERE p_name LIKE ?
  15. PLAN_ID: -1024543942
  16. EXTERNALIZED_PLAN:
  17. Gather(parallel=true)
  18. ParallelBKAJoin(condition="l_partkey = p_partkey", type="inner")
  19. 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)
  20. Gather(concurrent=true)
  21. 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 ?)")
  22. FIXED: 1
  23. ACCEPTED: 1
  24. *************************** 2. row ***************************
  25. BASELINE_ID: -399023558
  26. PARAMETERIZED_SQL: SELECT *
  27. FROM lineitem
  28. JOIN part ON l_partkey = p_partkey
  29. WHERE p_name LIKE ?
  30. PLAN_ID: -935671684
  31. EXTERNALIZED_PLAN:
  32. Gather(parallel=true)
  33. ParallelHashJoin(condition="l_partkey = p_partkey", type="inner")
  34. 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)
  35. 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)
  36. FIXED: 0
  37. ACCEPTED: 1
  38. 2 rows in set (0.01 sec)

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

  1. mysql> explain select * from lineitem join part on l_partkey=p_partkey where p_name like '%green%';
  2. +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  3. | LOGICAL PLAN |
  4. +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  5. | Gather(parallel=true) |
  6. | ParallelBKAJoin(condition="l_partkey = p_partkey", type="inner") |
  7. | 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) |
  8. | Gather(concurrent=true) |
  9. | 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 ?)") |
  10. | HitCache:true |
  11. +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  12. 8 rows in set (0.01 sec)

注明:本文为星速云原创版权所有,禁止转载,一经发现将追究版权责任!

发表评论

登录后才能评论
问答社区 QQ客服
微信客服
微信客服
分享本页
返回顶部