mysql索引优化 & 最左匹配 & 索引下推

1、联合索引的存储

1）联合索引结构：

表T1有字段a,b,c,d,e，其中a是主键，除e为varchar其余为int类型，并创建了一个联合索引idx_t1_bcd(b,c,d)。其内容如下：

InnoDB首先会使用主键创建一个主键B+树索引和数据文件，此外还会通过联合索引（b，c，d）生成一个索引树，同样是B+树的结构，只不过它的data部分存储的是联合索引所在行的主键值（上图叶子节点紫色背景部分），这里要注意，对于InnoDB存储引擎，辅助索引data部分存储主键值。

对于联合索引来说只不过比单值索引多了几列，而这些索引列全都出现在索引树上，存储引擎会首先根据第一个索引列排序，如果第一列相等则再根据第二列排序，依次类推就构成了下图的索引树：

2）联合索引的查找方式：

比如select * from T1 where b = 12 and c = 14 and d = 3;

存储引擎首先从根节点（一般常驻内存）开始查找，以此从索引节点中比较a、b、c列的值，最终找到合适的叶子节点，根据叶子结点下的data元素即ID值，再从主键索引树上找到最终数据。

我们创建的index_bcd(b,c,d)索引，相当于创建了(b)、（b、c）（b、c、d）三个索引，看一下一下查询使用的索引情况：

select * from T1 where b = 12 and c = 14 and d = 3;-- 全值索引匹配 三列都用到
select * from T1 where b = 12 and c = 14 and e = 'xml';-- 应用到两列索引
select * from T1 where b = 12 and e = 'xml';-- 应用到一列索引
select * from T1 where b = 12  and c >= 14 and e = 'xml';-- 应用到bc两列列索引及索引条件下推优化
select * from T1 where b = 12  and d = 3;-- 应用到一列索引  因为不能跨列使用索引 没有c列 连不上
select * from T1 where c = 14  and d = 3;-- 无法应用索引，违背最左匹配原则

2、最左前缀原理

当一个索引中包含多个字段时，可以称之为组合索引。MySQL中有个很重要的规则，即最左匹配原则用来定义组合索引的命中规则，它是指在检索数据时从联合索引的最左边开始匹配。假设对用户表建立一个联合索引（a，b，c），那么条件a，（a，b），（a，b，c）都会用到索引。

在匹配过程中会优先根据最左前面的字段a进行匹配，然后再判断是否用到了索引字段b，直到无法找到对应的索引字段，或者对应的索引被”破坏“（下文中会介绍）。

3、正确创建索引

既然索引可以加快查询速度，那么是不是只要是查询语句需要，就建上索引？答案是否定的。因为索引虽然加快了查询速度，但索引文件本身要消耗存储空间，同时索引会加重插入、删除和修改记录时的负担；另外，MySQL在运行时也要消耗资源维护索引，因此索引并不是越多越好。一般两种情况下不建议建索引：

1. 记录比较少；（全表扫描更快）
2. 选择性低的列不建议建索引；

1.1）尽量使用自增长主键：

使用自增长主键的原因笔者认为有两个。首先能有效减少页分裂，MySQL中数据是以页为单位存储的且每个页的大小是固定的（默认16kb），如果一个数据页的数据满了，则需要分成两个页来存储，这个过程就叫做页分裂。

如果使用了自增主键的话，新插入的数据都会尽量的往一个数据页中写，写满了之后再申请一个新的数据页写即可（大多数情况下不需要分裂，除非父节点的容量也满了）。

其次，对于缓存友好。系统分配给MySQL的内存有限，对于数据量比较多的数据库来说，通常只有一小部分数据在内存中，而大多数数据都在磁盘中。如果使用无序的主键，则会造成随机的磁盘IO，影响系统性能。

1.2）选择性高的列优先：

索引的选择性，也可称为数据的熵。在创建索引的时候通常要求将选择性高的列放在最前面，对于选择性不高的列甚至可以不创建索引。如果选择性不高，极端性情况下可能会扫描全部或者大多数索引，然后再回表，这个过程可能不如直接走主键索引性能高。

索引列的选择往往需要根据具体的业务场景来选择，但是需要注意的是索引的区分度越高则价值就越高，意味着对于检索的性价比就高。索引的区分度等于count(distinct 具体的列) / count(*)，表示字段不重复的比例。

唯一键的区分度是1，而对于一些状态值，性别等字段区分度往往比较低，在数据量比较大的情况下，甚至有无限接近0。假设一张表中用data_status来表示数据的状态，1-有效，2-删除，则数据的区分度为 1/500000。如果100万条数据中只有1条被删除，并且在查询数据时查找data_status = 0 的数据时，需要进行全表扫描。由于索引也是需要占用内存的，所以在内存较为有限的环境下，区分度不高的索引几乎没有意义。

