消息表 3 亿行，查询卡到 2 秒：按月分区 + 归档，我把零工系统的消息查询压回毫秒级

消息表 3 亿行查询卡到 2 秒按月分区 归档我把零工系统的消息查询压回毫秒级导读零工系统的站内消息表跑了两年涨到 3 亿行普通查询直接 2 秒起步运营后台翻个消息列表都能把人等急。没上分库分表就靠按月分区 定期归档查询从 2 秒干到 20 毫秒。这篇记录完整过程和踩的坑。先说业务背景。我们日结零工系统里工人的站内消息报名结果、打卡提醒、结算到账、系统公告全部落一张ums_message表一天几百万条。两年过去 3 亿行WHERE user_id? ORDER BY create_time DESC这种高频查询开始变慢EXPLAIN 看是走索引了但索引叶子太多回表量大极限场景 2 秒多。没急着上分库分表——单表 3 亿行在 MySQL 8 里其实还能救关键是把数据切开。消息表天然按时间访问用户只看近期消息按月分区是最合适的方案。第一步按月份 RANGE 分区改造核心是把表改成按月分区每个月一个分区查询时 MySQL 自动只扫相关分区ALTERTABLEums_messagePARTITIONBYRANGECOLUMNS(create_time)(PARTITIONp202401VALUESLESS THAN(2024-02-01),PARTITIONp202402VALUESLESS THAN(2024-03-01),PARTITIONp202403VALUESLESS THAN(2024-04-01),PARTITIONp202404VALUESLESS THAN(2024-05-01),PARTITIONp202405VALUESLESS THAN(2024-06-01),PARTITIONp202406VALUESLESS THAN(2024-07-01),PARTITIONp202407VALUESLESS THAN(2024-08-01),PARTITIONp202408VALUESLESS THAN(2024-09-01),PARTITIONp202409VALUESLESS THAN(2024-10-01),PARTITIONp202410VALUESLESS THAN(2024-11-01),PARTITIONp202411VALUESLESS THAN(2024-12-01),PARTITIONp202412VALUESLESS THAN(2025-01-01),PARTITIONpmaxVALUESLESS THAN MAXVALUE);几个注意点必须用RANGE COLUMNS支持 DATETIME 直接比较别用TO_DAYS那套老写法末尾留一个pmax分区兜底防止新数据进来没分区可放直接报错分区列必须是主键的一部分。原表主键是id得改成(id, create_time)联合主键这个后面细说。第二步查询要能裁剪分区分区不是分完就完查询条件里不带分区列等于白分。用户消息列表的查询长这样SELECTid,title,content,msg_type,is_read,create_timeFROMums_messageWHEREuser_id#{userId}ANDcreate_time#{startTime} -- 分区键进条件触发分区裁剪ORDERBYcreate_timeDESCLIMIT20;配合索引(user_id, create_time)EXPLAIN 里能看到partitions: p202409,p202410这种结果说明只扫了最近两个月的数据。如果 where 里没有 create_timeMySQL 会扫全部分区比没分区还慢。第三步定期归档别让表无限涨分区只解决查询切块不解决磁盘无限涨。每月做一次归档任务把 N 个月前的分区摘下来DETACH 很快元数据操作转成归档表或者干脆删掉-- 摘分区秒级不影响线上写入ALTERTABLEums_messageDETACHPARTITIONp202401;-- 归档表结构复制一份数据挪过去后 drop 掉原分区CREATETABLEums_message_arch_202401LIKEums_message;INSERTINTOums_message_arch_202401SELECT*FROMums_messagePARTITION(p202401);-- 确认无误后删除归档分区释放磁盘ALTERTABLEums_messageDROPPARTITIONp202401;Java 侧用 Quartz 每月 1 号凌晨跑一次先查当前分区列表动态拼 DDLComponentSlf4jpublicclassMessageArchiveJobimplementsJob{Overridepublicvoidexecute(JobExecutionContextcontext){// 归档 6 个月前的分区StringmonthLocalDate.now().minusMonths(6).format(DateTimeFormatter.ofPattern(yyyyMM));StringpartitionNamepmonth;// 校验分区存在再操作防止重复执行报错IntegercntmessageMapper.countPartition(partitionName);if(cntnull||cnt0){log.info(分区不存在跳过归档 partition{},partitionName);return;}messageMapper.archivePartition(partitionName);log.info(消息分区归档完成 partition{},partitionName);}}踩坑记录联合主键差点把线上写挂了问题现象执行 ALTER TABLE 加分区时报错A PRIMARY KEY must include all columns in the tables partitioning function。排查过程MySQL 规定分区列必须包含在所有唯一键里我们的主键是idcreate_time不在里面直接拒绝执行。网上有方案说改成PARTITION BY RANGE (TO_DAYS(create_time))可以绕试了也不行。定位思路只能改主键。但线上表 3 亿行ALTER TABLE ... DROP PRIMARY KEY会锁全表重建业务直接停摆。最终解决先在备库改表结构联合主键(id, create_time)用 pt-osc 这类工具在线变更低峰期切换。改完后加分区一次成功主键查询走id联合索引前缀性能不受影响。从那以后新表设计但凡可能分区主键直接带时间列省得后面再动刀。为什么没上分库分表可能有人问3 亿行直接上 ShardingSphere 不香吗我当时的判断是成本不划算。分库分表要引入中间件、改数据访问层、处理跨库事务和分布式 ID运维复杂度直接上一个台阶而消息这种数据天然按时间归档、热点集中在新数据分区表就能把查询慢这个核心矛盾解决掉改动小、风险低、可回退。取舍标准就一条数据是否有清晰的时间/租户维度、热点是否集中。有分区够用没有才考虑分片。零工消息、日志、站内信这类按月分区就是最优解。顺带说下分区后的索引。分区列进了 where 能裁剪但每个分区内部还是要索引的不然扫一个分区也慢。我们在(user_id, create_time)上建了联合索引消息列表查询走分区裁剪 索引下推两三层下来才算真正快ALTERTABLEums_messageADDINDEXidx_user_time(user_id,create_time);这里不用再单独建create_time索引——联合索引最左前缀已经覆盖了按时间归档的查询多建反而占空间拖写入。可直接抄的清单大表优先考虑按月 RANGE COLUMNS 分区别一上来就分库分表分区列必须进 WHERE否则分区裁剪失效比不分还慢分区列要包含在所有唯一键里新表主键直接带时间列末尾留pmax兜底分区防新数据报错归档用 DETACH/DROP PARTITION秒级操作不影响写入归档任务要幂等先查分区存在再执行。这套方案支撑着零工系统的消息模块也是 qkl-boot 里定时任务的典型用法。相关项目源码https://gitee.com/gzqkl/xllg