MySQL 5.7新特性 · Query Rewrite 学习心得分享

文章目录[隐藏]

一、使用场景
二、安装或卸载插件
三、使用示例
参考链接

一、使用场景

在业务繁忙并且紧急上线，对就是那种特别繁忙，又不能停的那种SQL。在系统不忙的时候明明跑的很好。但是一旦业务繁忙，就会造成业务阻塞。当查看MySQL的满查询日志中发现大量慢查询日志，（不是单单加索引就能搞定的哦）。这时候怎么办，难道怒对开发一顿，这时候你需要MySQL5.7新特性Query rewrite Plugin插件了。

从MySQL5.7.6版本开始支持Rewrite Plugin，可以将符合条件的SQL进行重写。在真实世界中，这个特性还是非常有用的，例如错误的上线了某个SQL，但由于无法走到索引导致全库; 或者你可能使用某个第三方的已编译好的软件，但SQL可能执行错误，你又无法直接修改应用，这个特性将会非常有用。你可以去编写符合你要求的插件。

二、安装或卸载插件

要安装或卸载Rewriter查询重写插件，请选择位于shareMySQL安装目录中的相应脚本：

install_rewriter.sql：选择此脚本来安装Rewriter插件及其相关组件。
uninstall_rewriter.sql：选择此脚本来卸载Rewriter插件及其相关组件。

[root@wjq ~]# mysql -u root -p -S /tmp/mysql3306.sock < /usr/local/mysql/share/install_rewriter.sql

运行安装脚本应安装并启用插件。要验证这一点，请连接到服务器并执行以下语句：

root@localhost:mysql3306.sock [(none)]>SHOW GLOBAL VARIABLES LIKE 'rewriter_enabled';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| rewriter_enabled | ON    |
+------------------+-------+
1 row in set (0.00 sec)

查看已经安装的插件

root@localhost:mysql3306.sock [(none)]>show plugins;

三、使用示例

安装完成之后，就会多出一个query_rewrite数据库，库中包含有一张表rewrite_rules

root@localhost:mysql3306.sock [(none)]>use query_rewrite;

root@localhost:mysql3306.sock [query_rewrite]>show tables;
+-------------------------+
| Tables_in_query_rewrite |
+-------------------------+
| rewrite_rules           |
+-------------------------+
1 row in set (0.00 sec)

表结构：

CREATE TABLE `rewrite_rules` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `pattern` varchar(10000) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `pattern_database` varchar(20) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `replacement` varchar(10000) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `enabled` enum('YES','NO') CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT 'YES',
  `message` varchar(1000) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `pattern_digest` varchar(32) DEFAULT NULL,
  `normalized_pattern` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

字段说明：

id :规则id ，此列是表的主键。

pattern：需要改写的源SQL，表示规则匹配的语句的模式的模板。使用?表示匹配的数据值是参数标记。

pattern_database：需要改写的DB名称；该数据库用于匹配语句中不合格的表名。如果相应的数据库名和表名相同，则语句中的合格表名与模式中的合格名匹配。仅当默认数据库与默认数据库相同pattern_database且表名相同时，语句中不合格的表名才会与模式中的不合格名匹配。

replacement：指定改写后的样子；指示如何重写与pattern列值匹配的语句的模板。使用 ?表示匹配的数据值是参数标记。在重写的语句中，插件使用与中的相应标记匹配的数据值替换?参数标记。

enabled：是否启用规则。加载操作（通过调用flush_rewrite_rules() 存储过程来执行）Rewriter仅在此列为时将规则从表中加载到内存中的缓存中YES。

通过此列，可以在不删除规则的情况下停用规则：将列设置为以外的值 YES，然后将表重新加载到插件中。

message：插件使用此列与用户交流。如果将规则表加载到内存中时未发生错误，则插件会将message 列设置为NULL。非NULL值表示错误，并且列内容为错误消息。在以下情况下可能会发生错误：

模式或替换是产生语法错误的错误SQL语句。

替换包含的? 参数标记多于模式。

如果发生加载错误，则插件还将 Rewriter_reload_error 状态变量设置为ON。

pattern_digest：此列用于调试和诊断。如果在将规则表加载到内存时该列存在，则插件将使用模式摘要对其进行更新。如果您尝试确定为什么某些语句无法重写的情况，则此列可能会很有用。

normalized_pattern：此列用于调试和诊断。如果在将规则表加载到内存时该列存在，则插件将使用模式的规范化形式对其进行更新。如果您尝试确定为什么某些语句无法重写的情况，则此列可能会很有用。

详细可参考：https://dev.mysql.com/doc/refman/5.7/en/rewriter-query-rewrite-plugin-reference.html

配置规则

root@localhost:mysql3306.sock [employees]>insert into query_rewrite.rewrite_rules(pattern, replacement, pattern_database) values ("select * from employees where emp_no + 1 = ?","select * from employees where emp_no = ? - 1", "employees");       
Query OK, 1 row affected (0.00 sec)

root@localhost:mysql3306.sock [employees]>select * from query_rewrite.rewrite_rules\G;                                                                                                                         
*************************** 1. row ***************************
                id: 1
           pattern: select * from employees where emp_no + 1 = ?
  pattern_database: employees
       replacement: select * from employees where emp_no = ? - 1
           enabled: YES
           message: NULL
    pattern_digest: NULL
normalized_pattern: NULL
1 row in set (0.00 sec)

调用存储过程

root@localhost:mysql3306.sock [employees]>CALL query_rewrite.flush_rewrite_rules();
Query OK, 0 rows affected (0.02 sec)

该存储过程先提交当前的会话的事务（如果有未提交的事务的话），Reset Query Cache.然后调用一个UDF函数load_rewrite_rules将规则加载到插件的内存中。

尝试执行SQL语句查看改写结果:

root@localhost:mysql3306.sock [employees]>select * from employees where emp_no + 1 = 10004;
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  |
+--------+------------+------------+-----------+--------+------------+
|  10003 | 1959-12-03 | Parto      | Bamford   | M      | 1986-08-28 |
+--------+------------+------------+-----------+--------+------------+
1 row in set, 1 warning (0.00 sec)

root@localhost:mysql3306.sock [employees]>show warnings;
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                            |
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1105 | Query 'select * from employees where emp_no + 1 = 10004' rewritten to 'select * from employees where emp_no = 10004 - 1' by a query rewrite plugin |
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Rewriter插件已经实现了比较完备的重写功能，具体如何玩耍参阅官方文档。但如果你还有一些自己个性化的定义，就可能需要修改或编写自己的插件，下文简单的对新增的一些接口进行描述。