mysql数据库索引实现_mysql数据库索引实现原理

mysql数据库索引实现原理

1. B-树

在介绍索引实现之前，我们先来了解下几种树的数据结构。

二叉搜索树

二叉搜索树有以下性质

1.每个节点有一个关键字

2.左右孩子至多有一个。

3.关键字大于左孩子，小于右孩子。

正因为二叉搜索树的特性，所以这种数据结构很适合用来做搜索，效率等同于二分查找，时间复杂度为log2(n),

但是这种最原始的二叉树有一个弊端，在极端的情况下会退化成链表

所以基础二叉搜索树有很多变种，例如红黑树和AVL树，基于特定的策略避免了树退化成链表，红黑树做数据量不大的搜索还是应用很广泛的，但是要基于文件索引系统。却不合适，因为红黑树虽然搜索效率高，但是树的高度比B-树和B+树要高，需要进行的磁盘IO就多，相比之下后者优势就比较明显了。

2.B-树

一种多路搜索树，不是二叉树

我们来看一个m阶B树

它有如下几个性质

1.根节点至少有2个孩子，根节点孩子数为[2,m]。

2.除根节点外每个非叶子节点的孩子数为[m/2,m](向上取整)。

3.非叶子节点的关键字个数=指向孩子的指针树-1。

4.所有叶子节点在同一层且关键字个数为k-1, 其中 m/2 <= k <= m

5.节点之间关键字的大小，类比二叉搜索树，即关键字的值按大小排列，pi的关键字的值属于(k[i],k[i+1])开区间，例如 p2的关键字要大于17，小于35。

图片来自网络

B树相对与二叉搜索树来说，变得矮胖，这样能减少io读取的次数，其搜索效率也是log2(n)但这还不足以用来做文件索引。因为B树有可能在非叶子节点命中数据，耗费存贮。所以文件索引一般用B+树。

3.B+树

B+树建立在B-树的基础之上，更改了几条性质

1.非叶子节点的关键字和孩子指针数相同

2.pi的关键字的值属于[k[i],k[i+1]]闭区间。

3.为叶子节点添加一个指针

图片来自网络

B+树在B-树的基础上添加了叶子链表指针，方便查找相邻的数据。

所有数据只有在叶子节点才会被命中，非叶子节点只提供索引，这样非叶子节点可以存贮更多的数据，索引B+树很适合做文件索引，

4.Mysql的B+索引实现原理

mysql存贮索引的文件以页为单位，一个节点相当于一页。不同的存贮引擎，索引实现也不同，就以innodb来说，默认一个索引页为16K，mysql在查询索引时，会预先把多个页加载到内存中，所以如果索引的键很大的话，会引起索引的列表，产生很多的碎片，因为如果一页存储不下一个节点的话，就会新开另外一页。

4.1索引查找模拟

首先我们看下mysql索引能存贮多少数据，

一页为16K，

1.我们假设以int(4B)大小的自增主键,一个子节点指针为6B

2.行数据大小假设为1KB

第一层：根节点

161024/(4+6)≈1638个索引键

第二层：非叶子节点

如果第二层也只存索引键，16381638≈2683044，能存200多万个索引键。

第三层如果只存数据：一页能存16行数据

1638163816=42928704，

三次io能查询4000多万数据，这就是为什么数据库为什么采取B+索引的原因，因为能减少IO的读取次数。

InnoDB使用的是聚簇索引，即数据文件就是索引文件，这里又分主键索引和非主键索引。

主键索引：将主键映射到B+树中，而叶子节点就是行数据。

非主键索引：存贮的是主键值，再通过主键的值，去搜索主键索引获得数据。

MyISM使用的是非聚簇索引：即索引文件和数据文件分开，叶子节点仅仅存贮了数据文件的地址。这点和oracle的B树索引实现类似，都是存贮了地址。myIsm的主键索引和非主键索引没什么区别，只是存贮的键值列不同，叶子节点都是存贮的数据地址。