DocsICP FilingConsole
官方文档
首页

Statement Outline

更新时间：
复制 MD 格式
产品详情
我的收藏

SQL query plans in production can shift unexpectedly when table statistics change or the optimizer picks a different path, causing latency spikes without any change to your application code. Statement Outline lets you lock a query to a specific execution plan by injecting optimizer hints or index hints at query time — no SQL changes required. Install the DBMS_OUTLN package to use this feature.

Prerequisites

Before you begin, ensure that you have:

  • A PolarDB-X Standard Edition instance with the MySQL 8.0 engine

Quick start

The following examples cover the most common tasks. All examples use CALL dbms_outln.<procedure>() syntax.

Force a specific index

CALL dbms_outln.add_index_outline('mydb', '', 1, 'USE INDEX', 'idx_col1', '',
  "SELECT * FROM t1 WHERE t1.col1 = 1 AND t1.col2 = 'value'");

Set a query timeout

CALL dbms_outln.add_optimizer_outline('mydb', '', 1, '/*+ MAX_EXECUTION_TIME(1000) */',
  "SELECT * FROM t1 WHERE id = 1");

Verify an outline is active

CALL dbms_outln.preview_outline('mydb', "SELECT * FROM t1 WHERE t1.col1 = 1 AND t1.col2 = 'value'");

Check outline hit counts

CALL dbms_outln.show_outline();

How it works

AliSQL uses a system table named mysql.outline to store outline rules, created automatically on instance startup. When a query runs, AliSQL looks up the stored rules and injects the matching hints into the query before the optimizer generates a plan — keeping the plan stable without any changes to your application SQL.

Statement Outline supports two hint types from MySQL 8.0 and MySQL 5.7:

  • Optimizer hints — scoped at the global, table, index, or join-order level. See Optimizer Hints.

  • Index hints — classified by type (USE INDEX, FORCE INDEX, IGNORE INDEX) and scope (FOR JOIN, FOR ORDER BY, FOR GROUP BY). See Index Hints.

Outline table reference

AliSQL stores outline rules in the mysql.outline system table, created automatically on instance startup. To recreate it manually, run:

CREATE TABLE `mysql`.`outline`(
 Id bigint AUTO_INCREMENT NOT NULL,
 Schema_name varchar(64) DEFAULT NULL,
 Digest varchar(64) NOT NULL,
 Digest_text longtext DEFAULT NULL,
 Type enum('IGNORE INDEX','USE INDEX','FORCE INDEX','OPTIMIZER') COLLATE utf8mb3_general_ci NOT NULL,
 Scope enum('','FOR JOIN','FOR ORDER BY','FOR GROUP BY') COLLATE utf8mb3_general_ci DEFAULT '',
 State enum('N','Y') COLLATE utf8mb3_general_ci DEFAULT 'Y' NOT NULL,
 Position bigint NOT NULL,
 Hint text NOT NULL,
 PRIMARY KEY Outline_id(id)
)engine=InnoDB STATS_PERSISTENT=0 CHARACTER SET utf8mb3 COLLATE utf8mb3_bin comment='Statement outline' TABLESPACE=mysql
ColumnDescription
IdRow ID of the outline rule
Schema_nameDatabase name
Digest64-character hash derived from Digest_text
Digest_textNormalized SQL text used to compute the digest
TypeOPTIMIZER for optimizer hints; USE INDEX, FORCE INDEX, or IGNORE INDEX for index hints
ScopeApplies to index hints only. Valid values: FOR JOIN, FOR ORDER BY, FOR GROUP BY. An empty string matches all scopes
StateY to enable the outline rule; N to disable it
PositionFor optimizer hints: the position of the keyword in query blocks (starts at 1). For index hints: the position of the table in the query (starts at 1)
HintFor optimizer hints: the full hint string, such as /*+ MAX_EXECUTION_TIME(1000) */. For index hints: a comma-separated list of index names, such as ind_1,ind_2

Manage outlines

The DBMS_OUTLN package provides six procedures for managing outline rules.

Add an optimizer outline

CALL dbms_outln.add_optimizer_outline('<Schema_name>', '<Digest>', <query_block>, '<hint>', '<query>');

Pass either Digest or query. If you pass the full query text, DBMS_OUTLN calculates Digest and Digest_text automatically.

Example: Set a 1-second execution timeout for a specific query.

CALL dbms_outln.add_optimizer_outline("outline_db", '', 1, '/*+ MAX_EXECUTION_TIME(1000) */',
  "SELECT * FROM t1 WHERE id = 1");

Add an index outline

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

Pass either Digest or Query. If you pass the full query text, DBMS_OUTLN calculates Digest and Digest_text automatically.

Example: Force ind_1 for a query on t1.

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 an outline match

Use preview_outline to check which rules match a given query before running it in production.

CALL dbms_outln.preview_outline('<Schema_name>', '<Query>');

Example:

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)

A row in the result confirms the rule matched and the hint will be applied when the query runs.

Show outline hit counts

Use show_outline to see all active outline rules in memory along with how many times each rule has matched.

CALL dbms_outln.show_outline();

Example output:

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

Output column reference:

ColumnDescription
IDRow ID of the outline rule
SCHEMADatabase name the rule applies to
DIGESTHash of the normalized SQL statement
TYPEHint type: OPTIMIZER, USE INDEX, FORCE INDEX, or IGNORE INDEX
SCOPEIndex hint scope: FOR JOIN, FOR ORDER BY, FOR GROUP BY, or empty (all scopes)
POSPosition of the query block (optimizer hints) or table (index hints) the rule targets
HINTThe hint string or index name list injected into the query
HITNumber of times the rule located the target query block or table and applied the hint
OVERFLOWNumber of times the rule did not find the destination query block or table
DIGEST_TEXTNormalized SQL text the rule matches against

Delete an outline

CALL dbms_outln.del_outline(<Id>);

This removes the rule from memory or the outline table.

Example:

CALL dbms_outln.del_outline(32);

If the rule ID does not exist, the procedure returns warnings instead of an error. Run SHOW WARNINGS to see the details:

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)

Reload outlines from the table

If you modify the outline table directly (for example, with an UPDATE statement), run flush_outline to reload the rules into memory.

CALL dbms_outln.flush_outline();

Example: Update a Position value and reload.

UPDATE mysql.outline SET Position = 1 WHERE Id = 18;
-- Query OK, 1 row affected (0.00 sec)

CALL dbms_outln.flush_outline();
-- Query OK, 0 rows affected (0.01 sec)

Verify an outline is active

Two methods confirm whether an outline rule is being applied.

Method 1: preview_outline

Run preview_outline with the target query. If a row is returned, the rule matched and the hint will be applied.

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)

The HINT column shows USE INDEX (ind_1), confirming the index outline matched and will be applied when this query runs.

Method 2: EXPLAIN with SHOW WARNINGS

Run EXPLAIN on the query, then SHOW WARNINGS. The warning message contains the rewritten SQL with the injected hint. If the hint appears in the Message field, the outline is active.

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)

The USE INDEX (ind_1) in the rewritten SQL confirms the outline is active and being applied to this query.

Previous：Statement concurrency controlNext：Returning