【MySQL】索引原理（三）：联合索引（最左前缀原则），覆盖索引，索引条件下推

àì夳堔傛蜴生んèń 2022-11-21 01:21 309阅读 0赞

准备工作，下面的演示都是基于user\_innodb表：

DROP TABLE IF EXISTS `user_innodb`;
    CREATE TABLE `user_innodb` (
      `id` bigint(64) NOT NULL AUTO_INCREMENT,
      `name` varchar(255) NOT NULL,
      `gender` tinyint(1) NOT NULL,
      `phone` varchar(11) NOT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

## 1.联合索引与最左前缀原则 ##

在平时开发中，我们最常见的是单列索引（比如主键primary key），但在我们需要多条件查询的时候，也会建立联合索引。单列索引可以看成是特殊的联合索引。比如我们在user表上面，给name和phone建立了一个联合索引。

> ALTER TABLE 可以用来创建索引，包括普通索引、UNIQUE索引或PRIMARY KEY索引：
> 
>  *  ALTER TABLE table\_name ADD INDEX index\_name (column\_list)
>  *  ALTER TABLE table\_name ADD UNIQUE (column\_list)
>  *  ALTER TABLE table\_name ADD PRIMARY KEY (column\_list)

ALTER TABLE user_innodb add INDEX comidx_name_phone(name,phone); -- 创建联合索引

### 1.1 联合索引是怎么组织的？ ###

下图是B+Tree的索引结构，是一个单值索引：

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzkzNTkyNw_size_16_color_FFFFFF_t_70_pic_center]  
我们可以看到，所有非叶子结点的构成都是由两部分组成，索引值+指针，而单值索引说的就是在索引值这里就只有一个值（比如id），而联合索引在索引值可能会有多个值（比如name和phone）

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzkzNTkyNw_size_16_color_FFFFFF_t_70_pic_center 1]

相比于单值索引：

1.  联合索引在 B+Tree 中是复合的数据结构
2.  由于 B+树本身是有序的，所以联合索引是从左到右的顺序来建立搜索树的（name在左边，phone在右边）。从上图可以看出来，name是有序的，phone是无序的。当name相等的时候，phone才是有序的。
3.  当存储引擎是InnoDB时，叶节点存储的是数据/主键

**问题一**：联合索引是怎么查找数据的？

比如，我们使用 where name=‘Bob’ and phone = ‘132xx’ 去查询数据的时候

1.  B+Tree 会优先比较 name 来确定下一步应该搜索的方向，往左还是往右
2.  如果 name相同的时候再比较 phone

但是如果查询条件没有name，就不知道第一步应该查哪个节点，因为建立搜索树的时候name是第一个比较因子，所以用不到索引。

**问题二**：联合索引与单值索引什么关系？

假设我们的项目里面有两个查询很慢：

SELECT * FROM user_innodb WHERE name= ?;
    SELECT * FROM user_innodb WHERE name= ? AND phone=?;

按照我们的想法，一个查询创建一个索引，所以我们针对这两条SQL创建了两个索引，这种做法觉得正确吗？

CREATE INDEX idx_name on user_innodb(name); 
    CREATE INDEX idx_name_phoneonuser_innodb(name,phone);

当我们创建一个联合索引的时候，用左边的字段name去查询的时候，也能用到索引，所以单独为name创建一个索引完全没必要。相当于建立了两个联合索引(name)、(name,phone)。

如果我们创建三个字段的索引index(a,b,c)，相当于创建三个索引：index(a)、index(a,b)、index(a,b,c)。用 where b=? 和 where b=? and c=? 和where a=? and c=?是不能使用到索引的。因为不能不用第一个字段，不能中断。

### 1.2 最左前缀原则 ###

> 因为联合索引中包含了多个字段，所以不能像单值索引那样直接使用就行。那需要遵守什么规则呢？  
> 答：**最左前缀原则**：带头大哥不能死，中间兄弟不能断。

我们在建立联合索引的时候，一定要把最常用的列放在最左边。比如下面的三条语句，能用到联合索引吗？

1.  使用两个字段，可以用到联合索引（注：两个字段的顺序颠倒并不影响，因为全值匹配时mysql会优化字段顺序）

EXPLAIN SELECT * FROM user_innodb WHERE name= '张三' AND phone='12345678910'

![在这里插入图片描述][20201029153811993.png_pic_center]

1.  使用左边的name字段，可以用到联合索引：

EXPLAIN SELECT * FROM user_innodb WHERE name= '张三'

![在这里插入图片描述][20201029153827889.png_pic_center]

1.  使用右边的phone字段，无法使用索引，全表扫描：

EXPLAIN SELECT * FROM user_innodb WHERE name= '12345678910'

![在这里插入图片描述][20201029153840249.png_pic_center]

