基于ID字段配置ProxySQL实现MySQL分片遇到的问题

核心配置思路 #

通过ProxySQL的mysql_query_rules和mysql_query_rules_fast_routing实现：

• 奇数ID的读写请求路由至hostgroup 1（shard1）
• 偶数ID的读写请求路由至hostgroup 2（shard2）
• 适配Spring Boot+Hibernate常用的预编译语句（占位符?）

配置步骤 #

1. 进入ProxySQL Admin接口

mysql -u admin -padmin -h 127.0.0.1 -P 6032

2. 插入奇数ID路由规则

-- 匹配显式指定奇数ID的SELECT/UPDATE/DELETE语句
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply)
VALUES (1, 1, 'WHERE\\s+id\\s*=\\s*(\\d*[13579])', 1, 1);

-- 匹配预编译语句中ID为奇数的情况（占位符?）
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply)
VALUES (2, 1, 'WHERE\\s+id\\s*=\\s*\\?', 1, 0);
INSERT INTO mysql_query_rules_fast_routing (rule_id, field, operator, value, destination_hostgroup)
VALUES (2, 'arg1', 'MOD', 2, 1);

-- 匹配显式指定奇数ID的INSERT语句
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply)
VALUES (3, 1, 'INSERT.*VALUES.*\\((.*,)?(\\d*[13579])(,.*)?\\)', 1, 1);

-- 匹配预编译INSERT中ID为奇数的情况（假设ID是第一个占位符）
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply)
VALUES (4, 1, 'INSERT.*VALUES.*\\((.*,)?\\?(,.*)?\\)', 1, 0);
INSERT INTO mysql_query_rules_fast_routing (rule_id, field, operator, value, destination_hostgroup)
VALUES (4, 'arg1', 'MOD', 2, 1);

3. 插入偶数ID路由规则

-- 匹配显式指定偶数ID的SELECT/UPDATE/DELETE语句
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply)
VALUES (5, 1, 'WHERE\\s+id\\s*=\\s*(\\d*[02468])', 2, 1);

-- 匹配预编译语句中ID为偶数的情况（占位符?）
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply)
VALUES (6, 1, 'WHERE\\s+id\\s*=\\s*\\?', 2, 0);
INSERT INTO mysql_query_rules_fast_routing (rule_id, field, operator, value, destination_hostgroup)
VALUES (6, 'arg1', 'MOD', 2, 2);

-- 匹配显式指定偶数ID的INSERT语句
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply)
VALUES (7, 1, 'INSERT.*VALUES.*\\((.*,)?(\\d*[02468])(,.*)?\\)', 2, 1);

-- 匹配预编译INSERT中ID为偶数的情况（假设ID是第一个占位符）
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply)
VALUES (8, 1, 'INSERT.*VALUES.*\\((.*,)?\\?(,.*)?\\)', 2, 0);
INSERT INTO mysql_query_rules_fast_routing (rule_id, field, operator, value, destination_hostgroup)
VALUES (8, 'arg1', 'MOD', 2, 2);

4. 保存并加载配置

-- 持久化配置到磁盘
SAVE MYSQL QUERY RULES TO DISK;
-- 加载配置到运行时生效
LOAD MYSQL QUERY RULES TO RUNTIME;

-- 验证配置是否生效
SELECT rule_id, match_pattern, destination_hostgroup, active FROM mysql_query_rules;
SELECT * FROM mysql_query_rules_fast_routing;

关键注意事项 #

• 占位符序号调整：如果你的INSERT语句中ID不是第一个占位符（比如INSERT INTO t(name, id) VALUES (?, ?)），需要将arg1改为对应位置的argN（比如arg2）。
• 规则匹配顺序：ProxySQL按rule_id从小到大匹配，确保规则顺序不会导致冲突（比如避免模糊匹配优先于精确匹配）。
• 测试验证：通过SELECT * FROM stats_mysql_query_rules查看规则匹配计数，确认请求是否正确路由；开启Hibernate SQL日志，检查语句格式是否符合规则匹配要求。
• 默认路由：你的用户配置中default_hostgroup设为1，未匹配到规则的请求会默认路由到shard1，可根据需求调整。

内容的提问来源于stack exchange，提问作者Abhimanyu