更新时间:2024-09-06 GMT+08:00

Statement Outline

MySQL数据库实例运行过程中,SQL语句的执行计划经常会发生改变,导致数据库不稳定。GaussDB(for MySQL) 设计了一套利用 MySQL Optimizer/Index hint 来稳定执行计划的方法,称为 Statement outline,并提供了一组管理接口方便使用(dbms_outln package)。

前提条件

GaussDB(for MySQL)的内核版本大于等于2.0.42.230600,支持Statement Outline功能。

注意事项

  1. Statement Outline默认关闭,如果您需要使用,则需要联系客服人员开启。
  2. Statement Outline不开启情况下对性能没有影响,但是特性开启后在规则较多的情况下对性能会产生影响,会导致性能下降。

功能描述

Statement Outline支持MySQL8.0的Optimizer Hints和Index Hints场景:

  • Optimizer Hints

    根据作用域(query block)和Hint对象,分为Global-Level Hint, Table-Level Hint, Index-Level Hint和Join-Order Hint等,详情请参见Optimizer Hints

  • Index Hints

    Index Hint是向优化器提供有关在查询处理期间如何选择索引,不更改优化器策略。合理的索引可以加快数据索引操作,常用的索引Hint方式有三种,USE(参考使用),IGNORE(忽略),FORCE(强制),详情请参见Index Hints

Statement Outline表介绍

GaussDB(for MySQL)内置了一个系统表(outline)保存Hint,系统启动时会自动创建该表,无需您手动创建。创建表的SQL语句如下:

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`)
) ENGINE=InnoDB
 DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0 COMMENT='Statement outline'

各字段解释如下:

表1 字段解释

参数

说明

Id

outline ID。

Schema_name

数据库名称。

Digest

Digest_text进行哈希计算得到的64字节的哈希字符串。

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。

管理Statement Outline

为了便捷地管理Statement Outline,定义了六个本地存储规则。

  • add_optimizer_outline

    增加Optimizer Hint。

    • 语法

      dbms_outln.add_optimizer_outline(<Schema_name>,<Digest>,<Query_block>,<Hint>,<Query>);

      DigestQuery(原始SQL语句)可以任选其一。如果填写Query,DBMS_OUTLN会计算Digest和Digest_text,建议直接设置Query。

    • 参数说明

      参数名称

      是否必选

      类型

      含义

      Schema_name

      VARCHAR

      语句所属的数据库名称。

      可设置为空/NULL, 设置为空/NULL后,语句不能匹配。

      Digest

      VARCHAR

      语句特征哈希值。

      Query参数可以选择其一设置, 不设置的话需要设置为空字符串。

      Query_block

      INT

      Hint作用对象在语句中的位置。

      取值范围:

      大于等于1。

      Hint

      VARCHAR

      Hint名称。

      Query

      VARCHAR

      SQL语句。

      • Digest参数可以选择其一,不设置的话需要设置为空字符串''。
      • 如果两个都设置的话,确认DigestQuery是匹配的,否则参数校验不通过,执行失败。
    • 示例

  • add_index_outline

    增加Index Hint。

    • 语法

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

      DigestQuery(原始SQL语句)可以任选其一。如果填写Query,DBMS_OUTLN会计算Digest和Digest_text,建议直接设置Query。

    • 参数说明

      参数名称

      是否必选

      类型

      含义

      Schema_name

      VARCHAR

      语句所属的db name。

      可设置为空/NULL, 设置为空后,语句不能匹配

      Digest

      VARCHAR

      语句特征哈希值。

      Query可以选择其一, 不设置的话需要设置为''

      Position

      INT

      Hint作用对象在语句中的位置,Index Hint作用对象为table, 即为表在语句中的位置。

      取值范围:大于等于1。

      Type

      ENUM

      Hint类型。取值如下:

      • OPTIMIZER
      • USE INDEX
      • FORCE INDEX
      • IGNORE INDEX

      Hint

      VARCHAR

      Hint名称,即索引名称集合,多个索引名之间用英文逗号分隔。

      Scope

      ENUM

      Hint作用域。取值如下:

      • FOR GROUP BY
      • FOR ORDER BY
      • FOR JOIN
      • 空字符串

      Query

      VARCHAR

      SQL语句。

      • Digest可以选择其一,不设置的话需要设置为''。
      • 如果两个都设置的话,确认DigestQuery是匹配的,否则参数校验不通过,执行失败。
    • 示例
      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>);

    • 参数说明

      参数名称

      是否必选

      参数类型

      含义

      Schema_name

      VARCHAR

      数据库名称。

      Query

      VARCHAR

      SQL语句。

    • 示例

  • show_outline

    展示Statement Outline在内存中命中的情况。

    • 语法

      dbms_outln.show_outline();

    • 示例

      关于HIT和OVERFLOW的说明如下:

      1. HIT为Statement Outline命中的次数。
      2. OVERFLOW为Statement Outline没有找到Query block或相应的表的次数。
  • del_outline

    删除内存和表中的某一条Statement Outline。

    • 语法

      dbms_outln.del_outline(<id>);

    • 参数说明

      参数名称

      是否必选

      类型

      含义

      id

      INT

      outline规则的序号ID,为mysql.outline表中的id列的值。不能为空。

    • 示例

      说明:如果删除的规则不存在,系统会报相应的警告,您可以使用show warnings;查看警告内容

  • flush_outline

    如果您直接操作了表outline修改Statement Outline,您需要让Statement Outline重新生效。

    • 语法

      dbms_outln.flush_outline();

    • 示例
      update mysql.outline set Position = 1 where Id = 18;
      call dbms_outln.flush_outline();

功能验证

验证Statement Outline是否有效果,有如下方法:

  • 通过preview_outline进行预览

  • 直接使用EXPLAIN查看