【.com原创稿件】你是不是一直都像MySQL索引知识点的大杂烩，好像什么都知道，深究的话，可能一个都答不上来。图片来自Pexels如果你去面试，面试官让你谈谈你对索引的理解，但是你对索引的理解仅限于检索数据快，是数据结构层面的，那你只能回家等通知上去。为了避免这种尴尬的事情发生，卡卡花了两天的时间，在自己理解的范围内整理了索引的内容。如有整理不全的地方，可以在评论区进行补充和建议。MySQL索引到底是什么？相信大部分小伙伴都买过技术书籍。不知道有没有看过，目录一定是看过次数最多的。看看目录有没有什么痛点，如果有，就根据目录对应的页码，以最快的速度浏览到相应的内容位置。那么在MySQL中也是如此。MySQL的索引是存储引擎快速查找数据的一种数据结构。MySQL的索引也有几种类型，分别是：B树索引哈希索引空间索引全文索引下面的所有内容都是在InnoDB的基础上讨论的。为什么要使用索引①索引可以加快数据的检索速度，这也是使用索引的主要原因。②索引本身是顺序的。在进行范围查询时，获取的数据已经排序，避免服务器重新排序和建立临时表的问题。③索引本身??的底层实现是顺序的，通过磁盘预读大致顺序寻址磁盘上的数据，即将随机I/O变为顺序I/O。如果你不明白这几点，暂时搁置一边，继续往下看，我会给你一个满意的解释。任何事物都有两个方面。既然能提供性能提升，自然会在其他方面付出额外的代价：索引与数据共存，因此会占用额外的存储空间。索引的创建和维护都需要时间成本，而且这个成本随着数据量的增加而增加。创建索引会降低数据增删改查的性能，因为修改数据的同时需要修改索引数据。为什么InnoDB使用B+Tree而不是BTree？说这道题的时候一定要分清楚BTree和B+tree的区别。首先，让我们看一下BTree。Btree解析首先我们来看一下BTree的数据结构。这里卡卡提供了一个网址，可以看到一些关于数据结构的实现过程：https://www.cs.usfca.edu/~galles/visualization/Algorithms.html我们先来看看BTree的数据结构。下图显示了卡卡在数据中填写的内容：这里是关于Max的一个陌生区域。学位，你可以理解为顺序或学位。比如这个值设置为4，那么一个节点最多可以存储3条数据，设置为5，则最多可以存储4条数据。现在可以看到只插入了3条数据：再添加一条数据，节点就会分裂，这也验证了当order设置为n时，一个节点可以存储n-1条数据数据。那我们再插几条数据看看：要实现数据的快速检索，需要满足两个特点，一个是有序的，一个是平衡的。从下图我们可以看出，BTree是有一定顺序的，平衡性比较理想。你可以看到上面生成的第一张图片。那么如何在BTree中找到一个值呢？例如，如果现在要查找值9，请看一下查找过程。看到的第一个数据是4，9大于4，所以要找4的正确节点。继续找6到8的节点，9大于8，所以要找右节点。最后一步是查找数据9.这个过程就是BTree数据结构查找数据的执行过程。了解了BTree的数据结构之后，我们再来看看BTree在MySQL中是如何存储的。下图中，P代表一个指针，指向下一个磁盘块。第一个节点中的16和24代表我们的键值是什么。date是这个键值对应的记录行是什么。那么此时如何找到key为33的记录呢。33在16和34之间，所以会去3号盘找。在3号盘判断，指针指向8号盘，在8号盘可以得到数据33，然后返回数据。那么这个过程中读取了多少条数据呢？在计算之前，需要了解一些知识点。从MySQL5.7开始，默认的存储引擎是innodb，innodb存储引擎用来管理数据的最小磁盘单位是页。这个页面有几种类型，分别是数据页面、撤销页面、系统页面和事务数据页面。一般来说，页面就是数据页。默认页大小为16kb，每页至少存储2行或更多行记录。然后根据BTree数据查找过程，可以知道一共读取了三个磁盘，每个磁盘的大小为16kb。目前的情况下，找到了三层，所以三层存储的数据为：16kb*16kb*16kb=4096kb。如果一条记录需要1kb的内存，那么这三层BTree可以存储4096条记录。你的数据库中的数据从几百万到几千万的数据，那么BTree的层级会越来越深，相对的查询效率也会越来越慢。这时候我们是不是应该思考一个问题，就是为什么Btree的48kb内存只能存放4000多条记录？问题出现在数据中。要知道在计算数据大小时，指针地址和key内存都是算的，如果不计算的话，只计算数据的内存。因为在BTree结构中，节点中不仅保存了key，还保存了指针地址和对应的数据，所以会造成单个磁盘保存的数据相对较少。为了解决单个节点存储数据量小的问题，又演化出了另一种结构，就是下面要说的B+Tree。B+Tree分析还是和之前一样。我们来看看B+Tree的数据结构。为了方便比较，将BTree和B+Tree的数据结构放在一起。那么我们可以看到B+Tree中的叶子节点存储的是全量数据，而非叶子节点只存储key值。嘿！这不是很好的解决了BTree带来的问题吗？它允许每个节点存储更多数据。每个节点存储的数据越多，树的相对深度就不会太深。了解了B+Tree的数据结构之后，我们再来看看B+Tree在MySQL中是如何存储的。从上图可以明显看出有两点不同：第一点：B+Tree的所有数据都存储在叶子节点上。第二点：B+Tree的所有叶子节点都是一个链环结构。那么这个过程中读取了多少条数据呢？如果B+Tree读取的数据深度和B-Tree一样，都是三层，那么同理每块磁盘的大小都是16kb。B+Tree中非叶子节点可以存储多少数据？一般来说，每个表都会有一个主键。基于三层计算，第一层和第二层存储键值，即主键值。我们都知道int类型占用的内存是4Byte（字节），指针的存储是6Byte，一共是10Tybe，所以第一层节点可以存储16*1000/10=1600。同样，第二层的每个节点也可以存储1600个密钥。第三层是叶节点。每个磁盘的存储大小与BTree相同，每条数据占用1kb。那么B+Tree中三层可以存储的数据为1600*1600*16=40960000。从这个角度来看，B+Tree中存储的数据与BTree中存储的数据不在一个层次上。所以可以得出结论，B+Tree相对于BTree能够保证检索的数据量是最大的，存储的数据量也是最大的。B+Tree在选择索引时，尽量选择占用内存空间小的类型，比如int类型。键占用的内存越小，节点存储的范围就越多。哈希索引首先创建一个哈希索引：altertableuseraddindexhash_genderusinghash(gender);存储引擎使用innodb：你会发现name的索引类型还是Btree，而在innodb上创建哈希索引叫做伪哈希索引，和真正的哈希索引不是一回事，这个一定要明白.在Innodb存储引擎中，有一个特殊的功能叫做自适应哈希索引。当索引值使用频率很高时，它会根据内存中的BTree索引创建一个哈希索引，这时你就有了哈希索引。希腊索引的某些功能，例如快速搜索。哈希索引是基于哈希表实现的。假设为名称建立哈希索引，查找过程如下图所示。哈希表是一种根据键值对访问的数据结构，它可以让检索到的数据通过哈希函数映射到哈希表的相应位置，查找效率非常高。哈希索引存储哈希值和行指针，但不存储键值和字段值，但哈希索引大部分是在内存中完成的，检索数据非常快，因此对性能影响不大：哈希索引不是按照索引值排序的，所以无法排序。哈希索引只支持等价操作，不支持范围搜索。只有=、in和<>可以在MySQL中使用。哈希索引在任何时候都无法避免表扫描。当哈希索引遇到大量哈希冲突时，存储引擎必须遍历链表的所有行指针，逐行进行比较。B+Tree和BTree的区别经过了很长的计算和绘制。现在我对两者的区别有了一定的了解！卡卡在这里总结一下：B+Tree的叶子节点存储全量数据（key+data），而不是叶子节点只存储key。B+Tree在同一深度存储的数据要比BTree大很多。B+Tree的每个叶子节点都有到下一个叶子节点的链接。这样做的好处是我们可以从任意一个叶子节点开始遍历，得到接下来的所有数据。B+Tree之所以适合做索引，是因为B+Tree树的非叶子节点只存储键值，所以相对于BTree节点，可以存储更多的数据，读入内存的键值也更多每一次。相对而言，I/O只是低一些。B+Tree树查询效率稳定，任何数据查找都必须从叶子节点到非叶子节点，所以每次数据查找的效率几乎是一样的。B+Tree树的叶子节点存储了全量的数据，并且是有序的，所以遍历叶子节点就可以扫描到所有的key，在查找范围的时候效率更高。以上就是对InnoDB存储引擎为什么使用B+Tree作为索引的分析。聚簇索引和非聚簇索引的区别在于聚簇索引和非聚簇索引又称为主索引和二级索引。那么如何区分聚集索引和非聚集索引呢？首先查看InnoDB引擎下建表生成的文件，可以看到有两个ibd文件。不知道大家在这里有没有疑问：为什么有的文章也有frm文件？但为什么不在这里呢？原因是MySQL8.0以后，源数据存放在表空间，所以没有frm文件了！我们都知道这个idb文件会存放数据信息和索引信息。然后看看Myisam存储引擎产生的文件来创建表。从图中可以看出，创建表会生成三个文件，扩展名为MYD、MYI、sdi：MYD：表数据文件（保存数据的文件）MYI：表索引文件（保存数据的文件）theindex)then可以得出一个结论：只要数据和索引存放在同一个文件中，就是聚簇索引，否则就是非聚簇索引。这时候有人会问了，当表有主键的时候，idb文件中存放的是主键+数据，那么不设置主键怎么办呢？记住这句话，在InnoDB中，插入数据时必须用一个索引值来绑定。如果没有主键，则选择唯一索引。如果没有唯一索引，将选择一个6Byte的rowid。表中有多个索引以及数据是如何存储的。看完上面的解释，你是不是有过疑惑呢？在InnoDB存储引擎下，如果有多个索引，会生成多个idb文件。在InnoDB中，只会保存一份数据。如果有多个索引，就会维护多个B+Trees，例如：表字段id、name、age、sex。如果id设置为主键索引（聚簇索引），name设置为普通索引，会存储多少份数据？不管一张表设置多少个索引，都只会存储一份数据，但是这张表会维护多个B+Tree。按照这种情况，id是主键索引，name是普通索引，那么这张表中会维护两棵B+树。id主键索引与数据存储在一起，name索引所在B+Tree中的叶子节点存储主键id的值。对应的图片就是下面两张图，大家可以好好看看：最后给大家总结一点：在InnoDB中，必须要有聚集索引，其他索引都是非聚集索引。这里简单提一下：Myisam中只有非聚集索引。这些关键词在面试中经常被问到索引的几个专业术语，分别是返回表、覆盖索引、最左原则、索引下推。你必须知道！返表网站上有各种关于返表的解释。各种各样，卡卡会告诉你一些简单易懂的，但是前提是你需要区分聚簇索引和非聚簇索引。还是用上面的例子，id是主键索引，name是普通索引。此时查询语句为：selectid,name,agefromtablewherename='kaka'，那么这条语句会先在name的B+Tree中找到主键id，然后根据主键的索引获取数据id并返回它。这个过程其实就是从非聚集索引跳到聚集索引去查找数据，也就是所谓的回表，也就是说当你查询的字段是非聚集索引，但是有没有非聚集索引需要查询的所有字段都包含返回表。这种情况下，非聚集索引name的叶子节点只有id，没有age，所以会跳转到聚集索引，根据id查询整条记录，返回需要的字段数据。覆盖索引覆盖索引，根据名字，可以理解的差不多，就是查询的所有字段都被索引了！此时查询语句为：selectid,namefromtablewherename='kaka'，那么这条语句使用了覆盖索引，因为id和name都是索引字段，查询字段也是这两个字段，所以称为索引覆盖.也就是说，当非覆盖索引的叶子节点中包含需要查询的字段时，称为覆盖索引。最左匹配最左匹配原则存在于复合索引中。还是用之前的表信息：表字段id、name、age、sex。这时候把名字和年龄设置成一个复合索引。以下哪个语句不符合最左原则：select*fromtablewherename=?andage=?select*fromtablewherename=?select*fromtablewhereage=?select*fromtablewhereage=?andname=?你可以自己做一个测试！只是第三条语句不会使用索引，其他三个语句都符合最左原则。关于这个最左原则远不止这么简单，一试就是一个坑，关于这部分内容会在后面的优化文章中提到。索引下推还是用这条sql语句：select*fromtablewherename=?andage=?索引下推出现在MySQL5.6及以后的版本中。之前的查询过程是先根据name在存储引擎中获取数据，然后在server层根据age进行过滤。索引下推后，查询过程就是根据name和age从存储引擎中获取数据，返回相应的数据，不再去server层进行过滤。使用Explain分析SQL语句时，如果出现Usingindex条件，说明使用了索引下推，复合索引最容易出现索引下推。索引存储在哪里？索引数据文件存储在磁盘上，需要持久化。但是使用索引时，数据会从磁盘读取到内存中，读取方式是分块读取。这时候就涉及到操作系统的概念了。操作系统从磁盘获取数据。假设要取数据的大小是1kb，但是操作系统不会只取你需要的1kb，而是会取4kb的数据。为什么是4kb，因为操作系统中一页的数据就是4kb。那为什么只需要1kb，就把整页数据拿出来呢？那么就会涉及到另外一个概念，就是局部性原理：数据和程序往往会聚集在一起。访问一条数据后，很有可能会再次访问这条数据和这条数据的相邻数据。因此，MySQL的InnoDB存储引擎在读取数据时也是采用局部性原则，每次读取的数据为16kb。在InnoDB存储引擎下，默认每页大小为16kb。这个参数也可以调整。参数是innodb_page_size。最后一点：由于题目问的是索引数据存放在哪里，所以第一句直接回答索引存放在磁盘上，以页为单位从磁盘读取到内存中。那为什么不直接存入内存呢？你有这个问题吗？如果索引数据只保存在内存中，那么当电脑关机，服务器宕机时，需要重新生成索引，效率很低。综上所述，以上就是卡卡对索引的理解，尽量把知识点讲解的比较全面。如果还有遗漏，或者文中有错误，还请大家多多指教。作者：卡卡编辑：陶嘉龙投稿：如有意投稿或求报道，请加编辑微信gordonlonglong【原创稿件，合作站转载请注明原作者和出处.com】
