第 63 章中间件使用踩坑

站长2026-11-11 08:00:00

内容摘要

MySQL 中间件（ProxySQL、ShardingSphere、MyCat 等）是读写分离、分库分表的关键一层，但它本身也会成为新的故障点。本篇基于 MySQL 8.0 生态，梳理中间件在路由、连接、SQL 改写、高可用等环节最容易踩的 11 个坑。前置知识是读写分离与分库分表。学完后你应能判断中间件该怎么配、它会偷偷做什么、以及出问题时怎么定位是中间件还是数据库本身。

📖 本篇简介：MySQL 中间件（ProxySQL、ShardingSphere、MyCat 等）是读写分离、分库分表的关键一层，但它本身也会成为新的故障点。本篇基于 MySQL 8.0 生态，梳理中间件在路由、连接、SQL 改写、高可用等环节最容易踩的 11 个坑。前置知识是读写分离与分库分表。学完后你应能判断中间件该怎么配、它会偷偷做什么、以及出问题时怎么定位是中间件还是数据库本身。

坑 1：中间件自身单点未做高可用
坑 2：读写路由规则写错把写请求打到从库
坑 3：广播表/全量表各分片数据不一致
坑 4：中间件缓存导致读到旧数据
坑 5：长连接被中间件或数据库空闲断开
坑 6：事务内跨分片路由结果漂移
坑 7：中间件 SQL 改写漏处理导致结果错
坑 8：只监控数据库不监控中间件自身
坑 9：中间件版本 Bug 直接上生产
坑 10：连接数未在中间件层收敛
坑 11：预编译语句 Prepare 不被中间件支持
中间件故障定位思路
中间件常见注意事项
速查表：中间件配置关键项
自测练习

坑 1：中间件自身单点未做高可用

场景 / 错误示范：

业务所有读写都经过一台 ProxySQL。这台 ProxySQL 机器重启或进程挂了，全库访问瞬间中断，比数据库本身挂了还严重。

# 错误：只部署了一个中间件实例，前端直连单点
mysql -h 10.0.0.5 -P 6033   # 应用都连这一台 ProxySQL

为什么是坑：中间件是所有数据库访问的必经之路，它一旦挂，后面数据库再健康也没用。把中间件当"无状态组件"只部署一台，等于在数据库集群前面又加了一个更脆弱的单点。高可用架构里，中间件层本身必须先高可用，否则就是本末倒置。

正确做法：

中间件至少部署 2 个以上实例，前端用 VIP/负载均衡把流量分散。

# 正确：部署多台 ProxySQL，前端用 LVS/HAProxy/VIP 负载均衡
# 应用连接的是 VIP，后端挂一台 ProxySQL 不影响整体
mysql -h db-proxy-vip -P 6033

-- ProxySQL 内部也要监控后端心跳，自动摘除异常节点
-- 本示例基于 ProxySQL 管理库
SELECT hostgroup_id, hostname, status FROM mysql_servers;
-- status=ONLINE 才接流量，被摘除的自动转 SHUNNED

加分点：中间件配置建议存入配置中心，多实例共享同一份规则，避免"改了一台漏了另一台"。

坑 2：读写路由规则写错把写请求打到从库

场景 / 错误示范：

ProxySQL 配置读写分离时，规则匹配顺序写错，一条 UPDATE 被路由到了只读从库，从库直接报错 The MySQL server is running with the --read-only option。

-- 错误：规则顺序错误，写请求被误判到读组
-- ProxySQL 规则先匹配到了"所有走读组"的宽泛规则
-- 本示例基于 ProxySQL
INSERT INTO mysql_query_rules(rule_id,active,match_digest,destination_hostgroup)
VALUES (10,1,'^SELECT',10),   -- 读组
       (20,1,'^UPDATE',20);     -- 写组（但优先级被前面宽泛规则盖过）

为什么是坑：中间件路由靠规则匹配，规则有优先级和顺序。如果写规则没匹配上（比如 SQL 前有注释、大小写、多语句），写请求落到只读从库，轻则报错，重则（从库不是 read_only 时）造成主从数据分叉。路由规则必须"写优先、读兜底"，而不是反过来。

正确做法：

写规则精确且优先级高，默认兜底路由到写组，避免漏匹配。

-- 正确：写类语句高优先级精确匹配，未命中的才当读
INSERT INTO mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,cache_ttl)
VALUES
  (5,1,'^SELECT.*FOR UPDATE',20),   -- 写意图，走写组
  (6,1,'^(INSERT|UPDATE|DELETE|REPLACE)',20),  -- 写组，高优先级
  (10,1,'^SELECT',10);              -- 普通读，走读组
