全部产品
Search
文档中心

云原生数据库 PolarDB:计划固化 (Statement Outline)

更新时间:Mar 07, 2024

生产环境中,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功能控制开关。取值范围如下:

  • ON(默认):启用Statement Outline功能。

  • OFF:禁用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

  • Optimizer Hints中,Hint类型的取值为OPTIMIZER

  • Index Hints中,Hint类型的取值为USE INDEXFORCE INDEXIGNORE 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

说明
  • Schema_name非空时,您的SQL语句所在的Schema_name和该语句的Digest值,需要与Statement Outline规则中的Schema_nameDigest值同时匹配,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_nameDigest值。示例如下:

      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) */', '');

      其中,36bebc61fce7e32b93926aec3fdd790dad5d895107e2d8d3848d1c60b74bcde6SELECT * FROM t1 WHERE id = 1Digest值,故两个语句的执行效果相同。

    • 匹配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>');
    说明

    DigestQuery(原始SQL语句)可以任选其一。如果填写Query,DBMS_OUTLN会计算DigestDigest_text

    示例

    • 匹配Schema_nameDigest值。示例如下:

      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', '', "");

      其中,b4369611be7ab2d27c85897632576a04bc08f50b928a1d735b62d0a140628c4cSELECT * 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)