如何使用和管理Statement Outline - PolarDB

生產環境中，SQL語句的執行計畫經常發生改變，導致資料庫不穩定。PolarDB利用Optimizer Hints和Index Hints讓MySQL穩定執行計畫，該方法稱為Statement Outline，並提供了工具包DBMS_OUTLN方便您快捷使用。本文將介紹如何使用和管理Statement Outline。

適用範圍

PolarDB叢集版本需為如下版本之一：

PolarDB MySQL版5.6版本且小版本為5.6.1.0.36或以上。
PolarDB MySQL版5.7版本且小版本為5.7.1.0.2或以上。
PolarDB MySQL版8.0.1版本且小版本為8.0.1.1.1或以上。
PolarDB MySQL版8.0.2版本。

您可以通過查詢版本號碼來確認叢集版本。

快速入門

通過Optimizer Hint指定索引

文法

說明

由於5.6版本不支援Hint，指定索引可以通過使用Index Outline來實現。

/*+ INDEX(table_name idx) */

樣本

CALL dbms_outln.add_optimizer_outline('test', '/*+ INDEX(t1 i_a) */', 'SELECT test.t1.a AS a FROM test.t1');

通過Index Hint指定索引

文法

說明

通過使用USE或FORCE INDEX的方式指定索引，通常適用於不支援Hint的5.6版本。

CALL dbms_outln.add_index_outline('<Schema_name>','<Digest>',<Position>,'<Type>','<Hint>','<Scope>','<Query>');

參數說明

Position參數用於指定Outline所影響的表。根據SQL文本中出現的順序，Position從1開始，Hint參數作用在第幾張表上，Position就是幾。
Digest參數大部分時候輸入Null 字元串即可，詳細資料請參見add_index_outline。

樣本

CALL dbms_outln.add_index_outline('outline_db', '', 1, 'USE INDEX', 'ind_1', '',
                                  "SELECT * FROM t1 WHERE t1.col1 =1 AND t1.col2 ='xpchild'");

指定JOIN順序

文法

說明

該Hint可以強制指定JOIN的順序。並不需要所有表都出現在Hint中，被指定的表將優先進行JOIN，後續的表則由最佳化器自動決定。

/*+ JOIN_PREFIX(t1, t2, ...) */

樣本

CALL dbms_outln.add_optimizer_outline('outline_db', '/*+ JOIN_PREFIX(it1, it2) */',
                                      'SELECT it3.id3, it2.i2, it1.id2
                                       FROM t3 it3, t1 it1, t2 it2
                                       WHERE it3.i3 = it1.id1
                                       AND it2.id2 = it1.id2
                                       GROUP BY it3.id3, it1.id2
                                       ) t, t2 ot
                                       WHERE ot.id2 = t.id2');

對語句單獨設定變數

文法

說明

可以設定只在該語句中生效的變數值。

/*+ SET_VAR(<var_name>=<var_value>) */

樣本

CALL dbms_outln.add_optimizer_outline('test', '/*+ SET_VAR(max_execution_time=1) */',
                                      'SELECT * FROM t1 ');

手動指定行列分流

在存在列存索引唯讀節點的叢集中，可以通過Outline指定的語句在行存唯讀節點和列存索引唯讀節點上執行。

Hint格式：

# 強制語句使用列存
/*+ SET_VAR(cost_threshold_for_imci=0) */

# 強制語句使用行存
/*+ SET_VAR(use_imci_engine=OFF) */

Outline：

# 強制語句使用列存索引唯讀節點
CALL dbms_outln.add_optimizer_outline('test', '/*+ SET_VAR(cost_threshold_for_imci=0) */', 'SELECT test.t1.a AS a FROM test.t1');

# 強制語句使用行存唯讀節點
CALL dbms_outln.add_optimizer_outline('test', '/*+ SET_VAR(use_imci_engine=OFF) */', 'SELECT test.t1.a AS a FROM test.t1');

功能簡介