-- 默认未命中写规则的请求才落到读组，杜绝写请求误读组

加分点：上线前用真实 SQL 样本批量回放校验路由结果，比逐条人工核对规则可靠。

坑 3：广播表/全量表各分片数据不一致

场景 / 错误示范：

ShardingSphere 把字典表 t_dict 配置成广播表（每个分片全量一份）。更新字典时只更新了一个分片，其他分片还是旧数据，不同请求查到的字典值不一样。

-- 错误：广播表只在一个分片上更新，其他分片未同步
UPDATE t_dict SET dict_value = '新值' WHERE dict_key = 'city';
-- ShardingSphere 未广播这条更新到所有分片

为什么是坑：广播表的价值是"每个分片都有全量数据，可本地 JOIN"。前提是各分片数据强一致。如果更新没有广播到所有分片，就会出现"同一字典表在不同分片数据不同"，JOIN 结果随路由分片变化，极其隐蔽难查。广播表适合"读多写极少"，写必须全分片同步。

正确做法：

广播表更新走中间件的广播路由，或避免频繁写广播表。

-- 正确：广播表的写操作由中间件自动下发到所有分片
-- 在 ShardingSphere 中把 t_dict 配置为 broadcastTable
-- 对它的任何写，中间件会广播执行到每一个分片
-- 并在更新后校验各分片行数一致

-- 校验：在各分片上对比广播表内容是否一致
SELECT dict_key, dict_value FROM t_dict WHERE dict_key = 'city';
-- 应在所有分片返回相同结果

加分点：广播表只放"几乎不变"的字典（行政区划、币种），频繁变更的配置不要放广播表。

坑 4：中间件缓存导致读到旧数据

场景 / 错误示范：

ProxySQL 配了 query cache。用户刚下单，后台却查不到订单，因为缓存里还是旧结果。等了几十秒缓存过期才出现。

-- 错误：对写后立刻读的请求启用了中间件查询缓存
-- ProxySQL 把 SELECT 结果缓存了 60 秒
UPDATE t_order SET status = 1 WHERE id = 99;   -- 刚写
SELECT status FROM t_order WHERE id = 99;      -- 命中缓存，读到旧值 0

为什么是坑：中间件缓存是为了减少重复查询压力，但它不感知"底层数据已变更"。写后立刻读同一行，中间件可能还返回缓存旧值，造成"明明写成功了却读不到"的幻影不一致。缓存对读多写少的静态数据有用，对业务实时写数据是灾难。

正确做法：

业务实时读写不要走中间件缓存，或对关键表禁用缓存。

-- 正确：对实时性要求高的表/语句禁用中间件查询缓存
-- ProxySQL 规则中对这类 SELECT 不设 cache_ttl
INSERT INTO mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,cache_ttl)
VALUES (11,1,'^SELECT.*FROM t_order',10,0);  -- cache_ttl=0 表示不缓存

加分点：读写分离下"写后读"本来就有主从延迟问题，中间件再叠缓存会让不一致时间更长，关键读要走写库或走 GTID 一致性读。

坑 5：长连接被中间件或数据库空闲断开

场景 / 错误示范：

应用通过中间件长连接访问数据库，空闲一段时间后，MySQL 侧 wait_timeout 把连接关了，但中间件还以为连接有效，复用时报 Lost connection during query。

# 错误：应用连接池长期复用一条空闲连接，未做有效性检测
conn = pool.get()       # 这条连接已被 MySQL 因 wait_timeout 关闭
conn.execute("SELECT 1")# 报错：Lost connection to MySQL server

为什么是坑：MySQL 对空闲连接有 wait_timeout 超时清理，中间件自身也有连接复用周期。长连接空闲超过阈值后会被静默关闭，而连接池/中间件不知道，下次复用就报错。这是"偶发、低频、难复现"的典型坑，高峰时集中爆发。

正确做法：

连接池和中间件都配置连接最大存活时间，小于数据库 wait_timeout，并做有效性探测。

-- 数据库侧设置合理的空闲超时（本示例基于 MySQL 8.0）
SET GLOBAL wait_timeout = 28800;       -- 8 小时
SET GLOBAL interactive_timeout = 28800;

-- 中间件/连接池侧：连接存活时间必须小于上面的值，例如 7 小时回收

