MySql索引剖析

MySql索引剖析

1.最近朋友问我一个问题，一时把我给问懵了。MySql 索引的本质是什么？

• 我当时是以我自己对索引的理解回答：类似于书中的目录，可以快速查询到指定的数据，主要用于加速查询速度。
  后面得知这个答案只能给 30分，所以我也特地去查询资料深入的学习了一番。

2.数据库索引的本质：分散存储的数据结构

• 
• 在RDBMS 系统中数据索引都是硬盘级索引
  *下图是索引的工作机制：
  
  3.MySQL中索引类型：
  （1）.hash索引：
• 优势： 查询速度快
• 劣势：不支持范围查询，hash冲突。
  由于innodb 中手动创建索引不支持hash索引。所以手动创建只能利用树形结构（二叉树）。

首先最简单的二叉搜索树如下：

它通过二分查找的方式大大减少需要检索的数据体量，从而加速查询速度。

当然有特殊情况：

当出现这种二叉树（例如通过自增id创建的索引），就会导致创建索引没有提升查询速度效果。

这个时候就用到平衡二叉搜索树来解决这个问题：

上图就是一个平衡二叉树，下面来简述以下它的查询过程：
eg: 需要查询 关键字 8
（1）.首先将二叉树最顶端关键字10存入缓存与8进行对比，发现8比10小。
（2）.然后就会通过p1 传导到 关键字5，将关键字5存入缓存再与8进行对比，发现8比5大。
（3）.然后就会通过p2 传导到 8关键字，与8进行匹对。匹对成功，将通过数据区的地址找到数据，最终返回。

但是这样会有很明显的缺陷，如果数据量很多，就会导致树过深，每次查询匹对都会进行IO操作，导致很浪费性能。
不仅如此，这种查询方式，每次进行IO操作的时候，都只进行一个关键字的缓存，造成内存浪费。（没有利用好操作系统跟磁盘的交互特性）。

此时就要利用 多路平衡树（B-树）来优化这个问题。

查询过程：
将查询分为5个区间：
(1). 无穷小 — 17
(2). 17
(3). 17------35
(4). 35
(5). 35 ---- 无穷大
其余查询机制同平衡二叉树。

下面做一个分析：
假设 一次IO交互可以存储 4KB的数据，而1个整型数 占用空间为 4B，再见上数据内容占用空间 假设也为 4B.那么每一次IO交互就能进行：
4KB = 4096 B
4096 / 8 = 500 +
500 + 关键字的匹对。这样很大程度的优化了这个查询性能。

思考：为什么不要在常变的列上建立索引？
因为为了维护二叉树的绝对平衡，会对节点进行不断的合并分裂旋转修改数据结构，导致性能损耗更大。

而MySql 中使用的是 B-树的加强版 ： B+树（加强版多路平衡二叉树）。

查询匹对规则：采用左闭合区间
上图分为以下几个匹对区间
(1). 1<= x < 28
(2). 28<= x < 66
(3). 68<= x
MySql定制的B+树中，根节点和支节点没有数据区，叶子节点才有数据区，这种好处就是：
(1).每次IO操作可以进行更多的数据匹对(释放了数据区的空间用于关键字缓存)。
(2).基于索引结构库，扫库扫表能力强于B-树
(3).B+树最底层的数据是首尾相连的双向链表，范围查询，由于数据天然有序，所以强于B-树。
(4).由于B+树的结构，导致B+树每次查询IO次数都是固定的，而B-树每次查询进行IO操作次数是不固定的，所以相对B-树 B+树 性能更加稳定。

这就是MySql 中使用的索引数据结构。 B+树（B+Tree）

*扩展：MySql 2大主流引擎实现 B+tree
每次创建一张表都会生成以下几个文件（5.7版本以前）

(1)…frm 文件— 表骨架 （5.7以前才有，8.0移除）
(2)…innodb 文件为 innodb 引擎 存储文件
(3). .myisam.MYD文件 为 myisam 引擎 存储数据文件
(3). .myisam.MYI 文件为 myisam 引擎 存储索引文件

myisam:如下
当多索引时：

两个索引没有主次之分，搜寻次级都是同等级。

innodb:
只有1个文件，索引和数据都存储在 这个文件（innodb.ibd）

什么是聚集索引？

• 主键索引就是聚焦索引， 只有主键索引，最末尾保存的数据，才是所有数据。

innodb中多索引：

思考：为什么辅助索引最底层不直接存储数据的地址，而存储的是主键索引，再回表进行查询？
正是因为 完全平衡二叉树的特性，当数据量发生变化时，为了保持平衡，会对节点进行合并分裂旋转，所以会导致，地址变更，所以不能直接存储地址。

但是这样的话，每次都需要回表查询，非常麻烦，所以MySql 底层进行了优化， 叫做 自适应hash索引。

简单理解为 索引的索引，非常的巧妙。