Statement Outline支援官方MySQL 8.0的所有Hint類型，分為如下兩類：

Optimizer Hints
根據範圍和Hint對象，分為Table-Level Optimizer hints、Index-Level Optimizer hints、Join-Order Optimizer hints等，詳情請參見Optimizer Hints。
說明
PolarDB MySQL版5.6版本暫不支援使用Optimizer Hints。
Index Hints
根據Index Hints的類型和範圍進行分類，詳情請參見Index Hints。

參數說明

您可以登入PolarDB控制台，在參數配置頁面通過設定參數來啟用或禁用Statement Outline或分表Outline（Sharding Outline）功能。

參數

層級

說明

loose_opt_outline_enabled

Global

Statement Outline功能控制開關。取值範圍如下：

ON（預設）：啟用Statement Outline功能。
OFF：禁用Statement Outline功能。

loose_outline_templated_digest_for_sharding_table

Session

分表Outline（Sharding Outline）功能控制開關。取值範圍如下：

ON（預設）
OFF

說明

該參數僅適用於以下版本：

PolarDB MySQL版8.0.1版本且小版本為8.0.1.1.54或以上。
PolarDB MySQL版8.0.2版本且小版本為8.0.2.2.33或以上。

管理Statement Outline

說明

當Schema_name非空時，您的SQL語句所在的Schema_name和該語句的Digest值，需要與Statement Outline規則中的Schema_name和Digest值同時匹配，Statement Outline才會生效。
當Schema_name為空白串時，您的SQL語句的Digest值與Statement Outline規則中的Digest值匹配，Statement Outline即可生效。

為了便捷地管理Statement Outline，PolarDB在DBMS_OUTLN中定義了五個本機存放區規則，詳細說明如下：

add_optimizer_outline：增加Optimizer Hints。
add_index_outline：增加Index Hints。
preview_outline：查看匹配Statement Outline的情況，可用於手動驗證。
show_outline：查看Statement Outline在記憶體中命中的情況。
del_outline：刪除記憶體和表中的某一條Statement Outline。

add_optimizer_outline

文法

dbms_outln.add_optimizer_outline('<Schema_name>','<Hint>','<query>');

參數說明

參數	說明
Schema_name	資料庫名稱。
Hint	Optimizer Hint中，Hint表示完整的Hint字串，例如*/+MAX_EXECUTION_TIME(1000) /*。
Query	需要添加Statement Outline的原始SQL語句。

說明

PolarDB MySQL版5.6版本暫不支援使用add_optimizer_outline。
Query語句中需要使用引號時，需要在Query語句中給需要添加引號的部分添加單引號，並使用雙引號包圍Query。
Query語句使用單引號建立的Statement Outline在實際執行時，無論是使用單引號還是雙引號，都能成功匹配Statement Outline。

樣本

原始Query語句：

SELECT * FROM t1 WHERE name="Tom";

修改後Query語句：

SELECT * FROM t1 WHERE name='Tom';

增加Outline的Query語句：

CALL dbms_outln.add_optimizer_outline("", "/*+ max_execution_time(1000) */", "SELECT * FROM t1 WHERE name='Tom'");

add_index_outline

文法

dbms_outln.add_index_outline('<Schema_name>','<Digest>',<Position>,'<Type>','<Hint>','<Scope>','<Query>');

說明

Query為需要添加Statement Outline的原始SQL語句。
Digest和Query可以任選其一。如果填寫Query，DBMS_OUTLN會計算Digest和Digest_text。

參數說明

參數	說明
Schema_name	資料庫名稱。
Digest	Digest_text進行hash計算得到的64位元組的hash字串，詳情請參見 STATEMENT_DIGEST()。
Position	Position表示表的位置，也是從1開始，Hint作用在第幾個表上，Position就是幾。
Type	Index Hint中，Hint類型的取值為USE INDEX、FORCE INDEX或IGNORE INDEX。
Hint	Index Hint中，Hint表示索引名字的列表，例如ind_1,ind_2。
Scope	分為如下三類： FOR GROUP BY FOR ORDER BY FOR JOIN 說明空串表示所有類型的Index Hint。
Query	需要添加Statement Outline的原始SQL語句。

