34.主键索引和聚簇索引

主键索引

数据页分裂的过程，在你不停的往表里插入数据的时候，会搞出来一个一个的数据页。

如果你的主键不是自增的，他可能会有一个数据行的挪动过程来保证你下一个数据页的主键值都大于上一个数据页的主键值。

假设我们有多个数据页，然后我们想要根据主键来查询数据，那么直接查询的话也是不行的，因为我们也不知道主键到底是在哪里。

现在假设你要搜id=4的数据，你怎么知道在哪个数据页里？没有任何证据可以告诉你他到底是在哪个数据页里啊！

所以假设还是这个样子的话，你也就只能全表扫描了，从第一个数据页开始，每个数据页都进入到页目录里查找主键，最坏情况下，所有数据页你都得扫描一遍，还是很坑的。

所以其实此时就需要针对主键设计一个索引了，针对主键的索引实际上就是主键目录，这个主键目录呢，就是把每个数据页的页号，还有数据页里最小的主键值放在一起，组成一个索引的目录，如下图所示。

现在我们有了上图的主键目录就方便了，直接就可以到主键目录里去搜索，比如你要找id=3的数据，此时就会跟每个数据页的最小主键来比，首先id=3大于了数据页2里的最小主键值1，接着小于了数据页8里的最小主键值4。

所以既然如此，你直接就可以定位到id=3的数据一定是在数据页2里的！

假设你有很多的数据页，在主键目录里就会有很多的数据页和最小主键值，此时你完全可以根据二分查找的方式来找你要找的id到底在哪个数据页里！

所以这个效率是非常之高的，而类似上图的主键目录，就可以认为是主键索引。

而大家都知道我们的数据页都是一坨一坨的连续数据放在很多磁盘文件里的，所以只要你能够根据主键索引定位到数据所在的数据页，此时假设我们有别的方式存储了数据页跟磁盘文件的对应关系，此时你就可以找到一个磁盘文件。

而且我们假设数据页在磁盘文件里的位置也就是offset偏移量，你也是可以知道的，此时就可以直接通过随机读的方式定位到磁盘文件的某个offset偏移量的位置，然后就可以读取连续的一大坨数据页了！

非聚簇索引

聚簇索引和非聚簇索引都是数据的存储和组织的方式。

非聚簇索引就是指B+Tree的叶子节点上的data，并不是数据本身，而是数据存放的地址。

主索引和辅助索引没啥区别:叶子节点上的data，存储的都不是数据本身，而是数据存放的地址。

聚簇索引

聚簇索引和非聚簇索引都是数据的存储和组织的方式。

聚簇索引的数据行只存放在索引(B+树)的叶子中，内部节点不存放数据。

聚簇索引默认是主键，如果表中没有定义主键，InnoDB 会选择一个唯一的非空索引代替。如果没有这样的索引，InnoDB 会隐式定义一个主键来作为聚簇索引。

主索引和辅助索引有区别:辅助索引的叶子存储的是主键值。

非聚簇索引比聚簇索引多了一次读取数据的IO操作，所以查找性能上会差。

现在假设我们要搜索一个主键id对应的行，此时你就应该先去顶层的索引页88里去找，通过二分查找的方式，很容易就定位到你应该去下层哪个索引页里继续找，如下图所示，我们给一个图示出来。

比如现在定位到了下层的索引页35里去继续找，此时在索引页35里也有一些索引条目的，分别都是下层各个索引页(20，28，59)和他们里面最小的主键值，此时在索引页35的索引条目里继续二分查找，很容易就定位到，应该再到下层的哪个索引页里去继续找，如下图所示。

我们这里看到，可能从索引页35接着就找到下层的索引页59里去了，此时索引页59里肯定也是有索引条目的，这里就存放了部分数据页页号(比如数据页2和数据页8)和每个数据页里最小的主键值

此时就在这里继续二分查找，就可以定位到应该到哪个数据页里去找，如下图所示。

接着比如进入了数据页2，里面就有一个页目录，都存放了各行数据的主键值和行的实际物理位置

此时在这里直接二分查找，就可以快速定位到你要搜索的主键值对应行的物理位置，然后直接在数据页2里找到那条数据即可了。

