MySQL Join关联查询索引原理及优化问题咨询

索引优化方案（先提供可落地的优化方法，再解答你的两个疑问） #

• 首先删除现有冗余/低效索引：
  1. 删除Metro表上你额外创建的(state,city)唯一索引：Metro表的主键本身就是(City,State)联合索引，关联条件为两个字段等值匹配时，字段顺序不影响索引使用，额外创建的索引属于冗余，只会增加写入开销。
  2. 删除charitable表上的state单列普通索引：该索引只能过滤state字段，过滤后仍需扫描大量行匹配city字段，过滤效率极低，对性能提升非常有限。
• 新建以下高效索引：
  1. 给charitable表创建(state, city, NAME)联合覆盖索引：关联需要的state、city字段作为联合索引前缀，查询需要的NAME字段放在索引尾部，所有查询需要的字段都包含在索引中，不需要回表查询主键索引，大幅降低IO开销。
  2. 给Metro表创建(state, city, metro_city)联合覆盖索引：同理，关联字段在前，查询需要的metro_city字段在后，匹配时无需回表。
     完成以上调整后，查询耗时可降至百毫秒级。

疑问1解答：大量重复(state,city)组合下的索引处理逻辑 #

MySQL的InnoDB引擎采用B+树存储索引，对于重复的索引键值，会按照主键的顺序排序存储在B+树的叶子节点中：

• 若索引区分度过低（比如单个state下有几十万条重复记录），单独的state单列索引的过滤效果很差：MySQL需要扫描该state对应的所有叶子节点，逐一匹配city字段，还要回表查询需要的NAME字段，开销和全表扫描相差不大，这也是你之前优化效果极差的核心原因。
• 若使用(state,city)联合索引，就算存在大量重复组合，也可以一次性完成state和city两个字段的过滤，直接定位到所有匹配的行，不需要额外扫描无关数据。如果是覆盖索引，还能省去回表开销，重复值多也不会影响索引的过滤效率。

疑问2解答：join关联查询中的索引运行机制 #

MySQL 8.0对join查询主要使用两种算法，索引在两种算法中的作用不同：

1. 嵌套循环连接（NLJ）算法：这是有索引时MySQL优先选择的算法，逻辑为：
   • 优先选择数据量小的表作为驱动表，遍历驱动表的每一行数据，取出关联字段的值
   • 拿着关联字段的值去被驱动表的关联字段索引中查找匹配的行，直接返回需要的字段
     这种场景下，被驱动表的关联字段索引是性能核心：如果有高效的覆盖索引，每次查找都是O(log n)的索引查询，不需要全表扫描。你当前场景下Metro是只有7000行的小表作为驱动表，charitable作为被驱动表，7000次索引查询远好于用charitable当驱动表的150万次查询。
2. 哈希连接（Hash Join）算法：关联字段没有索引时MySQL会选择的算法，逻辑为：
   • 先把小表的关联字段和需要查询的字段加载到内存中，构建哈希表
   • 遍历大表的每一行，计算关联字段的哈希值，和哈希表中的值匹配返回结果
     这种场景下索引的作用较小，但如果有覆盖索引，也可以加速哈希表的构建速度，不需要回表读取全量数据。

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