Rewriting a MySQL query for performance is an important process that every DBA should be aware of so they can fix the wrong queries on runtime without code changes on the application end. ProxySQL has great support for rewriting the queries, which Alkin Tezuysal already explored in his excellent blog ProxySQL Query Rewrite Use Case.
So far, MySQL community provides two built-in query rewrite plugins to perform this task. Recently they introduced the plugin “ddl_rewriter”.
- query rewriter plugin : It has support for INSERT / UPDATE / DELETE / REPLACE statements from MySQL 8.0.12.
- ddl_rewritter plugin: It supports the CREATE TABLE statement. Introduced at MySQL 8.0.16.
In this blog, I am going to explain the complete process of the implementation and the testing of those plugins. The testing is based on MySQL 8.x features.
Query Rewriter Plugin
The plugin will help to modify the SQL statements which are received by the server before execution. Before MySQL 8.0.12, the plugin was only supported for SELECT. From MySQL 8.0.12 the plugin supports INSERT, UPDATE, DELETE, REPLACE as well.
Implementation
There are two SQL files to perform the install and uninstall operations. The files are located under the shared folder.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
mysql> show global variables like 'lc_messages_dir'; +-----------------+----------------------------+ | Variable_name | Value | +-----------------+----------------------------+ | lc_messages_dir | /usr/share/percona-server/ | +-----------------+----------------------------+ 1 row in set (0.01 sec) [root@hercules7sakthi3 ~]# cd /usr/share/mysql-8.0/ [root@hercules7sakthi3 mysql-8.0]# ls -lrth | grep -i rewriter -rw-r--r--. 1 root root 1.3K Mar 26 14:16 uninstall_rewriter.sql -rw-r--r--. 1 root root 2.2K Mar 26 14:16 install_rewriter.sql |
- We can implement the rewriter plugin on runtime.
- When loading the SQL file “install_rewritter.sql” it will install the plugin “rewriter.so” and creates its own database, table, and function for the operations.
Install the plugin by loading the installer SQL file:
|
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 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 |
[root@hercules7sakthi3 mysql-8.0]# mysql -vv < install_rewriter.sql | grep -i 'create\|install\|drop' CREATE DATABASE IF NOT EXISTS query_rewrite CREATE TABLE IF NOT EXISTS query_rewrite.rewrite_rules ( INSTALL PLUGIN rewriter SONAME 'rewriter.so' CREATE FUNCTION load_rewrite_rules RETURNS STRING CREATE PROCEDURE query_rewrite.flush_rewrite_rules() mysql> show schemas like 'query_rewrite'; +--------------------------+ | Database (query_rewrite) | +--------------------------+ | query_rewrite | +--------------------------+ 1 row in set (0.00 sec) mysql> show tables from query_rewrite; +-------------------------+ | Tables_in_query_rewrite | +-------------------------+ | rewrite_rules | +-------------------------+ 1 row in set (0.05 sec) mysql> show create table query_rewrite.rewrite_rules\G *************************** 1. row *************************** Table: rewrite_rules Create Table: CREATE TABLE `rewrite_rules` ( `id` int NOT NULL AUTO_INCREMENT, `pattern` varchar(5000) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL, `pattern_database` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL, `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 DEFAULT NULL, `pattern_digest` varchar(64) DEFAULT NULL, `normalized_pattern` varchar(100) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.00 sec) mysql> select plugin_name,plugin_status, plugin_version from information_schema.plugins where plugin_name='Rewriter'; +-------------+---------------+----------------+ | plugin_name | plugin_status | plugin_version | +-------------+---------------+----------------+ | Rewriter | ACTIVE | 0.2 | +-------------+---------------+----------------+ 1 row in set (0.00 sec) |
Plugin installation is completed, and you can verify that from the above logs.
Test Case
(Remove the LOWER function from UPDATE to avoid the FTS)
I have created a table “qrw8012” and made some records for testing purposes.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
mysql> show create table qrw8012\G *************************** 1. row *************************** Table: qrw8012 Create Table: CREATE TABLE `qrw8012` ( `id` int NOT NULL AUTO_INCREMENT, `name` varchar(16) DEFAULT NULL, `dob` date DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_name` (`name`) ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.00 sec) mysql> select * from qrw8012; +----+--------+------------+ | id | name | dob | +----+--------+------------+ | 1 | jc | 0001-01-01 | | 2 | sriram | 1993-06-17 | | 3 | vijaya | 1969-11-23 | | 4 | durai | 1963-10-19 | | 5 | asha | 1992-06-26 | | 6 | sakthi | 1992-07-13 | +----+--------+------------+ 6 rows in set (0.00 sec) |
Requirement
The requirement is to update the column “name” from “sakthi” to “hercules7sakthi” WHERE id = 6. The UPDATE query from the application looks like this: