ステートメント概要の使用と管理方法 - PolarDB - Alibaba Cloud ドキュメントセンター

本番環境では、SQL 文の実行計画が頻繁に変更され、データベースの不安定化を引き起こすことがあります。PolarDB では、オプティマイザヒントおよびインデックスヒントを活用して MySQL の実行計画を安定化します。この手法を「ステートメント概要（Statement Outline）」と呼びます。PolarDB は、ステートメント概要を迅速に実装するための DBMS_OUTLN パッケージも提供しています。本トピックでは、ステートメント概要の使用方法および管理方法について説明します。

サポート対象バージョン

ご利用の PolarDB クラスターは、以下のいずれかのバージョンを実行している必要があります。

PolarDB for MySQL 5.6（マイナーバージョン 5.6.1.0.36 以降）
PolarDB for MySQL 5.7（マイナーバージョン 5.7.1.0.2 以降）
PolarDB for MySQL 8.0.1（マイナーバージョン 8.0.1.1.1 以降）
PolarDB for MySQL 8.0.2

クラスターのバージョンは、バージョン番号の表示により確認できます。

クイックスタート

オプティマイザヒントによるインデックスの指定

構文

説明

バージョン 5.6 ではヒントがサポートされていません。このバージョンでインデックスを指定する場合は、代わりにインデックス概要（Index Outline）を使用してください。

/*+ INDEX(table_name idx) */

例

CALL dbms_outln.add_optimizer_outline('test', '/*+ INDEX(t1 i_a) */', 'SELECT test.t1.a AS a FROM test.t1');

インデックスヒントによるインデックスの指定

構文

説明

インデックスを指定するには、USE または FORCE INDEX を使用します。この手法は、ヒントをサポートしないバージョン 5.6 でも動作します。

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

パラメーターの説明

Position パラメーターは、概要が適用されるテーブルを指定します。このパラメーターは 1 基準の序数であり、SQL テキスト内のテーブルの順序に対応します。Position の値は、Hint パラメーターが適用されるテーブルの序数です。
Digest パラメーターについては、通常空文字列を指定します。詳細については、「add_index_outline」をご参照ください。

例

CALL dbms_outln.add_index_outline('outline_db', '', 1, 'USE INDEX', 'ind_1', '',
                                  "SELECT * FROM t1 WHERE t1.col1 =1 AND t1.col2 ='xpchild'");

結合順序の指定

構文

説明

このヒントは特定の結合順序を強制します。すべてのテーブルをリストする必要はありません。リストされたテーブルが最初に結合され、残りのテーブルの順序はオプティマイザーが決定します。

/*+ JOIN_PREFIX(t1, t2, ...) */

例

CALL dbms_outln.add_optimizer_outline('outline_db', '/*+ JOIN_PREFIX(it1, it2) */',
                                      'SELECT it3.id3, it2.i2, it1.id2
                                       FROM t3 it3, t1 it1, t2 it2
                                       WHERE it3.i3 = it1.id1
                                       AND it2.id2 = it1.id2
                                       GROUP BY it3.id3, it1.id2
                                       ) t, t2 ot
                                       WHERE ot.id2 = t.id2');

単一ステートメントに対する変数の設定

構文

説明

この文にのみ適用される変数の値を設定します。

/*+ SET_VAR(<var_name>=<var_value>) */

例

CALL dbms_outln.add_optimizer_outline('test', '/*+ SET_VAR(max_execution_time=1) */',
                                      'SELECT * FROM t1 ');

行ストアまたは列ストア実行の手動指定

列ストアインデックスをサポートする読み取り専用ノードを備えたクラスターでは、ステートメント概要を使用して、ステートメントを行ストアの読み取り専用ノードまたは列ストアインデックスの読み取り専用ノードのいずれかで実行するよう強制できます。

ヒントのフォーマット：

# 列ストアの使用を強制
/*+ SET_VAR(cost_threshold_for_imci=0) */

# 行ストアの使用を強制
/*+ SET_VAR(use_imci_engine=OFF) */

概要：

# 列ストアインデックスの読み取り専用ノードでの実行を強制
CALL dbms_outln.add_optimizer_outline('test', '/*+ SET_VAR(cost_threshold_for_imci=0) */', 'SELECT test.t1.a AS a FROM test.t1');

# 行ストアの読み取り専用ノードでの実行を強制
CALL dbms_outln.add_optimizer_outline('test', '/*+ SET_VAR(use_imci_engine=OFF) */', 'SELECT test.t1.a AS a FROM test.t1');

機能概要

ステートメント概要は、公式 MySQL 8.0 のすべてのヒントタイプをサポートしています。これらは以下の 2 つのカテゴリに分類されます。

