運用環境でのSQL文の実行計画は常に変化します。 したがって、データベースが不安定になる可能性があります。 PolarDB for MySQLは、オプティマイザーヒントとインデックスヒントを使用して実行計画の安定性を確保するステートメントアウトライン機能を提供します。 PolarDB for MySQLは、DBMS_OUTLNパッケージも提供します。 パッケージを使用して、ステートメントアウトライン機能を有効にできます。 このトピックでは、ステートメントアウトライン機能を使用および管理する方法について説明します。
前提条件
クラスターは、次のいずれかのバージョンを実行するPolarDBクラスターです。
リビジョンバージョンが5.6.1.0.36以降のPolarDB for MySQLの5.6。
リビジョンバージョンが5.7.1.0.2以降のPolarDB for MySQLの5.7。
リビジョンバージョンが8.0.1.1.1以降のPolarDB for MySQL 8.0.1。
PolarDB for MySQL 8.0.2。
クラスターのバージョンを確認する方法については、「エンジンバージョンの照会」をご参照ください。
ヒント
ステートメントアウトライン機能は、MySQL 8.0によって提供される次の種類のヒントをサポートします。
オプティマイザーのヒント
オプティマイザーヒントは、適用可能なスコープとヒントオブジェクトに基づいて、テーブルレベルのオプティマイザーヒント、インデックスレベルのオプティマイザーヒント、および結合順序オプティマイザーヒントに分類されます。 詳細については、「Optimizer Hints」をご参照ください。
説明オプティマイザヒントは、PolarDB for MySQL 5.6クラスターではサポートされていません。
インデックスヒント
インデックスヒントは、タイプとスコープに基づいて分類されます。 詳細については、「インデックスのヒント」をご参照ください。
Parameters
にログインします。PolarDBコンソール. パラメーター設定ページで、次の表に示すopt_outline_enableパラメーターを設定して、ステートメントアウトライン機能を有効または無効にできます。 詳細については、「クラスターパラメーターとノードパラメーターの指定」をご参照ください。
パラメーター | レベル | 説明 |
loose_opt_outline_enable | グローバル | ステートメントアウトライン機能を有効にするかどうかを指定します。 有効な値：
|
アウトラインテーブル
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' ロングテキストCOLLATE utf8_bin、
'Type' enum('IGNORE INDEX' 、'USE INDEX' 、'FORCE INDEX' 、'OPTIMIZER') CHARACTER SET utf8 COLATE utf8_general_ci NOT NULL、
'Scope'enum (''、'FOR JOIN' 、'FOR ORDER BY' 、'FOR GROUP BY') 文字セット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 'テキストCOLLATE utf8_bin NOT NULL、
主要なキー ('Id')
) /*!50100 TABLESPACE 'mysql' */ ENGINE=InnoDB
DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0 COMMENT='ステートメントアウトライン' 次の表に、ステートメントのパラメーターを示します。
パラメーター | 説明 |
Id | アウトラインテーブルのID。 |
Schema_name | アカウントに属するデータベースの名前。 |
ダイジェスト | Digest_textパラメーターの値に基づいて計算される64バイトのステートメントのダイジェストハッシュ値。 詳細については、「STATEMENT_DIGEST() 」をご参照ください。 |
ダイジェスト_テキスト | SQL文のダイジェスト。 |
データ型 |
|
スコープ | このパラメーターは、インデックスヒントにのみ必要です。 有効な値：
説明 空の文字列は、すべての型のインデックスヒントを示します。 |
状態 | ルールを有効にするかどうかを指定します。 有効な値：
|
位置 |
|
ヒント |
|
Schema_nameパラメーターが指定されている場合、SQLステートメントのSchema_nameおよびDigest値は、ステートメントアウトラインルールのSchema_nameおよびDigest値と同じである必要があります。 それ以外の場合、ステートメントアウトラインルールは有効になりません。Schema_nameパラメーターが空の場合、SQLステートメントのDigest値は、ステートメントアウトラインルールのDigest値と同じである必要があります。 それ以外の場合、ステートメントアウトラインルールは有効になりません。
ステートメントアウトラインルールの管理
ステートメントアウトラインルールは、プライマリノードでのみ管理できます。 操作が完了すると、変更は他のノードに自動的に同期されます。
PolarDBは、DBMS_OUTLNパッケージに6つのローカルルールを提供します。 ルールを使用して、ステートメントのアウトラインルールを管理できます。
add_optimizer_outline: オプティマイザーヒントを追加します。
構文
構文1:
dbms_outln.add_optimizer_outline('<Schema_name>','<Digest>','<query_block>','<hint>','<query>');構文2: この構文には、構文1に存在するDigestパラメーターとquery_blockパラメーターがありません。 Digestパラメーターは未設定のままになり、query_blockパラメーターにデフォルト値1を使用します。
dbms_outln.add_optimizer_outline('<Schema_name>','<hint>','<query>');
説明add_optimizer_outlineルールは、PolarDB for MySQL 5.6クラスターではサポートされていません。
Syntax 1を使用する場合、ステートメントでDigestまたはqueryを指定できます。 クエリパラメーターにクエリステートメントを入力すると、DBMS_OUTLNはDigestパラメーターとDigest_textパラメーターの値を計算します。
クエリ文のフィールドに引用符が必要な場合は、フィールドに単一の引用符 (") を追加し、クエリ文を二重引用符 (" ") で囲みます。
例
クエリの
Schema_nameとDigestの値は、ステートメントアウトラインルールの対応する値と同じです。 例：CALL dbms_outln.add_optimizer_outline ('out_db' 、''、1、'/* + MAX_EXECUTION_TIME(1000) */' 、 'SELECT * FROM t1 WHERE id = 1 ');上記のステートメントは、次のステートメントと同等です。
CALL dbms_outln.add_optimizer_outline ('out_db' 、'/* + MAX_EXECUTION_TIME(1000) */' 、'SELECT * FROM t1 WHERE id = 1 ');上記のステートメントは、次のステートメントとも同じです。
CALL dbms_outln.add_optimizer_outline ('out_db', '36bebc61fce7e32b93926aec3fdd790dad5d895107e2d8d3848d1c60b74bcde6', 1, '/* + MAX_EXECUTION_TIME(1000) */', '');36bebc61fce7e32b93926aec3fdd790dad5d895107e2d8d3848d1c60b74bcde 6は、SELECT * from t1 WHERE id = 1ステートメントのDigest値です。 したがって、2つのステートメントは同じ結果になります。SQLステートメントの
Digest値は、ステートメントアウトラインルールの対応する値と同じです。 例：CALL dbms_outln.add_optimizer_outline(''、'' 、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を指定できます。 queryパラメーターにクエリステートメントを入力すると、DBMS_OUTLNはDigestパラメーターとDigest_textパラメーターの値を計算します。
例
クエリの
Schema_nameとDigestの値は、ステートメントアウトラインルールの対応する値と同じです。 例：CALL dbms_outln.add_index_outline ('out_db', '', 1, 'USE INDEX', 'ind_ 1 ', '', "SELECT * FROM t1 WHERE t1.col1 =1 AND t1.col2 ='xpchild '");上記のステートメントは、次のステートメントと同等です。
CALL dbms_outln.add_index_outline ('out_db' 、'b4369611be7ab2d27c85897632576a04bc08f50b928a1d735b62d0a140628c4c' 、1、'USE INDEX' 、'ind_ 1 '、''、" ");b4369611be7ab2d27c85897632576a04bc08f50b928a1d735b62d0a140628c4cは、SELECT * from t1 WHERE t1.col1 =1 AND t1.col2 ='xpchildステートメントのDigest値です。 したがって、2つのステートメントは同じ結果になります。SQLステートメントの
Digest値は、ステートメントアウトラインルールの対応する値と同じです。 例：CALL dbms_outln.add_index_outline(''、'' 、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>');例
CALL 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();例
CALL 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は、ステートメントアウトラインルールのメモリ内ヒット数を示します。[オーバーフロー]は、クエリブロックまたはテーブルが見つからなかった回数を示します。del_outline: メモリまたはテーブルからステートメントアウトラインルールを削除します。
構文
dbms_outln.del_outline(<Id>);例
CALL dbms_outln.del_outline(32);説明削除するステートメントアウトラインルールが存在しない場合は、エラーが返されます。
SHOW WARNINGS;ステートメントを実行して、エラーメッセージを表示できます。CALL dbms_outln.del_outline(1000); クエリOK、影響を受ける0行、2警告 (0.00秒) 警告を表示します。+ -------- + ------- + ---------------------------------------------- + | レベル | コード | メッセージ | + -------- + ------- + ---------------------------------------------- + | 警告 | 7521 | ステートメントの概要1000がテーブルに見つかりません | | 警告 | 7521 | 文のアウトライン1000がキャッシュに見つかりません | + -------- + ------- + ---------------------------------------------- + セットの2列 (0.00秒)flush_outline:
outlineテーブルのステートメントアウトラインルールを更新した場合、ステートメントアウトラインルールを再検証します。構文
dbms_outln.flush_outline();例
UPDATE mysql. アウトラインSET Position = 1 WHERE Id = 18; クエリOK、影響を受ける1行 (0.00秒) 一致した行: 1変更: 1警告: 0 CALL dbms_outln.flush_outline(); クエリOK、影響を受ける0行 (0.01秒)
機能のテスト
次のいずれかの方法を使用して、ステートメントアウトラインルールが有効になるかどうかを確認できます。
preview_outlineステートメントを実行します。
CALL 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ステートメントを実行します。
EXPLAIN SELECT * FROM t1 WHERE 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秒) 警告を表示します。+ ------ ------ + --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- + | レベル | コード | メッセージ | + ------ ------ + --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- + | 注: 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秒)