《MySQL中级系列教程-第六章-聚集索引和非聚集索引》

「爱情、让人受尽委屈。」 2023-01-02 12:23 157阅读 0赞

# 《MySQL中级系列教程-第六章-聚集索引和非聚集索引》 #

## 1.1 概念 ##

**`聚集索引`：也叫聚簇索引（ClusterIndex），一般来说是以主键创建的索引，一张表只能有一个聚集索引，而且只有InnoDB能够创建聚集索引**。

**`非聚集索引`：也叫普通索引、辅助索引（Secondary Index），除了聚集索引外的索引都是非聚集索引。**

**聚集索引的数据存放在B+Tree索引树的叶子节点上，而非聚集索引的B+Tree索引树上只会存储当前索引列的数据与主键索引列的数据，并不会存放整行数据，当需要通过非聚集索引去检索一行数据时，首先非聚集索引通过索引树找到主键，然后通过主键去主键建立的B+Tree上查询出整行的数据；**

> **上面这句话很重要，务必记牢！**

## 1.2 聚集索引与非聚集索引底层原理图 ##

我们通过聚集索引与非聚集索引的底层原理图来直观的感受下两者的区别

*  **聚集索引与非聚集索引示意图：**

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

> 在这里提一下，回表查询需要把聚集索引加载进内存，**首先加载的应该是根节点，而不是直接定位到叶子节点**，这里方便画图就没有画出来；

通过上面的索引图我们能够很直观的发现，普通索引（非聚集索引）的查询如果查询到了其他列的数据，那么需要借助主键索引来查询整行的数据，这个过程也称为**回表查询**；

## 1.3 索引组织表 ##

其实，MySQL中的表的所有的数据都是按照主键排序的方式存储在一颗B+Tree上（InnoDB引擎），这颗B+Tree也叫聚集索引，所以叫MySQL叫索引组织表；

索引组织表的数据按主键排序手段被存储在B+Tree索引中，除了存储主键列值外还存储非主键列的值。普通索引只存储索引列，**索引组织表就是索引（主键索引）存储表的所有列的值**

## 1.4 聚集索引说明 ##

**关于聚集索引还有一个定义：数据行的物理排列顺序与该列值（主键）的逻辑顺序相同，并且一个表中只能拥有一个聚集索引。**

**什么是"数据行的物理排列顺序"？**

**数据行的物理排列顺序就是我们眼睛直观看到的列的排列顺序**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70 1]

一般来说我们主键都是自增的，大小都是从小到大，因此会忽略一个问题：**数据行的物理排列顺序与聚集索引的排列顺序是保持一致的**，

我们把"小军"的ID改为50，重新查看数据库表，发现排列顺序如下：

![在这里插入图片描述][20200731165652684.png]

> 发现数据行的物理排列顺序默认是和主键顺序保存一致的；

**在InnoDB中，数据表中的物理排列顺序始终会保持与聚集索引的逻辑顺序一致**；而聚集索引底层是一个B+Tree索引树，自然而然逻辑排列顺序是：从小到大顺序排列；因此数据表中的物理排列顺序是根据主键来排序的，也就是我们刚刚说的聚集索引的逻辑顺序；

**这也是网上很多人说聚集索引在一张表中只能有一个的原因，为什么呢？因为我数据表的逻辑排列顺序要与聚集索引的排列顺序保持一致呀！那如果有多个聚集索引我到底跟谁保持一致呢？？**

**而且我们知道MySQL是索引组织表的，如果聚集索引有多份，那么数据是否也会存在多份呢？这样存储效率明显下降**

## 1.5 MyISAM引擎为什么只能创建非聚集索引？ ##

我们都知道InnoDB是底层存储文件是分为：`.frm`（表空间文件）、`.idb`（索引文件和数据文件）；

MyISAM则是分了三个文件：`.frm`（表空间）、`.MYD`（数据文件）、`MYI`（索引文件）；

### 1.5.1 MySIAM引擎索引底层实现原理图 ###

虽然MyISAM和InnoDB的索引底层采用的都是B+Tree数据结构，但MyISAM和InnoDB索引的底层实现稍有不同：

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

> MyISAM没有聚集索引，MyISAM建立的都是普通索引（即使是主键）；

MyISAM引擎是没有聚集索引的，因为MyISAM引擎不属于索引组织表，即有单独的空间存储表数据，表数据并不是和主键建立的B+Tree存储在一起的；**MyISAM表通过任何索引来查询数据都需要回表查询；**

> 大家可以按照我3.4小节的方式修改一下id，看myisam引擎的表是否会根据主键进行排序（答案是不会的）

## 1.6 InnoDB表能不能没有聚集索引？ ##

我们知道InnoDB表是属于索引组织表，整表的数据都需要根据主键（聚集索引）来排序，并且数据也是存储在主键（聚集索引）的B+Tree上的；那么万一我们在表中没有创建主键（聚集索引）那么怎么办呢？？？

**InnoDB必须要有且只有一个主键（聚集索引）！！！不能没有主键（聚集索引）！！！**

**那奇了怪了，我在InnoDB建表的时候明明可以不创建主键（聚集索引）呀,也没见他报错呀，咋回事？？？？**

是这样的，主键（聚集索引）对于InnoDB实在是太重要了，InnoDB不能没有他，如果你不创建他会根据规则来选出较为合适的一列来做聚集索引，实在不行他就帮你创建一个隐藏的列作为聚集索引，规则如下：

（1）如果表定义了主键，则该列就是聚集索引；

（2）如果表没有定义主键，则第一个`not null unique`列是聚集索引；

（3）以上条件都不满足：InnoDB会创建一个隐藏的`row-id`作为聚集索引；

> **现在知道聚集索引对InnoDB来说有多重要了吧？？？**

## 1.7 一级索引和二级索引 ##

大家多多少少有听过一些DBA大佬口中经常说一级索引、二级索引吧？啥是一级索引和二级索引？？？

关于一级索引的定义：**索引和数据存储是在一起的，都存储在同一个B+Tree中的叶子节点。一般主键索引都是一级索引。**

关于二级索引的定义：**二级索引树的叶子节点存储的是本列索引值和主键值；而不是数据。在使用二级索引检索数据时，需要借助一级索引；也就是说，在找到索引后，得到对应的主键，再回到一级索引中找主键对应的数据记录。**

**咋一看怎么和聚集索引和非聚集索引那么像？？？**

**对！一级索引就是聚集索引；二级索引就是非聚集索引！！**

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70]: /images/20221119/712da551df964400b481be2a76f73d9a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70 1]: /images/20221119/7e370dc156724743bcf9997aa5dddd97.png
[20200731165652684.png]: /images/20221119/888f5ef6f8054e3699c09ebe919f90d4.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70 2]: /images/20221119/358ce8620d18400583d3ec5bd1b3b202.png