# 连接池：连接最长存活时间 < wait_timeout，取连接前先 ping
pool = create_pool(
    "db-proxy-vip",
    pool_recycle=7*3600,   # 7 小时回收，小于 8 小时
    pool_pre_ping=True,    # 取连接前探活，失效连接自动剔除
)

加分点：这类"偶发 Lost connection"先去查 wait_timeout 和连接池 recycle 配置，命中率极高。

坑 6：事务内跨分片路由结果漂移

场景 / 错误示范：

一个事务里先按 user_id=10086 查订单（路由到分片 3），再按 order_no 更新（无分片键，被广播到所有分片）。同一个事务里路由不一致，部分语句落到了不同分片。

-- 错误：同一事务内，有的语句带分片键、有的不带，路由到不同分片
BEGIN;
SELECT * FROM t_order WHERE user_id = 10086;   -- 路由到分片 3
UPDATE t_order SET status=1 WHERE order_no='SO001'; -- 无分片键，广播到全部分片
COMMIT;
-- 分片 3 的事务上下文与其他分片对不上，结果不可预期

为什么是坑：中间件的路由是"逐条 SQL"判断的。一个事务内如果每条 SQL 的分片键不一致，中间件可能把它们路由到不同分片，本地事务就失去了"在同一连接同一分片"的前提。分布式事务本就复杂，再叠加路由漂移，结果几乎不可控。

正确做法：

同一事务内所有语句都带相同分片键，确保整个事务落在同一分片。

-- 正确：事务内所有语句都带 user_id，锁定同一分片
BEGIN;
SELECT * FROM t_order WHERE user_id = 10086 AND order_no='SO001';  -- 带分片键
UPDATE t_order SET status=1 WHERE user_id = 10086 AND order_no='SO001'; -- 同分片
COMMIT;   -- 全部落在分片 3，事务上下文一致

加分点：分片设计时让"一个业务实体的所有操作都带同一分片键"，是规避分布式事务和路由漂移的根本。

坑 7：中间件 SQL 改写漏处理导致结果错

场景 / 错误示范：

应用写了 SELECT * FROM t_order ORDER BY create_time DESC LIMIT 10，中间件为了分页做了 SQL 改写，但改写后的语句在分片上执行结果与预期不符。

-- 错误：依赖中间件自动改写复杂 SQL，结果语义被悄悄改变
SELECT * FROM t_order ORDER BY create_time DESC LIMIT 10;
-- 中间件改写后：各分片取 10 条再归并，全局排序可能不全

为什么是坑：中间件为了实现分库分表，会改写 SQL——补分片键、改分页、改聚合。改写越多，越可能改变原语义。比如全局排序+分页，中间件必须"每个分片取够多再全局归并"，一旦实现不当就会少数据或错数据。黑盒式信任中间件改写，是分片后数据错误的隐形来源。

正确做法：

复杂排序分页 SQL 在分片上要显式按分片键约束，避免依赖中间件"猜"。

-- 正确：分片键固定在一个分片内排序分页，不依赖跨片归并
SELECT * FROM t_order
WHERE user_id = 10086          -- 锁定单分片
ORDER BY create_time DESC
LIMIT 10;                       -- 单分片排序，结果正确

加分点：升级中间件版本后，务必用回归 SQL 集验证改写结果，版本升级常带来改写逻辑变化。

坑 8：只监控数据库不监控中间件自身

场景 / 错误示范：

团队对 MySQL 配了完善的监控，但中间件 ProxySQL 的连接数、慢查询、后端摘除情况一概没监控。中间件内存溢出挂了，告警却在数据库侧，排查方向完全错。

-- 错误：只监控后端 MySQL，不监控中间件
-- ProxySQL 的 stats 库里有大量关键指标，却没人采

为什么是坑：中间件是独立的一层，有自己的连接池、内存、规则缓存。它出问题时，表象是"数据库慢/连不上"，但根因在中间件。不监控中间件，故障定位就会在数据库侧空转，浪费宝贵的恢复时间。

正确做法：

把中间件自身指标纳入监控体系。

-- 监控 ProxySQL 的关键指标（本示例基于 ProxySQL stats 库）
-- 1) 各后端节点状态
SELECT hostgroup, hostname, status, ConnUsed, ConnFree FROM stats.stats_mysql_connection_pool;
-- 2) 慢查询/查询次数
SELECT SUM(count_star) FROM stats_mysql_query_digest;
-- 3) 后端被摘除（SHUNNED）情况
SELECT * FROM mysql_servers WHERE status <> 'ONLINE';

加分点：中间件的"路由命中率""后端摘除次数"是比 QPS 更有价值的健康指标。

