面试三轮我倒在了一道 sql 题上——sql 性能优化

一、前言

最近小农在找工作，因为今年疫情的特殊原因，导致工作不是特别好找，所以一旦有面试电话，如果可以，都会去试一试，刚好接到一个面试邀请，感觉公司还不错，于是就确定了面试时间，准备了一下就去面试了。

第一轮面试是小组组长面试，通过了。

第二轮是经理面试也是通过了。

第三轮总监面试，前面都还有模有样，突然画风一转，面试官说：“问你最后一个问题啊”

面试官：10W 条数据，我要从其中查出 100 条不连续的数据，给你 id，来查 name 和 password 进行展示,如何才能高性能的去使用？

我：在 id 上建立聚簇索引，然后用 in id 来缩小表搜索范围，最后 使用条件查询 小于最大 id，大于最小 id，这样可以让 sql 速度能够比较快的展示，虽然 In 的性能比较低

心里活动：雕虫小技，还最后一个问题，这样的问题再来一个吧

只见面试官紧锁眉头，与我心里期待的表情有点不一样啊，难道是哪个环节出了问题？

面试官：这样的性能不能达到最优化的程度，而且如果我给你的最小 id 是 1，最大 id 是 100000 呢？

你这就有点杠精了啊，那行吧，你是面试官你说了算

我：既然 id 已经给出来了，而且只查询两个字段，用聚簇索引那么查询数据是很快的，用 in id 应该是可以的。

面试官：好的，回去等通知吧

我。。。。。

二、后知

于是回去后，查询资料，才知道原来面试官，真正想考的是 “覆盖索引”

什么是覆盖索引：

当 sql 语句的所求查询字段（select 列）和查询条件字段（where 子句）全都包含在一个索引中 （联合索引），可以直接使用索引查询而不需要回表。这就是覆盖索引，通过使用覆盖索引，可以减少搜索树的次数，这就是 覆盖索引，在了解覆盖索引之前，我们先来看看什么是索引。

三、什么是索引？

我们有一个主键列为 id 的表，表中有字段 name，并且在 name 上有索引

表中 ```t_user``` 值分别为(1，张一)、(2，张二)、(3，张三)、(4，张四)、(5，张五)

表结构如下：

>mysql> create table t_user (

id bigint(20) not null auto_increment ,

name varchar(255) not null,

primary key (id),

index index_name (name) using btree)

engine=innodb

default character set=utf8 collate=utf8generalci

两棵树的示例示意图如下：

从图中不难看出，根据叶子节点的内容，索引类型分为主键索引和二级索引（非主键索引）。

主键索引： 主键索引的叶子节点保存着主键即对应行的全部数据。在 InnoDB 里，主键索引也被称为聚簇索引（clustered index）。

二级索引（非主键索引）： 二级索引树中的叶子结点保存着索引值和主键值，当使用二级索引进行查询时，需要进行回表操作。在 InnoDB 里，非主键索引也被称为二级索引（secondary index）

通过上面所讲的，我们来看看如何通过 sql 语句来区分 主键索引和普通索引的查询

• select * from t_user where id=1 即主键查询方式，则只需要搜索 id 这棵 B+树

• select * from t_user where name=张三 即普通索引查询方式，则需要先搜索`name`索引树，得到id的值为3，再到 id 索引树搜索一次。这个过程称为回表

也就是说，基于二级索引（非主键索引）的查询需要多扫描一棵索引树。因此，我们在应用中应该尽量使用主键查询。

看到这里如果你看懂了上面的介绍，那么这里你会有一个疑问，我直接用``in id``不就好了吗，建立 id 主键索引，就可以不用回表了，速度不也就提升了吗？

如果是 5.5 之前的版本确实不会走索引的，在 5.5 之后的版本，MySQL 做了优化。MySQL 在 2010 年发布 5.5 版本中，优化器对 in 操作符可以自动完成优化，针对建立了索引的列可以使用索引，没有索引的列还是会走全表扫描，也就是我们所说的回表。

那么，有没有可能经过索引优化，避免回表过程呢？答应是有的

四、覆盖索引

sql 语句如下，其中 id 自增，name 为索引：

