Mysql索引的数据结构

柔光的暖阳◎ 2022-03-11 09:16 261阅读 0赞

## 前言 ##

#### [博主github][github] ####

#### [博主个人博客http://blog.healerjean.com][http_blog.healerjean.com] ####

InnoDB是Mysql的默认存储引擎(Mysql5.5.5之前是MyISAM，文档)

我们知道，数据库查询是数据库的最主要功能之一。我们都希望查询数据的速度能尽可能的快，因此数据库系统的设计者会从查询算法的角度进行优化。

最基本的查询算法当然是顺序查找（linear search），这种复杂度为O(n)的算法在数据量很大时显然是糟糕的，好在计算机科学的发展提供了很多更优秀的查找算法，例如二分查找（binary search）、二叉树查找（binary tree search）等。

如果稍微分析一下会发现，每种查找算法都只能应用于特定的数据结构之上，例如二分查找要求被检索数据有序，而二叉树查找只能应用于二叉查找树上，但是数据本身的组织结构不可能完全满足各种数据结构（例如，理论上不可能同时将两列都按顺序进行组织）。

所以，在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。

## 1、前言 ##

在开始讲这一小节之前，我们先来看一下在数据库没有加索引的情况下，SQL中的where字句是如何查找目标记录的。

我们先看下左边表格第二列Col2列的数据时如何查找的，如果我要查找where Col2 = 22的记录，我们在没加索引的情况下是按顺序从第一条记录查找，由此可知需要查找5次才能找到；

如果对Col2字段加上索引后，我们假设使用\*\*最简单的二叉树（左子树小于根节点，根节点小于由子树）\*\*作为索引存储方式，再次查找where Col2 = 22的记录这次只需要查找2次就能找到目标记录，效率提高十分明显（具体二叉树怎么查找请看下文）。

![WX20190307-131555@2x][WX20190307-131555_2x]

## 1、索引的数据结构 ##

### 1.1、二叉树 ###

二叉树是一种比顺序结构更加高效地查找目标元素的结构 （先序遍历查找）

以从第一个父节点开始跟目标元素值比较，如果相等则返回当前节点，

如果目标元素小于当前节点，则移动到左侧子节点进行比较，

如果目标元素大于当前节点，则移动到右侧子节点进行比较

#### 1.1、下面是一个放入二叉树的顺序（也可以当做是查找顺序） ####

![20181010103721723][]

#### 1.2、缺点 ####

在大部分情况下，我们设计索引时都会在表中提供一个自增整形字段作为建立索引的列（比如主键），在这种场景下使用二叉树的结构会导致我们的索引总是添加到右侧，在查找记录时跟没加索引的情况是一样的，如下图所示：

