MySQL 5.6多表关联查询索引优化及执行计划相关问题咨询

问题解答 #

1. 该查询的最优索引是什么 #

推荐创建如下索引：

CREATE INDEX opt_idx ON Table1 (int_field, created_datetime DESC, enum_filed, fk_int);

设计逻辑：

• int_field是等值查询条件，放在索引最左前缀可快速定位符合条件的记录集合
• created_datetime DESC紧跟等值条件，此时所有满足int_field=?的记录在索引中天然按创建时间降序排列，可完全避免对百万级结果集的高开销filesort操作，是性能提升的核心
• enum_filed放在排序字段之后，可通过MySQL 5.6支持的ICP（索引条件下推）特性在索引层面直接过滤不符合enum_filed != 'value'的记录，无需回表过滤
• fk_int放在索引末尾，关联Table2时可直接从索引中读取关联字段值，避免回表额外IO
• 额外注意：Table2的pk_int如果未设为主键/唯一索引，需要补充创建索引，避免join时触发全表扫描。如果业务允许分页返回结果，加上LIMIT限制返回行数后性能会有数量级提升。

2. 为什么添加索引后EXPLAIN的key_len值为5，不符合17的计算结果 #

key_len统计的是索引用于范围定位的列的总长度，不包含ICP过滤、索引排序、关联读取用到的列：

• 你创建的原索引(int_field, enum_filed, created_datetime, fk_int)中，只有int_field是等值条件，用于索引的起始定位
• 后续的enum_filed是!=范围条件，无法用于索引范围定位，更靠后的created_datetime、fk_int也不会被计入key_len
• 如果你的int_field字段允许为NULL，int类型本身占4字节，NULL标识占1字节，总长度刚好为5，和你看到的key_len结果完全匹配。

3. ORDER BY涉及的字段是否需要加入索引 #

分场景判断：

• 如果查询返回结果集较大（比如你的场景是百万级），且可以通过调整索引顺序让排序字段紧跟在等值查询条件之后、排序方向和索引顺序一致，此时将排序字段加入索引可以避免高开销的filesort，收益极高
• 如果排序字段位于范围查询条件之后，索引的有序性无法被利用，加了也无法避免排序，此时没有必要加入
• 如果结果集很小（千条以内），filesort本身开销极低，加不加对性能影响不大。

4. JOIN关联用到的字段是否需要加入索引 #

分驱动表和被驱动表两种情况：

• 对驱动表（你的场景是Table1）：关联字段加入索引后，遍历索引时可直接读取关联值，无需回表访问聚集索引，减少随机IO开销，推荐加入
• 对被驱动表（你的场景是Table2）：关联字段必须创建索引（主键默认自带索引），否则每次关联都会触发全表扫描，性能会出现数量级下降。

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