MySQL聚簇索引和非聚簇索引的区别

柔情只为你懂 2022-08-29 00:25 240阅读 0赞

### 目录 ###

*   *  （1）聚簇索引
     *  （2）辅助索引（非聚簇索引）
     *  （3）InnoDB索引的实现
     *   *  （3.1）主键索引
         *  （3.2）InnoDB的辅助索引
     *  （4）MyISAM索引的实现
     *   *  （4.1）主键索引
         *  （4.2）辅助索引（Secondary key）
     *  （5）总结

## （1）聚簇索引 ##

聚簇索引就是按照每张表的 **主键** 构造一棵B+树，**同时叶子节点中存放的就是整张表的行记录数据**，也将聚集索引的叶子节点称为数据页。这个特性决定了索引组织表中数据也是索引的一部分，每张表只能拥有一个聚簇索引。

**在 InnoDB 中，只有主键索引是聚簇索引，如果没有主键，则挑选一个唯一键建立聚簇索引。如果没有唯一键，则MySQL自动为InnoDB表生成一个隐含字段来建立聚簇索引，这个字段长度为6个字节，类型为长整形。**

聚簇索引的优缺点  
优点：

1.  数据访问更快，因为聚簇索引将**索引和数据**保存在同一个B+树中，因此从聚簇索引中获取数据比非聚簇索引更快
2.  聚簇索引对于主键的排序查找和范围查找速度非常快

缺点：

1.  插入速度严重依赖于插入顺序，按照主键的**顺序插入**是最快的方式，否则将会出现页分裂，严重影响性能。因此，对于InnoDB表，我们一般都会定义**一个自增的ID列为主键**
2.  更新主键的代价很高，因为将会导致被更新的行移动。因此，对于InnoDB表，我们一般定义主键为不可更新。
3.  二级索引访问需要两次索引查找，第一次找到主键值，第二次根据主键值找到行数据。

## （2）辅助索引（非聚簇索引） ##

**在聚簇索引之上创建的索引称之为辅助索引**，辅助索引访问数据总是需要 **二次查找**。辅助索引叶子节点存储的不再是行的物理位置，而是**主键值**。通过辅助索引首先找到的是主键值，再通过主键值找到数据行的数据页，再通过数据页中的Page Directory找到数据行。

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

## （3）InnoDB索引的实现 ##

### （3.1）主键索引 ###

**MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址**。而在**InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构**，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTM2NjQ5OQ_size_16_color_FFFFFF_t_70 1]  
　　(图inndb主键索引）是InnoDB主索引（同时也是数据文件）的示意图，可以看到叶节点包含了完整的数据记录。这种索引叫做聚集索引。因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。

### （3.2）InnoDB的辅助索引 ###

InnoDB的所有辅助索引都引用主键作为data域，**辅助索引的data域存储相应记录主键的值而不是地址**，这也是和MyISAM不同的地方。例如，下图为定义在Col3上的一个辅助索引：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTM2NjQ5OQ_size_16_color_FFFFFF_t_70 2]  
　　InnoDB 表是基于聚簇索引建立的。因此InnoDB 的索引能提供一种非常快速的主键查找性能。不过，它的辅助索引（Secondary Index， 也就是非主键索引）也会包含主键列，所以，**如果主键定义的比较大，其他索引也将很大**。如果想在表上定义 、很多索引，则争取尽量把主键定义得小一些。InnoDB 不会压缩索引。

## （4）MyISAM索引的实现 ##

**MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址**

### （4.1）主键索引 ###

MyISAM引擎使用B+Tree作为索引结构，**叶节点的data域存放的是数据记录的地址**。下图是MyISAM主键索引的原理图：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTM2NjQ5OQ_size_16_color_FFFFFF_t_70 3]  
这里设表一共有三列，假设我们以Col1为主键，图myisam1是一个MyISAM表的主索引（Primary key）示意。可以看出MyISAM的索引文件仅仅保存数据记录的地址。

### （4.2）辅助索引（Secondary key） ###

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

**MyISM使用的是非聚簇索引**，非聚簇索引的两棵B+树看上去没什么不同，节点的结构完全一致只是存储的内容不同而已，主键索引B+树的节点存储了主键，辅助键索引B+树存储了辅助键。表数据存储在独立的地方，这两颗B+树的叶子节点都使用一个地址指向真正的表数据，对于表数据来说，这两个键没有任何差别。由于索引树是独立的，通过辅助键检索无需访问主键的索引树。

为了更形象说明这两种索引的区别，我们假想一个表如下图存储了4行数据。其中Id作为主索引，Name作为辅助索引。图示清晰的显示了聚簇索引和非聚簇索引的差异。

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTM2NjQ5OQ_size_16_color_FFFFFF_t_70 5]

## （5）总结 ##

**MySQL聚簇索引和非聚簇索引的区别：**

*  **聚簇索引的叶子节点存放的是主键值和数据行，支持覆盖索引**
 *  **非聚簇索引的叶子节点存放的是主键值或数据记录的地址（InnoDB辅助索引的data域存储相应记录主键的值，MyISAM辅助索引的data域保存数据记录的地址）**

**MyISAM和InnoDB实现B树索引方式的区别是什么？**

*  **InnoDB 主键索引使用的是聚簇索引，MyISAM 不管是主键索引，还是二级索引使用的都是非聚簇索引。**

<table> 
 <thead> 
  <tr> 
   <th></th> 
   <th>InnoDB</th> 
   <th>MyISAM</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>主键索引</td> 
   <td>聚簇索引，data域保存主键值和数据行</td> 
   <td>非聚簇索引，data域保存数据记录的地址</td> 
  </tr> 
  <tr> 
   <td>辅助索引</td> 
   <td>非聚簇索引，data域保存相应记录主键的值</td> 
   <td>非聚簇索引，data域保存数据记录的地址</td> 
  </tr> 
 </tbody> 
</table>

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

以上内容仅供参考学习，如有侵权请联系我删除！  
如果这篇文章对您有帮助，左下角的大拇指就是对博主最大的鼓励。  
您的鼓励就是博主最大的动力！

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTM2NjQ5OQ_size_16_color_FFFFFF_t_70]: /images/20220829/62298e4bae1d464d9637cc142cf7d3c5.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTM2NjQ5OQ_size_16_color_FFFFFF_t_70 1]: /images/20220829/10a56f7c4dee4c92a16bfd96be28a3cc.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTM2NjQ5OQ_size_16_color_FFFFFF_t_70 2]: /images/20220829/72e5e764482b4e90ba8ca329552924b9.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTM2NjQ5OQ_size_16_color_FFFFFF_t_70 3]: /images/20220829/a25e3d580f9c45ae9001742b1e967c7f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTM2NjQ5OQ_size_16_color_FFFFFF_t_70 4]: /images/20220829/295ccf7c019a4967957024bb9985d078.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTM2NjQ5OQ_size_16_color_FFFFFF_t_70 5]: /images/20220829/6d7a3f3d38924273b0470c8eb0fda5ec.png