记录一次MySQLRedis实现优化百万数据统计的方式先说个发生在身边的真实场景。某天项目压测运营后台一个订单统计接口直接超时看监控发现耗时飙到了接近3秒。查了一圈慢查询日志罪魁祸首是一条count(*)和两条group by语句——它们要扫的是一张已经超过百万行的订单主表而这张表每天还在以几万条的速度增长。当时第一反应不是去调整MySQL的索引因为业务方要的统计维度非常多按天、按渠道、按状态、按商品分类、按地区任意组合。就这种玩法靠后端写SQL硬查任何索引方案都架不住。于是想了个组合方案MySQL负责数据落库Redis负责统计的预聚合。整套方案落地之后统计接口从2.8秒干到了30毫秒左右并且随着数据继续增长查询耗时增长也非常有限。这篇文章把当时的思路、踩过的坑、具体实现都拆开讲清楚。如果你是做后端、做数据统计、或者正在为“百万行数据count一下都好慢”发愁这篇应该能直接给你一套可落地的参考方案。1. 问题复现与性能瓶颈分析1.1 为什么MySQL直接统计百万行会慢很多人第一反应是“加索引啊”。加索引确实能救某些场景但救不了统计型查询。举个典型例子SELECT COUNT(*) FROM orders WHERE status 2 AND created_at 2024-06-01;即使你在status和created_at上建了联合索引InnoDB引擎做count(*)时依然要扫描索引中匹配的行然后逐行计数。当匹配结果有几万甚至几十万行时这个扫描本身就很耗时。更别提下面这种组合统计SELECT category_id, COUNT(*) FROM orders WHERE channel app AND status 1 GROUP BY category_id;GROUP BY需要把匹配的行按分类分组MySQL可能在临时表里做排序和分组操作。数据量一大磁盘临时表都给你整出来顺序IO一多查询别说毫秒级秒级能返回都算运气好。1.2 还有一种隐藏性能杀手深分页统计场景不止是count和group by还有“导出明细、分页查看”这类需求。百万行数据里要跳转到第10000页LIMIT 100000, 20这种写法MySQL得先扫描前面10万行再扔掉只留最后20行成本全花在无意义的扫描上。这就是MySQL在“大数据量统计和深度分页”场景下的本质瓶颈每次查询都实时扫描大量数据做全量计算。而很多统计指标其实是可以“攒”出来的不需要每次实时算。2. 方案设计与技术选型面对这种局面常见的优化思路有几类我逐个对比一下实际选型时是怎么取舍的。2.1 优化SQL和索引这个方案短期有效但很有限。针对单条慢SQL你是可以通过覆盖索引Covering Index强制让查询走索引、不回表速度能提升不少。但统计业务往往是多个维度任意组合索引建少了不顶用建多了写入的索引维护成本爆炸。属于“能挺一阵子不解决根本”的方案适合作为第一层优化手段完全靠它不现实。2.2 离线T1统计把统计任务挪到凌晨跑批处理把结果汇总到统计表/数仓里。这个方案对实时性没要求时确实香但运营后台经常需要看“实时单量”“今天实时GMV”离线数据只能看到昨天满足不了业务。2.3 引入Redis做预聚合核心思路很简单把“查询数据时实时统计”改成“写入数据时同步计数”。每一笔订单创建、状态流转、支付完成时顺手把订单对应的几个统计计数在Redis里加一。查询端要任何维度的数据直接到Redis里读对应的计数器毫秒级返回。这个方案的优点实时性极高订单入库的同时统计值就已经变化查询路径极短一个key/value读取不涉及全表扫描维度扩展灵活新维度无非是多维护几个计数器缺点也直白Redis如果炸了或者数据丢了统计会不准需要一套补偿/重建机制。但这问题有解后面我会专门讲。2.4 最终架构选型我最终采用的组合是MySQL作为唯一数据源所有订单数据可靠落库保证任何时刻可以重建Redis里的统计信息Redis作为统计加速层所有实时统计都走预聚合计数器查询接口只读Redis定时全量重建任务每天凌晨或者检测到统计偏移超阈值时用MySQL全量算一遍指标刷新Redis对账任务周期性对比MySQL统计值和Redis计数器的相近程度偏差过大就触发重建这个架构说白了就是“实时增量为主全量重建兜底”。既拿到了实时统计的查询性能又不至于因为Redis不可用导致统计功能彻底瘫痪。3. 核心实现预聚合与双写架构3.1 Redis的Key设计设计Key是整个方案的灵魂。这里特别讲究不能随手写死一个字符串就完事。我采用的规则是统计业务:统计维度:维度值:时间粒度。举例order:total:2024-06-016月1日订单总量order:status:2:2024-06-016月1日状态为2的订单量order:channel:app:2024-06-016月1日渠道为app的订单量order:category:1001:2024-06-016月1日分类1001的订单量这样每个Key对应一个可解释的统计口径出了问题排查是也方便。3.2 写入时实时计数订单表的数据入口一定要统一。我当时把所有订单写入统一收口到一个服务方法不管是用户下单、后台补单、还是定时任务生成订单都得走这个方法。在这个方法里完成MySQL写入后紧接着把与这笔订单相关的所有Redis计数器全部加一。计数器使用Redis的INCR命令天然原子操作不需要担心并发下的加一丢失问题。而且INCR一个Key之后还能顺手设置有效期比如只保留最近31天的统计数据过期自动清理避免Redis里堆积无用数据。代码如下import redis r redis.Redis(host127.0.0.1, port6379, db0) def incr_metrics(order): 订单写入后更新所有相关统计指标 day order[created_at].strftime(%Y-%m-%d) pipe r.pipeline(transactionTrue) # 基础总量 total_key forder:total:{day} pipe.incr(total_key) pipe.expire(total_key, 31 * 24 * 3600) # 保留31天 # 状态维度 status_key forder:status:{order[status]}:{day} pipe.incr(status_key) pipe.expire(status_key, 31 * 24 * 3600) # 渠道维度 channel_key forder:channel:{order[channel]}:{day} pipe.incr(channel_key) pipe.expire(channel_key, 31 * 24 * 3600) # 分类维度 category_key forder:category:{order[category_id]}:{day} pipe.incr(category_key) pipe.expire(category_key, 31 * 24 * 3600) pipe.execute()Pipeline在这里是必须的。如果不用Pipeline一笔订单要更新4到5个Key每个Key一次RTT订单量一大Redis的请求量直接爆炸。用Pipeline批量发送多个命令只有一次网络往返性能完全不是一个量级。3.3 状态变更的递减处理订单不是只管写入还会有取消、退款、完成等状态变更。这时的原则是状态离开旧值对旧计数器执行DECR状态进入新值对新计数器执行INCR。相当于维护一个“当前状态快照”任何时候从Redis读出来的状态计数都严格等于当前处于该状态的订单数。def change_order_status(order_id, new_status): 订单状态从 old_status 变更为 new_status order get_order_from_db(order_id) old_status order[status] day order[created_at].strftime(%Y-%m-%d) pipe r.pipeline(transactionTrue) if old_status ! new_status: # 旧状态减一 old_key forder:status:{old_status}:{day} pipe.decr(old_key) # 新状态加一 new_key forder:status:{new_status}:{day} pipe.incr(new_key) # 同时更新MySQL中的订单状态 update_order_status_in_db(order_id, new_status) pipe.execute()这里有个顺序问题的坑如果先执行Redis再更新MySQLRedis更新了但MySQL失败数据不一致如果先更新MySQL再执行RedisRedis执行失败又怎样。当时的做法是Redis操作和MySQL操作放到同一个事务/大事务里不现实所以用了“本地消息表定时对账”来兜底。简单说更新MySQL状态之后异步重试执行Redis计数变更重试多次失败的写入对账日志由对账任务扫出来重新刷。3.4 业务写入双写一致性保障“双写”听上去优雅真正落地的时候你得处理好失败场景。我的策略分三层即时重试Redis操作失败后立即重试2次每次间隔100毫秒异步补偿如果即时重试仍失败把失败的计数事件订单ID 操作类型 时间写入本地消息表由后台任务每隔1分钟扫一次重新执行Redis更新定期对账每天凌晨2点用MySQL跑一遍全量统计跟Redis计数器的值逐一比对偏差超过阈值就整体重建对应的统计Key。这套组合下来Redis瞬时故障基本不会对统计准确性造成不可逆影响。最坏情况是某个时段的统计短暂偏差最多延迟1分钟被异步补偿修复。4. 关键代码与实操细节4.1 MySQL订单表设计参考原订单表最重要的是含统计需要的业务字段和索引。真实环境里订单表字段动辄几十个这里只放统计相关核心字段CREATE TABLE orders ( id bigint(20) unsigned NOT NULL AUTO_INCREMENT, order_no varchar(32) NOT NULL COMMENT 订单号, channel varchar(20) NOT NULL COMMENT 渠道app/h5/mini_program, status tinyint(4) NOT NULL COMMENT 订单状态0待支付 1已支付 2已发货 3已完成 4已取消, category_id int(11) NOT NULL COMMENT 商品分类ID, amount decimal(10,2) NOT NULL COMMENT 订单金额, created_at datetime NOT NULL COMMENT 下单时间, updated_at datetime NOT NULL COMMENT 更新时间, PRIMARY KEY (id), KEY idx_channel_status (channel, status), KEY idx_created_at (created_at) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;建索引核心就两个idx_channel_status加速按渠道状态的组合查询idx_created_at加速按日期的查询和统计再多建索引写入压力太大这两个作为兜底真正高频统计走Redis。4.2 Redis计数器初始化历史数据如果已经在MySQL里了上线预聚合方案时不可能丢掉。必须把已有的历史数据先“补”进Redis。我当时写了个初始化脚本按月扫描订单表对每天、每个渠道、每个分类、每个状态分别统计然后批量写入Redis。def init_redis_from_mysql(start_date, end_date): 从MySQL全量初始化Redis计数器 cur_date start_date while cur_date end_date: date_str cur_date.strftime(%Y-%m-%d) # 每日总订单数 total db.query_one(SELECT COUNT(*) FROM orders WHERE created_at LIKE %s, (date_str %,)) r.set(forder:total:{date_str}, total) # 按状态统计 status_rows db.query(SELECT status, COUNT(*) FROM orders WHERE created_at LIKE %s GROUP BY status, (date_str %,)) for status, cnt in status_rows: r.set(forder:status:{status}:{date_str}, cnt) # 按渠道统计 channel_rows db.query(SELECT channel, COUNT(*) FROM orders WHERE created_at LIKE %s GROUP BY channel, (date_str %,)) for channel, cnt in channel_rows: r.set(forder:channel:{channel}:{date_str}, cnt) # 按分类统计 category_rows db.query(SELECT category_id, COUNT(*) FROM orders WHERE created_at LIKE %s GROUP BY category_id, (date_str %,)) for category_id, cnt in category_rows: r.set(forder:category:{category_id}:{date_str}, cnt) cur_date timedelta(days1)这个脚本运行了几分钟就完成了因为历史数据按created_at做范围扫描配合索引效率不差。注意一点如果数据量大一次性查出所有汇总可能内存爆用日期分段扫是稳妥的。4.3 金额统计用Hash除了订单量业务方经常要看GMV成交总额。同样是统计但这一次不是加一而是累加金额。那么INCR就不够用了得用HINCRBYFLOAT或者直接维护一个Hash结构。我用的方式是按天一个Hash Keyfield是渠道value是该渠道的成交金额。def incr_gmv(order): 累加当日GMV day order[created_at].strftime(%Y-%m-%d) key forder:gmv:{day} pipe r.pipeline(transactionTrue) pipe.hincrbyfloat(key, order[channel], float(order[amount])) pipe.expire(key, 31 * 24 * 3600) pipe.execute()Hash结构的好处是一个Key下能挂多个维度的值查询时不需要遍历多个Key。比如要“查询某天所有渠道的GMV”一次HGETALL就拿到了。但你也要注意别把所有维度都塞进一个Hash后面会讲大Key的问题。4.4 使用Lua脚本保证批量原子性前面代码里用Pipeline虽然能批量发送但Pipeline并不保证局部原子性——执行过程中如果某个命令失败其他命令已经执行了。统计计数这个东西要么全部成功要么全不成功半吊子状态很讨厌。解决方法是使用Lua脚本。Redis从2.6版本开始支持执行Lua并且Lua脚本在执行过程中不会被其他命令插入天然具备原子性。-- KEYS[1] ~ KEYS[5] 是要更新的计数器Key -- ARGV[1] 是有效期秒 local i for i 1, #KEYS do redis.call(INCR, KEYS[i]) redis.call(EXPIRE, KEYS[i], ARGV[1]) end return 1Python端这样调用import redis r redis.Redis(host127.0.0.1, port6379, db0) lua_incr r.register_script( local i for i 1, #KEYS do redis.call(INCR, KEYS[i]) redis.call(EXPIRE, KEYS[i], ARGV[1]) end return 1 ) def incr_metrics_lua(keys, expire_seconds): lua_incr(keyskeys, args[expire_seconds])用Lua的最大好处是要么Keys里所有计数器全部加一要么一个都不加。不会出现“总量加了但状态没加”这种数据打架的情况。4.5 查询端的读取实现查询端的复杂度主要在于前端可能传一个或多个筛选条件后台需要把这些条件组合成对应的Redis Key然后批量读取合并。def get_order_stats(date_str, channelNone, statusNone, category_idNone): 按条件查询订单统计 pipe r.pipeline(transactionTrue) # 基础总量 pipe.get(forder:total:{date_str}) # 各维度求和或取具体Key的值 if channel: pipe.get(forder:channel:{channel}:{date_str}) if status: pipe.get(forder:status:{status}:{date_str}) if category_id: pipe.get(forder:category:{category_id}:{date_str}) results pipe.execute() # 解析并返回结果 return results有人会问如果同时传了渠道状态分类三个条件单独读三个Key没法交叉统计啊。这时候有两个选型维护组合维度的Key即order:channel:app:status:1:date每次订单写入时把这些组合Key也更新一遍交叉统计场景少的话可以直接Redis返回粗粒度再由MySQL兜底查询少数条件的精确值。我当时选了方案一因为业务方比较强势要多个维度的组合统计。组合Key的维护成本高但单次写入多更新几个Key而已写入耗时可以接受。更关键的是组合Key不要无限分类穷举只针对高频使用的组合做预聚合低频组合直接走MySQL。4.6 大Key和热点Key问题说到组合Key这里就不得不提大Key的问题。最典型的是一个年月日维度下把“当天所有分类的订单量”放到一个Hash里key forder:category:2024-06-01 # 一次HMSET把当天所有分类的count都塞进去这个Hash如果分类很多每个分类还带多个子维度瞬间可能变成几十万个field的大Key。Redis对单Key的命令执行是单线程处理的大Key的HGETALL会阻塞Redis服务几十甚至几百毫秒期间所有其他命令都在排队——这是生产环境的灾难。所以我的原则每个Key的粒度尽可能小一个Key只承载一个维度的聚合field数量严格控制在几千以内定期用redis-cli --bigkeys扫描大Key热点Key也遇到过比如“当天所有订单统计”这个Key运营一打开后台首页必然狂刷。单Key的QPS非常高一个Redis实例可能扛不住。处理方式是在读取端做本地缓存比如进程内缓存10秒或者把Key做分片散列比如拆成order:total:2024-06-01:0、order:total:2024-06-01:1读取时求和。5. 踩坑记录与常见问题排查5.1 场景一Redis重启后统计全丢Redis的持久化默认配置不一定满足统计业务需求。如果用的是RDB快照默认可能是save 900 1这种——900秒内至少1个key变化才保存。崩溃时丢失最近十几分钟甚至更久的数据统计值就缺了一块。我的处理方案开启AOF持久化appendfsync设为everysec最多丢1秒的数据上面提到的1分钟一次的异步补偿任务能从消息表补上增量每个整点跑一次增量对账发现偏差超过千分之五就重建这套组合下来Redis重启对统计业务的影响可以控制在分钟级以内而且最终对账会把数据纠正回来。5.2 场景二先更新MySQL还是先更新Redis这是双写架构里永恒的争论点。我的结论是如果两边都有可能失败那就不存在完美的先后顺序。真正要保证最终一致必须依赖补偿机制而不是费劲研究顺序。实际做法是MySQL更新成功作为主流程Redis的更新失败进补偿队列。理由很朴素——MySQL是数据源它要是丢了Redis里那点统计值啥也不是。所以优先级一定是先保MySQLRedis尽量去同步失败后快速补偿恢复。5.3 场景三统计维度扩展导致代码到处加计数最开始我是在下单方法里硬编码写了几条计数更新。后来产品又加了“地区维度”“用户标签维度”每次加维度都得改业务代码门槛高耦合大。后来重构了一版定义好统计事件比如order.created、order.status_changed所有订单状态变化都发布事件由统计消费者订阅事件然后统一更新计数器。这样一来业务代码里一行统计代码都没有新增维度只需要在消费者那边加配置就行。如果你预感到统计维度一定会膨胀建议一开始就按这个思路设计别像我一样回头再重构。5.4 场景四多实例部署导致并发重复计数项目部署了多个实例同一个订单的更新请求可能被两个实例同时处理导致重复INCR。这种情况必须保证幂等。我的做法通过订单唯一事件ID做幂等。每个订单生命周期中的每个操作都生成一个全局唯一的事件ID如order_{order_id}_{event_type}Redis用SETNX记录该事件是否已处理if r.setnx(fevent:processed:{event_id}, 1): # 第一次处理执行计数器更新 incr_metrics(order) else: # 已处理过直接返回 pass同时给这个Key设置一个适当的过期时间防止无界增长。这个方案在处理消息重试、应用重跑场景时非常稳。5.5 场景五HyperLogLog与UV统计有段时间产品要实时看紫外UV也就是当天去重的访客数。用INCR完全没法去重但又不值得为了UV统计去维护一个巨大的Set集合。这里用的是Redis的HyperLogLog结构标准误差0.81%用极小的内存就能统计超大基数的去重数。对于UV这种量级的数据误差可以接受def record_visitor(day, user_id): key fuv:{day} r.pfadd(key, user_id) def get_uv(day): key fuv:{day} return r.pfcount(key)不过HyperLogLog有个特点pfcount多个Key的结果时会自动合并去重。所以如果你要按渠道看UV得渠道维度单独key。注意HyperLogLog的误差是概率性的对于需要精准去重计数的业务比如“实际支付用户数”还是得用Set或者MySQL的COUNT(DISTINCT user_id)兜底。6. 实测效果与扩展思考6.1 优化前后性能对比改动上线后我在压测环境直接对比了同一批接口优化前后的数据统计场景优化前MySQL实时计算优化后Redis预聚合当日订单总数2890ms23ms按渠道分组的当日订单量3560ms31ms按状态分组的近7天订单量5240ms47ms指定分类渠道的当日组合统计4730ms29ms分页查询第10000页订单明细6310ms186ms走Redis缓存ID列表优化前某些接口甚至因为超过3秒而触发了报警优化后基本都在50毫秒以内。数据库的压力也直线下降——Redis承担了绝大多数统计读流量MySQL的QPS明显降低慢查询日志整页整页翻的情况再也没出现过。其实当时压测数据到500万行量级后优化前的接口基本跑不动了好几秒而Redis预聚合方案查询耗时仍然稳定在几十毫秒几乎没有明显增长。因为查询路径只跟“Key的数量”有关跟“业务数据总量”基本解耦。6.2 这套方案的边界和局限Redis预聚合统计虽好也不是银弹。数据量到了千万级、亿级有几个新问题值得提前思考。组合维度爆炸如果业务要求几十个维度任意交叉组合Key数量会呈几何级数膨胀内存占用和写入开销都会失控。这种情况建议引入OLAP引擎如ClickHouse、Doris做专业分析型存储Redis只做热数据的核心维度统计。精度要求极高HyperLogLog有误差预聚合万一漏了补偿也会偏。如果统计结果用于财务对账必须用MySQL做最终数据源定期全量校准。Redis内存容量虽然统计Key大多是几字节或几十字节的小对象但积少成多。上百万个Key也得几个GB内存单机Redis不够时要考虑集群或者裁剪维度。如果你遇到的情况是“数据量更大、维度更多、实时性要求还贼高”那更合适的架构是把统计落到专门的分析型数据库里让MySQL只做源头的事务存储。Redis预聚合方案更像是“在现有架构里花最小成本解决80%统计问题”的务实选择。6.3 后续还能怎么扩展这套基础架构跑顺之后我还在上面扩展了两个玩法第一个是多时间粒度。在记录日维度的同时顺便记录小时维度order:total:2024-06-01:14这样想看当天某个时段的订单趋势也很方便。第二个是Ranking排行。用Redis的Sorted Set维护“商品销量排行”“热门分类Top10”分数用销量排序天然维护好。查询排行榜时一句ZREVRANGE搞定不需要再对订单表做聚合。如果你正在规划类似的数据统计优化建议第一步先用SHOW PROFILE或慢查询日志把当前真正慢的SQL捞出来分析一下是集中在count还是group by第二步按照本文的思路梳理核心统计维度和数据入口第三步再动手把预聚合逻辑接到写入链路里。从我这些天的实操体会来看这套方案最大的价值不是快了多少毫秒而是让“统计”这个功能从“查询时实时算”变成了“写入时顺手记”一套逻辑把数据库的压力和接口的响应时间同时改善。如果你也踩在百万数据统计的性能门槛上可以按照这个思路试起来有问题欢迎随时交流。