MES系统数据库读写分离：主从复制+分库分表实战踩坑

【摘要】上一篇把应用层从320 QPS压到12800之后，瓶颈完整地转移到了MySQL：主库CPU长期90%，质量追溯查询40秒超时。这篇记录我做读写分离和分表的全过程，重点是三个真实踩坑：报工完立刻查看板看不到数据的读己之写问题、夜间归档删除200万行把主从延迟拖到47分钟、以及分片键选错导致追溯查询比不分表还慢的返工经历。附查询耗时量化对比表与完整路由代码。

栏目：MES高并发架构优化 | 发布日期：2026-08-15 | 环境：MySQL 8.0.32 / 一主两从 / ThinkPHP 6.1 / 工单表2100万行 / 报工明细日增40万行

一、问题背景：应用层优化完了，锅甩给了数据库

上一篇我写了怎么把ThinkPHP6的接口吞吐从320 QPS推到12800。但那篇文章结尾我留了个尾巴：应用层不再是瓶颈之后，压力一分不少地压到了MySQL身上。说实话这个结果我早就预料到了，只是没想到来得这么快、这么难看。

当时的数据库是单实例MySQL 8.0，8核32G加SSD，跑着我们整个MES。几个关键的表规模是这样的：工单表两千一百万行、报工明细表日增约四十万行、设备参数采集表日增一千二百万行（这张表最凶，但它是独立库）。症状有四个。第一，主库CPU长期在85%到95%之间，凌晨归档时段直接顶满。第二，慢查询日志一天能滚出六百多兆，里面最多的是看板的当班产出汇总，一次三千四百毫秒，而看板是每十五秒自动刷新一次的。第三，质量追溯这个功能基本上不能用了：质检要查一个批次从投料到出货的全部报工记录，跨十二个月的数据，一次查询四十一秒，前端nginx三十秒就超时了，同事只能来找我手动跑SQL导Excel。第四，也是最影响生产的，报工写入开始出现锁等待，偶发地能等到两秒以上，PDA那边就是一个转圈。

我先做的当然是常规动作：把慢查询全捞出来，逐个explain，补了七个索引、改写了四个SQL、砍掉两个select星号。这一轮做完确实有效果，慢查询数量降了六成。但我很快意识到这条路的天花板在哪：看板汇总这类查询本质上要扫几十万行做聚合，索引优化能让它从三千四百毫秒降到一千八，但降不到两百。而且真正的矛盾不是单条SQL慢，而是读和写在抢同一份资源：看板的大范围聚合查询会占满IO和buffer pool，导致报工写入的事务提交变慢；反过来写入产生的锁又会让查询等待。这两类负载的特征完全冲突，一个要吞吐、一个要延迟，硬挤在一个实例里怎么调参都是拆东墙补西墙。

所以方向就清楚了：先做读写分离，把读负载从主库剥离出去；再做分表，把单表体量降下来。整个改造前后花了差不多三个月，中间踩了三个不小的坑，其中分片键那个坑让我返工了一次，白干了三周。下面按顺序讲。

二、技术原理：主从复制的延迟到底从哪来

2.1 复制链路的四个环节

要理解主从延迟，必须先搞清楚一条数据从主库到从库要走哪几步。主库上事务提交时把变更写入binlog；从库的IO线程连上主库，通过一个binlog dump线程把binlog事件拉过来写入本地的relay log；从库的SQL线程再读relay log把这些事件回放执行。这条链路上任何一个环节慢了，都表现为延迟。

实践中我遇到的延迟，九成来自最后那个回放环节。原因是MySQL传统的复制里SQL线程是单线程的：主库上可以有几十个连接并发写入，但从库只有一个线程按binlog顺序串行回放。主库并发写的能力天然大于从库串行放的能力，一旦写入量超过某个阈值，延迟就会开始累积并且不会自己恢复。MySQL 5.7之后提供了并行复制，8.0默认的并行策略是基于WRITESET的，它通过计算每个事务修改的行的哈希来判断事务之间有没有冲突，没有冲突的事务就可以在从库上并行回放。把slave_parallel_workers调到8之后，我们从库的回放能力大概提升了五到六倍，这是解决延迟性价比最高的一个参数。

第二类延迟来源是大事务。binlog是按事务为单位传输和回放的，一个删除两百万行的事务在从库上必须完整执行完才算结束，这期间后面所有事务都在排队，而且并行复制对单个大事务完全无能为力。这就是我们踩的第二个坑的根因，后面细说。第三类是从库自身被大查询拖住：如果你把报表查询也放在同一个从库上，一个扫描千万行的聚合查询会把buffer pool打脏、把IO占满，SQL线程回放就跟着变慢。所以从库也要按用途隔离，不能什么都往上放。

