避免回表，引入索引下推|提高索引命中率 | 提前下班啦

末蓝、 2024-03-25 15:07 52阅读 0赞

## 为什么这么设计索引 ##

*  说白了就是为了提高查询效率。因为只有B+Tree 在满足平衡特性的情况下能够存储更多的索引内容从未维持这颗树在一定高度上。至于为什么不用Hash表那就更不用说了。一个是它发生冲突概率太大另外一个就是他实际就是个链表，链表和树相比肯定是树查询快。

## 有哪些索引 ##

*  在 `mysql` 中默认主键索引名是通过 `pk_` 开头进行标识的。

### 普通索引 ###

> CREATE INDEX 索引名 ON 表名(字段名)

*  普通索引常常是用来进行条件快速查找的，除此之外对字段本身并无要求。

### 唯一索引 ###

> CREATE UNIQUE INDEX 索引名 ON 表名(字段名)

### 主键索引 ###

> alter table 表名 add primary key(字段名)

### 组合索引 ###

> CREATE INDEX 索引名 ON 表名(字段名1,字段名2);

*  组合索引我们可以理解成多列组成的普通索引，所以和普通索引创建的语法相同。关于组合索引往往也是面试高频题。其他索引都是单列索引命中与否也很好判断。但是组合索引涉及到最左匹配原则往往是别人忘记的一点。
 *  关于最左匹配原则呢？这里先简单阐述下，比如你创建了一个联合索引 \`\`create index idx\_abc on t`(`b`,`c`,`d`)` 。 此时有个sql `select * from t where e=1` 。这个sql是不会走索引的，最左匹配原则就是联合索引需要从左开始匹配。

### 全文索引 ###

> 全文索引首先是 MySQL 的一种索引类型，也是搜索引擎的关键技术。
> 
> 试想在1M大小的文件中搜索一个词，可能需要几秒，在100M的文件中可能需要几十秒，如果在更大的文件中搜索那么就需要更大的系统开销，这样的开销是不现实的。
> 
> 所以在这样的矛盾下出现了全文索引技术，有时候有人叫倒排文档技术。

*  关于全文索引其实这里和 `es` 的分词倒是有点像。将分词与数据挂钩，这样通过分词条件查找的时候就会非常的方便，详细讲解可以参考下这篇[文章][Link 1]

## 如何命中索引 ##

## 回表&索引下推 ##

#### 回表 ####

*  回表和索引下推都是基于B+Tree发生的必要场景。因为 `mysql` 中只有主键索引是聚簇索引也就是只有主键索引叶子节点存储的是真实的行数据，其他的普通索引叶子节点都存储的是主键。当我们通过普通索引查询时就极有可能需要在通过主键进行查找真实数据，这就是回表。

![format_png][]

*  但不是所有的普通索引查询的 `sql` 都会发生回表的。下面我们来如何避免发生回表的且通过普通索引查询的 `sql` 。
 *  想要避免回表我们就要做到 `索引覆盖` . 覆盖索引并不是指索引结构上有啥不同，而是指我们的sql 尽量不要用普通索引以外的信息。比如现在我们又一个索引 `idx_name(username)` ， 针对这个普通索引我们使用`select username from t where username='xxx'` 或者使用`select id from t where username='xxx'` ， 这两种都是实现了索引覆盖。因为上述两种查询 `sql` 条件和查询分别用到了 `username` 和 `id` ，恰巧这两个字段都在 `idx_name` 这个索引中。所以这种情况时不需要回表的。
 *  但是这种情况下我们为了方便或者使用的类似 `mybatis-Plus` 这种框架都会产生类似这种sql `select * from t where username='xxx'` 。针对这种sql 对于数据库来说是很不友好的。因为在业务上你可能只需要通过 `username` 查找，而字段你可能只需要id或者username 甚至是你不需要查询字段内容。但是sql却去查询了，况且为了其他类似于`age` 字段 ，`mysql` 甚至得花一次IO去回表查询，这样对于高性能服务来说是一种IO浪费。

#### 索引下推 ####

*  5.6前存储引擎通过回表进行判断其他索引是否符合
 *  5.6后存储引擎继续判断其他索引是否符合之后在回表 这叫索引下推。
 *  好像还不是很明白什么是索引下推。 现在我们创建一个索引`idx_uas(username,age,sex)` 这个联合索引。 开始索引下推之前我们先了解下联合索引如何命中这个问题。

CREATE TABLE `t` (
      `id` int(11) NOT NULL,
      `user_name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
      `age` int(11) DEFAULT NULL,
      `sex` int(11) DEFAULT NULL,
      PRIMARY KEY (`id`),
      KEY `uk_ua` (`user_name`,`age`,`sex`) USING BTREE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
    复制代码

