Mysql的联合索引

一、媒介

上一篇中已经讲过了索引相关的知识，为什么还要在讲一下联合索引（二级索引），是由于这个知识点特殊告急，不论是在面试中，还是在实际的使用过程中，明白和掌握联合索引，是我们提拔数据库查询优化操作大概提拔性能的告急手段之一。
二、什么是联合索引

Mysql从物理存储大将索引上分为：分为聚簇索引和非聚簇索引
  主键索引也被称为聚簇索引（clustered index），也叫作聚集索引。其余都称谓为非主键索引也被称为二级索引（secondary index），也叫作辅助索引。
它们区别就在于叶子节点存放的是什么数据：

• 聚簇索引的叶子节点存放的是实际数据，所有完备的用户记载都存放在聚簇索引的叶子节点；
  
• 二级索引的叶子节点存放的是主键值，而不是实际数据。
  

联合索引属于非聚簇索引的一种，也称为二级索引，使用多个字段来共同构建成一个索引：

1. KEY idx_abc (a, b, c);

复制代码

2.1、聚簇索引

     InnoDB存储引擎表是索引构造表，即表中数据按照主键顺序存放。而聚集索引就是按照每张表的主键构造一棵B+树，同时叶子节点中存放的即为整张表的行记载数据 。
     我们也将聚集索引的叶子节点称为数据页。聚集索引的这个特性决定了索引构造表中数据也是索引的一部分。同B+树数据结构一样，每个数据页都通过一个双向链表来进行链接。
     由于实际的数据页只能按照一颗B+树进行排序，因此每张表只能拥有一个聚集索引。在多数情况下，查询优化器倾向于采用聚集索引。由于聚集索引能够在B+树索引的叶子节点上直接找到数据。此外，由于界说了数据的逻辑顺序，聚集索引能够特殊快地访问针对范围值的查询。查询优化器能够快速发现某一段范围的数据页需要扫描。

特点：
    1）自动建立，一个表只有1个。
    2）叶子节点包含所有用户记载（包罗隐藏列），record_type为0
    3）每层节点都是按照主键从小到大排序
    4）内节点（非叶子节点）：存储主键值以及页号， record_type为1
    注意：
    聚集索引的存储并不是物理上的连续，而是逻辑上的连续。这其中有两点
    a.  前面说过的页通过双向链表链接，页按照主键的顺序排序，
    b.  每个页中的记载是通过单向链表进行维护的，物理存储上可以同样不按照主键存储。
2.2、联合索引

     对于联合索引，叶子节点并不包含行记载的全部数据。叶子节点除了包含键值以外，每个叶子节点中的索引行中还包含了一个书签（bookmark)。该书签用来告诉InnoDB存储引擎哪里可以找到与索引相对应的行数据。由于InnoDB存储引擎表时索引构造表，因此InnoDB存储引擎的辅助索引的书签就是相应行数据的聚集索引键。
     辅助索引的存在并不影响数据在聚集索引中的构造，因此每张表上可以有多个辅助索引。当通过辅助索引来寻找数据时，InnoDB存储引擎会遍历辅助索引并通过叶级别的指针获得指向主键索引的主键，然后再通过主键索引来找到一个完备的行记载。
    如下图：

   特点：
   1）手动创建，可以有多个
   2）非叶子节点包含索引列、主键列、页号（page_no）
   3）叶子节点只包含索引列以及记载主键的值
   4）每层节点都是按照索引列的值从小到大排序（索引列值相同时按照主键排序）
三、覆盖索引-covering index