1.3）联合索引优先于多列独立索引：

联合索引优先于多列独立索引， 假设有三个字段a,b,c, 索引（a）(a,b)，(a,b,c)可以使用(a,b,c)代替。MySQL中的索引并不是越多越好，各个公司的规定中往往会限制单表中的索引的个数。原因在于，索引本身也会占用一定的空间，并且维护一个索引时有一定的代码的，所以在满足需求的情况下一定要尽可能创建更少的索引。

1.4）覆盖索引避免回表：

覆盖索引如果执行的语句是 select ID from T where k between 3 and 5，这时只需要查 ID 的值，而 ID 的值已经在 k 索引树上了，因此可以直接提供查询结果，不需要回表。也就是说，在这个查询里面，索引 k 已经“覆盖了”我们的查询需求，我们称为覆盖索引。由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。

覆盖索引优化：

覆盖索引同时还会影响索引的选择，对于（a，b，c）索引来说，一下查询理论上来说不满足最左匹配原则，无法使用（a,b,c）索引，但是实际上也会走索引。原因在于，优化器认为（a，b，c）索引的性能会高于全表扫描，实际情况也是这样的，感兴趣的小伙伴不妨分析一下上文中介绍的数据结构。

explain select a,b,c from test_table where b = "188466668888" and c = "23";

 

执行计划如下：

 

1.5）索引可以包含排序的列：

 

select c, d from test_table  where a = 1 and b = 2 order by c;

虽然查询条件只使用了a,b两个字段，但是由于排序用到了c字段，我们能可以建立(a,b,c)联合索引来进行优化。

1.6）考虑索引的大小——前缀索引：

内存中的空间十分宝贵，而索引往往又需要在内存中。为了在有限的内存中存储更多的索引，在设计索引时往往要考虑索引的大小。比如我们常用的邮箱，xxxx@xx.com, 假设都是abc公司的，则邮箱后缀完全一致为@abc.com, 索引的区分度完全取决于@前面的字符串。

针对上述情况，MySQL 是支持前缀索引的，也就是说，你可以定义字符串的一部分作为索引。默认地，如果你创建索引的语句不指定前缀长度，那么索引就会包含整个字符串。

语法：alter table tab_name add ind_name(col(n)); --即指定col列的前n个字符来创建索引。

1）我们先看一个例子：

假设表test建立了两个索引，alter table test add ind_1(email)和alter table test add ind_2(email(7))，我们通过select * from test where emial="liqiang156@11.com"; 语句来查询

 

• 使用ind_1索引：从 ind_1 索引树找到满足索引值是’liqiang156@11.com’的这条记录，取得 id （主键）值，到主键上查到对应主键值的行，将这行记录加入结果集；然后取 email 索引树上刚刚查到的位置的下一条记录，发现已经不满足 email='liqiang156@qq.com’的条件了，循环结束。这个过程中，只需要回主键索引取一次数据，所以系统认为只扫描了一行。但是它的问题就是索引的后半部分都是重复的，浪费内存。
• 使用ind_2索引：从ind_2索引树找到满足索引值是’liqiang’的记录，找到的第一个是 ID1，到主键上查到主键值是 ID1 的行，判断出 email 的值是’liqiang156@xxx.com’，加入结果集；取ind_2上刚刚查到的位置的下一条记录，发现仍然是’liqiang’，取出 ID2，再到 ID 索引上取整行然后判断，这次值仍然不对，则丢弃继续往下取。重复上一步，直到在 ind_2 上取到的值不是’liqiang’或者索引搜索完毕之后，循环结束。在这个过程中，要回主键索引取 4 次数据，也就是扫描了 4 行。通过这个对比，你很容易就可以发现，使用前缀索引后，可能会导致查询语句读数据的次数变多。

所以，在使用前缀索引的时候需要提前预估余下前缀长度，否则就会出现多次回表查询的情况。

此外，对于一些比较长的字段的等值查询，我们也可以采用其他方式来缩短索引的长度。比如url一般都是比较长，我们可以冗余一列存储其Hash值。对于我们国家的身份证号，一共 18 位，其中前 6 位是地址码，所以同一个县的人的身份证号前 6 位一般会是相同的。为了提高区分度，我们可以将身份证号码倒序存储。

2）缺点：

前缀索引兼顾索引大小和查询速度，但是其缺点是不能用于ORDER BY和GROUP BY操作，也不能用于覆盖索引Covering index（即当索引本身包含查询所需全部数据时，不再访问数据文件本身）；使用不当会造成多次回表查询。

4、正确使用索引

