生產環境中，SQL語句的執行計畫經常發生改變，導致資料庫不穩定。PolarDB利用Optimizer Hints和Index Hints讓MySQL穩定執行計畫，該方法稱為Statement Outline，並提供了工具包DBMS_OUTLN方便您快捷使用。本文將介紹如何使用和管理Statement Outline。
前提條件
PolarDB叢集版本需為如下版本之一：
PolarDB MySQL版5.6版本且Revision version為5.6.1.0.36或以上。
PolarDB MySQL版5.7版本且Revision version為5.7.1.0.2或以上。
PolarDB MySQL版8.0.1版本且Revision version為8.0.1.1.1或以上。
PolarDB MySQL版8.0.2版本。
您可以通過查詢版本號碼來確認叢集版本。
功能設計
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控制台。在參數配置頁面通過設定參數opt_outline_enable的值來啟用或禁用Statement Outline功能。設定參數的具體操作請參見設定叢集參數和節點參數。
參數 | 層級 | 說明 |
loose_opt_outline_enable | Global | Statement Outline功能控制開關。取值範圍如下：
|
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 |
|
Scope | 僅Index Hints需要提供該參數，分為如下三類：
說明 空串表示所有類型的Index Hints。 |
State | 本規則是否啟用，取值範圍：
|
Position |
|
Hint |
|
當
Schema_name非空時，您的SQL語句所在的Schema_name和該語句的Digest值，需要與Statement Outline規則中的Schema_name和Digest值同時匹配，Statement Outline才會生效。當
Schema_name為空白串時，您的SQL語句的Digest值與Statement Outline規則中的Digest值匹配，Statement Outline即可生效。
管理Statement Outline
管理Statement Outline的操作只能在主節點上進行，完成後會自動同步到其他節點。
為了便捷地管理Statement Outline，PolarDB在DBMS_OUTLN中定義了六個本機存放區規則，詳細說明如下：
add_optimizer_outline：增加Optimizer Hints。
文法
文法一：
dbms_outln.add_optimizer_outline('<Schema_name>','<Digest>','<query_block>','<hint>','<query>');文法二：文法二等價於將文法一中的Digest值設定為空白，將query_block設定為1。
dbms_outln.add_optimizer_outline('<Schema_name>','<hint>','<query>');
說明PolarDB MySQL版5.6版本暫不支援使用add_optimizer_outline。
使用文法一時，Digest和Query（原始SQL語句）可以任選其一。如果填寫Query，DBMS_OUTLN會計算Digest和Digest_text。
Query語句中需要使用引號時，需要在query語句中給需要添加引號的部分添加單引號，並使用雙引號包圍query。
樣本
匹配
Schema_name和Digest值。樣本如下：CALL dbms_outln.add_optimizer_outline('outline_db', '', 1, '/*+ MAX_EXECUTION_TIME(1000) */', 'SELECT * FROM t1 WHERE id = 1');上述樣本內容等價於以下語句：
CALL dbms_outln.add_optimizer_outline('outline_db', '/*+ MAX_EXECUTION_TIME(1000) */', 'SELECT * FROM t1 WHERE id = 1');同時等價於以下語句：
CALL dbms_outln.add_optimizer_outline('outline_db', '36bebc61fce7e32b93926aec3fdd790dad5d895107e2d8d3848d1c60b74bcde6', 1, '/*+ MAX_EXECUTION_TIME(1000) */', '');其中，
36bebc61fce7e32b93926aec3fdd790dad5d895107e2d8d3848d1c60b74bcde6為SELECT * FROM t1 WHERE id = 1的Digest值，故兩個語句的執行效果相同。匹配
Digest值。樣本如下：CALL dbms_outln.add_optimizer_outline('', '', 1, '/*+ MAX_EXECUTION_TIME(1000) */', 'SELECT * FROM t1 WHERE id = 1');
add_index_outline：增加Index Hints。
文法
dbms_outln.add_index_outline('<Schema_name>','<Digest>',<Position>,'<Type>','<Hint>','<Scope>','<Query>');說明Digest和Query（原始SQL語句）可以任選其一。如果填寫Query，DBMS_OUTLN會計算Digest和Digest_text。
樣本
匹配
Schema_name和Digest值。樣本如下：CALL dbms_outln.add_index_outline('outline_db', '', 1, 'USE INDEX', 'ind_1', '', "SELECT * FROM t1 WHERE t1.col1 =1 AND t1.col2 ='xpchild'");上述樣本內容等價於以下語句：
CALL dbms_outln.add_index_outline('outline_db', 'b4369611be7ab2d27c85897632576a04bc08f50b928a1d735b62d0a140628c4c', 1, 'USE INDEX', 'ind_1', '', "");其中，
b4369611be7ab2d27c85897632576a04bc08f50b928a1d735b62d0a140628c4c為SELECT * FROM t1 WHERE t1.col1 =1 AND t1.col2 ='xpchild'的Digest值，故兩個語句的執行效果相同。匹配
Digest值。樣本如下：CALL dbms_outln.add_index_outline('', '', 1, 'USE INDEX', 'ind_1', '', "SELECT * FROM t1 WHERE t1.col1 =1 AND t1.col2 ='xpchild'");
preview_outline：查看匹配Statement Outline的情況，可用於手動驗證。
文法
dbms_outln.preview_outline('<Schema_name>','<Query>');樣本
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.00 sec)show_outline：查看Statement 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：刪除記憶體和表中的某一條Statement 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)flush_outline：如果您直接操作了表
outline修改Statement Outline，您需要使用該預存程序使Statement Outline重新生效。文法
dbms_outln.flush_outline();樣本
UPDATE mysql.outline SET Position = 1 WHERE Id = 18; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 CALL dbms_outln.flush_outline(); Query OK, 0 rows affected (0.01 sec)
功能測試
您可以使用以下兩種方式中的任意一種驗證Statement Outline的效果。
通過preview_outline進行預覽。
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查看。
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 | +----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) 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)