MySQL单实例下高活跃800万行级大表性能优化方案咨询

• 原本用于匹配最高频查询条件的fk_claim_user_id索引被设置为INVISIBLE，MySQL优化器完全不会选择该索引执行查询，所有按用户ID过滤的请求实际执行全表扫描，这是查询越优化越慢的核心原因。
• 表中存在大量冗余无效索引：与主键完全重复的global_id_UNIQUE唯一键、低选择度的单字段索引（rarity、emoji、各外键关联字段的独立索引）不仅没有查询加速效果，还会大幅拉高增删改操作的索引维护开销，拖慢整体吞吐。
• 4个带ON DELETE CASCADE的外键会在高并发写入时产生额外的锁校验、级联扫描开销，是高活跃表常见的性能堵点。
• 所有现有索引都是单字段结构，无法覆盖典型查询的回表、排序需求，哪怕索引可见，也需要额外的回表、文件排序操作消耗性能。
• 之前执行碎片整理后性能没有明显提升甚至下降，是因为整理操作会清空InnoDB缓冲池的热缓存，且原本核心的fk_user_id索引处于不可见状态，无论缓存温度多高，查询依然要扫大量无效数据。

所有操作不需要分区、分库分表，仅需执行标准ALTER语句完成，无额外维护成本：

• 清理无效冗余索引，降低写开销
  • 删除完全冗余的唯一键：ALTER TABLE claim DROP INDEX global_id_UNIQUE;（主键本身已经保证global_id全局唯一，该索引无任何存在价值）
  • 删除所有低价值的单字段索引：fk_claim_character_id、fk_claim_image_id、fk_claim_series_id、原不可见的旧fk_claim_user_id索引、fk_claim_gif_id、fk_claim_rarity、fk_claim_emoji，如果没有单独按这些字段跨用户检索的固定业务场景，这些索引对核心用户维度的查询没有加速作用，反而持续增加写操作的维护成本。
  • 删除所有4个级联外键，将关联数据的一致性校验迁移到业务层事务中实现：单节点部署下，外键带来的锁竞争、级联扫描开销远大于业务层校验的开发成本，删除后可直接降低30%以上的写操作延迟，避免高并发下的外键死锁问题。
• 构建覆盖核心查询的定制索引，直压响应时间
  针对三个典型慢查询的执行逻辑，建立一个复合覆盖索引即可覆盖90%的查询需求，索引顺序严格按照查询的过滤、排序、返回字段设置：

  ALTER TABLE claim ADD INDEX idx_user_globalid_character (fk_user_id, global_id, fk_character_id);
  

  该索引的性能优势完全匹配三个慢查询的执行逻辑：
  • 匹配SELECT global_id FROM claim WHERE fk_user_id = ?：索引直接存储所有fk_user_id对应的global_id，查询全程不回表，直接从索引树返回结果。
  • 匹配SELECT global_id, fk_user_id, fk_character_id FROM claim WHERE fk_user_id = ? ORDER BY global_id ASC LIMIT 0,20：同用户的记录在索引中天然按global_id排序，查询时直接顺着索引扫前20条记录即可返回，不需要额外的文件排序操作，也不需要回表查数据，800万行的表下该查询的响应时间可稳定在50ms以内。
  • 匹配SELECT global_id FROM claim WHERE fk_user_id = ? GROUP BY fk_character_id HAVING MAX(fk_character_id) = 1：索引存储了所有分组需要的字段，分组聚合时不需要回表查原表数据；针对该查询的业务逻辑，可进一步将SQL简化为等值查询SELECT global_id FROM claim WHERE fk_user_id = ? AND fk_character_id = 1 LIMIT 1，查询会直接定位到目标记录，响应时间可稳定在10ms以内。
• 适配高活跃表的运行配置
  • 不需要定期做全表碎片整理：高活跃表频繁增删改产生的碎片是正常现象，全表整理会清空InnoDB缓冲池的热数据，导致后续数小时的查询性能下降，仅当碎片率超过50%时再低峰期执行整理即可。
  • 保持InnoDB缓冲池大小为服务器可用内存的50%-70%，让热索引和热数据全部驻留内存，避免磁盘IO开销。

完成上述优化后，核心查询的响应时间可稳定在100ms以内，完全满足300ms以内的性能要求，且无额外的分布式、分库分表的维护成本，适配单节点部署的限制。

内容的提问来源于stack exchange，提问作者King Reload