联合索引与索引下推

一、联合索引

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

二、最左匹配原则

  在通过联合索引检索数据时，从索引中最左边的列开始，一直向右匹配，如果遇到范围查询(>、<、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 如何选择合适的联合索引

1. where a = xxx and b = xxx and c = xxx 如果我们的查询是这样的，建索引时，就可以考虑将选择性高的列放在索引的最前列，选择性低的放后边;
2. 如果是 where a > xxx and b = xxx 或 where a like ‘xxx%’ and b = xxx 这样的语句，可以对 (b, a) 建立索引;
3. 如果是 where a = xxx order by b 这样的语句，可以对 (a, b) 建立索引;

三、索引覆盖

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

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

1. 辅助索引不包含行数据的所有信息，故其大小远小于聚簇索引，因此可以减少大量的IO操作;
2. 索引覆盖只需要扫描一次索引树，不需要回表扫描聚簇索引树，所以性能比回表查询要高;
3. 索引中列值是按顺序存储的，索引覆盖能避免范围查询回表带来的大量随机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,效率不应该很高，但事实上，效率提高了。为什么呢？
——其实这就是索引下推；

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