mysql的query rewrite插件介绍
参考：
https://sakthismysqlblog.wordpress.com/2020/04/05/mysql-query-rewritten-plugin-now-supporting-delete-insert-update-replace/
https://dev.mysql.com/doc/refman/8.0/en/rewriter-query-rewrite-plugin.html
As of MySQL 8.0.12: SELECT, INSERT, REPLACE, UPDATE, and DELETE.
Prior to MySQL 8.0.12: SELECT only.
假设Rewriter插件已启用，它会检查并可能修改服务器接收到的每个可重写语句。该插件根据其内存中的重写规则缓存来确定是否重写语句，这些重写规则是从数据库中的rewrite_rules表中加载的query_rewrite。
information_schema> show global variables like '%lc%dir';
+-----------------+-----------------------------------+
| Variable_name | Value |
+-----------------+-----------------------------------+
| lc_messages_dir | /usr/local/GreatSQL-8.0.25/share/ |
+-----------------+-----------------------------------+
1 row in set
Time: 0.011s
# root @ centos7-3 in /usr/local/mysql [23:51:52]
$ ./bin/mysql -S /data/GreatSQL/mysql.sock test -A < share/install_rewriter.sql
information_schema> SHOW GLOBAL VARIABLES LIKE 'rewriter_enabled';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| rewriter_enabled | ON |
+------------------+-------+
information_schema> select * from `PLUGINS` where `PLUGIN_NAME` like '%rewr%' \G
***************************[ 1. row ]***************************
PLUGIN_NAME | Rewriter
PLUGIN_VERSION | 0.2
PLUGIN_STATUS | ACTIVE
PLUGIN_TYPE | AUDIT
PLUGIN_TYPE_VERSION | 4.1
PLUGIN_LIBRARY | rewriter.so
PLUGIN_LIBRARY_VERSION | 1.10
PLUGIN_AUTHOR | Oracle Corporation
PLUGIN_DESCRIPTION | A query rewrite plugin that rewrites queries using the parse tree.
PLUGIN_LICENSE | GPL
LOAD_OPTION | ON
information_schema> select * from routines where routine_schema like '%rewr%' \G
***************************[ 1. row ]***************************
SPECIFIC_NAME | flush_rewrite_rules
ROUTINE_CATALOG | def
ROUTINE_SCHEMA | query_rewrite
ROUTINE_NAME | flush_rewrite_rules
ROUTINE_TYPE | PROCEDURE
DATA_TYPE |
CHARACTER_MAXIMUM_LENGTH | <null>
CHARACTER_OCTET_LENGTH | <null>
NUMERIC_PRECISION | <null>
NUMERIC_SCALE | <null>
DATETIME_PRECISION | <null>
CHARACTER_SET_NAME | <null>
COLLATION_NAME | <null>
DTD_IDENTIFIER | <null>
ROUTINE_BODY | SQL
ROUTINE_DEFINITION | BEGIN
DECLARE message_text VARCHAR(100);
COMMIT;
SELECT load_rewrite_rules() INTO message_text;
IF NOT message_text IS NULL THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = message_text;
END IF;
END
EXTERNAL_NAME | <null>
EXTERNAL_LANGUAGE | SQL
PARAMETER_STYLE | SQL
IS_DETERMINISTIC | NO
SQL_DATA_ACCESS | CONTAINS SQL
SQL_PATH | <null>
SECURITY_TYPE | DEFINER
CREATED | 2022-04-22 23:51:56
LAST_ALTERED | 2022-04-22 23:51:56
SQL_MODE |
ROUTINE_COMMENT |
DEFINER | root@localhost
CHARACTER_SET_CLIENT | utf8mb4
COLLATION_CONNECTION | utf8mb4_0900_ai_ci
DATABASE_COLLATION | utf8mb4_0900_ai_ci
1 row in set
配置文件加下
[mysqld]
rewriter_enabled=ON
information_schema> use query_rewrite
You are now connected to database "query_rewrite" as user "dts"
Time: 0.001s
query_rewrite> show tables;
+-------------------------+
| Tables_in_query_rewrite |
+-------------------------+
| rewrite_rules |
+-------------------------+
1 row in set
query_rewrite> show create table rewrite_rules \G
***************************[ 1. row ]***************************
Table | rewrite_rules
Create Table | CREATE TABLE `rewrite_rules` (
`id` int NOT NULL AUTO_INCREMENT,
`pattern` varchar(5000) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
`pattern_database` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
`replacement` varchar(5000) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
`enabled` enum('YES','NO') CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT 'YES',
`message` varchar(1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
`pattern_digest` varchar(64) DEFAULT NULL,
`normalized_pattern` varchar(100) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set
INSERT INTO query_rewrite.rewrite_rules (pattern, replacement) VALUES('SELECT ?', 'SELECT ? + 1');
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
query_rewrite> call query_rewrite.flush_rewrite_rules();
Query OK, 1 row affected
query_rewrite> select 1;
+-----+
| 1+1 |
+-----+
| 2 |
+-----+
query_rewrite> select 199+1;
+-------+
| 199+1 |
+-------+
| 200 |
+-------+
[query_rewrite]> truncate table rewrite_rules ;
[query_rewrite]> insert into rewrite_rules(pattern,replacement,enabled) values ('select * from sbtest.sbtest1 where upper(k)=?','select * from sbtest.sbtest1 where k=?','YES');
[query_rewrite]> call query_rewrite.flush_rewrite_rules();
[query_rewrite]> use sbtest
Database changed
[sbtest]> select * from sbtest.sbtest1 where upper(k)='86078' ;
+-------+-------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
| id | k | c | pad |
+-------+-------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
| 6 | 86078 | 18043372364-75289283623-05417774399-20182187171-84841253748-73006659624-88721543079-70037577746-96449883172-05854255645 | 59078476541-85948796048-00845389059-29962879743-91675763913 |
| 21012 | 86078 | 50848351671-48415491230-33106215789-79149544427-58524716632-61772985665-26453060334-73428647300-93208687962-03848926913 | 21942235826-84355050326-82841896720-42782358137-92732468397 |
+-------+-------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)
[sbtest]> show warnings;
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------+
| Note | 1105 | Query 'select * from sbtest1 where upper(k)='86078'' rewritten to 'select * from sbtest1 where k='86078'' by a query rewrite plugin |
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
[sbtest]> select * from sbtest.sbtest1 where k='86078' ;
+-------+-------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
| id | k | c | pad |
+-------+-------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
| 6 | 86078 | 18043372364-75289283623-05417774399-20182187171-84841253748-73006659624-88721543079-70037577746-96449883172-05854255645 | 59078476541-85948796048-00845389059-29962879743-91675763913 |
| 21012 | 86078 | 50848351671-48415491230-33106215789-79149544427-58524716632-61772985665-26453060334-73428647300-93208687962-03848926913 | 21942235826-84355050326-82841896720-42782358137-92732468397 |
+-------+-------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
2 rows in set (0.00 sec)
[sbtest]> show warnings;
Empty set (0.00 sec)
此外， update、delete ，在8.0.12开始也支持 rewrite了。
这种在 某些sql异常，但是不方便发版改代码时候，只能在数据库或者代理（proxysql这类）上做改写。
官方的例子：
https://dev.mysql.com/doc/refman/8.0/en/rewriter-query-rewrite-plugin-usage.html
INSERT INTO query_rewrite.rewrite_rules (pattern, replacement)
VALUES('DELETE FROM db1.t1 WHERE col = ?',
'UPDATE db1.t1 SET col = NULL WHERE col = ?');
CALL query_rewrite.flush_rewrite_rules();
[test]> select * from query_rewrite.rewrite_rules \G
*************************** 3. row ***************************
id: 12
pattern: DELETE FROM db1.t1 WHERE col = ?
pattern_database: NULL
replacement: UPDATE db1.t1 SET col = NULL WHERE col = ?
enabled: YES
message: NULL
pattern_digest: e246a61c325ea344469f22417cd746bf14e816dd61c004b9054cd989a7d60bd6
normalized_pattern: delete from `db1`.`t1` where (`col` = ?)下面再来一个复杂的sql的rewrite，sql不是重点，主要是看多个变量情况下的rewrite的写法
下面这个sql来自生产环境，做了脱敏和表结构的精简
SELECT DISTINCT
phone_no,
COUNT(phone_no) phoneNoCount,
SUM(amount) AS amountSum
FROM
test.tb_111
WHERE 1 = 1
AND record_date >= '2022-04-25 00:00:00'
AND record_date <= '2022-06-25 00:00:00'
AND income = 13
AND phone_no NOT IN (
'5829202798403814789' -- 实际上这里可能会有N多个数值
)
GROUP BY phone_no
ORDER BY amountSum DESC
LIMIT 10;
-- 建测试表
CREATE TABLE `tb_111` (
`id` bigint NOT NULL COMMENT 'id',
`phone_no` bigint(13) NOT NULL COMMENT '手机号',
`amount` decimal(10,2) NOT NULL COMMENT '金额',
`income` tinyint NOT NULL COMMENT '标识',
`record_date` date NOT NULL COMMENT '记录日期',
PRIMARY KEY (`id`) USING BTREE,
KEY `idx_phone_no` (`phone_no`)
KEY `idx_record_date` (`record_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- 生成100w条随机数据
$ ./mysql_random_data_load test tb_111 1000000 --user=dts --password=dts --port=3316 --max-threads=4 --host=192.168.31.181
INFO[2022-04-25T00:49:46+08:00] Starting
INFO[2022-04-25T00:52:35+08:00] 1000000 rows inserted
2m33s [====================================================================] 100%
-- 随机抽一条数据看下效果
[test]> select * from tb_111 order by rand() limit 1 ;
+---------------------+--------------------+--------+--------+-------------+
| id | phone_no | amount | income | record_date |
+---------------------+--------------------+--------+--------+-------------+
| 6274283659384047887 | 200296964576606303 | 0.00 | 6 | 2022-04-25 |
+---------------------+--------------------+--------+--------+-------------+
1 row in set (0.08 sec)
-- 执行下目标sql，记录下结果
SELECT DISTINCT
phone_no,
COUNT(phone_no) phoneNoCount,
SUM(amount) AS amountSum
FROM
test.tb_111
WHERE 1 = 1
AND record_date >= '2022-04-25 00:00:00'
AND record_date <= '2022-06-25 00:00:00'
AND income = 13
AND phone_no NOT IN (
'5829202798403814789'
)
GROUP BY phone_no
ORDER BY amountSum DESC
LIMIT 10;
+---------------------+--------------+-----------+
| phone_no | phoneNoCount | amountSum |
+---------------------+--------------+-----------+
| 8191268767504556227 | 1 | 7.00 |
| 7030342559149458373 | 1 | 7.00 |
| 2759503958775420570 | 1 | 7.00 |
| 6455414206327618647 | 1 | 7.00 |
| 2534162369479104831 | 1 | 7.00 |
| 6088848362750791550 | 1 | 7.00 |
| 6674954236962923750 | 1 | 7.00 |
| 5333469094946989218 | 1 | 6.99 |
| 3998286462748783400 | 1 | 6.99 |
| 3400081772821665332 | 1 | 6.99 |
+---------------------+--------------+-----------+
10 rows in set (2.22 sec)
并看下对应的执行计划：
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tb_111
partitions: NULL
type: range
possible_keys: idx_record_date
key: idx_record_date
key_len: 3
ref: NULL
rows: 561541
filtered: 9.00
Extra: Using index condition; Using where; Using temporary; Using filesort
1 row in set, 1 warning (0.00 sec)
我们下面加个hint看下
SELECT DISTINCT
phone_no,
COUNT(phone_no) phoneNoCount,
SUM(amount) AS amountSum
FROM
test.tb_111 force index(idx_record_date)
WHERE 1 = 1
AND record_date >= '2022-04-25 00:00:00'
AND record_date <= '2022-06-25 00:00:00'
AND income = 13
AND phone_no NOT IN (
'5829202798403814789'
)
GROUP BY phone_no
ORDER BY amountSum DESC
LIMIT 10;
+---------------------+--------------+-----------+
| phone_no | phoneNoCount | amountSum |
+---------------------+--------------+-----------+
| 6088848362750791550 | 1 | 7.00 |
| 2534162369479104831 | 1 | 7.00 |
| 8191268767504556227 | 1 | 7.00 |
| 2759503958775420570 | 1 | 7.00 |
| 7030342559149458373 | 1 | 7.00 |
| 6455414206327618647 | 1 | 7.00 |
| 6674954236962923750 | 1 | 7.00 |
| 3998286462748783400 | 1 | 6.99 |
| 8547138990913837269 | 1 | 6.99 |
| 5938981549642367853 | 1 | 6.99 |
+---------------------+--------------+-----------+
10 rows in set (1.72 sec)
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tb_111
partitions: NULL
type: range
possible_keys: idx_record_date
key: idx_record_date
key_len: 3
ref: NULL
rows: 561541
filtered: 9.00
Extra: Using index condition; Using where; Using temporary; Using filesort
1 row in set, 1 warning (0.00 sec)
-- 下面开始使用 query write 插件进行sql改下
use query_rewrite;
TRUNCATE TABLE rewrite_rules;
insert into rewrite_rules (pattern,replacement,enabled) values (
'SELECT DISTINCT phone_no,COUNT(phone_no) phoneNoCount,SUM(amount) AS amountSum FROM test.tb_111 where 1=1 AND record_date >= ? AND record_date <= ? AND income = ? AND phone_no NOT IN (?) GROUP BY phone_no ORDER BY amountSum DESC LIMIT 10',
'SELECT DISTINCT phone_no,COUNT(phone_no) phoneNoCount,SUM(amount) AS amountSum FROM test.tb_111 force index(idx_record_date) where 1=1 AND record_date >= ? AND record_date <=? AND income = ? AND phone_no NOT IN (?) GROUP BY phone_no ORDER BY amountSum DESC LIMIT 10',
'YES'
);
call query_rewrite.flush_rewrite_rules();
select * from query_rewrite.rewrite_rules \G
*************************** 1. row ***************************
id: 1
pattern: SELECT DISTINCT phone_no,COUNT(phone_no) phoneNoCount,SUM(amount) AS amountSum FROM test.tb_111 where 1=1 AND record_date >= ? AND record_date <= ? AND income = ? AND phone_no NOT IN (?) GROUP BY phone_no ORDER BY amountSum DESC LIMIT 10
pattern_database: NULL
replacement: SELECT DISTINCT phone_no,COUNT(phone_no) phoneNoCount,SUM(amount) AS amountSum FROM test.tb_111 force index(idx_record_date) where 1=1 AND record_date >= ? AND record_date <=? AND income = ? AND phone_no NOT IN (?) GROUP BY phone_no ORDER BY amountSum DESC LIMIT 10
enabled: YES
message: NULL
pattern_digest: 61823cba6c3150ecc5710c14fa50a7df43f7f80f929fda5ae44d1520f9fadc53
normalized_pattern: select distinct `phone_no`,count(`phone_no`),sum(`amount`) from `test`.`tb_111` where ((? = ?) and (
1 row in set (0.00 sec)
[test]> SELECT DISTINCT
phone_no,
COUNT(phone_no) phoneNoCount,
SUM(amount) AS amountSum
FROM
test.tb_111
WHERE 1 = 1
AND record_date >= '2022-04-25 00:00:00'
AND record_date <= '2022-06-25 00:00:00'
AND income = 13
AND phone_no NOT IN (
'5829202798403814789'
)
GROUP BY phone_no
ORDER BY amountSum DESC
LIMIT 10;
+---------------------+--------------+-----------+
| phone_no | phoneNoCount | amountSum |
+---------------------+--------------+-----------+
| 6088848362750791550 | 1 | 7.00 |
| 2534162369479104831 | 1 | 7.00 |
| 8191268767504556227 | 1 | 7.00 |
| 2759503958775420570 | 1 | 7.00 |
| 7030342559149458373 | 1 | 7.00 |
| 6455414206327618647 | 1 | 7.00 |
| 6674954236962923750 | 1 | 7.00 |
| 3998286462748783400 | 1 | 6.99 |
| 8547138990913837269 | 1 | 6.99 |
| 5938981549642367853 | 1 | 6.99 |
+---------------------+--------------+-----------+
10 rows in set, 1 warning (1.72 sec)
[test]> show warnings \G
*************************** 1. row ***************************
Level: Note
Code: 1105
Message: Query 'SELECT DISTINCT
phone_no,
COUNT(phone_no) phoneNoCount,
SUM(amount) AS amountSum
FROM
test.tb_111
WHERE 1 = 1
AND record_date >= '2022-04-25 00:00:00'
AND record_date <= '2022-06-25 00:00:00'
AND income = 13
AND phone_no NOT IN (
'5829202798403814789'
)
GROUP BY phone_no
ORDER BY amountSum DESC
LIMIT 10' rewritten to 'SELECT DISTINCT phone_no,COUNT(phone_no) phoneNoCount,SUM(amount) AS amountSum FROM test.tb_111 force index(idx_record_date) where 1=1 AND record_date >= '2022-04
1 row in set (0.00 sec)
注意，如果我们对于上面的sql稍微改动下（例如 phone_no 条件增加一个值，即 phone_no NOT IN ( '5829202798403814789','5111103814789' ) 或者将LIMIT 10 改为 LIMIT 10,10 这样就无法匹配到rewrite rules了。
如下示例：
[test]> SELECT DISTINCT
phone_no,
COUNT(phone_no) phoneNoCount,
SUM(amount) AS amountSum
FROM
tb_111
WHERE 1 = 1
AND record_date >= '2022-04-25 00:00:00'
AND record_date <= '2022-06-25 00:00:00'
AND income = 13
AND phone_no NOT IN (
'5829202798403814789',
'5111103814789'
)
GROUP BY phone_no
ORDER BY amountSum DESC
LIMIT 10;
+---------------------+--------------+-----------+
| phone_no | phoneNoCount | amountSum |
+---------------------+--------------+-----------+
| 8191268767504556227 | 1 | 7.00 |
| 7030342559149458373 | 1 | 7.00 |
| 2759503958775420570 | 1 | 7.00 |
| 6455414206327618647 | 1 | 7.00 |
| 2534162369479104831 | 1 | 7.00 |
| 6088848362750791550 | 1 | 7.00 |
| 6674954236962923750 | 1 | 7.00 |
| 5333469094946989218 | 1 | 6.99 |
| 3998286462748783400 | 1 | 6.99 |
| 3400081772821665332 | 1 | 6.99 |
+---------------------+--------------+-----------+
10 rows in set (2.25 sec) ----> 这里不再出现 1 warning的提醒，也就是没有进行查询改写因此，从最后的案例可以看到实际生产局限性还是很大的 【例如mybatis中的动态传参，传入的phone_no的个数可能很多种情况。 sql中的条件也可能有很多种组合。 这样一条rewrite rule 根本无法去满足实际的需求】，只能用在少数SQL基本固定场景下。
本文参与 腾讯云自媒体分享计划，分享自作者个人站点/博客。
原始发表：2022-04-25，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读
相关产品与服务
云数据库 SQL Server
腾讯云数据库 SQL Server （TencentDB for SQL Server）是业界最常用的商用数据库之一，对基于 Windows 架构的应用程序具有完美的支持。TencentDB for SQL Server 拥有微软正版授权，可持续为用户提供最新的功能，避免未授权使用软件的风险。具有即开即用、稳定可靠、安全运行、弹性扩缩等特点。