ステートメントアウトラインの使用と管理 - PolarDB - Alibaba Cloud ドキュメントセンター

運用環境では、SQL文の実行計画が頻繁に変更され、データベースが不安定になることがあります。 PolarDBにはステートメントアウトライン機能があり、オプティマイザーヒントまたはインデックスヒントを使用してSQLステートメントの実行計画を安定させることができます。 PolarDBは、ステートメントの概要を効率的に管理するためのDBMS_OUTLNツールキットも提供します。このトピックでは、ステートメントの概要を使用および管理する方法について説明します。

サポートされているバージョン

PolarDBクラスターは、次のいずれかのデータベースエンジンバージョンを実行します。

リビジョンバージョンが5.6.1.0.36以降のPolarDB for MySQL5.6。
リビジョンバージョンが5.7.1.0.2以降のPolarDB for MySQL5.7。
リビジョンバージョンが8.0.1.1.1以降のPolarDB for MySQL 8.0.1。
PolarDB for MySQL 8.0.2。

PolarDBクラスターのデータベースエンジンバージョンを照会する方法については、「エンジンバージョンの照会」をご参照ください。

ヒント

ステートメントアウトライン機能は、MySQL 8.0によって提供される次の種類のヒントをサポートします。

オプティマイザーのヒント
オプティマイザーヒントは、適用可能なスコープとヒントオブジェクトに基づいて、テーブルレベルのオプティマイザーヒント、インデックスレベルのオプティマイザーヒント、および結合順序オプティマイザーヒントに分類されます。詳細については、「Optimizer Hints」をご参照ください。
説明
Optimizerヒントは、PolarDB for MySQL 5.6クラスターでは使用できません。
インデックスヒント
インデックスヒントは、タイプとスコープに基づいて分類されます。詳細については、「インデックスのヒント」をご参照ください。

Parameters

PolarDBコンソールにログインして、opt_outline_enabledパラメーターを設定することで、ステートメントアウトライン機能を有効または無効にできます。パラメーターの設定方法については、「クラスターおよびノードパラメーターの設定」をご参照ください。

パラメーター

レベル

説明

loose_opt_outline_enabled

全世界

ステートメントアウトライン機能を有効にするかどうかを指定します。有効な値：

ON (デフォルト)
オフ

アウトラインテーブル

PolarDBは、outlineという名前の組み込みシステムテーブルを使用してヒントを格納します。システムは起動時に自動的にアウトラインテーブルを作成します。次のステートメントを使用して、アウトラインテーブルを作成します。

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	SQL文が実行されるデータベーススキーマ。
ダイジェスト	Digest_textパラメーターの値に基づいて計算される64バイトのステートメントのダイジェストハッシュ値。詳細については、「STATEMENT_DIGEST() 」をご参照ください。
ダイジェスト_テキスト	SQL文のダイジェスト。
タイプ	オプティマイザーヒントの場合、ヒントタイプはoptimizerです。インデックスヒントの場合、ヒントの種類はUSE index、FORCE INDEX、またはIGNORE INDEXです。
スコープ	ヒントが適用されるコンテキスト。このパラメーターは、インデックスヒントにのみ必要です。有効な値：グループによってオーダーバイ参加のため説明空の文字列は、ヒントがすべてのコンテキストに適用されることを指定します。
状態	ヒントを有効にするかどうかを指定します。有効な値： N Y (デフォルト)
位置	オプティマイザーヒントの場合、Positionパラメーターは、オプティマイザーヒントが適用されるクエリブロックの数を指定します。 Positionパラメーターの値は、1から始まる数値識別子です。インデックスヒントの場合、Positionパラメーターは、インデックスヒントが適用されるテーブルの数を指定します。 Positionパラメーターの値は、1から始まる数値識別子です。
ヒント	オプティマイザーヒントの場合、Hintパラメーターはヒント文字列を指定します。例: `/* + MAX_EXECUTION_TIME(1000) */` インデックスヒントの場合、Hintパラメーターはインデックス名のリストを指定します。例: `ind_1,ind_2`

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

説明

Schema_nameパラメーターが空でないステートメントアウトラインは、SQLステートメントのSchema_nameパラメーターとDigestパラメーターの値が、ステートメントアウトラインのSchema_nameパラメーターとDigestパラメーターの値と一致する場合にのみ有効になります。
Schema_nameパラメーターが空のステートメントアウトラインは、SQLステートメントのDigestパラメーターの値がステートメントアウトラインのDigestパラメーターの値と一致する場合にのみ有効になります。

