mysql索引的实现和原理

超、凢脫俗 2023-07-10 08:12 21阅读 0赞

## 一、定义 ##

索引是为了加速对表中的数据行的检索而创造的一种分散存储的数据结构

## 二、索引实现 ##

mysql的索引是由存储引擎来实现，不同的存储引擎实现方式不同。这里我们只分析2种主流的引擎MyISAM（非聚集索引）和InnoDB（聚集索引）。  
1、B+树中的B不是代表的二叉（Binary） ，而是代表平衡（Balance），因为B+树是从最早的平衡二叉树演化而来，但是B+树不是一个二叉树。

2、B+树是为磁盘或其他直接存取辅助设备设计的一种平衡查找树，在B+树中，所有的记录节点都是按照键值大小顺序存在同一层的叶子节点，由叶子节点指针进行相连。

3、B+树在数据库中的特点就是高扇出，因此在数据库中B+树的高度一般都在2~4层，这也就是说查找一个值的记录时，最多只需要2到4次IO,当前的机械硬盘每秒至少可以有100次IO,2-4次IO意味着查询时间只需要0.02到0.04秒。

4、B+树索引并不能找到一个给定键值的具体行，B+树索引能找到的只是被查找的键值所在行的页，然后数据库把页读到内存，再内存中进行查找，最后找到要查找的数据。

5、数据库中B+树索引可以分为，聚集索引和非聚集索引，但是不管是聚集索引还是非聚集索引，其内部都是B+树实现的，即高度是平衡的，叶子节点存放着所有的数据，聚集索引和非聚集索引不同的是，叶子节点是否存储的是一整行信息。每张表只能有一个聚集索引。

6、B+树的每个数据页（叶子节点）是通过一个双向链表进行链接，数据页上的数据的顺序是按照主键顺序存储的。

## 三、索引分类 ##

1.  索引按照索引列是否是主键，分为**主键索引和辅助索引**（除主键列索引外，其他列的索引都是辅助索引）
2.  辅助索引又可分为**唯一索引，普通索引，全文索引**

**主键索引**：即主索引，根据主键建立索引，不允许重复，不允许空值；  
**唯一索引**：用来建立索引的列的值必须是唯一的，允许空值；  
**普通索引**：用表中的普通列构建的索引，没有任何限制；  
**全文索引**：用大文本对象的列构建的索引，在MySQL5.6以下，只有MyISAM表支持全文检索。在MySQL5.6以上Innodb引擎表也提供支持全文检索；

## 四、MyISAM引擎和InnoDB引擎的区别 ##

### MyISAM 引擎 ###

MyISAM 引擎使用 B+Tree 作为索引结构,叶节点的 **data 域存放的是数据记录的地址**。

1.  **主键索引：**

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTUwNzMyNA_size_16_color_FFFFFF_t_70]  
这里设表一共有三列,假设我们以 Col1 为主键,则图 8 是一个 MyISAM 表的主索引(Primary key)示意。可以看出 MyISAM 的索引文件仅仅保存数据记录的地址。

1.  **辅助索引：**  
    在 MyISAM 中,主索引和辅助索引(Secondary key)在结构上没有任何区别,只是主索引要求 key 是唯一的,而辅助索引的 key 可以重复。如果我们在 Col2 上建立一个辅助索引,则此索引的结构如下图所示  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTUwNzMyNA_size_16_color_FFFFFF_t_70 1]  
    同样也是一颗 B+Tree,data 域保存数据记录的地址。因此,MyISAM 中索引检索的算法为首先按照 B+Tree 搜索算法搜索索引,如果指定的 Key 存在,则取出其data 域的值,然后以 data 域的值为地址,读取相应数据记录。

### InnoDB引擎 ###

虽然 InnoDB 也使用 B+Tree 作为索引结构,但具体实现方式却与 MyISAM 截然不同。  
**主键索引：**

1.  InnoDB存储引擎中表数据文件本身就是按 B+Tree 组织的一个索引结构。这棵树的叶点data 域保存了完整的数据记录。这个索引的 key 是数据表的 **主键**

> 从上文知道,MyISAM 索引文件和数据文件是分离的,索引文件仅保存数据记录的地址。

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTUwNzMyNA_size_16_color_FFFFFF_t_70 2]  
上图是 InnoDB 主索引(同时也是数据文件)的示意图,可以看到叶节点包含了完整的数据记录。**这种索引叫做聚集索引**。因为 InnoDB 的数据文件本身要按主键聚集。