这就是基于索引数据结构去查找主键的一个过程，那么大家有没有发现一件事情，其实最下层的索引页，都是会有指针引用数据页的，所以实际上索引页之间跟数据页之间是有指针连接起来的，如下图。

另外呢，其实索引页自己内部，对于一个层级内的索引页，互相之间都是基于指针组成双向链表的，如下面图示

大家可以看看，这个同一层级内的索引页组成双向链表，就跟数据页自己组成双向链表是一样的。

比如现在定位到了下层的索引页35里去继续找，此时在索引页35里也有一些索引条目的，分别都是下层各个索引页(20，28，59)和他们里面最小的主键值，此时在索引页35的索引条目里继续二分查找，很容易就定位到，应该再到下层的哪个索引页里去继续找，如下图所示。

我们这里看到，可能从索引页35接着就找到下层的索引页59里去了，此时索引页59里肯定也是有索引条目的，这里就存放了部分数据页页号(比如数据页2和数据页8)和每个数据页里最小的主键值

此时就在这里继续二分查找，就可以定位到应该到哪个数据页里去找，如下图所示。

接着比如进入了数据页2，里面就有一个页目录，都存放了各行数据的主键值和行的实际物理位置

此时在这里直接二分查找，就可以快速定位到你要搜索的主键值对应行的物理位置，然后直接在数据页2里找到那条数据即可了。

这就是基于索引数据结构去查找主键的一个过程，那么大家有没有发现一件事情，其实最下层的索引页，都是会有指针引用数据页的，所以实际上索引页之间跟数据页之间是有指针连接起来的，如下图。

索引页自己内部，对于一个层级内的索引页，互相之间都是基于指针组成双向链表的，如下面图示

大家可以看看，这个同一层级内的索引页组成双向链表，就跟数据页自己组成双向链表是一样的。

把索引页和数据页综合起来看，他们都是连接在一起的，看起来就如同一颗完整的大的B+树一样，从根索引页88开始，一直到所有的数据页，其实组成了一颗巨大的B+树。

在这颗B+树里，最底层的一层就是数据页，数据页也就是B+树里的叶子节点了！

所以，如果一颗大的B+树索引数据结构里，叶子节点就是数据页自己本身，那么此时我们就可以称这颗B+树索引为聚簇索引！

也就是说，上图中所有的索引页+数据页组成的B+树就是聚簇索引！

其实在InnoDB存储引擎里，你在对数据增删改的时候，就是直接把你的数据页放在聚簇索引里的，数据就在聚簇索引里，聚簇索引就包含了数据！比如你插入数据，那么就是在数据页里插入数据。

如果你的数据页开始进行页分裂了，他此时会调整各个数据页内部的行数据，保证数据页内的主键值都是有顺序的，下一个数据页的所有主键值大于上一个数据页的所有主键值

同时在页分裂的时候，会维护你的上层索引数据结构，在上层索引页里维护你的索引条目，不同的数据页和最小主键值。

然后如果你的数据页越来越多，一个索引页放不下了，此时就会再拉出新的索引页，同时再搞一个上层的索引页，上层索引页里存放的索引条目就是下层索引页页号和最下主键值。

按照这个顺序，以此类推，如果你的数据量越大，此时可能就会多出更多的索引页层级来，不过说实话，一般索引页里可以放很多索引条目，所以通常而言，即使你是亿级的大表，基本上大表里建的索引的层级也就三四层而已。

聚簇索引默认是按照主键来组织的，所以你在增删改数据的时候，一方面会更新数据页，一方面其实会给你自动维护B+树结构的聚簇索引，给新增和更新索引页，这个聚簇索引是默认就会给你建立的。

感觉上面的很不直观,贴一张完整的B+树主键索引图。

其中:1-12-25 1-5-8 5-6-7 等分别都是在1个数据页上,只要把这一页读入内存这个页的所有的数据就被读出来了。

InnoDB 的索引模型

在InnoDB中，表都是根据主键顺序以索引的形式存放的，这种存储方式的表称为索引组织表。又因为前面我们提到的，InnoDB使用了B+树索引模型，所以数据都是存储在B+树中的。每一个索引在InnoDB里面对应一棵B+树。假设，我们有一个主键列为ID的表，表中有字段k，并且在k上有索引。这个表的建表语句是：