オプティマイザーヒント
これらには、テーブルレベル、インデックスレベル、および結合順序のオプティマイザヒントが含まれます。詳細については、「オプティマイザヒント」をご参照ください。
説明
PolarDB for MySQL 5.6 ではオプティマイザヒントがサポートされていません。
インデックスヒント
これらはタイプと範囲で分類されます。詳細については、「インデックスヒント」をご参照ください。

パラメーターの説明

PolarDB コンソールにログインします。[パラメーター設定] ページで、パラメーターの設定を使用して、ステートメント概要またはシャーディング概要を有効化または無効化します。

パラメーター

レベル

説明

loose_opt_outline_enabled

グローバル

ステートメント概要の有効化／無効化を制御します。有効な値は以下のとおりです。

ON（デフォルト）：ステートメント概要を有効化します。
OFF：ステートメント概要を無効化します。

loose_outline_templated_digest_for_sharding_table

セッション

テーブルシャーディング概要機能のトグル設定です。有効な値は以下のとおりです。

ON（デフォルト）
OFF

説明

このパラメーターは、以下のバージョンでのみ適用されます。

PolarDB for MySQL 8.0.1（マイナーバージョン 8.0.1.1.54 以降）
PolarDB for MySQL 8.0.2（マイナーバージョン 8.0.2.2.33 以降）

ステートメント概要の管理

説明

Schema_name が空でない場合、SQL ステートメントの Schema_name およびそのステートメントの Digest 値が、ステートメント概要ルールの Schema_name および Digest 値と一致する必要があります。
Schema_name が空の場合、SQL ステートメントの Digest のみが、ステートメント概要ルールの Digest と一致すればよいです。

ステートメント概要を便利に管理するため、PolarDB では DBMS_OUTLN 内に 5 つのローカルストアドプロシージャを定義しており、以下に示します。

add_optimizer_outline：オプティマイザヒントを追加します。
add_index_outline：インデックスヒントを追加します。
preview_outline：ステートメント概要にマッチするステートメントをプレビューします。手動で概要を検証する際に使用します。
show_outline：ステートメント概要がメモリ内でアクティブであるかどうかを確認します。
del_outline：ステートメント概要をメモリおよびシステムテーブルから削除します。

add_optimizer_outline

構文

dbms_outln.add_optimizer_outline('<Schema_name>','<Hint>','<query>');

パラメーター

パラメーター	説明
Schema_name	データベース名です。
Hint	オプティマイザヒントでは、Hint は完全なヒント文字列（例：*/+MAX_EXECUTION_TIME(1000) /*）です。
Query	ステートメント概要を追加する元の SQL ステートメントです。

説明

PolarDB for MySQL バージョン 5.6 では、add_optimizer_outline はサポートされていません。
Query ステートメントで引用符が必要な場合、引用符を必要とする部分を Query ステートメント内でシングルクォートで囲み、Query 全体をダブルクォートで囲んでください。
Query ステートメント内にシングルクォートを含むステートメント概要を作成した場合、ステートメント概要のマッチングは、シングルクォートまたはダブルクォートのいずれを使用しても成功します。

例

元の Query ステートメント：

SELECT * FROM t1 WHERE name="Tom";

修正後の Query ステートメント：

SELECT * FROM t1 WHERE name='Tom';

ステートメント概要を追加するための Query ステートメント：

CALL dbms_outln.add_optimizer_outline("", "/*+ max_execution_time(1000) */", "SELECT * FROM t1 WHERE name='Tom'");

add_index_outline

構文

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

説明

Query は、ステートメント概要を追加する元の SQL ステートメントです。
ダイジェストまたはクエリのいずれかを選択します。クエリを選択した場合、DBMS_OUTLN はダイジェストおよびダイジェストテキストを計算します。

パラメーター

パラメーター	説明
Schema_name	データベース名です。
Digest	Digest_text をハッシュ化した 64 バイトのハッシュ文字列です。詳細については、「STATEMENT_DIGEST()」をご参照ください。
Position	Position はテーブルの位置を示し、1 から始まります。ヒントが N 番目のテーブルに適用される場合、Position は N となります。
Type	インデックスヒントでは、ヒントの種類として USE INDEX、FORCE INDEX、IGNORE INDEX のいずれかを指定できます。
Hint	インデックスヒントでは、Hint はインデックス名のリスト（例：ind_1,ind_2）です。
Scope	以下の 3 種類があります。 FOR GROUP BY FOR ORDER BY FOR JOIN 説明空文字列は、すべての種類のインデックスヒントを意味します。
Query	ステートメント概要を追加する元の SQL ステートメントです。

例

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

構文

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.01 sec)

show_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 はステートメント概要がマッチした回数を示し、OVERFLOW はクエリブロックまたは対応するテーブルが見つからなかった回数を示します。

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

シャーディング概要