> 在 InnoDB 上采用自增字段做表的主键。因为 InnoDB  
> 数据文件本身是一棵B+Tree,非单调的主键会造成在插入新记录时数据文件为了维持 B+Tree  
> 的特性而频繁的分裂调整,十分低效,而使用自增字段作为主键则是一个很好的选择。如果表使用自增主键,那么每次插入新的记录,记录就会顺序添加到当前索引节点的后续位置,当一页写满,就会自动开辟一个新的页。如下图所示:

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTUwNzMyNA_size_16_color_FFFFFF_t_70 3]

> 这样就会形成一个紧凑的索引结构,近似顺序填满。由于每次插入时也不需要移动已有数据,因此效率很高,也不会增加很多开销在维护索引上。聚集索引这种实现方式使得按主键的搜索十分高效

**辅助索引**  
与 MyISAM 索引的不同是 InnoDB 的辅助索引 **data 域存储相应记录主键的值而不是地址**。换句话说,InnoDB 的所有辅助索引都引用主键作为 data 域。  
例如,图 11 为定义在 Col3 上的一个辅助索引:  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTUwNzMyNA_size_16_color_FFFFFF_t_70 4]  
如果使用辅助索引作为搜索条件那么需要检索两遍索引:首先检索辅助索引获得主键,然后用主键到主索引中检索获得记录。

> 即： 找到对应的叶子节点，获取主键值，然后再通过聚簇索引中的B+树检索到对应的叶子节点，然后获取整行数据。这个过程叫做**回表**。回表就是先通过辅助索引扫描出数据所在的行，再通过行主键id取出索引中未提供的数据，即基于非主键索引的查询需要多扫描一棵索引树。

> 不建议使用过长的字段作为主键： 因为所有辅助索引都引用主索引,过长的主索引会令辅助索引变得过大。

> InnoDB 要求表必须有主键(MyISAM 可以没有),如果没有显式指定,则  
> MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键,如果不存在这种列,则MySQL 自动为 InnoDB  
> 表生成一个隐含字段作为主键,类型为长整形。

### 聚簇索引与非聚簇索引 ###

1.  InnoDB 使用的是聚簇索引, 将主键组织到一棵 B+树中, 而行数据就储存在叶子节点上, 若使用"where id = 14"这样的条件查找主键, 则按照 B+树的检索算法即可查找到对应的叶节点, 之后获得行数据。 若对 Name 列进行条件搜索, 则需要两个步骤:  
    第一步在辅助索引 B+树中检索 Name, 到达其叶子节点获取对应的主键。  
    第二步使用主键在主索引 B+树种再执行一次 B+树检索操作, 最终到达叶子节点即可获取整行数据。
2.  MyISM 使用的是非聚簇索引, 非聚簇索引的两棵 B+树看上去没什么不同, 节点  
    的结构完全一致只是存储的内容不同而已, 主键索引 B+树的节点存储了主键, 辅助键索引B+树存储了辅助键。 表数据存储在独立的地方, 这两颗 B+树的叶子节点都使用一个地址指向真正的表数据, 对于表数据来说, 这两个键没有任何差别。 由于索引树是独立的, 通过辅助键检索无需访问主键的索引树。
3.  为了更形象说明这两种索引的区别, 我们假想一个表如下图存储了 4 行数据。 其中Id 作为主索引, Name 作为辅助索引。 图示清晰的显示了聚簇索引和非聚簇索引的差异  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTUwNzMyNA_size_16_color_FFFFFF_t_70 5]

## 五、联合索引及最左原则 ##

参考资料：  
[参考连接1][1]  
[参考链接2][2]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTUwNzMyNA_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200301003054338.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTUwNzMyNA==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTUwNzMyNA_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20200301003212260.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTUwNzMyNA==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTUwNzMyNA_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20200301003532290.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTUwNzMyNA==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTUwNzMyNA_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/20200301003639331.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTUwNzMyNA==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTUwNzMyNA_size_16_color_FFFFFF_t_70 4]: https://img-blog.csdnimg.cn/20200301003717177.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTUwNzMyNA==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTUwNzMyNA_size_16_color_FFFFFF_t_70 5]: https://img-blog.csdnimg.cn/20200301004033748.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTUwNzMyNA==,size_16,color_FFFFFF,t_70
[1]: https://blog.csdn.net/u013308490/article/details/83001060
[2]: https://segmentfault.com/a/1190000019384980?utm_source=tag-newest