SQLのパフォーマンスチューニングをしている際などに、ときどきアプリケーションを変更せずにクエリだけ書き換えたくなることがあります。本来はアプリケーションを改変して直したいところではあるのですが、たとえば第117回 MySQL 8.0のオプティマイザーヒント で紹介しているヒント句等を埋め込みたいこともあると思います。ただ、ORマッパーを使用していてSQLコメントを埋め込む機能がない場合には、困ってしまうこともあると思います。
今回はそういった場合に便利な、クエリを実行時に書き換えるQuery Rewrite Pluginsを紹介していきます。
Query Rewrite Pluginsを使ってみる
インストールして確認する
Query Rewrite Pluginsを有効にするためには、pluginのインストールが必要です。pluginのインストールは次のコマンドを実行します。今回はHomebrewでインストールしたMySQLを使用して検証を行います。実行すると自動的に有効になります。
まずは、インストールしたmysqlのバージョンのshareディレクトリにあるinstall_rewriter.sqlを探しましょう。今回使用したバージョンは8.0.27ですが、以下のディレクトリ存在します。インストールしたバージョンに合わせて移動をしてみてください。
$ cd /usr/local/Cellar/mysql/8.0.27/share/mysql
$ ls | grep rewriter
install_rewriter.sql
uninstall_rewriter.sql
install用のスクリプトとuninstall用のスクリプトが存在していることがわかります。
ではinstallをしてみましょう。
$ mysql -uroot < install_rewriter.sql
以上のコマンドを実行します。終わったらMySQLにログインして確認してみましょう。
mysql> SHOW GLOBAL VARIABLES LIKE 'rewriter_enabled';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| rewriter_enabled | ON |
+------------------+-------+
1 row in set (0.00 sec)
以上の形になっていたら、完了です。
もし、ERROR 1123 (HY000): Can't initialize function 'load_rewrite_rules'; UDFs are unavailable with the --skip-grant-tables option
というエラーが発生して先に進めなかった場合は、my.cnfにskip-grant-tables
が指定されていないか確認して、設定されたいた場合は外しましょう。
Dockerで実行したい場合
Dockerで実行したい場合ですが、Dockerイメージ内には残念ながら、install_rewriter.sql
が含まれていないため、どこからか持ってくる必要があります。ただし、install_rewriter.sql
は名前の通りSQLなのと、中を見てみるとわかるのですが、専用のDatabaseとテーブルを作成して、pluginをロードするという内容のSQLです。ロードされているrewriter.soはDockerのMySQLイメージにも含まれているため、移し替えて実行することで確認することができます。
Query Rewrite Pluginsでクエリを書き換えてみる
それでは、まずは簡単なパターンを登録してみましょう。最低限の登録は簡単で、install_rewriter.sqlで作成されたテーブルのカラムであるpatternとreplacementにそれぞれ、置き換え前と置き換え後のパターンで設定を行います。今回はSELECT ?
で合致するクエリをSELECT ? + 1
に書き換えるという処理を行うパターンです。
mysql> INSERT INTO query_rewrite.rewrite_rules (pattern, replacement) VALUES('SELECT ?', 'SELECT ? + 1');
Query OK, 1 row affected (0.00 sec)
さて、これで置き換わるかと思いきや、以下のように変更されません。
mysql> SELECT 1;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)
実は、これだけでは駄目で、CALL query_rewrite.flush_rewrite_rules();
を呼んであげる必要があります。実行後は以下のように変更されていることがわかります。
mysql> CALL query_rewrite.flush_rewrite_rules();
Query OK, 1 row affected (0.03 sec)
mysql> SELECT 1;
+-------+
| 1 + 1 |
+-------+
| 2 |
+-------+
1 row in set, 1 warning (0.00 sec)
さて、今回はうまく行った場合をためしてみましたが、失敗した場合どうなるでしょうか? 文法的におかしなSQLを登録してみましょう。
mysql> INSERT INTO query_rewrite.rewrite_rules (pattern, replacement) VALUES('SELECT ?', 'ELECT ? + 1');
Query OK, 1 row affected (0.00 sec)
登録自体は文字列の登録なので、できてしまいました。続いてプロシージャを実行してみます。
mysql> CALL query_rewrite.flush_rewrite_rules();
ERROR 1644 (45000): Loading of some rule(s) failed.
このように正しくないSQLは、登録ができないことがわかります。またこんな場合、エラーが起こったことはわかるのですが、どんなエラーが起こったのか気になると思います。こちらは、query_rewrite.rewrite_rulesテーブル内のmessageカラムに出力されます。
mysql> select * from query_rewrite.rewrite_rules\G
*************************** 1. row ***************************
id: 6
pattern: SELECT ?
pattern_database: NULL
replacement: ELECT ? + 1
enabled: YES
message: Parse error in replacement: >>You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ELECT ? + 1' at line 1<<
pattern_digest: NULL
normalized_pattern: NULL
1 row in set (0.00 sec)
ということで、構文解析に失敗したということがわかります。Query Rewrite Pluginsを使っていてエラーが発生した場合には、messageを覗いて何が起こっているのか確認してみましょう。
それ以外のカラムに関しても簡単に説明をしていきます。pattern_databaseは、特定のデータベースでのみパターンを有効したい場合に使います。enabledはYESの場合は有効で、NOの場合は無効になります。pattern_digestとnormalized_patternは、プロシージャが成功するとそれぞれ入力されるカラムで、デバッグ用途で用いられることが想定されているそうです。
これを上手く使う方法として、たとえばUSE INDEXを使っているINDEXをドロップしてしまった場合に、USE INDEXで指定したINDEXが存在しないとエラーになってしまいます。
mysql> select * from zipcode USE INDEX(test01) limit 1;
ERROR 1176 (42000): Key 'test01' doesn't exist in table 'zipcode'
そこで以下のように、エラーになったINDEXを呼び出さないように変更するクエリを登録してプロシージャを呼び出します。
mysql> INSERT INTO query_rewrite.rewrite_rules (pattern_database, pattern, replacement) VALUES('zipcode','SELECT * FROM zipcode USE INDEX(test01) limit ?', 'SELECT * FROM zipcode limit ?');
Query OK, 1 row affected (0.02 sec)
mysql> CALL query_rewrite.flush_rewrite_rules();
Query OK, 1 row affected (0.02 sec)
すると当然ですが、エラーが発生しなくなります。
mysql> select * from zipcode USE INDEX(test01) limit 1;
+-------+-------------+----------+-----------------------+--------------------------------------+-----------------------------------------------+------------+--------------------+--------------------------------+
| code | old_zipcode | zip_code | prefecture_kana | city_kana | town_kana | prefecture | city | town |
+-------+-------------+----------+-----------------------+--------------------------------------+-----------------------------------------------+------------+--------------------+--------------------------------+
| 01101 | 060 | 0600000 | ホッカイドウ | サッポロシチュウオウク | イカニケイサイガナイバアイ | 北海道 | 札幌市中央区 | 以下に掲載がない場合 |
+-------+-------------+----------+-----------------------+--------------------------------------+-----------------------------------------------+------------+--------------------+--------------------------------+
あまりないとは思いますが、この機能が有効になっているサーバでうっかりindexをdropしてしまった場合に、いったんサービスを戻すといったことも可能になります。
デメリット
Query Rewrite Pluginsは動的にMySQLでクエリを書き換えることができるので、非常におもしろく、どんどん使ってみたくなると思いますが、デメリットも存在します。
installしてプラグインを有効にした場合、クエリが実行された際に該当するクエリであるかないか判断を行うことになるので、必然的に性能が悪くなります。そのため公式のドキュメント の最初に注記として書かれていますが「インストールされている場合、Rewriter プラグインは無効になっていても多少のオーバーヘッドを伴います。 このオーバーヘッドを回避するには、使用する予定がないかぎり、プラグインをインストールしないでください。」とあります。
まとめ
今回紹介したQuery Rewrite Pluginsは、SQLをMySQL側で実行前に置き換えることができる仕組みになります。アプリケーション側を弄れないが書き換えたい場合に、適切なindexを使わせたいときやカラムの変更に際して問題が起こらないように書き換えたり、8.0.12以降ではSELECT文以外のDMLでも使用することができるようになったので、DELETE文をUPDATE文に置き換えて論理削除にするというアグレッシブな対応も取れるようになりました。
しかし、仕組み上どうしても、実行するSQLを読み取って対象となるクエリかどうか確認するという作業が入るため、パフォーマンスに影響が出てしまう可能性があります。メリット・デメリットをよく理解して便利に活用しましょう。