• 欢迎访问DBA的辛酸事儿,推荐使用最新版火狐浏览器和Chrome浏览器访问本网站
  • 欢迎大家关注博主公众号:DBA的辛酸事儿
  • 博文中若有错误的地方,请大家指正,大家的指正是我前进的动力

MySQL 5.7新特性 · Query Rewrite

MySQL SEian.G 5年前 (2019-10-14) 32732次浏览 已收录 0个评论
文章目录[隐藏]

一、使用场景

在业务繁忙并且紧急上线,对就是那种特别繁忙,又不能停的那种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;

MySQL 5.7新特性 · Query Rewrite

三、使用示例

安装完成之后,就会多出一个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插件已经实现了比较完备的重写功能,具体如何玩耍参阅官方文档。但如果你还有一些自己个性化的定义,就可能需要修改或编写自己的插件,下文简单的对新增的一些接口进行描述。

参考链接

https://blog.csdn.net/weixin_33936401/article/details/90618637

https://dev.mysql.com/doc/refman/5.7/en/rewriter-query-rewrite-plugin.html

MySQL 5.7新特性 · Query Rewrite


如果您觉得本站对你有帮助,那么可以收藏和推荐本站,帮助本站更好地发展,在此谢过各位网友的支持。
转载请注明原文链接:MySQL 5.7新特性 · Query Rewrite
喜欢 (5)
SEian.G
关于作者:
用心去记录工作,用心去感受生活,用心去学着成长;座右铭:苦练七十二变,笑对八十一难
发表我的评论
取消评论

表情 贴图 加粗 删除线 居中 斜体 签到

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址