query_rewrite 插件功能
- MySQL 5.7 版本新特性
- 作用是把輸入的一種語句改寫成另外一種模式
- 優(yōu)勢是業(yè)務(wù)無法及時調(diào)整情況下通過MySQL將接收到的語句改寫衙熔，如強制使用索引九孩、子查詢優(yōu)化等
安裝插件
- 支持社區(qū)版
- 官方自帶腳本安裝
# 安裝腳本位于MySQL的share目錄，腳本集成了安裝插件阱扬，建相應(yīng)庫表操作
mysql -S /tmp/mysql_sandbox5725.sock -uroot -proot < /opt/sandboxes/base/5.7.25/share/install_rewriter.sql
# 對應(yīng)卸載使用腳本uninstall_rewriter.sql
- 安裝完成將自動創(chuàng)建query_rewrite庫rewrite_rules表
show create table query_rewrite.rewrite_rules\G
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 | 規(guī)則id 泣懊，此列是表的主鍵。 |
| pattern | 需要改寫的源SQL |
| pattern_database | 需要改寫的DB名稱 |
| replacement | 指定改寫后的樣子 |
| enabled | 改寫是否啟用 |
插件使用
啟用與關(guān)閉
- rewriter_enabled參數(shù)可全局關(guān)閉/開啟 query_rewrite功能
改寫語句
# 直接插入要改寫的語句麻惶、改寫后的以及對應(yīng)業(yè)務(wù)庫即可
INSERT INTO query_rewrite.rewrite_rules
(pattern, replacement, pattern_database) VALUES(
'SELECT * FROM users WHERE id = ?',
'SELECT * FROM users WHERE user_id = ?',
'appdb'
);
或或?qū)憥焘晒危砬凹訉僦?INSERT INTO query_rewrite.rewrite_rules
(pattern, replacement) VALUES(
'SELECT * FROM appdb.users WHERE id = ?',
'SELECT * FROM appdb.users WHERE user_id = ?'
);
# 調(diào)用存儲過程是讓插入的新規(guī)則生效
CALL query_rewrite.flush_rewrite_rules();
舉例說明
# 希望select不加where條件的語句默認(rèn)都帶上 limit 1的效果
mysql> select * from t_user;
+----+---------+------+
| id | name | age |
+----+---------+------+
| 1 | Michael | 18 |
| 2 | Jane | 20 |
| 3 | Lucy | 17 |
| 4 | Antony | 35 |
+----+---------+------+
# 插入改寫規(guī)則
INSERT INTO query_rewrite.rewrite_rules
(pattern, replacement, pattern_database) VALUES(
'SELECT * FROM t_user',
'SELECT * FROM t_user limit 1',
'test'
);
# 調(diào)用存儲過程使規(guī)則生效
CALL query_rewrite.flush_rewrite_rules();
# 效果
mysql> select * from t_user;
+----+---------+------+
| id | name | age |
+----+---------+------+
| 1 | Michael | 18 |
+----+---------+------+
1 row in set, 1 warning (0.00 sec)
參考
https://dev.mysql.com/doc/refman/5.7/en/rewriter-query-rewrite-plugin.html
