1. 为什么要重写 SQL 语句
ProxySQL 在收到前端发送来的 SQL 语句后, 可以根据已定制的规则去匹配它, 匹配到了还可以去重写这个语句, 然后再路由到后端去.
什么时候需要重写 SQL 语句?
对于下面这种简单的读, 写分离, 当然用不上重写 SQL 语句.
这样的读写分离, 实现起来非常简单. 如下:
- mysql_replication_hostgroups:
- +------------------+------------------+----------+
- | writer_hostgroup | reader_hostgroup | comment |
- +------------------+------------------+----------+
- | 10 | 20 | cluster1 |
- +------------------+------------------+----------+
- mysql_servers:
- +--------------+----------+------+--------+--------+
- | hostgroup_id | hostname | port | status | weight |
- +--------------+----------+------+--------+--------+
- | 10 | master | 3306 | ONLINE | 1 |
- | 20 | slave1 | 3306 | ONLINE | 1 |
- | 20 | slave2 | 3306 | ONLINE | 1 |
- +--------------+----------+------+--------+--------+
- mysql_query_rules:
- +---------+-----------------------+----------------------+
- | rule_id | destination_hostgroup | match_digest |
- +---------+-----------------------+----------------------+
- | 1 | 10 | ^SELECT.*FOR UPDATE$ |
- | 2 | 20 | ^SELECT |
- +---------+-----------------------+----------------------+
但是, 复杂一点的, 例如 ProxySQL 实现 sharding 功能. 对 db1 库的 select_1 语句路由给 hg=10 的组, 将 db2 库的 select_2 语句路由给 hg=20 的组, 将 db3 库的 select_3 语句路由给 hg=30 的组.
在 ProxySQL 实现 sharding 时, 基本上都需要将 SQL 语句进行重写. 这里用一个简单的例子来说明分库是如何进行的.
假如, 计算机学院 it_db 占用一个数据库, 里面有一张学生表 stu,stu 表中有代表专业的字段 zhuanye(例子只是随便举的, 请无视合理性).
it_db 库: stu 表
- +---------+----------+---------+
- | stu_id | stu_name | zhuanye |
- +---------+----------+---------+
- | 1-99 | ... | Linux |
- +---------+----------+---------+
- | 100-150 | ... | MySQL |
- +---------+----------+---------+
- | 151-250 | ... | JAVA |
- +---------+----------+---------+
- | 251-550 | ... | Python |
- +---------+----------+---------+
分库时, 可以为各个专业创建库. 于是, 创建 4 个库, 每个库中仍保留 stu 表, 但只保留和库名对应的学生数据:
Linux 库: stu 表
- +---------+----------+---------+
- | stu_id | stu_name | zhuanye |
- +---------+----------+---------+
- | 1-99 | ... | Linux |
- +---------+----------+---------+
MySQL 库: stu 表
- +---------+----------+---------+
- | stu_id | stu_name | zhuanye |
- +---------+----------+---------+
- | 100-150 | ... | MySQL |
- +---------+----------+---------+
JAVA 库: stu 表
- +---------+----------+---------+
- | stu_id | stu_name | zhuanye |
- +---------+----------+---------+
- | 151-250 | ... | JAVA |
- +---------+----------+---------+
Python 库: stu 表
- +---------+----------+---------+
- | stu_id | stu_name | zhuanye |
- +---------+----------+---------+
- | 251-550 | ... | Python |
- +---------+----------+---------+
于是, 原来查询 MySQL 专业学生的 SQL 语句:
select * from it_db.stu where zhuanye='MySQL' and xxx;
分库后, 该 SQL 语句需要重写为:
select * from MySQL.stu where 1=1 and xxx;
至于如何达到上述目标, 本文结尾给出了一个参考规则.
sharding 而重写只是一种情况, 在很多使用复杂 ProxySQL 路由规则时可能都需要重写 SQL 语句. 下面将简单介绍 ProxySQL 的语句重写, 为后文做个铺垫, 在之后介绍 ProxySQL + sharding 的文章中有更多具体的用法.
2.SQL 语句重写
在 mysql_query_rules 表中有 match_pattern 字段和 replace_pattern 字段, 前者是匹配 SQL 语句的正则表达式, 后者是匹配成功后(命中规则), 将原 SQL 语句改写, 改写后再路由给后端.
需要注意几点:
如果不设置 replace_pattern 字段, 则不会重写.
要重写 SQL 语句, 必须使用 match_pattern 的方式做正则匹配, 不能使用 match_digest. 因为 match_digest 是对参数化后的语句进行匹配.
ProxySQL 支持两种正则引擎: RE2 和 PCRE, 默认使用的引擎是 PCRE. 这两个引擎默认都设置了 caseless 修饰符(re_modifiers 字段), 表示匹配时忽略大小写. 还可以设置其它修饰符, 如 global 修饰符, global 修饰符主要用于 SQL 语句重写, 表示全局替换, 而非首次替换.
因为 SQL 语句千变万化, 在写正则语句的时候, 一定要注意 "贪婪匹配" 和 "非贪婪匹配" 的问题.
stats_mysql_query_digest 表中的 digest_text 字段显示了替换后的语句. 也就是真正路由出去的语句.
本文的替换规则出于入门的目的, 很简单, 只需掌握最基本的正则知识即可. 但想要灵活运用, 需要掌握 PCRE 的正则, 如果您已有正则的基础, 可参考我的一篇总结性文章: pcre 和正则表达式的误点.
例如, 将下面的语句 1 重写为语句 2.
- select * from test1.t1;
- select * from test1.t2;
插入如下规则:
- delete from mysql_query_rules;
- select * from stats_mysql_query_digest_reset where 1=0;
- insert into mysql_query_rules(rule_id,active,match_pattern,replace_pattern,destination_hostgroup,apply)
- values (1,1,"^(select.*from )test1.t1(.*)","\1test1.t2\2",20,1);
- load mysql query rules to runtime;
- save mysql query rules to disk;
- select rule_id,destination_hostgroup,match_pattern,replace_pattern from mysql_query_rules;
- +---------+-----------------------+------------------------------+-----------------+
- | rule_id | destination_hostgroup | match_pattern | replace_pattern |
- +---------+-----------------------+------------------------------+-----------------+
- | 1 | 20 | ^(select.*from )test1.t1(.*) | \1test1.t2\2 |
- +---------+-----------------------+------------------------------+-----------------+
然后执行:
- $ proc="mysql -uroot -pP@ssword1! -h127.0.0.1 -P6033 -e"
- $ $proc "select * from test1.t1;"
- +------------------+
- | name |
- +------------------+
- | test1_t2_malong1 |
- | test1_t2_malong2 |
- | test1_t2_malong3 |
- +------------------+
可见语句成功重写.
再看看规则的状态.
- Admin> select rule_id,hits from stats_mysql_query_rules;
- +---------+------+
- | rule_id | hits |
- +---------+------+
- | 1 | 1 |
- | 2 | 0 |
- +---------+------+
- Admin> select hostgroup,count_star,digest_text from stats_mysql_query_digest;
- +-----------+------------+------------------------+
- | hostgroup | count_star | digest_text |
- +-----------+------------+------------------------+
| 20 | 1 | select * from test1.t2 | <-- 已替换
+-----------+------------+------------------------+
更简单的, 还可以直接替换单词. 例如:
- delete from mysql_query_rules;
- select * from stats_mysql_query_digest_reset where 1=0;
- insert into mysql_query_rules(rule_id,active,match_pattern,replace_pattern,destination_hostgroup,apply)
- values (1,1,"test1.t1","test1.t2",20,1);
- load mysql query rules to runtime;
- save mysql query rules to disk;
- select rule_id,destination_hostgroup,match_pattern,replace_pattern from mysql_query_rules;
- +---------+-----------------------+---------------+-----------------+
- | rule_id | destination_hostgroup | match_pattern | replace_pattern |
- +---------+-----------------------+---------------+-----------------+
- | 1 | 20 | test1.t1 | test1.t2 |
- +---------+-----------------------+---------------+-----------------+
3.sharding: 重写分库 SQL 语句
以本文前面 sharding 示例中的语句为例, 简单演示下 sharding 时的分库语句怎么改写. 更完整的 sharding 实现方法, 见后面的文章.
- # 原来查询 MySQL 专业学生的 SQL 语句:
- select * from it_db.stu where zhuanye='MySQL' and xxx;
- |
- |
- |
- \|/
- # 改写为查询分库 MySQL 的 SQL 语句:
- select * from MySQL.stu where 1=1 and xxx;
以下是完整语句: 关于这个规则中的正则部分, 稍后会解释.
- delete from mysql_query_rules;
- select * from stats_mysql_query_digest_reset where 1=0;
- insert into mysql_query_rules(rule_id,active,apply,destination_hostgroup,match_pattern,replace_pattern)
- values (1,1,1,20,"^(select.*?from) it_db\.(.*?) where zhuanye=['""](.*?)['""] (.*)$","\1 \3.\2 where 1=1 \4");
- load mysql query rules to runtime;
- save mysql query rules to disk;
- select rule_id,destination_hostgroup dest_hg,match_pattern,replace_pattern from mysql_query_rules;
- +---------+---------+-----------------------------------------------------------------+-----------------------+
- | rule_id | dest_hg | match_pattern | replace_pattern |
- +---------+---------+-----------------------------------------------------------------+-----------------------+
- | 1 | 20 | ^(select.*?from) it_db\.(.*?) where zhuanye=['"](.*?)['"] (.*)$ | \1 \3.\2 where 1=1 \4 |
- +---------+---------+-----------------------------------------------------------------+-----------------------+
然后执行分库查询语句:
- proc="mysql -uroot -pP@ssword1! -h127.0.0.1 -P6033 -e"
- $proc "select * from it_db.stu where zhuanye='MySQL'and 1=1;"
看看是否命中规则, 并成功改写 SQL 语句:
- Admin> select rule_id,hits from stats_mysql_query_rules;
- +---------+------+
- | rule_id | hits |
- +---------+------+
- | 1 | 1 |
- +---------+------+
- Admin> select hostgroup,count_star,digest_text from stats_mysql_query_digest;
- +-----------+------------+-------------------------------------------+
- | hostgroup | count_star | digest_text |
- +-----------+------------+-------------------------------------------+
- | 20 | 1 | select * from MySQL.stu where ?=? and ?=? |
- | 10 | 1 | select @@version_comment limit ? |
- +-----------+------------+-------------------------------------------+
解释下前面的规则:
- match_pattern:
- -
- "^(select.*?from) it_db\.(.*?) where zhuanye=['""](.*?)['""] (.*)$"
- replace_pattern:
- -
- "\1 \3.\2 where 1=1 \4"
^(select.*?from): 表示不贪婪匹配到 from 字符. 之所以不贪婪匹配, 是为了避免子查询或 join 子句出现多个 from 的情况.
it_db\.(.*?): 这里的 it_db 是稍后要替换掉为 "MySQL" 字符的部分, 而 it_db 后面的表稍后要附加在 "MySQL" 字符后, 所以对其分组捕获.
- zhuanye=['""](.*?)['""]
- :
- 这里的 zhuanye 字段稍后是要删除的, 但后面的字段值 "MySQL" 需要保留作为稍后的分库, 因此对字段值分组捕获. 同时, 字段值前后的引号可能是单引号, 双引号, 所以两种情况都要考虑到.
- ['""]: 要把引号保留下来, 需要对额外的引号进行转义: 双引号转义后成单个双引号. 所以, 真正插入到表中的结果是['"].
- 这里的语句并不健壮, 因为如果是 zhuanye='MySQL" 这样单双引号混用也能被匹配. 如果要避免这种问题, 需要使用 PCRE 的反向引用. 例如, 改写为:
zhuanye=(['""])(.*?)\g[N]
, 这里的 [N] 要替换为 (['""]) 对应的分组号码, 例如 \ g3.
(.*)$: 匹配到结束. 因为这里的测试语句简单, 没有 join 和子查询什么的, 所以直接匹配.
"\1 \3.\2 where 1=1 \4"
: 这里加了 1=1, 是为了防止出现 and/or 等运算符时前面缺少表达式. 例如(.*)$ 捕获到的内容为 and xxx=1, 不加上 1=1 的话, 将替换为 where and xxx=1, 这是错误的语句, 所以 1=1 是个占位表达式.
可见, 要想实现一些复杂的匹配目标, 正则表达式是非常繁琐的. 所以, 很有必要去掌握 PCRE 正则表达式.
来源: https://www.cnblogs.com/f-ck-need-u/p/9309760.html