Go to Qiita Advent Calendar 2024 Top
LoginSignup
7
2

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

@hmatsu47

MySQL 8.0（8.0.12以降）で更新処理に対応したQuery Rewrite Pluginを使ってみる

Posted at

Query Rewrite Plugin は、アプリケーション側の変更なしに SQL を RDBMS（MySQL）側で書き換えて実行するものです。
MySQL 5.7 から利用できるようになっていましたが、これまで（MySQL 8.0.11 まで）は**SELECTの書き換えのみ**に対応していました。

先日リリースされた MySQL 8.0.12 で、INSERT/UPDATE/DELETEにも対応したので、実際に試してみます。

※MySQL 5.7 の Query Rewrite Plugin についてはこちらで紹介されています（MySQL 5.7.5-labs 時点のもの）。

プラグインの有効化

こちらを参考に…というかそのまま実行します。

※MySQL Community Server 8.0.12 は事前にインストールしています。なお、以下は CentOS 7 で確認しました。

プラグイン有効化
$ cd /usr/share/mysql-8.0/
$ mysql -u root -p < install_rewriter.sql
Enter password:【パスワード入力】
$ mysql -u root -p
Enter password:【パスワード入力】
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.12 MySQL Community Server - GPL

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SHOW GLOBAL VARIABLES LIKE 'rewriter_enabled';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| rewriter_enabled | ON    |
+------------------+-------+
1 row in set (0.01 sec)

先ほどのinstall_rewriter.sqlを実行すると、デフォルトでプラグインが有効になるようです。
起動時に無効にするには、my.cnfの[mysqld]セクションに記述するか、以下の SQL を発行します。

プラグイン無効化
mysql> SET PERSIST rewriter_enabled = OFF;
Query OK, 0 rows affected (0.00 sec)

mysql> quit
Bye
$ sudo systemctl stop mysqld.service
$ sudo systemctl start mysqld.service
$ mysql -u root -p
Enter password:【パスワード入力】
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.12 MySQL Community Server - GPL

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SHOW GLOBAL VARIABLES LIKE 'rewriter_enabled';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| rewriter_enabled | OFF   |
+------------------+-------+
1 row in set (0.00 sec)

戻しておきます。

プラグイン有効化
mysql> SET PERSIST rewriter_enabled = ON;
Query OK, 0 rows affected (0.00 sec)

使ってみる（SELECT）

こちらも途中までリファレンスマニュアルに沿って進めます。

まずは単純なSELECTの書き換えを行ってみます。

SELECT書き換え
mysql> INSERT INTO query_rewrite.rewrite_rules (pattern, replacement)
    -> VALUES('SELECT ?', 'SELECT ? + 1');
Query OK, 1 row affected (0.06 sec)

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: NULL
normalized_pattern: NULL
1 row in set (0.00 sec)

mysql> SELECT 10;
+----+
| 10 |
+----+
| 10 |
+----+
1 row in set (0.00 sec)

この段階ではまだ書き換えは行われません。リライトルールをフラッシュすると書き換えが行われるようになります。

リライトルールフラッシュ
mysql> CALL query_rewrite.flush_rewrite_rules();
Query OK, 1 row affected (0.10 sec)

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 ?
1 row in set (0.00 sec)

mysql> SELECT 10;
+--------+
| 10 + 1 |
+--------+
|     11 |
+--------+
1 row in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
  Level: Note
   Code: 1105
Message: Query 'SELECT 10' rewritten to 'SELECT 10 + 1' by a query rewrite plugin
1 row in set (0.00 sec)

書き換えが行われました。書き換えが行われると、WARNINGが出力されます。
なお、ルールに合わない SQL は当然ですが書き換えが行われません。

ルールにマッチしない場合
mysql> SELECT PI();
+----------+
| PI()     |
+----------+
| 3.141593 |
+----------+
1 row in set (0.00 sec)

こちらは（MySQL ですので当然？）マッチします。

文字列を強引に…
mysql> SELECT '10';
+----------+
| '10' + 1 |
+----------+
|       11 |
+----------+
1 row in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
  Level: Note
   Code: 1105
Message: Query 'SELECT '10'' rewritten to 'SELECT '10' + 1' by a query rewrite plugin
1 row in set (0.00 sec)

更新処理（INSERT/UPDATE/DELETE）で使ってみる

いよいよ今回の主役の登場です。

リファレンスマニュアルにはありませんが、以下のテーブルを作成しておきます（データ件数が少ないのでセカンダリ INDEX はなしです）。

テスト用テーブル作成
mysql> CREATE DATABASE db1;
Query OK, 1 row affected (0.03 sec)

mysql> CREATE TABLE db1.t1 (id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT, data VARCHAR(100), col INT UNSIGNED);
Query OK, 0 rows affected (0.11 sec)