> 从联合索引的结构中，我们看到了索引是已经排好序的，那我们如在遵守最左前缀原则的前提下，order by时用到索引(避免 filesort)？
> 
>  *  使用形式一：order by 索引最前列 ==> 整体有序。
>  *  使用形式二：where + order by索引列 ==> 局部有序（为了最左前缀原则，尽量order by索引列（用where 保证中间不断开）)
> 
>   
> 再说一句，分组（group by）的实质是先排序后分组，原则类似order by。where 高于 having，where中能限定但条件不要去having中限定

另外，不要在索引上做任何操作，因为可能会导致索引失效，转而全表扫描。

### 1.3 什么情况下会索引失效？ ###

当索引列出现以下六种操作时常常出现索引失效：

1.  使用函数（replace\\SUBSTR\\CONCAT\\sum count avg）、表达式、计算（+ - \* /）。因为当前值改变后就无法与索引存的值匹配上。

SELECT * FROM user_innodb where left(name, 3)='张三'；-- left函数是一个字符串函数，它返回具有指定长度的字符串的左边部分

1.  使用范围查询(!=,<=>,in)会导致右边列失效。因为二叉树的查找是 = 查找，若是一个范围的话无法继续下探。
    
     *  最左列用范围，该列也不会使用索引，全部索引列失效
     *  其余列用范围，当前列仍会使用索引，但右边索引列失效

SELECT * FROM user_innodb where name='张三' and age > 22；

1.  like以通配符开头（’%abc…’），mysql索引失效会变成全表扫描操作。因为无法判断%代表多少字符。
    
     *  方案一：like (‘abc%’)
     *  方案二：覆盖索引

SELECT * FROM user_innodb where name like '%三'；

1.  字符串不加’ '索引失效。因为会出现出现隐式转换，相当于给索引列做了操作。

SELECT * FROM user_innodb where name = 007；-- "007"从字符串变成了数字007

1.  少用or，用它连接时很多情况下索引会失效

SELECT * FROM user_innodb where name = '张三' or name = '李四'；

1.  is null，is not null 无法使用索引

SELECT * FROM user_innodb where name is null；

==> 对这一部分内容通过一首打油诗做个总结：

全值匹配我最爱，最左前缀要遵守
    							带头大哥不能死，中间兄弟不能断
    							索引列上少计算，范围之后全失效
    							like百分写最右，覆盖索引不写星
    						    不等空值还有or，索引失效要少用

## 2.覆盖索引 ##

> 回表：非主键索引，我们先通过索引找到主键索引的键值，再通过主键值查出索引里面没有的数据，它比基于主键索引的查询多扫描了一棵索引树，这个过程就叫回表。例如：select \* from user\_innodb where name = ‘青山’;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzkzNTkyNw_size_16_color_FFFFFF_t_70_pic_center 2]

在辅助索引里面，不管是单列索引还是联合索引，如果select的数据列只用从索引中就能够取得，不必从数据区中读取，这时候使用的索引就叫做覆盖索引，这样就避免了回表。

我们先来创建一个联合索引：

CREATE INDEX idx_name_phoneonuser_innodb(name,phone);

这三个查询语句都用到了覆盖索引：

EXPLAIN SELECT name,phone FROM user_innodb WHERE name='青山' AND phone='13666666666';
    EXPLAIN SELECT name FROMuser_innodb WHERE name='青山' AND phone='13666666666'; 
    EXPLAIN SELECT phone FROM user_innodb WHERE name='青山' AND phone='13666666666';

![在这里插入图片描述][20201029153917874.png_pic_center]

Extra里面值为“Using index”代表使用了覆盖索引。另外，select \* ，用不到覆盖索引。很明显，因为覆盖索引减少了IO次数，减少了数据的访问量，可以大大地提升查询效率。

## 3.索引条件下推（ICP） ##

在讲ICP前，我们再创建一张数据表（员工表），并且在last\_name和first\_name上面创建联合索引。

CREATE TABLE `employees`( 
        `emp_no`int(11)NOTNULL,
        `birth_date`date NULL,
        `first_name`varchar(14)NOTNULL,
        `last_name`varchar(16)NOTNULL, 
        `gender`enum('M','F')NOTNULL, 
        `hire_date`date NULL, 
        PRIMARYKEY(`emp_no`)
    )ENGINE=InnoDBDEFAULTCHARSET=latin1;
    
    alter table employees add index idx_lastname_firstname(last_name,first_name); -- 创建联合索引
    
    INSERT INTO `employees`(`emp_no`,`birth_date`,`first_name`,`last_name`,`gender`,`hire_date`)VALUES(1, NULL,'698','liu','F',NULL); 
    INSERT INTO `employees`(`emp_no`,`birth_date`,`first_name`,`last_name`,`gender`,`hire_date`)VALUES(2, NULL,'d99','zheng','F',NULL); 
    INSERT INTO `employees`(`emp_no`,`birth_date`,`first_name`,`last_name`,`gender`,`hire_date`)VALUES(3, NULL,'e08','huang','F',NULL); 
    INSERT INTO `employees`(`emp_no`,`birth_date`,`first_name`,`last_name`,`gender`,`hire_date`)VALUES(4, NULL,'59d','lu','F',NULL); 
    INSERT INTO` employees`(`emp_no`,`birth_date`,`first_name`,`last_name`,`gender`,`hire_date`)VALUES(5, NULL,'0dc','yu','F',NULL); 
    INSERT INTO` employees`(`emp_no`,`birth_date`,`first_name`,`last_name`,`gender`,`hire_date`)VALUES(6, NULL,'989','wang','F',NULL); 
    INSERT INTO` employees`(`emp_no`,`birth_date`,`first_name`,`last_name`,`gender`,`hire_date`)VALUES(7, NULL,'e38','wang','F',NULL); 
    INSERT INTO` employees`(`emp_no`,`birth_date`,`first_name`,`last_name`,`gender`,`hire_date`)VALUES(8, NULL,'0zi','wang','F',NULL); 
    INSERT INTO` employees`(`emp_no`,`birth_date`,`first_name`,`last_name`,`gender`,`hire_date`)VALUES(9, NULL,'dc9','xie','F',NULL); 
    INSERT INTO` employees`(`emp_no`,`birth_date`,`first_name`,`last_name`,`gender`,`hire_date`)VALUES(10, NULL,'5ba','zhou','F',NULL);

