MySQL索引，你可能不知道的那些dd

MySQL索引

今天聊点啥呢，嗯 ，思来想去，辗转反侧夜不能寐，梦回毕业那年夏天:

“他喵的，今天面试又被怼了，气的我又多吃了2碗饭，老这么整也不是个事，面试过不过另说，米饭也受不了啊！是时候跟索引掰扯掰扯了，万事开头难，从哪下手呢。。。”

后来自己查资料看书，面试的时候碰到索引也能聊点东西糊弄过去了，现如今再翻开当时零零散散的笔记，em。。。是时候该重新整理整理了，话不多说，起身开搞，索引，面对疾风吧！

索引其实和我们书的目录很像，它的作用就是能帮我们快速定位到我们想要查找的记录，如果想彻底搞明白索引，你首先要知道记录的结构、页的结构、记录在页中是如何存储的、在单独的一页中是如何查找数据的、为什么根据主键查找数据速度就快、多页的时候又是如何查找数据的等等巴拉巴拉一大堆东西，好那我们就从记录的结构开始说起！

一、记录结构

我们平时与数据库打交道时，基本上操作都是针对记录进行操作，根据我们自己定义，它可能长成这样，

那么记录的结构，是不是就是我们平时看到的这样呢？答案肯定不是哈，不然就不用单独拿它出来说了，那么它到底长相何如呢？

我们现在使用的MySQL版本大多数5.7或者5.8，所以默认使用的存储引擎是Innodb，所以我们就说说Innodb中的记录结构。

Innodb中共有4种不同的记录格式，分别是Compact、Redundant、Dynamic和Compressed，可以在建表的时候通过命令ROW_FORMAT指定选择使用哪种记录格式，比如在test数据库下创建一张user表，使用COMPACT行格式，语句如下：