坑 9：中间件版本 Bug 直接上生产

场景 / 错误示范：

开发图新，直接把最新版 ProxySQL/ShardingSphere 拉上生产，结果遇到已知 Bug——某个特定 SQL 改写导致数据错乱，故障后才发现官方早已修复。

# 错误：不看 release note、不在预发验证，直接上最新版
yum install proxysql  # 装了最新版，未经过任何预发验证

为什么是坑：中间件是高频迭代组件，新版本往往带新 Bug，尤其是 SQL 改写、路由这类核心逻辑。直接上生产等于把生产当测试环境。中间件一出问题是全集群级别的，代价远高于业务应用。

正确做法：

用稳定版 + 预发验证 + 灰度升级。

# 正确：选社区验证过的稳定版本，先在预发跑回归 SQL 集
# 升级路径：预发 -> 1 台生产灰度 -> 观察 -> 全量
# 升级前备份规则配置

-- 升级后用回归 SQL 集校验改写结果是否符合预期
-- 本示例基于 MySQL 8.0
SELECT * FROM t_order WHERE user_id = 10086 ORDER BY id DESC LIMIT 5;
-- 对比升级前后结果，确认路由和改写未变

加分点：中间件大版本升级前，准备好一键回退到旧版本的预案，升级窗口可控。

坑 10：连接数未在中间件层收敛

场景 / 错误示范：

几十个应用实例，每个都建一个数据库连接池（max=50），直连 MySQL。数据库瞬间面临 50×50=2500 个连接，打满 max_connections，新连接被拒。

# 错误：每个应用实例独立连库，连接数线性膨胀
pool_size = 50   # 每个实例 50 连接，50 个实例就是 2500

为什么是坑：MySQL 每个连接都要占内存，连接数是昂贵资源。应用实例一多，各自建池直连，连接数相乘很容易打满数据库 max_connections。中间件的一个重要作用就是"连接收敛"——应用连中间件可以多，中间件连数据库复用少量后端连接，把数据库连接数压下来。

正确做法：

用中间件收敛后端连接，数据库侧连接数可控。

-- 在中间件层配置后端连接池大小，避免直连打满数据库
-- ProxySQL: 每个 hostgroup 的后端连接数统一管理
-- 应用 -> 中间件可以多连接；中间件 -> MySQL 复用少量长连接
-- 本示例基于 MySQL 8.0，数据库 max_connections 据此规划
SET GLOBAL max_connections = 1000;

加分点：中间件"连接收敛"和"读写分离"同等重要，是它在分库之外的另一大核心价值。

坑 11：预编译语句 Prepare 不被中间件支持

场景 / 错误示范：

应用用了预编译 PreparedStatement 提升性能，但中间件对 COM_STMT_PREPARE 支持不完整，参数绑定后查询行为异常或报错。

# 错误：跨中间件使用预编译，中间件未正确转发 prepare 协议
cursor = conn.prepare("SELECT * FROM t_order WHERE user_id = ?")
cursor.execute(10086)   # 中间件转发 prepare 失败或参数绑定错乱

为什么是坑：预编译协议（COM_STMT_PREPARE/EXECUTE）比普通文本协议复杂，很多中间件对它的支持不完整或有 Bug。分片场景下，prepare 的语句要路由到哪个分片、参数怎么绑定，都需要中间件额外处理。贸然依赖预编译，容易遇到中间件支持不全的暗坑。

正确做法：

确认中间件对预编译的支持情况，必要时改用文本协议 + 应用层参数转义。

# 稳妥：使用文本协议查询，参数由驱动正确转义，避免 prepare 协议兼容问题
user_id = 10086
cursor.execute("SELECT * FROM t_order WHERE user_id = %s", (user_id,))
# 驱动负责转义，中间件只需转发文本 SQL，兼容性最好

加分点：如果一定要用预编译，先在目标中间件版本上做完整协议测试，别想当然。

中间件故障定位思路

出问题时第一步要分清"是中间件的问题，还是后端数据库的问题"，否则排查方向会完全错误。以下是一套可复用的定位路径。

| 现象 | 先怀疑中间件 | 先怀疑数据库 |

|---|---|---|

| 偶发连接错误 | 连接池耗尽、后端摘除 | max_connections 打满 |

| 查询突然变慢 | 路由变了、缓存失效 | 慢 SQL、锁等待 |

| 数据查出来不对 | SQL 改写、路由分片错 | 主从延迟、脏读 |

| 大面积报错 | 中间件进程/配置异常 | 单机宕机 |

定位步骤：

