mysql优化之索引篇

大家在面试的过程中经常被问到关于 MySQL索引的优化。

1.什么是索引

MySQL 的索引是在存储引擎层实现的（本文主要讲 InnoDB），不同的存储引擎会使用不同的索引，InnoDB 默认使用 B+TREE 索引，如下图所示：
B+ 树只有叶子节点才会存储数据，而且存储的数据都是在一行上，而且这些数据都是有指针指向的，也就是有顺序的。这也就是为什么我们 order by 的时候为什么要加索引了。

InnoDB 中的索引为聚集索引，也就是说也就是说数据和索引是在一起。

2.主键索引

下图就是Innodb的主键索引。

3. 辅助索引（次要索引）

下图为什么 InnoDB 的辅助索引示意图，从图中可以看到辅助索引存放的数据记录为主键，所以当我们通过辅助索引去查询数据的时候会先通过索引数据查询到主键的值，然后再通过主键去查询其他的值，这种方式称之为“回表查询”。实际上所有的辅助索引都会引用主键索引，所以在表结构设计中主键索引不宜设置过长，不然空间开销会比较大。

在下面这个表中使用如下语句的执行过程是怎么样的呢？

select * from myindex  where age between  25 and 26；

建表语句如下：

create table myindex (
ID int primary key,
age int NOT NULL DEFAULT 0, 
name varchar(16) NOT NULL DEFAULT '',
index age(age))
engine=InnoDB;

insert into myindex values(1,20, '张三'),(2,25,'李四'),(3,30,'王五'),(5,26,'朱六'),(6,27,'桌七'),(7,37,'赵八');

现在，我们一起来看看这条 SQL 查询语句的执行流程：

1. 在 age 索引树上找到 age=25 的记录，得到 id = 2；
2. 再到 id 索引树查到 id=2 对应的 数据；
3. 在 age 索引树取下一个值 age=26，取得 id=4；
4. 再回到 id 索引树查到 id=4 对应的 数据；
5. 在 age 索引树取下一个值 age=27，不满足条件，循环结束。

在这个流程当中先通过 age 获取到 id，再通过 id 获取数据的过程称之为回表查询，假如我们是直接通过主键索引来查询那就不需要回表了，但是在大多数场景中我们都需要用到非主键索引来查询，那么可不可以不会表呢，答案是可以的，那就是使用索引覆盖，那么什么叫索引覆盖呢，例如我们改下上面的查询语句，改为：

 select  id from myindex  where age between  25 and 26；

这时候当我们再执行上面的第一步的时候得到了 id=2 就是我们需要的数据，这时候就不需要回表了，这就是索引覆盖，通俗的讲就是索引已经覆盖了我需要的数据了，那么下面的语句怎么做到索引覆盖呢？

 select  name from myindex  where age between  25 and 26；

这时候索引中并没有 name 的值，在这种场景下就需要用到组合索引了，创建组合索引（age、name），当我们根据 age 去查询 name 的时候由于索引树上已经存在了 age 和 name，这时候就不需要回表了，那么组合索引只是用于索引覆盖吗？当然不是，我们在实际工作中应尽量使用组合索引，例如当我们再增加一个字段 sex，我们建立一个组合索引（age、name、sex）这时候其实就相当于建立了三个索引，分别为 age，age、name，age、name、sex，想想看这时候一个索引树同时实现了三个索引，大大节省了空间。

而且 MySQL 创建组合索引（age、name）的规则是首先会对组合索引的最左边的，也就是第一个 age 字段的数据进行排序，在第一个字段的排序基础上，然后再对后面第二个的 name 字段进行排序。其实就相当于实现了类似 order by age name 这样一种排序规则，这时候对于需要用到排序的时候也是有益的 为什么我们上面说的三个索引是 age, （age、name）, （ age、name、sex）呢？而没有（name、sex）呢，这就是组合索引中的最左匹配原则，当我们使用下面的语句的时候，其实索引已经失效了，我们再为表增加两个字段：

ALTER TABLE myindex add COLUMN remark varchar(32);
ALTER TABLE myindex add COLUMN birthady datetime;

当我们执行如下语句的时候其实已经索引失效了，可以通过 explain 来查看执行计划，这里 type 为 all 代表为全盘扫描，说明索引失效了，关于 explain 后面会详细讲解。

select  * from myindex  where name='张三'；

当我们查询条件中超过了我们设置的组合索引的时候会怎么样呢？我们以（age、name）组合索引为例，当我们执行如下语句的时候会如何？

select  * from myindex  where age=20 and name='张三' and sex=1；

我们可以看下执行计划，可以看到 Using index condition，这就是索引下推，怎么理解呢?就是查询的时候会先从索引中找到符合 age=20 和 name='张三' 的记录，然后再回表比较其他的条件，这样就减少了回表的次数。

4.explain 执行计划

MySQL 提供了一个 EXPLAIN 命令, 它可以对 SELECT 语句的执行计划进行分析，并输出 SELECT 执行的详细信息, 以供开发人员针对性优化。

