阅读 409

MySQL 索引之覆盖索引,联合索引与索引下推

请移步至 【DobbyKim 的每日一题】 查看更多的题目~

本文章为【极客时间】课程【MySQL 实战 45 讲】的内容整理

一:覆盖索引

表 T 的建表语句如下:

create table T (
  ID int primary key,
  k int NOT NULL DEFAULT 0,
  s varchar(16) NOT NULL DEFAULT '',
  index k(k)
)engine=InnoDB;

insert into T values(100,1, 'aa'),(200,2,'bb'),(300,3,'cc'),(500,5,'ee'),(600,6,'ff'),(700,7,'gg');
复制代码

如果我执行语句

select * from T where k between 3 and 5
复制代码

会执行几次树的搜索操作?

这条 SQL 语句的执行流程如下:

  1. 在 k 索引树上找到 k = 3 的记录,取得 ID = 300;
  2. 再到 ID 主键索引树查找到对应的 R3,这个过程我们称作回表
  3. 在 k 索引树上找到 k = 5 的记录,取得 ID = 500;
  4. 再到 ID 主键索引树查找到对应的 R4;
  5. 在 k 索引树取得下一个值 k = 6,不满足条件,循环结束。

我们看到,这条查询语句读了 k 索引树的三条记录(步骤 1、3、5),并且回表了两次

我们是否有办法优化查询,避免回表呢?

如果执行的语句是:

select ID from T where k between 3 and 5
复制代码

这条 SQL 语句只需要查询 ID 的值,因为 ID 的值已经在 k 索引树上了,因此可以直接提供查询结果,不需要发生回表的过程。

这条 SQL 语句的执行流程如下:

  1. 在 k 索引树上找到 k = 3 的记录,取得 ID = 300;
  2. 在 k 索引树上找到 k = 5 的记录,取得 ID = 500;
  3. 在 k 索引树取得下一个值 k = 6,不满足条件,循环结束。

因为索引 k “覆盖了”我们的查询需求,我们也称之为覆盖索引。

覆盖索引查询可以减少树的搜索次数,显著提升性能,所以使用覆盖索引查询是一个常用的性能优化手段。

二:联合索引

联合索引,又叫做复合索引。当我们遇到多条件查询时,使用复合索引会让我们的查询变得高效。

最左匹配原则

联合索引中最为重要的原则就是最左匹配原则。

什么是最左匹配原则呢?

举个例子,user 表的建表语句如下:

create table `user` (
  ID int primary key,
  `name` varchar(32) DEFAULT NULL, 
  `age` int(11) DEFAULT NULL, 
  `ismale` tinyint(1) DEFAULT NULL,
  KEY `name_age_ismale`(`name`,`age`,`ismale`)
)engine=InnoDB
复制代码

我们创建了(name,age,ismale) 这三个字段的联合索引。

联合索引的最左匹配原则就是,当我们创建了(name,age,ismale) 这个联合索引时,会按照从左向右的顺序来建立搜索树:首先 B+ 树会按照 “name”字段来排序,如果“name”列相同,再按照“age”进行排序,如果这个时候“age”列还是相同的,那么再按照“ismale”进行排序。

如果你的 sql 语句用到了联合索引中“最左边的”索引,那么这条 sql 语句是可以使用联合索引进行匹配的。通俗地讲,我们相当于创建了(name) 这个单列索引,(name,age)这个联合索引以及(name,age,ismale)这三个字段组合的联合索引。值得注意的是,当联合索引的某个字段遇到范围查询时,匹配就会停止。

举例:

select * from user where name = 'Jack'
复制代码

这条语句是可以使用联合索引进行匹配的。

select * from user where name = 'Jack' and age = 22
复制代码

这条语句也是可以使用联合索引进行匹配的。

select * from user where age > 18 and name = 'Jack'
复制代码

该语句是可以使用联合索引进行匹配的,age > 18 不是等值匹配,而 name = 'Jack' 则是等值匹配,优化器会优化这条查询语句,先匹配 name 字段,然后再匹配 age 字段。

select * from user where age = 18;
复制代码

这条 sql 查询语句是无法使用联合索引进行匹配的,因为 age 字段相对于 name 字段有序,但是全局整体无序,并不符合最左匹配原则。所以单拿出 age 字段是用不上索引的。

三:索引下推

user 表的建表语句如下:

create table `user` (
  ID int primary key,
  `name` varchar(32) DEFAULT NULL, 
  `age` int(11) DEFAULT NULL, 
  KEY `name_age`(`name`,`age`)
)engine=InnoDB
复制代码

user 表中,我们建立了(name,age) 联合索引

索引示意图如下:

现有查询语句:

select * from user where name like '张%' and age=10
复制代码

我们知道,根据最左匹配原则,该查询可以对 name 字段进行匹配,但是 name 并非等值匹配,所以 age 字段是无法用到索引的。

在 MySQL 5.6 以前,该语句会从联合索引 name = '张六' 那一行开始,然后回表到主键索引上找出数据行,再进行 age = 10 的判断。对于该查询来说,会执行四次回表操作。

而 MySQL 5.6 开始便引入了索引下推优化(index condition pushdown)简称 ICP,索引下推是指在索引遍历的过程中,对索引中包含的字段先做判断,直接过滤掉不满足条件的记录来减少回表次数。所以,age = 10 这个条件可以直接在联合索引中进行判定。

因为有索引下推优化,在遍历索引时,内部就判断了 age 是否等于 10。对于不等于 10 的记录,会直接跳过,不会发生回表,对于我们这个示例,通过MySQL 5.6 引入的索引下推,只会发生两次回表操作。

文章分类
后端
文章标签