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

 

本文出处：http://www.cnblogs.com/wy123/p/7211742.html 
（保留出处并非什么原创作品权利，本人拙作还远远达不到，仅仅是为了链接到原文，因为后续对可能存在的一些错误进行修正或补充，无他）

 

 

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，可重复的列，比如如下