灵魂拷问，MySQL索引知多少?

爱面试的小豪上次跟宇哥聊过数据库索引的常见数据结构后，对索引产生了浓厚的学习兴趣，经过系列的学习，感觉自己对索引好像有了新的认识和理解，于是跟宇哥得瑟了起来。

故事人物背景介绍
小豪： 23岁，武汉某双非本科不知名专业大学四年级学生，成绩一般，面临毕业，对后端开发、Java很感兴趣，正求职找工作。
宇哥: 跟小豪通过租房认识，两人是室友，26岁，毕业后长期从事软件开发工作，是一个半吊子工程师，兴趣爱好是吹牛，不打草稿那种。

炫耀知识的小豪

宇哥躺在沙发上看着电视，喝着可乐，刷着手机，好不快活，小豪兴冲冲得凑上去扒拉了一下宇哥。

小豪：宇哥啊，我跟你说，经过我刻苦专研，我对索引有了新的认识哦。
宇哥：嗯，我给你几分薄面，你说说看。

小豪：其实啊，索引的出现以及优化的思路在我看来就是一句话，减少访问数据的总量，相应的减少磁盘IO操作。

宇哥：嗯，有点内味，你是咋发现的？

小豪：上回你说了，磁盘上的存储空间被划分成了一页页的磁盘页，数据库中每个表的数据就一行行的存储在磁盘页中。访问数据的时候磁盘就需要每次至少将一页磁盘页的数据读取到内存中，而磁盘IO的性能非常低，从而严重的影响数据库系统的性能。

小豪：当数据库需要定位表的某行数据时，就需要将所有存储了该表数据的磁盘页一页页的读取到内存中，需要大量的磁盘IO操作。这时候，如果想要优化，无非就是从两个角度来思考，一个是优化存储结构，减少数据的存储空间，减少磁盘页总数。第二个就是减少无效数据的读取操作，减少无效磁盘页的磁盘IO操作。

宇哥：是的，实际上，这就是索引出现的原因哦。

小豪：对，我发现，确定表的某行数据，其实并不需要匹配一整行的数据，我只要通过其中一列或几列的数据来匹配相关的行就好了，可以把这些用来匹配的列值单独取出来存在额外的磁盘页中，并且为每一个列值增加一个指向该列值对应的那一行数据的指针。这些列值和地址指针组成的磁盘页就是索引的祖先Dense Index，因为单独一个列值和指针的大小远远小于一整行的数据，假设一行数据有十个列的话，现在存储列值和指针的磁盘页数大概只需要存储全部表中数据的十分之1，再访问表中某行数据时，只需要一页页地读取Dense Index磁盘页，当找到对应的列值，然后通过指针读取存储该行数据的磁盘页就好了，相当于最多只需要访问所有Dense Index磁盘页和一页表数据磁盘页就可以了，磁盘IO操作少了整整十倍呢！

宇哥：哇，好厉害！

小豪：你可以夸的再诚恳一点吗？我还没说完呢。光利用Dense Index当然不够，因为还可以继续优化，如果将Dense Index中的键值排好序再利用二分查找算法，查询速度又能快不少呢，这就是折半查找，原来查询无序的所有Dense Index磁盘页时间复杂度是O（n），现在利用二分查找算法查找有序的Dense Index，时间复杂度就是O（log n），再一次减少了磁盘IO的次数，而且到这里为止，我们用额外磁盘页存储的这些东西，刚好对应上了那句，索引是排好序的便于快速查询的数据结构吗？

宇哥：666，属实有点东西，感觉你这个时候才真正理解了这句话啊。

小豪：虽然感觉你有嘲讽我蠢的意思，但是还是当你在夸我好了。索引的进化故事还没完，现在，把排好序的Dense Index磁盘页每一页的第一行列值+指针1（指向表数据）提取出来，并且再加上对应的指针2（指向Dense Index磁盘页）再单独存到新的磁盘页中，把这些新的磁盘页叫做Sparse Index。这里，Sparse Index里面的列值也是有序的，访问数据时，先一页页地读取所有的Sparse Index磁盘页，并对列值进行折半查找，然后读取查找对应的那一页Dense Index 磁盘页，最后读取对应的那一页表数据磁盘页。也就是说，到这里为止，将Sparse Index 磁盘页页数记为n的话，一次访问操作需要读取的磁盘页数最多为n+1+1,这里的n可是远远小于原来的表数据磁盘页数和Dense Index磁盘页数的哦！！！