### 3.1 ICP是干什么用的？ ###

现在我们要查询所有姓wang，并且名字最后一个字是zi的员工，比如王胖子，王瘦子。查询的SQL如下：

select * from employees where last_name='wang' and first_name LIKE '%zi';

这条SQL有两种执行方式：

1.  根据联合索引查出所有姓wang的二级索引数据，然后回表，到主键索引上查询全部符合条件的数据（3 条数据）。然后返回给 Server 层，在 Server 层过滤出名字以zi结尾的员工。
    
    > 注：索引的比较是在存储引擎进行的，数据记录的比较是在Server层进行的。而当first\_name的条件不能用于索引过滤时，Server 层不会把first\_name的条件传递给存储引擎，所以读取了两条没有必要的记录。如果将数据规模扩大，比如满足last\_name='wang’的记录有100000条，就会有99999条没有必要读取的记录。

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzkzNTkyNw_size_16_color_FFFFFF_t_70_pic_center 3]

1.  根据联合索引查出所有姓wang的二级索引数据（3个索引），然后从二级索引中筛选出first\_name以zi结尾的索引（1个索引），然后再回表，到主键索引上查询全部符合条件的数据（1条数据），返回给Server 层。

很明显，第二种方式到主键索引上查询的数据更少，但mysql在没开启ICP前使用的都是第一种。

explain select * from employees where last_name='wang' and first_name LIKE '%zi';

![在这里插入图片描述][20201029154002781.png_pic_center]

Using Where代表从存储引擎取回的数据不全部满足条件，需要在Server 层过滤。先用last\_name 条件进行索引范围扫描，读取数据表记录，然后进行比较，检查是否符合first\_name LIKE ‘%zi’ 的条件。此时3条中只有1条符合条件。

### 3.2 怎么开启ICP？ ###

开启命令如下：

set optimizer_switch='index_condition_pushdown=on';

开启后再查看此时的执行计划，Using index condition：

![在这里插入图片描述][20201029154020562.png_pic_center]

把first\_name LIKE '%zi’下推给存储引擎后，只会从数据表读取所需的1条记录。索引条件下推（IndexConditionPushdown），5.6以后完善的功能。只适用于二级索引。ICP 的目标是减少访问表的完整行的读数量从而减少 I/O 操作。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzkzNTkyNw_size_16_color_FFFFFF_t_70_pic_center]: /images/20221120/49e095a44dce4d00bc480a8973e8102e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzkzNTkyNw_size_16_color_FFFFFF_t_70_pic_center 1]: https://img-blog.csdnimg.cn/20201104170130963.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzkzNTkyNw==,size_16,color_FFFFFF,t_70#pic_center
[20201029153811993.png_pic_center]: https://img-blog.csdnimg.cn/20201029153811993.png#pic_center
[20201029153827889.png_pic_center]: https://img-blog.csdnimg.cn/20201029153827889.png#pic_center
[20201029153840249.png_pic_center]: https://img-blog.csdnimg.cn/20201029153840249.png#pic_center
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzkzNTkyNw_size_16_color_FFFFFF_t_70_pic_center 2]: https://img-blog.csdnimg.cn/20201029153903796.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzkzNTkyNw==,size_16,color_FFFFFF,t_70#pic_center
[20201029153917874.png_pic_center]: https://img-blog.csdnimg.cn/20201029153917874.png#pic_center
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzkzNTkyNw_size_16_color_FFFFFF_t_70_pic_center 3]: https://img-blog.csdnimg.cn/2020102915395038.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzkzNTkyNw==,size_16,color_FFFFFF,t_70#pic_center
[20201029154002781.png_pic_center]: https://img-blog.csdnimg.cn/20201029154002781.png#pic_center
[20201029154020562.png_pic_center]: https://img-blog.csdnimg.cn/20201029154020562.png#pic_center