【MySQL】索引内部数据结构

最新推荐文章于 2025-04-14 22:26:17 发布

千g

最新推荐文章于 2025-04-14 22:26:17 发布

阅读量605

点赞数 1

分类专栏： mysql MySQL

本文链接：https://blog.youkuaiyun.com/n950814abc/article/details/91347971

版权

mysql 同时被 2 个专栏收录

15 篇文章

订阅专栏

MySQL

14 篇文章

订阅专栏

索引是什么？

几种数据结构介绍：

二叉查找树（Binary Search Tree,BST）：

红黑树：

平衡二叉查找树：

多路平衡查找树B-Tree（balanced tree）：

B+Tree 一个节点到底存多少元素合适？

mysql中B+Tree的一个节点大小为多大呢？

索引是什么？

除数据本身以外，数据库还维护着一个满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，这样就可以在这些数据结构的基础上实现高级查找算法，这种数据结构就是索引。

快速检索出数据的数据结构。

一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以文件形式存储在硬盘上。

几种数据结构介绍：

可以通过如下网址进行数据结构查看：https://www.cs.usfca.edu/~galles/visualization/Algorithms.html

现在从二叉查找树、平衡二叉树、多路平衡查找树B-Tree、B+Tree依次分析一下：

二叉查找树（Binary Search Tree,BST）：

是一棵相对平衡的有序二叉树
对其进行插入、查找、删除等操作，平均复杂度均为O(logn)
左子节点比父节点的值小，右节点的值比父节点的值大

如果组织结构不好，就会组成线性的结构，查找也不是很好

红黑树：

相对平衡

平衡二叉查找树：

它的左右两个子树的高度差的绝对值不超过1

缺点：

搜索时IO次数过多
节点数据内容较少，这样就会导致高度变高，查找的IO次数过多

例如：如果查找关键字为8的话，需要进行3次IO操作。

根据根节点找到磁盘块10，读入内存，CPU进行比较 8 < 10【磁盘进行一次I/O操作】
磁盘，5，内存，CPU 8 > 5
磁盘，8，内存，CPU 8 > 8

多路平衡查找树B-Tree（balanced tree）：

是绝对平衡的。
所有叶子节点都在同一层，且不包含其它关键字信息
每个节点中不仅包含数据的key值，还有data值。叶子节点和非叶子节点都会存储数据。而每一个页的存储空间是有限的，如果data数据较大时将会导致每个节点（即一个页）能存储的key的数量很小，当存储的数据量很大时同样会导致B-Tree的深度较大，增大查询时的磁盘I/O次数，进而影响查询效率

路=关键字个数 + 1

经常改变的数据尽量不要建索引，会经常改变索引的结构（通过左旋）

B+Tree：

非叶子节点并不保存数据相关信息，只保存关键字和子节点的引用
所有叶子节点之间都有一个链指针
数据记录都存放在叶子节点中（聚集索引的B+Tree中的叶子节点存放的是整张表的行记录数据）

优点：

提高数据检索效率，降低数据库的IO成本
通过索引列对数据进行排序，降低数据排序成本，降低了CPU的消耗缺点：

实际上索引也是一张表，该表保存了主键和索引字段，并指向实体表的记录,所以索引列也是要占用空间的；
虽然索引大大提高了查询速度，同时却会降低更新表的速度,如果对表INSERT,UPDATE和DELETE。因为更新表时，MySQL不仅要不存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息
索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立优秀的索引，或优化查询语句

路 = 关键字个数

HASH

在memory表里,默认是hash索引, hash的理论查询时间复杂度为O(1)。

既然hash的查找如此高效,为什么不都用hash索引?

原因：

hash函数计算后的结果,是随机的,如果是在磁盘上放置数据。例如主键为id为例, 那么随着id的增长, id对应的行,在磁盘上随机放置。
不能对范围查询进行优化.
无法利用前缀索引。例如在btree中, field列的值“hellopworld”,并加索引

查询 xx=helloword,自然可以利用索引, xx=hello,也可以利用索引(左前缀索引)

因为hash(‘helloword’),和hash(‘hello’),两者的关系仍为随机。