界说：如果一个索引包含（大概说覆盖）所有查询字段所需要的值，称之为覆盖索引，则只需要扫描索引而不需要回表。
   回表：回表：指从辅助索引（也称二级索引）查到主键值后，再去查主键索引（一级索引）然后才拿到数据。需要扫描两次 B + 树 才能拿到数据，而覆盖索引只需要扫描一次         即从辅助索引中就可以得到查询的记载，而不需要查询聚集索引中的记载。使用覆盖索引的一个好处是辅助索引不包含整行记载的所有信息，故其大小要远小于聚集索引，因此可以淘汰大量的IO操作。
       简朴来说，覆盖索引指的就是只需要在一棵索引树上就能获取SQL所需的所有列数据，无需回表，速率更快。
       覆盖索引是一种非常强盛的工具，能大大进步查询性能，只需要读取索引而不消读取数据有以下一些优点：
       1）索引项通常比记载要小，所以MySQL访问更少的数据
       2）索引都按值的大小顺序存储，相对于随机访问记载，需要更少的I/O
       3）覆盖索引对于InnoDB表尤其有用，由于InnoDB使用聚集索引构造数据，如果二级索引中包含查询所需的数据，就不再需要在聚集索引中查找了。
总结： 就是把单列的非主键 索引 修改为多字段的联合索引, 在一棵索引数上 就找到了想要的数据, 不需要去主键索引树上,再检索一遍 这个现象,称之为 索引覆盖。
小结：为什么要使用联合索引？

2-1、淘汰开销

建一个联合索引 (a, b, c)，实际上相称于建了 (a)、(a, b)、(a, b, c) 三个索引。如许我们就不需要创建 (a)、(b)、(c) 三个单值索引了。我们知道，每多一个索引，都会增加数据库写操作的开销和磁盘空间的开销，对于大量数据的表，使用联合索引会大大的淘汰开销！
2-2、覆盖索引

对联合索引 (a, b, c)，如果有如下的 SQL：select a, b, c from test where a=1 and b=2。那么 MySQL 可以直接通过遍历索引取得数据，而无需回表，从而淘汰了很多的随机 IO 操作。而淘汰 IO 操作，而淘汰随机 IO 是 DBA 主要的优化策略，在真正的实际应用中，覆盖索引是主要的提拔性能的优化手段之一。
2-3、进步服从

联合索引的字段越多，通过索引筛选出的数据越少。假如有 1000W 条数据的表，有如下 sql: select * from table where a=1 and b=2 and c=3，假设每个条件可以筛选出 10% 的数据，如果只有单值索引，那么通过该索引能筛选出 1000W * 10% = 100w 条数据，然后再回表从 100w 条数据中找到符合 b=2 and c=3 的数据，然后再排序，再分页。
但如果是联合索引，则通过索引直接筛选出的数据为：1000w * 10% * 10% * 10% = 1w，这服从的提拔可想而知！
四、最左前缀匹配原则（告急）

1、最左匹配原则的规则

最左前缀匹配原则指的是在使用联合索引时，MySQL 会根据索引中的字段顺序，从左到右依次匹配查询条件中的字段。如果查询条件与索引中的最左侧字段相匹配，那么 MySQL 就会使用索引来过滤数据，如允许以进步查询服从。
最左匹配原则会一直向右匹配，直到遇到范围查询（如 >、<）为止。对于 >=、<=、BETWEEN 以及前缀匹配 LIKE 的范围查询，不会停止匹配（相关阅读：联合索引的最左匹配原则全网都在说的一个错误结论）。
2、索引是否见效的场景

是否满足最左匹配原则是衡量联合索引掷中与否的依据。存在的场景比力多，假设我们创建了以 a, b, c 三个字段的联合索引 idx_abc(a, b, c)，下面我们分别展开讨论索引是否失效的场景。
2-1、全字段全值匹配

索引的全部字段都在查找条件当中，并且都是使用 = 进行全值匹配的情况下，索引是掷中见效的：

1. select * from table_name where a = '1' and b = '2' and c = '3'
2. select * from table_name where b = '2' and a = '1' and c = '3'
3. select * from table_name where c = '3' and b = '2' and a = '1'

复制代码

