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

心已赠人 2022-10-16 09:56 277阅读 0赞

### 1、联合索引的存储 ###

**1）联合索引结构：**

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

![2021052918014510.png][]

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

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

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpdXhpYW83MjM4NDY_size_16_color_FFFFFF_t_70][]

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

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

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpdXhpYW83MjM4NDY_size_16_color_FFFFFF_t_70 1][]

存储引擎首先从根节点（一般常驻内存）开始查找，以此从索引节点中比较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";

执行计划如下：

![20210529180728303.png][]

**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][liqiang156_11.com]"; 语句来查询  
![20210529180922188.png][]

*  使用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）不在索引上进行任何操作**

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

![20210529181608713.png][]

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

![2021052918155076.png][]

**1.2）隐式类型转换**

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

![20210529181531256.png][]

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

**1.3）只查询需要的列**

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

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

**1.4）不等式条件**

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

**1.5）模糊匹配查询**

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

**1.6）最左匹配原则**

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

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

例如：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。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpdXhpYW83MjM4NDY_size_16_color_FFFFFF_t_70 4][]

[2021052918014510.png]: /images/20221014/97b3339c8d9b4158be66fa6fd8cf7b42.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpdXhpYW83MjM4NDY_size_16_color_FFFFFF_t_70]: /images/20221014/eb9e22af5c334842be27f9cc04d933ca.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpdXhpYW83MjM4NDY_size_16_color_FFFFFF_t_70 1]: /images/20221014/19ac9064fd364ddf87e1f02ca9b3e586.png
[20210529180728303.png]: /images/20221014/08d896443514404e851cd66c82e6857e.png
[liqiang156_11.com]: mailto:liqiang156@11.com
[20210529180922188.png]: /images/20221014/7916bf4c7b574e73906d330534136696.png
[20210529181608713.png]: /images/20221014/9f529019148c45c99ce6022786fee495.png
[2021052918155076.png]: /images/20221014/1ad920e526064a389c7a752050bb6f35.png
[20210529181531256.png]: /images/20221014/a5cd86d9be614c629d995d4fe97577df.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpdXhpYW83MjM4NDY_size_16_color_FFFFFF_t_70 2]: /images/20221014/c35f3c7806994833a94923f1b9d87c7f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpdXhpYW83MjM4NDY_size_16_color_FFFFFF_t_70 3]: /images/20221014/8848d6d56e7947d9812181367a12530f.png
[20210529181227730.png]: /images/20221014/aff6999efbbd4161b1b28d3a0a17bc6c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpdXhpYW83MjM4NDY_size_16_color_FFFFFF_t_70 4]: /images/20221014/e9d430d1b51d4de39929d5af1dd334c8.png