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”.
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.
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.
There are two SQL files to perform the install and uninstall operations. The files are located under the shared folder.
|
1 |
mysql> show global variables like 'lc_messages_dir';<br>+-----------------+----------------------------+<br>| Variable_name | Value |<br>+-----------------+----------------------------+<br>| lc_messages_dir | /usr/share/percona-server/ |<br>+-----------------+----------------------------+<br>1 row in set (0.01 sec)<br><br>[root@hercules7sakthi3 ~]# cd /usr/share/mysql-8.0/<br>[root@hercules7sakthi3 mysql-8.0]# ls -lrth | grep -i rewriter<br>-rw-r--r--. 1 root root 1.3K Mar 26 14:16 uninstall_rewriter.sql<br>-rw-r--r--. 1 root root 2.2K Mar 26 14:16 install_rewriter.sql |
Install the plugin by loading the installer SQL file:
|
1 |
[root@hercules7sakthi3 mysql-8.0]# mysql -vv < install_rewriter.sql | grep -i 'create|install|drop'<br>CREATE DATABASE IF NOT EXISTS query_rewrite<br>CREATE TABLE IF NOT EXISTS query_rewrite.rewrite_rules (<br>INSTALL PLUGIN rewriter SONAME 'rewriter.so'<br>CREATE FUNCTION load_rewrite_rules RETURNS STRING<br>CREATE PROCEDURE query_rewrite.flush_rewrite_rules()<br><br>mysql> show schemas like 'query_rewrite';<br>+--------------------------+<br>| Database (query_rewrite) |<br>+--------------------------+<br>| query_rewrite |<br>+--------------------------+<br>1 row in set (0.00 sec)<br><br>mysql> show tables from query_rewrite;<br>+-------------------------+<br>| Tables_in_query_rewrite |<br>+-------------------------+<br>| rewrite_rules |<br>+-------------------------+<br>1 row in set (0.05 sec)<br><br>mysql> show create table query_rewrite.rewrite_rulesG<br>*************************** 1. row ***************************<br> Table: rewrite_rules<br>Create Table: CREATE TABLE `rewrite_rules` (<br> `id` int NOT NULL AUTO_INCREMENT,<br> `pattern` varchar(5000) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,<br> `pattern_database` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,<br> `replacement` varchar(5000) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,<br> `enabled` enum('YES','NO') CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT 'YES',<br> `message` varchar(1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,<br> `pattern_digest` varchar(64) DEFAULT NULL,<br> `normalized_pattern` varchar(100) DEFAULT NULL,<br> PRIMARY KEY (`id`)<br>) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci<br>1 row in set (0.00 sec)<br><br>mysql> select plugin_name,plugin_status, plugin_version from information_schema.plugins where plugin_name='Rewriter';<br>+-------------+---------------+----------------+<br>| plugin_name | plugin_status | plugin_version |<br>+-------------+---------------+----------------+<br>| Rewriter | ACTIVE | 0.2 |<br>+-------------+---------------+----------------+<br>1 row in set (0.00 sec) |
Plugin installation is completed, and you can verify that from the above logs.
I have created a table “qrw8012” and made some records for testing purposes.
|
1 |
mysql> show create table qrw8012G<br>*************************** 1. row ***************************<br> Table: qrw8012<br>Create Table: CREATE TABLE `qrw8012` (<br> `id` int NOT NULL AUTO_INCREMENT,<br> `name` varchar(16) DEFAULT NULL,<br> `dob` date DEFAULT NULL,<br> PRIMARY KEY (`id`),<br> KEY `idx_name` (`name`)<br>) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci<br>1 row in set (0.00 sec)<br><br>mysql> select * from qrw8012;<br>+----+--------+------------+<br>| id | name | dob |<br>+----+--------+------------+<br>| 1 | jc | 0001-01-01 |<br>| 2 | sriram | 1993-06-17 |<br>| 3 | vijaya | 1969-11-23 |<br>| 4 | durai | 1963-10-19 |<br>| 5 | asha | 1992-06-26 |<br>| 6 | sakthi | 1992-07-13 |<br>+----+--------+------------+<br>6 rows in set (0.00 sec) |
The requirement is to update the column “name” from “sakthi” to “hercules7sakthi” WHERE id = 6. The UPDATE query from the application looks like this:
|
1 |
update qrw8012 set name='hercules7sakthi' where LOWER(name)='sakthi'; |
From a database perspective, all my rows are updated with lower case only. So, here the LOWER function is not required. Also, using the LOWER function on the WHERE clause column will hide the index for that particular column. In our case, the query will scan the entire table ( FTS ).
|
1 |
mysql> show create table qrw8012G<br> KEY `idx_name` (`name`)<br>1 row in set (0.18 sec)<br><br>mysql> explain select * from qrw8012 where LOWER(name)='sakthi'G<br>*************************** 1. row ***************************<br> id: 1<br> select_type: SIMPLE<br> table: qrw8012<br> partitions: NULL<br> type: ALL<br>possible_keys: NULL<br> key: NULL<br> key_len: NULL<br> ref: NULL<br> rows: 6<br> filtered: 100.00<br> Extra: Using where<br>1 row in set, 1 warning (0.00 sec) |
It goes to a full table scan (FTS).
|
1 |
mysql> explain select * from qrw8012 where name='sakthi'G<br>*************************** 1. row ***************************<br> id: 1<br> select_type: SIMPLE<br> table: qrw8012<br> partitions: NULL<br> type: ref<br>possible_keys: idx_name<br> key: idx_name<br> key_len: 67<br> ref: const<br> rows: 1<br> filtered: 100.00<br> Extra: NULL<br>1 row in set, 1 warning (0.00 sec) |
In this case, the query is using the available index.
Note: I have converted the UPDATE to SELECT for analysis purposes.
From the above example, I have the index for the column “name”. But, still, it is not usable with the LOWER function. If I remove the LOWER function, the index is usable. Let’s see how we can fix this using the query rewrite plugin.
In the first step, I need to update the query rules in the table “rewrite_rules”. Below are the key points to be followed when updating the query rules.
|
1 |
mysql> insert into rewrite_rules<br> -> (id,pattern_database,pattern,replacement) values<br> -> (1,'percona','update qrw8012 set name = ? where LOWER(name) = ?','update qrw8012 set name = ? where name = ?');<br>Query OK, 1 row affected (0.01 sec)<br><br>mysql> call query_rewrite.flush_rewrite_rules();<br>Query OK, 1 row affected (0.03 sec)<br><br>mysql> select id,pattern_database,pattern,replacement from rewrite_rulesG<br>*************************** 1. row ***************************<br> id: 1<br>pattern_database: percona<br> pattern: update qrw8012 set name = ? where LOWER(name) = ?<br> replacement: update qrw8012 set name = ? where name = ?<br>1 row in set (0.00 sec) |
I configured the query rules, so now executing the query.
|
1 |
mysql> update qrw8012 set name='hercules7sakthi' where LOWER(name)='sakthi';<br>Query OK, 1 row affected, 1 warning (0.02 sec)<br>Rows matched: 1 Changed: 1 Warnings: 1<br><br>mysql> show warningsG<br>*************************** 1. row ***************************<br> Level: Note<br> Code: 1105<br>Message: Query 'update qrw8012 set name='hercules7sakthi' where LOWER(name)='sakthi'' rewritten to 'update qrw8012 set name = 'hercules7sakthi' where name = 'sakthi'' by a query rewrite plugin<br>1 row in set (0.00 sec)<br><br>Output from general log :<br><br>2020-06-22T11:20:36.952153Z 22 Query update qrw8012 set name = 'hercules7sakthi' where name = 'sakthi'<br><br> |
Yes, it works well. We can confirm this by checking the warning message and the general log output.
To uninstall the plugin you have to load the SQL file “uninstall_rewriter.sql”. It will drop the database, function, and uninstall the plugin as well.
|
1 |
[root@hercules7sakthi3 mysql]# cat /usr/share/mysql-8.0/uninstall_rewriter.sql <br>...<br>...<br>DROP DATABASE IF EXISTS query_rewrite;<br>DROP FUNCTION load_rewrite_rules;<br>UNINSTALL PLUGIN rewriter; |
MySQL community team introduced the ddl_rewriter plugin in MySQL 8.0.16. The plugin can be used to modify the CREATE TABLE statements received by the server. The plugin will remove the below clauses from the CREATE TABLE statement.
We can configure the plugin by using the INSTALL PLUGIN command.
|
1 |
mysql> install plugin ddl_rewriter soname 'ddl_rewriter.so';<br>Query OK, 0 rows affected (0.04 sec)<br><br>mysql> select plugin_name,plugin_status, plugin_version from information_schema.plugins where plugin_name like '%ddl%';<br>+--------------+---------------+----------------+<br>| plugin_name | plugin_status | plugin_version |<br>+--------------+---------------+----------------+<br>| ddl_rewriter | ACTIVE | 1.0 |<br>+--------------+---------------+----------------+<br>1 row in set (0.01 sec) |
Once ddl_rewriter is installed, you can use the –ddl-rewriter option for subsequent server startups to control ddl_rewriter plugin activation. For example, to deactivate the feature:
|
1 |
[mysqld]<br>ddl-rewriter = OFF |
I have two MySQL environments, which are called “source” and “destination”. At my source environment, all my tables are configured with encryption and some of the tables have different DATA DIRECTORY and INDEX DIRECTORY.
The requirement is I need to migrate the table “ddl_rwtest” from source to destination. The table has encryption and different DATA DIRECTORY and INDEX DIRECTORY as well. I don’t need the encryption and separate data and index directory at my destination.
At the source, the table structure looks like this:
|
1 |
create table ddl_rwtest <br>(id int primary key, name varchar(16),dob date,msg text) <br>ENCRYPTION='Y' <br>DATA DIRECTORY = '/mysql/data'<br>INDEX DIRECTORY = '/mysql/index'; |
In the first step, I enabled the ddl_rewriter plugin as shown in the implementation section. Now, I am just going to load the structure using the same SQL command.
|
1 |
mysql> create table ddl_rwtest <br> -> (id int primary key, name varchar(16),dob date,msg text) <br> -> ENCRYPTION='Y' <br> -> DATA DIRECTORY = '/mysql/data'<br> -> INDEX DIRECTORY = '/mysql/index';<br>Query OK, 0 rows affected, 1 warning (0.08 sec)<br><br>mysql> show warningsG<br>*************************** 1. row ***************************<br> Level: Note<br> Code: 1105<br>Message: Query 'create table ddl_rwtest <br>(id int primary key, name varchar(16),dob date,msg text) <br>ENCRYPTION='Y' <br>DATA DIRECTORY = '/mysql/data'<br>INDEX DIRECTORY = '/mysql/index'' rewritten to 'create table ddl_rwtest <br>(id int primary key, name varchar(16),dob date,msg text) ' by a query rewrite plugin<br>1 row in set (0.00 sec)<br><br>mysql> show create table ddl_rwtestG<br>*************************** 1. row ***************************<br> Table: ddl_rwtest<br>Create Table: CREATE TABLE `ddl_rwtest` (<br> `id` int NOT NULL,<br> `name` varchar(16) DEFAULT NULL,<br> `dob` date DEFAULT NULL,<br> `msg` text,<br> PRIMARY KEY (`id`)<br>) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci<br>1 row in set (0.01 sec) |
From the above logs, the ddl_rewriter plugin has removed those encryption and data/index directories from my SQL command. You can verify the warning message to confirm this.
This plugin will really help with huge data structure migrations using logical backups.
It seems the MySQL community team is proactively working on Query rewrite plugin development as we have a new DDL rewriter plugin from MySQL 8.0.16. Right now the plugin supports only CREATE TABLE statements, and I am looking forward to more features and support of other DDL statements as well.
