MySQL索引优化最佳实践

目录

1 联合索引优化

2 覆盖索引优化

3 不在查询条件索引列中做任何操作（计算，函数，类型转换）

4 索引字段避免使用!=，<>，not in ，not exists，is null ，is not null查询，否则会走全表扫描

5 使用in，or，<，>，<=，>=时MySQL会进行成本计算评估是否使用索引

6 like查询时遵循最左前缀查询

7 范围查询优化，大范围拆分小范围

8 order by和group by优化

9 没必要再小基数字段上建立索引

10 长字符串使用前缀索引优化

11 where 和 order by 冲突时，优先考虑where使用索引字段

---

注意：

以下结论不一定完全正确；

因为MySQL执行时会进行成本计算，选择那一条执行途径

而影响成本计算的因素有表结构，数据量等

因此需要具体的情况结合explain进行分析，确定优化途径

1 联合索引优化

• 在使用联合索引时，尽可能使用更多的索引字段

• 遵循最左前缀法则，查询条件从联合索引最左列开始，不跳过索引中的列

• 使用联合索引时避免左边索引字段进行范围查询

2 覆盖索引优化

• 范围查询覆盖索引优化

• 等值查询覆盖索引优化

3 不在查询条件索引列中做任何操作（计算，函数，类型转换）

• left函数导致索引失效

• 隐式类型转换导致索引失效

4 索引字段避免使用!=，<>，not in ，not exists，is null ，is not null查询，否则会走全表扫描

5 使用in，or，<，>，<=，>=时MySQL会进行成本计算评估是否使用索引

• in查询数据量少的情况下不走索引

• in查询在数据量大的情况下走索引

6 like查询时遵循最左前缀查询

• like导致索引失效

• 使用覆盖索引让左like可以走索引

• like时去掉左边%使其可以走索引

索引下推

• 如上like查询使用索引下推机制，使用上了全部索引字段
• 索引下推就是在遍历索引树时，使用了本应该使用不上的索引字段，在存储引擎层提前做了过滤，然后再回表查询
• 当使用>范围查询时，未使用索引下推，那么此时从索引树先过滤出name>'Jack'的结果集，然后在执行层使用条件过滤，再进行回表查询

那么为什么like ‘Jack%’可以使用索引下推而> ‘Jack’未使用到呢？

因为MySQL任务like 'Jack%‘通常查出来的结果集较少，而> 'Jack'过滤出来的数据更多，因此在执行层再进行过滤。

7 范围查询优化，大范围拆分小范围

8 order by和group by优化

• 当order by字段与where 条件字段组合起来可以使用索引字段时，无需使用文件排序

• 当order by多个字段时，字段顺序会影响是否使用文件排序

• 当order by和where中使用同一个字段时，条件等值查询会导致order by后字段被优化掉

• order by+where条件字段顺序与联合索引一致时，降序会影响使用使用文件排序

• where中条件使用范围查询，会导致使用文件排序

• where条件中使用使用in查询等同于范围查询

• group by查询时默认会使用该字段进行排序

• 如果想要不使用文件排序时，可以增加order by null语句

• 当group by字段+where 字段组合可以使用索引时，查询不会产生临时表

9 没必要再小基数字段上建立索引

10 长字符串使用前缀索引优化

对于varchar(255)这种大字段建立索引时，其因为占用空间大会导致以下问题

• 单个数据页能包含的数据条数少
• 数据量大的话会索引树高度会增加

以上问题会导致索引检索效率降低，因此可以基于字段的前N个字符建立索引

alter table actor add index index_name(name(10));

这样的话检索时会使用索引下推机制优化查询

但是order by 和group by 查询将使用不到该索引

11 where 和 order by 冲突时，优先考虑where使用索引字段