正确使用索引 - Rocky's blog

在日常开发中,遇到 Mysql 查询慢,”索引”是我们最经常使用的一个技术,索引可以加快读取数据的速度,但是我们要知道索引并不是乱加的.如果使用不好还有可能适得其反.拖垮整个数据库.
我们需要了解一下几个常用的知识点

回表

假如有这么一个表:

mysql> create table student (
ID int primary key,
id_card int NOT NULL DEFAULT 0, 
name varchar(16) NOT NULL DEFAULT '',
age int NOT NULL 0,
index id_card(id_card)
)
engine=InnoDB;
#插入以下数据
insert into student values(10,1, 'a'),(20,2,'b'),(40,3,'c'),(50,5,'d'),(60,6,'e'),(70,7,'f');

执行 select * from student where id_card between 3 and 5, 需要执行几次树的搜索操作，会扫描多少行？

我们先来看一下这个语句的执行流程

在 id_card 索引树上找到 id_card=3 的记录，索引上存着主键取得ID = 40；
再到主键索引树查到 ID=40 对应的记录；
在 id_card 索引树取下一个值 k=5，取得 ID=50；
再回到 ID 索引树查到 ID=50 对应的记录；
在 id_card 索引树取下一个值 k=6，不满足条件，循环结束。

在这个过程中，回到主键索引树搜索的过程，我们称为回表。可以看到，这个查询过程读了 id_card 索引树的 3 条记录（步骤 1、3 和 5），回表了两次（步骤 2 和 4）。

回表次数越多,效率越低

联合索引

两个或更多个列上的索引被称作联合索引，联合索引又叫复合索引。对于复合索引,Mysql 从左到右的使用索引中的字段，一个查询可以只使用索引中的一部份，但只能是最左侧部分。例如索引是key index (a,b,c). 可以支持 a | a,b| a,b,c 3种组合进行查找，但不支持 b,c进行查找 .当最左侧字段是常量引用时，索引就十分有效。

联合索引配合 覆盖索引 和 最左前缀 是最常用的优化手段. 可以满足很多场景下的索引需求.

覆盖索引

上面的例子中,因为我们查找的字段是 * 所以会造成回表, 如果我们是 select id from student where id_card between 3 and 5 则不需要回表, 当索引满足了我们的查询请求而不需要回表时,我们称为 覆盖索引

覆盖索引可以避免回表查询,所以可以有效的的提高查询效率, 使用覆盖索引来提高查询效率是我们常用的一种优化手段.

最左前缀原则

在 Mysql 建立联合索引时会遵循最左前缀匹配的原则，即最左优先，在检索数据时从联合索引的最左边开始匹配

示例：
对列col1、列col2和列col3建一个联合索引

KEY test_col1_col2_col3 on test(col1,col2,col3);

联合索引 test_col1_col2_col3 实际建立了(col1)、(col1,col2)、(col,col2,col3)三个索引。
所以一下3个语句都可以走索引

#上面这个查询语句执行时会依照最左前缀匹配原则，检索时会使用索引(col1,col2)进行数据匹配。
SELECT * FROM test WHERE col1=“1” AND clo2=“2” AND clo4=“4”

SELECT * FROM test WHERE col1=“1” AND clo2=“2”
# 与查询的顺序无关,优化器会执行最优 执行路径.
SELECT * FROM test WHERE col2=“2” AND clo1=“1”

注意

在建立联合索引的时候，如何安排索引内的字段顺序?

这里我们的评估标准是，索引的复用能力。

因为可以支持最左前缀，所以当已经有了 (col1,col2) 这个联合索引后，一般就不需要单独在 col1 上建立索引了。因此，第一原则是，如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的。

那么，如果既有联合查询，又有基于 col1、 col2各自的查询呢？查询条件里面只有 b 的语句，是无法使用 (a,b) 这个联合索引的，这时候你不得不维护另外一个索引，也就是说你需要同时维护 (col1,col2)、(col2) 这两个索引。这时候，我们要考虑的原则就是空间了。比如上面这个学生表的情况，从存储的角度来说 name 字段是比 age 字段大的，那我就建议你创建一个（name,age) 的联合索引和一个 (age) 的单字段索引。

索引下推

索引下推优化（index condition pushdown)是在 MySQL 5.6之后引入的,可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

例如

1 2	`#有一个联合索引（name, age） select * from student where name like '张%' and age=10 and ismale=1;`

在 Mysql5.6 之前,搜索索引树的时候，只能用 “张”，找到第一个满足条件的记录。然后只能从第一个记录开始一个个回表。到主键索引上找出数据行，再对比字段值。

在 Mysql5.6 之后,搜索索引树的时候，用 “张”，找到第一个满足条件的记录。然后在索引遍历过程中，对索引中包含的字段先做判断，以sql 为例 age != 10 的数据不会再回表,直接过滤掉不满足条件的记录，从而大大减少了回表次数。