【MySQL】聚簇索引 && 非聚簇索引

聚簇索引 && 非聚簇索引

一、MyISAM -- 非聚簇索引

MyISAM 引擎是 MySQL5.5.8 版本之前默认的存储引擎，不支持事务，但支持全文检索。其使用 B+树作为索引结构，叶节点的 data 域存放的是数据记录的地址。

MyISAM 存储引擎的索引方式也叫做非聚集索引，这是为了方便索引树和主键树可以映射同样的数据。

下图为 MyISAM 表的主索引，其中 Col1 是主键：

其中，MyISAM 存储引擎最大的特点就是，将索引 Page 和数据 Page 分离，也就是叶子节点没有数据，只有对应数据的地址。

并且每次拿着数据的地址去主表里面查找真实数据的这个过程，叫做回表查询，这会导致 I/O 次数变多，使得效率降低！

当然，MySQL 除了默认会建立主键索引外，一般我们也有可能按照其它列信息建立索引，这种索引可以叫做普通索引。而对于 MyISAM，建立普通索引和主键索引本质没有什么差别，无非就是主键不能重复并且具有唯一性，而非主键可重复且可有多个。

简单地说，普通索引也是会创建其独立的一颗索引树的！

下图就是基于 MyISAM 的 Col2 建立的索引，和主键索引没有差别：

这个普通索引同样也是一棵 B+树，其中 data 域保存数据记录的地址。

但是现在的版本就不再使用 MyISAM 为默认索引引擎了，因为它不支持事务，而要知道的是我们现在生活中存在着大量的事务。比如说微信支付，这个时候要考虑收入和支出的问题，还有就是如果支付失败了，那么钱得返回去，这些就是事务的例子，这也是为什么 MyIASM 会被取消默认索引引擎的理由。而我们下面要介绍的 InnoDB 是支持事务的！

💥需要注意的是，表只有一张，而索引树是有多颗的，它们指向同一张表！

二、InnoDB -- 聚簇索引

InnoDB 存储引擎支持事务，其设计目标主要面向在线事务处理的应用，从 MySQL 5.5.8 版本开始，InnoDB存储引擎是默认的存储引擎。

但 InnoDB 使用 B+树作为索引结构时，具体实现方式却与 MyISAM 存储引擎截然不同，体现在节点的存储内容上。

它们的区别如下所示：

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

第二个区别是InnoDB 的辅助索引 data 域存储相应记录主键的地址而不是值，所有辅助索引都引用主键作为 data 域。

以下图为例，就是以 id 为主键，其中叶子节点数据本身就是一个表的一部分数据，这样子的话方便我们对数据的操作。同时若是有其它索引，比如下图中的 name 为辅助索引，那么我们不可能在 name 的索引树里面也存一份完整的数据呀，那也太浪费空间了！

此时因为 name 就是一个非聚簇索引，所以形成了一个回表操作！在 name 的数据中存放聚簇索引 id 的地址，然后回表到主表中重新查询指定数据，最后获得想要的数据！

所以通过辅助索引 name，可以找到目标记录，然后需要进行两遍索引：首先检索辅助索引获得主键，然后用主键到主索引树中检索获得最终的完整数据记录！

可以看到主键树中叶子节点包含了完整的数据记录，这种索引叫做聚集索引。因为 InnoDB 的数据文件本身要按主键聚集，所以 InnoDB 要求表必须有主键（ MyISAM 可以没有主键）。

如果 InnoDB 没有显式指定主键的话，则 MySQL 系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则 MySQL 自动为 InnoDB 表生成一个隐含字段作为主键，这个字段长度为 6 个字节，类型为长整型。

三、两种存储引擎的区别

三层树高的B+树可以存放多少条记录❓❓❓

一、每页可以存放多少条记录？

InnoDB 的页大小通常是 16KB，每条记录大小会因字段类型不同而有很大差异。我们用一个中等记录大小来估算：

• 假设每条记录平均大小为 100B。
• 页面中除去头部、尾部和目录等开销，真正可用于数据的约 13KB 左右。

那么，每页大概可存记录数：

每页可用空间 ≈ 13KB = 13312 字节
每页记录数 ≈ 13312 / 100 ≈ 133 条记录

✅ 我们保守取值：每页 100 条记录

二、B+ 树结构与高度的关系

B+ 树的层次结构大致是：

Level 2 (Root 节点)        → 1 页
   ↓
Level 1 (中间节点)         → 每个节点指向若干页
   ↓
Level 0 (叶子页)           → 每个叶子页存储数据记录

每个非叶子节点存储的是 子页指针 + 键值对，每个指针项大小约为 8B~16B。

估算一下：

• 每个非叶节点页可指向的子页数：约 500 个（取中间值，约 8~16B/项 × 16KB）
• 所以一层能容纳下 约 500 个子页

三、三层 B+ 树可存放的记录数

现在，我们来估算：

• 叶子节点（底层）：每个叶子页能存 100 条记录
• 每个中间节点可指 500 个叶子页
• 根节点再指 500 个中间节点

所以总记录数估算为：

总记录数 ≈ 500（根） × 500（中间） × 100（叶子记录数）
        = 25,000,000 条记录

结论：一个三层高度的 B+ 树，在 InnoDB 中大约可以容纳 2500 万条记录。（假设每条记录约 100B）