テーブルシャーディングのシナリオでは、構造は同一だが名前に連続する数字のサフィックスが付いた多数の物理テーブル（例：t_001、t_002、…、t_999）を作成することがよくあります。従来のステートメント概要では、各具体的なテーブル名に対して個別のルールを設定する必要があります。これにより、ルール数が膨大になり、メンテナンスコストが高くなり、見落としが頻発するという課題がありました。

シャーディング概要では、テーブル名およびカラム名の末尾にある連続する数字から自動的にテンプレートを作成します。たとえば、t_1、t_2、t_100 を単一のパターン t_? として認識します。これにより、1 つの概要ルールで、命名規則に従うすべてのシャードテーブルにマッチさせることができます。ルールを 1 回設定するだけで、すべての該当テーブルに適用されます。

比較項目	標準概要	テーブルシャーディング概要
テーブル名のマッチング	完全なテーブル名（例：`t_1`）を厳密にマッチします。	末尾の数字を自動的にテンプレート化（例：`t_?`）します。
適用範囲	単一の特定のテーブル	命名規則が一貫した複数のシャードテーブル
ルール数	各シャードテーブルごとに個別のルールが必要	1 つのルールで該当するすべてのシャードテーブルをカバー
メンテナンスの複雑さ	高い：一括作成・更新・削除が必要	低い：作成・更新・削除の統一管理により、強力な整合性を確保

範囲

ご利用の PolarDB クラスターは、以下のいずれかのバージョンである必要があります。

PolarDB for MySQL 8.0.1（マイナーバージョン 8.0.1.1.54 以降）
PolarDB for MySQL 8.0.2（マイナーバージョン 8.0.2.2.33 以降）

使用方法

PolarDB コンソールにログインします。[パラメーター設定] ページで、loose_outline_templated_digest_for_sharding_table パラメーターを設定して、シャーディング概要機能を有効化します。

専用のストアドプロシージャ add_optimizer_outline_sharding を呼び出します。そのパラメーター構造およびセマンティクスは add_optimizer_outline と同一です。唯一の違いは、テーブルシャーディングのテンプレート化機能が追加されている点です。

CALL dbms_outln.add_optimizer_outline_sharding(
  'test',                          -- Schema_name
  '',                              -- Digest（空のままにすると、システムが自動計算します）
  1,                               -- Position（1 基準のテーブル位置。N 番目のテーブルにヒントを適用する場合は、Position を N に設定します）
  '/*+ MAX_EXECUTION_TIME(1000) */', -- ヒント文字列
  "SELECT t_1.c_1 FROM t_1"        -- 元の SQL（t_1、t_2、t_100 など、任意のシャードテーブル名を含められます）
);

実行後、このルールは t_? パターンに一致するすべてのテーブル（例：t_1、t_2、t_100）に適用されます。

デモ例

ステートメント概要の効果を検証するには、以下のいずれかの方法を使用します。

preview_outline を使用して概要をプレビューします。

SQL コマンドは以下のとおりです。

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 を使用して実行計画を確認できます。

説明

Extra 列の値は、以下のバージョンでのみ表示されます。

PolarDB for MySQL 8.0.1（マイナーバージョン 8.0.1.0.34 以降）
PolarDB for MySQL 8.0.1（マイナーバージョン 8.0.2.2.27 以降）

SQL コマンドは以下のとおりです。

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; Using outline 1 |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+------------------------------+
1 row in set, 1 warning (0.00 sec)

SQL コマンドは以下のとおりです。

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)

付録：ステートメント概要テーブル

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	概要 ID です。
Schema_name	データベース名です。
Digest	Digest_text をハッシュ化した 64 バイトのハッシュ文字列です。詳細については、「STATEMENT_DIGEST()」をご参照ください。
Digest_text	SQL ステートメントの特徴です。
Type	オプティマイザヒントの場合、ヒントの種類は OPTIMIZER です。インデックスヒントの場合、ヒントの種類は USE INDEX、FORCE INDEX、IGNORE INDEX のいずれかです。
Scope	このパラメーターは、インデックスヒントでのみ必要です。以下の 3 種類があります。 FOR GROUP BY FOR ORDER BY FOR JOIN 説明空文字列は、すべての種類のインデックスヒントを意味します。
State	このルールが有効かどうかを示します。有効な値は以下のとおりです。 N Y（デフォルト）
Position	オプティマイザヒントの場合、Position はクエリブロックを示します。すべてのオプティマイザヒントはクエリブロックに適用される必要があります。Position は 1 から始まります。ヒントがステートメント内の N 番目のキーワードに適用される場合、Position は N となります。インデックスヒントの場合、Position はテーブルの位置を示します。これも 1 から始まります。ヒントが N 番目のテーブルに適用される場合、Position は N となります。
Hint	オプティマイザヒントの場合、Hint は完全なヒント文字列（例：`/+ MAX_EXECUTION_TIME(1000) /`）を表します。インデックスヒントの場合、Hint はインデックス名のリスト（例：`ind_1,ind_2`）を表します。