【mysql 5.7】什么是索引下推？（第二篇）

最新推荐文章于 2024-06-07 21:27:14 发布

云川之下

最新推荐文章于 2024-06-07 21:27:14 发布

阅读量708

点赞数

分类专栏：数据库文章标签： mysql 数据库 sql

原文链接：https://blog.csdn.net/daidaineteasy/article/details/110246532

版权

数据库专栏收录该内容

57 篇文章 8 订阅

订阅专栏

文章目录

相关文章：
什么是最左原则、什么是索引下推？
什么是索引下推？（第二篇）

在开始介绍索引下推之前，先看下联合索引的特性

1. 联合索引

联合索引（也叫组合索引、复合索引、多列索引）是指对表上的多个列进行索引。联合索引的创建方法跟单个索引的创建方法一样，不同之处仅在于有多个索引列。

例如，创建如下表，idx_name 是联合索引，索引列为 (name,age)

CREATE TABLE `t_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` varchar(32) NOT NULL COMMENT '姓名',
  `age` tinyint(3) unsigned NOT NULL COMMENT '年龄',
  `gender` tinyint(3) unsigned NOT NULL COMMENT '性别：1男，0女',
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`,`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

假如表中有如下数据：

id	name	age	gender
1	LiLei	18	1
2	HanMeimei	17	0
3	Lucy	17	0
4	Lili	16	0
5	WeiHua	32	0
6	ZhangWei	25	1
7	Ann	36	0
8	Lisa	19	0
9	ZhangWei	18	1
10	Kate	17	1

我们来看一下这棵索引树的结构：
在这里插入图片描述
从图中我们可以看出，叶子节点中的键值都是按顺序存储的，即(“Ann”,36)、(“HanMeimei”,17)、(“Kate”,17)、(“LiLei”,18)、(“Lili”,16)、(“Lisa”,19)、(“Lucy”,17)、(“WeiHua”,32)、(“ZhangWei”,18)、(“ZhangWei”,25)。

索引会先根据 name 排序，如果 name 相同，再根据 age 进行排序。因此如果问age是有序的吗，不能直接回答有序，因为name相同的情况下才是有序的。

1.1 联合索引的最左匹配原则

索引的目的其实就是为了提高数据查询的效率，联合索引也一样，使用联合索引时，一定要注意符合最左匹配原则：在通过联合索引检索数据时，从索引中最左边的列开始，一直向右匹配，如果遇到范围查询(>、<、between、like等)，就停止后边的匹配。

假如对字段 (a, b, c) 建立联合索引，如下查询语句可以使用到索引：

where a = xxx
where a = xxx and b = xxx
where a = xxx and b = xxx and c = xxx
where a like 'xxx%'
where a > xxx
where a = xxx order by b
where a = xxx and b = xxx order by c
group by a

书写顺序不影响对联合索引的使用：

where b = xxx and a = xxx
where a = xxx and c = xxx and b = xxx

这种查询条件书写顺序不影响对联合索引的使用，因为执行 sql 的时候，MySQL优化器会帮我们调整 where 后 a,b,c 的顺序，让我们用上索引。

而还有一些语句是只能用到联合索引的一部分的：

#跳过某些
where a = xxx and c = xxx 可以用到 a 列的索引，用不到 c 列索引。
#使用%，后面的不用索引
where a like 'xxx%' and b = xxx 可以用到 a 列的索引，用不到 b 列的索引。   '索引下推正是为了优化该场景'
#使用>号，后面的不用索引
where a > xxx and b = xxx 可以用到 a 列的索引，用不到 b 列的索引

最需要注意类似下边的这些查询，完全用不到 (a, b, c) 这个联合索引：

where b = xxx               -- 跳跃
where c = xxx               -- 跳跃
where a like '%xxx'			-- 不满足最左前缀
where d = xxx order by a	-- 出现非排序使用到的索引列 d 
where a + 1 = xxx	-- 使用函数、运算表达式及类型隐式转换等

1.2 如何选择合适的索引列顺序

建立联合索引时，一般我们遵循的经验是：将选择性最高的列放在索引的最前列。这在某些场景下比较有用，但通常不如避免随机IO和排序那么重要。正确的顺序应该依赖于使用该索引的查询，并且同时需要考虑如何更好的满足排序和分组的需要。

where a = xxx and b = xxx and c = xxx 如果我们的查询是这样的，建索引时，就可以考虑将选择性高的列放在索引的最前列，选择性低的放后边。
如果是 where a > xxx and b = xxx 或 where a like 'xxx%' and b = xxx 这样的语句，可以对 (b, a) 建立索引。
因为这样以来，等价于 where b = xxx and a > xxx了，符合组合索引顺序了
如果是 where a = xxx order by b 这样的语句，可以对 (a, b) 建立索引。

2. 索引下推（索引条件下推）

什么是索引下推（Index Condition Pushdown，ICP）呢？我们通过例子来了解下。

假设我们想从一开始创建的表中，查询 name 以 ‘L’ 开头，并且 age 为 17 的人员信息。

select * from t_user where name like 'L%' and age = 17;

2.1 不使用索引下推

在不用索引下推的情况下，根据前边"最左匹配原则"描述的那样，该查询在联合索引中只有 name 列可以使用到索引，age 列是用不到索引的。

在扫描 (‘name’, age) 索引树时，根据 name like ‘L%’ 这个条件，可以查找到 LiLei、Lili、Lisa、Lucy 四条索引数据，接下来，再根据这四条索引数据中的 id 值，逐一进行回表扫描，从聚簇索引中找到相应的行数据，将找到的行数据返回给 server 层。server 层中，再根据 age = 17 这个条件进行筛选，最终只留下 Lucy 用户的数据信息。

select * from t_user where name like 'L%' and age = 17;

不用索引下推的过程，如下图示：
在这里插入图片描述

2.2 使用索引下推

在使用索引下推的情况下，存储引擎层还是先根据 name like 'L%' 这个条件，查找到 LiLei、Lili、Lisa、Lucy 四条索引数据，不过接下来不是直接进行回表操作，而是根据 age = 17 这个条件，对四条索引数据进行判断筛选，将符合条件的索引对应的 id 进行回表扫描，最终将找到的行数据返回给 server 层。（也就是我们把本应该在 server 层进行筛选的条件，下推到存储引擎层来进行筛选判断了。这个下推的前提是索引中有 age 列信息，如果是其它条件，如 gender = 0，这个即使下推下来也没用）

使用索引下推的过程，如下图示：
在这里插入图片描述
由上比较可以看出，使用索引下推优化，可以有效减少回表次数，也可以减少 server 层从存储引擎层接收数据的次数，从而大大提升查询效率。

索引下推是 MySQL 5.6 及以上版本上推出的，用于对查询进行优化。默认情况下，索引下推处于启用状态。我们可以使用如下命令来开启或关闭。

set optimizer_switch='index_condition_pushdown=off'; 	-- 关闭索引下推
set optimizer_switch='index_condition_pushdown=on';		-- 开启索引下推

话说，正常情况下，我们有什么理由来关闭这么好用的功能呢？

参考：
《MySQL联合索引与索引下推图文详解》