【SOP 系列 12】如何在 OceanBase 中使用 Outline 来固定执行计划

木芯心 · 2022 年6 月 23 日 15:29

1、为什么需要使用 Outline 固定执行计划

正常情况下 SQL 优化器都会帮我们自动选择一个最优的执行计划，但是当 SQL 的执行计划在统计数据不准确的情况下，可能选择的执行计划就不是最优的，从而引起执行性能降低。
业务已上线，发现 SQL 性能问题，且无法直接修改业务 SQL 代码的情况。

总之，在 OceanBase 中我们使用 outline，无需业务进行 SQL 更改，而是通过 DDL 操作将一组 hint 加入到 SQL 中，从而使优化器根据指定的一组 hint，对该 SQL 生成更优的执行计划。我们将这样的 hint 称为 outline，通过对某条 SQL 创建 outline 来实现固定执行计划的目的。

2、如何使用 Outline 固定执行计划

2.1 创建 Outline

在 OceanBase 里创建 outline 有两种方式：

通过 sql_text 创建（sql_text 是没有参数化的, 也就是常量没有替换为?，即执行的原始 SQL)。
通过 sql_id 创建（sql_id 是将 sql_text 中常量替换为 ? 后进行 md5 生成的)。

当 sql_id 相同时，使用 sql_text 创建的 outline 会覆盖使用 sql_id 创建的 outline，即使用 sql_text 的优先级更高。

sql_text 方式是因为是按照原始 SQL 文本严格匹配的，sql_id 方式是快速参数化后的 SQL 文本严格匹配的。可能因为一些大小写/空白(格)/换行的情况，无法按照预期固定执行计划，因此在生产环境推荐使用 sql_id 来创建 outline。

创建测试表

mysql> show create table test_order \G
*************************** 1. row ***************************
       Table: test_order
Create Table: CREATE TABLE `test_order` (
  `order_id` int(11) NOT NULL,
  `user` varchar(20) DEFAULT NULL,
  `status` int(11) DEFAULT NULL,
  `col4` varchar(64) DEFAULT NULL,
  `col5` varchar(64) DEFAULT NULL,
  `col6` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`order_id`),
  KEY `idx_status` (`status`) BLOCK_SIZE 16384 LOCAL
) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = COMPACT COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 3 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0
1 row in set (0.01 sec)

比如有一个如下的查询：

select count(*) from test_order where order_id>0;

说明： 我们不用过多的考虑该 SQL 实际的意义，这里旨在模拟出通过创建 outline 后，可以按照我们的预期固定想要的执行计划。

触发查询一次我们目标的查询 SQL，这一步的目的是后续创建 outline 的时候，获取对应的 sql_id 。

select count(*) from test_order where order_id>0;

select tenant_id,svr_ip,svr_port,sql_id, plan_id, statement, outline_id, outline_data
from oceanbase.gv$plan_cache_plan_stat 
where statement like 'select count(*) from test_order where order_id>?'
and statement not like '%plan_cache_plan_stat%'\G

*************************** 1. row ***************************
   tenant_id: 1001
      svr_ip: 172.30.199.111
    svr_port: 2882
      sql_id: 29F485C4A509417392C7C49DC8B5F379
     plan_id: 423
   statement: select count(*) from test_order where order_id>?
  outline_id: -1
outline_data: /*+ BEGIN_OUTLINE_DATA FULL(@"SEL$1" "db1.test_order"@"SEL$1") END_OUTLINE_DATA*/
1 row in set (0.04 sec)

确认该目标查询 SQL 的物理执行计划

select operator, name from oceanbase.gv$plan_cache_plan_explain 
WHERE tenant_id = 1001 and ip = '172.30.199.111' 
AND port = 2882 and plan_id = 423;

+----------------------+------------+
| operator             | name       |
+----------------------+------------+
| PHY_SCALAR_AGGREGATE | NULL       |
|  PHY_TABLE_SCAN      | test_order |
+----------------------+------------+
2 rows in set (0.00 sec)

基于上面获取的 sql_id（29F485C4A509417392C7C49DC8B5F379）来创建 outline。

说明，在创建 outline 的时候需要切换到对应的对应的 database 下。

use db1;

create outline otl_test_order_idx_status
on "29F485C4A509417392C7C49DC8B5F379" 
using hint /*+ index(test_order idx_status) */ ;

2.2 查询 Outline

mysql> select * from oceanbase.gv$outline \G
*************************** 1. row ***************************
        tenant_id: 1001
      database_id: 1100611139404827
       outline_id: 1100611139404787
    database_name: db1
     outline_name: otl_test_order_idx_status
visible_signature:
         sql_text:
   outline_target:
      outline_sql:
1 row in set (0.01 sec)

2.3 验证 Outline

select tenant_id,svr_ip,svr_port,sql_id, plan_id, statement, outline_id, outline_data
from oceanbase.gv$plan_cache_plan_stat 
where statement like 'select count(*) from test_order where order_id>?'
and statement not like '%plan_cache_plan_stat%'\G

*************************** 1. row ***************************
   tenant_id: 1001
      svr_ip: 172.30.199.111
    svr_port: 2882
      sql_id: 29F485C4A509417392C7C49DC8B5F379
     plan_id: 435
   statement: select count(*) from test_order where order_id>?
  outline_id: 1100611139404787
outline_data: /*+ BEGIN_OUTLINE_DATA INDEX(@"SEL$1" "db1.test_order"@"SEL$1" "idx_status") END_OUTLINE_DATA*/
1 row in set (0.02 sec)

outline_id 字段不为“-1” 说明已经固定了执行计划。

再次确认目标 SQL 的物理执行计划，执行的目标 SQL 跟之前的一样，但是对比之下计划，已经按照我们的预期走了索引扫描。

select operator, name from oceanbase.gv$plan_cache_plan_explain 
WHERE tenant_id = 1001 and ip = '172.30.199.111' 
AND port = 2882 and plan_id = 435;

+----------------------+------------------------+
| operator             | name                   |
+----------------------+------------------------+
| PHY_SCALAR_AGGREGATE | NULL                   |
|  PHY_TABLE_SCAN      | test_order(idx_status) |
+----------------------+------------------------+
2 rows in set (0.00 sec)

2.4 删除 Outline

删除 outline 跟创建 outline 一样，也需要切换到对应的 database 下执行。

mysql> use db2;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> drop outline otl_test_order_idx_status;
ERROR 5265 (HY000): Outline 'db2.otl_test_order_idx_status' doesn't exist

mysql> use db1;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> drop outline otl_test_order_idx_status;
Query OK, 0 rows affected (0.02 sec)

mysql> select * from oceanbase.gv$outline;
Empty set (0.00 sec)