MySQL优化一：索引底层数据结构与算法_添加索引优化的底层-CSDN博客

本文链接：https://blog.csdn.net/zyh0110/article/details/128691347

前言：

在我们的项目开发中，尤其是对外效果展示的时候，数据库查询性能一直是一个非常重要的指标(排除中间缓存实现)，它很大程度上决定了你的页面输出效率，对响应时间要求严格的客户来说，这个至关重要。
那么就要求我们在数据查询实现过程中，去做各种各样的优化，来提高我们的查询效率。
这时候，索引就应运而生了。

一、什么是索引？为什么要建立索引去优化查询？

在平时数据库表查询的时候，大家或多或少会用到索引查询，那么什么是索引呢？为什么DBA在我们项目开发完成后建议我们一定要针对不同的表建立索引去优化查询呢？这里给大家做了介绍。

索引的本质：MySQL一种用来高效查询的排好序的数据结构。

如下表所示，有这样一条SQL语句：select * from test where Col2 = 89;

MySQL是怎么定位到89那条记录的呢？(分为两种情况)
查询条件没有索引：从第一条记录开始，依次将每条记录与条件做对比，直到找到符合条件的记录，然后返回。而表数据都存储在磁盘文件中，也就意味着，读取每条数据都需要与磁盘IO交互。我们都知道IO操作其实是比较耗费性能且效率也不高的，那么我们找到89这条记录一共需要读取6条数据，也就是6次IO读取操作。
有索引(Col2列建立索引，暂时将右侧二叉树作为索引结构来看)：会从根节点开始比较节点的值是否满足条件，可以看出，找到89只需读取两次(也就是IO两次)即可。

从上面分析可知，在一条sql语句查询时，使用索引的效率>>>不使用索引的效率。

那可能有人会问了，这些表数据Col1是自增列，那么我将sql的条件换成Col1= 6，那这些数据从磁盘查找也很快啊，也不是那么需要索引嘛。这其实是一种错误的观点！！！
表数据文件在磁盘上是随机存储的，表中相邻行的数据在磁盘上的存储不一定在一块。这里有个时间差的概念，也就是说，表中相邻数据的存储有时间差的话，那这个时间差可能会有其他数据写入磁道，这样一来，相邻行数据在磁盘上存储就不一定在一块了。

归根结底，我们为什么要使用索引来优化查询呢，就是为了减少定位条件数据而与磁盘IO的交互次数！！！

二、数据结构选取

那么mysql索引底层使用的数据结构到底是不是二叉树呢？为什么不使用其他的数据结构去构建索引树呢？

二叉树❌

对于单列自增长的列去建立索引的话，结果会形成伪链表形式的二叉树，这样的话，要想找到目标记录，跟全表扫描没有什么区别，索引就形同虚设(全表扫描6次，索引也扫描6次)。

特殊情况下如👆图所示。

红黑树❌

红黑树根据其自平衡特性，完美的解决了上述二叉树所遇到的特殊问题，但是数据量很大的情况下，树的高度会很高，极端情况下，要想找到目标数据，树有多高就扫描多少次，效率仍旧不高。树的高度不可控！！！

极端情况下(树的节点延展越来越多时，高度会越来越高)如👆图所示

hash索引❌

定义：对索引的key进行一次hash运算就可以定位出数据存储的位置。

局限性

仅能满足"in"、"or"查询，不支持范围查询。

存在hash碰撞问题。

示例如👆图所示

B树❌

特点

叶子节点具有相同的深度，叶子节点的指针为空。

所有的索引元素不重复。

节点中的数据索引从左到右递增排列。

B树示例如👆图所示

mysql数据存储(思维构建)如👆图所示

🔥弃用理由(与B+树相比)

B+树的非叶子节点不存储data，只存储索引，因此一个叶子节点(数据页)可以存储更多的索引。

假如使用bigint类型(8个字节)的数据列来构建索引树，那么mysql一个数据页大约可以存储16384/(8+6)=1170个索引，一个叶子节点所在的数据页大约可以存储16条数据(一条数据假设总大小为1k)。如果树的高度=3，那么此索引树一共可以存储1170x1170x16条数据(大约2000多万条)，这已经可以满足绝大多数情况了。
而2000多万条数据如果使用B树存储的话，一个数据节点页大约可以存储16条数据，那么树的高度将可能远远大于3(16^n=2000万)，那么查询效率较B+树会很低。

优化后的B+树的叶子节点使用双向指针连接，区间访问数据(范围查询)更加高效。

B+树✔️

特点

非叶子节点不存储data，只存储索引(冗余)，这决定了整棵树的高度。

叶子节点包含所有的索引字段。

MySQL优化后，叶子节点使用双向指针连接，提高了区间访问的可能。

B+树示例如👆图所示

MySQL存储(优化后使用双向指针)示例图片如👆图所示

三、索引结构组织文件

不同的存储引擎数据和索引的文件存储方式不同，那么我们平时使用的InnoDB表存储引擎，其索引数据都是怎么存储的呢？

MyISAM存储引擎

索引文件：.MYI文件；数据文件：.MYD文件；表结构文件：.frm文件

非聚簇索引：索引文件和数据文件是分开存储的。查询数据时，需要通过索引文件找到数据在数据文件的位置，再去数据文件定位。

InnoDB存储引擎

索引数据文件：.ibd文件；表结构文件：.frm文件。.ibd文件是按照B+树组织的一个索引数据文件，包含索引和数据。

聚簇索引(一般只有一个，就是主键索引)的叶子节点包含了完整的数据记录，可以理解为主键索引(一般主键索引就包含了完整数据记录)。

聚簇索引(主键索引)示例图

二级索引不属于聚簇索引，其叶子节点的value值是主键索引的主键，想要完整的数据记录需要进行回表操作(根据主键值到主键索引树查询完整的数据记录)

非聚簇索引(二级索引)示例图

相关面试题解答

1，为什么建议InnoDB表必须建主键，并且推荐使用整型的自增主键？
必须建主键：使用索引构建索引树时，如果没有设置主键，mysql会逐列查找没有重复数据的一列作为索引列去组织索引文件。如果没有找到这样的一列，mysql会自己维护一列不重复的数据(隐藏列，rowid)作为索引列去构建索引文件。这件事情也是会消耗mysql内部性能的，应该尽量减少这样的操作。
整型：占用空间相比较其他数据类型来说比较小，一个索引数据(节点)页能够存放更多的索引，降低索引树的高度，提高查询效率；节约整体磁盘占用空间。数据时常要进行比大小，整型数据比大小快捷方便，效率也较高。
自增：使用B+树结构组织索引树时，如果不是自增数据，那么可能会造成一个数据页因为要插入中间数据而进行分裂数据页的操作，甚至整个索引树都要再次平衡。如果是自增数据的话，只需要新增索引页即可，比起前者，性能效率都会比较高。

2，为什么非主键索引结构叶子节点存储的是主键值？
一致性：如果存储的是完整的数据，一旦数据变更，主键索引的叶子节点+非主键索引的叶子节点数据都更新完成后，才算更新操作完成。而存储的是主键的话，主键索引的叶子节点数据更新完成后即可认为是更新操作完成，为了保证一致性，避免同步数据造成的数据问题。
节省磁盘存储空间，相比较完整的数据，主键显然占用空间更小。