全部產品
Search

搜尋本產品

    ApsaraDB RDS：Statement Outline

    文件中心

    ApsaraDB RDS：Statement Outline

    更新時間：Feb 28, 2024

    生產環境中，SQL語句的執行計畫經常會發生改變，導致資料庫不穩定。阿里雲利用Optimizer Hint和Index Hint讓MySQL穩定執行計畫，該方法稱為Statement Outline，並提供了工具包（DBMS_OUTLN）便於您快捷使用。

    前提條件

    RDS執行個體版本如下：

    • MySQL 8.0
    • MySQL 5.7

    功能設計

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

    • Optimizer Hint

      根據範圍和hint對象，分為Global level hint、Table/Index level hint、Join order hint等。詳情請參見MySQL官網。

    • Index Hint

      根據Index Hint的類型和範圍進行分類。詳情請參見MySQL官網

    Statement Outline表介紹

    AliSQL內建了一個系統資料表（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'​

    參數說明如下。

    參數說明
    IdOutline ID。
    Schema_name資料庫名。
    DigestDigest_text進行hash計算得到的64位元組的hash字串。
    Digest_textSQL語句的特徵。
    Type
    • Optimizer Hint中，hint類型的取值為OPTIMIZER。
    • Index Hint中，hint類型的取值為USE INDEX、FORCE INDEX或IGNORE INDEX。
    Scope僅Index Hint需要提供，分為如下三類：
    • FOR GROUP BY
    • FOR ORDER BY
    • FOR JOIN

    空串表示所有類型的Index Hint。

    State本規則是否啟用。
    Position
    • Optimizer Hint中，Position表示Query Block， 因為所有的Optimizer Hint必須作用到 Query Block上，所以，Position從1開始，hint作用在語句的第幾個關鍵字上，Position就是幾。
    • Index Hint中，Position表示表的位置， 也是從1開始，hint作用在第幾個表上，Position就是幾。
    Hint
    • Optimizer Hint中，Hint表示完整的hint字串，例如/*+ MAX_EXECUTION_TIME(1000) */。
    • Index Hint中，Hint表示索引名字的列表， 例如ind_1,ind_2。

    管理Statement Outline

    為了便捷地管理Statement Outline，AliSQL在DBMS_OUTLN中定義了六個本機存放區規則。詳細說明如下：
    • add_optimizer_outline
      增加Optimizer Hint。命令如下： 
      dbms_outln.add_optimizer_outline('<Schema_name>','<Digest>','<query_block>','<hint>','<query>');
      說明 Digest和Query（原始SQL語句）可以任選其一。如果填寫Query，DBMS_OUTLN會計算Digest和Digest_text。

      樣本：

      mysql> call dbms_outln.add_optimizer_outline("outline_db", '', 1, '/*+ MAX_EXECUTION_TIME(1000) */',
                                      "select * from t1 where id = 1");
    • add_index_outline
      增加Index Hint。命令如下： 
      dbms_outln.add_index_outline('<Schema_name>','<Digest>',<Position>,'<Type>','<Hint>','<Scope>','<Query>');
      說明 Digest和Query（原始SQL語句）可以任選其一。如果填寫Query，DBMS_OUTLN會計算Digest和Digest_text。

      樣本：

      mysql> 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
      查看匹配Statement 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.00 sec)​
    • show_outline
      展示Statement Outline在記憶體中命中的情況。命令如下： 
      dbms_outln.show_outline();

      樣本：

      ​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)​

      關於HIT和OVERFLOW的說明如下。

      參數說明
      HIT此Statement Outline命中的次數。
      OVERFLOW此Statement Outline沒有找到Query block或相應的表的次數。
    • del_outline
      刪除記憶體和表中的某一條Statement Outline。命令如下： 
      dbms_outln.del_outline(<Id>);

      樣本：

      ​mysql> call dbms_outln.del_outline(32);
      說明 如果刪除的規則不存在，系統會報相應的警告，您可以使用show warnings;查看警告內容。 
      ​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)​
    • flush_outline
      如果您直接操作了表outline修改Statement Outline，您需要讓Statement Outline重新生效。命令如下： 
      dbms_outln.flush_outline();

      樣本：

      mysql> update mysql.outline set Position = 1 where Id = 18;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

​mysql> call dbms_outln.flush_outline(); 
Query OK, 0 rows affected (0.01 sec)​

    功能測試

    驗證Statement Outline是否有效果，有如下兩種方法：
    • 通過preview_outline進行預覽。 
      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)
    • 直接使用explain查看。 
      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)