2.2 读写分离最难的不是路由，是一致性

读写分离本身的实现并不复杂。ThinkPHP6在配置层就支持，把deploy设为1、rw_separate设为true，主机列表里第一个当主库、其余当从库，框架会自动把写走主、读走从。中间件方案比如ProxySQL、MyCat能做得更精细，可以按SQL特征路由、支持连接复用和故障自动切换，但会多一跳网络和一个需要运维的组件。我们的选择是应用层路由，理由很实在：团队只有我一个人能搞得定数据库这块，再引入一个中间件意味着多一个我半夜要爬起来处理的东西。

真正的难点是一致性，具体说就是「读己之写」。MySQL的异步复制不保证从库和主库实时一致，主库提交后从库可能一两秒才追上。这在很多业务里无所谓，但在MES里有些场景是致命的：工人在PDA上报完工，界面立刻跳转到该工序的进度页，这个查询如果走从库，读到的就是报工之前的旧数据，工人会以为报工失败，然后再报一次。这就是我们的第一个坑，也是上线首日就爆的。

解决读己之写有三条路。最简单的是关键接口直接强制走主库，缺点是这类接口一多，读写分离就白做了。第二条是会话标记法：一次写操作之后，在Redis里给这个会话打一个几秒钟的标记，标记期内该会话的读全部走主库，标记过期后恢复走从库。这个方案的好处是只影响刚写过数据的那个人，其他人的读依然分流到从库，对整体分流率的影响很小，我们实测被强制走主库的读请求只占总读量的3.7%。第三条最精确，是GTID等待：写事务提交后拿到它的GTID，读从库之前先调用WAIT_FOR_EXECUTED_GTID_SET等这个GTID被从库执行完，带超时保护。它的语义最严谨，代价是要多一次等待，我们只在报工确认、工单开关这几个强一致接口上用。

2.3 分片键的选择决定了分表是不是白做

分表的关键从来不是怎么把表拆开，而是分片键怎么选。分片键必须满足一个条件：你的主要查询模式里都带着它。如果查询条件里没有分片键，数据库就不知道该去哪张表找，只能把所有分片都扫一遍再合并，这时候你不但没有获得分表的收益，还额外付出了多次查询和结果归并的成本，比不分表更慢。这句话是我用三周的返工换来的，后面第三节我会把过程写清楚。

图1：纵轴为对数刻度。收益最大的是批次质量追溯，从41秒降到1.35秒，主要来自分片键改造后不再盲扫全部月表；写入耗时只小幅改善，因为写仍然只走主库。

三、实战案例：三个坑，一次三周的返工

3.1 坑一：报工完立刻查，看板上没有

读写分离上线是个周三凌晨，一主两从，配置改完压测数据很漂亮，主库CPU从90%掉到46%，看板查询从三千四降到六百多毫秒。我睡了个好觉。结果早上八点四十分，车间班长打电话说工人反映报工后进度不更新，等一会儿刷新才有。

这就是典型的读己之写问题，我在设计时完全没考虑到。我上服务器量了一下当时的Seconds_Behind_Master，早班高峰是1.8秒左右，也就是说报工提交后1.8秒内查从库都是旧数据。而PDA的交互是提交成功后立即跳转查询，间隔不到200毫秒，百分百读到旧值。

当天上午我先用最粗暴的办法止血：把工单进度查询接口整个强制走主库，先把生产恢复。下午开始做正式方案，也就是前面讲的会话标记法，写操作后打三秒标记，标记内该会话读主库。三秒这个值不是拍的，我统计了一周的延迟分布，TP99是1.9秒、TP999是2.7秒，取三秒能覆盖住绝大多数情况。另外对报工确认这个最关键的接口，我额外加了GTID等待，超时设800毫秒，超时了就降级读主库，宁可主库多扛一点也不能给错数据。这套方案上线后再没收到过类似反馈。

3.2 坑二：一条归档SQL把主从延迟拖到47分钟

第二个坑更隐蔽，因为它只在凌晨发生，白天完全看不见。读写分离跑了两周后，我偶然在早上翻监控，发现Seconds_Behind_Master在凌晨两点有个巨大的尖峰，最高到2820秒，也就是四十七分钟，而且一直到早上八点还有九十多秒的残余延迟没消化完。这意味着早班接班时看板上的数据是一分半钟前的，难怪之前有人跟我说过「早上的数字对不上」，我当时以为是他看错了。

