MySQL百万数据统计优化：Redis预计算实战，从27秒到250毫秒

百万数据统计在 MySQL 里能慢到什么程度这次我是真真切切体会到了。当时我手头维护的是一个电商后台系统里面有一张订单明细表负责给运营看板提供数据。最开始表里也就三四十万行统计接口基本秒开。可等量级爬到两百多万行之后运营反馈那个“经营看板”页面打开一次要半分钟左右页面里的数字迟迟不加载第一批点进去的同事甚至以为系统卡死了。那段时间我每天都要收到好几条“统计怎么又超时”的消息。刚开始我也以为是网络问题后来打开慢查询日志才确认锅全在几条统计 SQL 上。当时用 MySQL Redis 做百万数据统计优化的完整过程包括最初的故障定位、方案选型、核心改造代码以及那些不实际踩一遍根本想不到的坑我都记在这篇里了。如果你正在为某个统计接口从几秒变成几十秒头疼或者你已经在考虑引入大数据组件但不知道值不值得这篇内容应该能给你一个比较有参考价值的解法。1. 事故现场一个统计接口从 1 秒变成 27 秒1.1 业务场景与 SQL 现场这个后台看板需要统计的指标并不复杂今日订单总数、今日销售额支付成功、待付款、退款中、已关闭各状态订单数今日客单价、昨日同环比近 7 天订单量趋势全部来自一张order_info表。表结构也不是什么特别夸张的大宽表核心字段大概长这样CREATE TABLE order_info ( id bigint NOT NULL AUTO_INCREMENT, order_no varchar(32) NOT NULL COMMENT 订单号, user_id bigint NOT NULL COMMENT 用户ID, status tinyint NOT NULL COMMENT 订单状态1待付款 2已支付 3已发货 4已完成 5退款中 6已关闭, channel tinyint NOT NULL COMMENT 下单渠道 1小程序 2APP 3线下, amount decimal(12,2) NOT NULL COMMENT 订单金额, pay_time datetime DEFAULT NULL COMMENT 支付时间, create_time datetime NOT NULL COMMENT 下单时间, PRIMARY KEY (id), KEY idx_status (status), KEY idx_create_time (create_time), KEY idx_user_id (user_id) ) ENGINEInnoDB;原本的统计实现非常“直接”这几个指标全是实时查表算出来的。看板加载时主接口会并行执行好几条类似的 SQLSELECT COUNT(*) AS order_count, COALESCE(SUM(amount), 0) AS total_amount FROM order_info WHERE create_time 2025-03-01 00:00:00 AND create_time 2025-03-02 00:00:00; SELECT status, COUNT(*) AS cnt FROM order_info WHERE create_time 2025-03-01 00:00:00 AND create_time 2025-03-02 00:00:00 GROUP BY status;单独看任何一条 SQL 都不算重问题是它们全挤在一个页面上。表量级到了 200 万行以后配合上运营筛选渠道、时间范围等条件单条 SQL 就开始出现 4~8 秒的耗时多条并行再加上页面轮询接口直接把慢查询日志刷屏了。1.2 索引为什么救不了统计场景刚开始团队的第一反应也是加索引。给create_time、status、channel都建了单列索引后来又试过联合索引(create_time, status, channel)。效果确实有但非常有限。单天数据量在十几万行的时候即使走create_time索引MySQL 也需要把这些行全部读出来再聚合。因为统计查询本质上是把某个时间段内所有满足条件的行“扫”一遍而不是精确找某几行。这里有个容易忽略的问题普通索引只能帮你缩小扫描范围没法帮你省略计算。你要的是 SUM、COUNT、GROUP BY 的结果数据库必须访问所有命中的行。除非你要查的是一个有前缀匹配能力的单条记录否则索引只是让扫描从 200 万行变成 20 万行计算成本依然存在。EXPLAIN 的结果也证明了这一点核心查询走的是idx_create_time索引type 为 rangerows 估算 20 万左右Extra 里出现Using temporary; Using filesort。20 万行不算多但如果每天看板要被几十个人开几十次再加上好几次分组统计并行执行数据库 CPU 很快就飙上去了。那段排查经历给我的最大启发是在“频繁多维聚合”这个场景里与其不断优化 MySQL 的“即时计算”能力不如想办法把计算提前到数据写入那一刻。看板需要的永远是一个结果数字而不是中间那 20 万行明细。2. 为什么没有直接换库三个方案对比与取舍2.1 常规解法盘点当时团队内部其实提过好几个方向除了在 MySQL 上做 SQL 优化还有重一点的方案。我简单列一下当时对比过的三条路。第一种继续在 MySQL 里做文章。比如增加汇总表、分区表、物化视图。MySQL 8 的物化视图不算内建只能靠定时任务手动刷。分区表对这类跨天统计帮助也存在局限如果今天不分区按create_time查历史数据依然要扫全表。最后我们判断这条路只适合小步优化解决不了根子上的“每次现算”问题。第二种引入 OLAP 引擎。例如把订单明细实时同步到分析型数据库看板查询全部走分析引擎。这类引擎在亿级数据量下也能秒出结果能力确实强。但代价是至少要新增一套组件还要处理明细数据同步链路原本 MySQL 主从、备份、监控那套运维体系全要往外延伸一圈。我们当时数据量才 200 万专职负责这个系统的人手也有限上重组件有点杀鸡用牛刀。第三种就是这次实施的 MySQL Redis 组合优化方案。核心思路是把统计结果“事前算好”然后让 Redis 扛实时读MySQL 只负责存明细和做离线兜底。这个方案不引入任何新存储能复用现有 Redis 集群研发成本相对可控。2.2 三个方案的横向对比方案开发量运维成本实时性数据量适用段主要风险SQL 优化索引小无新增秒级百万边缘无法根治现算开销引入 OLAP 引擎中高秒级千万到亿级组件运维与同步延迟MySQLRedis 预计算中低秒级百万到千万Redis 数据一致性对比完就很清晰了200 万行这个量级既没有到必须用分析型数据库的程度也不能继续纵容 MySQL 反复全表聚合。用 Redis 在业务写入时同步累加计数再用一张预聚合表承接跨天趋势属于“刚刚好”的复杂度。2.3 别忽略一个前提统计口径固定这套方案能成立的前提是看板上的指标要相对固定不能是那种“用户随意选字段、自由组合维度”的分析页面。一旦维度组合爆炸Redis 里的 key 数量也会爆炸预聚合表更没法提前算。我们当时的看板恰好是固定指标只是时间范围可选就很适合这么干。所以做任何技术选型之前先把业务场景框清楚。这看起来像废话但真到了项目里很多人一上来就谈组件、谈数据量反而把“为什么需要统计、统计项会不会变”给放一边了。结果往往是把一个轻量业务搬上重型架构后期维护成本远比想象中高。3. 核心改造Redis 计数器、预聚合表与查询路由三层结构3.1 先按指标特性分类把所有统计指标拆成三类决定它们各自的存储位置实时累加类今日订单数、今日销售额、支付成功数、退款数。这类指标本质上是一个不断增长的计数器最适合用 Redis 的 INCR、HINCRBY 处理。分组分布类各状态订单占比、各渠道销量。需要按维度分组Redis 的 Hash 可以做到 O(1) 更新单个分组也可以按分数排序用 ZSet。跨天趋势类近 7 天订单趋势、近 30 天销售额曲线。Redis 里不适合长期存这么多天数据放到 MySQL 预聚合表里更合适定时任务每小时汇总一次即可。这个分类非常关键。分类错了后面代码全是坑。比如你把趋势类指标也塞进 Redis不设过期时间的话内存会一路涨到让你怀疑人生。3.2 Redis 键设计与写入代码键的命名我统一设计成stat:{业务}:{指标}:{yyyyMMdd}。例如stat:order:total:20250301 stat:order:status:20250301 stat:order:channel:20250301 stat:order:amount:20250301其中status和channel用 Hash 结构field 是具体状态值/渠道值value 是订单数。amount用 String 累加销售额。核心更新代码大概是这样的// 订单创建成功时同步累加当日统计 String day LocalDate.now().format(DateTimeFormatter.BASIC_ISO_DATE); String totalKey stat:order:total: day; String statusKey stat:order:status: day; String channelKey stat:order:channel: day; String amountKey stat:order:amount: day; // 同一批 Redis 操作在事务/管道里执行减少网络开销 stringRedisTemplate.executePipelined((RedisCallbackObject) connection - { connection.incr(totalKey.getBytes()); connection.hIncrBy(statusKey.getBytes(), String.valueOf(order.getStatus()).getBytes(), 1); connection.hIncrBy(channelKey.getBytes(), String.valueOf(order.getChannel()).getBytes(), 1); connection.incrBy(amountKey.getBytes(), order.getAmount().movePointRight(2).longValue()); return null; });这里有两个容易忽略的细节。第一金额我转成了整数再累加避免浮点误差第二一次订单创建涉及多个 key 更新我放在同一个 pipeline 里执行把多次网络交互合并成一次实际在高并发下对 Redis 的压力会小很多。客单价这种派生指标不需要单独存查询时用当日销售额 / 当日订单数算出来即可。因为销售额和订单数都在 Redis 里两个读操作一起取出来一次接口请求也就多几十微秒。3.3 MySQL 预聚合表承接历史趋势为了近 30 天趋势和跨天对比我加了一张简单的汇总表CREATE TABLE order_stat_hourly ( stat_date date NOT NULL COMMENT 统计日期, stat_hour tinyint NOT NULL COMMENT 统计小时 0-23, status tinyint NOT NULL COMMENT 订单状态, order_count int NOT NULL DEFAULT 0 COMMENT 订单数量, total_amount decimal(14,2) NOT NULL DEFAULT 0 COMMENT 订单金额, PRIMARY KEY (stat_date, stat_hour, status) ) ENGINEInnoDB COMMENT订单小时汇总表;这张表由一个定时任务维护。任务每 10 分钟跑一次拉取最近一个小时内新增/变更的订单按stat_date, stat_hour, status分组后做增量累加。注意我没有用“每天凌晨全量重刷”的方式因为当天数据还在变化等到凌晨再刷就来不及了。增量同步的 SQL 大致逻辑是INSERT INTO order_stat_hourly (stat_date, stat_hour, status, order_count, total_amount) SELECT DATE(create_time), HOUR(create_time), status, COUNT(*), SUM(amount) FROM order_info WHERE create_time ? AND create_time ? GROUP BY DATE(create_time), HOUR(create_time), status ON DUPLICATE KEY UPDATE order_count VALUES(order_count), total_amount VALUES(total_amount);这里有个教训不能直接对整个小时段反复跑同一段 SQL。如果同一小时的数据因为补单、退款等原因发生变更重复执行会把计数加重复。我当时给汇总任务加了一个“按订单update_time 上次执行时间”的增量过滤条件保证一条订单在一个小时窗口内只被统计一次。具体实现上还会配合一个本地任务执行状态表避免定时任务重复触发。补充一句这张小时表默认只保留最近 90 天。更早的数据如果业务上还需要再按天汇总一层归档完全没必要在明细表上做历史全量统计。3.4 查询路由三层顺序怎么定改造后的查询逻辑很直接接口入口处做了一个路由判断查“今日/今日实时”类指标直接读 Redis不存在才回源查“近 7 天趋势、昨日对比”类指标读 MySQL 预聚合表一次查出多天小时数据后在应用层做二次聚合只有兜底场景才查询明细表且查询结果设置短缓存。这个顺序的核心理念是能查结果就不查明细能用预聚合就不用实时算。Redis 的价值是让当天数据读取变成 O(1) 内存操作MySQL 预聚合表的价值是让历史趋势查询只面对一张“每天都只有几十行”的小表。我举个例子你就懂了。改造后查“近 7 天订单量趋势”对应的 SQL 是这样的SELECT stat_date, SUM(order_count) AS total FROM order_stat_hourly WHERE stat_date ? AND stat_date ? GROUP BY stat_date;这张表全量撑死也就 90 天 × 24 小时 × 几个状态几千行而已。哪怕用最笨的全表扫描耗时都在几毫秒级别。和之前动辄扫描几十万订单明细完全是两个量级。4. 改造过程中最折磨人的三个坑4.1 事务边界与 Redis 不一致定时对账兜底第一个坑来自架构上绕不开的问题MySQL 事务和 Redis 操作无法保证原子性。业务代码里如果在一个事务方法中先写订单表再更新 Redis一旦 Redis 操作超时或失败订单已经入库但计数器没加上当天的统计数就会越差越多。这个问题在理论上有很多优雅解法比如事务消息、本地消息表、binlog 订阅后异步消费。但当时线上系统已经比较老改造面太大。我退而求其次做了两层补偿业务上将 Redis 更新放在事务提交之后执行这样至少不会把“没用的事务”也算进计数新增一个每天凌晨执行的“统计对账任务”用预聚合表里前一天的结果和 Redis 中的统计值做差值校验超过阈值就自动重建对应时间的统计值。这个方案不完美胜在简单可控。对账任务会打印差异明细运营那边也接受“偶尔延迟修正但不会一直错下去”的口径。如果你所在业务对准确性要求非常苛刻我还是建议老老实实上事务消息或消费 binlog别学我这个偷懒方案。4.2 回填风暴Redis 重启之后别让 MySQL 裸奔第二个坑是在压测阶段爆出来的。Redis 集群做过一次主从切换重启之后内存里空空如也看板页面刚好被运营开着一瞬间所有请求全部打到 MySQL 上把主库 CPU 直接跑满。这个现象就是经典的缓存穿透。我们原本给每个统计 key 设置了当日过期时间比如expireAt 当天 23:59:59但根本没有为“Redis 重启后数据怎么回来”设计预案。后来加了两个机制。第一是启动预热应用启动时按“热 key 优先”原则把昨日和今日的统计 key 用异步任务逐批回填逐批的意思是一批只回填 100 个 key不一次性全量干。第二是查询互斥读 Redis 发现没有值时只让一个线程负责回源查 MySQL其余请求短暂等待或返回上次成功值防止同一时间内大量线程同时回源。另外为了不让 key 在同一时刻集体过期造成集中穿透过期时间我也加上了随机扰动。比如基础 TTL 是 1 小时实际 TTL 是基础时间 random(0, 300)秒。别小看这五分钟的随机性它能让回源压力在时间轴上摊平。4.3 Redis 内存无界增长按日期滚动删除第三个坑比较隐蔽是在上线一周后看 Redis 内存监控时发现的。统计 key 如果只加不过期短时间内没什么问题时间长了会变成内存黑洞。比如按天统计的 key 如果永久保留一年就是 365 组每组包含多个 Hash field日积月累非常可观。我当时用了一个很土但有效的办法只保留近 3 天的实时统计 key历史数据一律以 MySQL 预聚合表为准。每天凌晨对账任务结束后把昨天之前所有stat:*前缀的 key 全部删除。为了避免删除大 key 阻塞 Redis用的是 SCAN 命令分批遍历删除而不是 KEYS 全量遍历。# 伪代码扫描删除过期统计 key SCAN 0 MATCH stat:*:20250228 COUNT 1000 # 循环分批 DEL另外一个兜底措施是给 Redis 配置了maxmemory-policy allkeys-lru万一内存不够最不常用的统计 key 会被优先淘汰不至于把整个缓存打挂。对了删除历史 key 也要注意时机。我一开始放在对账任务的最前面执行结果刚删完统计 key 又有查询进来触发了回源把数据库打出一阵小高峰。后来把删除动作放到对账任务完全结束之后再执行就平稳多了。5. 最终收益与这套打法的适用边界5.1 性能数据对比改造上线一周后我从监控系统里拉了一组对比数据指标改造前改造后经营看板首屏加载耗时27 秒180~250 毫秒数据库主库 CPU 使用率峰值 92%峰值低于 25%Redis 新增内存占用0约 45MB3 天数据日订单量支持上限表越大越卡平稳支撑无感知这组数据给我最大的触动不是“快了 100 倍”而是数据库彻底从统计重压下解放出来了。之前慢查询日志里被看板 SQL 霸屏改造后慢查询日志几乎安静了。运营那边也从“打开页面先转圈”变成了“数字秒出”体感完全不同。5.2 这套方案能用多久结合这次实战我想给这套 MySQL Redis 优化打法划个清晰边界。它适合以下场景数据量在百万到千万级统计指标固定、维度不会爆炸实时性要求高团队没有专职运维大型分析引擎的人力Redis 基础设施已经存在。它不适合以下场景数据量已经上亿且还在快速增长允许用户自由选择任意字段做多维聚合需要关联多张明细大表做复杂计算对数据准确性要求达到“一分钱都不能差”的金融级别。这四类场景建议直接考虑分析型数据库、数据仓库或专门的预计算引擎。如果我们这个订单表继续以每年翻倍的速度增长到千万级时预聚合表方案依然能扛因为趋势数据已经提前算好了但如果哪天业务方开始要求“按任意渠道任意活动任意城市交叉分析”这套方案就需要升级了。到那时候我会优先考虑把明细数据同步进分析型引擎而 Redis 计数器这套思路可以继续保留在实时指标层。最后说一点个人体会。这次改造并没有用到什么特别高深的技术Redis 的 INCR、HINCRBY 恐怕每个用过 Redis 的人都会写MySQL 的INSERT ... ON DUPLICATE KEY UPDATE也是常见语法。真正让我觉得值得记录的是整个问题的思考路径为什么一开始没发现统计 SQL 会变成瓶颈为什么加索引只能缓解不能根治为什么最后没有直接上大数据组件而是选了最朴素的三层结构。我个人最大的心得是面对百万数据统计先别急着换引擎先看看能不能把统计计算前置。很多统计场景其实只需要结果不需要随时现算明细。把“事中计算”改成“事前累计”再用 Redis 把热数据顶在最前面成本低、见效快、后续也好维护。当然方案是否适用永远取决于你的业务指标稳定不稳定。如果指标天天变这套路就走不通了。希望这篇记录能给正在折腾 MySQL 大表统计的你一点启发。