mysql> INSERT INTO db1.t1 SET data = 'abc', col = 100;
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO db1.t1 SET data = 'def', col = 105;
Query OK, 1 row affected (0.09 sec)

mysql> INSERT INTO db1.t1 SET data = 'ghi', col = 102;
Query OK, 1 row affected (0.10 sec)

mysql> INSERT INTO db1.t1 SET data = 'jkl', col = 100;
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO db1.t1 SET data = 'mno', col = 108;
Query OK, 1 row affected (0.03 sec)

mysql> SELECT * FROM db1.t1;
+----+------+------+
| id | data | col  |
+----+------+------+
|  1 | abc  |  100 |
|  2 | def  |  105 |
|  3 | ghi  |  102 |
|  4 | jkl  |  100 |
|  5 | mno  |  108 |
+----+------+------+
5 rows in set (0.00 sec)

リファレンスマニュアルの通り、DELETEをUPDATEで書き換えてみます。

DELETE→UPDATE(NULL)
mysql> INSERT INTO query_rewrite.rewrite_rules (pattern, replacement)
    -> VALUES('DELETE FROM db1.t1 WHERE col = ?',
    ->        'UPDATE db1.t1 SET col = NULL WHERE col = ?');
Query OK, 1 row affected (0.08 sec)

mysql> CALL query_rewrite.flush_rewrite_rules();
Query OK, 1 row affected (0.04 sec)

mysql> DELETE FROM db1.t1 WHERE col = 100;
Query OK, 2 rows affected, 1 warning (0.06 sec)
Rows matched: 2  Changed: 2  Warnings: 1

mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
  Level: Note
   Code: 1105
Message: Query 'DELETE FROM db1.t1 WHERE col = 100' rewritten to 'UPDATE db1.t1 SET col = NULL WHERE col = 100' by a query rewrite plugin
1 row in set (0.00 sec)

mysql> SELECT * FROM db1.t1;
+----+------+------+
| id | data | col  |
+----+------+------+
|  1 | abc  | NULL |
|  2 | def  |  105 |
|  3 | ghi  |  102 |
|  4 | jkl  | NULL |
|  5 | mno  |  108 |
+----+------+------+
5 rows in set (0.00 sec)

id = 1, 4の行が削除される代わりに、col列がNULLでUPDATEされました。

その他もろもろ

特定のリライトルールを無効にすることもできます。

リライトルール無効化
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 ?
*************************** 2. row ***************************
                id: 2
           pattern: DELETE FROM db1.t1 WHERE col = ?
  pattern_database: NULL
       replacement: UPDATE db1.t1 SET col = NULL WHERE col = ?
           enabled: YES
           message: NULL
    pattern_digest: e246a61c325ea344469f22417cd746bf14e816dd61c004b9054cd989a7d60bd6
normalized_pattern: delete from `db1`.`t1` where (`col` = ?)
2 rows in set (0.00 sec)

mysql> UPDATE query_rewrite.rewrite_rules SET enabled = 'NO' WHERE id = 1;
Query OK, 1 row affected (0.07 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> CALL query_rewrite.flush_rewrite_rules();
Query OK, 1 row affected (0.06 sec)

mysql> SELECT 10;
+----+
| 10 |
+----+
| 10 |
+----+
1 row in set (0.00 sec)

有効に戻します。

リライトルール有効化
mysql> UPDATE query_rewrite.rewrite_rules SET enabled = 'YES' WHERE id = 1;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> CALL query_rewrite.flush_rewrite_rules();
Query OK, 1 row affected (0.05 sec)

mysql> SELECT 10;
+--------+
| 10 + 1 |
+--------+
|     11 |
+--------+
1 row in set, 1 warning (0.00 sec)

その他、SQLのマッチングの仕組みなど、詳しいことは前掲のリファレンスマニュアルに書かれていますので確認してみてください。

おまけ：悪い DBA

ムシャクシャしたからといって、こういう罠を仕掛けるのに使うのはやめましょう。

悪いDBA
mysql> INSERT INTO query_rewrite.rewrite_rules (pattern, replacement)
    -> VALUES('DELETE FROM db1.t1 WHERE id = ?',
    ->        'DELETE FROM db1.t1');
Query OK, 1 row affected (0.07 sec)

mysql> CALL query_rewrite.flush_rewrite_rules();
Query OK, 1 row affected (0.09 sec)

mysql> DELETE FROM db1.t1 WHERE id = 1;
Query OK, 5 rows affected, 1 warning (0.09 sec)

mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
  Level: Note
   Code: 1105
Message: Query 'DELETE FROM db1.t1 WHERE id = 1' rewritten to 'DELETE FROM db1.t1' by a query rewrite plugin
1 row in set (0.00 sec)

mysql> SELECT * FROM db1.t1;
Empty set (0.00 sec)
7
2
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
7
2

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?