作者：阿裡雲資料庫産品事業部
進階技術專家 冷香
背景
在生産環境，MySQL 資料庫執行個體運作過程中，一些 SQL 語句會發生執行計劃的變化，導緻增加了資料庫穩定性的風險。
這裡邊有幾個因素和場景，比如：随着表資料量的變化，以及統計資訊的自動收集，CBO optimizer 計算得到了一個cost 更低的 plan， 又或者 表結構發生了變化，增加和删減了某些索引，或者在執行個體更新遷移等過程中，MySQL 自身優化器行為和算法發生了變化等。
為了能夠線上應對和幹預業務SQL語句的執行計劃，AliSQL設計了一套利用 MySQL optimizer/index hint 來穩定執行計劃的方法，稱為 Statement outline，并提供了一組管理接口友善使用（DBMS_OUTLN package）， 并在阿裡雲RDS MySQL 8.0 産品上公開使用。
Outline設計
AliSQL 8.0 outline 支援 MySQL 8.0 官方支援的所有 hint 類型，主要分為兩大類：
- Optimizer Hint
根據作用域（query block）和 hint 對象，又分為：Global level hint，Table/Index level hint,
Join order hint等等。
詳細資訊參考：
https://dev.mysql.com/doc/refman/8.0/en/optimizer-hints.html- Index Hint
主要根據 index hint 的類型 （USE, FORCE, IGNORE）和 scope （FOR JOIN, FOR ORDER BY,
FOR GROUP BY）進行分類。
詳細文法參考：
https://dev.mysql.com/doc/refman/8.0/en/index-hints.html為了表示和抽象這些 Hint，并能夠持久化 outline，AliSQL 8.0 增加了一個系統表 mysql.outline，其結構如下：
MYSQL.OUTLINE
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' Columns 說明
Digest/Digest_text
Outline 根據語句的特征進行比對，這個特征就是 Digest text，根據這個 Digest Text 進行 hash 計算得到一個 64 位元組的 hash 字元串。例如：
Statement query: select * from t1 where id = 1 根據計算得到的Digest 和 Digest text 分别是：
Digest ：36bebc61fce7e32b93926aec3fdd790dad5d895107e2d8d3848d1c60b74bcde6
Digest_text: SELECT * FROM `t1` WHERE `id` = ? 當語句 parse 完之後， 會根據 [schema + digest] 作為 hash key，進行查詢比對的 Outline。
Type
Optimizer hint 的 type 統一為 OPTIMIZER.
Index hint 分為三類， 分别是：
USE INDEX
FORCE INDEX
IGNORE INDEX
Scope
scope 隻針對 Index hint 而言，分為四類：
FOR GROUP BY
FOR ORDER BY
FOR JOIN
ALL
Position
Optimizer hint
position 表示 Query Block， 因為所有的 optimizer hint 必須作用到 Query Block上，
這裡判斷比較簡單， 因為 Optimizer hint 隻支援 這幾類關鍵字：
SELECT /*+ ... */ ...
INSERT /*+ ... */ ...
REPLACE /*+ ... */ ...
UPDATE /*+ ... */ ...
DELETE /*+ ... */ ... 是以，position 從 1 開始，hint 作用在語句的第幾個關鍵字錨點上，就是第幾個。
Index hint
position 表示 table position， 也是從1開始，hint作用在第幾個 table 錨點上，就是第幾個。
Hint
在 Index hint 中， 這裡表示的是 索引名字的清單， 比如 “ind_1, ind_2”
在 Optimizer hint 中， 這裡表示的就是完整的 hint 字元串，比如：
“/+ MAX_EXECUTION_TIME(1000) /”
使用者接口
為了更友善的管理 Statement outline，AliSQL 設計了一個 DBMS_OUTLN package 來進行管理，并提供了 6 個native procedure 接口：
DBMS_OUTLN.add_index_outline(); 增加 index hint
DBMS_OUTLN.add_optimizer_outline(); 增加 optimizer hint
DBMS_OUTLN.preview_outline(); 預覽某一個 SQL 語句命中 outline 的情況
DBMS_OUTLN.show_outline(); 展示記憶體中可用的所有 outline 及命中情況
DBMS_OUTLN.del_outline(); 删除記憶體和持久化表中的 outline
DBMS_OUTLN.flush_outline(); 重新整理所有的 outline，從 mysql.outline 表中重新 load 為了友善的介紹 DBMS_OUTLN 的使用，這裡使用一些測試表：
CREATE TABLE `t1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`col1` int(11) DEFAULT NULL,
`col2` varchar(100) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `ind_1` (`col1`),
KEY `ind_2` (`col2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `t2` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`col1` int(11) DEFAULT NULL,
`col2` varchar(100) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `ind_1` (`col1`),
KEY `ind_2` (`col2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8; 1. Add index outline
文法和參數
CALL DBMS_OUTLN.add_index_outline(schema=>, digest=>, position=>, type=>,
scope=>, hint=>, query=>);
說明：
digest 和 query 可以選擇其一， 如果填寫了原始query語句，這個 proc 會計算 digest 和 digest text。
測試 case 1
測試語句
select * from t1 where t1.col1 =1 and t1.col2 ='xpchild'; 使用 ind_1 的索引
call dbms_outln.add_index_outline('outline_db', '', 1, 'USE INDEX', 'ind_1', '',
"select * from t1 where t1.col1 =1 and t1.col2 ='xpchild'");
檢視 outline：
mysql> call dbms_outln.show_outline();
+------+------------+------------------------------------------------------------------+-----------+-------+------+-------+------+----------+------------------------------------------------------------------+
| ID | SCHEMA | DIGEST | TYPE | SCOPE | POS | HINT | HIT | OVERFLOW | DIGEST_TEXT |
+------+------------+------------------------------------------------------------------+-----------+-------+------+-------+------+----------+------------------------------------------------------------------+
| 30 | outline_db | b4369611be7ab2d27c85897632576a04bc08f50b928a1d735b62d0a140628c4c | USE INDEX | | 1 | ind_1 | 0 | 0 | SELECT * FROM `t1` WHERE `t1` . `col1` = ? AND `t1` . `col2` = ? |
+------+------------+------------------------------------------------------------------+-----------+-------+------+-------+------+----------+------------------------------------------------------------------+
1 row in set (0.00 sec) 驗證 Outline
驗證 Outline 是否其效果，可以有兩種方法：
dbms_outln.preview_outline() 進行預覽：
直接使用 explain 進行檢視：
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)
mysql> 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)
mysql> 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) 測試 case 2
測試語句：
select * from t1, t2 where t1.col1 = t2.col1 and t2.col2 ='xpchild' 測試使用 t2 表的 ind_2 索引：
call dbms_outln.add_index_outline('outline_db', '', 2, 'USE INDEX', 'ind_2', '',
"select * from t1, t2 where t1.col1 = t2.col1 and t2.col2 ='xpchild'"); mysql> explain select * from t1, t2 where t1.col1 = t2.col1 and t2.col2 ='xpchild';
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | t1 | NULL | ALL | ind_1 | NULL | NULL | NULL | 1 | 100.00 | NULL |
| 1 | SIMPLE | t2 | NULL | ref | ind_2 | ind_2 | 303 | const | 1 | 100.00 | Using where |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------------+
2 rows in set, 1 warning (0.01 sec)
mysql> 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`,`outline_db`.`t2`.`id` AS `id`,`outline_db`.`t2`.`col1` AS `col1`,`outline_db`.`t2`.`col2` AS `col2` from `outline_db`.`t1` join `outline_db`.`t2` USE INDEX (`ind_2`) where ((`outline_db`.`t2`.`col1` = `outline_db`.`t1`.`col1`) and (`outline_db`.`t2`.`col2` = 'xpchild')) |
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec) 2. Add optimizer outline
CALL DBMS_OUTLN.add_optimizer_outline(schema=>, digest=>, query_block=>
hint=>, query=>);
說明：digest 和 query 同樣可以填其一，或者都填入。proc 會自動計算digest 和 digest text。 增加全局 MAX_EXECUTION_TIME / SET VAR optimizer hint;
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", '', 1, '/*+ SET_VAR(foreign_key_checks=OFF) */',
"select * from t1 where id = 1");
驗證 Outline
mysql> explain select * from t1 where id = 1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | no matching row in const table |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
1 row in set, 1 warning (0.01 sec)
mysql> show warnings;
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note | 1003 | /* select#1 */ select /*+ MAX_EXECUTION_TIME(1000) SET_VAR(foreign_key_checks='OFF') */ NULL AS `id`,NULL AS `col1`,NULL AS `col2` from `outline_db`.`t1` where multiple equal(1, NULL) |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec) 測試多表關聯查詢：Nested-Loop join processing
CALL DBMS_OUTLN.add_optimizer_outline('outline_db', '', 1, '/*+ BNL(t1,t2) */',
"select t1.id, t2.id from t1,t2"); 驗證Outline：
mysql> explain select t1.id, t2.id from t1,t2;
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+----------------------------------------------------+
| 1 | SIMPLE | t1 | NULL | index | NULL | ind_1 | 5 | NULL | 1 | 100.00 | Using index |
| 1 | SIMPLE | t2 | NULL | index | NULL | ind_1 | 5 | NULL | 1 | 100.00 | Using index; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+----------------------------------------------------+
2 rows in set, 1 warning (0.01 sec)
mysql> show warnings;
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note | 1003 | /* select#1 */ select /*+ BNL(`t1`@`select#1`) BNL(`t2`@`select#1`) */ `outline_db`.`t1`.`id` AS `id`,`outline_db`.`t2`.`id` AS `id` from `outline_db`.`t1` join `outline_db`.`t2` |
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec) 測試 case 3
測試 subquery 中帶有 query block name 的情況
CALL DBMS_OUTLN.add_optimizer_outline('outline_db', '', 2, ' /*+ QB_NAME(subq1) */',
"SELECT * FROM t1 WHERE t1.col1 IN (SELECT col1 FROM t2)");
CALL DBMS_OUTLN.add_optimizer_outline('outline_db', '', 1, '/*+ SEMIJOIN(@subq1 MATERIALIZATION, DUPSWEEDOUT) */ ',
"SELECT * FROM t1 WHERE t1.col1 IN (SELECT col1 FROM t2)");
驗證 Outline：
mysql> explain SELECT * FROM t1 WHERE t1.col1 IN (SELECT col1 FROM t2);
+----+--------------+-------------+------------+--------+---------------+------------+---------+--------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------+-------------+------------+--------+---------------+------------+---------+--------------------+------+----------+-------------+
| 1 | SIMPLE | t1 | NULL | ALL | ind_1 | NULL | NULL | NULL | 1 | 100.00 | Using where |
| 1 | SIMPLE | <subquery2> | NULL | eq_ref | <auto_key> | <auto_key> | 5 | outline_db.t1.col1 | 1 | 100.00 | NULL |
| 2 | MATERIALIZED | t2 | NULL | index | ind_1 | ind_1 | 5 | NULL | 1 | 100.00 | Using index |
+----+--------------+-------------+------------+--------+---------------+------------+---------+--------------------+------+----------+-------------+
3 rows in set, 1 warning (0.00 sec)
mysql> show warnings;
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note | 1003 | /* select#1 */ select /*+ SEMIJOIN(@`subq1` MATERIALIZATION, DUPSWEEDOUT) */ `outline_db`.`t1`.`id` AS `id`,`outline_db`.`t1`.`col1` AS `col1`,`outline_db`.`t1`.`col2` AS `col2` from `outline_db`.`t1` semi join (`outline_db`.`t2`) where (`<subquery2>`.`col1` = `outline_db`.`t1`.`col1`) |
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec) 3. Preview outline
dbms_outln.preview_outline() 具體 SQL 語句，檢視比對 Outline 的情況，用于手動驗證。
其文法和參數：
CALL DBMS_OUTLN.preview_outline(schema=>, 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.00 sec) 4. Show outline
dbms_outln.show_outline 展示 outline 在記憶體 cache 中 命中的情況，裡邊有兩個字段:
- Hit：outline 命中的次數
- Overflow：outline hint 沒有找到 query block 或者 相應的 table 的次數 mysql> 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) 5. Delete outline
dbms_outln.del_outline() 可以删除記憶體和表中的某一條 outline。
文法和參數如下：
CALL DBMS_OUTLN.del_outline(outline_id=>);
mysql> call dbms_outln.del_outline(1000);
Query OK, 0 rows affected, 2 warnings (0.00 sec)
mysql> 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) 6. Flush outline
dbms_outln.flush_outline() 支援清理 cache 中 outline，并從 mysql.outline 表中重新 load。
如果使用者直接修改表來加載 outline，需要調用 flush 到 cache 中。
mysql> call dbms_outln.flush_outline();
Query OK, 0 rows affected (0.01 sec) 相關閱讀
為更強大而生的開源關系型資料庫來了！阿裡雲RDS for MySQL 8.0正式上線
幹貨 | 淺析RDS MySQL 8.0語句級并發控制
What's new | 阿裡雲 RDS for MySQL 8.0獨家技術解讀
媒體聲音 | 在開源道路上，阿裡AliSQL笃定前行
活動預告
![SQL優化SQL語句優化的目的[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)