>mysql> create table t_user (

id bigint(20) not null auto_increment ,

name varchar(255) not null,

password varchar(255) ,

primary key (id),

engine=innodb

default character set=utf8 collate=utf8generalci

比如有这么两句 sql

语句 A： ``select id from user_table where name= '张三'``

语句 B： ``select password from user_table where name= '张三'``

语句 A： 因为 name 索引树 的叶子结点上保存有 name 和 id 的值 ，所以通过 name 索引树 查找到 id 后，因此可以直接提供查询结果，不需要回表，也就是说，在这个查询里面，索引 name 已经 “覆盖了” 我们的查询需求，我们称为 覆盖索引

语句 B： name 索引树 上 找到 name='张三' 对应的主键 id, 通过回表在主键索引树上找到满足条件的数据

因此我们可以得知，当 sql 语句的所求查询字段（select 列）和查询条件字段（where 子句）全都包含在一个索引中（联合索引），可以直接使用索引查询而不需要回表。这就是覆盖索引。

例如上面的语句 B 是一个高频查询的语句，我们可以建立(name,password)的联合索引，这样，查询的时候就不需要再去回表操作了，可以提高查询效率。

所以关于上面的面试题我们就可以得出，使用联合索引就可以很好的回答面试官的问题（id,name,password）这样的联合索引就可以调用到覆盖索引，可以减少树的搜索次数，不再需要回表查整行记录，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。

说到了联合索引我们就不得不说联合索引中最重要的匹配原则，最左匹配原则了

五、最左匹配原则

最左前缀匹配原则，是非常重要的原则，mysql 会从左向右进行匹配。

例如我们定义了(name,password)两个联合索引字段，我们 使用 ``where name = '张三' and password = '2' `索引可以生效的，当我们是颠倒了他们的顺序 使用`where password = '1' and name = '王五'`，索引同样也是可以生效的，**在mysql查询优化器会判断纠正这条sql语句该以什么样的顺序执行效率最高，最后才生成真正的执行计划**，我们能尽量的利用到索引时的查询顺序效率最高，所以mysql查询优化器会最终以这种顺序（`where name = '张三' and password = '2' `）进行查询执行，就类似 我们的 `order by name,password``这样一种排序规则，先对张三的用户进行查询排序，在对 password 进行处理

比如我们要查询姓张的用户，我们的条件查询可以为 ``"where name like ‘张%’"`，但是不能是 `where name like '%张%'`或者是 `where name like '%张'``，因为索引可以用于查询条件字段为索引字段，根据字段值必须是最左若干个字符进行的模糊查询，也就是需要是 '张 %' 这样的添加才可以使用。

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

如果既有联合查询，又有基于 name,password 各自的查询呢？查询条件里面只有 password 的语句，是无法使用(name,password)这个联合索引的，这时候你需要同时维护(name,password)、(password) 这两个索引。

创建索引时，我们也要考虑空间代价，使用较少的空间来创建索引

假设我们现在不需要通过 name 查询 password 了，需要通过 name 查询 age 或通过 age 查询 name

• 1.（name,age）联合索引+age 单字段索引

• 2.（age,name）联合索引+name 单字段索引

name 字段是比 age 字段大的，所以，选择第一种，索引占用空间较小的一个

六、索引下推

上面我们说到满足最左前缀原则的时候，最左前缀可以用于在索引中定位记录。那么如果那些不符合最左前缀的部分，会怎么样呢？

如果现在有一个需求：检索出表中“名字第一个字是张，而且没有删除的信息(is_del = 1)。SQL 语句如下：

mysql> select * from tuser where name like '张 %' and isdel=1

在 MySQL 5.6 之前，只能从匹配的位置一个个回表。到主键索引上找出数据行，再对比字段值

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

根据（username,is_del）联合索引查询所有满足名称以“张”开头的索引，然后回表查询出相应的全行数据，然后再筛选出未删除的用户数据。过程如下图：

每一个虚线箭头表示回表一次

图一（无索引下推执行流程）

每一个虚线箭头表示回表一次

图二（索引下推执行流程）

图 1 跟图 2 的区别是，InnoDB 在(name,is_del)索引内部就判断了数据是否逻辑删除，对于逻辑删除的记录，直接判断并跳过。在我们的这个例子中，只需要对 ID1、ID4 这两条记录回表取数据判断，就只需要回表 2 次

mysql 默认启用索引下推，我们也可以通过修改系统变量 optimizerswitch 的 indexcondition_pushdown 标志来控制``SET optimizer_switch = 'index_condition_pushdown=off';``

我们也需要注意：

• innodb 引擎的表，索引下推只能用于二级索引，因为 innodb 的主键索引树叶子结点上保存的是全行数据，所以这个时候索引下推并不会起到减少查询全行数据的效果

• 索引下推一般可用于所求查询字段（select 列）不是/不全是联合索引的字段，查询条件为多条件查询且查询条件子句（where/order by）字段全是联合索引

六、小结

今天的内容就到这里了，我们在上面描述了数据库索引的概念，包括了覆盖索引、联合索引、索引下推，那么下次如果有面试官问你刚开始的问题，相信大家可以好好的回（dui）答（ta）一下面试官了，在 sql 优化中，减少回表次数，或者直接使用覆盖索引是比较重要的，尽量少地访问资源也是数据库设计的重要原则之一，谢谢大家，加油~

我是牧小农，怕什么真理无穷进一步有进一步的欢喜，大家加油！！！