insert into t(id,user_name,age,sex) values(1,'zxhtom',11,1);
    insert into t(id,user_name,age,sex) values(2,'sun',15,2);
    insert into t(id,user_name,age,sex) values(3,'wukong',21,1);
    insert into t(id,user_name,age,sex) values(4,'zhu',11,3);
    insert into t(id,user_name,age,sex) values(5,'baijie',45,1);
    insert into t(id,user_name,age,sex) values(6,'sha',101,2);
    insert into t(id,user_name,age,sex) values(7,'heshang',1,1);
    insert into t(id,user_name,age,sex) values(8,'zxz',121,1);
    复制代码

![format_png 1][]

sql1

> select \* from t where user\_name > 'zxhtom' and age<2;

*  在 `explain` 之前我一度认为该sql 只能全表扫描了。虽然他是按照最左匹配原则书写的条件匹配，但是上来就遇到了范围查询。而索引匹配在遇到范围查询时就会失效，这是我从书本上背来的，但是事实却并不是我认为的那样。

![format_png 2][]

*  很明显可以看到 `Using index` 说明使用了索引，并且可能是 `uk_ua` 这个索引。这是因为 `mysql` 会先将 `user_name>'zxhtom'` 这个条件到 `uk_ua` 构建的BTree中查找到具体的页节点上。因为`最左匹配原则` ，我们能够知道 `user_name>'zxhtom'` 的都会在 `user_name='zxhtom'` 的右侧。在第一层查询到的范围之后再去查找 `age<2` 的数据。此时`age` 这块可以继续上面定位到的节点继续往下搜索。

![format_png 3][]

sql2

> select \* from t where age<2;

![format_png 4][]

*  这个sql 就更让我疑惑了，最左匹配的 `user_name` 都没有，为什么还能走 `uk_ua` 这颗索引树呢？但是仔细想想也能够明白，毕竟age就存在这颗树中。只不过查询的范围就比较广。查了8条记录这就相当于时全表扫描了。而且还设计回表，这就是引擎的不智能。有的时候走索引不比全表扫描快。
 *  两个 `sql` 对比能够发现 `最左匹配原则` 遇到非精确查询时会结束索引，而不是不走索引。

下推

*  好了简单了解最左匹配之后我们再来看看 `索引下推` 是什么意思吧。

![format_png 5][]

*  上面联合索引中当我们通过`最左匹配原则` 匹配到节点时，我们知道其他字段尤其是下一字段`age` 在该内部是局部递增排列的。这个时候如果我们在根据`age` 进行定位查找，效率是非常快的。这个过程我们称之为 `索引下推`。

select * from t where user_name='zxhtom' and age=2
    复制代码

*  在5.7之后的 `mysql` 服务器中会先根据 `user_name` 定位到节点，然后在节点周边去定位`age` 从而最终确定到所需节点。然后在根据叶子节点的主键数据回表查询完整数据。
 *  而在 5.7之前并不支持 `索引下推` 。 在 5.7之前是先通过 `user_name` 定位到节点。然后将定位到的节点进行回表查询完整数据，在根据回表查到的数据进行筛选age属性。这样做肯定不如 `索引下推` 来的效率高。毕竟精确定位好数据之后在回表这样可以避免不必要的数据查询。此时如果通过 `explain` 去分析的话你会发现即使使用的`最左匹配` 查询 使用的也是 `where` 搜索，而不是像5.7之后使用的是 `index condition` 。

## 哪些场景会索引失效 ##

*  这里就仅仅罗列失效的场景，关于失效的原因以后慢慢单独出篇 `explain` 详解一下。

1.  未遵循最左匹配原则
2.  索引列参与计算 ： `select * from t where id+1=2`
3.  对索引列存在包装 ： `select * from t where concat(id,'')='1'`
4.  模糊查询中出现左侧模糊的情况 ： `select * from t where id like '%test'` 或者 `select * from t where id like '%test%'`
5.  发生类型转换 ： `select * from t where id ='2'`
6.  sql 中出现 OR 语句 ； not null 语句 ； not in. not exist 等语句
7.  出现两列比较，不管比较列在不在索引中 `select * from t where id!=name`
8.  非精确查询即 不等于查找 `select * from t where id !=1`。

[Link 1]: https://link.juejin.cn?target=
[format_png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/97ba4f9ec8c34a4f9ccdc7dd629ec557.png
[format_png 1]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/7b37586100ce4034a713db5ff9f96a25.png
[format_png 2]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/53285dd122264f24bc5de4240be128ee.png
[format_png 3]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/9e3ea26d31124bb185caad5e7cf5079a.png
[format_png 4]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/bbbc07d78e4040e382d1f5160f8e8136.png
[format_png 5]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/002d01c2a08e4141b41f86e9ff1cf7f6.png