ステートメント概要 - PolarDB - Alibaba Cloud ドキュメントセンター

運用環境でのSQL文の実行計画は常に変化します。したがって、データベースが不安定になる可能性があります。 PolarDB-X Standard Editionは、オプティマイザーヒントとインデックスヒントを使用して、システムが安定した方法でクエリプランを実行できるようにするステートメントアウトライン機能を提供します。この機能を使用するには、DBMS_OUTLNパッケージをインストールします。

前提条件

インスタンスのエディションはPolarDB-X Standard editionで、エンジンはMySQL 8.0です。

機能の説明

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

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

概要テーブル

AliSQLは、outlineという名前のシステムテーブルを使用してヒントを格納します。インスタンスシステムは、システムの起動時に自動的にテーブルを作成します。次のステートメントを実行して、テーブルを作成できます。

CREATE TABLE 'mysql'.'outline '(
 Id bigint AUTO_INCREMENT NOT NULL、
 Schema_name varchar (64) DEFAULT NULL、
 ダイジェストvarchar (64) NOT NULL,
 Digest_text longtext DEFAULT NULL、
 タイプenum('IGNORE INDEX' 、'USE INDEX' 、'FORCE INDEX' 、'OPTIMIZER') COLLATE utf8mb3_general_ci NOT NULL、
 スコープの列挙 (''、'FOR JOIN' 、'FOR ORDER BY' 、'FOR GROUP BY') COLLATE utf8mb3_general_ci DEFAULT'' 、
 状態列挙 ('N','Y') COLLATE utf8mb3_general_ci DEFAULT 'Y' NOT NULL,
 位置bigint NOT NULL、
 ヒントテキストNOT NULL、
 PRIMARYキーOutline_id(id)
) engine=InnoDB STATS_PERSISTENT=0 CHARACTER SET utf8mb3 COLLATE utf8mb3_bin comment='Statement outline 'TABLESPACE=mysql

次の表に、ステートメントのパラメーターを示します。

パラメーター	説明
Id	アウトラインテーブルのID。
Schema_name	データベースの名前。
ダイジェスト	DIGEST_TEXTパラメーターから取得した64バイトのハッシュ文字列。
データ型	オプティマイザヒントのヒントタイプはoptimizerです。インデックスヒントのヒントタイプは、USE index、FORCE INDEX、またはIGNORE INDEXです。
スコープ	このパラメーターは、インデックスヒントにのみ必要です。有効な値：グループによってのため注文のため参加のため空の文字列は、すべての型のインデックスヒントを示します。
状態	ステートメントアウトラインを有効にするかどうかを指定します。
Position	オプティマイザーヒントの場合、すべてのオプティマイザーヒントがクエリブロックに適用されるため、Positionパラメーターはクエリブロック内のキーワードの位置になります。 Positionの値は、ヒントによって適用されるキーワードの順序を示します。 Positionの有効な値は1から始まります。インデックスヒントの場合、Positionパラメーターはテーブルの位置です。 Positionの値は、ヒントによって適用されるテーブルの順序を示します。有効な値は1から始まります。
ヒント	オプティマイザヒントの場合、Hintは、/* + EXECUTION_TIME(1000) * /などの統合ヒント文字列を示します。インデックスヒントの場合、Hintパラメーターはind_1、ind_2などのインデックス名のリストを指定します。

ステートメントアウトラインの管理

AliSQLは、DBMS_OUTLNパッケージに6つの管理インターフェイスを提供します。次のリストは、インターフェイスについて説明します。

add_optimizer_outline
オプティマイザーヒントを追加します。サンプルコマンド:
```
dbms_outln.add_optimizer_outline('<Schema_name>','<Digest>','<query_block>','<hint>','<query>');
```
説明
ステートメントにDigestまたはQueryを入力できます。クエリパラメーターにクエリステートメントを入力すると、DBMS_OUTLNはDigestパラメーターとDigest_textパラメーターの値を計算します。
例：
```
mysql> dbms_outln.add_optimizer_outline("outline_db", '', 1, '/* + MAX_EXECUTION_TIME(1000) */' 、
                                      "select * from t1 where id = 1"); 