根因很快就找到了：我们有个凌晨两点执行的归档作业，把三个月前的报工明细搬到归档库，然后一条delete把原表数据删掉，一次删两百多万行。这条delete在主库上跑了六分钟就完事了，因为主库有并发能力、有充足的buffer pool，但它在binlog里是一个巨大的单一事务，从库的SQL线程必须串行地把两百万行的删除全部回放完，并行复制在这里毫无用处，因为它就是一个事务。从库硬件还比主库差一档（16G内存），回放就跑了四十多分钟。

改造分两步。第一步是把大事务拆小：归档删除改成循环分批，每批两千行，每批之间sleep一百毫秒，用主键区间做定位而不是用时间条件全表扫。改完之后单批binlog很小，从库能跟着并行回放，整个归档从六分钟变成十八分钟，主库耗时是增加了，但从库延迟峰值从2820秒降到210秒。这是个非常值得的交换：归档慢十二分钟没人在意，但从库延迟四十七分钟会让看板数据整个上午都不可信。

第二步是开启并行复制，把slave_parallel_workers设成8、slave_parallel_type设为LOGICAL_CLOCK、binlog_transaction_dependency_tracking设为WRITESET。这三个参数配合下来，延迟峰值又从210秒降到21秒，全天绝大部分时间都在个位数秒级。顺便提一个细节：开启并行复制后从库的relay log应用是乱序的，所以Seconds_Behind_Master这个指标的准确性会下降，我们后来改用心跳表来量延迟——主库上有个定时任务每秒更新一行时间戳，从库读这行和当前时间比差值，这个数字比Seconds_Behind_Master可信得多。

3.3 坑三：按工单号Hash分表，追溯查询反而更慢了

这是我在这个项目里犯的最大的错误，值得完整复盘。读写分离解决了读写争抢，但报工明细表的体量还在涨，一年一亿四千万行，单表索引的B+树层高上去之后，每次查询的随机IO次数明显增加。所以要分表。

当时我几乎没怎么犹豫就选了工单号做分片键，理由听起来很充分：工单号分布均匀、Hash之后数据能平摊到十六张表、写入没有热点，而且MES里最常见的查询就是「按工单查报工记录」。我花了三周做改造，包括分表中间层、数据迁移脚本、双写验证，上线之后按工单号查确实快了，从四十六毫秒降到十一毫秒。

问题是质量追溯。质检的查询模式是「给我批次L2608A0231从投料到出货的所有记录」，条件是批次号加时间范围，压根没有工单号——因为一个批次在流转过程中会拆分、合批，对应的是十几个甚至几十个不同的工单。于是这个查询在分表后必须把十六张表全部扫一遍，每张表内部再按批次号索引查，最后在应用层归并排序。实测下来从原来的四十一秒变成了五十八秒，比不分表还慢了四成。我拿着这个数字在会上被问得说不出话，那个下午挺难受的。

返工方案是把分片键换成时间。报工明细天生带时间属性，而且几乎所有查询都会带时间范围条件——看板查当班、报表查当月、追溯查一个批次的生命周期区间。我们改成按月分表，表名是wo_report加年月后缀，一个月一张表大概一千二百万行，在可控范围内。针对追溯这种不知道具体月份的场景，我额外建了一张很小的路由表lot_month_route，记录每个批次号出现在哪几个月，这张表只有批次号和年月两列，一年也就几百万行，全内存命中。追溯查询先查路由表拿到月份列表，通常一个批次的生命周期只跨一到两个月，于是只需要扫一到两张月表，查询时间降到1350毫秒。

这次返工给我的教训写在了团队文档第一条：选分片键之前，先把最近三个月生产环境里执行次数最多的二十条查询和耗时最长的二十条查询列出来，看候选分片键在这四十条查询的where条件里出现的比例，低于八成就不要选它。我当初如果做了这个动作，五分钟就能发现工单号不合适，而不是花三周做完才发现。

3.4 后续：垂直分库与全局ID

月表跑稳之后我们又做了一层垂直拆分，按厂区把库拆开，两个厂区各一套主从，跨厂区的集团报表在应用层聚合。这一步主要是为了故障隔离，一个厂区的数据库出问题不至于让另一个厂区停产。拆库之后主键不能再用自增了，我们换成了雪花算法生成的全局ID，这里又踩了个小坑：有台机器做NTP校时的时候时钟回拨了两秒，雪花算法直接抛异常导致那两秒的写入全失败。后来我们改成检测到回拨时不抛异常，而是在同一毫秒内借用序列号位继续发号，同时打告警日志让运维去查时钟源，这样业务不中断，问题也不会被掩盖。

图2：改造前凌晨两点的归档作业把延迟顶到2820秒（47分钟），早班八点接班时还有96秒残余延迟，看板数据肉眼可见地滞后；两项改造叠加后全天延迟稳定在个位数秒级。

四、完整代码：读写分离配置与月表路由