樣本

CALL dbms_outln.add_index_outline('outline_db', '', 1, 'USE INDEX', 'ind_1', '', "SELECT * FROM t1 WHERE t1.col1 =1 AND t1.col2 ='xpchild'");

preview_outline

文法

dbms_outln.preview_outline('<Schema_name>','<Query>');

樣本

mysql> CALL dbms_outln.preview_outline('outline_db', "SELECT * FROM t1 WHERE t1.col1 =1 AND t1.col2 ='xpchild'");
+------------+------------------------------------------------------------------+------------+------------+-------+---------------------+
| SCHEMA     | DIGEST                                                           | BLOCK_TYPE | BLOCK_NAME | BLOCK | HINT                |
+------------+------------------------------------------------------------------+------------+------------+-------+---------------------+
| outline_db | b4369611be7ab2d27c85897632576a04bc08f50b928a1d735b62d0a140628c4c | TABLE      | t1         |     1 | USE INDEX (`ind_1`) |
+------------+------------------------------------------------------------------+------------+------------+-------+---------------------+
1 row in set (0.01 sec)

show_outline

文法

dbms_outln.show_outline();

樣本

CALL dbms_outln.show_outline();
+------+------------+------------------------------------------------------------------+-----------+-------+------+-------------------------------------------------------+------+----------+-------------------------------------------------------------------------------------+
| ID   | SCHEMA     | DIGEST                                                           | TYPE      | SCOPE | POS  | HINT                                                  | HIT  | OVERFLOW | DIGEST_TEXT                                                                         |
+------+------------+------------------------------------------------------------------+-----------+-------+------+-------------------------------------------------------+------+----------+-------------------------------------------------------------------------------------+
|   33 | outline_db | 36bebc61fce7e32b93926aec3fdd790dad5d895107e2d8d3848d1c60b74bcde6 | OPTIMIZER |       |    1 | /*+ SET_VAR(foreign_key_checks=OFF) */                |    1 |        0 | SELECT * FROM `t1` WHERE `id` = ?                                                   |
|   32 | outline_db | 36bebc61fce7e32b93926aec3fdd790dad5d895107e2d8d3848d1c60b74bcde6 | OPTIMIZER |       |    1 | /*+ MAX_EXECUTION_TIME(1000) */                       |    2 |        0 | SELECT * FROM `t1` WHERE `id` = ?                                                   |
|   34 | outline_db | d4dcef634a4a664518e5fb8a21c6ce9b79fccb44b773e86431eb67840975b649 | OPTIMIZER |       |    1 | /*+ BNL(t1,t2) */                                     |    1 |        0 | SELECT `t1` . `id` , `t2` . `id` FROM `t1` , `t2`                                   |
|   35 | outline_db | 5a726a609b6fbfb76bb8f9d2a24af913a2b9d07f015f2ee1f6f2d12dfad72e6f | OPTIMIZER |       |    2 |  /*+ QB_NAME(subq1) */                                |    2 |        0 | SELECT * FROM `t1` WHERE `t1` . `col1` IN ( SELECT `col1` FROM `t2` )               |
|   36 | outline_db | 5a726a609b6fbfb76bb8f9d2a24af913a2b9d07f015f2ee1f6f2d12dfad72e6f | OPTIMIZER |       |    1 | /*+ SEMIJOIN(@subq1 MATERIALIZATION, DUPSWEEDOUT) */  |    2 |        0 | SELECT * FROM `t1` WHERE `t1` . `col1` IN ( SELECT `col1` FROM `t2` )               |
|   30 | outline_db | b4369611be7ab2d27c85897632576a04bc08f50b928a1d735b62d0a140628c4c | USE INDEX |       |    1 | ind_1                                                 |    3 |        0 | SELECT * FROM `t1` WHERE `t1` . `col1` = ? AND `t1` . `col2` = ?                    |
|   31 | outline_db | 33c71541754093f78a1f2108795cfb45f8b15ec5d6bff76884f4461fb7f33419 | USE INDEX |       |    2 | ind_2                                                 |    1 |        0 | SELECT * FROM `t1` , `t2` WHERE `t1` . `col1` = `t2` . `col1` AND `t2` . `col2` = ? |
+------+------------+------------------------------------------------------------------+-----------+-------+------+-------------------------------------------------------+------+----------+-------------------------------------------------------------------------------------+
7 rows in set (0.00 sec)