宇哥：哈哈哈哈，可以可以，说到这你算是把这个问题搞明白了。又因为Sparse Index本身是有序的，所以可以为在现有的Sparse Index再建一层Sparse Index。通过你说的方法，一层一层的建立 Sparse Index，直到最上层的Sparse Index只占用一页磁盘页为止,如下图所示。

小豪：对呀对呀，

• 这个最上层的Sparse Index称作整个索引树的根(root).
• 每次进行定位操作时，都从根开始查找。
• 每层索引只需要读出一页磁盘页。
• 最底层的Dense Index或数据称作叶子(leaf).
• 每次查找都必须要搜索到叶子节点，才能定位到数据。
• 索引的层数称作索引树的高度(height).
• 索引的IO性能和索引树的高度密切相关。索引树越高，磁盘IO越多。

宇哥：总结滴不错哦，小豪弟弟。

小豪：既然话已经说到这了，我就不藏着掖着了，实际上如果表中的某列数据，本身就是有序的，比如id啦啥的，那么我们就可以直接在存储表数据的磁盘页上建立Sparse Index层了哦，就不用中间那层Dense Index了，这实际上就是常说的聚簇索引了，那个排序的键呢就叫做主键，所以这个索引又叫做主键索引。另外考虑到为了提升范围查找的效率呢， 将数据块以双向链表的方式进行连接，因为键值是有序的，所以直接遍历链表就可以实现高效的范围查找。如下图所示：

小豪：我们把这张图逆时针旋转90度，宇哥你看，新鲜出炉的B+树，哈哈哈。

宇哥：优秀，你算是把索引的演变历史给整的明明白白了。

小豪：我敢保证我再也不会忘记索引和B+树是什么了。哎，光知道这些也没用，索引优化还有好多知识点我都不太会，没有经验。

宇哥：别丧气，学海无涯，饭要一口口吃，慢慢进步嘛。

小豪：你说得对，不过宇哥，聚簇索引，也就是主键索引，我知道它是干什么的了，那普通索引怎么定义呀。

宇哥：其实很简单，主键索引的叶子节点存的是整行数据。在InnoDB里，主键索引也被称为聚簇索引（clustered index）。非主键索引的叶子节点内容是主键的值。在InnoDB里，非主键索引也被称为二级索引（secondary index），也就是你说的普通索引。

小豪：那我懂了，也就是说如果现在要查询一个表，该表有一个主键索引 ID，还有一个普通索引k，如果语句是：

select * from T where ID=500

就代表采用的是主键查询方式，则只需要搜索ID这棵B+树；

如果语句是：

select * from T where k=5

就代表采用的是普通索引查询方式，则需要先搜索k索引树，得到对应的id值，再到ID索引树搜索一次。

宇哥：对的，普通索引查找到对应的id值后需要再进行回表操作，再到ID索引树搜索一次。
也就是说，基于非主键索引的查询需要多扫描一棵索引树。因此，我们在应用中应该尽量使用主键查询。

小豪：可是宇哥，我有一个疑问，我看过一篇文章，它说一个innoDB引擎的表，数据量非常大，使用二级索引搜索会比主键搜索快，文章阐述的原因是主键索引和数据行在一起，非常大导致搜索慢，我的疑惑是：通过普通索引找到主键ID后，同样要跑一遍主键索引，为什么它说用二级索引会变快呀？

宇哥：哈哈哈，这其实就是索引的一种优化技术了，叫做覆盖索引。比如如果执行的语句是

select ID from T where k between 3 and 5；

这时只需要查ID的值，而ID的值已经在k索引树上了，因此可以直接提供查询结果，不需要回表。也就是说，在这个查询里面，索引k已经“覆盖了”我们的查询需求，我们称为覆盖索引。
由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。

