少年，MySQL Query Rewrite了解一下

查看原文

其他

少年，MySQL Query Rewrite了解一下

Original: yangyidba yangyidba 2019-07-08

一前言

作为DBA 大家是否遇到系统遇到bad sql 导致数据库thread_running 飙高，开发短期不能修复(紧急修改，紧急发布之后可能需要小时级别的时间)，而且自己又束手无策干等着的情形？

如何解决呢？MySQL 从版本 5.7.6 开始支持Query Rewrite 功能,该功能可以将符合特定规则的SQL语句进行重写。

二 Query Rewrite Plugin介绍

2.1 原理

一个sql的请求流程如下:

QR插件支持两种重写方式，一种是在语法解析之前，直接修改SQL字符串，一种是在语法解析之后，通过操控语法解析树来进行重写。

2.2 安装与卸载

MySQL 以插件的方式提供查询重新功能，与其他插件不同的时候，安装该plugin插件会创建数据库query_rewrite和存储过程 flush_rewrite_rules()。安装的时候只需要执行:

source /path/mysql/share/install_rewriter.sql

安装完之后可以通过该命令查看是否启用，

[TEST:3316]>SHOW GLOBAL VARIABLES LIKE 'rewriter_enabled';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| rewriter_enabled | ON    |
+------------------+-------+

卸载也很简单，直接执行如下命令即可。

source /path/mysql/share/uninstall_rewriter.sql

需要注意的是:

不过卸载之再安装Rewriter plugin,show plugin 显示 Rewriter为deleted，不过此时是生效的。最好卸载完之后重启数据库，在做其他操作。生产环境大家认真评估。

2.3 Query Rewrite 的相关结构

启用Query Rewrite之后，系统中也会多一个数据库query_rewrite和表rewrite_rules

[TEST:3316]>use  query_rewrite;
Database changed
[TEST:3316]>show tables;
+-------------------------+
| Tables_in_query_rewrite |
+-------------------------+
| rewrite_rules           |
+-------------------------+
1 row in set (0.00 sec)

[TEST:3316]>show create table rewrite_rules \G
*************************** 1. row ***************************
       Table: rewrite_rules
Create Table: 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 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

介绍其中的核心字段含义:

pattern:         表示要改写的源SQL模板
pattern_database:需要改写SQL所访问的dbname，如果sql from 后面的table没有带dbname 前缀，必须指定该参数，否则报错。
replacement:     指定改写后的SQL模板或者具体的sql
enabled:         表示是否启用
message:         如果加载规则遇到报错，错误信息会显示在这里，一般为null。

如何写入 rewrite_rules

主要是sql的改写，我们可以使用特点的sql 比如 from tab where uid=4 。也可以讲sql的参数抽取出来使用 ? 代替，比如

from tab where uid=？
from tab where id>？

具体使用可以参考下面章节的案例。

存储过程 flush_rewrite_rules()，具体创建语句大家可以查看 /path/mysql/share/install_rewriter.sql 。当我们写一条规则到rewrite_rules表之后需要执行该存储过程，将重写规则加载到内存中。

call query_rewrite.flush_rewrite_rules();

综合以上介绍，我们总结开启重新功能:

1. 安装插件，检查是否安装成功。
2. 将规则写入到表rewrite_rules中。
3. 调用存储过程query_rewrite.flush_rewrite_rules();
4. 执行sql

纸上来得终觉浅，据知此事要躬行。

三具体案例实践

我们将通过正确和错误的案例，介绍query rewriter的具体用法和使用注意事项。案例是使用sysbench构造的记录行数为1000w的表sbtest1。

3.1 简单案例

[TEST:3316]>INSERT INTO query_rewrite.rewrite_rules (pattern, replacement)
    -> VALUES('SELECT ?', 'SELECT ? + 1');
Query OK, 1 row affected (0.00 sec)