排序也无法优化。
必须回行。就是说通过索引拿到数据位置,必须回到表中取数据。

哈希表（散列表）：把Key通过一个固定的算法函数既所谓的哈希函数转换成一个整型数字，然后将该数字对数组长度进行取余，取余结果就当作数组的下标，将value存储在以该数字为下标的数组空间里。

哈希算法（散列），就是把任意长度值（Key）通过散列算法变换成固定长度的Key地址，通过这个地址进行访问的数据结构。

mysql索引数据结构：

按照索引方法划分：

B+Tree

hash

mysql中索引用的是B+Tree（多路平衡查找树），hash

MySQL B+Tree落地形式

插拔式，mysql中B+Tree索引体现形式分为：MyISAM、innodb

show VARIABLES LIKE 'datadir'; // 查询数据都保存在了哪里

innodb-聚簇索引

聚簇索引：主索引文件和数据文件为同一文件。

聚簇索引主要应用在innodb存储引擎中。

Innodb中主键索引的叶子节点的数据区域存储的是数据记录，辅助索引存储的是主键值。

1: 主键索引既存储索引值,又在叶子中存储行的数据
2: 如果没有主键, 则会Unique key做主键
3: 如果没有unique,则系统生成一个内部的rowid做主键.
4: 像innodb中,主键的索引结构中,既存储了主键值,又存储了行数据,这种结构称为”聚簇索引”

优势:

根据主键查询,不用回行(数据就在主键节点下)

如果不是主键索引，会进行二次查找。例如上面的name为索引，如果查找name为James的那一行数据，会先查找索引为James的，指向了主键索引为1的，然后根据主键索引1找到具体那一行数据。

劣势:

如果碰到不规则数据插入时,造成频繁的页分裂.

节点分裂：对于innodb，节点下存储了“行数据”，分类的时候，还要移动行数据

对于innodb的主键，尽量用整型，而且是递增的整型。如果是无规律的数据，将会产生很多的分裂。

MyISAM-非聚簇索引

非聚簇索引：非聚簇索引就是指B+Tree的叶子节点上的data，并不是数据本身，而是数据存放的地址。

主要用在MyISAM存储引擎中。

非聚簇索引比聚簇索引多了一次读取数据的IO操作，所以查找性能上会差。

myisam中, 主索引和次索引,都指向物理行(磁盘位置)，会回行。主索引和辅助索引没啥区别，只是主索引中的key一定得是唯一的。

节点分裂：myisam，节点存储的对物理行地址，内容较小，又缓存再内存里，分类速度很快

按照逻辑角度划分：

普通索引：

最基本的索引，它没有任何限制。

唯一索引：

与普通索引类似，不同的就是索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。

主键索引

它是一种特殊的唯一索引，用于唯一标识数据表中的某一条记录，不允许有空值，一般用 primary key 来约束。

联合索引（又叫复合索引）：

多个字段上建立的索引，能够加速复合查询条件的检索。

全文索引：

老版本MySQL 自带的全文索引只能用于数据库引擎为 MyISAM 的数据表，新版本 MySQL 5.6 的 InnoDB 支持全文索引。默认 MySQL 不支持中文全文检索，可以通过扩展 MySQL，添加中文全文检索或为中文内容表提供一个对应的英文索引表的方式来支持中文

索引覆盖：

定义：如果查询的列恰好是索引的一部分，查询只需要在索引文件上进行，不需要回行到磁盘再找数据。

优点：

可以优化缓存，减少磁盘IO操作
可以减少随机IO，使随机IO操作变为顺序IO操作
可以避免对Innodb主键索引的二次查询
可以避免MyISAM表进行系统调用

无法使用覆盖索引的情况

存储引擎不支持覆盖索引
查询中使用了太多的列
使用了双%号的like查询

IO原理：

局部性原理：

当一个数据被用到时，其附近的数据也通常会马上被使用。所以操作系统为了提高效率，读取数据时往往不是严格按需读取，而是每次都会预读，即使只需要一个字节，操作系统也会从这个位置开始，顺序向后读取一定长度的数据存放内存。这里的一定长度叫“页”，也就是操作系统操作磁盘时基本单位。一般操作系统中一页的大小是4Kb.