1. 先绕开中间件直连后端数据库跑同一条 SQL，对比结果和耗时。

2. 直连正常而走中间件异常，问题锁定在中间件的路由、改写或连接层。

3. 查中间件的慢查询日志和路由记录，看这条 SQL 实际被发往了哪个分片、改写后长什么样。

4. 确认是哪台中间件实例的问题后，先把该实例从负载均衡摘除，再慢慢定位。

这套"直连对照法"是中间件故障排查的第一原则，能快速把怀疑范围砍半。

中间件常见注意事项

中间件是"双刃剑"——它简化了架构，也引入了新的故障维度。以下注意事项帮助你在享受便利的同时控制风险。

| 注意点 | 建议 | 不做的后果 |

|---|---|---|

| 中间件先高可用 | 至少 2 实例 + 前端负载均衡 | 中间件单点=全库中断 |

| 路由规则 | 写优先、读兜底、默认写组 | 写请求误落从库报错 |

| SQL 改写 | 关键 SQL 显式带分片键，不依赖自动改写 | 改写语义变化导致数据错 |

| 中间件监控 | 连接池/后端摘除/慢查询全采 | 故障在 DB 侧空转 |

| 版本升级 | 稳定版 + 预发回归 + 灰度 | 新版本 Bug 全集群故障 |

口诀：中间件先高可用，路由写优先兜底写，改写不黑盒信任，监控要采中间件自身。

速查表：中间件配置关键项

| 配置项 | 错误做法 | 正确做法 | 影响 |

|---|---|---|---|

自测练习

1. 为什么说中间件本身必须先做高可用？

中间件是所有数据库访问的必经路径，它挂了后面数据库再健康也无法访问。单点中间件比数据库单点更脆弱。应至少部署 2 个实例并前端负载均衡。

</details>

2. 读写路由规则为什么要"写优先、读兜底"？

如果读规则宽泛优先匹配，写请求可能误落只读从库，轻则报错重则主从分叉。应把 INSERT/UPDATE/DELETE 等高优先级精确匹配到写组，未命中的才走读组。

</details>

3. 中间件查询缓存在什么场景下会出问题？

"写后立刻读同一数据"时，缓存返回旧值。业务实时读写不应走中间件缓存，关键表需禁用 query cache。

</details>

4. 长连接偶发 Lost connection 通常怎么排查？

先查 MySQL 的 wait_timeout 和连接池 pool_recycle 是否大于它，空闲连接被数据库静默关闭后复用即报错。应让连接池回收时间小于 wait_timeout，并开启 pre_ping 探活。

</details>

5. 中间件"连接收敛"解决什么问题？

多应用实例各自建池直连会让数据库连接数相乘打满 max_connections。中间件把前端多连接收敛为少量后端长连接复用，从而控制数据库侧连接总数。

</details>

下一篇预告：第 64 章监控与告警踩坑

— 全文完 —回到顶部 ↑

下载推广海报

DISCUSSION

文章回复

0 条公开回复

还没有回复，欢迎参与讨论。

第 63 章中间件使用踩坑

目录

坑 1：中间件自身单点未做高可用

坑 2：读写路由规则写错把写请求打到从库

坑 3：广播表/全量表各分片数据不一致

坑 4：中间件缓存导致读到旧数据

坑 5：长连接被中间件或数据库空闲断开

坑 6：事务内跨分片路由结果漂移

坑 7：中间件 SQL 改写漏处理导致结果错

坑 8：只监控数据库不监控中间件自身

坑 9：中间件版本 Bug 直接上生产

坑 10：连接数未在中间件层收敛

坑 11：预编译语句 Prepare 不被中间件支持

中间件故障定位思路

中间件常见注意事项

速查表：中间件配置关键项

自测练习

文章推广海报

文章回复

第 63 章 中间件使用踩坑

目录

坑 1：中间件自身单点未做高可用

坑 2：读写路由规则写错把写请求打到从库

坑 3：广播表/全量表各分片数据不一致

坑 4：中间件缓存导致读到旧数据

坑 5：长连接被中间件或数据库空闲断开

坑 6：事务内跨分片路由结果漂移

坑 7：中间件 SQL 改写漏处理导致结果错

坑 8：只监控数据库不监控中间件自身

坑 9：中间件版本 Bug 直接上生产

坑 10：连接数未在中间件层收敛

坑 11：预编译语句 Prepare 不被中间件支持

中间件故障定位思路

中间件常见注意事项

速查表：中间件配置关键项

自测练习

第 63 章中间件使用踩坑