\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-jJphDAWr-1587536543670)(https://raw.githubusercontent.com/HealerJean/HealerJean.github.io/master/blogImages/20181010103721723-1.gif)\]

### 1.2、红黑树（平衡二叉树） ###

#### 1.2.1、优点 ####

**红黑树也叫平衡二叉树**，它不仅继承了二叉树的放入数据的结构，但是解决了上面二叉树遇到的自增整形索引的问题，因为它会自动调整结构的位置，从而始终保证左子节点 < 父节点 < 右子节点的规则。

![20181010104201991][]

#### 1.2.2、缺点 ####

在数据量大的时候，深度也很大。

从图中可以看出每个父节点只能存在两个子节点，如果我们有很多数据，那么树的深度依然会很大，可能就会超过十几二十层以上，对我们的磁盘寻址不利，依然会花费很多时间查找。

### 1.3、Hash ###

对数据进行Hash（散列）运算，主流的Hash算法有MD5、SHA256等等，然后将哈希结果作为文件指针可以从索引文件中获得数据的文件指针，再到数据文件中获取到数据

#### 1.3.1、优点 ####

我们在查找**where Col2 = 22**的记录时只需要**对22做哈希运算**得到该索引所对应那行数据的文件指针，从而在MySQL的数据文件中定位到目标记录，查询效率非常高。

#### 1.3.1、缺点 ####

无法解决范围查询（Range）的场景，比如 select count(id) from sus\_user where id >10；因此Hash这种索引结构只能针对字段名=目标值的场景使用。

不适合模糊查询（like）的场景。

### 1.3、B-Tree ###

为了解决红黑树存在缺点，那么我们可以在红黑树的基础上（自动分类）构思一种新的储存结构。

既然觉得树的深度太长，就只需要**适当地增加每个树节点能存储的数据**个数即可，但是数据个数也必须要设定一个合理的阈值，不然一个节点数据个数过多会产生多余的消耗。**节点中的数据key从左到右递增排列**

度(Degree)-节点的数据存储个数，每个树节点中数据个数 \*\*15/16\*Degree（未验证）\*\*时会自动分裂，调整结构

#### 1.3.1、优点 ####

B-Tree的结构可以弥补红黑树的缺点，解决数据量过大时整棵树的深度过长的问题。

1、相同数量的数据只需要更少的层，  
2、相同深度的树可以存储更多的数据，查找的效率自然会更高。

![20181010110545875][]

在这里需要说明下的是，BTree的结构里每个节点包含了索引值和表记录的信息，我们可以按照Map集合这样理解：key=索引，value=表记录，如下图所示：

![20181010101451500][]

#### 1.3.2、举例 ####

\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ZS7wAqdL-1587536543673)(https://raw.githubusercontent.com/HealerJean/HealerJean.github.io/master/blogImages/15519422281134.jpg)\]

假如在该图中查找关键字47，

1、首先从根结点开始，根据根结点指针t找到\*a结点，因为47大于 \*a 结点的关键字35，所以会去A1指针指向的 \*c结点继续寻找，

2、因为 \*c的关键字 43 < 要查找的47 < \*c结点的关键字78，所以去 \*c结点A1指针指向的 \*g结点去寻找

2、结果在 \*g结点中找到了关键字47，查找成功。

#### 1.3.3、缺点 ####

从上面得知，在查询单条数据是非常快的。**但如果范围查的话，BTree结构每次都要从根节点查询一遍，效率会有所降低**

因此在实际应用中采用的是另一种BTree的变种B+Tree（B+树）。

### 1.4、 B+Tree（MySQL索引的真正存储结构） ###

#### 1.4.1、1. 为什么要对BTree继续做优化？ ####

索引和表数据在不使用的时候是存储在文件中的，也就是磁盘，当我们执行查询操作时会DBMS（数据库管理系统）首先会先从内存中查找，如果找到直接使用，如果找不到则从磁盘文件中读取；

操作系统储存数据的最小单位是页（page），一页假设是4K大小（由操作系统决定），对内存和磁盘读取数据是按一页的整数倍读取的。

![20181010101530718][]

这里我们假设数据库一次IO操作就读取1页4K的数据，再假设图中圈起来的元素就是一个大节点，内含多个小节点的索引和数据，其大小是10MB，那么我们要从磁盘中读取完整个大节点需要进行 10M / 4K = 2500次IO操作，这样就可以看出如果大节点数据总量越大，需要执行的IO操作越多，花费的时间也越长，因此为了提高性能，数据库会建议我们一个大节点只存储一页4K大小的数据，这里的数据包含了索引和表记录，另外我们还能计算出树的度Degree应该设置成多大才合理：

度是节点存储的个数
    Degree = 内存页大小（4K） / 单个索引值字节大小；

进一步分析，索引值的大小相对于整条记录的大小是很小的，如果我们需要查找的数据刚好是在最后，那么前面遍历过的节点中存储的记录数据是不是对我们来说是没用的，它会占用比索引大得多的空间，导致我们一个大节点里能遍历的索引数量大大减少，需要向下继续遍历的几率就更大，花费更多时间查找，所以需要对它进行优化

#### 1.4.2、B+tree做了哪些优化 ####

##### 1.4.2、B+tree只有叶子节点存储记录数据 #####

新的B+树结构没有在所有的节点里存储记录数据，而是只在**最下层的叶子节点**存储，上层的所有非叶子节点只存放索引信息，这样的结构可以让单个节点存放下更多索引值，**增大度Degree的值，提高命中目标记录的几率。**

这种结构会在上层非叶子节点存储一部分冗余数据，但是这样的缺点都是可以容忍的，因为冗余的都是索引数据，不会对内存造成大的负担。

![20181010101518627][]

##### 1.4.3、每个叶子节点都指向下一个叶子节点（不是根节点哦） #####

这点优化有什么用呢？我们直接看下面的B+Tree结构，如果我们进行范围查找where id > 4的记录，我们只需要先找到id = 4的记录后自然就能通过叶子节点间的双向指针方便地查询出大于4的所有记录。

![20181010101552670][]

\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Tyq4HyOu-1587536543678)(https://raw.githubusercontent.com/HealerJean/HealerJean.github.io/master/blogImages/2018101011051775.gif)\]

## 2、联合索引底层存储结构 ##

在通过索引查找记录时，会先将联合索引中第一个索引列与节点中第一个索引值进行匹配，匹配成功接着匹配第二个索引列和索引值，直到联合索引的所有索引列都匹配完；如果过程中出现某一个索引列与节点相应位置的索引值不匹配的情况，则无需再匹配节点中剩余索引列，前往下一个节点。（A B C (A不匹配那么B就不会匹配了)）

## 3、MyISAM与InnoDB两者之间区别 ##

### 3.1、MyISAM ###

#### 3.1.1、不支持事物 ####

#### 3.1.2、myisam只支持表级锁， ####

a、对MyISAM表的读操作（加读锁）  
MyISAM在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，**不会阻塞其他进程对同一表的读请求**，但会阻塞对同一表的写请求。只有当读锁释放后，才会执行其它进程的写操作。

b、对MyISAM表的写操作（加写锁）  
在执行更新操作（UPDATE、DELETE、INSERT等）前，会自动给涉及的表加写锁。会阻塞其他进程对同一表的读和写操作，只有当写锁释放后，才会执行其它进程的读写操作。

#### 3.1.3、MyISAM的索引和数据是分开的。 ####

#### 3.1.4、支持全文索引 ####

#### 3.1.3、不支持事物 ####

### 3.2、InnoDB ###

#### 3.2.1 、支持事物 ####

#### 3.2.2、支持行锁 ####

#### 3.2.3、InnoDB的数据文件本身就是索引文件 ####

#### 3.2.4、不支持全文索引 ####

![ContactAuthor][]

[github]: https://github.com/HealerJean
[http_blog.healerjean.com]: http://HealerJean.github.io
[WX20190307-131555_2x]: /images/20220311/10849f85b99b450d8a98c6fa4e062e27.png
[20181010103721723]: /images/20220311/e92214cf397540f79ca2d4fda514872b.png
[20181010104201991]: /images/20220311/6cfe4588b2a4401b8c13845834017b3b.png
[20181010110545875]: /images/20220311/a494bf7356d040c58766ae77db6dc700.png
[20181010101451500]: /images/20220311/d603bef4b7f04cb7b99615a74b1621b7.png
[20181010101530718]: /images/20220311/e0988bc85b7c474cb08a56c15e296602.png
[20181010101518627]: /images/20220311/64afdd4e97354d58bf5f6bcbd1d5bc04.png
[20181010101552670]: /images/20220311/094b05ca92d04c2d844edf066450307a.png
[ContactAuthor]: /images/20220311/5fd65bb021764870b8d3ada9fbd6db56.png