[TEST:3316]>SELECT * FROM query_rewrite.rewrite_rules\G
*************************** 1. row ***************************
                id: 1
           pattern: SELECT ?
  pattern_database: NULL
       replacement: SELECT ? + 1
           enabled: YES
           message: NULL
    pattern_digest: NULL
normalized_pattern: NULL
1 row in set (0.00 sec)

[TEST:3316]>CALL query_rewrite.flush_rewrite_rules();
Query OK, 0 rows affected, 1 warning (0.01 sec)
###这个warning无关紧要

重新查询 select 10 会看到已经被重写了，结果是显示10+1 。

[TEST:3316]>select 10;
+--------+
| 10 + 1 |
+--------+
|     11 |
+--------+
1 row in set, 1 warning (0.00 sec)

[TEST:3316]>select 10 as num; ---不符合select ？模式，故不生效。
+-----+
| num |
+-----+
|  10 |
+-----+
1 row in set (0.00 sec)

3.2 复杂案例

比如业务场景中遇到 count 一个大表或者 count 语句的where条件涉及到很多记录亦或者where条件的索引发生隐式转换，我们都可以将这些问题sql重新。

比如我们的业务系统并发执行一个count 1000w的大表，单个sql耗时1.8s，此时数据库thread_running活跃会话飙高，开发又无法快速添加缓存或者发布代码解决。我们可以将查询条件重写：

[TEST:3316]>select  count(*) from sysbench.sbtest1;
+----------+
| count(*) |
+----------+
| 10000000 |
+----------+
1 row in set (1.82 sec)

插入重写规则并加载到内存:

[TEST:3316]>insert into query_rewrite.rewrite_rules (pattern, pattern_database,replacement) values('select  count(*) from sysbench.sbtest1 where  id >=1; ','sysbench','select 10000000 as "count(*)"');
Query OK, 1 row affected (0.00 sec)

[TEST:3316]>call query_rewrite.flush_rewrite_rules();
Query OK, 0 rows affected (0.00 sec)

[TEST:3316]>select  count(*) from sysbench.sbtest1;
+----------+
| count(*) |
+----------+
| 10000000 |
+----------+
1 row in set, 1 warning (0.00 sec) <--查询时间下降到1毫秒以内。

waring信息是

Query 'select count(*) from sysbench.sbtest1' rewritten to 'select 10000000 as "count(*)"' by a query rewrite plugin

3.3 错误的例子

一般加载重写规则失败的时候会报错

[TEST:3316]>CALL query_rewrite.flush_rewrite_rules();
ERROR 1644 (45000): Loading of some rule(s) failed.

本案例我们模拟一个有错误语法规则的语句, replacement的语句中为count(*)而非"count(*)"

[TEST:3316]>INSERT INTO query_rewrite.rewrite_rules (pattern, pattern_database,replacement) values('select  count(*) from sysbench.sbtest1','sysbench','select 10000000 as count(*)');
Query OK, 1 row affected (0.00 sec)

[TEST:3316]>SELECT * FROM query_rewrite.rewrite_rules\G
*************************** 1. row ***************************
                id: 1
           pattern: SELECT ?
  pattern_database: NULL
       replacement: SELECT ? + 1
           enabled: YES
           message: NULL
    pattern_digest: 24e5a8c19fe8009f403640dabf2a9e0f
normalized_pattern: select ?
*************************** 2. row ***************************
                id: 2
           pattern: select  count(*) from sysbench.sbtest1
  pattern_database: sysbench
       replacement: select 10000000 as count(*)
           enabled: YES
           message: NULL
    pattern_digest: NULL
normalized_pattern: NULL
2 rows in set (0.00 sec)

[TEST:3316]>CALL query_rewrite.flush_rewrite_rules();##报错
ERROR 1644 (45000): Loading of some rule(s) failed.

