MySQL 8.x DDL重写器和查询重写器插件：实现和用例

原创小小亮 2020-08-11

1332

重写MySQL查询以提高性能是每个DBA应该意识到的重要过程，以便他们可以在运行时修复错误的查询，而无需在应用程序端更改代码。ProxySQL对重写查询提供了强大的支持。

到目前为止，MySQL社区提供了两个内置的查询重写插件来执行此任务。最近，他们推出了插件“ ddl_rewriter”。

查询重写器插件：它支持MySQL 8.0.12中的INSERT / UPDATE / DELETE / REPLACE语句。
ddl_rewritter插件：它支持CREATE TABLE语句。在MySQL 8.0.16引入。

在这个博客中，我将解释实现和插件测试的完整过程。该测试基于MySQL 8.x功能。

查询重写器插件

该插件将帮助修改服务器在执行之前接收到的SQL语句。在MySQL 8.0.12之前，该插件仅支持SELECT。从MySQL 8.0.12起，该插件还支持INSERT，UPDATE，DELETE，REPLACE。

实验操作

有两个SQL文件可以执行安装和卸载操作。这些文件位于共享文件夹下。

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

我们可以在运行时实现重写器插件。
加载SQL文件“ install_rewritter.sql”时，它将安装插件“ rewriter.so”，并为操作创建其自己的数据库，表和函数。

通过加载安装程序SQL文件来安装插件：

[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)

插件安装完成，您可以从上述日志中进行验证。

测试用例

（从UPDATE删除LOWER函数以避免FTS）

我创建了一个表“ qrw8012”，并做了一些记录以供测试。

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)

需求

要求是将“ 名称” 列从“ sakthi”更新为“ hercules7sakthi”，其中id =6。来自应用程序的UPDATE查询如下所示：

update qrw8012 set name='hercules7sakthi' where LOWER(name)='sakthi';

从数据库的角度来看，我所有的行都仅使用小写字母进行更新。因此，这里不需要LOWER功能。同样，在WHERE子句列上使用LOWER函数将隐藏该特定列的索引。在我们的例子中，查询将扫描整个表（FTS）。

具有降低功能

mysql> show create table qrw8012\G
  KEY `idx_name` (`name`)
1 row in set (0.18 sec)

mysql> explain select * from qrw8012 where LOWER(name)='sakthi'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: qrw8012
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 6
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

它将进行全表扫描（FTS）。

没有降低功能

mysql> explain select * from qrw8012 where name='sakthi'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: qrw8012
   partitions: NULL
         type: ref
possible_keys: idx_name
          key: idx_name
      key_len: 67
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

在这种情况下，查询使用的是可用索引。

*注意：出于分析目的，我已将UPDATE转换为SELECT。 *

在上面的示例中，我具有“名称”列的索引。但是，它仍然不能与LOWER功能一起使用。如果删除LOWER函数，则索引可用。让我们看看如何使用查询重写插件解决此问题。

第一步，我需要更新“ rewrite_rules”表中的查询规则。以下是更新查询规则时要遵循的关键点。

我们必须使用查询摘要输出来配置查询规则。
一旦修改了规则表，我们就必须始终调用函数“ flush_rewrite_rules”。
如果错误地配置了查询规则，则会收到错误消息“ ERROR 1644（45000）：某些规则加载失败。” 在冲洗函数调用期间。
我们可以检查警告消息以了解是否应用了查询规则。

mysql> insert into rewrite_rules
    -> (id,pattern_database,pattern,replacement) values
    -> (1,'percona','update qrw8012 set name = ? where LOWER(name) = ?','update qrw8012 set name = ? where name = ?');
Query OK, 1 row affected (0.01 sec)

mysql> call query_rewrite.flush_rewrite_rules();
Query OK, 1 row affected (0.03 sec)

mysql> select id,pattern_database,pattern,replacement from rewrite_rules\G
*************************** 1. row ***************************
              id: 1
pattern_database: percona
         pattern: update qrw8012 set name = ? where LOWER(name) = ?
     replacement: update qrw8012 set name = ? where name = ?
1 row in set (0.00 sec)

我配置了查询规则，因此现在执行查询。

