はじめに
MySQL では 5.7 から、Query Rewrite プラグイン を使用することができます。
このプラグインが有効になっている場合、サーバーでクエリが実行される前にチェックされて、指定した条件に合致していた場合は書き換えがおこなわれてから実行されるようになります。
このとき、書き換えの対象となるのは MySQL 8.0.11 以前は SELECT のみでしたが、MySQL 8.0.12 以降は SELECT、INSERT、REPLACE、UPDATE、DELETE を対象とすることができます。
なお、ビューやストアドプロシージャ内で実行されるクエリについては書き換えの対象とならないので注意してください。
また、このプラグインをインストールしていると、クエリの実行にルールのチェックをおこなう必要があるため、多少のオーバーヘッドが発生してしまいます。
このオーバーヘッドはプラグインを無効にしていても発生してしまうため、ルールを適用して使用する予定がない限り、このプラグインをインストールすることは推奨されていません。
今回は、この Query Rewrite プラグインについて、具体的な使用方法やルールの書き方、またどの程度のオーバーヘッドが発生するのかについて、簡単に調査をおこなってみます。
利用方法
インストール
Query Rewrite プラグインを利用する場合は、プラグインのインストールに加えて、テーブルやストアドプロシージャを追加しておく必要があります。
ここで、MySQL をインストールした際の share ディレクトリには、以下のようなファイルがそれぞれ用意されています。
|
1 2 3 |
# ls -l /usr/share/mysql-8.0/*_rewriter.sql -rw-r--r-- 1 root root 2216 9月 14 03:45 /usr/share/mysql-8.0/install_rewriter.sql -rw-r--r-- 1 root root 1248 9月 14 03:45 /usr/share/mysql-8.0/uninstall_rewriter.sql |
実施される内容はそれぞれ以下の通りです。
[install_rewriter.sql]
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
-- データベースおよびテーブルの作成 CREATE DATABASE IF NOT EXISTS query_rewrite; CREATE TABLE IF NOT EXISTS query_rewrite.rewrite_rules ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, pattern VARCHAR(5000) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL, pattern_database VARCHAR(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin, replacement VARCHAR(5000) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL, enabled ENUM('YES', 'NO') CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT 'YES', message VARCHAR(1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin, pattern_digest VARCHAR(64), normalized_pattern VARCHAR(100) ) DEFAULT CHARSET = utf8mb4 ENGINE = INNODB; -- プラグインのインストール INSTALL PLUGIN rewriter SONAME 'rewriter.so'; -- ストアドプロシージャの作成 CREATE FUNCTION load_rewrite_rules RETURNS STRING SONAME 'rewriter.so'; DELIMITER // CREATE PROCEDURE query_rewrite.flush_rewrite_rules() BEGIN DECLARE message_text VARCHAR(100); COMMIT; SELECT load_rewrite_rules() INTO message_text; IF NOT message_text IS NULL THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = message_text; END IF; END // DELIMITER ; |
[uninstall_rewriter.sql]
|
1 2 3 4 |
-- データベースやストアドプロシージャの削除 DROP DATABASE IF EXISTS query_rewrite; DROP FUNCTION load_rewrite_rules; UNINSTALL PLUGIN rewriter; |
そのため、プラグインをインストールする場合、以下のように実施することが可能です。
|
1 2 3 4 5 6 7 8 |
# mysql < /usr/share/mysql-8.0/install_rewriter.sql # mysql mysql> SHOW GLOBAL VARIABLES LIKE 'rewriter_enabled'; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | rewriter_enabled | ON | +------------------+-------+ |
なお、Query Rewrite プラグインはインストールしているだけでクエリの実行時にオーバーヘッドがかかるようになってしまうため、使用する予定がない場合はインストールをしないように注意してください。
ルールの適用
Query Rewrite プラグインにルールを追加する場合は、まず query_rewrite.rewrite_rules テーブルに行を追加します。
rewrite_rules テーブルのうち、作成時に指定することができるのは以下の 3 つです。
-
pattern :
書き換え対象となるクエリのステートメントのパターンを示すテンプレートを指定します。
パターンはプリペアドステートメントと同様の構文が使用可能であり、データ値を指定する際に ? 文字をパラメータマーカーとして使用できます。 -
pattern_database :
ステートメント内に未修飾のテーブル名があった場合に、デフォルトのデータベースが一致しているかで書き換えを判断するために指定します。 -
replacement :
書き換え後のステートメントを指定します。その際に ? 文字をパラメータマーカーとして使用できます。
ステートメントを実行する際は、pattern カラムの対応するマーカーに一致するデータ値を、replacement の ? 文字と置換した上で実行されます。
たとえば、以下のように記述することが可能です。
このとき、;(セミコロン) を入れてしまうと正常に読み取られなくなってしまうので注意してください。
|
1 |
mysql> INSERT INTO query_rewrite.rewrite_rules (pattern, replacement) VALUES('SELECT ?', 'SELECT ? + 1'); |
その後、flush_rewrite_rules() を実行します。これによって、内容のチェックおよびテーブルの内容がプラグインのメモリーキャッシュにロードされて、ルールが適用されます。
|
1 |
mysql> CALL query_rewrite.flush_rewrite_rules(); |
ロードが成功すると、パターンおよびダイジェストハッシュ値から正規化されたステートメントダイジェスト情報が normalized_pattern および pattern_digest カラムに反映されます。
|
1 2 3 4 5 6 7 8 9 10 |
mysql> SELECT * FROM query_rewrite.rewrite_rules\G *************************** 1. row *************************** id: 1 pattern: SELECT ? pattern_database: NULL replacement: SELECT ? + 1 enabled: YES message: NULL pattern_digest: d1b44b0c19af710b5a679907e284acd2ddc285201794bc69a2389d77baedddae normalized_pattern: select ? |
なお、ロードに失敗した場合は flush_rewrite_rules() の実行時にエラーが発生して、message カラムにその内容が書き込まれます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
mysql> INSERT INTO query_rewrite.rewrite_rules (pattern, replacement) VALUES('SELECT * FROM t1 WHERE id = ?', 'SELECT * FROM t1 WHERE id = ?+1'); mysql> CALL query_rewrite.flush_rewrite_rules(); ERROR 1644 (45000): Loading of some rule(s) failed. mysql> SELECT * FROM query_rewrite.rewrite_rules\G *************************** 1. row *************************** id: 1 pattern: SELECT * FROM t1 WHERE id = ? pattern_database: NULL replacement: SELECT * FROM t1 WHERE id = ?+1 enabled: YES message: Parse error in pattern: >>No database selected<< pattern_digest: NULL normalized_pattern: NULL |
上記のようなケースでは、pattern_database にデフォルトデータベースを指定するか、テーブル名を [db_name].t1 のように修飾して記載し直す必要があります。
その他の注意事項
クエリを書き換える際は pattern カラムのパラメータマーカーの数が replacement カラムのマーカーと一致するか多ければ問題ありません。もし多い場合、不要なデータ値は破棄されます。
そのため、以下のように別の種類のクエリに書き換えをおこなうことも可能です。
|
1 |
mysql> INSERT INTO query_rewrite.rewrite_rules (pattern, replacement) VALUES('DELETE FROM test.t1 WHERE id = ?','UPDATE test.t1 SET id = NULL WHERE id = ?'); |
また、ルールを一時的に無効化したい場合は、無効化したいルールの enabled カラムを更新してから、flush_rewrite_rules() で反映することで実現できます。
|
1 2 |
mysql> UPDATE query_rewrite.rewrite_rules SET enabled = 'NO' WHERE id = 1; mysql> CALL query_rewrite.flush_rewrite_rules(); |
なお、MySQL 8.0.31 から、新しく SKIP_QUERY_REWRITE 権限が追加されています。
この権限を追加したユーザーによって発行されたクエリは、Query Rewrite プラグインによってクエリが書き換えられることはなくなります。
そのため、管理者(root)ユーザーや mysqladmin、mysqldump などの書き換えを発生させたくない処理を実行するユーザーにおいては、SKIP_QUERY_REWRITE 権限を付与しておくことが推奨されています。
このとき、レプリケーションに PRIVILEGE_CHECKS_USER アカウントを設定してる場合は該当のアカウントに権限を付与すれば良いですが、もし設定してない場合、rewriter_enabled_for_threads_without_privilege_checks を無効にしなければクエリの書き換えが発生してまう点にご注意ください。
運用方法
Query Rewrite プラグインを利用することで柔軟にクエリを書き換えることが可能ですが、条件によってはデータの整合性などが崩れてしまうため、取り扱いには十分注意する必要があります。
実際の運用において本プラグインを使用することが有効なのは、たとえばクエリのチューニングをおこなったもののアプリケーション側の改修が難しい場合に、サーバー側で書き換えをおこなうようなケースが考えられます。
その場合、実行されるクエリがある程度パターン化されていれば、以下のようにインデックスヒントやオプティマイザヒントの追加、あるいは JOIN 句の書き換えなども可能です。
|
1 |
mysql> INSERT INTO query_rewrite.rewrite_rules (pattern, replacement) VALUES('SELECT * FROM test.t1 WHERE id = ?', 'SELECT * FROM test.t1 USE INDEX(idx_1) WHERE id = ?'); |
また、プラグインを有効にしている場合はいくつかのステータス変数が利用できるため、実際に有効利用されているかの確認などが可能です。
- Rewriter_number_loaded_rules : 現在ロードされているルールの数
- Rewriter_number_rewritten_queries : プラグインが有効になってからクエリの書き換えが実行された回数
- Rewriter_reload_error : 最後のルールのロード時にエラーが発生したか否か
性能への影響
公式のリファレンスには、Query Rewrite プラグインをインストールしているだけでクエリの実行時にオーバーヘッドがかかるという記載があります。
そのため、実際にどの程度の性能影響があるのかを、ベンチマークを取って比較してみたいと思います。
実行環境
検証をおこなった環境は以下の通りです。
- CentOS 7.7
- vCPU : 4
- メモリー : 8GB
- MySQL 8.0.31
- 以下のシステム変数を設定(それ以外はデフォルト)
- skip_bin_log
- innodb_buffer_pool_size = 5GB
- innodb_redo_log_capacity = 5GB
上記の環境において、sysbench で 5GB 程度のデータを作成して、 oltp_read_write をそれぞれ 10 分間以下のようなコマンドで実行しました。
なお、sysbench については、過去に弊社ブログでも取り上げた「MySQLのベンチマークツール: sysbench の紹介」をご参照ください。
|
1 2 3 4 5 6 7 8 9 10 11 |
sysbench --db-driver=mysql \ --mysql-host=127.0.0.1 \ --mysql-user=sbtest \ --mysql-password=MySQL8.0 \ --mysql-db=sbtest \ --tables=20 \ --table_size=1000000 \ --threads=4 \ --time=600 \ --report-interval=60 \ oltp_read_write run |
検証結果
プラグインをインストールした場合としなかった場合で、それぞれ 5 回ずつテストを実行して平均した結果は以下の通りです。
| プラグインの状態 | read | write | other | total |
|---|---|---|---|---|
| 無効(インストールなし) | 7,136,127 | 2,038,893 | 1,019,446 | 10,194,468 |
| 有効(インストール済み) | 7,020,020 | 2,005,720 | 1,002,860 | 10,028,600 |
有効にした場合、約 2% 程のオーバーヘッドが発生することがわかりました。
まとめ
ここまで、Query Rewrite プラグインの機能および性能影響について確認をおこないました。
プラグインを有効にすることで一定の性能影響は出てしまいますが、MySQL 8.0.31 の機能追加でプラグインの影響を受けないようにする権限も追加されたため、特定のユーザーのみルールを適用するといった運用も可能になりました。
従来ならばDBA だけでは実施できないクエリを書き換えるチューニングなども対応できるようになるため、一部のシステムにおいては魅力的な機能なのではないでしょうか。
