MySQL 索引分析

水深无声 2023-07-17 06:37 2阅读 0赞

### 文章目录 ###

*  MyISAM 和 InnoDB 的区别
 *  索引与数据处理
 *   *  什么是索引？
     *  检索原理
 *  MySQL 索引结构
 *   *  hash
     *  二叉树
     *   *  中序遍历
     *  平衡二叉树（AVL）
     *   *  二叉树的结构图
         *  结论
     *  B 树
     *   *  动画演示
         *  B 树的结构图
         *  底层原理
         *  缺点
         *   *  什么是磁盘块 / 页？
             *   *  磁盘块 / 页之间的关系
         *  B 树检索原理
         *   *   *  结论
     *  B+ 树
     *   *  动画演示
         *  B+ 树结构图
         *  检索原理
         *  结论
         *   *   *  从 B 树到 B+ 树
                 *  B+ 树相对于 B 树有什么不同？

# MyISAM 和 InnoDB 的区别 #

现在最常用的存储引擎是 InnoDB，它从 MySQL 5.5 版本开始成为了默认的存储引擎

--------------------

<table> 
 <thead> 
  <tr> 
   <th>对比项</th> 
   <th>MyISAM</th> 
   <th>InnoDB</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>主外键</td> 
   <td>不支持</td> 
   <td>支持</td> 
  </tr> 
  <tr> 
   <td>事务</td> 
   <td>不支持</td> 
   <td>支持</td> 
  </tr> 
  <tr> 
   <td>行表锁</td> 
   <td>表锁，即使操作一条记录也会锁住整个表，不适合高并发操作</td> 
   <td>行锁，操作时只锁住某一行，不对其它行有影响，<font>适合高并发操作</font></td> 
  </tr> 
  <tr> 
   <td>缓存</td> 
   <td>只缓存索引，不缓存真实数据</td> 
   <td>不仅缓存索引还要缓存真实数据，对内存要求较高，而且内存大小对性能有决定性影响</td> 
  </tr> 
  <tr> 
   <td>表空间</td> 
   <td>小</td> 
   <td>大</td> 
  </tr> 
  <tr> 
   <td>关注点</td> 
   <td>性能</td> 
   <td>事务</td> 
  </tr> 
  <tr> 
   <td>默认安装</td> 
   <td>是</td> 
   <td>是</td> 
  </tr> 
 </tbody> 
</table>

**小结：** MyISAM 适合读多写少，InnoDB 适合写多读少

# 索引与数据处理 #

## 什么是索引？ ##

MySQL 官方对索引的定义为：索引（Index）是帮助 MySQL 高效获取数据的数据结构，可以得到索引的本质：索引是数据结构

也可以简单地理解为索引是 排好序的快速查找 B+ 树数据结构

B+ 树中的 B 代表的是平衡（balance）而不是二叉（binary）

## 检索原理 ##

在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。下图是一种可能的索引方式：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1dvb19ob21l_size_16_color_FFFFFF_t_70]

左边的是数据表，一共有两列 7 条记录，最左边的是数据记录的物理地址

--------------------

画得不是很好，不要介意哈。为了加快 Column 2 的查找，可以维护一个左边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录的物理地址的指针，这样就可以运用二叉查找在一定的复杂度内获取到相应的数据，从而快速的检索出符合条件的记录

# MySQL 索引结构 #

## hash ##

加速查找速度的的数据结构，常见的有两类：

*  哈希，例如 HashMap，查询 / 插入 / 修改 / 删除的平均时间复杂度都是 O(1)
 *  树，例如平衡二叉树搜索树，查询 / 插入 / 修改 / 删除的平均时间复杂度都是 O(log2(n))

可以发现，不管是读请求，还是写请求，哈希类型的索引，都要比树型的索引更快一些，那为什么索引结构要设计成树型呢？

举个例子：假如现在有 16 条数据

如果是使用哈希的话，时间复杂度就是 O(16) = 16，也就是说要查找 16 次才能找到  
如果是使用树的话，时间复杂度就是 O(log16) = 4，也就是说查找 4 次就能找到

想想范围 / 排序等其它 SQL 条件  
哈希型的索引，时间复杂度会退化为 O(n) 而树型的 “有序” 特性，依然能够保持 O(log2(n)) 的高效率

所以这里总结一下：

InnoDB 并不支持哈希索引

## 二叉树 ##

上面说过哈希（hash）比树（tree）更快，那为什么索引结构要设计成树型呢？

**二叉树的检索原理**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1dvb19ob21l_size_16_color_FFFFFF_t_70 1]  
问题：

