MySQL InnoDB亿级表联合索引前导列选唯一还是低基数字段？

两种方案存在非常明显的性能差距，你的业务场景下优先选方案1：(another_id, state)，核心原因结合InnoDB的B+树二级索引逻辑拆解如下：

• 方案1的执行路径极短：another_id是全局唯一的高区分度字段，作为联合索引前导列时，优化器会直接对传入的100个IN列表值做精准的等值定位，每个another_id对应索引里最多1条记录，定位到记录后直接在索引页就能读到state字段做匹配判断。整个过程走覆盖索引不需要回表，固定扫描100条左右的索引记录，不管传入的state是0-3哪个值，性能都极其稳定，1亿行的表上也能做到毫秒级返回。
• 方案2的执行成本高1~2个数量级：state是只有4个取值的低基数字段，作为前导列时，首先会定位到传入state对应的索引区间——1亿行的表平均每个state对应2500万行记录，哪怕后面跟的another_id在索引里是有序的，要在2500万条记录的区间里找100个离散的another_id值，需要做多次索引页的读取和二分查找，扫描的索引页数是方案1的几十上百倍，数据量越大差距越明显。
• 额外提一句：你已经给another_id建了单字段唯一键，新建(another_id, state)联合索引后可以直接删掉原来的单字段索引，因为联合索引的最左前缀完全可以覆盖原单字段索引的所有查询场景，不会造成索引能力缺失，还能减少一个索引的维护开销、节省存储空间。

针对这个1亿行规模的InnoDB表，设计索引时还要注意这几点：

• 优先保证覆盖索引：你的查询是COUNT(*)统计，不需要返回其他业务字段，只要索引包含WHERE条件里用到的another_id、state两个字段，就完全不需要回主键索引读取数据，能把随机IO降到最低，这是这类统计查询性能拉满的核心前提。
• 等值查询的联合索引顺序，不要死套“低基数不能放前面”的规则，但要记住：多个等值匹配条件下，高区分度字段放前导列的收益永远更高。高区分度列在前可以直接把扫描范围压缩到极小的区间，尤其是IN查询场景，能把扫描行数从千万级直接降到百级，收益差距是数量级的。
• 严格避免冗余索引：除了前面提到的another_id单字段索引可以被联合索引替换之外，也要注意不要建功能重复的索引，每多一个二级索引，写入、更新、删除数据时就要多维护一棵B+树，1亿行规模的表索引维护开销会非常明显。
• 注意查询参数的类型匹配：another_id是bigint类型，传入IN列表的参数必须是整型值，不要传字符串格式的数字，否则会触发隐式类型转换，导致索引失效。
• 评估索引的通用性：(another_id, state)的索引除了能满足你当前的COUNT统计需求，还能支持所有带another_id条件的查询，不管有没有带state条件都能命中；但(state, another_id)的索引只能支持同时带state等值条件+another_id条件的查询，只要查询里不带state条件就完全无法命中，通用性极差。

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