MySQL InnoDB两类索引的内部差异及查询适用性咨询

1. 单字段索引与联合索引的核心差异 #

InnoDB采用聚簇索引架构，主键索引为聚簇索引，所有二级索引的叶子节点均存储主键值，两类索引的核心差异体现在以下两点：

• 排序逻辑不同

  • 单字段addedOn索引：仅按addedOn字段值排序，索引条目为(addedOn值, EmployeeId)，同一addedOn值的记录按主键自然排序（InnoDB内部维护）。
  • 联合(addedOn, EmployeeId)索引：先按addedOn排序，当addedOn值相同时，再按EmployeeId排序。虽然索引条目内容和单字段索引一致，但多了EmployeeId的排序维度，能更精准定位符合多条件的记录。

• 查询过滤效率不同

  • 对于带EmployeeId IN (...)的查询，联合索引可在addedOn > ?的范围内，直接利用EmployeeId的有序性快速定位目标主键，减少需处理的索引条目数量。
  • 单字段索引只能先获取所有addedOn > ?的主键集合，再逐一过滤是否在IN列表中，当符合addedOn条件的记录量较大时，过滤成本会显著升高。

2. 单字段索引能否满足所有查询模式？ #

可以满足，但效率存在差异：

• 针对查询1：select * from Employee where addedOn > (?)
  单字段索引完全适配，MySQL会通过addedOn索引找到所有符合条件的主键，再通过主键回表获取完整数据，这是二级索引的常规使用场景，性能表现正常。

• 针对查询2：select * from Employee where addedOn > (?) and EmployeeId in ('1', '2', '3')
  单字段索引可被MySQL使用，但执行逻辑为：先通过addedOn索引筛选出所有符合时间条件的主键，再检查这些主键是否在IN列表中，最后回表取数。如果addedOn > ?返回的记录量很大，内存中过滤主键的开销会很高；若返回记录量小，和联合索引的性能差异不明显。

另外补充：你提到的“单字段索引隐式指向主键”是正确的——InnoDB所有二级索引的叶子节点都会存储主键值，这是聚簇索引架构的固有特性，二级索引本身就是通过主键关联到完整数据的。

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