建立合适的索引是前提，想要取得理想的查询性能，还应保证能够用到索引。避免索引失效即是优化。

1.1）不在索引上进行任何操作

索引上进行计算，函数，类型转换等操作都会导致索引从当前位置（联合索引多个字段，不影响前面字段的匹配）失效，可能会进行全表扫描。

 

对于需要计算的字段，则一定要将计算方法放在“=”后面，否则会破坏索引的匹配，目前来说MySQL优化器不能对此进行优化。

 

1.2）隐式类型转换

需要注意的是，在查询时一定要注意字段类型问题，比如a字段时字符串类型的，而匹配参数用的是int类型，此时就会发生隐式类型转换，相当于相当于在索引上使用函数。

 

a是字符串类型，然后使用int类型的1进行匹配,此时就发生了隐式类型转换，破坏索引的使用。

1.3）只查询需要的列

在日常开发中很多同学习惯使用 select * … 来构建查询语句，这种做法也是极不推荐的。主要原因有两个，首先查询无用的列在数据传输和解析绑定过程中会增加网络IO，以及CPU的开销，尽管往往这些消耗可以被忽略，但是我们也要避免埋坑。

其次就是会使得覆盖索引"失效", 这里的失效并非真正的不走索引。覆盖索引的本质就是在索引中包含所要查询的字段，而 select * 将使覆盖索引失去意义，仍然需要进行回表操作，毕竟索引通常不会包含所有的字段，这一点很重要。

1.4）不等式条件

查询语句中只要包含不等式，负向查询一般都不会走索引，如 !=, <>, not in, not like等。

 

1.5）模糊匹配查询

最左前缀在进行模糊匹配时，一般禁止使用%前导的查询，如like “%zhangsan”。

 

1.6）最左匹配原则

索引是有顺序的，查询条件中缺失索引列之后的其他条件都不会走索引。比如(a, b, c)索引，只使用b, c索引，就不会走索引。

如果索引从中间断开，索引会部分失效。这里的断开指的是缺失该字段的查询条件，或者说满足上述索引失效情况的任意一个（比如不等式查询、类型转换）。不过这里的仍然会使用到索引，只不过只能使用到索引的前半部分。

 

例如：b字段使用了不等式查询，c字段使用了类型转换，但仍然可以使用部分索引。

1.7）索引下推

索引下推（index condition pushdown ）简称ICP，在Mysql5.6的版本上推出，用于优化查询。

• 在不使用ICP的情况下，在使用非主键索引（又叫普通索引或者二级索引）进行查询时，存储引擎通过索引检索到数据，然后返回给MySQL服务器，服务器然后判断数据是否符合条件 。
• 在使用ICP的情况下，如果存在某些被索引的列的判断条件时，MySQL服务器将这一部分判断条件传递给存储引擎，然后由存储引擎通过判断索引是否符合MySQL服务器传递的条件，只有当索引符合条件时才会将数据检索出来返回给MySQL服务器 。

索引条件下推优化可以减少存储引擎查询基础表的次数，也可以减少MySQL服务器从存储引擎接收数据的次数。

已知有索引(a,b,c)，则根据条件(a,c)查询时会不会走索引呢？根据上面最左匹配原则可知，一定会走索引的，即使没有索引下推也会会根据最左匹配原则，使用到索引中的a字段。有了索引下推之后会增加查询的效率：可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数，是对查询的一种优化，感兴趣的同学可以看一下官方说明https://dev.mysql.com/doc/refman/8.0/en/index-condition-pushdown-optimization.html。

看一个实际的例子：

user表主要字段有：id、name、age、address，并建立联合索引（name，age），执行如下sql：SELECT * from user where name like '陈%' and age=20

• 5.6之前的版本是没有索引下推这个优化的，会忽略age这个字段，直接通过name进行查询，在(name,age)这课树上查找到了两个结果，id分别为2,1，然后拿着取到的id值一次次的回表查询，因此这个过程需要回表两次。
• 5.6版本添加了索引下推这个优化，InnoDB并没有忽略age这个字段，而是在索引内部就判断了age是否等于20，对于不等于20的记录直接跳过，因此在(name,age)这棵索引树中只匹配到了一个记录，此时拿着这个id去主键索引树中回表查询全部数据，这个过程只需要回表一次。

利用索引下推，每次尽可能在辅助索引中将不符合条件数据过滤掉。比如，索引中已经包含了name和age，索引不妨暂且忽略破坏索引匹配的条件直接匹配。

 

1.8）查询优化-自适应索引顺序

查询时，mysql的优化器会优化sql的执行，即使查询条件的顺序没有按照定义顺序来使用，也是可以使用索引的。但是需要注意的是优化本身也会消耗一定的性能，所以还是推荐按照索引的定义来书写sql。