大数据库中MySQL索引未正常生效问题排查及查询优化求助

先逐个解答你的疑问，再给出针对性优化方案：

1. 为什么possible_keys为NULL？ #

possible_keys字段显示的是MySQL认为可以用来过滤数据的索引选项，但你的查询没有WHERE子句——不需要过滤任何数据，只需要遍历全表/全索引做分组，所以MySQL找不到符合条件的过滤索引，possible_keys自然为NULL。这是正常现象，不代表索引没用。

2. 明明显示使用了索引，为什么感觉没生效？ #

你看到key字段是type_sub_type，说明MySQL确实用了这个索引，但问题在于：这个索引只包含type_id和sub_type_id，分组后要计算max(settlement_date_time)时，MySQL需要通过索引里的主键值回表（去主键索引）查询对应的settlement_date_time值，也就是回表查询。3700万行的情况下，这种回表会产生大量随机磁盘IO，这才是查询耗时超10分钟的核心原因——不是索引没生效，是索引不够“全”。

3. 为什么ref为NULL？ #

ref字段表示的是索引与哪些常量/列做等值匹配（比如WHERE type_id = 1时，ref会显示对应的常量或列）。你的查询没有任何等值过滤条件，只是遍历整个索引进行分组，所以ref为NULL，这完全正常，不用纠结。

4. 核心优化方案：创建覆盖索引 #

解决这个问题最有效的方式是创建覆盖索引——把查询需要用到的所有字段都包含在索引里，让MySQL不需要回表就能完成计算。

创建这个索引：

ALTER TABLE transaction_history ADD INDEX type_sub_type_settlement (type_id, sub_type_id, settlement_date_time);

这个索引的顺序很重要：先按分组字段type_id、sub_type_id排序，再包含settlement_date_time。这样MySQL遍历索引时，同一type_id+sub_type_id的分组是连续的，而且直接能拿到settlement_date_time的值，直接取最大值即可，完全不需要回表。

创建完这个索引后，再执行EXPLAIN，你会看到Extra字段会显示Using index（表示使用覆盖索引，不需要回表），查询耗时会大幅降低。

5. 现有索引的问题 #

你现有的type_sub_type索引是有用的，但不够高效——因为缺少需要聚合的字段，导致必须回表。另外注意你的表结构里有个笔误：KEY sub_type_id_idx (sub_type_id_id)这里的sub_type_id_id应该是sub_type_id吧？这个索引如果是笔误的话，建议修正或删除，避免无效索引占用空间。

6. MySQL配置参数辅助优化 #

如果服务器内存足够，可以调整以下参数来进一步提升性能：

• innodb_buffer_pool_size：调大这个值（比如设置为服务器内存的50%-70%），让更多的索引和数据缓存到内存中，减少磁盘IO。
• innodb_read_io_threads和innodb_write_io_threads：适当调大（比如设置为8-16），提升磁盘IO并发能力。

但记住：配置优化是辅助，核心还是覆盖索引——没有覆盖索引的话，调配置效果有限。

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