固然 where 子句几个搜索条件顺序调换了，但不影响查询结果，这是由于 MySQL 的查询优化器会自动调解 where 子句的条件顺序以使用恰当的索引，所以 MySQL 不存在 where 子句的顺序问题而造成索引失效。
2-2、从左到右按顺序匹配

1. select * from table_name where a = '1'
2. select * from table_name where a = '1' and b = '2'
3. select * from table_name where a = '1' and b = '2' and c = '3'

复制代码

只要是按照联合索引创建的字段从左到右的顺序依次使用，不管使用其中多少个字段，都会掷中索引。
2-3、缺失最左边的字段

1. select * from table_name where  b = '2'
2. select * from table_name where  c = '3'
3. select * from table_name where  b = '1' and c = '3'

复制代码

这种缺失了最左边 a 字段的情况就是违反最左匹配原则的典型例子，结果就是没有用到索引（索引失效）。
由于缺失了最左边的字段，导致索引数据结构 B+ 树不知道第一步该查哪个节点，从而需要去全表扫描了。在建立搜索树的时间 a 就是第一个比力因子，必须要先根据 a 来搜索，进而才能今后继承查询 b 和 c。
2-4、缺失中心的字段

假如去掉中心的字段，生存最左边和右边的字段（就是我们说的索引字段不连续）：

1. select * from table_name where a = '1' and c = '3'

复制代码

结果就是只用到了 a 列的索引，而 b 列和 c 列都没有用到。
由于在这种情况下进行数据检索时，B+ 树可以用 a 来指定第一步的搜索方向，但由于下一个字段 b 的缺失，所以只能先把 a = 1 的数据主键 ID 都找出来，然后通过查到的主键 ID 回表查询相关行，再去匹配 c 值的数据了。固然，这至少把 a = 1 的数据筛选出来了，总比直接全表扫描很多多少了
2-5、匹配范围值

出现匹配范围值的情况大概比力复杂或难以明白，但我们只需要牢记最左匹配原则的规则：遇到范围查询 (>、<、between、like) 时就会停止匹配。
比如下面这种情况：

1. select * from table_name where  a = 1 and b > 3 and c = 'mm';

复制代码

这种情况下，由于 a 是等值匹配，所以 B+ 树走完 a 索引之后 b 还是有序的，但走完 b 索引之后，由于 b 是范围匹配，所以此时 c 已经是无序的了，终极只使用了 (a, b) 两个索引（由于此时 c 就没法走索引，所以优化器只能根据 a, b 得到数据的主键 ID 回表查询，终极影响了执行服从）。
再比如下面的情况：

1. select * from table_name where  a > 1 and b > 1
2. select * from table_name where  a > 1 and a < 3 and b > 1;

复制代码

当多个列同时进行范围查找时，只有对索引最左边的谁人列进行范围查找才用到 B+ 树索引，也就是只有 a 用到索引，在 a > 1 和 1 < a < 3 的范围内 b 是无序的，所以 b 不能用索引，找到 a 的记载后，只能根据条件 b > 1 继承逐条过滤。
2-6、like 语句匹配问题

当索引列是字符型，并且使用了 like 语句进行含糊查询时，如果通配符 % 不出现在开头，则可以用到索引，否则将会违反了最左匹配原则，而不会使用索引，走的是全表扫描：

1. select * from table_name where a like 'As%';   //走索引查询
2. select * from table_name where a like '%As';   //全表查询
3. select * from table_name where a like '%As%';  //全表查询

复制代码

我们先相识一下字符型字段的比力规则：当列是字符型的话，它的比力规则是先比力字符串的第一个字符，第一个字符小的谁人字符串就比力小，如果两个字符串第一个字符相同，那就再比力第二个字符，依次类推。
所以，如果通配符 % 出现在开头，B+ 树则无法进行比力匹配，进而导致索引失效。
3、办理文件排序的问题

