MySQL 中的索引选择策略

ps：如果这篇帖子对于还在找工作和找实习的你有所帮助，可以关注我，给本贴点赞、评论、收藏并订阅专栏；同时不要吝啬您的花花

当SQL语句 select * from t where a = ? and b = ? 中，表t没有a和b的联合索引，只有a的单独索引（idx_a）和b的单独索引（idx_b）时，数据库优化器会根据「数据量、过滤性、索引成本」选择以下3种方式之一，核心逻辑是“选过滤性最好、成本最低的索引”，具体如下：

1. 索引合并（Index Merge）—— 最优场景（过滤性均较好）

这是最理想的情况，优化器会同时使用idx_a和idx_b两个单独索引，通过“交集”快速定位满足a=? and b=? 的数据行，步骤如下：

1. 分别通过idx_a找到所有满足 a=? 的行的主键ID；
2. 分别通过idx_b找到所有满足 b=? 的行的主键ID；
3. 对两个主键ID集合取「交集」（只保留同时存在的ID）；
4. 通过主键ID回表（回表：通过主键索引找到完整行数据），返回最终结果。

• 适用场景：a和b的过滤性都不错（比如筛选后的数据量占比低），索引合并的成本低于单索引+全表扫描。

2. 选择其中一个过滤性更好的单独索引—— 常见场景（会走索引下推）

如果两个索引的过滤性差异较大，优化器会选择「过滤性更强」（筛选后数据量更少）的那个索引，放弃另一个，这种场景下会触发索引下推（ICP，Index Condition Pushdown），无需回表后再过滤b字段，能进一步降低成本，具体步骤如下：

• 示例：假设idx_a的过滤性更好（a=? 能筛选出10行，b=? 能筛选出1000行），优化器会优先用idx_a，同时触发索引下推；

1. 通过idx_a找到所有满足 a=? 的索引记录（包含主键ID，无需立即回表）；
2. 利用索引下推，直接在索引层面（而非回表后）过滤出满足 b=? 的记录，筛选掉不满足b条件的主键ID；
3. 仅对筛选后符合条件的主键ID进行回表，取出完整行数据，返回最终结果。

• 核心补充：索引下推的作用的是“减少回表次数”—— 原本需要先回表拿到完整数据再过滤b，现在在索引层面就完成b的过滤，只对符合a和b双重条件的记录回表，大幅降低IO消耗；只有当MySQL版本≥5.6（索引下推功能默认开启）、查询条件包含非索引列（此处b列未在idx_a中）时，才会触发该机制。

3. 放弃所有单独索引，走全表扫描—— 最差场景

当两个单独索引的过滤性都极差（比如 a=? 和 b=? 筛选后的数据量占表总数据的50%以上），优化器会判断“用索引+回表”的成本，高于直接全表扫描，此时会放弃idx_a和idx_b，直接扫描全表，逐行判断是否满足 a=? and b=?。

• 原因：过滤性差时，用索引筛选后仍需大量回表，回表的总耗时，比直接全表扫描逐行判断更久。

关键补充

• 过滤性判断标准：索引列的「区分度」（比如性别字段区分度低，ID字段区分度高），区分度越高，过滤性越好，优化器越倾向于选择该索引。
• 不同数据库（MySQL、Oracle等）逻辑一致，但优化器判断“成本”的细节有差异（比如MySQL的cost计算包含IO、CPU消耗），但核心规则不变。
• 补充说明：索引下推仅适用于“单索引筛选+其他条件过滤”场景（如本条），索引合并场景无需触发索引下推（因已通过两个单独索引取交集完成双重筛选）。

ps：如果这篇帖子对于还在找工作和找实习的你有所帮助，可以关注我，给本贴点赞、评论、收藏并订阅专栏；同时不要吝啬您的花花