目录
一、 primary key对索引的影响
1. 主键数据有序问题
我们先创建如下一个user表：
注意，在这个表中的id是带有primary key属性的。
表创建好后，插入如下数据：
注意，在插入数据的时候，我们特地将id的序号按乱序插入。
插入完成后，查看数据表中的数据：
此时可以发现，虽然插入数据时是乱序插入的，但是显示数据时，数据却是按照主键有序的状态显示的。那这里就有一个问题了。要知道，乱序插入的数据在显示时要有序，就势必是经过排序了的。那数据排序是谁做的呢？为什么这个带有主键的表会以主键有序的方式显示数据呢？
要理解这个问题，就需要理解mysql中的page。
2. mysql中的page
在上一篇文章“索引概念”中讲了，mysql与磁盘进行IO交互时，是以page为单位的，一个page的大小为16KB。很明显，16KB的大小是不可能保存完数据库的所有数据的，这就意味着当存在大量数据时，mysql中一定存在大量的page。同时要知道，这些page中的数据可能需要修改、删除或保持不变等等操作，这也就意味着，mysql想要方便的使用这些page，就必须要将这些page保存起来。
要保存mysql中的所有page，就一定需要“先描述，再组织”。这就意味着page其实并不只是一个内存块，page内部除了对应的数据外，还要管理page的信息。
因此，按照我们以前对进程pcb的理解，page其实也就是一个结构化数据。这些page可以将其看为在内存中是按照双链表的方式管理起来的，这就意味着page中应该还存有两个指针，分别指向前后page，然后还要一个内存区，用于保存数据。这些数据加起来的可容纳总大小就为16KB。
这些page就被统一存放在mysql的buffer pool中等待使用。通过如上将page用链表管理起来，就可以将对page的增删查改变为对链表的增删查改。
那mysql和磁盘进行IO交互时，为什么要以page为单位交互，而不是mysql用多少，磁盘加载多少呢？原因很简单，就是因为page中的数据其实都是连续的，用page交互的本质其实就是预加载，提前将mysql可能用到的数据加载到内存中，减少mysql与磁盘IO的次数，提高效率。当然，我们无法完全保证mysql需要的数据就在这个page中，但根据局部性原理，可以肯定需要的数据在page中的概率是很大的。
不要误以为影响IO效率的主要原因是加载数据量的大小，而应该是IO的次数。
3. 主键排序问题
通过上面的测试，其实可以知道当表中存在主键时，mysql会默认按照主键中的数据进行排序。那么为什么要进行排序呢？简单来讲，其实就是为了便于算法优化。通过排序，我们在线性查找的时候就是从小到大查找，因此，当我们在查找中遍历线性数据时，每次查找都是有效查找。并且，我们也可以设计出基于有序结构的查找方法，提高查找效率。
二、理解多个page
1. 数据在page中的保存
通过上面的分析就可以知道，在数据库中查询某条数据的时候，是直接将一整页的数据加载到内存中，以减少硬盘IO次数，从而提高效率。同时也可以知道，page的内部是采用了链表的结构，要比对数据，本质上还是通过数据的逐条比较来找到特定数据的。
如果有1000万条数据，就势必需要多个page来保存这些数据。这些page彼此用双链表连接起来，page内的数据也是用链表连接起来的。这就意味着，如果要查找一条特定数据，就势必需要用线性查找，很明显，效率是很低的。
2. 页目录
既然以线性方式遍历链表的效率很低，那能不能想办法优化一下呢？由于在这里面的page和page中的数据都是以链表方式存储的，那优化方向就有两个。一个是优化在page表内的数据的查找，另一个就是优化找到对应数据在哪个page中的效率。
先来看如何优化page表内的数据。在大家的书中，为了方便找到特定的内容，都有目录。这个目录中就记录了书中的特定内容所在的位置。既然在书中都可以用目录来让读者快速定位特定内容的位置，那我们能不能把目录引入到page中呢？很明显，是可以的。
3. 单页情况
在page中，除了指向前后page的指针和有效数据外，还可以再预留一块空间，这块空间就用于充当目录，如下图所示：
在page中，这片保存目录的空间中就会存在许多指针，这些指针指向的内容就是有效数据的键值。例如目录1指向id=1，目录2指向id=3。假设我们要找id为2的数据，就可以先去遍历这些目录，遍历目录1的时候id为1，不存在；遍历目录2时id为3，而2的值小于3，因此可以断定数据就在目录1和目录2之间的范围内，再到这个范围去遍历数据，就可以缩减遍历次数，提高效率。
由此，我们就可以知道为什么mysql要按照主键进行排序了。其实就是因为要引入目录，就必须保证数据是有序的。这样目录才能正常生效。
4. 多页情况
要知道，mysql中每一页的大小只有16KB，随着数据量的增加，16KB不可能存下所有的数据，这就要求要有其他page来存储数据。
在单表数据不断被插入的情况下，mysql会在容量不足时自动开辟新的page来保存新的数据，然后再通过指针的方式，将所有的page组织起来。这一点在上面已经说过，其实就是用双链表连接起来，如上所示。
这就意味着，在数据量足够大的情况下，会存在大量的page，要在这些page之间跳转，就依然需要线性遍历，效率低下。
因此，我们可以继续在page上引入目录，通过目录来进行管理。如下图：
在page以上的目录中，就不再有有效数据。在这里面的每个目录由两部分组成，一个是它所管理的下层page中的最小键值，另一个就是指向这个page的指针。例如在上图中，page1的键值是1，所以上层目录中保存的就是1；下一个目录中保存的就是page2的最小键值6，依次往下。
通过这种方式，下层page就被上层目录保存起来，要查找数据时，就是直接到对应目录下找。例如要找数据5，先遍历目录，第一个目录是1，第二个目录是6，这就说明数据5必然在第一个目录指向的page中。由此，便能够在很大程度上提高效率。
同时要知道，一个page是16KB，这就意味着一个只保存目录的page能够保存的目录是很多的。假设键值的属性是4字节的int，指针是64位系统下的8字节，加起来一个目录也就12字节。而一个page是16KB，即16384字节，可以保存1365个目录。而1356个目录，每个目录指向一个16KB的page， 即可以管理21840KB，即21MB左右的数据。
那假设这第二层page的数量也太多了呢？很简单，再加一层即可：
通过这种方式，就可以进一步提高效率。如果三层依然会导致最上层page过多，那就继续想上加。当然，这种情况是很少见的，一般来讲就是2 ~ 3层就可以完全满足查找需求了。我们可以来算一下，当加了第三层后，最上层的一个page中可以保存上千个目录，假设就是上面算的1356个目录， 即21MB数据。那要将最上层的page填满，就需要有21 * 1356 = 28476MB，即27.8GB左右的数据。这还仅仅是三层目录中最上层中一个page的可保存数据量，再多加几个甚至保存数据量可以到T。如果再加一层，大家可以想象到底有多少数据。所以在一般情况2 ~ 3层就足够使用了。
通过这种方法，当需要查找数据时，就不再是遍历叶子节点中的数据，而是从上至下根据page中保存的目录找到对应数据了。效率大大提升。
这个数据结构，其实就叫做“B+树”。是一种高阶数据结构。当然，这并不是说mysql中的数据存储方式就一定是B+树，这可能会因为搜索引擎的改变而改变。但InnoDB中的存储结构一般就是B+树。
5. 为什么除了叶子节点外的其他节点不保存数据，只保存目录
看到这里，大家可能就会有一个疑惑，那就是为什么在这个B+树中，除了叶子节点外的其他节点，都不保存数据，而只保存目录项。
这其实就是为了能够存储更多的目录项，让一个page可以管理更多的page。通过这种方式形成的树，必然是一个又矮又胖，即“矮胖型”的树。这也就意味着它途径的路上节点会很少，即找到目标所需要的page也就更少。同时，mysql要拿到的page中的数据都是保存在磁盘上的，需要的page减少，也就意味着mysql和磁盘需要的IO交互次数更少，提高了效率。
在未来查询数据时，并不需要将整棵树加载进来，而是将里面的部分page加载进来，通过这些page找到需要的page，然后再加载。这也就可以让我们在查找数据时，以page为单位按需获取。大大提高了整体搜索效率。
在上面的整棵B+树，就被称为“mysql innodb下的索引结构”。当我们使用的是innodb引擎时，一般我们在建表和插入数据时，就是在这个B+树的结构下执行CURD操作。
此时有人可能就有疑问了，这个B+树的构建是依赖于主键的，那要是我们的表里面没有主键怎么办。其实就算表中没有主键，mysql在插入数据时也会默认插入一列隐藏数据，这列数据就是主键。在插入数据时，就是按照这个隐藏主键构建B+树的。
当然，由于我们在查数据时无法用这个隐藏主键查找数据，所以查找效率就很低。
6. 为什么叶子节点全部用链表连接起来
通过上面的B+树的图大家应该会发现，在图中除了叶子节点用链表连接起来外，其他节点直接都是相互独立的。既然如此，为什么叶子节点要全部用链表连接起来呢？
有两方面原因。
一个原因是叶子节点全部用链表连接起来，其实是B+树这个数据结构的特性。
第二个原因就是在查找数据时，一般都是希望进行范围查找。如果叶子节点不用链表连接起来，假设我们查的范围数据被分别保存在了2个甚至多个page中，这就意味着我们需要重新从头开始再查询一遍，找到下一个page。但如果用叶子节点连接起来，要找下一个page中的数据时，就直接用page中保存的地址找到下一个page即可，无需重新查找。
7. 为什么选择B+树而不选择其他数据结构
大家看到这里可能会疑惑，有这么多的数据结构，既然B+树可以胜任承担索引结构的工作，那其他数据结构呢？
我们一个个来看。
（1）链表：链表是线性遍历，在上文中就说过是不行的，效率太低。
（2）二叉搜索树：我们插入数据时的插入顺序是不可预测的，这就导致二叉搜索树可能在插入的过程中退化为线性结构，效率低。
（3）AVL或红黑树：这两个数虽然是平衡或者说是近似平衡的，但是它毕竟是二叉结构，相比较多阶B+，它的树整体过高。这两种树都是自顶向下找，层高越低，意味着系统与硬盘更少的IO交互。因此，虽然AVL和红黑树可行，但在这种场景下不如B+树优秀。
（4）hash：在官方的索引中，mysql是支持hash的，但是在innodb和myisam中并不支持hash。虽然它的算法特征决定了它的搜索效率很高，但在面对范围查找时就不如B+树优秀。当然还有一些其他差别，这里就不再多说。
（5）B树：B树大家可能不太理解，它也是一种高阶数据结构。它和B+树在结构上是很相似的，但是与B+树不同的是，B树的节点中除了目录，还会保存有效数据。这就导致B树中的page中保存的目录数量势必会少于B+树，查找数据时需要经过更多的路上节点。另一个问题就是B树的叶节点不会用链表连接起来，这就会出现当需要查找范围数据时，如果某些数据在其他page上，就需要重新找一遍，花费更多的时间。因此，在这个场景下，B树的整体效率就比不上B+树。
三、聚簇索引与非聚簇索引
1. 聚簇索引与非聚簇索引的概念
在上面所讲的索引结构中，其实都是innodb的索引结构。这类将数据直接存放在叶子节点中的索引方式，叫做“聚簇索引”。
还有一种索引方式，叫做“非聚簇索引”。MyISAM就是采用的这种方式。在MyISAM引擎中同样使用B+树作为索引结果，但不同的是，它的节点中存放的是数据的位置，而非数据本身，如下图所示：
其中Col就是主键。
2. 实际看看聚簇索引与非聚簇索引的区别
要实际看到这两种索引方式的区别，我们可以分别创建一个用InnoDB和MyISAM引擎的数据表。
先创建一个InnoDB引擎的表：
查看它的创建信息：
因为我们在配置文件中设置了默认采用InnoDB引擎，所以创建表时不指定搜索引擎就会默认采用InnoDB。
表创建好后，我们进入mysql数据库存放数据的目录：
然后进入上面的表所在的数据库，即目录。然后查看它里面的文件：
可以看到，在这个数据库中除了数据库配置文件db.opt外，还有两个文件，它的名字就是我们刚刚创建的表的名字。在这两个文件中，后缀为frm的文件，就是这个表的表结构；后缀为ibd的文件，其中i大家可以理解为innodb，b理解为block，d理解为data。即这个表的索引和数据就放在这个文件中。由此就可以知道，在InnoDB中，是将索引和数据放在一起的。
我们再创建一个test2表，用MyISAM引擎：
表创建好后，我们再来目录下的文件：
可以看到，此时就多了三个文件，这三个文件就是test2表的文件。其中frm后缀的是表结构；MYI后缀的文件，就是索引结构；而MYD后缀的文件，就是数据。可以看到，它的大小是0，因为当前这个表内还没有插入数据。
3. 主键索引与辅助（普通）索引
3.1 主键索引的概念
在上面的两个表中，以主键来充当索引的方案，叫做“主键索引”。
3.2 辅助（普通）索引的概念
在一张表中，除了可以让主键充当索引，还可以让其他列充当索引，这种用没有主键属性的列充当索引的方案，就叫做“辅助（普通）索引”。
3.3 主键索引与辅助（普通）索引的区别
对于MyISAM而言，建立主键索引和普通索引是没有区别的，无非就是主键不能重复，而非主键可以重复。
既然可以为一个表创建多个索引，这就意味着一张表可能存在多个B+树，在MyISAM中，主键索引建立的B+树和普通索引建立的B+树都是一样的。唯一的不同就是page中保存的用于比对的值不再是主键值，而是其他列数据中的值。
因此，索引的本质，其实就是在特定的数据结构中搜索数据。
3.4 回调查询
那么对于InnoDB呢？在InnoDB中，同样是可以建立普通索引的。但是我们知道，InnoDB的B+树的叶子节点保存的是数据本身，如果再建一张拥有同样数据的B+树，很明显就会出现两份相同数据，浪费空间。
因此在InnoDB中，如果用没有primary key属性的列建立普通索引，此时mysql中就会按照这一列中的数据构建一棵B+树，但是在这棵B+树的叶子节点中，就不再保存除了主键以外的其他数据。例如现在有如下一张表：
假设我们用第三列数据建立普通索引，那么它的B+树结构如下：
在这棵B+树中，除了用于作为索引的列数据外，叶子节点中只保存主键值。
同时，当有了这棵B+树后，主键索引的B+树中的对应列数据就会全部删除。以避免出现重复数据。当用这棵普通索引的树查找数据，并且还想要获得对应行数据中的其他数据时，mysql就会拿着叶子节点中的主键，回到主键索引的B+树中去检索获得其他数据。
这种通过普通索引，找到目标数据的主键值，然后拿着主键值回到主键中查找其他数据的查询，就叫做“回表查询”。
因此，虽然常见的索引可以分为主键索引、唯一键索引和普通索引，但总的来说就两种索引，分别是主键索引和普通索引。普通索引中就包含了唯一键索引。
扫一扫
2334
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
