C++ 全栈知识体系

MySQL进阶 - 聚簇索引和非聚簇索引

索引概念

数据库表的索引从数据存储方式上可以分为聚簇索引和非聚簇索引两种。“聚簇”的意思是数据行被按照一定顺序一个个紧密地排列在一起存储。我们熟悉的InnoDB和MyISAM两大引擎，InnoDB的默认数据结构是聚簇索引，而MyISAM是非聚簇索引。

聚簇索引（Clustered Index）并不是一种单独的索引类型，而是一种数据存储方式。当表有了聚簇索引的时候，表的数据行都存放在索引树的叶子页中。无法把数据行放到两个不同的地方，所以一张表只允许有一个聚簇索引。InnoDB的聚簇索引实际上是将索引和数据保存中同一个B-Tree中。InnoDB通过主键聚集数据，如果没有定义主键，InnoDB会选择一个唯一的的非空索引代替。如果没有这样的索引，InnoDB会隐式定义一个主键来作为聚簇索引。

非聚簇索引（NoClustered Index），又叫二级索引。二级索引的叶子节点中保存的不是指向行的物理指针，而是行的主键值。当通过二级索引查找行，存储引擎需要在二级索引中找到相应的叶子节点，获得行的主键值，然后使用主键去聚簇索引中查找数据行，这需要两次B-Tree查找。

两者详细介绍

聚簇索引

因为聚簇和非聚簇索引本质上是数据存储方式，需要依赖于载体，即以InnoDB引起来讲解聚簇索引，以MyISAM来讲解非聚簇索引。下述讲解的图都引用自《高性能MySQL》。

对于InnoDB引擎来说，是按照聚簇索引的形式存储数据：

InnoDB引擎-1

它的每个聚簇索引的叶子节点都包含主键值、事务ID、回滚指针(用于事务和MVCC)以及余下的列。从物理文件也可以看出 InnoDB的数据文件只有数据结构文件.frm和数据文件.ibd 其中.ibd中存放的是数据和索引信息 是存放在一起的。

InnoDB的二级索引和主键索引也有很大的不同，二级索引存放的是主键值而不是行指针，减少了移动数据或者分裂时维护二级索引的开销，因为不需要更新索引的行指针。

InnoDB引擎-2

聚簇索引的特点：

优点：

  1. 可以把相关数据保存在一起。例如实现电子邮箱时，可以根据用户ID来聚集数据，这样只需要从磁盘读取少量的数据页就能获取某个用户全部邮件，如果没有使用聚集索引，则每封邮件都可能导致一次磁盘IO。
  2. 数据访问更快，聚集索引将索引和数据保存在同一个B-Tree中，因此从聚集索引中获取数据通常比在非聚集索引中查找要快。
  3. 使用覆盖索引扫描的查询可以直接使用页节点中的主键值。

缺点：

  1. 聚簇数据最大限度地提高了IO密集型应用的性能，但如果数据全部放在内存中，则访问的顺序就没有那么重要了，聚集索引也没有什么优势了。
  2. 插入速度严重依赖于插入顺序，按照主键的顺序插入是加载数据到InnoDB表中速度最快的方式，但如果不是按照主键顺序加载数据，那么在加载完成后最好使用optimize table命令重新组织一下表。
  3. 更新聚集索引列的代价很高，因为会强制InnoDB将每个被更新的行移动到新的位置。
  4. 基于聚集索引的表在插入新行，或者主键被更新导致需要移动行的时候，可能面临页分裂的问题，当行的主键值要求必须将这一行插入到某个已满的页中时，存储引擎会将该页分裂成两个页面来容纳该行，这就是一次页分裂操作，页分裂会导致表占用更多的磁盘空间。
  5. 聚集索引可能导致全表扫描变慢，尤其是行比较稀疏，或者由于页分裂导致数据存储不连续的时候。
  6. 二级索引可能比想象的更大，因为在二级索引的叶子节点包含了引用行的主键列。
  7. 二级索引访问需要两次索引查找，而不是一次。

非聚簇索引

对于MyISAM引擎来说，是按照非聚簇索引的形式存储数据：

原始数据：

MyISAM引擎-1

存储方式：

MyISAM引擎-2

按照列值和行号来组织索引的，叶子节点中保存的实际上是指向存放数据块的指针。从物理文件中也可以看出MyISAM的索引文件.MYI和数据文件.MYD是分开存储的 是相对独立的。

举例：执行流程：select * from user where id =1

  1. 查看该user表的myi索引文件中有没有以id为索引的索引树
  2. 在id索引树上通过id值找到相应节点，从而得到节点的数据（叶子节点存的是索引值和数据地址，数据地址指向当前表myd数据文件具体的哪一行）
  3. 根据数据地址去myd文件里找到对应的数据返回。

MyISAM引擎-3

两者的区别

数据存储方式

最直观的区别是反映在数据存储方式上，在MySQL数据库中InnoDB（聚簇）和MyISAM（非聚簇）数据存储文件格式如下：

存储引擎是InnoDB, 在data目录下会看到2类文件：.frm、.ibd

（1）*.frm--表结构的文件。
（2）*.ibd--表数据文件
1
2

存储引擎是MyISAM, 在data目录下会看到3类文件：.frm、.myi、.myd

（1）*.frm--表定义，是描述表结构的文件。
（2）*.MYD--"D"数据信息文件，是表的数据文件。
（3）*.MYI--"I"索引信息文件，是表数据文件中任何索引的数据树
1
2
3
4

示意图，test1的存储引擎为InnoDB，test2的存储引擎为MyISAM：

引擎示意图

聚簇索引和非聚簇索引的存储方式区别：

  1. 在MyISAM引擎索引和数据是分开存储的，而InnoDB是索引和数据是一起以idb文件的形式进行存储的。
  2. 在访问速度上，聚簇索引比非聚簇索引快。非聚簇索引需要先查询一遍索引文件，得到索引，跟据索引获取数据。而聚簇索引的索引树的叶子节点的直接指向要查找的数据行。

## 二级索引查询

对于采用聚簇索引的InnoDB引擎的主键索引B+Tree和MyISAM的主键索引树以及MyISAM的二级索引B+Tree都是采用这样的结构。

二级索引-1

但是InnoDB的二级索引B+Tree却是这样的：

二级索引-2

可以得出： 　　在使用二级索引进行查询的时候，InnoDB首先通过二级索引B+Tree得到数据行的主键索引，然后再通过主键索引树查询数据。所以在二级索引，InnoDB的性能消耗比较大。 　　但是，这种情况在InnoDB中有一定的优化，不是认为控制的，而是引擎实现的，通过二级索引查询多了，InnoDB会生成自适应的哈希索引。

引用高性能MySQL的图能更加清晰的看到其差异：

聚簇索引

从图中可以看出 InnoDB二级索引的叶子节点存放的是KEY字段+主键值，因此首先通过二级索引查找到的是主键值，再根据主键值在主键索引中查找到相应的数据文件。而MyISAM的二级索引存放的还是列值和行号的组合 叶子节点中保存的是指向物理数据的指针，因此它的主建索引和二级索引的结构并没有任何区别，只是说主键索引的索引值是唯一且非空的，而MyISAM引擎可以不设置主键。InnoDB引擎是必须设置主键的，需要依赖主键生成聚簇索引。

← MySQL进阶 - 索引使用 →