数据结构：Mysql索引原理（通俗易懂）_mysql数据库索引原理

前言

在工作中如果经常写业务代码，说白了就是CURD，比如做一个查询功能，我们会将前端参数传递到后端后拼接SQL查询数据，那为了提高用户体验，查询速度肯定是越快越好，这就要求我们能够对SQL调优，让查询速度更快。

由于数据需要持久化，且数据量庞大，所以只能将数据存储到硬盘中，我们在查询时需要先将数据从硬盘中加载到内存中，因为CPU从内存中拿数据的效率要比从硬盘中拿数据的效率高很多。查询数据时通常需要拿到当前的数据后对比是否满足查询条件，这个过程由于是在内存中进行的所以速度非常快，而要进行数据对比时，就需要先将磁盘中的数据加载到内存中，这就是我们平常所说的IO，IO的时间损耗比较高，基本上整个查询过程的IO的占比要比数据对比耗时高得多，所以就要想办法减少IO的次数，提高查询效率。

假设每次加载数据大小是16K，而表数据中每行大小为1K，也就是1次IO只能加载16条数据，那对于量大的表，完全没法玩了。那可以为每行数据建立一个唯一的ID列，然后在外部建立一个key-value的容器，key为ID列的值，value为对应数据行的数据地址，这样每个key-value的数据大小肯定远远小于每行数据的大小，这样我一次IO就能加载很多key-value到内存中，然后去做比较，找出符合条件的key-value，然后通过value存储的地址去加载表中的数据，这样效率就可以大大提高，索引就是这种原理。

正文

既然知道可以通过索引来提高查询效率了，那如何设计索引呢？

索引结构-数组

数据无疑是最简单的结构了，初始化Entry<key,value>数组，当插入数据后，往数组中维护key为行数据的所有列值，value为行数据的空间地址；

无序数组

如果数组中的数据不是根据key值进行排序插入的，那么每次插入只需要往容量值大小位置插入即可，效率较高；当删除数据时，如果不考虑空间碎片化问题时，即允许被删除位置为null，删除的效率也是比较高；虽然插入删除效率较高，但是查询效率却非常低，由于是无序的，意味着需要从头遍历数组，直到找出合适的数据位置，时间复杂度为O(N)，那如果有1百万个索引值，这样的效率显然不能满足我们的要求；无序对范围查询不友好，需要遍历整个数组；

有序数组

无序的数据查询的时间复杂度为O(N)，不能满足大数据量下的要求，那如果数据按key的顺序进行插入，变成一个有序数组，有序数组查询时可以使用二分查找法，时间复杂度为O(logN)，这样的查询效率就比较高了，由于是有序的，对于范围查询效率比较高，不需要遍历整个数组。但有序数组在插入数据时，会涉及数组的移动，在大数据量的场景下，插入的数据位置刚好比较靠前，那么后面的数据得往后挪动，那数据插入这块耗时就比较久，显然也不适合作为索引的数据结构。

数组由于需要先开辟空间，因此要指定长度，当数据空间满时，还需要考虑扩容问题，显然大数据场景下不适合使用数据作为数据结构；

索引数据结构-Hash

数组插入时涉及到空间的移动，插入效率低下，那如果对key进行hash后跟数组长度取模，这样就可以计算出key在数组中的下标，而且查询的时间复杂度为O(1)，听起来增删改查都好快，那选Hash来作为索引的数据结构吧。

别高兴的太早，再好的hash算法都可能发生冲突，也就是不同的key计算出来的下标地址可能一样。

解决Hash冲突有多种方式，我们来分析下有没有适合的；

Hash冲突-链式寻址法

在每个索引节点新增一个字段next，用来保存下个索引节点的地址；当计算出数组已经存在数据时，开辟新的空间存储插入节点，然后将空间地址赋值给数组对应链表的尾节点的next字段；通过这种方法解决hash冲突后，查询时通过公式计算出数组下标，拿出当前数组下标节点（头节点），将链表挨个遍历对比key的值是否一致，查询值符合要求的数据。这种结构当数据量大时，可能hash冲突非常严重，导致链表长度非常长，这样查询遍历的损耗时间就很长。

