数据库索引（3）：索引优化手段

开发中常用的索引优化手段①使用索引覆盖，避免回表，②遵循最左前缀原则，避免索引失效，③定期整理索引碎片。

使用索引覆盖

原理：索引覆盖可以可以避免回表操作，从而提高性能。

索引覆盖：索引覆盖指索引中包含了要查询的全部字段，在查询时，从索引中即能得到查询结果，无需读取记录数据。当使用的是非聚簇索引时，无需读取记录数据那么就避免了回表操作。

避免回表：回表指使用非聚簇索引时，一次select需要执行两次查询：先从非聚簇索引树中查到目标记录的主键，在根据主键值在聚簇索引树中查到真正的记录数据。

方法：

1. 尽量使用主键查询查找数据，因为主键默认建立索引，通过主键查询信息时不仅会用到索引，而且不会回表。

2. 对于常用的单个查询关键字建立单列索引，对于常用的多个关键字查询建立组合索引，用以实现索引覆盖。

注意：尽管使用联合索引能加快查找速度，但过度使用会导致前面提到的三个问题（时间、空间、碎片）。

使用explain命令时，若使用索引覆盖，会有一个Using index标志

 

使用最左前缀

对联合索引进行查询时，若查询条件不满足最左前缀原则，将扫描整个索引树，而不是利用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次数。