1. Extra是NULL
kd_competition_drill_record表中有联合索引 (competition_name, user_id, drill_id)
- where中使用到了联合索引的前缀列;
- 但是没有使用索引覆盖,需要回表(聚簇索引)查询完整的select列;
- 属于“能用索引,只能用了一点点”。
explain SELECT * FROM kd_competition_drill_record force index(ix_kd_competition_user_drill_id)WHERE kd_competition_drill_record.competition_name ='hzqw2203'
2. Extra是单独Using index
- where中使用到了联合索引的前缀列;
- 使用了索引覆盖。通过二级索引直接可以查询select列,不需要二次回表查询;
- 属于“充分利用了索引”。
explain SELECT competition_name FROM kd_competition_drill_record force index(ix_kd_competition_user_drill_id)WHERE kd_competition_drill_record.competition_name ='hzqw2203'
3. Extra是单独的Using Where
可能用到了索引,也可能没有用到索引,还需要结合type和key字段去判断。
(1)where中的列competition_id不存在索引
explain SELECT * FROM kd_competition_drill_record force index(ix_kd_competition_user_drill_id)WHERE kd_competition_drill_record.competition_id ='ZrrLY7yVYj'
这里结合key和type就知道,属于“一点没用到索引”。
(2)where中的列user_id在联合索引中,但是select列没有被联合索引覆盖。
explain SELECT * FROM kd_competition_drill_record force index(ix_kd_competition_user_drill_id)WHERE kd_competition_drill_record.user_id = 'afcb876a-52ea-4148-8e66-285ac8e74e74'
这里结合key和type就知道,属于“一点没用到索引”。
(3)where中的列user_id式联合索引的前缀索引或者单独的索引,但是where中的其他列不是索引列
ix_kd_competition_drill_record_user_id索引是kd_competition_drill_record表中单独的user_id索引
explain SELECT * FROM kd_competition_drill_record use index(ix_kd_competition_drill_record_user_id) WHERE kd_competition_drill_record.competition_name IN ('hzqw2203', 'hzreshen2203') AND kd_competition_drill_record.user_id = 'afcb876a-52ea-4148-8e66-285ac8e74e74'
可以看到这里实际是用到了索引user_id,但是因为where中还有一列competition_name不在索引中。所以这里是引擎层通过user_id索引查出了一部分数据,然后交给了server层去通过where条件过滤。也是属于“用到了索引,但是只用到了一点点”。
4. Extra是Using where; Using index
- 对比一下上面3.2种的情况,这个case中Extra是Using where; Using index:
- where中的列user_id不是联合索引的前缀列;
- select列是联合索引的一列(不一定是前缀列);
- 可以利用联合索引查询到select列,也就是利用了索引覆盖,避免了全表扫描。
- 属于“能用索引,只能用了一点点”。
(1)where列不是联合索引的前缀列
explain SELECT competition_nameFROM kd_competition_drill_record force index(ix_kd_competition_user_drill_id)WHERE kd_competition_drill_record.user_id = 'afcb876a-52ea-4148-8e66-285ac8e74e74'
(2)where列是联合索引的前缀列,但是用了IN关键字
explain SELECT competition_nameFROM kd_competition_drill_record force index(ix_kd_competition_user_drill_id)WHERE kd_competition_drill_record.competition_name IN ('hzqw2203', 'hzreshen2203')
5. Extra是 Using index condition
Using index condition 是索引下推,也就是没有遵循联合索引的最左前缀原则,但是也可以利用联合索引,通过where条件过滤掉一部分数据。然后再回表查询,出现Using index condition意味着肯定要回表。
如果没有回表查询,不管有没有下推都是利用索引覆盖,Extra就是Using where; Using index。
- where列肯定要包含联合索引的前缀列,但没有用等于,而是用的IN;
- where列不一定遵循最左原则;
- where条件不能出现非联合索引的列;
- select列一定没有利用索引覆盖,需要回表查询。
(1)where列是联合索引的前缀列,但是用了IN关键字
explain SELECT *FROM kd_competition_drill_record force index(ix_kd_competition_user_drill_id)WHERE kd_competition_drill_record.competition_name IN ('hzqw2203', 'hzreshen2203')
(2)where列包含前缀列,但是中间列空缺,直接使用后面的列
explain SELECT *FROM kd_competition_drill_record force index(ix_kd_competition_user_drill_id)WHERE kd_competition_drill_record.competition_name = 'hzqw2203'AND drill_id = 'jump_rope_landscape_2007'