###此时，我们需要查询rewrite_rules的message字段的内容，找到报错的原因
(none) [RW][TEST:qa_mytest:3316] 08:14:18 >SELECT * FROM query_rewrite.rewrite_rules where id=2\G
*************************** 1. row ***************************
                id: 2
           pattern: select  count(*) from sysbench.sbtest1
  pattern_database: sysbench
       replacement: select 10000000 as count(*)
           enabled: YES
           message: Parse error in replacement: >>You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'count(*)' at line 1<<
    pattern_digest: NULL
normalized_pattern: NULL
1 row in set (0.00 sec)

其实就是 select 10000000 as count(*) 需要写为 select 10000000 as "count(*)"

3.4 关闭某个规则

[RW][TEST:3316]>update     rewrite_rules  set  enabled='NO' where id=3;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0
[RW][TEST:3316]>CALL query_rewrite.flush_rewrite_rules();
Query OK, 0 rows affected, 1 warning (0.00 sec)

3.5 开启某个规则

[RW][TEST:3316]>update rewrite_rules  set  enabled='YES' where id=3;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0
[RW][TEST:3316]>CALL query_rewrite.flush_rewrite_rules();
Query OK, 0 rows affected, 1 warning (0.00 sec)

3.6 全局开启和关闭重新规则

[RW][TEST:3316]>select 10 ;
+--------+
| 10 + 1 |
+--------+
|     11 |
+--------+
1 row in set, 1 warning (0.00 sec)

[RW][TEST:3316]>set global rewriter_enabled=off;
Query OK, 0 rows affected (0.00 sec)

[RW][TEST:3316]>select 10 ;
+----+
| 10 |
+----+
| 10 |
+----+
1 row in set (0.00 sec)

[RW][TEST:3316]>set global rewriter_enabled=on;
Query OK, 0 rows affected (0.00 sec)

[RW][TEST:3316]>select 10 ;
+--------+
| 10 + 1 |
+--------+
|     11 |
+--------+
1 row in set, 1 warning (0.00 sec)

3.7 设置pattern_database

编写重新规则时候，需要指定pattern_database字段或者sql 语句中写 dbname.tablename ，例如

### 指定数据库名称，则能正常工作
[TEST:3316] >insert into rewrite_rules(pattern,pattern_database,replacement) values('select id from sbtest1 where  id=1000','sysbench','select id from sbtest1 where id=1001');
Query OK, 1 row affected (0.00 sec)
[TEST:3316] >CALL query_rewrite.flush_rewrite_rules();
Query OK, 0 rows affected, 1 warning (0.02 sec)
[TEST:3316] >use sysbench;
Database changed
[TEST:3316] >select id from sbtest1 where  id=1000;
+------+
| id   |
+------+
| 1001 |
+------+
1 row in set, 1 warning (0.00 sec)


当不指定数据库名称时，则报错
[TEST:3316] >insert into rewrite_rules(pattern,replacement) values('select id from sbtest1 where  id=1000','select id from sbtest1 where id=1001');
ERROR 1146 (42S02): Table 'sysbench.rewrite_rules' doesn't exist

四总结

遇到开发短期无法修改sql的时候，dba可以开启该功能，启到雪中送炭的作用，目前查询重新也有部分使用限制。

目前5.7版本支持select查询，不支持DML的sql重新，8.0中会支持DML语句的查询重新功能。
不支持存储过程中的sql改写。
pattern和replacement语句不能以分号结尾，否则无效。

有兴趣的朋友可以自己挖掘其他的玩法和更详细的技术细节，本文如有纰漏也请多多指教。

参考文档

https://dev.mysql.com/doc/refman/5.7/en/rewriter-query-rewrite-plugin.html
http://www.fordba.com/mysql-query-rewrite-plugin-test.html
https://yq.aliyun.com/articles/53686

泪目！8死17伤！江苏一职校持刀伤人案，背后隐情令人心惊！

突发！宜兴一学校发生持刀伤人案件！致8死17伤！太恶劣了！

一小学门口突发！多名学生被撞伤！

“占坑式辩护”，侵犯了谁？

突发！一小学门口发生撞人事件