ステートメントの概要を効率的に管理するために、PolarDBはDBMS_OUTLNツールキット内の一連の定義済みプロシージャを提供します。

add_optimizer_outline: オプティマイザーヒントを追加します。
add_index_outline: インデックスヒントを追加します。
preview_outline: SQL文が既存のアウトラインと一致するかどうかを確認します。これは、手動検証に役立ちます。
show_outline: 一致し、メモリで使用されるステートメントのアウトラインを表示します。
del_outline: メモリとアウトラインテーブルからステートメントアウトラインを削除します。

add_optimizer_outline

構文

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

Parameters

パラメーター	説明
Schema_name	SQL文が実行されるデータベーススキーマ。
ヒント	ヒント文字列 (*/ + MAX_EXECUTION_TIME(1000) /* など) 。
クエリ	ステートメントアウトラインを使用する元のSQLステートメント。

説明

add_optimizer_outlineプロシージャは、PolarDB for MySQL 5.6クラスターではサポートされていません。

Queryステートメントの部品に引用が必要な場合は、単一引用符 (') を使用してQueryステートメントの部品を囲み、二重引用符 (') を使用してQueryステートメントを囲みます。
Queryステートメントで一重引用符 (') または二重引用符 (") を使用するかどうかに関係なく、ステートメントのアウトラインは、クエリのダイジェストがステートメントのアウトラインと一致する場合にのみクエリに適用されます。

例

元のクエリ文:

SELECT * FROM t1 WHERE name="Tom";

変更されたクエリ文:

SELECT * FROM t1 WHERE name='Tom';

クエリ文には、文のアウトラインが使用されます。

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ステートメントを指定します。
ステートメントでDigestまたはQueryパラメーターを指定できます。クエリステートメントを指定した場合、DBMS_OUTLNはDigestおよびDigest_textパラメーターの値を計算します。

Parameters

パラメーター	説明
Schema_name	SQL文が実行されるデータベーススキーマ。
ダイジェスト	Digest_textパラメーターに基づいて計算される64バイトのステートメントのダイジェストハッシュ値。詳細については、「STATEMENT_DIGEST() 」をご参照ください。
位置	インデックスヒントが適用されるテーブルの番号。 Positionパラメーターの値は、1から始まる数値識別子です。
タイプ	ヒントタイプ。有効な値: USE INDEX、FORCE INDEX、またはIGNORE INDEX。
ヒント	ind_1、ind_2などのインデックス名のリスト。
スコープ	ヒントが適用されるコンテキスト。有効な値：グループによってオーダーバイ参加のため説明空の文字列は、ヒントがすべてのコンテキストに適用されることを指定します。
クエリ	ステートメントアウトラインを使用する元の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は、ステートメントアウトラインのメモリ内ヒット数を示します。 [オーバーフロー] は、クエリブロックまたはテーブルが見つからなかった回数を示します。

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)

シナリオ

オプティマイザーヒントを使用したインデックスの指定

構文

説明

PolarDB for MySQL 5.6はヒントをサポートしていません。インデックスのアウトラインを使用してインデックスを指定できます。

/*+ INDEX(table_name idx) */

例

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

インデックスヒントを使用したインデックスの指定

構文

説明

PolarDB for MySQL 5.6などのヒントをサポートしないバージョンでは、USE句またはFORCE INDEX句を使用してインデックスを指定できます。

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

Positionパラメーターは、ステートメントのアウトラインが有効になるテーブルを指定します。パラメータの初期値は1です。 Hintパラメーターは、Positionパラメーターで指定されたテーブルに適用されます。

例

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操作の注文の指定

構文

説明

ヒントを使用して、クエリでJOIN操作の注文を強制することができます。ヒントにすべてのテーブルを含める必要はありません。指定されたテーブルが最初に結合されます。オプティマイザは、残りのテーブルの順序を自動的に決定します。

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

SQL文の変数の設定

構文

説明

特定のSQL文でのみ有効になる変数値を指定できます。

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

例

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

テスト

次のいずれかの方法を使用して、ステートメントのアウトラインが有効かどうかを確認できます。

preview_outlineステートメントを実行します。詳細については、「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ステートメントを実行します。

説明

[追加] 列の結果は、次のバージョンでのみ表示されます。

リビジョンバージョンが8.0.1.0.34以降のPolarDB for MySQL 8.0.1。
リビジョンバージョンが8.0.2.2.27以降のPolarDB for MySQL 8.0.1。

サンプル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)