使用 explain 这个命令来查看一个这些 SQL 语句的执行计划，查看该 SQL 语句有没有使用上了索引，有没有做全表扫描，这都可以通过 explain 命令来查看。

可以通过 explain 命令深入了解 MySQL 的基于开销的优化器，还可以获得很多可能被优化器考虑到的访问策略的细节，以及当运行 SQL 语句时哪种策略预计会被优化器采用。

EXPLAIN 命令用法十分简单，在 SELECT 语句前加上 explain 就可以了。

通过前面的内容我们知道 expain 出来的信息有 10 列，分别是 id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra。

id

每个 SELECT 语句都会自动分配的一个唯一标识符表示查询中操作表的顺序，有三种情况：

• id 相同：执行顺序由上到下
• id 不同：如果是子查询，id 号会自增，id 越大，优先级越高
• id 相同的不同的同时存在

id 列为 null 的就表示这是一个结果集，不需要使用它来进行查询。

select_type

查询类型，主要用于区别普通查询、联合查询（union、union all）、子查询等复杂查询。

• simple：表示不需要 union 操作或者不包含子查询的简单 select 查询。有连接查询时，外层的查询为 simple，且只有一个。
• primary：一个需要 union 操作或者含有子查询的 select，位于最外层的单位查询的 select_type 即为 primary。且只有一个。
• subquery：除了 from 字句中包含的子查询外，其他地方出现的子查询都可能是 subquery。
• union：union 连接的两个 select 查询，第一个查询是 PRIMARY，除了第一个表外，第二个以后的表 select_type 都是 union。
• dependent union：与 union 一样，出现在 union 或 union all 语句中，但是这个查询要受到外部查询的影响。

• dependent subquery：与 dependent union 类似，表示这个 subquery 的查询要受到外部表查询的影响。
• union result：包含 union 的结果集，在 union 和 union all 语句中,因为它不需要参与查询，所以 id 字段为 null。
• derived：from 字句中出现的子查询，也叫做派生表，其他数据库中可能叫做内联视图或嵌套 select。

table

• 显示的查询表名，如果查询使用了别名，那么这里显示的是别名。
• 如果不涉及对数据表的操作，那么这显示为 null。
• 如果显示为尖括号括起来的就表示这个是临时表，后边的 N 就是执行计划中的 id，表示结果来自于这个查询产生。
• 如果是尖括号括起来的 <union M,N>，与类似，也是一个临时表，表示这个结果来自于 union 查询的 id 为“M,N”的结果集。

type

从好到坏分别为：

system、const、eq_ref、ref、fulltext、ref_or_null、unique_subquery、index_subquery、range、index_merge、index、ALL

除了 all 之外，其他的 type 都可以使用到索引，除了 index_merge 之外，其他的 type 只可以用到一个索引。

• system：表中只有一行数据或者是空表。
• const：使用唯一索引或者主键，返回记录一定是 1 行记录的等值 where 条件时，通常 type 是 const。其他数据库也叫做唯一索引扫描
• eq_ref：关键字、连接字段主键或者唯一性索引。此类型通常出现在多表的 join 查询，表示对于前表的每一个结果，都只能匹配到后表的一行结果。并且查询的比较操作，通常是 '=' 查询效率较高。
• ref：针对非唯一性索引，使用等值（=）查询非主键。或者是使用了最左前缀规则索引的查询。
• fulltext：全文索引检索，要注意，全文索引的优先级很高，若全文索引和普通索引同时存在时，MySQL 不管代价，优先选择使用全文索引。
• ref_or_null：与 ref 方法类似，只是增加了 null 值的比较。实际用的不多。
• unique_subquery：用于 where 中的 in 形式子查询，子查询返回不重复值唯一值
• index_subquery：用于 in 形式子查询使用到了辅助索引或者 in 常数列表，子查询可能返回重复值，可以使用索引将子查询去重。
• range：索引范围扫描，常见于使用>,<,is null,between ,in ,like 等运算符的查询中。
• index_merge：表示查询使用了两个以上的索引，最后取交集或者并集，常见 and、or 的条件使用了不同的索引，官方排序这个在 ref_or_null 之后，但是实际上由于要读取所个索引，性能可能大部分时间都不如 range。
• index：关键字：条件是出现在索引树中的节点的。可能没有完全匹配索引。索引全表扫描，把索引从头到尾扫一遍，常见于使用索引列就可以处理不需要读取数据文件的查询、可以使用索引排序或者分组的查询。
• all：这个就是全表扫描数据文件，然后再在 server 层进行过滤返回符合要求的记录。

possible_keys

此次查询中可能选用的索引，一个或多个。

key

查询真正使用到的索引，select_type 为 index_merge 时，这里可能出现两个以上的索引，其他的 select_type 这里只会出现一个。

key_len

用于处理查询的索引长度，如果是单列索引，那就整个索引长度算进去，如果是多列索引，那么查询不一定都能使用到所有的列，具体使用到了多少个列的索引，这里就会计算进去，没有使用到的列，这里不会计算进去。留意下这个列的值，算一下你的多列索引总长度就知道有没有使用到所有的列了。另外，key_len 只计算 where 条件用到的索引长度，而排序和分组就算用到了索引，也不会计算到 key_len 中。

