MySQL 中的Innodb 聚簇索引和Myisam 的非聚簇索引

水深无声 2022-05-13 21:08 185阅读 0赞

簇（cù）：数据存储在硬盘的时候，是以簇为单位。所以无论文件大小是多少，除非正好是簇大小的倍数，否则文件所占用的最后一个簇或多或少都会产生一些剩余的空间。而且这些空间不能给其他文件使用，即使这个文件只有0字节，也不允许两个文件或两个以上的文件共用一个簇，不然会造成数据混乱。【百度百科】

在刚开始接触到聚簇索引和非聚簇索引的时候，直观理解成聚簇索引就是在索引在一起，非聚簇索引就是索引不在一起。这个理解不痛不痒。

随着研究深入，发现Mysql 中的索引现在是使用的B+树结构存储，而对于不同的存储引擎，Innodb 采用的是聚簇索引，Myisam 使用的是非聚簇索引。

### **聚簇索引与非聚簇索引的区别** ###

想象一个场景，图书馆的藏书都是相同专业的书籍放在相近的区域，你在去图书馆借计算机专业书的时候，首先会根据书名查询所在区域，比如查询出A区3号书架2排，你按照这个方向查找过去，在这个地方就能找到这本书，而且你会发现，整个3号书架都是计算机专业的书籍，整个2排可能都是同一个书名的不同版本，不能出版社的书籍。这种你要找的书籍和给到你的索引是同一个时，就是聚簇索引。

再假想一个场景，你去医院找某个医生，可能先看到的科室分布的地图，你查询到科室所在区域是B座2楼1单元，你按照这个地址找过去的时候，那具体的医生在哪个房间，科室的前台可能再会告诉你具体的医生在哪个房间，你再去找对应的房间（可能这个房间不在这个区域）。这种你要找的医生和给到你的索引不是同一个地方，但是在这个地方又给你提供了房间，这就是非聚簇索引。

在《数据库原理》一书中对聚簇索引和非聚簇索引的区别解释是：

聚簇索引的叶子节点就是数据节点

非聚簇索引的叶子节点仍然是索引节点，只不过有指向对应数据块的指针。

### 索引结构 ###

**Innodb中聚簇索引的结构**

索引的叶子节点也储存了数据

![20190411213810428.png][]

**聚簇索引的优点**

> 1.  聚簇索引将索引和数据行保存在同一个B-Tree中，查询通过聚簇索引可以直接获取数据，相比非聚簇索引需要第二次查询（非覆盖索引的情况下）效率要高。
> 2.  聚簇索引对于范围查询的效率很高，因为其数据是按照大小排列的

**聚簇索引的缺点**

> 1.  聚簇索引的更新代价比较高，如果更新了行的聚簇索引列，就需要将数据移动到相应的位置。这可能因为要插入的页已满而导致“页分裂”。
> 2.  插入速度严重依赖于插入顺序，按照主键进行插入的速度是加载数据到Innodb中的最快方式。如果不是按照主键插入，最好在加载完成后使用`OPTIMIZE TABLE`命令重新组织一下表。
> 3.  聚簇索引在插入新行和更新主键时，可能导致“页分裂”问题。
> 4.  聚簇索引可能导致全表扫描速度变慢，因为可能需要加载物理上相隔较远的页到内存中（需要耗时的磁盘寻道操作）。

### Myisam 非聚簇索引的结构 ###

在Myisam 中，一个数据表table，它是由 **table.frm、table.myd 以及table.myi **组成。table.myd记录了数据，table.myi记录了索引的数据。在用到索引时，先到table.myi（索引树）中进行查找，取到数据所在table.myd的行位置，拿到数据。所以myisam引擎的索引文件和数据文件是独立分开的，这就是非聚簇索引。

### 总结 ###

INNODB和MYISAM的主键索引与二级索引的对比：

![20190411214554396.jpg][]

可以看到Myisam 的主键索引和二级索引几乎没有区别，它们的叶子节点都没有存储数据，都是存储的对数据行的指针。

Innodb 的主键索引的叶子节点存储的就是数据，二级索引存储的key+主键值，二级索引首先找到的是主键，然后再通过主键找到数据。

**参考：**

[Mysql索引PRIMARY、NORMAL、UNIQUE、FULLTEXT 区别和使用场合][Mysql_PRIMARY_NORMAL_UNIQUE_FULLTEXT]

[基于索引的SQL语句优化之降龙十八掌][SQL]（推荐）

[数据库索引原理及优化][Link 1]（全）

[MYSQL索引：对聚簇索引和非聚簇索引的认识][MYSQL]（区分Innodb和Myisam）

[聚簇索引与非聚簇索引（也叫二级索引）][Link 2]

[MySQL中Innodb的聚簇索引和非聚簇索引][MySQL_Innodb]

[20190411213810428.png]: /images/20220514/1de2524fddf04617b9e9fd0abeda07d7.png
[20190411214554396.jpg]: /images/20220514/eafea0571fd14a3cb4f20c7df7a1e6ba.png
[Mysql_PRIMARY_NORMAL_UNIQUE_FULLTEXT]: https://blog.csdn.net/u010177899/article/details/68061493
[SQL]: https://blog.csdn.net/lovelion/article/details/8462814
[Link 1]: https://blog.csdn.net/suifeng3051/article/details/52669644
[MYSQL]: https://blog.csdn.net/alexdamiao/article/details/51934917
[Link 2]: https://www.jianshu.com/p/fa8192853184
[MySQL_Innodb]: https://blog.csdn.net/u013132035/article/details/82193763