一文搞懂mysql中的索引(下)

上一篇文章(一文搞懂mysql中的索引)中讨论了索引的基本原理和mysql中的聚集索引，下面我们一起来学习下，非聚簇索引相关内容。

因为非聚簇索引的叶子结点上存放的是数据表主键值，而完整的行数据存放在聚簇索引中。所以，如果要使用非聚簇索引查找完整的行数据，需要先找到主键值，然后再通过聚簇索引，找到完整的数据。

在这个过程中，通过聚簇索引查找完整数据的过程，称为回表，简单来说，回表就是再去聚簇索引中查询一次索引树。

在日常使用中，非聚簇索引又可以分为如下三类：唯一索引，普通索引，联合索引。

唯一索引

唯一索引在支持快速定位数据的基础上，还提供了保证索引字段在数据表中唯一性的能力。这为我们在业务代码中维护数据唯一性的基础上，提供了数据唯一性的最后一道屏障，在进行新增修改数据时，数据库检测到存在数据不唯一的时候，会出现"违反数据唯一性约束"的报错，来保证保存到数据表中数据的正确性。

普通索引

普通索引就是基于某个字段建立的索引，没有什么特殊之处。其实唯一索引可以看做是一种特殊的普通索引，只是在普通索引的基础上，增加了唯一性限制，不过这里有一个问题：如果我们可以在业务代码上保证数据的唯一性，那么是否就可以不需要唯一索引了呢？关于这个问题可以参考作者的这个文章：面试官问：普通索引和唯一索引该怎么选择

联合索引

很多时候在使用数据库进行查询时，会使用多个条件进行联合查询，联合索引的出现主要就是为了提高多条件查询效率。

为了方便下文描述，建立如下测试表结构：

CREATE TABLE `ds_table` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `ds` varchar(20)  DEFAULT NULL,
  `age` int(11)  DEFAULT NULL,
  `name` varchar(11) DEFAULT NULL,
  `last_name` varchar(11) DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  key uoinonKey(`ds`,`age`,`name`)
) ENGINE=InnoDB;

建立名称为 ds_table的数据表，使用字段 ds，age，name 作为联合主键。
使用 存储过程i_procedure插入1000条数据：

create procedure i_procedure()
begin
   declare i int;
   set i=1;
   while(i<=200)do
   	insert into `ds_table` (ds,age,name,last_name ) values('2021-06-07',1,'name_1','lst_name1');
   	insert into `ds_table` (ds,age,name,last_name ) values('2021-06-07',2,'name_2','lst_name2');
   	insert into `ds_table` (ds,age,name,last_name ) values('2021-06-08',1,'name_1','lst_name1');
   	insert into `ds_table` (ds,age,name,last_name ) values('2021-06-08',2,'name_2','lst_name2');
   	insert into `ds_table` (ds,age,name,last_name ) values('2021-06-09',1,'name_1','lst_name1');
   	set i=i+1;
   end while;
end;;
call i_procedure();

最左前缀

首先需要强调一下，索引之所以可以提高数据查询效率，是因为索引字段在索引数据结构中是有序的。在联合索引中，多个索引字段的的排序规则是先按照第一个字段进行排序，如果第一个字段相同，在按照第二个字段排序，如果第二个字段相同，在按照第三个字段排序，以此类推。

因为最左边的索引字段是有序的，所以当使用最左端的字段进行数据查询时，也可以使用该联合索引。

除此之外，因为第二个索引的有序性是建立在第一个索引字段的相同的基础上的。所以使用联合索引时，必须先使用最左边字段的索引进行数据的定位。

基于联合索引的这种数据组织形式，在不使用第一个字段检索的基础上，无法使用第二个字段进行数据检索。

对此可以做以下实验进行验证，分别执行以下sql进行验证，

select count(1) from ds_table where ds = '2021-06-07' and age =1;
和
select count(1) from ds_table where age =1;

因为测试数据表中数据量比较少，直接验证sql执行时间难以进行区分，我们可以通过统计，每个sql语句在执行过程中innodb扫描数据行数，来进行比较。统计一条sql语句执行过程中，扫描数据行数的语句如下：

/* 打开optimizer_trace，只对本线程有效 */
SET optimizer_trace='enabled=on'; 

