MySQL Rewriter Query Rewrite Plugin

简单的说一下，就是查询重写的插件，和Oracle 物化视图的查询重写还不太一样

Server version: 5.7.19-17-log Percona Server (GPL), Release 17, Revision e19a6b7b73f
OS version: Red Hat Enterprise Linux Server release 6.3 (Santiago)

安装

1. 安装路径
mysql 目录下是 share 目录下，有个install_rewriter.sql 
2. 安装
mysql> source install_rewriter.sql
Query OK, 1 row affected (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.04 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

3. 检查 是否启用
mysql> SHOW GLOBAL VARIABLES LIKE 'rewriter_enabled';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| rewriter_enabled | ON    |
+------------------+-------+
1 row in set (0.01 sec)

4. 配置文件中增加以下这段
[mysqld]
rewriter_enabled=ON

5. 在线启用
SET GLOBAL rewriter_enabled = ON;
SET GLOBAL rewriter_enabled = OFF;

测试下

1. 新增规则
mysql>   INSERT INTO query_rewrite.rewrite_rules (pattern, replacement) VALUES('SELECT ?', 'SELECT ? + 1');
Query OK, 1 row affected (0.00 sec)

2. 检查规则状态
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: NULL
normalized_pattern: NULL
1 row in set (0.01 sec)

3. 必须执行 flush_rewrite_rules() 才会把上面的规则加载到 plugin in-memory cache:
mysql> CALL query_rewrite.flush_rewrite_rules();
Query OK, 0 rows affected (0.00 sec)

4. 执行 flush_rewrite_rules()后，检查规则状态，可见pattern_digest 和 normalized_pattern 已更新，应该是通过 pattern 计算得来的
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: 512e888765927f4393e9f938df43b1ec
normalized_pattern: select ?
1 row in set (0.00 sec)

官方这个解释。。也是够够的了。。。
pattern_digest
This column is used for debugging and diagnostics. If the column exists when the rules table is loaded into memory, the plugin updates it with the pattern digest. This column may be useful if you are trying to determine why some statement fails to be rewritten.

normalized_pattern
This column is used for debugging and diagnostics. If the column exists when the rules table is loaded into memory, the plugin updates it with the normalized form of the pattern. This column may be useful if you are trying to determine why some statement fails to be rewritten.

测试
mysql> SELECT PI();
+----------+
| PI()     |
+----------+
| 3.141593 |
+----------+
1 row in set (0.00 sec)

mysql> SELECT 10;
+--------+
| 10 + 1 |
+--------+
|     11 |
+--------+
1 row in set, 1 warning (0.00 sec)

mysql> SELECT 11;
+--------+
| 11 + 1 |
+--------+
|     12 |
+--------+
1 row in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
  Level: Note
   Code: 1105
Message: Query 'SELECT 11' rewritten to 'SELECT 11 + 1' by a query rewrite plugin
1 row in set (0.00 sec)


关闭规则
UPDATE query_rewrite.rewrite_rules SET enabled = 'NO' WHERE id = 1;
CALL query_rewrite.flush_rewrite_rules();

以下enabled 变为NO
mysql> SELECT * FROM query_rewrite.rewrite_rules\G
*************************** 1. row ***************************
                id: 1
           pattern: SELECT ?
  pattern_database: NULL
       replacement: SELECT ? + 1
           enabled: NO
           message: NULL
    pattern_digest: 512e888765927f4393e9f938df43b1ec
normalized_pattern: select ?
1 row in set (0.00 sec)

mysql> SELECT 11;
+----+
| 11 |
+----+
| 11 |
+----+
1 row in set (0.00 sec)

启用规则
UPDATE query_rewrite.rewrite_rules SET enabled = 'YES' WHERE id = 1;
CALL query_rewrite.flush_rewrite_rules();

SQL 与查询重新中规则的 匹配规则：

1. 先计算sql 的hash值，然后和 rewrite_rules 表中的比较，这种方法很快，但是不是很准，所以这种方法也是有风险的
2. 如果sql 的hash 值匹配多条 rewrite_rules 中的值了，就通过规范化的sql 匹配，即rewrite_rules 表中normalized_pattern
3. 如果是通过规范化的sql 匹配，rules 里面的 问号，也要匹配上

规则
SELECT ?, 3  
是按照如下表格匹配的。。

Prepared Statement	Whether Pattern Matches Statement
PREPARE s AS 'SELECT 3, 3'	Yes
PREPARE s AS 'SELECT ?, 3'	Yes
PREPARE s AS 'SELECT 3, ?'	No
PREPARE s AS 'SELECT ?, ?'	No

所以。。碰上那种不靠谱的项目组、索引走的不对、生产又要急用的情况下，临时救个急还是可以的。。