*  第一种情况  
    如果 id 的值是持续自增的话，会是什么样的结构？  
    会出现树的左倾或者右倾  
    会从树转换为链表，复杂度从 O(log2(n)) 变为 O(n)  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1dvb19ob21l_size_16_color_FFFFFF_t_70 2]
 *  第二种情况  
    又是树又是链表  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1dvb19ob21l_size_16_color_FFFFFF_t_70 3]

### 中序遍历 ###

中序遍历首先遍历左子树，然后访问根节点，最后遍历右子树。若二叉树为空则结束返回，否则：

*  （1）中序遍历左子树
 *  （2）访问根节点
 *  （3）中序遍历右子树

如下图所示的二叉树，中序遍历结果为：D B E A F C  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1dvb19ob21l_size_16_color_FFFFFF_t_70 4]

## 平衡二叉树（AVL） ##

### 二叉树的结构图 ###

从算法的数学逻辑来讲，二叉树的查找速度和比较次数都是最小的，看下下面这个动画

![在这里插入图片描述][20200320141352582.gif_pic_center]  
图中有 10 个元素，假如要查找的元素是 10，那么就需要 4 步

### 结论 ###

但是当插入的数据越来越多的时候，树的高度也会增加，树的高度越高，那么查找的次数就会越多，查找次数越多，那么磁盘 IO 数的量就会越大，导致系统随着数据量的加大，从而系统性能急剧下降

## B 树 ##

### 动画演示 ###

![在这里插入图片描述][20200704203948754.gif]

### B 树的结构图 ###

![在这里插入图片描述][2020032014332025.png]  
同样的元素，不一样的数据结构，这个数据结构的查找 10 只需 3 步

### 底层原理 ###

*  数据库的索引是存储在磁盘上的，如果数据很大，必须导致索引的大小也会变大，超多几个 G（比如一本书的页数很多，那么目录相对也会变多）
 *  当我们利用索引查询的时候，是不可能将全部几个 G 的索引都加载进内存的，我们能做的只能是：  
    逐一加载每一个磁盘页，因为磁盘页对应着索引树的节点

### 缺点 ###

从 B 树结构 图中看到每个节点中不仅包含数据的 key 值，还有 data 值。而每一个页的存储空间是有限的，如果 data 数据较大时将会导致每个节点（即一个页）能存储的 key 的数量很小，当存储的数据量越大时同样会导致 B 树的深度较大，增大查询时的磁盘 I/O 次数进而影响查询效率

#### 什么是磁盘块 / 页？ ####

比如我们连接 MySQL 之后输入以下 SQL 语句执行

SHOW GLOBAL STATUS LIKE 'Innodb_page_size'

输出：  
![在这里插入图片描述][20200320145001620.png]  
Innodb\_page\_size 俗称磁盘页大小（就跟翻书的一页一页一样），从输出可以发现 Innodb\_page\_size 大小默认是 16KB

##### 磁盘块 / 页之间的关系 #####

系统从磁盘读取数据到内存时是以 磁盘块（block）为基本单位的，位于同一个磁盘块中的数据会呗一次性读取出来，而不是需要什么取什么

InnoDB 存储引擎中有页（Page）的概念，页是其磁盘管理的最小单位

系统的一个磁盘块的存储空间往往没有这么大，因此 InnoDB 每次申请磁盘空间时都会是 若干个地址连接磁盘块来达到页的大小 16 KB，InnoDB 在把磁盘数据读入到磁盘时会 以页为基本单位，在查询数据的时候如果一个页中的每条数据都能有助于定位数据记录的位置，这将会减少磁盘 I/O 次数，提高查询效率

### B 树检索原理 ###

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1dvb19ob21l_size_16_color_FFFFFF_t_70 5]  
每个节点占用一个盘块的磁盘空间，一个节点上有两个升序排序的关键字和三个指向子树根节点的指针，指针存储的是子节点所在磁盘块的地址

--------------------

模拟查找关键字 29 的过程：  
根据根节点找到磁盘块 1，读入内存【磁盘 I/O 操作第 1 次】  
比较关键字 29 在区间（17,35）之间，找到磁盘块 1 的指针 p2  
根据 p2 指针找到磁盘块 3，读入内存【磁盘 I/O 操作第 2 次】  
比较关键字 29 在区间（26,30）之间，找到磁盘块 3 的指针 p2  
根据 p2 指针找到磁盘块 8，读入内存【磁盘 I/O 操作第 3 次】  
在磁盘块 8 中的关键字列表中找到关键字 29