ref

• 如果是使用的常数等值查询，这里会显示 const
• 如果是连接查询，被驱动表的执行计划这里会显示驱动表的关联字段
• 如果是条件使用了表达式或者函数，或者条件列发生了内部隐式转换，这里可能显示为 func

rows

这里是执行计划中估算的扫描行数，不是精确值（InnoDB 不是精确的值，MyISAM 是精确的值，主要原因是 InnoDB 里面使用了 MVCC 并发机制）。

extra

这个列包含不适合在其他列中显示单十分重要的额外的信息，这个列可以显示的信息非常多，有几十种，常用的有如下：

• distinct：在 select 部分使用了 distinct 关键字。
• using filesort：排序时无法使用到索引时，就会出现这个。常见于 order by 和 group by 语句中。说明 MySQL 会使用一个外部的索引排序，而不是按照索引顺序进行读取。MySQL 中无法利用索引完成的排序操作称为“文件排序”。
• using index：查询时不需要回表查询，直接通过索引就可以获取查询的数据。表示相应的 SELECT 查询中使用到了覆盖索引（Covering Index），避免访问表的数据行，效率很好。如果同时出现 Using Where，说明索引被用来执行查找索引键值。如果没有同时出现 Using Where，表明索引用来读取数据而非执行查找动作。
• using intersect：表示使用 and 的各个索引的条件时，该信息表示是从处理结果获取交集。
• using union：表示使用 or 连接各个使用索引的条件时，该信息表示从处理结果获取并集。
• using sort_union 和 using sort_intersection：与前面两个对应的类似，只是他们是出现在用 and 和 or 查询信息量大时，先查询主键，然后进行排序合并后，才能读取记录并返回。
• using temporary：表示使用了临时表存储中间结果。MySQL 在对查询结果 order by 和 group by 时使用临时表，临时表可以是内存临时表和磁盘临时表，执行计划中看不出来，需要查看 status 变量 used_tmp_table、used_tmp_disk_table 才能看出来。
• using where：表示存储引擎返回的记录并不是所有的都满足查询条件，需要在 server 层进行过滤。
• using index condition：查询条件中分为限制条件和检查条件，5.6 之前，存储引擎只能根据限制条件扫描数据并返回，然后 server 层根据检查条件进行过滤再返回真正符合查询的数据。5.6.x 之后支持 ICP 特性，可以把检查条件也下推到存储引擎层，不符合检查条件和限制条件的数据，直接不读取，这样就大大减少了存储引擎扫描的记录数量。

5.索引使用的场景

在我们实际的工作中要根据具体的业务场景来设计索引，这样才能充分利用索引的特性，没有合理的使用索引可能还会导致性能变差，例如频繁进行增删改操作的表，而读操作很少的表，就要慎重使用索引了，因为索引会加锁。

6.什么情况下需要索引

• 主键，自动建立唯一索引
• 频繁作为查询条件的字段应该创建索引
• 多表关联查询中，关联字段应该创建索引 on 两边都要创建索引
• 查询中排序的字段，应该创建索引
• 频繁查找字段 覆盖索引
• 查询中统计或者分组字段，应该创建索引 group by

7.什么情况下不需要索引

• 表记录太少
• 经常进行增删改操作的表
• 频繁更新的字段
• where 条件里使用频率不高的字段

8.索引失效与合理的索引

1. 最左匹配

前面的章节有讲到当我们使用组合索引时要遵循最左匹配原则，当使用（age、name、sex）时假如查询时去掉了 age，例如下面的语句就会造成索引失效，那么这是为什么呢？因为当我们建立索引的时候，索引是有序的，默认按照第一个字段排序，也就是 age，然后是 name、sex。所以当我们没有 age 的时候就无法从索引树上快速查找了。

select * from myindex where name='张三'  and sex=1;

2. 中间索引不能断

下面的语句，其实只会用到 age 索引，没有用到 sex，这是因为在使用组合索引的时候，假如中间断了，后面的索引就会失效。

select * from myindex where age=20  and sex=1;

3. 禁止在索引上做运算等操作

不能在索引上做任何操作，如计算、函数、类型转换等，如下面的语句使用到了类型转换就会造成索引失效，因为实际上类型转换会使用 cast 函数：

select * from myindex where age='20'  ;

4. 范围条件右边的列失效

不能继续使用索引中范围条件（bettween、<、>、in 等）右边的列,如下语句，sex 索引没有用上：

select * from myindex where name='张三' and age>20 and sex=1;

5. 索引字段上不要使用不等

索引字段上使用（!= 或者 < >）判断时，会导致索引失效而转向全表扫描。

6. 主键索引字段上不可以判断 null

主键字段上不可以使用 null 索引字段上使用 is null / is not null 判断时，可使用索引。

7. 索引字段使用 like 不以通配符开头

索引字段使用 like 以通配符开头（'%字符串'）时，会导致索引失效而转向全表扫描。

8. 索引字段不要使用 or

索引字段使用 or 时，会导致索引失效而转向全表扫描。