通过mysql 5.7的新特性query rewrite临时处理故障，紧急恢复业务

一 背景

有时会有大量慢查询日志（不是单单加索引就能搞定的哦），导致整个数据库的性能都受到影响。这时候怎么办？这时候你需要MySQL5.7新特性query_rewrite _Plugin插件了，可以改写sql。

 

有时可将某个慢查询改成select 1,示例：

INSERT INTO query_rewrite.rewrite_rules(pattern, replacement, pattern_database) VALUES (

"SELECT * FROM emp",

"SELECT 1",

"dba");

 

有时需要改写成强制走某个索引：

insert into query_rewrite.rewrite_rules(pattern, replacement, pattern_database) values ( "SELECT k, id from sbtest1 where k = ?", "SELECT k, id from sbtest1 force index(primary) where k = ?", "sb1");

二 具体案例

2.1 安装插件

cd /usr/local/mysql/share

mysql -u root -p < install_rewriter.sql

 

mysql> SHOW GLOBAL VARIABLES LIKE 'rewriter_enabled';

+------------------+-------+ | Variable_name | Value | +------------------+-------+ | rewriter_enabled | ON | +------------------+-------+

新生成了query_rewrite库：

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| dba |

| mysql |

| performance_schema |

| query_rewrite |

| sys |

+--------------------+

6 rows in set (0.00 sec)

 

mysql> use query_rewrite;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

 

Database changed

mysql> show tables;

+-------------------------+

| Tables_in_query_rewrite |

+-------------------------+

| rewrite_rules |

+-------------------------+

1 row in set (0.00 sec)

 

查看rewrite_rules表，表结构大概是这样的

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

 

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

pattern：需要改写的源SQL

pattern_database：需要改写的DB名称

replacement：指定改写后的样子

enabled：是否启用

2.2 改写sql

例如为如下语句加一条hint:

SELECT k, id from sbtest1 where k = ?;

改写成：

select k, id from sbtest1 force index(primary) where k = ?;

为实现该目标，我们可以进行如下操作：

#创建规则

insert into query_rewrite.rewrite_rules(pattern, replacement, pattern_database) values ( "SELECT k, id from sbtest1 where k = ?", "SELECT k, id from sbtest1 force index(primary) where k = ?", "sb1");

mysql> select * from query_rewrite.rewrite_rules\G

*************************** 1. row ***************************

id: 4 pattern: SELECT k, id from sbtest1 where k = ? pattern_database: sb1 replacement: SELECT k, id from sbtest1 force index(primary) where k = ? enabled: YES message: NULL pattern_digest: cb5652eecffae66bdaa4ef31895b6476 normalized_pattern: select `k`,`id` from `sb1`.`sbtest1` where (`k` = ?) 1 row in set (0.00 sec)

然后调用存储过程，使规则生效:

CALL query_rewrite.flush_rewrite_rules();

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

假如报错：

ERROR 1644 (45000): Loading of some rule(s) failed.

SELECT * FROM query_rewrite.rewrite_rules看下message列是否有报错。

 

现在我们执行SQL试试：

mysql> select k, id from sbtest1 where k = 19618;

+-------+------+ | k | id | +-------+------+ | 19618 | 4574 | +-------+------+ 1 row in set, 1 warning (0.02 sec)

mysql> show warnings\G

*************************** 1. row ***************************

Level: Note Code: 1105 Message: Query 'select k, id from sbtest1 where k = 19618' rewritten to 'SELECT k, id from sbtest1 force index(primary) where k = 19618' by a query rewrite plugin 1 row in set (0.00 sec)

 

对于那种SQL隐式转换啦，强制走个索引啦 子查询的优化啦 等等等等 。尤其是系统繁忙来不及修改程序 来不及修改SQL的时候。真的能够救你的系统一命哦。

 

--试验发现：在sqlyog上执行的命令无法重写，用navicat可以，从命令行里也可以。