前言
MySQL在5.7.6开始支持Query Rewrite,能够将符合对应pattern的SQL语句进行重写。如果是普通的只查询单行记录的SQL，如果表小的话，加个索引能够很快的解决问题。但是如果表有几十个G，或者SQL的where条件已经全部都在索引上了，但是执行了count(*)或者group导致扫描了大量数据，除了下线SQL或者停止应用没有别的更好办法，这时候我们可以直接把count(*)的结果拿到，直接RewriteSQL进行返回，这个特性就非常有用了。
我们来实际的测试一下。
Install Plugin
安装创建非常简单，cd 到软件的安装目录的share子目录。
$ cd /opt/mysql/share
$ ll | grep rewrite
-rw-r--r-- 1 root root 1812 Jun 27 2018 install_rewriter.sql
-rw-r--r-- 1 root root 834 Jun 27 2018 uninstall_rewriter.sql
我们可以看到有两个SQL文件，我们来安装
shell> mysql -u root -p < install_rewriter.sql
Enter password: (enter root password here)
安装上之后，默认就已经启用了Plugin，我们可以通过如下命令进行查看
mysql> SHOW GLOBAL VARIABLES LIKE 'rewriter_enabled';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| rewriter_enabled | ON |
+------------------+-------+
这时候，就已经创建好了一个query_rewrite库 以及在query_rewrite库里面创建了一张rewrite_rules表。
mysql >show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| mytest |
| performance_schema |
| query_rewrite |
| sys |
| sysbench |
| test |
+--------------------+
8 rows in set (0.00 sec)
mysql >show tables;
+-------------------------+
| Tables_in_query_rewrite |
+-------------------------+
| rewrite_rules |
+-------------------------+
1 row in set (0.00 sec)
Plugin 用法
1.添加重新规则
INSERT INTO query_rewrite.rewrite_rules (pattern, replacement)
VALUES('SELECT ?', 'SELECT ? + 1');
2.将规则刷新到内存里面
mysql> CALL query_rewrite.flush_rewrite_rules();
mysql> 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: 46b876e64cd5c41009d91c754921f1d4
normalized_pattern: select ?
当插件读取到rewrite_rules表的记录时，会生成一个normalized_pattern和pattern_digest。normalized_pattern表示常规化的pattern，例如plugin 会把 select count(*) 改写成 select count(?)
3.查看加载失败
如果某个规则加载失败，将显示
ERROR 1644 (45000): Loading of some rule(s) failed.
从rewrite_rules 表的message列里面可以详细看到失败的原因
mysql > select * from rewrite_rules\G
*************************** 1. row ***************************
id: 8
pattern: insert into t1 values(1)
pattern_database: NULL
replacement: insert into t1 values(2)
enabled: YES
message: Pattern needs to be a a select statement.
pattern_digest: NULL
normalized_pattern: NULL
4 rows in set (0.00 sec)
4.Rewrite支持的类型
MySQL 5.7的Query Rewrite Plugin 只支持SELECT，在8.0 里面会支持INSERT，UPDATE，DELETE。
The Rewriter query plugin currently only support SELECT statements as patterns. The purpose of this worklog is to expand the Rewriter plugin to support INSERT, UPDATE and DELETE statements. This expansion should be an addition of features and should not break existing functionality in the Rewriter plugin, or the mysql server.
5.查看Rewrite的语句
mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
Level: Note
Code: 1105
Message: Query 'SELECT 10' rewritten to 'SELECT 10 + 1' by a query rewrite plugin
6.禁用某个rule
UPDATE query_rewrite.rewrite_rules SET enabled = 'NO' WHERE id = 1;
CALL query_rewrite.flush_rewrite_rules();
7.启用某个rule
UPDATE query_rewrite.rewrite_rules SET enabled = 'YES' WHERE id = 1;
CALL query_rewrite.flush_rewrite_rules();
8.设置匹配的数据库
在编写rule的时候，要么指定pattern_database字段，要么就直接写到表名里面，例如
insert into rewrite_rules(pattern,replacement,pattern_database) values('select * from sbtest1 where id=1','select * from sbtest1 where id=2','sysbench')
匹配的是
mysql > use sysbench;
mysql > select * from sbtest1 where id=1
insert into rewrite_rules(pattern,replacement) values('select * from sysbench.sbtest1 where id=1','select * from sysbench.sbtest1 where id=2')
匹配的是
mysql > use test;
mysql > select * from sysbench.sbtest1 where id=1
insert into rewrite_rules(pattern,replacement)
否则会出现报错
message: Parse error in pattern: >>No database selected<<
实操
采用sysbench 表，单表数据量是1000万
python脚本
#!/usr/bin/env python
conn = pymysql.connect(***)
b_time = time.time()
cursor = conn.cursor(pymysql.cursors.DictCursor)
cursor.execute('select count(*) from sbtest1')
print 'time cost:%s ' % (time.time()-b_time)
ret = cursor.fetchall()
print ret
print cursor.description
print cursor.rownumber
cursor.close()
conn.close()
不用Query Rewrite Plugin的输出
time cost:1.94331288338
[{u'count(*)': 10000000}]
((u'count(*)', 8, None, 21, 21, 0, False),)
1
编写Query Rewrite Plugin规则
insert into query_rewrite.rewrite_rules(pattern,replacement,pattern_database) values('select count(*) from sbtest1','select 10000000 as "count(*)"','sysbench');
CALL query_rewrite.flush_rewrite_rules();
使用Query Rewrite Plugin的输出
time cost:0.0701510906219
[{u'count(*)': 10000000}]
((u'count(*)', 8, None, 8, 8, 0, False),)
Warning: (1105, 'Query \'select count(*) from sbtest1\' rewritten to \'select 10000000 as "count(*)"\' by a query rewrite plugin')
self._do_get_result()
1
直接改写count(*)，让他直接返回，时间上从1.94秒变成了0.07秒，提升非常明显，在业务上无法及时更改count(*) 实现的时候，快速返回count(*)的结果将是一个不错的方法。
压测表现
压测命令：
sysbench --test=oltp.lua --oltp-tables-count=10 --oltp-table-size=5000000 --mysql-db=sysbench --mysql-user=test --mysql-password=test --mysql-socket=mysql.sock --max-time=14400 --max-requests=0 --num-threads=72 --oltp-test-mode=complex run
-- 未开启Plugin
-QPS- -TPS- -Hit%- ------threads------
ins upd del sel iud| lor hit| run con cre cac|
8962 17921 8962 125995 35845| 1604426 100.00| 44 73 0 2|
8803 17605 8802 122898 35210| 1566268 100.00| 38 73 0 2|
8828 17669 8830 123969 35327| 1579377 100.00| 37 73 0 2|
--开启plugin
-QPS- -TPS- -Hit%- ------threads------
| ins upd del sel iud| lor hit| run con cre cac|
| 8890 17773 8890 124379 35553| 1589927 100.00| 37 73 0 2|
| 8858 17715 8858 124079 35431| 1581700 100.00| 39 73 0 2|
| 8730 17469 8731 121991 34930| 1558362 100.00| 41 73 0 2|
整体开启rewrite plugin的时候，对数据库整体影响小于1%，在可接受范围内。
小结
在加索引无法解决，加索引时间过长，无法停止业务，修改代码重新发布时间过长等情况下，利用Query Rewrite Plugin来快速改写SQL，消除慢查，恢复数据库，是个不错的选择。
参考资料：
1.https://dev.mysql.com/worklog/task/?spm=a2c4e.11153940.blogcont53686.7.704c42941UdZy8&id=8776
2.https://dev.mysql.com/doc/refman/5.7/en/rewriter-query-rewrite-plugin-usage.html
近期评论