CREATE TABLE `test`.`user`  (
  `id` bigint(100) NOT NULL,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `age` int(10) NULL DEFAULT NULL,
  `deleted` int(11) NULL DEFAULT 0,
  `create_time` datetime(0) NULL DEFAULT NULL,
  `update_time` datetime(0) NULL DEFAULT NULL,
  `version` int(255) NULL DEFAULT 1,
  `tenant_id` bigint(20) NOT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `name_index`(`name`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = COMPACT;

如果我们不指定行格式的话，默认使用的是Dynamic，整体结构如图所示，

也就是说，一条完整的记录实际上可以被分为记录的额外信息和记录的真实数据两大部分，真实数据部分是给我们看的，而记录的额外信息是给MySQL服务器看的。

从图中可以得知，记录的额外信息包括变长字段长度列表、NULL值列表以及记录头信息三部分组成，下面分别来看看这些我们平时看不到的额外信息都是干麻地。

1.1、记录的额外信息

1、变长字段长度列表

我们平时使用比较多的类型比如varchar、varbinary、text等都属于变长类型，其特点就是变长类型的字段存储多少字节的数据是不固定的，为了能够让MySQL服务器知道变长字段占用的空间，所以特意设计了变长字段长度列表来存储变长字段占用的字节数（其实应该称为非NULL变长字段长度列表，因为它只存储值为非NULL的变长列的长度，值为NULL 的变长列的长度不需要储存 ）。

存放的时候按照列的顺序逆序存放，并约定当列的长度小于255字节，用1字节表示，若大于255个字节，用2个字节表示（变长字段的长度最大不可以超过2个字节，这也就解释了为什么MySQL中varchar的最大长度为65 535，因为2个字节为16位，即2^16-1=65 535）。

需要注意的是，并不是所有记录都有这个变长字段长度列表部分，如果表中所有的列都不是变长的数据类型的话，就不会有变长字段长度列表。

对于变长字段长度列表的概念我们知道有这么个东西就行，跟我们今天讨论的重点索引关系不是很大，点到为止，如果感兴趣的兄弟们可以再往深研究研究哈。

2、NULL值列表

表中的某些列可能为空值，如果把空值存到记录的真实数据中，会很占地方，所以把一条记录中为空的列统一管理起来，存到null值列表中，按照列的顺序逆序排列，每个列用一个二进制位表示，1表示该列为null，0表示不为null。

同样的，点到为止，你懂的！

3、记录头信息（重要）

记录头信息，说白了就是描述记录用的，其中有这么几个名称需要我们了解一下：

delete_mask： 删除标记，标识当前记录是否被删除，0未删除，1表示删除。这些删除的记录之所以不立刻在磁盘上移除，是因为移除之后其它的记录在磁盘上重新排列需要性能消耗，所有被标识1的记录会被组成一个垃圾链表，这个链表中记录占用的空间为可重用空间，之后如果有新记录插入，会覆盖掉可重用空间；

heap_no： 当前记录在记录堆中的位置

record_type： 记录类型，0表示普通记录；1表示目录项记录；2表示最小记录；3表示最大记录。

n_owned： 标识该记录所在的组拥有的记录数，组中最后一条记录才会标记这个属性。

next_record： 表示当前记录到下一条记录的偏移量，这是不是跟链表有点像？不急，等下聊它。

关于记录头信息中这些名词及含义，大伙先混个脸熟，看不懂也不用纠结，一会你就知道是怎么回事了，别放弃，继续跟我向下看，相信我，等下你一定会回过头重新看这部分的。

1.2、记录的真实数据

记录的真实数据除了平时我们看到的自己定义的列数据以外，MySQL还会为每个记录默认的添加一些列也称为隐藏列（准确的说应该是Innodb存储引擎自动帮我们生成的），如下所示

• DB_ROW_ID（可选）：行id，用于唯一标识一条记录，只有当我们没有自定义主键以及Unique键的情况下才会添加该列作为主键
• DB_TRX_ID（必选）：事务ID
• DB_ROLL_PTR（必选）：回滚指针

也就是说InnoDB存储引擎会为每条记录都添加事务ID和回滚指针这两个列（这两个列的作用将来分析事务的时候会详细讲，这里我们先知道有这么个东西就行），但ROWID是可选的。

二、页的结构

我们表中的数据都是存储到磁盘当中的，这样才保证了数据的持久性，需要的时候再到磁盘中把数据加载到内存中处理，处理完后再刷回磁盘中，那么它具体是怎么做的呢？

InnoDB存储引擎工作时，采用了分治的概念，就是将数据划分为若干个页，以页作为基本单位，一般每页大小为16k，所以数据在磁盘和内存间交互时，一次最少从磁盘读取的数据量是一页大小也就是16k；向磁盘写的时候也是如此；

根据不同的使用场景，MySQL提供了不同类型的页，由于我们今天讨论的重点是索引，所以下面我们就具体来看看索引页的结构。

一个完整的索引页大概由以下部分组成：

1、页面描述信息

存储当前页的地址、页号、页的类型、页里面有多少条记录、上一页和下一页的页号等。

2、用户记录空间

专门存储我们手动添加的记录，也就是真实记录，为了快速查找定位到这些记录，MySQL还是费了不少心思，等下具体说。

3、空闲空间

表示页中尚未使用的空间，实际上在页初始化的时候并没有用户记录空间，随着我们的使用，每次插入记录的时候，就会从空闲空间挪出一个记录大小的空间划到用户记录空间；当空闲空间使用完后再插入新记录的时候，就需要申请新的页了。

4、最小和最大记录

MySQL自定义的两条记录，最小记录指向本页中主键值最小的记录；本页中主键值最大的记录指向最大记录。

5、页目录

存储页中某些记录地址的偏移量，由槽组成，在页中查找数据就靠这玩意，等下具体说。

同样的，关于页的结构中这些名词及含义，大伙先混个脸熟，看不懂也不用纠结，一会你就知道是怎么回事了，别放弃，继续跟我向下看，相信我，等下你一定会回过头重新看这部分的。

三、记录在页中的存储

关于记录的结构、页的结构这些概念性的东西简单的带着大伙过了一遍，相信大伙已经从开始的一知半解发展到现在满脸懵逼的状态了，节操碎了一地，下面我们就开始破镜重生，一点一点的把节操整回来！正题开始，下一个问题，记录在页中是如何存储的呢？

为了方便说明，我们实际弄个表，插几条记录，这样看着直观点，如图所示

当然了，这只是我们平时看到的数据，还记得我们刚才提到过的记录结构吗?那么实际上，它是这个样子的，

如图所示，我们手动添加的记录都会放到用户记录空间里面，存放的顺序不是插入的顺序，而是按照id即主键值从小到大的顺序存放，此外上面next_record指向的箭头表示指向该记录，可不是指向delete_mask属性的意思，大家不要误解哈，具体说明如下：

• delete_mask，删除标记，因为没有删除，所以都是0；

• heap_no，表示该记录在本页当中的位置，这里需要注意一下，规定0号位置为最小记录，1号位置为最大记录；

• n_owned，现在还是不太好解释，等下再说；

• record_type，记录类型，我们手动插入的记录为普通记录，type为0，最小记录为2，最大记录为3，那么1表示什么呢？还是等下再说！

• next_record，从图中也可以看的出来，记录之间通过该属性最终形成一个单向链表，图中的10是我随便写的，它表示从当前记录的真实数据到下一条记录的真实数据的地址偏移量为10，也就是说从当前记录的地址向后找10个字节就是下一条记录；

  需要注意的是，默认规定最小记录指向本页中主键值最小的用户记录；而本页中主键值最大的用户记录指向最大记录，从图中可以看到最大记录的next_record为0，也就是说它是本页当中最后一条记录。

• 其它信息，因为和我们下面的分析关系不大，所以我就略过了，但不代表它不存在！

现在我们应该知道了，记录在页中是按照主键值从小到大的顺序最终形成一个单链表摆在用户记录空间，可是，这跟索引有什么关系呢？ok，我们一步一步来，先抛开索引的概念，看看在单页中是如何查找数据的。

四、在单页中查找记录

我们刚才插入了5条数据，假设我现在需要查找贝贝的相关信息应该怎么做呢？

最笨的方法就是从头找，我们只需要比较主键值即可，直到找到为止；如果当主键值大于我们要找的主键时就可以停止遍历了，因为链表是按主键顺序排列的。

如果记录少的话，这样做倒还可以，如果记录变多后，明显效率不行，有么有什么更优的办法呢?

设计InnoDB的大叔们从书的目录找到了灵感，我们平时翻书找查资料的时候，一般都是先在目录找相关内容对应的页号，然后再到具体的页查找内容，所以在页中为了方便查找数据，也为我们提供了一个类似目录功能的东东，在页的结构里面我们称之为页目录！

具体是这样做的：

• 首先将所有的记录进行分组，不包括标记为已删除的记录，具体分组规则我们等下单独说；
• 分组之后，在每个组的最后一条记录（也就是该组内主键值最大的那条记录）的头信息中通过n_owned属性记录一下当前分组中共有多少条记录；
• 将每个组中的最后一条记录的地址偏移量单独拿出来放到页目录当中，我们把这些地址偏移量称为槽，也就是我们前面说的页目录是由槽组成的；

分组规则：

• 最小记录所在的组只能有一条记录，就是它本身；最大记录所在的组可以有1-8条记录；其它组的记录数能在4-8条之间，在页的初期还没有添加用户记录的时候，只有两个分组，即最小记录和最大记录；
• 随着应用，我们每插入一条记录，都需要拿新插入记录的主键值去页目录中找主键值大于新值且差值最小的槽，然后根据槽找到对应的记录，将它的n_owned值加1，表示本组多了一条记录；
• 当一个组中的记录数大于8时（该组插入第九条记录时）需要扩容，将组中的记录拆分成两个组，一个组中4条记录，另一个5条记录，同时在页目录中新增一个槽记录刚分出来这个组内主键值最大的记录的偏移量；

为了演示分组之后的效果，我们再多插入几条记录，分组之后，如图所示

原谅我偷了个懒，省略了一些东西，全画出来看着比较乱，有了前面的介绍相信这个简易版的图也能看懂！

如图所示，现在连带最小、最大记录一共有18条记录，分成了5个组，每个组中主键值最大的记录的偏移量单独提出来放到页目录中，也就是槽0~槽4，那么我们现在如何查找记录呢？比如查找主键值为9的记录，它的过程是这样的：

• 定槽

  因为有了页目录了，和我们翻书查找过程相类似，首先我们先确定要查找记录所属的槽，当然你可以遍历，这么做除了慢没别的毛病，有没有更优的办法呢？因为我们的记录是从小到大的顺序排列的，所以槽也是这个顺序，所以我们可以采用二分法！

  1、取中间槽：(0+4)/2=2，槽2对应主键值为8，8<9,所以设置low=2，high不变；

  2、继续取中间槽：（2+4）/2=3，槽3对应主键值为10，10>9，所以设置high=3，low不变；

  3、high-low=1，所以确定主键值为9的记录应该在槽3对应的组中

• 遍历槽对应的组

  定位我们要找的记录所在的槽之后，只需要从该组中主键值最小的记录开始遍历即可，因为一个组中最多只有8条记录，所以遍历一个组中的记录速度还是很快的。

  由于我们前面说过槽对应的记录是一个组中主键值最大的记录，而记录之间又是单向链表，如何定位到该组中主键值最小的记录呢？

  我们通过观察可以发现到，页目录中的槽是挨着的并且是有序的，由于我们要找的记录在槽3当中，所以我们可以先定位到槽2对应的组，那么对应的就是槽2组中主键值最大的记录，也就是主键值为8的赵六，该记录的下一条记录也就是next_record属性指向的就是槽3当中主键值最小的记录，所以这样就拿到了槽3组中主键值最小的记录，从这开始遍历即可。

总结下在一页当中查找数据，其实分为两种情况，

• 在一页当中根据主键值查找数据的过程：

  1、 通过二分法确定该记录所在的槽（通过二分法计算得到槽，然后去查看这个槽对应的记录的主键值，拿这个值和我们要找的值比较，继续缩小查找范围，直到找到为止），并找到该槽中主键值最小的那条记录

  2、 确定查找数据所在的槽之后，从该组中主键值最小的记录开始，遍历该组的记录

• 在一页当中以其他列作为条件查找数据：

  对于其它列也就是非主键列来说，查找的时候就不能通过定槽+遍历的办法查找数据了，因为页中只针对主键值建立了页目录，所以当使用非主键列作为条件查找数据的时候，只能从本页当中的最小记录开始，依次向后遍历本页当中的全部数据，所以这就是为什么通过主键值查找的效率比较高的原因。

五、在多页中查找记录

单独的一个数据页可以存放的记录是有限的，随着数据量越来越多，所以我们的数据页也会越来越多，记录在页中是一个单向链表，页与页之间是个双向链表，大概长成这个样子，

那么在多页当中是如何定位到我们想查找的记录呢？

分为两种情况：

• 根据主键值查找

  首先确定要查找记录所属的页，如何定位呢？通过比较主键值范围确定记录所属的页（如果你不知道怎么比较确定记录属于哪个页的话，那么你应该是忘记了页中最小记录和最大记录的作用，所以这里的遍历只需比较页的最小记录和最大记录即可）；

  确定页之后，再通过我们前面提过的二分法定位槽然后遍历分组进行查找记录；

• 根据非主键值查找

  这个就比较麻烦了，没有捷径可以走，只能从第一个页的第一条记录开始，依次遍历所有页中的记录进行比较查找，可以想象，效率极低。。。

六、索引的诞生

1、聚集索引（主键索引）

通过分析在多页当中查找记录的过程，我们get到了痛点在哪，所以有必要针对这个痛点来提升下我们查找数据的效率。

这个痛点是什么呢？就是各个页是无序的，虽然单独一页中的记录是按主键值从小到大排列的，但是页与页之间主键值是无序的，所以我们只能通过遍历所有的页来确定我们要查找的记录。

找到问题的关键就好办了，还记得我们每个页中的页目录吗？通过它我们可以快速的通过二分法确定记录所在的槽，那我们是不是也可以在所有的数据页之间也维护这样一个目录来帮助我们快速定位到具体的页呢？

为了建立一个这样的目录，我们需要保证这样几件事：

• 维护所有页中主键值的顺序

  当我们把第一个页装满之后，这时会创建第二个页继续存储记录，后续插入记录的时候，我们需要维护一个顺序，即下一个数据页中记录的主键值必须大于上一个页中记录的主键值，确保页与页之间的先后顺序，假设一个页中我们可以存放3条记录（容许我偷个懒哈），我们现在有一个数据页，它长成这个样子，

图中省略了记录的箭头指向、其它信息等，图中没有，但是我们心里要有哈！这里特意的保留了record_type属性是干嘛的呢？一会就知道了哈！可以看到页0当中主键值最大的是6，然后我们开始插入第四条记录，这时候需要有另一个页来存放记录了，因为我们假设一个页最多只能存3条记录，假设我们插入一条id为5，name为赵六，age为13的记录，插入之后，就长成这个样子，

页0、页5表示的是页号，新分配的页号可能不是连续的，因为链表的空间可以不是连续的，它们只是通过指针即页中记录的上一个页和下一个页的指向来维护链表关系。

但是插入之后发现，新插入记录的主键值小于前一页的最大主键值，即5<6，因为我们现在需要维护页与页之间存储记录主键值的大小，所以呢这个记录需要移动一下，怎么移动呢？就是去它该去的位置，也就是把赵六放到页0中，然后把王五放到页5中，经过交换之后，就变成了这个样子，

所以后续所有的增删改操作，都要维护这个顺序，即下一个页中的用户记录的主键值必须大于前一页记录的最大主键值，那么维护这个顺序的过程我们称之为 页分裂。

• 给所有的页建立一个目录

  为了故事的继续，我们再多搞几个页，如图所示

这里页号有可能是连续的有可能不是连续的，这个不重要，为了方便我们快速的定位到要查找记录所在的页，所以需要给它建立一个目录，由于现在我们已经维护了页与页之间主键值大小的顺序，所以这个目录我们可以这样做，分别记录每个页对应的页号（page_no）以及该页中用户记录的最小主键值（key），因为数据页现在是有顺序的，所以相应建立的目录也是有顺序的，如图所示

有了目录之后我们根据主键查找记录时就可以这样做，比如找id=8的记录，首先查找目录，确定主键值为8的记录应该页5当中，然后再用我们前面唠叨的在一页中根据主键查找记录的办法，就可以快速的找到我们要找的id=8的记录。

这个目录好归好，但是这样摆着终究不是个事，为了灵活的管理这些目录，所以我们也专门用一种数据页来存放这些目录。但是数据页是用来存放记录的，所以我们也把这些目录设计成一条条记录，这些由目录设计成的记录我们简称为目录项记录，它只负责存储页号+该页当中最小的主键值。

那我们如何区分一个记录是目录项记录还是我们存储的真正的用户记录呢？还记得记录头信息的record_type属性嘛！没错就是这样！我们约定record_type=1时表示该记录为目录项记录，0的时候表示用户记录！ 这就是为什么上面的图省略了很多东西，但唯独这个属性一直带着的原因，相信此时已经有小伙伴回头翻记录头信息那部分了把，哈哈！所以把目录放到数据页后，它是这个样子的，

这里需要注意的是，存放目录项记录的数据页和存放用户记录的数据页都是同一种类型的页（为了方便区分，下面称为目录页），也就是说它们的组成结构都是一样的，它也会根据主键值生成页目录，所以跟我们前面唠叨的在单页中根据主键值查找的过程是一样的，假设现在我们查找id=10的记录就可以这样做：

​ 1、首先到页10也就是存储目录的页中，通过二分法快速定位到6<id=10的记录<11，所以在页5当中

​ 2、定位到具体的页之后，剩下的过程，你懂的（如果没有印象，那就回头看看在单页中查找记录的过程把）！

这就是索引嘛？当然不是，我们继续往下推，由于目录项记录只存储页号+主键值，所以一个数据页中可以存储的目录项记录的条数要比存储用户记录的条数多一些，但一个数据页的大小毕竟有限，随着数据增加，一个数据页来存储所有的目录显然是不够的，所以我们需要更多的页来存储其它的目录项记录，如图所示

那这个时候，我们应该如何快速定位到具体的目录页呢？彷佛又回到了原来的问题，即在多页中查找记录的问题，又要遍历嘛？当然不！

我们这个时候只需要针对目录页的上层，再做一层目录即可，最终就变成了这个样子，

终于画完了。。。。顺便说一下，这玩意就是我们心心念念的索引！

我们需要知道的是，MySQL的索引是用B+Tree实现的，那么不论是用于存放我们真正用户记录的数据页，还是用来存放目录项记录的目录页，在B+Tree当中，都是一个一个的节点，其中我们真正的用户记录放在最下面一层的节点，也就是叶子节点当中；其它用于表示目录的记录都存放在树干上，也就是非叶子节点；而最顶层单独的那个节点，我们称之为根节点，所以上面的图再抽象一点，实际就是这个样子的，

那么这个树当中，可以存储多少条记录呢？理论上它是可以无限向下延申的，但实际上呢，我们一般使用到的B+Tree都不会超过4层，为社么这么说呢？我们不妨来算一下 。

前面我们为了方便演示，提到一个页中只负责存储几条记录，但实际上一个数据页它是可以存很多条记录的，通过前面的分析，可知叶子节点存储的内容是真实记录（所有的列值及隐藏列）， 而非叶子节点中存的是索引记录(只包含页号+主键值，实际上存的是指针)，所以一个数据页中可以存放的真实记录和索引记录的条数是不一样的。
我们的数据页大小是16k，假设一个数据页当中可以存放100条真实的用户记录；而非叶子节点存储的是索引记录，也就是指针，这里假设一个指针大小是14字节，那么一页16k可以存储的索引记录为16kb/14b=1170条，也就是一个数据页可以存1170条索引记录（代表着指向1170个数据页的地址）；那么：

• 如果B+树只有1层，也就是只有1个数据页存储记录时，那么它可以存放100条真正的用户记录；
• 如果B+树有2层，也就是一个索引页+一层数据页时，那么它可以存放1170×100=11 7000条记录≈10万条用户记录。
• 如果B+树有3层，也就是一个索引页+一层索引页+一层数据页时，那么它可以存放1170×1170×100=13689 0000≈1亿条用户记录
• 如果B+树有4层，也就是一个索引页+一层索引页+一层索引页+一层数据页时，那么它可以存放1170×1170×1170×100=16116130 0000≈1万亿条用户记录

天哪。。我这辈子都不会在一张表里存储这么多记录的，所以我们用到的B+Tree一般都不会超过4层，也就意味着我们通过索引进行查找数据时，最多只需要在4个页当中查找，也就是4次磁盘IO；而每个页当中，通过主键查找的速度又快的飞起，好吧，索引牛逼！

主键生成策略

InnoDB表对主键的生成策略：

• 优先使用用户定义的主键作为主键
• 如果没有定义主键，则在表中选取一个唯一键作为主键
• 如果没有定义主键，同时表中也没有唯一键，这个时候存储引擎会为表默认添加一个隐藏列row_id作为主键

2、非聚集索引

聚集索引好归好，但它也有盲点！也就是我们只能通过主键值匹配时，它才有用；当使用其它列进行查找数据时，主键索引就用不上了，但这种情况在实际开发中又很常见，所以我们也可以针对非主键的列创建索引（索引列可以是单独的某个列，也可以是多个列，即组合索引），也就是再创建一颗索引树，这种由非主键创建的索引我们统称为非聚集索引！

它和主键索引的功能类似，都是为了帮助我们快速定位到想要查找的数据，只是实现上略有差异，具体区别如下：

• 主键索引，数据页中的记录是根据主键进行排序形成的单向列表，各个数据页之间也是根据主键值的大小进行排序形成的双向链表；而非主键索引，数据页中的记录是根据索引列进行排序形成的单向列表，各个数据页之间是根据索引列的大小进行排序形成的双向链表；

• 主键索引，存储引擎默认会为每个表自动生成主键索引；

  而非聚集索引，需要我们自己创建，不会自动生成；

• 主键索引，主键值必须保证唯一性，不能重复；

  而非聚集索引，索引列可以不具备唯一性，即非聚集索引列的值可以重复；

• 主键索引，非叶子节点也就是索引记录存储的是主键+页号，而叶子节点存储的是完整的用户记录；

  而非主键索引，非叶子节点存储的是索引列+页号+主键（这里存储主键值是为了确保目录项记录也就是索引记录的唯一性，因为非聚集索引的列值是可重复的，在插入的时候，如果仅靠索引列不能确定新插入数据的范围，所以需要存储主键值来确定具体存放的位置），而叶子节点存储的是索引列+对应的主键值；

以上非聚集索引最大的特点就是，叶子节点存储的不再是完整的用户记录，而是索引列的值+主键值，这意味着，通过非聚集索引查找数据时，如果想要获取完整的记录信息，需要通过记录的主键值，再查一遍主键索引，从而获得完整的记录信息，这个过程我们称之为”回表“。

正是因为这个特性，也就是需要一次回表操作才可以获取到完整的用户记录信息，所以非聚集索引我们也称之为二级索引或者辅助索引。

那么我们根据非聚集索引查找数据的过程最终就是这样的：

• 从非主键索引树的根节点开始，通过层层的比较，最终定位到要查找记录对应的主键值
• 拿到主键值后，回到主键索引中，根据上面主键索引的查找过程，最终获得完整的用户记录

思考：

为什么非聚集索引的叶子节点不直接存储完整的用户记录，而是需要一次回表去查主键索引获取呢？这样设计有什么好处嘛？

这样设计当然也可以，只是有点浪费存储空间，因为要把所有的用户记录再拷贝一份出来。

七、索引的代价

需要注意的是，索引虽然可以提升我们查询的效率，但是它也需要付出一定代价，不能滥用，不能随意创建！

• 空间成本

  每创建一个索引都会建立一棵B+树，而树是由大量的数据页构成的，所以这玩意可是真正的会占用一片空间的。

• 时间成本

  前面提到过，索引的前提就是需要在存储数据的时候维护一个顺序，而我们每次对数据进行增、删、改操作的时候，会涉及到记录的移动、页分裂等操作来维护这个顺序。如果我们创建了很多索引，那意味着记录每变动一次，都需要维护所有的索引树来保证记录在各自树上的存储顺序，真正的牵一发而动全身！

所以说一个表上的索引建的越多，就会占用越多的存储空间，在增删改记录的时候性能也会降低。所以我们需要合理的创建使用索引。

八、索引的使用原则

案例环境:

CREATE TABLE test_query(
    id INT NOT NULL auto_increment,
    name VARCHAR(100) NOT NULL,
	age int(5) NOT NULL,
    country varchar(100) NOT NULL,
    birthday DATE NOT NULL,
    phone_number CHAR(20) NOT NULL,
    PRIMARY KEY (id),
    KEY idx_name_age (name, age,country)
);

上述脚本创建了一张表名为test_query，这个表中创建了两个索引，一个是主键索引，一个是组合索引（name,age,country）；

• 主键索引，以id值进行排序，叶子节点存储完整的用户记录
• 组合索引（name,age,country），以这三个字段进行排序，排序的规则是
  • 优先以name的值排序
  • name值相同的以age进行排序
  • name和age相同的，以country进行排序
  • name、age、country都相同的以主键值进行排序

这些事交代清楚后，我们来进行具体的案例分析；关于索引的使用原则，在网上也是流传着这样的口诀，

• 全值匹配我最爱，最左前缀要遵守;
• 带头大哥不能死，中间兄弟不能断;
• 索引列上少计算，范围之后全失效;
• LIKE百分写最右，覆盖索引不写星;
• 不等空值还有or，索引失效要少用;

下面我们就分别来看下，口诀中具体的含义

1、全值匹配我最爱

我们在查询数据时，如果使用到了索引中的所有列进行等值查询时，我们就称之为全值匹配，比如

SELECT * FROM test_query WHERE name = '张三' AND age = 27 AND country = 'china';

2、带头大哥不能死，中间兄弟不能断

在使用组合索引的索引列作为条件时，需要满足最左前缀原则，也就是要按照索引的顺序依次向下匹配，不能乱了顺序，比如可以这样

SELECT * FROM test_query WHERE name = '张三' ;
SELECT * FROM test_query WHERE name = '张三' AND age = 27;
SELECT * FROM test_query WHERE name = '张三' AND age = 27 AND country = 'china';

但是不能这样

#带头大哥死
SELECT * FROM test_query WHERE age = 27;
SELECT * FROM test_query WHERE country = 'china';
SELECT * FROM test_query WHERE age = 27 AND country = 'china';


#中间兄弟断，只有name列索引生效，其它两列失效
SELECT * FROM test_query WHERE name = '张三' AND country = 'china';

3、索引列上少计算

如果想要使用索引进行排序，必须保证索引列是以单独列的形式出现，而不是修饰过的形式，也就是不要在索引列进行计算、函数、自动/手动类型转换等操作，否则会导致索引失效而转向全表扫描，比方说这样：

#索引失效
SELECT * FROM test_query ORDER BY UPPER(name) ;

SELECT * FROM test_query where left(name,5)='Marry';

4、范围之后全失效

在满足最左前缀匹配原则的前提下，如果某个索引列使用了范围查询（bettween、<、>、in等），那么该索引列之后的索引列失效，比如

# name列可以生效，name后面的age、country失效
SELECT * FROM test_query WHERE name >'张三'  and age=71 and country="china";

#精确匹配某一列同时范围匹配其它列,name和age生效，country失效
SELECT * FROM test_query WHERE name ='张三'  and age>71 and country="china";

#name、age、country都可以生效
SELECT * FROM test_query WHERE name ='张三'  and age=71 and country>"china";

5、LIKE百分写最右

索引字段使用like以通配符开头时（‘%字符串’），会导致索引失效而转向全表扫描

#索引失效
SELECT * FROM test_query WHERE name like'%张'  ;
SELECT * FROM test_query WHERE name like'%张%'  ;

#索引生效
SELECT * FROM test_query WHERE name like'张%'  ;

6、覆盖索引不写星

查询时，尽量使用索引覆盖即只查询索引的列，也就是索引列和查询列一致，避免使用select *，也就是减少回表操作，比如

#需要回表操作
SELECT * FROM test_query WHERE name ='Marry' AND age < 71;

#不需要回表
SELECT name FROM test_query WHERE name ='Marry' AND age < 71;
SELECT name, age FROM test_query WHERE name ='Marry' AND age < 71;
SELECT name, age, country FROM test_query WHERE name ='Marry' AND age < 71 AND country='china';

7、不等空值还有or

• 索引字段上使用（！= 或者 < >）判断时，会导致索引失效而转向全表扫描
• 索引字段上使用 is null / is not null 判断时，会导致索引失效而转向全表扫描
• 索引字段使用 or 时，会导致索引失效而转向全表扫描

例子：

#失效
SELECT * FROM test_query WHERE name !='Marry';

#失效
SELECT * FROM test_query WHERE name is null;

#失效
SELECT * FROM test_query WHERE name ='Marry' or name='Mali';

8、索引字段字符串要加单引号

索引字段是字符串，但查询时不加单引号，会导致索引失效而转向全表扫描，比如

#生效
SELECT * FROM test_query WHERE name ='Marry';

#失效
SELECT * FROM test_query WHERE name =Marry;

九、索引创建原则

1、哪些情况需要创建索引

1. 频繁出现在where 条件判断，order排序，group by分组字段，可以考虑创建索引
2. select 频繁查询的列，考虑是否需要创建组合索引（覆盖索引，不回表）
3. 频繁使用多表join关联查询，on字段两边的字段都要创建索引

2、索引列的挑选原则

• 频繁更新的字段不建议作为索引
• 区分度低的字段，不建议作为索引
• 索引列的类型尽量小
• 无序的值不建议作为索引，例如身份证、UUID
• 可以只对字符串值的前缀建立索引
• 尽可能的让主键具有自增属性，避免移动记录和页分裂带来的额外开销

学习过程中难免会有理解偏差的地方，如果各位发现哪里有问题，及时评论，感激不尽！

每日一皮

当初我们学校有一个保送北大的名额，我没去。有两个原因，一是北京天气不适合我，二是被保送的那个人不是我。。。