当我们对查询的数据进行 order by 排序时，一般情况下，我们是先把数据记载加载到内存中，再用一些排序算法，比如快速排序，归并排序等在内存中对这些记载进行排序。但有时间查询的结果集太大不能在内存中进行排序时，需要临时借助磁盘空间存放中心结果，排序操作完成后再把排好序的结果返回客户端。Mysql 把这种在磁盘上进行排序的方式称为文件排序（Filesort）。
文件排序黑白常慢非常耗性能的，但如果 order by 子句用到了索引列，就有大概避免文件排序的问题：

1. select * from table_name order by a, b, c limit 10;

复制代码

由于 B+ 树索引本身就是按照上述规则排序的，正确来说就是：索引是有序的，所以得到的结果集已经排好序了，不消再进行额外的排序操作。
注意：order by 的子句后面的字段顺序也必须按照索引字段的顺序给出，不能颠倒顺序（MySQL 不会自动调解排序字段的顺序）。
下面这种就是由于颠倒顺序而没有使用索引的情况：

1. select * from table_name order by b, c, a limit 10;

复制代码

下面这种是用到部分索引的情况：

1. select * from table_name order by a limit 10;
2. select * from table_name order by a, b limit 10;

复制代码

下面这种情况，由于联合索引左边列为常量，后边的列排序可以用到索引：

1. select * from table_name where a =1 order by b, c limit 10;

复制代码

五、常见问题解答

      比条件：test表中有c1,c2,c3这列，建立联合索引（c1,c2,c3）
      1、 索引的掷中与查询sql中字段的顺序有关吗？
      比如 select * from test where c1=2 and c2=5; 与 select * from test where c2=5 and c1=2？
      答案：与查询的字段顺序无关，由于查询优化器会对搜索字段顺序跟索引字段顺序不划一的sql进行优化。
      2、最左前缀原则如何明白，它的原理是什么？
     最左前缀原是指从索引的有效掷中来说，并不是触发。只要是索引，大概某个联合索引的一部分，就会被触发，详细掷中了那些索引字段，要根据key_len来做判断。
    原理：
      我们从上面的联合索引介绍中看到，它的排序方式是：每层节点先按照索引中的第1列排序。第1列值相等时，按第2列排序。第2列值相等时，按第3列排序 依次类推，所有列都相等时按照主键排序。
      所以我们可以如许来分析：
       对于联合索引中c1字段是放在最前面的，所以c1是完全有序的，但是c1不知情的条件下，对于c2字段就是无序的，没办法排序。因此只有当c1相同的时间，c2字段的索引排序才是完全有序的。
       因此，在联合索引中你只有使用以下的规则的方式查询才会使用到索引：
      c1
       c1 ,c2
      c1, c2, c3
       我们再思考一下，select * from test where c2>=5 and c3=7;  这条sql语句会掷中索引么？
      分析：这里使用了c2和c3这两个字段作为查询条件，但是没有使用c1字段，由于在c1不知情的条件下，对于c2是无序的。对于c2>=5的条件大概在很多的c1不同中都有符合条件的出现，所以就没有办法使用索引，这也是索引实现的缘故原由，一定要遵照「查找有序，充分使用索引的有序性」。
      再比如 select * from test where c1>=5 and c2=9;  
      分析：这个查询语句中，c1列索引会被掷中，c2列却不会，只有c1列值相等时，才会按c2列排序。但是由于这里的c1>=5，那么c1列是不确定的，后面也就无法按照c2来排序，c2不会被掷中。
       3、前导含糊查询为什么会导致索引失效？
       比如 select  *  from test where c1  like '%d%'; 
       字符串的查询是对字符串里面的字符一个一个的匹配，「如果字符串最左边为%表现一个不确定的字符串，那么是没办法使用到索引的有序性」。
       但是如果修改为 ：where  c1 like 'd%'；就可以使用索引，由于最左边的字符串是确定的，这种称为「匹配列前缀」。
       补充说明：实际业务场景中联合索引的创建，「我们应该把辨认度比力高的字段放在前面，进步索引的掷中率，充分的使用索引」。
       4、 如何办理数量占表记载比重较大，查询优化器放弃索引，直接全表扫描？
       通过限定查询条数来避免索引失效，比如 select * from test where c2=8 limit  10;
       5、为什么MySQL的InnoDB引擎采用B+树而不是B树？
       1）范围查询服从高
       B+树在非叶子节点只存储键值信息，而不存储数据记载的详细位置，这使得B+树在进行范围查询时更加高效。范围查询通常涉及到一系列相邻的键值，B+树的叶子节点形成了一个有序链表，可以很方便地进行范围扫描。 
       2）更恰当磁盘存储
       B+树的叶子节点形成了一个有序链表，便于顺序I/O，淘汰磁盘I/O的次数。这对于数据库来说非常告急，由于磁盘I/O是一个相对较慢的操作，通过淘汰I/O次数，可以进步查询性能。
       3）更恰当范围查询
       B+树中叶子节点使用双向指针相连接，形成一条双休有序链表，以及在叶子节点存储了所有关键字的信息，使得范围查询更为高效。而B树则需要在非叶子节点中存储所有关键字的信息，限定了非叶子节点的容量，不太恰当范围查询。
       4）更恰当内存的使用
       B+树的内部节点只存储键值信息，而不存储详细数据，这意味着在同样的内存空间下，B+树可以容纳更多的节点，进步了缓存掷中率，淘汰了内存占用。总体而言，B+树更恰当数据库索引的应用场景，特殊是对于范围查询和大数据集的情况。因此，InnoDB引擎选择了B+树作为其索引结构。
       6、二级索引与null值的关系
       值为NULL的二级索引记载被放在了B+树的最左边。这是由于InnoDB的设计中有如许的规定：
     We define the SQL null to be the smallest possible value of a field.
       翻译：我们把SQL中的NULL值以为是列中最小的值。
 六、如何挑选索引

