面试不要再问我数据库索引了！！！

索引

索引这个问题在面试中被问到的几率还是挺大的，比如常见的几种问题：索引有哪些类型？索引是不是越多越好？索引底层用的是什么数据结构？为什么用B+树而不用B树？什么是最左前缀原则？密集索引和稀疏索引？为什么最好使用主键自增的索引？如果你只回答索引就像字典一样，能快速的根据目录查找数据，可能面试官听着就不太过瘾，或者说认为你理解的不够深刻。通过这篇文章，你将会从各个方面了解索引，无论是从实战还是面试角度都会有很大的帮助。

什么是索引

官方的定义是：索引是帮助MySQL高效获取数据的数据结构，简单点说索引就是一种数据结构。为什么要用数据结构存储呢？两个字：高效的存储数据。

索引为什么高效

现在有一张表，想要找到col2的值为89所在的那一行，方法有很多，最简单的就是从头开始扫描，一行一行的向下遍历，经过6次扫描后就可以找到了，显然这样效率是不高的，如果用二分搜索树存储呢？

可以看到下图将col2作为索引列，其实就是把clo2的所有值存到二叉树中，通过这颗树找到89只需要1次I/O。同时每一行数据在内存中都会存在一个地址值（最左侧16进制表示），通过这个地址值就可以找到这行所对应的所有数据，找到了89也就能拿到0X77，拿到0X77就能获取到这一行其他列的数据，这就是索引。

索引用的是什么数据结构？

可以看到经过上面的优化，找到89只需要一次，但不是每次都这么"幸运"。同样是二分搜索树的情况下，因为元素进入的顺序不一样，会导致"树形"也不一样，下图中左边是比较理想的，找到89只需要一次IO，但是在极端的情况下，二分搜索树就会退化成线性数据结构，像右图这样找到89就会经过6次IO。如果要将col1这个列所有的元素存储到二分搜索树，那也一定是按照线性结构的，可以看到查找一个元素效率很低。

如果树变成平衡二叉树(AVL)或者红黑树就解决了问题呢？这两种数据结构是可以解决退化成链表的问题，但随着数据的增加，树的深度也会不断增加，IO次数同样会变多，性能同样会下降。

那索引到底是什么数据结构呢？不装了，我是亿万富翁，我摊牌了。那就是B树的改良版B+树。
说B+树之前要说一说B树，说B树之前先说一说哈希，为什么不用哈希存储呢？Hash存取多快啊，哈希存取是快，但哈希有一个最大的弊端，就是不适合范围查询，而MySQL中使用范围查询又比较多，所以目前大多数还是用B+树的比较多，同时哈希不在本文讨论的范围之内。说完哈希，说一说B树，上面不是说数据量大的情况下，树的深度会很深，导致IO次数过多影响效率嘛，那么B树其实就很好的解决了这个问题。

从上图来看，B树就是把每一个存储位置做了横向扩展，原来只能存储一个，现在能存储3个甚至更多，从而来减少树的深度。在MySQL使用的B树中，定义一个最基本的存储单位为[Key,Data]，Key就是索引列对应的值，Data就是除了Key以外的数据，找到了Key就找到了其他数据。关于B树过多的底层实现和复杂度分析不在这里赘述。

• 每个节点能存储的最大元素个数，称之为B树的度(MaxDegree)
• 元素从左到右递增排列（有序存储）
• 所有叶子节点都具有相同的深度（平衡）
• 一个节点指针要么指向另一个节点，要么指向null（叶子节点的指针为空）

既然可以设置度的大小，那么度约大，里面可以存放的元素就越多，树的深度就越浅，IO次数就越少，甚至度足够大，树的深度可以达到一层，那是否度的取值越大越好呢？这样不就经过一次IO就可以了？答案是不可以的，一次性是读不出来的，效率很慢，因为cpu从硬盘取数据时，一次只能取一部分。至于这个度取多少合适？MySQL会自动根据机器配置帮你优化。如果B树作为索引的底层数据结构，效率是非常高的，B+树是B树的改良版，其实B树改良的数据结构很多，B+树只是其中一种，而MySQL用的就是B+树。

那B+树对B树做了哪些改良呢？

• 非叶子节点不存储数据，只存储Key（在度同样的情况下，可以存储更多的Key）
• 叶子节点不存储指针，用于存储数据
• 叶子节点增加顺序访问指针，提高区间访问的性能。（select id from table where id > 20 and id < 50）查找到id>20后，顺着指针直接查询id<50，无须从根节点从新遍历比较
• 通常度一般会超过100，因此树的深度也很浅（一般为3到5层之间）

看到这就可以解释，为什么主键推荐用整型自增了，用UUID不也挺好嘛？首先整型作为索引在插入到B+树的时候，由于树的排序特性，会将元素和其他元素比较，插入到合适的位置，整型很好比较，而UUID很长，比较起来比较繁琐，效率低。自增是为了元素在插入的时候，很快的可以按左小右大的顺序排到最后，不需要多次比较插入到其他位置，然后还需要维护树的结构。

到这里就介绍完索引的基本原理了，明天会更新MyISAM以及InnoDB在使用索引时底层的区别以及更多索引问题。

MyISAM和InnoDB在实现索引的区别

通过上面的介绍，你已经知道了索引的概念，以及MySQL的底层如何用B+树这种数据结构存储索引和数据，但要是具体来看，这里的索引用的是主键索引嘛？还是非主键索引？数据具体指的是数据对应的地址值还是真实数据？由于不同的存储引擎在实现索引的时候，底层实现是不一样的。目前，MyISAM和InnoDB这两种存储引擎使用的最广泛，下面也主要介绍这两种。

MyISAM的数据和索引分离存储的，也就是数据存到一个文件，索引存到一个文件中，下图中MYD就是存储数据的文件，MYI就是存储索引的文件，至于那个sdi是MySQL8.0的新特性，用于代替原来的frm(表结构定义文件)，不在本文的范畴内。

对于MyISAM，MYI存储索引，MYD存储数据，所以说要想查询一条数据，要先根据索引从MYI文件中查到数据对应的地址值，在根据地址值去MYD文件中去找，要经过两次查询。下图是MyISAM存储引擎，使用主键作为索引。

非主键索引结构也是一样的，除了使用col2(非主键列)作为索引外，其他的没有任何变化。

再来看InnoDB，InnoDB用的虽然也是B+树，结构和MyISAM一样，但是具体的存储方式却截然不同，使用InnoDB存储引擎，数据和索引都存储在一个IDB文件中。

和MyISAM不同的是，InnoDB使用主键作为索引时，叶子节点存储的是对应的完整数据。

InnoDB使用非主键索引的时候，与MyISAM区别也比较大，MyISAM无论使用主键还是非主键做为索引，叶子节点存储的都是对应数据的地址值。而InnoDB使用主键索引时，叶子节点存储的是索引对应完整的数据，使用非主键索引时，叶子节点的值存储的是主键索引。

什么？存储的是主键的索引？那如果我建表的时候，没有创建主键呢，也不会报错啊？MySQL已经帮你想好了，如果你建表的时候没有创建索引，MySQL系统会自动选择一个可以作为唯一标识的列当作索引，如果没有合适的列， MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。这样做的好处是什么呢？第一是保持一致性，始终维护一份数据，不容易乱，第二就是节省空间。 知道了InnoDB的索引实现后，就很容易明白为什么不建议使用过长的字段作为主键，除了上面分析问题外，还有就是因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大下图中非主键是按照ASCII码来做比较的。

明天会更新关于索引和SQL优化方面更多知识！
点赞关注不迷路！