create table T( id int primary key, k int not null, name varchar(16), index (k))engine=InnoDB;

表中R1~R5的(ID,k)值分别为(100,1)、(200,2)、(300,3)、(500,5)和(600,6)，两棵树的示例示意图如下。

主键索引&普通索引&回表

从图中不难看出，根据叶子节点的内容，索引类型分为主键索引和非主键索引。

主键索引的叶子节点存的是整行数据,在InnoDB里，主键索引也被称为聚簇索引(clustered index)。

非主键索引的叶子节点内容是主键的值,在InnoDB里，非主键索引也被称为二级索引(secondary index)。

没有主键的表，innodb会给默认创建一个Rowid做主键。

根据上面的索引结构说明，我们来讨论一个问题：基于主键索引和普通索引的查询有什么区别？

· 如果语句是select * from T where ID=500，即主键查询方式，则只需要搜索ID这棵B+树；

· 如果语句是select * from T where k=5，即普通索引查询方式，则需要先搜索k索引树，得到ID的值为500，再到ID索引树搜索一次。这个过程称为回表。

也就是说，基于非主键索引的查询需要多扫描一棵索引树。因此，我们在应用中应该尽量使用主键查询。

自增主键&KV主键

基于上面的索引维护过程说明，我们来讨论一个案例：

你可能在一些建表规范里面见到过类似的描述，要求建表语句里一定要有自增主键。

当然事无绝对，我们来分析一下哪些场景下应该使用自增主键，而哪些场景下不应该。

自增主键是指自增列上定义的主键，在建表语句中一般是这么定义的： NOT NULL PRIMARY KEY AUTO_INCREMENT。

插入新记录的时候可以不指定ID的值，系统会获取当前ID最大值加1作为下一条记录的ID值。

也就是说，自增主键的插入数据模式，正符合了我们前面提到的递增插入的场景。

每次插入一条新记录，都是追加操作，都不涉及到挪动其他记录，也不会触发叶子节点的分裂。

而有业务逻辑的字段做主键，则往往不容易保证有序插入，这样写数据成本相对较高。

除了考虑性能外，我们还可以从存储空间的角度来看。

假设你的表中有一个唯一字段，比如字符串类型的身份证号，那是用身份证号做主键，还是用自增字段做主键呢？

由于每个非主键索引的叶子节点上都有主键的值。如果用身份证号做主键，那么每个二级索引的叶子节点占用约20个字节，而如果用整型做主键，则只要4个字节，如果是长整型(bigint)则是8个字节。

B+树的插入可能会引起数据页的分裂，删除可能会引起数据页的合并，二者都是比较重的IO消耗，所以比较好的方式是顺序插入数据，这也是我们一般使用自增主键的原因之一。

B+树的内节点是一个页,一个页大小默认16K，也就是说作为主键的字段越小,可以存储的主键的数量也就越多。

越能使用到时空局部性原理。

一旦一个指令被执行,这个指令在不久的将来也会被执行。

一旦访问一个存储单元,那它附近的单元也将很快被访问。

假如编号是4个字节 身份证是20个字节。

假如编号是主键,身份证做二级索引。

那么主键索引是4字节,二级索引是 20 + 4= 24 字节

假如身份证是主键,编号做二级索引。

那么主键索引是20字节,二级索引是 20 + 4= 24 字节

显然，主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小。

所以，从性能和存储空间方面考量，自增主键往往是更合理的选择。

有没有什么场景适合用业务字段直接做主键的呢？

比如，有些业务的场景需求是这样的:只有一个索引且该索引必须是唯一索引,这就是典型的KV场景。 由于没有其他索引，所以也就不用考虑其他索引的叶子节点大小的问题。这时候我们就要优先考虑上一段提到的“尽量使用主键查询”原则，直接将这个索引设置为主键，可以避免每次查询需要搜索两棵树。

参考:https://time.geekbang.org/column/intro/139

参考:https://blog.csdn.net/zht245648124/article/details/129271361

参考:https://blog.csdn.net/zht245648124/article/details/129290832