```
add_index_outline
インデックスヒントを追加します。サンプルコマンド:
```
dbms_outln.add_index_outline('<Schema_name>' 、'<Digest>' 、<Position> '、'<Type>'、'<Hint>'、'<Scope>'、'<Query>');
```
説明
ステートメントにDigestまたはQueryを入力できます。クエリパラメーターにクエリステートメントを入力すると、DBMS_OUTLNはDigestパラメーターとDigest_textパラメーターの値を計算します。
例：
```
mysql> dbms_outln.add_index_outline ('out_db' 、''、1、'USE INDEX' 、'ind_ 1 '、'' 、''、を呼び出します。
                                "select * from t1 where t1.col1 =1およびt1.col2 ='xpchild '"); 
```

preview_outline

手動検証に使用できるステートメントのアウトラインを使用してSQLステートメントの一致結果を照会するには、サンプルコマンドを実行します。

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

例：

mysql> dbms_outln.preview_outline ('out_db' 、"select * from t1 where t1.col1 =1 and t1.col2 ='xpchild'");
----------- ------------------------------------------------------------------ -------------------------------------------------- --------------------- +
| SCHEMA | DIGEST | BLOCK_TYPE | BLOCK_NAME | ブロック | ヒント |
----------- ------------------------------------------------------------------ -------------------------------------------------- --------------------- +
| outline_db | b4369611be7ab2d27c85897632576a04bc08f50b928a1d735b62d0a140628c4c | TABLE | t1 | 1 | USE INDEX ('ind_1 ') |
----------- ------------------------------------------------------------------ -------------------------------------------------- --------------------- +
1行セット (0.00秒)

show_outline

メモリ内のステートメントアウトラインのヒットを照会します。サンプルコマンド:

dbms_outln.show_outline();

例：

mysql> dbms_outln.show_outline() を呼び出します。------ ----------- ------------------------------------------------------------------ ------------------------------------ ------------------------------------------------------- ------------------ ------------------------------------------------------------------------------------- +
| ID | スキーマ | DIGEST | タイプ | スコープ | POS | ヒント | ヒット | オーバーフロー | 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 | オプティマイザー | | 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' から 't1' 、't2' |
| 35 | outline_db | 5a726a609b6fbfb76bb8f9d2a24af913a2b9d07f015f2ee1f6f2d12dfad72e6f | OPTIMIZER | | 2 | /* + QB_NAME(subq1) */ | 2 | 0 | SELECT * FROM 't1' WHERE 't1'. 'col1' IN ('t2' から 'col1' を選択) |
| 36 | outline_db | 5a726a609b6fbfb76bb8f9d2a24af913a2b9d07f015f2ee1f6f2d12dfad72e6f | OPTIMIZER | | 1 | /* + SEMIJOIN(@ subq1 MATERIALIZATION、DUPSWEEDOUT) */ | 2 | 0 | SELECT * FROM 't1' WHERE 't1'. 'col1' IN ('t2' から 'col1' を選択) |
| 30 | outline_db | b4369611be7ab2d27c85897632576a04bc08f50b928a1d735b62d0a140628c4c | 使用インデックス | | 1 | ind_1 | 3 | 0 | SELECT * FROM 't1' WHERE 't1'. 'col1' = ? AND 't1' 。 'col2' = ?                    |
| 31 | outline_db | 33c71541754093f78a1f2108795cfb45f8b15ec5d6bff76884f4461fb7f33419 | 使用インデックス | | 2 | ind_2 | 1 | 0 | SELECT * FROM 't1' , 't2' WHERE't1'. 'col1' = 't2' 。 'col1' AND 't2' 。 'col2' = ? |
------ ----------- ------------------------------------------------------------------ ------------------------------------ ------------------------------------------------------- ------------------ ------------------------------------------------------------------------------------- +
セットの7行 (0.00秒)

次の表に、HITおよびOVERFLOWパラメーターを示します。

パラメーター	説明
ヒット	ステートメントのアウトラインが目的のクエリブロックまたはテーブルを見つけた回数。
オーバーフロー	ステートメントのアウトラインで目的のクエリブロックまたはテーブルが見つからない回数。

del_outline

メモリまたはテーブルからステートメントのアウトラインを削除します。サンプルコマンド:

dbms_outln.del_outline(<Id>);

例：

mysql> dbms_outln.del_outline(32) を呼び出します。

説明

削除するステートメントアウトラインルールが存在しない場合は、エラーが返されます。 show warnings; ステートメントを実行して、エラーメッセージを表示できます。

mysql> dbms_outln.del_outline(1000) を呼び出します。クエリOK、影響を受ける0行、2警告 (0.00秒)

mysql> 警告を表示します。+ -------- + ------- + ---------------------------------------------- +
| レベル | コード | メッセージ |
+ -------- + ------- + ---------------------------------------------- +
| 警告 | 7521 | ステートメントの概要1000がテーブルに見つかりません |
| 警告 | 7521 | 文のアウトライン1000がキャッシュに見つかりません |
+ -------- + ------- + ---------------------------------------------- +
セットの2列 (0.00秒)

flush_outline
アウトラインテーブルのステートメントアウトラインを変更する場合は、ステートメントアウトラインが再び有効になるように、次のステートメントを実行する必要があります。サンプルコマンド:
```
dbms_outln.flush_outline();
```
例：
```
mysql> update mysql.outline set Position = 1ここでId = 18;
クエリOK、影響を受ける1行 (0.00秒)
一致した行: 1変更: 1警告: 0

mysql> dbms_outln.flush_outline() を呼び出します。クエリOK、影響を受ける0行 (0.01秒) 
```

テスト

ステートメントのアウトラインが有効かどうかを確認する方法は2つあります。

preview_outlineステートメントを実行します。

mysql> dbms_outln.preview_outline ('out_db' 、"select * from t1 where t1.col1 =1 and t1.col2 ='xpchild'");
----------- ------------------------------------------------------------------ -------------------------------------------------- --------------------- +
| SCHEMA | DIGEST | BLOCK_TYPE | BLOCK_NAME | ブロック | ヒント |
----------- ------------------------------------------------------------------ -------------------------------------------------- --------------------- +
| outline_db | b4369611be7ab2d27c85897632576a04bc08f50b928a1d735b62d0a140628c4c | TABLE | t1 | 1 | USE INDEX ('ind_1 ') |
----------- ------------------------------------------------------------------ -------------------------------------------------- --------------------- +
1行セット (0.01秒)

EXPLAINステートメントを実行します。

mysql> select * from t1 (t1.col1 =1および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列、1警告 (0.00秒)

mysql> 警告を表示します。+ ------ ------ + --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- +
| レベル | コード | メッセージ |
+ ------ ------ + --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- +
| 注: 1003 | /* select#1 */ select 'outline_db'.'t1'.'id' AS 'id','outline_db'.'t1'.'col1' AS 'col1', 'outlin_db'. 't1'.'col2' は 'outlin_db'. 't1' から 'col2' としてINDEX ('ind_1) を使用してください (('outlin_db'. 't1'.'col1' = 1) および ('outlin_db'. 't1'.'col2' = 'xpchild')) |
+ ------ ------ + --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- +
1行セット (0.00秒)