MySQL InnoDB大数据量下使用索引查询过慢问题排查

涉及的测试查询语句：

SELECT name 
FROM players USE INDEX(players_name_status_IDX) 
WHERE name = 'test' AND status = 1

上述查询在查找不存在的name值时耗时随数据量线性增长的现象，核心原因有以下几点：

• 索引失效触发全索引扫描：正常情况下(name, status)结构的联合索引，查询指定name值时可以通过B+树的有序性直接定位到对应区间，若目标name不存在可以立刻返回结果，不会扫描全表。出现全量扫描的核心原因大多是隐式类型/字符集转换：比如name字段的字符集和查询参数的字符集不匹配，导致MySQL无法使用最左前缀匹配规则，只能遍历整个联合索引的所有叶子节点做逐行匹配，扫描行数和表总数据量成正比，耗时自然随数据量增长线性上升。
• 缓冲池命中率下降：当表数据量为15万时，整个联合索引的体积较小，可以完全加载到InnoDB缓冲池中，扫描操作都是内存访问，耗时较低。当数据量增长到49万后，联合索引的体积超过了缓冲池的可用空间，每次全索引扫描需要从磁盘读取部分冷数据页，磁盘随机IO的耗时是内存访问的数百倍，直接推高了查询耗时。
• 索引碎片累积：随着表数据量增长，DML操作会导致联合索引的B+树出现大量页分裂和碎片，索引页的填充率降低，相同数量的索引记录需要占用更多的物理页，全扫描时需要读取的IO次数增加，耗时同步上升。

问题验证方法 #

• 执行EXPLAIN语句查看执行计划，若type字段值为index则确认是全索引扫描，符合扫描全量数据集的现象。
• 执行SHOW FULL COLUMNS FROM players WHERE Field = 'name';查看name字段的排序规则，和当前会话的character_set_connection参数值对比，若不一致即可确认是字符集不匹配触发的索引失效。
• 查看InnoDB缓冲池命中率：执行SHOW ENGINE INNODB STATUS，查看BUFFER POOL AND MEMORY部分的Hit ratio指标，若低于99%说明缓冲池空间不足。

优化方案 #

• 统一字符集：调整表字段和会话的字符集一致，避免隐式转换，让查询可以用到索引最左前缀匹配，不存在的name查询耗时可以降到1ms以内。
• 调整缓冲池配置：将innodb_buffer_pool_size参数调整为服务器可用内存的50%~70%，保证热索引数据可以全部缓存在内存中，消除磁盘IO开销。
• 定期整理碎片：数据变更频繁的表可以定期执行OPTIMIZE TABLE players;整理索引碎片，降低索引的离散度。

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