1、只为用于搜索、排序或分组的列创建索引
2、思量列的基数
      列基数：列值不重复的数，基数越大索引效果越好
3、索引列的范例尽量小
     1）数据范例越小，在查询时进行的比力操作越快
     2）数据范例越小，数据页内就可以放下更多的记载，从而减小磁盘I/O带来的性能消耗
4、索引字符串的前缀
5、索引列在比力表达式中单独出现
6、主键顺序插入（综合评估页分裂、回表、索引树大小）
七、索引总结：

1、B+树索引在空间和时间上都有代价，所以必须公道创建索引
2、B+树索引适用于下边这些情况：全值匹配
1）匹配左边的列
2）匹配范围值
3）精确匹配某一列并范围匹配另外一列
4）用于排序
5）用于分组
3、在使用索引时需要注意下边这些事项
1） 只为用于搜索、排序或分组的列创建索引
2） 为列的基数大的列创建索引
3） 索引列的范例尽量小
4） 可以只对字符串值的前缀建立索引
5） 只有索引列在比力表达式中单独出现才可以适用索引
6） 为了尽大概少的让聚簇索引发生页面分裂和记载移位的情况，发起让主键拥有auto_increment属性
7） 定位并删除表中的重复和冗余索引
8）尽量使用覆盖索引进行查询，避免回表带来的性能消耗。
这里有几点要注意下：
1）字符串比力大小：逐字比力，费时，服从低，不发起。
2）key_len表现索引中使用的字节数，查询中使用的索引的长度（最大大概长度），并非实际使用长度，理论上长度越短越好。key_len是根据表界说计算而得的，不是通过表内检索出的。
参考文献：

https://www.51cto.com/article/720535.html
https://www.cnblogs.com/hld123/p/14749217.html
五分钟告诉你什么是MySQL的覆盖索引_mysql什么是覆盖索引-CSDN博客
MySQL 覆盖索引详解 - 掘金

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。