其中，HIT表示該Statement Outline命中的次數，OVERFLOW表示該Statement Outline沒有找到Query Block或相應的表的次數。

del_outline

文法

dbms_outln.del_outline(<Id>);

樣本

CALL dbms_outln.del_outline(32);

說明

如果刪除的規則不存在，系統會報相應的警告，您可以使用SHOW WARNINGS;查看警告內容。

CALL dbms_outln.del_outline(1000);
Query OK, 0 rows affected, 2 warnings (0.00 sec)

SHOW WARNINGS;
+---------+------+----------------------------------------------+
| Level   | Code | Message                                      |
+---------+------+----------------------------------------------+
| Warning | 7521 | Statement outline 1000 is not found in table |
| Warning | 7521 | Statement outline 1000 is not found in cache |
+---------+------+----------------------------------------------+
2 rows in set (0.00 sec)

分表Outline（Sharding Outline）

在分表情境中，經常建立大量結構相同、僅表名尾碼數字不同的物理表（如t_001、t_002 … t_999）。傳統Statement Outline要求為每個具體表名單獨配置規則，導致規則數量爆炸、維護成本高、易遺漏。

分表Outline通過自動模板化表名與列名末尾的連續數字（例如將t_1、t_2、t_100統一識別為t_?），使單條Outline規則可匹配所有符合命名模式的分表，實現一次配置、全域生效。

對比維度	普通Outline	分表Outline
表名匹配	嚴格匹配完整表名（如`t_1`）	自動模板化末尾數字（如`t_?`）
適用範圍	單個具體表	多個命名規律一致的分表
規則數量	每個分表需獨立規則	單條規則覆蓋全部匹配分表
維護複雜度	高：需大量建立/更新/刪除	低：統一增刪改查，一致性保障強

適用範圍

PolarDB叢集版本需為如下版本之一：

PolarDB MySQL版8.0.1版本且小版本為8.0.1.1.54或以上。
PolarDB MySQL版8.0.2版本且小版本為8.0.2.2.33或以上。

使用方式

您可以登入PolarDB控制台，在參數配置頁面設定loose_outline_templated_digest_for_sharding_table參數來啟用分表Outline（Sharding Outline）功能。

調用專用預存程序add_optimizer_outline_sharding，其參數結構、語義與add_optimizer_outline完全一致，僅增加分表模板化能力：

CALL dbms_outln.add_optimizer_outline_sharding(
  'test',                          -- Schema_name
  '',                              -- Digest（留空由系統計算）
  1,                               -- Position（表示表的位置，從1開始，Hint作用在第幾個表上，Position就是幾）
  '/*+ MAX_EXECUTION_TIME(1000) */', -- Hint 字串
  "SELECT t_1.c_1 FROM t_1"        -- 原始SQL（含任意分表名，如 t_1/t_2/t_100）
);

執行後，該規則將同時對t_1、t_2、t_100等所有t_?形式表生效。

示範樣本

您可以使用以下兩種方式中的任意一種驗證Statement Outline的效果。

通過preview_outline進行預覽。

SQL命令如下：

CALL dbms_outln.preview_outline('outline_db', "SELECT * FROM t1 WHERE t1.col1 =1 AND t1.col2 ='xpchild'");

運行結果如下：

