MySQL InnoDB引擎B+树索引简单整理说明

共计 2170 个字符，预计需要花费 6 分钟才能阅读完成。

MySQL 中的 InnoDB 引擎表索引类型有一下几种（以下所说的索引，没有特殊说明，均指 InnoDB 引擎表索引。）
0 = Secondary Index, 二级索引，
1 = Clustered Index, 聚集索引
2 = Unique Index, 唯一索引
3 = Primary Index, 主键索引
32 = Full-text Index, 全文索引
64 = Spatial Index, 空间索引
128 = A secondary index that includes a virtual generated column. 二级计算列索引。

其中二级索引，聚集索引，唯一索引，主键索引，二级计算列索引，按照逻辑存储结构来看，都是 B + 树来存储的
这一点与其他数据库的 B + 树索引逻辑存储结构上看，并没有太大的不同，以上几种索引都是从逻辑角度来划分的。

如果从物理存储角度来看，MySQL 中的这几类索引可以划分为聚集索引和二级索引（或者叫非聚集索引）
其中，主键索引和聚集索引，可以归类为聚集索引，二级索引，唯一索引，二级计算列索引都数据非聚集索引。

MySQL 中的聚集索引

MySQL 聚集索引就是根据主键，把整张表的数据，在逻辑上组织成一棵 B + 树，因此一个表只能由一个聚集索引。
非叶子节点存储聚集索引 key 值，叶子节点存储表中的数据本身，叶子节点与叶子节点之间采用双向链表的方式连接在一起。

了解 MySQL 的聚集索引之前，先了解一下 MySQL 主键生成机制。
MySQL 的 InnoDB 必须有一个主键，
如果在建表的时候指定了主键，那么这个主键就是该表的主键（听起来这么别扭，主要是跟未指定主键的情况下，自动生成的额主键作对比）
如果在建表的时候没有指定主键，那么存储引擎会自动为表上建一个主键列
1）对于指定了主键的表，主键生成的索引就是“主键索引”，
2）对于未指定主键的表，如果有（一个或者多个）非空的唯一索引，（第一个）非空唯一约束做主键
3）对于未指定主键的表，且没有唯一约束的表，默认生成一个主键，该主键上生成的索引就是“聚集索引”，
实际上，前者的“主键索引”和后者的“聚集索引”，物理存储上都可以归属为聚集索引

1，显式主键索引（聚集索引）

如下截图，创建了 test_index_type_1
在建表的时候指定了主键，则主键默认生成主键索引，索引类型是 3（从物理存储角度看，是聚集索引）
在表创建完成之后创建了索引，生成的是二级索引，索引类型是 0（从物理存储角度看，是非聚集索引）

2，非空唯一约束生成的主键索引（聚集索引）

如下截图，创建了 test_index_type_2,
在建表的时候没有指定了主键，但是指定了一个唯一的非空约束，那么这个字段会当做主键用，生成的索引类型是 3（从物理存储角度看，是聚集索引）

3，系统默认主键生成的聚集索引（聚集索引）

如下截图，创建了 test_index_type_3 表,
在建表的时候没有指定了主键，也没有指定唯一的非空约束，InnoDB 引擎会自动生成一个 6 字节的指针，生成的索引类型是聚集索引，类型是 1（从物理存储角度看，是聚集索引）

非聚集索引

非聚集索引，非聚集索引同样是 B + 树的结构来存储数据的，
与聚集索引做大的差异在于非聚集索的叶子节点存储的仅仅是索引的 key 值 + 聚集索引的 key 值，但是不包括所有的非索引键值。

1，唯一索引约束生成的唯一索引（非聚集索引）

如下截图，创建了 test_index_type_4 表,
指定了 id 为 unique 的，那么会自动在 id 列上创建一个唯一索引。

2，手动创建的唯一索引（非聚集索引）
如下截图，创建了 test_index_type5 表,
手动在创建一个唯一的索引，那么这个索引类型为唯一索引

3，手动创建的二级索引（非聚集索引）
如下截图，创建了 test_index_type6 表,
那手动在创建一个的索引（未指定 unique），那么这个索引类型为二级索引

4，计算列索引，在计算列上手动创建索引（非聚集索引）
如下截图，创建了 test_index_type7 表,
test_index_type7 上有一个计算列，创建完成之后在计算列上加索引，索引为计算列索引

总结：

整体上来看，MySQL 的几种类型的 B + 树的索引还是比较容易理解的，跟 SQL Server 中的索引也比较类似。
MySQL 的 InnoDB 引擎表中，主键索引，非空唯一约束生成的聚集索引，聚集索引，从物理存储上看都数据聚集索引。
主键索引，非空唯一约束生成的聚集索引，聚集索引，三者有一个明显的特点，都要求所在的列是非空且唯一的。
另外就是 MySQL 无法显式创建聚集索引，也即 create clustered index.

这一点与 SQL Server 有很大的不同，
1，在 SQL Server 中，如果没有指定主键，或者指定了主键没有但是指定为 nonclustered，那么表就是为堆表，系统不会添加默认字段作为聚集索引
2，SQL Server 的主键可以仅仅是主键，可以不是聚集索引（默认情况下主键是聚集索引）。

聚集索引可以指定在任意一个列上，可以是非主键列，可以是非唯一，可为 null，可重复的列，比如如下

本文永久更新链接地址：http://www.linuxidc.com/Linux/2017-07/145999.htm