Statement Outline
操作场景
TaurusDB实例运行过程中,SQL语句的执行计划可能频繁变化,导致数据库性能不稳定。TaurusDB提供了Statement Outline功能,利用 MySQL Optimizer/Index hint 稳定执行计划,并通过dbms_outln包提供管理接口。Statement Outline支持对用户表、用户视图、系统表、系统视图固定执行计划。
前提条件
TaurusDB的内核版本大于等于2.0.42.230600,支持Statement Outline功能。内核版本的查询方法请参见如何查看云数据库 TaurusDB实例的版本号。
注意事项
- Statement Outline默认关闭,如果您需要使用,请参见开启Statement Outline。
- 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
- 登录TaurusDB管理控制台。
- 在“实例管理”页面,选择目标实例,单击实例名称,进入实例概览页面。
- 在左侧导航栏选择“参数修改”。
- 在搜索框中搜索参数“rds_opt_outline_enabled”,将对应的值改为“ON”。
表1 参数说明 参数名称
描述
rds_opt_outline_enabled
Statement Outline功能开关。
- ON:表示开启Statement Outline功能。
- OFF:表示关闭Statement Outline功能。
- 单击“保存”。
Statement Outline表介绍
TaurusDB内置了一个系统表(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' 各字段解释如下:
| 参数 | 说明 |
|---|---|
| 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需要提供该参数,取值如下:
|
| State | 本规则是否启用,取值范围:
|
| Position | |
| Hint |
|
管理Statement Outline
为了便捷地管理Statement Outline,定义了六个本地存储规则。
示例
- 示例1:对系统表mysql.db访问固定执行计划
- 默认情况下,以下查询语句的执行计划选择 `User` 索引。
mysql> EXPLAIN SELECT Host, Db, User FROM mysql.db WHERE User = "root"\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: db partitions: NULL type: ref possible_keys: PRIMARY,User key: User key_len: 96 ref: const rows: 1 filtered: 100.00 Extra: Using index 1 row in set, 1 warning (0.00 sec) - 通过 Statement Outline 特性固定执行计划,强制选择主键索引(PRIMARY)。
mysql> CALL dbms_outln.add_index_outline('', '', 1, 'FORCE INDEX', 'PRIMARY', '', 'SELECT Host, Db, User FROM mysql.db WHERE User = "root"'); Query OK, 0 rows affected (0.01 sec) - 查看 Statement Outline 规则是否生效。
mysql> call dbms_outln.show_outline()\G *************************** 1. row *************************** ID: 1 SCHEMA: DIGEST: deca3340e9cdb939599fd68aaf3ba61c5d71b8fccab4f0406386ae006f05349d TYPE: FORCE INDEX SCOPE: POS: 1 HINT: PRIMARY HIT: 0 OVERFLOW: 0 DIGEST_TEXT: SELECT HOST , `Db` , SYSTEM_USER FROM `mysql` . `db` WHERE SYSTEM_USER = ? 1 row in set (0.01 sec)mysql> CALL dbms_outln.preview_outline('', 'SELECT Host, Db, User FROM mysql.db WHERE User = "root"')\G *************************** 1. row *************************** SCHEMA: DIGEST: deca3340e9cdb939599fd68aaf3ba61c5d71b8fccab4f0406386ae006f05349d BLOCK_TYPE: TABLE BLOCK_NAME: db BLOCK: 1 HINT: FORCE INDEX (PRIMARY) 1 row in set (0.00 sec) - 执行业务 SQL,确认执行计划变为选择主键索引(PRIMARY)。
mysql> EXPLAIN SELECT Host, Db, User FROM mysql.db WHERE User = "root"\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: db partitions: NULL type: index possible_keys: PRIMARY key: PRIMARY key_len: 543 ref: NULL rows: 2 filtered: 50.00 Extra: Using where; Using index 1 row in set, 1 warning (0.00 sec) - 删除 Statement Outline 规则。
mysql> call dbms_outln.del_outline(1); Query OK, 0 rows affected (0.01 sec) mysql> call dbms_outln.show_outline(); Empty set (0.01 sec)
- 默认情况下,以下查询语句的执行计划选择 `User` 索引。
- 示例2:对系统视图 INFORMATION_SCHEMA.TABLES 访问固定执行计划
- 默认情况下,以下查询语句的表连接顺序为 cat->sch->tbl。
mysql> EXPLAIN SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE, TABLE_COMMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = "mysql" AND TABLE_NAME = "user"\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: cat partitions: NULL type: index possible_keys: PRIMARY key: name key_len: 194 ref: NULL rows: 1 filtered: 100.00 Extra: Using index *************************** 2. row *************************** id: 1 select_type: SIMPLE table: sch partitions: NULL type: eq_ref possible_keys: PRIMARY,catalog_id key: catalog_id key_len: 202 ref: mysql.cat.id,const rows: 1 filtered: 100.00 Extra: Using index *************************** 3. row *************************** id: 1 select_type: SIMPLE table: tbl partitions: NULL type: eq_ref possible_keys: schema_id key: schema_id key_len: 202 ref: mysql.sch.id,const rows: 1 filtered: 100.00 Extra: Using where *************************** 4. row *************************** id: 1 select_type: SIMPLE table: col partitions: NULL type: eq_ref possible_keys: PRIMARY key: PRIMARY key_len: 8 ref: mysql.tbl.collation_id rows: 1 filtered: 100.00 Extra: Using index *************************** 5. row *************************** id: 1 select_type: SIMPLE table: ts partitions: NULL type: eq_ref possible_keys: PRIMARY key: PRIMARY key_len: 8 ref: mysql.tbl.tablespace_id rows: 1 filtered: 100.00 Extra: Using index *************************** 6. row *************************** id: 1 select_type: SIMPLE table: stat partitions: NULL type: const possible_keys: PRIMARY key: PRIMARY key_len: 388 ref: const,const rows: 1 filtered: 100.00 Extra: Using index 6 rows in set, 1 warning (0.02 sec) - 通过 Statement Outline特性固定执行计划,将表连接顺序调整为cat->tbl->sch。
mysql> CALL dbms_outln.add_optimizer_outline('', '', 1, -> '/*+ JOIN_ORDER(@`select#1` cat,tbl,sch) */', -> 'SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE, TABLE_COMMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = "mysql" AND TABLE_NAME = "user"'); Query OK, 0 rows affected (0.01 sec) - 查看 Statement Outline规则是否生效。
mysql> call dbms_outln.show_outline()\G *************************** 1. row *************************** ID: 2 SCHEMA: DIGEST: 383a6c3a24768145b30ac43eb34f479bb679361b499d697e2855af23a5bb394d TYPE: OPTIMIZER SCOPE: POS: 1 HINT: /*+ JOIN_ORDER(@`select#1` cat,tbl,sch) */ HIT: 0 OVERFLOW: 0 DIGEST_TEXT: SELECT `TABLE_SCHEMA` , TABLE_NAME , `TABLE_TYPE` , `TABLE_COMMENT` FROM `INFORMATION_SCHEMA` . `TABLES` WHERE `TABLE_SCHEMA` = ? AND TABLE_NAME = ? 1 row in set (0.00 sec) - 执行业务 SQL,确认表连接顺序调整为cat->tbl->sch。
mysql> EXPLAIN SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE, TABLE_COMMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = "mysql" AND TABLE_NAME = "user"\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: cat partitions: NULL type: index possible_keys: PRIMARY key: name key_len: 194 ref: NULL rows: 1 filtered: 100.00 Extra: Using index *************************** 2. row *************************** id: 1 select_type: SIMPLE table: tbl partitions: NULL type: ALL possible_keys: schema_id key: NULL key_len: NULL ref: NULL rows: 478 filtered: 10.00 Extra: Using where; Using join buffer (hash join) *************************** 3. row *************************** id: 1 select_type: SIMPLE table: sch partitions: NULL type: eq_ref possible_keys: PRIMARY,catalog_id key: PRIMARY key_len: 8 ref: mysql.tbl.schema_id rows: 1 filtered: 16.67 Extra: Using where *************************** 4. row *************************** id: 1 select_type: SIMPLE table: col partitions: NULL type: eq_ref possible_keys: PRIMARY key: PRIMARY key_len: 8 ref: mysql.tbl.collation_id rows: 1 filtered: 100.00 Extra: Using index *************************** 5. row *************************** id: 1 select_type: SIMPLE table: ts partitions: NULL type: eq_ref possible_keys: PRIMARY key: PRIMARY key_len: 8 ref: mysql.tbl.tablespace_id rows: 1 filtered: 100.00 Extra: Using index *************************** 6. row *************************** id: 1 select_type: SIMPLE table: stat partitions: NULL type: const possible_keys: PRIMARY key: PRIMARY key_len: 388 ref: const,const rows: 1 filtered: 100.00 Extra: Using index 6 rows in set, 1 warning (0.00 sec) - 删除Statement Outline规则。
mysql> call dbms_outln.del_outline(2); Query OK, 0 rows affected (0.01 sec) mysql> call dbms_outln.show_outline(); Empty set (0.01 sec)
- 默认情况下,以下查询语句的表连接顺序为 cat->sch->tbl。