/* @a保存Innodb_rows_read的初始值 */
select VARIABLE_VALUE into @a from  performance_schema.session_status where variable_name = 'Innodb_rows_read';

/* 执行语句 */
xxx (被验证的查询语句)

/* @b保存Innodb_rows_read的当前值 */
select VARIABLE_VALUE into @b from performance_schema.session_status where variable_name = 'Innodb_rows_read';

/* 计算Innodb_rows_read差值 */
select @b-@a;

经过验证可以发现，执行sql

select count(1) from ds_table where ds = '2021-06-07' and age =1;

扫描数据行数为：200，使用了索引uoinonKey。

执行sql：

select count(1) from ds_table where age =1;

扫描数据行数为：1000，没有使用使用索引，走了全表扫描。

除了这种验证方式外，也可以直接使用 explain 来查看sql语句的执行计划，查看索引使用情况。

覆盖索引

覆盖索引并不是一种索引，而是联合索引的一种使用场景。上文中说过，非聚簇索引的叶子节点存放的数据是主键，要想查找完整的数据行，需要使用查找到主键，到聚簇索引中进行二次查找，这个二次查找的过程被称为回表。

但是很多时候，我们并不需要完整的数据行，而是某几个字段的值。如果这几个字段刚好在索引中，那么就可以直接从索引中取出即可，也就是说，索引中的字段覆盖了需要查询的字段。是查询过程不需要进行回表操作。

如果查询语句如下：

select ds,age from ds_table where ds= '2021-06-07' and age=1;

因为联合索引中已经包含了 ds和age两列，而查询语句中也只需要ds和age两个字段数据，所以这种情况是不需要回表。可以通过查看查询语句的执行计划进行判断，如图：

在查询计划中，extra中只有using index；

索引下推

索引下推也是mysql减少回表的一种优化手段。

由于最左前缀的限制，联合索引中只有最左边的字段可以使用索引进行检索，如下sql语句查询：

select last_name from ds_table where ds = '2021-06-07' and name = 'name1';

只能使用联合索引中的ds字段进行检索，在没有索引下推的情况下，mysql需要扫描400行数据(因为满足 ds = '2021-06-07’条件的有400行)，因为要判断 name='name1’的条件是否满足，需要回表400次，取出400行完整数据，对name='name1’的条件进行判断，最终选出其中的200行数据的last_name字段。

而有了索引下推机制后，对name='name1’条件的判断，就不需要回表了，直接就可以利用联合索引中的name字段进行判断。这样利用联合索引，就可以直接定位到满足条件的200行数据，此时，只需要对这200行数数据进行回表获取last_name字段即可。

范围查询会使联合索引失效

经常在其他资料上看到，范围查询会使联合索引失效，其实这个说法是不全对的，我们可以做一下实验进行验证。

1.范围查询使联合索引失效

我们执行以下sql查询

explain select age from ds_table where ds<= '2021-06-08' and ds>= '2021-06-07' order by age;

在sql的执行计划中可以看到排序使用到了 filesort，虽然这个sql使用到了联合索引，但是ds后的age字段索引，却失效了。

而如果ds条件变成了等值查询的话，如下：

explain select * from ds_table where ds= '2021-06-08' order by age;

在执行计划中，extra中没有了filesort，说明排序使用到了联合索引中的age字段，对mysql中排序流程不清楚的老铁，可以参考如何优化sql中的orderBy？

2.联合索引快速定位查询位置

上面的排序过程虽然表明了，在联合索引中，索引字段的范围查询会对导致该索引字段后面的索引字段失效。但是这个失效也不是绝对的，也就是说，ds的范围查询，并不会使 ds，age组成的联合索引退化成ds的普通索引。

我们可以使用如下实验进行验证。执行如下sql语句：

select count(1) from ds_table where ds<= '2021-06-08' and ds>= '2021-06-07' and age =2; 

如果对于只有ds一个字段的索引来说，该条sql语句在执行过程中，innodb引擎会扫描800行数据，但是对于ds和age组成的联合索引来说只会扫描600行。因为在扫描数据时，mysql会使用联合索引直接定位 ds=2021-06-07，age=2的第一条数据的位置，然后逐个向后遍历，遍历600条数据后，遇到ds=2021-06-09，(也就是图中的红色箭头)终止遍历。如下图：