联合索引与索引下推

最新推荐文章于 2024-10-05 11:48:06 发布

钱多多_qdd

最新推荐文章于 2024-10-05 11:48:06 发布

阅读量1.2k

点赞数 28

文章标签： mysql

本文链接：https://blog.youkuaiyun.com/moneywenxue/article/details/138405398

版权

本文详细解释了联合索引的工作原理，包括最左匹配规则，以及如何利用索引覆盖和索引下推来提升查询性能。重点讨论了何时选择合适的联合索引并给出了生产环境中的应用实例。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

一、联合索引

联合索引大家都知道，就不介绍了。

二、最左匹配原则

在通过联合索引检索数据时，从索引中最左边的列开始，一直向右匹配，如果遇到范围查询(>、<、between、like等)，就停止后边的匹配。

这个定义不太好理解，我解释一下：
假如对字段 (a, b, c) 建立联合索引，现在有这样一条查询语句：

where a > xxx and b=yyy and c=zzz
where a like 'xxx%' and b=yyy and c=zzz

在这个条件语句中，只有a用到了索引，后面的b,c就不会用到索引。这就是“如果遇到范围查询(>、<、between、like等)，就停止后边的匹配。”的意思。

还是假如对字段 (a, b, c) 建立联合索引，

2.1 如下查询语句可以使用到索引：

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

2.1 如下查询条件也会使用索引：

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

虽然b和a的顺序换了，但是mysql中的优化器会帮助我们调整顺序。

2.3 如下查询条件只用到联合索引的一部分：

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 列的索引。

2.4 如下查询条件完全用不到索引

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

2.5 如何选择合适的联合索引

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 a = xxx order by b 这样的语句，可以对 (a, b) 建立索引;

三、索引覆盖

建立了联合索引后，直接在索引中就可以得到查询结果，从而不需要回表查询聚簇索引中的行数据信息。

索引覆盖可以带来很多的好处：

辅助索引不包含行数据的所有信息，故其大小远小于聚簇索引，因此可以减少大量的IO操作;
索引覆盖只需要扫描一次索引树，不需要回表扫描聚簇索引树，所以性能比回表查询要高;
索引中列值是按顺序存储的，索引覆盖能避免范围查询回表带来的大量随机IO操作。

explain语句中，entra列如果是Using index，就表示使用到了索引 , 并且所取的数据完全在索引中就能拿到，也就是用到了索引覆盖。

四、索引下推

索引下推是索引下推是 MySQL 5.6 及以上版本上推出的，用于对查询进行优化。

索引下推是把本应该在 server 层进行筛选的条件，下推到存储引擎层来进行筛选判断，这样能有效减少回表。

举例说明：
首先使用联合索引（name，age），现在有这样一个查询语句：
select * from t_user where name like ‘L%’ and age = 17;

这条语句从最左匹配原则上来说是不符合的，原因在于只有name用的索引，但是age并没有用到。

不用索引下推的执行过程：

第一步：利用索引找出name带’L’的数据行：LiLei、Lili、Lisa、Lucy 这四条索引数据;
第二步：再根据这四条索引数据中的 id 值，逐一进行回表扫描，从聚簇索引中找到相应的行数据，将找到的行数据返回给 server 层;
第三步：在server层判断age = 17,进行筛选，最终只留下 Lucy 用户的数据信息。

使用索引下推的执行过程：

第一步：利用索引找出name带’L’的数据行：LiLei、Lili、Lisa、Lucy 这四条索引数据;
第二步：根据 age = 17 这个条件，对四条索引数据进行判断筛选，最终只留下 Lucy 用户的数据信息
（注意：这一步不是直接进行回表操作，而是根据 age = 17 这个条件，对四条索引数据进行判断筛选）；
第三步：将符合条件的索引对应的 id 进行回表扫描，最终将找到的行数据返回给 server 层。

比较二者的第二步我们发现，索引下推的方式极大的减少了回表次数。

索引下推需要注意的情况：
下推的前提是索引中有 age 列信息，如果是其它条件，如 gender = 0，这个即使下推下来也没用。

开启索引下推：

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

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

配置文件：

[mysqld]
optimizer_switch="index_condition_pushdown=on"-- 开启索引下推
optimizer_switch="index_condition_pushdown=off" -- 关闭索引下推

查询：

SELECT @@optimizer_switch;

五、生产中例子（脱敏，用abc代替）

假如对字段 (a, b, c) 建立联合索引，现在有这样一条查询语句：

where a='xx' and c='yy';

目前索引：索引a；——发现查询慢；
接着，索引：a，改为联合索引：abc；

按理说where条件用了ac,效率不应该很高，但事实上，效率提高了。为什么呢？
——其实这就是索引下推；

文章引用：
索引知识二：联合索引、覆盖索引和索引下推详解