开发中常用的索引优化手段①使用索引覆盖,避免回表,②遵循最左前缀原则,避免索引失效,③定期整理索引碎片。
使用索引覆盖
原理:索引覆盖可以可以避免回表操作,从而提高性能。
索引覆盖:索引覆盖指索引中包含了要查询的全部字段,在查询时,从索引中即能得到查询结果,无需读取记录数据。当使用的是非聚簇索引时,无需读取记录数据那么就避免了回表操作。
避免回表:回表指使用非聚簇索引时,一次select需要执行两次查询:先从非聚簇索引树中查到目标记录的主键,在根据主键值在聚簇索引树中查到真正的记录数据。
方法:
1. 尽量使用主键查询查找数据,因为主键默认建立索引,通过主键查询信息时不仅会用到索引,而且不会回表。
2. 对于常用的单个查询关键字建立单列索引,对于常用的多个关键字查询建立组合索引,用以实现索引覆盖。
注意:尽管使用联合索引能加快查找速度,但过度使用会导致前面提到的三个问题(时间、空间、碎片)。
使用explain命令时,若使用索引覆盖,会有一个Using index标志
![](https://i-blog.csdnimg.cn/blog_migrate/5baacb0061071d8bdd69610255e8f31a.png)
使用最左前缀
对联合索引进行查询时,若查询条件不满足最左前缀原则,将扫描整个索引树,而不是利用B+树的有序性进行查找。
1. 最左前缀:在查询索引树时,会将查询条件从联合索引的最左边起开始匹配。
1. 在列A、B、C上建立索引A_B_C,那么相当于建立了A、A_B、A_B_C这三个索引(没有B_C)
当查询条件为A_C时,也能够用到索引A,因为建立联合索引时,会先根据最左边的索引进行排序,在最左边索引排序完成的基础上,在对次左边的索引进行排序,故此处能够先用A进行联合查询。
2. 假如对a、b建立索引(a,b),那么索引树可能如下:
能够看到节点首先根据a进行排序,在确定a排序的基础上在对b进行排序,故从整个索引的角度来说a是有序的b是无序的,但当a相同时b就是有序的了
因此,当对上图中的索引树进行查找时,若直接对b进行查找,在同一层的范围内存储的索引字段b是无序的,因此我们需要将整一层都读入才能完成对b的查找
注:“当查询条件和联合索引最左边的一列或者连续几列匹配时,就能通过索引执行查找”这种说法不完全正确。只要查询条件中的列包含在联合索引中,就算不符合最左匹配原则,但也会通过扫描整个索引树进行查询,但需要注意的是索引树扫描通常会比全表扫描要慢,尽管索引树扫描的IO次数可能会少一点,但索引树扫描是随机IO,全表扫描是顺序IO,顺序IO的速度远快于随机IO。
2. 在模糊查询中(like),前缀查询可以使用索引,中缀和后缀查询都不能使用索引。
即模糊查询中,以'%'开头的查询值不能使用索引。
select * from user where id like 'A%'; // 前缀-使用索引查询 select * from user where id like '%A%'; // 中缀-使用全表查询 select * from user where id like '%A'; // 后缀-使用全表查询
3. 在范围查询中,MySQL最多只能用到一个范围列参与索引,且遇到范围列后停止最左匹配。(即索引树中范围列后的列将不会参与最左匹配。)
这里的范围列指的是<、>、<=、>=
对于查询条件where id = 1 and name = "laowang" and age > 20 and descript = "靓仔",
如果建立(id, name, age, descript)的联合索引,那么descript是用不到索引的,因为在本查询中age是范围列,索引树中age后面的列将不会参与最左匹配。
如果建立(id, name, descript, age)的联合索引,那么id、name、descript、age都能用到索引(尽管在查询中age在descript之前,但是在执行时MySQL的优化器会调整顺序)。
对于查询条件where id > 10 order by name,若建立(id, name)的联合索引,只能用到索引id,而不能用到索引id_name。
整理索引碎片
在长时间使用MySQL后,增删操作会导致索引碎片,导致查询时IO次数增多而影响查询效率。定期清理索引碎片可以在一定程度上解决该问题(如果碎片太多导致整理时间过长,或可尝试删除并重建索引)。
注:MySQL中清理索引碎片的命令为OPTIMIZE table_name。
例子:对于用户表user(id, name, age, gender, descript),其中id为主键。
1. 下列选项中利用了索引的有
1. 查询用户姓名的个数
select count(`name`) from user;
2. 对name添加索引后查询zhangsan的ID
alter table `user` add index NAME(`name`); select `id`, `name` from `user` where `name` = "zhangsan";
3. 根据ID查询ID为3的用户信息
select `id`, `name`, `age`, `gender` from `user` where `id` = 3;
答案:B、C。A未能利用索引,因为原user表中只有主键id默认建立了索引。B利用了索引,因为对name建立了索引,在InnoDB中建立普通索引后,索引树的叶子节点中保存了数据的主键,在user表中就是id字段,故在name的索引树可以直接得到id、name这两个信息。C利用了索引,这个就不解释了。
2. 对下列操作进行优化:
对name添加索引后查询zhangsan的年龄
alter table `user` add index NAME(`name`); select `id`, `age`, `name` from `user` where `name` = "zhangsan";
优化:建立age、name的联合索引。create index AGE_NAME on user(age, name)。仅对name建立单列索引后,根据name查询age时还需要一次回表操作,对age、name建立联合索引后搜索一次索引树即可得到age、name数据.
3. 在表上建立联合索引index ID_NAME_AGE(id, name, age),下列中用到索引的有:
select * from user where id = 1; select * from user where name = "xiaoming"; select * from user where id = 1 and name = "laowang"; select * from user where id = 1 and age = 20; select * from user where id = 1 and name = "laowang" and age = 20; select * from user where age = 20 and name = "laowang" and id = 3; select * from user where name = "laowang" and age = 20;
答案:A、C、D、E、F。
A:最左匹配(id);
B:不符合最左匹配原则,故无法使用索引(查询条件中没有最左边的列id);
C:最左匹配(id, name);
D:最左匹配(id),对于查询条件age,由于age和id并不相邻,故使用id查询完成后无法继续使用age进行查询,所以尽管这里查询条件是id和age,但实际上只能匹配到id;
E:最左匹配(id, name, age);
F:最左匹配(id, name, age),尽管查询条件并不符合索引的顺序,但在执行时MySQL会对查询语句进行优化;
G:不符合最左匹配原则,故无法使用索引(和B的问题一样)。
注意:在上题中B、G项尽管没有使用索引进行匹配,但也在一定程度上提高了查询速度——当查询name、 name_age时,MySQL通过扫描整棵索引而不是全表扫描进行查找,能够减少IO次数。