小豪：soga，原来这就是所谓的覆盖索引，这样结合具体的例子对比分析，很容易理解。

宇哥：不错，类似的，在实际开发中不同的业务场景可能会需要你建立不同的索引，这时候会有很多索引优化技巧，比如联合索引、最左前缀原则、索引下推等等，都需要你慢慢去学习理解。

小豪：宇哥，你咋都知道啊，也没看你学啊。

宇哥：哈哈哈哈，还好还好，只知道一点点，忽悠你还是没问题滴！

小豪：宇哥，听你说了之后，我大概知道聚簇索引和普通索引的区别了，那再实际开发过程中，聚簇索引和普通索引到底应该怎么选择呢？

宇哥：你问的很好，我简单说下。首先，如果你的业务场景不能保证会不会写入重复数据，或者说你的业务干脆就是需要用主键来确保数据的唯一性，那么没得选，你必须对该字段创建主键索引。但是，可能会有这样一类业务场景，业务代码已经保证不会写入重复数据，或者说类似归档库这种确保不会出现唯一键冲突，如果这时候出现了大量插入数据慢、内存命中率低的情景，你可以尝试把表里面的唯一索引改成普通索引。

小豪：为什么呀，在进行更新过程时，普通索引的性能还能高于唯一索引吗？

宇哥：在给你解释这个问题之前，我需要跟你简单地解释一下什么叫做写缓冲(change buffer)。

在需要删除、新增记录或更新一个数据页时，如果数据页在内存中就直接更新，而如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下，InooDB会将这些更新操作缓存在change buffer中，这样就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行change buffer中与这个页有关的操作。通过这种方式就能保证这个数据逻辑的正确性。显然，如果能够将更新操作先记录在change buffer，减少读磁盘，语句的执行速度会得到明显的提升。

你分析一下，写缓冲这个机制是适用于主键索引还是普通索引？

小豪：主键索引好像用不上这个东东，因为如果是利用主键索引来更新数据时，肯定在进行更新操作前是要判断唯一性的，那就需要将数据页中的数据读取到内存中才能判断呀，这就用不了写缓冲了啊。

宇哥：优秀。的确是这样，只有普通索引才能使用上change buffer机制，现在你应该清楚了，对于这种写多读少的业务来说，页面在写完以后马上被访问到的概率比较小，此时change buffer的使用效果最好，你用普通索引能够很大程度上避免磁盘IO操作的消耗。

小豪：我懂了，对于数据的更新过程确实是这样，那么对于数据的查询过程来说，主键索引和普通索引的性能区别不大吗？

宇哥：可以忽略不计。假设，执行查询的语句是

select id from T where k=5；  #此处是覆盖索引哦，不用回表

这个查询语句在索引树上查找的过程，先是通过B+树从树根开始，按层搜索到叶子节点，然后可以认为数据页内部通过二分法来定位记录。

• 对于普通索引来说，查找到满足条件的第一个记录后，需要查找下一个记录，直到碰到第一个不满足k=5条件的记录。
• 对于唯一索引来说，由于索引定义了唯一性，查找到第一个满足条件的记录后，就会停止继续检索。

也就是说，普通索引查询也就比唯一索引多了一个判断的步骤，当下一个记录在内存里面时，判断所需要的时间基本可以忽略不计，而如果k=5这个记录刚好是这个数据页读到内存的最后一条记录，操作会复杂一些，但是显然这种机率很小。

小豪：啊啊啊啊啊啊啊，通透了，我懂了！
宇哥：你又懂了…

开心的小豪

小豪：今天我们聊了索引的进化史，主键索引和普通索引的结构差别以及两者在查询和更新过程中的性能差别，最后聊了两种索引在不同的业务场景下的选择问题，我还知道了change buffer。真是与君一席话，胜读十年书啊。

宇哥：行了，这些都是些基础，你对于索引的学习才刚起步呢。
小豪：我不管，我最6。我饿了，吃饭去吧，冲冲冲！

参考

更多

觉得文章写的不错，关注、点赞、评论是对我最大的支持！
欢迎关注微信公众号LearnJava，一起学习，一起交流哦！