MySQL—索引常见数据结构

举起大象的蚂蚁

已于 2023-11-26 16:48:34 修改

阅读量105

点赞数 1

分类专栏： mysql 文章标签：数据结构

于 2023-11-11 16:38:59 首次发布

本文链接：https://blog.csdn.net/zx_liu/article/details/134336668

版权

mysql 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

1、索引概念

索引 ( index）是帮助MySQL高效获取数据的排好序的数据结构。

2、常见的索引数据结构

树的高度和查询速度紧密相关，树越高，查询效率越低。

2.1、二叉树（mysql未使用）

特点：

1、树的高度不可控，会严重影响查询效率

2、对于单边增长的数据列（比如自增主键），对查找效率没有帮助

2.2、红黑树（平衡二叉树,mysql未使用）

特点：

1、当数据量大时，树的高度不可控制，树的高度会非常大，如果要查找的数正好在叶子节点上，红黑树是从根结点开始查找，比大小的原则往下去找，如果树高度是20，那么也要查找20次才能找到，效率很低

2.3、Hash表（mysql中可以选择使用）

假设0x56 为 zhao 对应的磁盘文件地址，例如查询 name 为 zhao 时，会先通过 hash 运算得到结果桶 1，再去遍历桶 1 的链表，最终查询到 zhao，包括所对应的磁盘文件地址。

特点：

1、对索引的key进行一次hash计算就可以定位出数据存储的位置

2、很多时候Hash索引要比B+树索引更高效

3、仅能满足“=”，“IN”，不支持范围查询

4、hash冲突问题

2.4、B-Tree（mysql未使用）

特点

1、叶节点具有相同的深度，叶节点的指针为空

2、所有索引元素不重复，非叶子节点也会存储data

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

2.5、B+Tree（B-Tree变种，mysql采用该结果进行存储数据的）

特点：

1、非叶子节点不存储data，只存储索引（冗余，比如8、51、88这些就是冗余索引），可以放更多的索引

2、叶子节点包含所有索引字段

3、叶子节点用指针连接，提高区间访问的性能

3、存储引擎

3.1、MyISAM存储引擎

MyISAM索引文件和数据文件是分离的（非聚集索引，数据和索引是分开存储的）

MYI文件：以Col1建立索引，MYI文件用B+Tree的的结构组织存储好索引信息，根据上图可知，叶子节点中data存储的是磁盘文件地址，根据磁盘地址可以去MYD文件里定位到具体的这一行数据。比如查询条件：where Col1=36,通过MYI可以找到磁盘地址是0x88,再通过0x88去MYD文件中找到这一行数据。

3.2、InnoDB存储引擎

InnoDB索引实现（聚集索引，数据和索引是在一起的）

1、表数据文件本身就是按B+Tree组织的一个索引结构文件
2、聚集索引—叶节点包含了完整的数据记录
3、为什么建议InnoDB表必须建主键，并且推荐使用整型的自增主键？

        问题①建议InnoDB表必须建主键：若不建主键，则会在创建表时，遍历每一列，选择其中数据没有重复出现的一列作为主键；若不存在符合条件的列，则会自动创建一个隐藏列（rowId）作为主键来进行维护。因此需要在创建表时主动建主键，减少数据库不必要的性能消耗。
        问题②推荐使用整型的自增主键：当我们找一个数据时，都是从根节点开始查找，在索引定位的过程中，中间经过了很多次数据比大小，而整型在进行比较时效率最高（像uuid这种比大小肯定没有整型的快）。索引这些文件是存储在磁盘上的，整型的占用的磁盘空间更小（更节约空间），所以用整型做索引的最好。
        问题③为什么用自增主键：因为B+Tree是递增的结构。若不使用自增主键，则在加入新的数据时，可能会多次将数据插入到前面已经放满索引元素的节点，使其进行分裂调整，甚至会导致整棵树重新进行平衡，极大降低效率。因此选择使用自增主键，在每次加入新的数据时，都从末端插入数据，效率最高。
4、为什么非主键索引结构叶子节点存储的是主键值？（一致性和节省存储空间）

a、主键索引（即聚集索引）由主键来组织整张表的数据，因此叶子节点存放即为具体数据；
b、非主键索引（即二级主键/二级索引/非聚集索引/稀疏索引）的叶子节点存放的是主键索引，以此来保证数据的一致性，即在操作过程中，完成主键索引的操作即可，二级主键的增删操作不会影响数据的一致性；以及不需要重复存储相同的数据，节省存储空间。
c、非主键索引进行查找时，先根据二级主键定位到对应的主键（即二级主键的叶子节点），再根据主键索引查找到对应的数据（即回表操作）。