数据量大时，由于链表较长，每次IO只会加载一定量的数据到内存中，量大IO频率变高，导致时间较长。HashMap在1.8引入了红黑树，就是用于优化链表过长查询效率低下问题。

像HashMap、Redis采用这种数据结构方式，我理解是因为数据是存在内存中的，不像数据库的索引文件存在硬盘，所以节省了从硬盘读取数据的IO开销。

Hash冲突-再哈希法

当通过hash公式计算出下标，发生hash冲突时，使用新的hash公式或者原公式对hash后的值再进行hash，直到计算出来的下标位置为空时进行存储。那取值时，通过公式计算出下标后，比较key是否一致，不一样时再进行hash计算下标进行比值，以此类推直到查询到新的值为止；再Hash法可能需要多次公式计算，所以比较消耗时间。

Hash冲突-开放地址法

当发生hash冲突时，在当前下标往后进行遍历查找，寻找出地址为空的下标进行存储。当查询时，通过hash公式计算出下标，然后比较Key值是否一致，不一致则往后遍历寻找，如果遍历的位置下标为空时，查询结束，代表当前数组中不存在符合条件的数据了。由于遇到空的就会结束查找，所以当删除数据时，不能将该下标数据清空（只能弄个字段来做标记），不然其后面的同义数据（Hash冲突的哪些节点数据）将会查询不到。那为了减少hash冲突，就要求填充因子要尽量小（当前已存储容量/容量总长度），数据量大时，在保持填充因子小的情况会空间利用率会变得很低。

ThreadLocal内部数据结构就是采用开放地址法来解决Hash冲突的。

Hash方式不适合进行范围查找，只能扫描整个数据容器把符合条件的筛选出来，对于没有范围查找的索引key，可以使用hash的方法来作为底层数据结构；

索引数据结构-树

既然hash不支持索引，那我们可以考虑使用Tree来作为数据结构

二叉树

使用二叉树来作为索引的数据结构，插入时比较key的大小，比当前节点的key大时往右边插入，比当前节点的key小时往左边插入。查询时也是通过比较其key值看是否相等，不相等则比较大小来解决查询方向，使用二叉树的查询效率比较快，时间复杂度为O(logn)，查询效率高且支持范围查询；

二叉树虽然查询效率高，且支持范围查询，但是在极端的情况下会链化，如按顺序插入10、20、30、40、50，这种情况的查询时间复杂度就变成O(N)了，大数据量的情况下显示不能被接受；

平衡二叉树

为了解决二叉树链化的情况，为此增加了一条限制，左右两个子树的高度差不能超过 1，超过时通过左旋和右旋来维持平衡，因此左右两边相对平衡，因此称之为平衡树；

在线演示地址：https://www.cs.usfca.edu/~galles/visualization/AVLtree.html

插入30后，由于10的右边与左边的高度差为2，因此需要进行左旋

插入10后，由于30的左边与右边的高度差为2，因此需要进行右旋

平衡二叉树通过旋转避免了极端情况下链化的场景，但是在大数据量的情况下，每次插入可能因为维持平衡而导致多次左旋或右旋，这样在插入效率上极低，显然平衡而插入不适合大数量的场景；

红黑树

平衡二叉树数据量大的插入数据由于要通过多次的左旋或者右旋保持平衡，导致效率低下，那有没有办法可以减少左旋或者右旋的次数呢？

红黑树可以认为是平衡二叉树的增强版，它引入了颜色标记来减少旋转的次数，之前笔者有专门写了一篇红黑树的，想深入了解红黑树可以去看《HashMap源码学习：红黑树原理详解》

红黑树的特性