+------------+------------------------------------------------------------------+------------+------------+-------+---------------------+
| SCHEMA     | DIGEST                                                           | BLOCK_TYPE | BLOCK_NAME | BLOCK | HINT                |
+------------+------------------------------------------------------------------+------------+------------+-------+---------------------+
| outline_db | b4369611be7ab2d27c85897632576a04bc08f50b928a1d735b62d0a140628c4c | TABLE      | t1         |     1 | USE INDEX (`ind_1`) |
+------------+------------------------------------------------------------------+------------+------------+-------+---------------------+
1 row in set (0.01 sec)

通過EXPLAIN查看。

說明

Extra 列中的結果僅在以下版本中顯示：

PolarDB MySQL版8.0.1版本且小版本為8.0.1.0.34或以上。
PolarDB MySQL版8.0.1版本且小版本為8.0.2.2.27或以上。

SQL命令如下：

EXPLAIN SELECT * FROM t1 WHERE t1.col1 =1 AND t1.col2 ='xpchild';

結果如下：

+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+------------------------------+
| id | select_type | table | partitions | type | possible_keys | key   | key_len | ref   | rows | filtered | Extra                        |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+------------------------------+
|  1 | SIMPLE      | t1    | NULL       | ref  | ind_1         | ind_1 | 5       | const |    1 |   100.00 | Using where; Using outline 1 |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+------------------------------+
1 row in set, 1 warning (0.00 sec)

SQL命令如下：

SHOW warnings;

結果如下：

+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                                                                                                                                 |
+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ SELECT `outline_db`.`t1`.`id` AS `id`,`outline_db`.`t1`.`col1` AS `col1`,`outline_db`.`t1`.`col2` AS `col2` FROM `outline_db`.`t1` USE INDEX (`ind_1`) WHERE ((`outline_db`.`t1`.`col1` = 1) AND (`outline_db`.`t1`.`col2` = 'xpchild')) |
+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

附錄：Statement Outline表

PolarDB內建了一張系統資料表outline來儲存Hint，系統啟動時會自動建立該表，無需您手動建立。該系統資料表的建立語句如下所示：

CREATE TABLE `mysql`.`outline` (
  `Id` bigint(20) NOT NULL AUTO_INCREMENT,
  `Schema_name` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `Digest` varchar(64) COLLATE utf8_bin NOT NULL,
  `Digest_text` longtext COLLATE utf8_bin,
  `Type` enum('IGNORE INDEX','USE INDEX','FORCE INDEX','OPTIMIZER') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `Scope` enum('','FOR JOIN','FOR ORDER BY','FOR GROUP BY') CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT '',
  `State` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'Y',
  `Position` bigint(20) NOT NULL,
  `Hint` text COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`Id`)
) /*!50100 TABLESPACE `mysql` */ ENGINE=InnoDB
DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0 COMMENT='Statement outline'

參數說明如下：

參數	說明
Id	Outline ID。
Schema_name	資料庫名稱。
Digest	Digest_text進行hash計算得到的64個位元組的hash字串，詳情請參見 STATEMENT_DIGEST()。
Digest_text	SQL語句的特徵。
Type	Optimizer Hints中，Hint類型的取值為OPTIMIZER。 Index Hints中，Hint類型的取值為USE INDEX、FORCE INDEX或IGNORE INDEX。
Scope	僅Index Hints需要提供該參數，分為如下三類： FOR GROUP BY FOR ORDER BY FOR JOIN 說明空串表示所有類型的Index Hints。
State	本規則是否啟用，取值範圍： N Y（預設）
Position	Optimizer Hints中，Position表示Query Block，因為所有的Optimizer Hints必須作用到Query Block上，Position從1開始，Hint作用在語句的第幾個關鍵字上，Position就是幾。 Index Hints中，Position表示表的位置，也是從1開始，Hint作用在第幾張表上，Position就是幾。
Hint	Optimizer Hints中，Hint表示完整的Hint字串，例如`/+ MAX_EXECUTION_TIME(1000) /`。 Index Hints中，Hint表示索引名字的列表，例如`ind_1,ind_2`。