MySQL实时排行榜更新与内存表索引优化

MySQL排行榜实时更新卡顿，先检查是否在用普通InnoDB表做高频UPDATE

排行榜更新出现卡顿？首先不要急于质疑业务逻辑，问题很可能出在表结构和更新方式与业务场景不匹配上。InnoDB引擎每次执行UPDATE操作，都需要经过redo log、buffer pool和索引树维护等完整流程。如果遇到每秒数百次的用户积分变更，同时还要执行ORDER BY score DESC LIMIT 100这类查询，磁盘I/O和锁竞争的压力会迅速达到峰值。

具体的优化方向如下：

• 将排行榜的核心字段（例如user_id、score、updated_at）独立出来，创建一张结构精简的表，并且只保留必要的索引。这意味着，所有与排行榜查询无关的冗余字段和索引都应被移除。
• 避免在排行榜表中存储昵称、头像等大字段信息。这些内容完全可以在查询出排行榜结果后，通过JOIN users表或在应用层进行异步补全。
• 如果更新频率超过每秒50次，就不应再考虑直接对原表进行UPDATE ... ORDER BY操作。更可行的策略是采用“写缓冲加定时合并”的方案，下文将详细说明。

使用MEMORY表做实时榜单？警惕重启丢数据与内存溢出

确实，MEMORY表的速度很快，因为它将整张表存放在内存中，不进行持久化存储。但这同时也带来了一个致命缺陷：一旦MySQL服务重启、进程被OOM Killer终止，或执行了TRUNCATE TABLE操作，数据将彻底丢失——这不是程序错误，而是其固有的设计机制。

因此，使用MEMORY表需要遵循以下策略：

• 仅用它来存储“最终用于展示”的精简榜单，例如一个top100的视图。可以每天定时从主表执行一次INSERT INTO top100 SELECT ... ORDER BY score DESC LIMIT 100来刷新数据。
• 务必显式调大MAX_HEAP_TABLE_SIZE参数。默认的16MB容量很容易被占满。在设置为256M之前，建议先进行估算：假设有100万用户，每条记录包含4字节的user_id、8字节的score和4字节的rank，大约需要16MB。为了留出余量，建议在此基础上将容量翻倍设置。
• 不要在MEMORY表上创建HASH索引，并期望用它来执行ORDER BY排序。因为HASH索引不支持范围查询和排序操作，一旦执行ORDER BY，就会触发全表扫描，反而导致性能下降。

score字段未加索引？ORDER BY score DESC LIMIT N将导致全表扫描

许多人存在一个误解：认为“反正只查询前100条数据”，不加索引也无妨。这种想法是错误的。MySQL优化器并不知道你只需要TopN的结果，它必须完整地对所有行按score字段进行排序，然后再取出前N条。一旦数据量超过10万，EXPLAIN执行计划中立刻就会出现Using filesort的提示。

正确的做法是：

• 为score字段单独建立索引：ALTER TABLE leaderboard ADD INDEX idx_score (score)。如果业务还需要按时间进行衰减（例如只统计最近7天的分数），则应建立联合索引：ADD INDEX idx_score_updated (score, updated_at)。
• 注意score字段的数据类型。优先使用INT或BIGINT，避免使用DECIMAL(10,2)这类浮点类型。浮点数排序性能较差，且可能因精度问题导致相同分数的排名出现错乱。
• 如果业务可以接受“近似TopN”的结果，可考虑使用采样查询来提升性能，例如：SELECT * FROM leaderboard TABLESAMPLE SYSTEM (5) ORDER BY score DESC LIMIT 100（该语法需要MySQL 8.0.23及以上版本支持）。

真正能扛住高并发更新的方案：异步聚合与缓存兜底

归根结底，让数据库硬扛所有实时请求始终是最笨拙的方法。在真实的生产环境中，几乎没有纯粹依赖SQL来实时刷新排行榜的案例。核心思路非常清晰：将“写操作”与“读操作”彻底解耦。

具体实施可以如下：

• 用户的积分变更事件，不应直接去UPDATE leaderboard表。而是将其发送到消息队列（例如Kafka或Pulsar），由下游的消费者进行批量聚合（例如，每1秒合并同一个用户的多次变更），然后再统一写入数据库。
• 前端查询排行榜时，优先从Redis获取。使用ZADD leaderboard:week user_id score来更新分数，用ZREVRANGE leaderboard:week 0 99 WITHSCORES来获取Top100。MySQL在此处的角色，应退居为数据兜底和对账的源头。
• 如果业务要求强一致性，可以尝试调大MySQL的排序缓存，例如在MySQL 8.0中使用SET PERSIST sort_buffer_size = 4194304。但必须清醒认识到，这只能缓解症状，无法根治设计上的根本缺陷。

最后，还有一个最容易被忽略的关键问题：你们业务所定义的“实时”，究竟指什么？是秒级更新？毫秒级响应？还是只要用户感知不到延迟即可？把这个问题彻底厘清、想明白，往往比调整任何技术参数都更为重要。