--------------------

根据上面的分析过程，发现需要 3 次磁盘 I/O 操作，和 3 次内存查找操作。由于内存中的关键字是一个有序表结构，可以利用二分法查找提高效率。而 3 次磁盘 I/O 操作是影响整个 BTree 查找效率的决定因素。BTree 相对于 AVLTree 缩减了节点个数，使每次磁盘 I/O 取到内存的数据都发挥了作用，从而提高了查询效率

##### 结论 #####

B 树比平衡二叉树减少了一次 IO 操作

## B+ 树 ##

### 动画演示 ###

![在这里插入图片描述][2020070420451388.gif]

### B+ 树结构图 ###

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1dvb19ob21l_size_16_color_FFFFFF_t_70 6]  
从图中可以看出所有 data 信息都移动到 叶子节点中，而且子节点和子节点之间会有个指针指向，这个也是 B+ 树的核心点，这样可以大大提升 范围查询效率，也方便遍历整棵树

（1） 非叶子节点不再存储数据，数据只存储在同一层的叶子节点上  
（2）叶子之间，增加了 链表 ，获取所有节点，不再需要 中序遍历（上述有讲到过）

### 检索原理 ###

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1dvb19ob21l_size_16_color_FFFFFF_t_70 7]  
在 B+ 树中，所有数据记录节点都是按照键值大小顺序存放在 同一层的叶子节点上，而非叶子节点上只存储 key 值信息，这样可以大大加大每个节点存储的 key 是数量，降低 B+ 树的高度

1、InnoDB 存储引擎的最小存储单元是页，页可以用于存储数据也可以用于存储键值 + 指针，在 B+ 树中叶子节点存放数据，非叶子节点存放键值 + 指针

2、索引组织表通过非叶子节点的二分查找法以及指针确定数据在哪个页中，首先找到根页进而再去数据页中查找到需要的数据

B+ 树算法：通过继承了 B 树的特征，B+ 树相比 B 树，新增叶子节点  
与非叶子节点关系

叶子节点中包含了键值和数据

非叶子节点中只是包含键值和子节点引用，不包含数据

通过非叶子节点查询叶子节点获取对应的数据，所有相邻的叶子节点包含非叶子节点使用 链表进行结合，叶子节点是顺序排序并且相邻节点有顺序引用的关系

### 结论 ###

##### 从 B 树到 B+ 树 #####

B+ 树是在 B 树基础上的一种优化使其更适合实现外存储索引结构，InnoDB 存储引擎就是使用 B+ 树实现其索引结构

##### B+ 树相对于 B 树有什么不同？ #####

*  非叶子节点只存储键值信息
 *  所有叶子节点之间都有一个链指针
 *  数据记录都存放在叶子节点中

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1dvb19ob21l_size_16_color_FFFFFF_t_70]: /images/20230528/5978145bb3124b8f8b192c1cca697828.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1dvb19ob21l_size_16_color_FFFFFF_t_70 1]: /images/20230528/ab110787d7444cff97eee53a0c128d92.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1dvb19ob21l_size_16_color_FFFFFF_t_70 2]: /images/20230528/c39ca040fa484ed59fb3d376e1d7fb83.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1dvb19ob21l_size_16_color_FFFFFF_t_70 3]: /images/20230528/713c8ca95785476ba570195307309fe8.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1dvb19ob21l_size_16_color_FFFFFF_t_70 4]: /images/20230528/dcea75a39fce4133b00cdd49bb97a39b.png
[20200320141352582.gif_pic_center]: /images/20230528/9d0986a53939483e98db1102d70cf8e8.png
[20200704203948754.gif]: /images/20230528/d7af3e9975b4457785a4dc42d79e0a49.png
[2020032014332025.png]: /images/20230528/1dd32d33a97e47ec962d36190d6b1974.png
[20200320145001620.png]: /images/20230528/fcbabb739dd04925baec74a427c27462.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1dvb19ob21l_size_16_color_FFFFFF_t_70 5]: /images/20230528/ab90a3cdd30b4f20a454a1362653734d.png
[2020070420451388.gif]: /images/20230528/93623888c2314c6cb524748e1573a0a0.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1dvb19ob21l_size_16_color_FFFFFF_t_70 6]: /images/20230528/634fcfb5f07e4b70bb4ae038b813f9b2.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1dvb19ob21l_size_16_color_FFFFFF_t_70 7]: /images/20230528/453cd90dface4216a7a868c7db5100b6.png