红黑树在线演示地址：https://www.cs.usfca.edu/~galles/visualization/RedBlack.html

• 根节点必须是黑色节点。
• 节点是红色或黑色。
• 所有叶子都是黑色。（叶子是Null节点）
• 每个红色结点的两个子结点都是黑色。（从每个叶子到根的所有路径上不能有两个连续的红色节点）
• 从任一结点到其每个叶子的所有路径都包含相同数目的黑色节点。

红黑树如何减少旋转

红黑树左边结构要保持平衡只需要变色即可；

平衡二叉树左边要保持平衡需要以35为旋转节点进行左旋，再以50为旋转节点进行右旋，所以需要经过两次旋转；

红黑树的变色只需要修改字段的状态值即可，不涉及多个指针变动，减少了旋转，所以插入效率要比平衡二叉树高；

好了，既然红黑树的插入效率提高了很多，那么我们来看下查询效率吧，在大数据量的情况，由于每个节点只有两个分支，树的高度会变得非常多层，如果每次从磁盘中加载了一层，总共30层高的话，需要进行30次IO，那查询会损耗大量时间在IO上，显然这种不适合作为大数据量下的索引数据结构；

B树

红黑树在大数据量的情况下缺点比较明显，说白了就是分支偏少，每次查询的范围较大，如果分支增加那么就可以缩小范围，树的高度也会因此而减低。多路树就是在让每个节点拥有的分支更多，让查询的范围缩小；

B树在线演示地址：https://www.cs.usfca.edu/~galles/visualization/BTree.html

B树也是多路树的一种实现，B-树中的每个节点除了存放索引值、其它节点引用地址外，还存储数据。B树每个节点存储子节点个数取决于每个子节点的大小，比如每次加载一个16K的节点，每个子节点占用4k（索引值、数据、引用地址等），即每个节点只能放4个子节点。

在查询时，如果子节点存储数据过大，会导致IO次数变多，比如每次加载一个页16K到内存中，每个节点占用2K，最终只能加载8个节点到内存中，节点的占用越低每次加载到内存的节点树就会越多，在内存中进行计算肯定是比加载数据IO时间少得多的，所以像Mysql每行数据字段特别多的情况下，使用B树显示查询效率会很慢。

B+树

B树的缺点是会因为节点文件过大导致读取磁盘次数变得，让查询效率变低。那如果节点不存储真实数据，那其大小就会变得很低，B+树就是结合这种场景，将数据放到叶子节点中，而我们的非叶子节点只存索引值、其它节点引用值即可，这样可以大大减少读取磁盘的次数了。叶子节点通过指针链化，这种结构支持范围查询；B+树在大数据量的场景下，查询效率也是比较高，这也是为什么Mysql使用B+树的原因了。

B+树在线演示地址：https://www.cs.usfca.edu/~galles/visualization/BPlusTree.html

Mysql的索引

一级索引

Mysql的InnoDB存储引擎会以主键作为聚簇索引，如果没有主键则以非空的唯一索引作为聚簇索引，如果都没有则以隐式的方式创建自增的ID来作为聚簇索引。聚簇索引就是一级索引，它以B+树作为数据结构，存储了索引值和真实的行数据值。

现有表数据（ID为主键）：

一级索引建立后：

当我们使用ID作为搜索条件时，就会走索引，快速拿到查询数据

select * from student stu where stu.id=2; 
1

二级索引

二级索引不会存储完整的行数据，只会存储索引值以及一级索引的值；

下面以年龄字段建立普通索引：

当执行查询语句时，会根据age索引找到一级索引的值，由于要返回整行的数据，二级索引查询到值后，拿出一级索引值回表查询，也就是去一级索引树中查询整行数据。那如果刚好要返回的数据值在二级索引树中存在，就不要回表了。

select * from student stu where stu.age=12; 
1

总结

不同的数据结构适用于不同的场景，具体的结构选型应根据数据量、字段类型、查询频率来决定。