SQL索引的使用以及优化

---

一、Order by 优化关键点

1. 如果排序字段没有设置索引，会导致filesort，效率降低。
filesort：在Extra列中出现了filesort字段则说明排序字段在内存中或者在磁盘中进行排序

	explain select salary from employees order by last_name;

会导致 extra中出现 filesort，效率降低

2. 没有过滤条件使用 order by，则order by中的条件不会走索引，即使加了索引

	explain select salary from employees where salary=6000 order by salary;

3. order by 中出现的字段 与联合索引的顺序错误，会导致Extra出现filesort，不满足最左前缀

4. order by 排序字段若有多个，则要么都降序，要么都升序，若同时出现了降序和升序会导致Extra中出现filesort

二、如果优化？

1. 首先要对sql进行分析检查必要的查询字段，不要写*，会导致回表扫描获取主键所相对应的一行全部数据，过滤字段，排序字段是否按照顺序创建好了索引
2. 如果查询字段不再索引中可能会引起回表操作导致Extra中出现filesort，性能降低
3. 一定要有过滤字段不然索引字段不能生效
4. 多个字段排序如果方向不一致也会导致Extra中出现filesort，性能降低
5. 使用explain观察查询类型和索引利用情况
6. 尽可能减少不必要的filesort出现

三、适合创建索引的几种情况

1. 字段的数值有唯一性的限制

2. 频繁作为where查询条件的字段
   某个字段在select语句的where条件中经常被使用到，那么就需要给这个字段创建索引。尤其时候当数据量大的情况下，创建索引可以大幅度提升查询效率

3. 经常Group BY 和 Order By的列
   索引就是让数据按照某种顺序进行存储或检索,因此当我们使用GroupBy对数据进行分组查询,获取使用OrderBy对数据进行排序的时候,就需要对分组或者排序的字段进行索引。若都出现了GroupBy和OrderBy的列可以添加联合索引

4. Update和Delete的where条件列
   更新的时候，更新的字段是非索引字段，提升的效率会更明显，这是因为非索引字段更新不需要对原有索引进行维护

5. distinct字段需要创建索引

6. 多表join连接操作时，创建索引注意事项
   首先，连接表的数量尽量不要超过3张，因为每增加一张表就相当于增加了一次嵌套循环，数量级增长很快影响效率
   其次，对where条件创建索引，因为where才是对数据条件的过滤。
   最后，对用于连接的字段创建索引，并且该字段在多张表中的类型必须一致。

7. 使用字符串前缀创建索引
   例如：address varchar(120)，做数据库中的address字段存储的数据都很长，可以使用
   add index(address(12))来当索引，既节约空间又减少了字符串的比较时间

8. 区分度高的列适合作为索引

9. 使用最频繁的列放到联合索引的左侧

四、索引失效

1. 当创建联合索引中某个字段是范围查询，那么务必将 这个范围查询的字段放在联合索引的最后
   当联合索引是A,B,C时，若B字段是范围查询，那么用到该索引时

   例如
   select xxx from table  where A='' And B>xx And C=''
   

   联合索引B字段后的索引是不生效的，导致索引利用率不高
2. 不等于( ! = 或者 <>)索引失效
3. is null 可以使用索引 is not null 不能使用索引
4. like 通配符 %开头索引失效
5. 类型转换导致索引失效
6. 计算、函数、类型转换(自动或者手动)导致索引失效

总结

来自B站尚硅谷课程总结