mysql> update qrw8012 set name='hercules7sakthi' where LOWER(name)='sakthi';
Query OK, 1 row affected, 1 warning (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 1

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Note
   Code: 1105
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
1 row in set (0.00 sec)

Output from general log :

2020-06-22T11:20:36.952153Z   22 Query update qrw8012 set name = 'hercules7sakthi' where name = 'sakthi'

是的，它运作良好。我们可以通过检查警告消息和常规日志输出来确认这一点。

要卸载该插件，您必须加载SQL文件“ uninstall_rewriter.sql”。它将删除数据库，功能并卸载插件。

[root@hercules7sakthi3 mysql]# cat /usr/share/mysql-8.0/uninstall_rewriter.sql 
...
...
DROP DATABASE IF EXISTS query_rewrite;
DROP FUNCTION load_rewrite_rules;
UNINSTALL PLUGIN rewriter;

DDL Rewriter插件

MySQL社区团队在MySQL 8.0.16中引入了ddl_rewriter插件。该插件可用于修改服务器接收的CREATE TABLE语句。该插件将从CREATE TABLE语句中删除以下子句。

加密
数据目录
索引目录

实验操作：

我们可以使用INSTALL PLUGIN命令配置插件。

mysql> install plugin ddl_rewriter soname 'ddl_rewriter.so';
Query OK, 0 rows affected (0.04 sec)

mysql> select plugin_name,plugin_status, plugin_version from information_schema.plugins where plugin_name like '%ddl%';
+--------------+---------------+----------------+
| plugin_name  | plugin_status | plugin_version |
+--------------+---------------+----------------+
| ddl_rewriter | ACTIVE        | 1.0            |
+--------------+---------------+----------------+
1 row in set (0.01 sec)

一旦安装了ddl_rewriter，就可以使用–ddl-rewriter选项进行后续服务器启动，以控制ddl_rewriter插件的激活。例如，要禁用该功能：

[mysqld]
ddl-rewriter = OFF

测试用例

（不使用ENCRYPTION，DATA DIRECTORY和INDEX DIRECTORY将表结构从源迁移到目标）

需求

我有两个MySQL环境，分别称为“源”和“目标”。在我的源环境中，我所有的表都配置了加密，并且某些表具有不同的DATA DIRECTORY和INDEX DIRECTORY。

要求是我需要将表“ ddl_rwtest”从源迁移到目标。该表具有加密功能，并且具有不同的DATA DIRECTORY和INDEX DIRECTORY。我不需要在目的地进行加密以及将数据和索引目录分开。

从源头来看，表结构如下所示：

create table ddl_rwtest 
(id int primary key, name varchar(16),dob date,msg text) 
ENCRYPTION='Y'  
DATA DIRECTORY = '/mysql/data'
INDEX DIRECTORY = '/mysql/index';

处理

第一步，如实现部分所示，我启用了ddl_rewriter插件。现在，我将使用相同的SQL命令加载结构。

mysql> create table ddl_rwtest 
    -> (id int primary key, name varchar(16),dob date,msg text) 
    -> ENCRYPTION='Y'  
    -> DATA DIRECTORY = '/mysql/data'
    -> INDEX DIRECTORY = '/mysql/index';
Query OK, 0 rows affected, 1 warning (0.08 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Note
   Code: 1105
Message: Query 'create table ddl_rwtest 
(id int primary key, name varchar(16),dob date,msg text) 
ENCRYPTION='Y'  
DATA DIRECTORY = '/mysql/data'
INDEX DIRECTORY = '/mysql/index'' rewritten to 'create table ddl_rwtest 
(id int primary key, name varchar(16),dob date,msg text) ' by a query rewrite plugin
1 row in set (0.00 sec)

mysql> show create table ddl_rwtest\G
*************************** 1. row ***************************
       Table: ddl_rwtest
Create Table: CREATE TABLE `ddl_rwtest` (
  `id` int NOT NULL,
  `name` varchar(16) DEFAULT NULL,
  `dob` date DEFAULT NULL,
  `msg` text,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.01 sec)

从上面的日志中，ddl_rewriter插件已从我的SQL命令中删除了那些加密和数据/索引目录。您可以验证警告消息以确认这一点。

该插件将真正帮助使用逻辑备份进行大规模数据结构迁移。

结论

MySQL社区团队似乎正在积极地进行Query rewrite插件的开发，因为我们有一个来自MySQL 8.0.16的新DDL rewriter插件。现在，该插件仅支持CREATE TABLE语句，我也期待其他DDL语句的更多功能和支持。

作者：Sri Sakthivel
文章来源：https://www.percona.com/blog/2020/08/07/mysql-8-x-ddl-rewriter-and-query-rewriter-plugins-implementation-and-use-cases/

mysql

最后修改时间：2020-08-11 12:49:16

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

MySQL 8.x DDL重写器和查询重写器插件：实现和用例

查询重写器插件

实验操作

测试用例

需求

具有降低功能

没有降低功能

DDL Rewriter插件

实验操作：

测试用例

需求

处理

结论

评论