本番環境では、SQL ステートメントの実行計画が頻繁に変化し、データベースの安定性が損なわれる場合があります。Alibaba Cloud は、MySQL の実行計画を安定させるために、オプティマイザヒントとインデックスヒント (ステートメントアウトラインと呼ばれる方法) を使用します。SQL コマンドと DBMS_OUTLN ツールキットを使用して、ステートメントアウトラインを迅速に適用できます。
前提条件
RDS インスタンスが次のいずれかの MySQL バージョンで実行されていることを確認します。
MySQL 8.4
MySQL 8.0
MySQL 5.7
仕組み
ステートメントアウトラインは、MySQL 8.4、8.0、5.7 の公式バージョンで利用可能なすべてのヒントタイプをサポートします。これらのヒントは、次の 2 つのカテゴリに分類されます。
オプティマイザヒント
オプティマイザヒントは、適用範囲と対象オブジェクトに基づいて、グローバルレベルのヒント、テーブルレベルまたはインデックスレベルのヒント、結合順序ヒントに分類されます。詳細については、「MySQL ドキュメント」をご参照ください。
インデックスヒント
インデックスヒントは、タイプと適用範囲によって分類されます。詳細については、「MySQL ドキュメント」をご参照ください。
ステートメントアウトラインテーブル
AliSQL には、ヒントを格納するための組み込みシステムテーブル outline が含まれています。このテーブルは起動時に自動的に作成されます。次の CREATE TABLE ステートメントを参考として示します。
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 バイトのハッシュ文字列。 |
Digest_text | SQL ステートメントのダイジェスト。 |
Type |
|
Scope | インデックスヒントの場合にのみ必要です。有効な値：
空文字列は、インデックスヒントがすべてのタイプに適用されることを示します。 |
State | ステートメントアウトラインが有効かどうかを示します。 |
Position |
|
Hint |
|
ステートメントアウトラインの管理
AliSQL は、ステートメントアウトラインを管理するために、DBMS_OUTLN に次の 6 つのストアドプロシージャを提供します。
add_optimizer_outline
オプティマイザヒントを追加します。次のコマンドを使用します。
dbms_outln.add_optimizer_outline('<Schema_name>','<Digest>',<query_block>,'<hint>','<query>');説明Digest または Query (元の SQL ステートメント) のいずれかを指定できます。Query を指定すると、DBMS_OUTLN は Digest と Digest_text を計算します。
例：
mysql> call 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 (元の SQL ステートメント) のどちらかを指定できます。Query を指定した場合、DBMS_OUTLN は Digest と Digest_text を計算します。
例：
mysql> 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>');説明max_digest_lengthパラメーターの値は、ステートメントアウトラインの照合精度に影響する場合があります。ステートメントアウトラインが正しく一致しない場合は、このパラメーターの値を増やしてください。例：
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.00 sec)show_outline
現在メモリ内に読み込まれているステートメントアウトラインを、ヒット統計とともに表示します。次のコマンドを使用します。
dbms_outln.show_outline();例：
mysql> 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 列について説明します。
パラメーター
説明
HIT
このステートメントアウトラインが一致した回数。
OVERFLOW
このステートメントアウトラインが、対応するクエリブロックまたはテーブルを見つけられなかった回数。
del_outline
メモリおよび
outlineテーブルからステートメントアウトラインを削除します。次のコマンドを使用します。dbms_outln.del_outline(<Id>);例：
mysql> call dbms_outln.del_outline(32);説明削除しようとしているアウトラインが存在しない場合、警告が返されます。
show warnings;を実行して詳細を確認できます。mysql> call dbms_outln.del_outline(1000); Query OK, 0 rows affected, 2 warnings (0.00 sec) mysql> 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)flush_outline
outlineテーブルを直接変更した場合は、このコマンドを実行して変更を適用する必要があります。次のコマンドを使用します。dbms_outln.flush_outline();例：
mysql> update mysql.outline set Position = 1 where Id = 18; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> call dbms_outln.flush_outline(); Query OK, 0 rows affected (0.01 sec)
ステートメントアウトラインの検証
ステートメントアウトラインが有効かどうかは、次のいずれかの方法で確認できます。
preview_outlineを使用して一致を確認します。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)EXPLAINを使用して実行計画を確認します。mysql> 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) mysql> 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)