MySQL聚集索引和非聚集索引

介绍一

MySQL的Innodb存储引擎的索引分为聚集索引和非聚集索引两大类，理解聚集索引和非聚集索引可通过对比汉语字典的索引。汉语字典提供了两类检索汉字的方式，第一类是拼音检索（前提是知道该汉字读音），比如拼音为cheng的汉字排在拼音chang的汉字后面，根据拼音找到对应汉字的页码（因为按拼音排序，二分查找很快就能定位），这就是我们通常所说的字典序；第二类是部首笔画检索，根据笔画找到对应汉字，查到汉字对应的页码。拼音检索就是聚集索引，因为存储的记录（数据库中是行数据、字典中是汉字的详情记录）是按照该索引排序的；笔画索引，虽然笔画相同的字在笔画索引中相邻，但是实际存储页码却不相邻。

 

正文内容按照一个特定维度排序存储，这个特定的维度就是聚集索引；

Innodb存储引擎中行记录就是按照聚集索引维度顺序存储的，Innodb的表也称为索引表；因为行记录只能按照一个维度进行排序，所以一张表只能有一个聚集索引。

非聚集索引索引项顺序存储，但索引项对应的内容却是随机存储的；

 

举个例子说明下：

create table student (

`id` INT  UNSIGNED AUTO_INCREMENT,

`name`  VARCHAR(255),

PRIMARY KEY(`id`),

KEY(`name`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 

该表中主键id是该表的聚集索引、name为非聚集索引；表中的每行数据都是按照聚集索引id排序存储的；比如要查找name='Arla'和name='Arle'的两个同学，他们在name索引表中位置可能是相邻的，但是实际存储位置可能差的很远。name索引表节点按照name排序，检索的是每一行数据的主键。聚集索引表按照主键id排序，检索的是每一行数据的真实内容。

 

也就是说查询name='Arle'的记录时，首相通过name索引表查找到Arle的主键id（可能有多个主键id，因为有重名的同学），再根据主键id的聚集索引找到相应的行记录；

 Mysql聚集索引和非聚集索引最简单的表述：

聚集索引一般是表中的主键索引，如果表中没有显示指定主键，则会选择表中的第一个不允许为NULL的唯一索引，如果还是没有的话，就采用Innodb存储引擎为每行数据内置的6字节ROWID作为聚集索引。

每张表只有一个聚集索引，因为聚集索引在精确查找和范围查找方面良好的性能表现（相比于普通索引和全表扫描），聚集索引就显得弥足珍贵，聚集索引选择还是要慎重的（一般不会让没有语义的自增id充当聚集索引）。

 

从宏观上分析下聚集索引和普通索引的性能差异，还是针对上述student表：

（1）select * from student where id >5000 and id <20000;

（2）select * from student where name > 'Alie' and name < 'John';

 

第一条SQL语句根据id进行范围查询，因为(5000, 20000)范围内的记录在磁盘上按顺序存储，通过顺序读取磁盘很快就能读到这批数据。

 

第二条SQL语句查询（'Alie', 'John'）范围内的记录，主键id分布可能是离散的1，100，20001，5000.....；增加了随机读取数据页几率；所以普通索引的范围查询效率被聚集索引甩开几条街都不止；非聚集索引的精确查询效率还是可以的，比聚集索引查询只增加了一次IO开销。

 

介绍二

我们先了解下InnoDB引擎表的一些关键特征：InnoDB引擎表是基于B+树的索引组织表(IOT)。

• 每个表都需要有一个聚集索引(clustered index)；
  所有的行记录都存储在B+tree的叶子节点(leaf pages of the tree)；
• 基于聚集索引的增、删、改、查的效率相对是最高的；
  • 如果我们定义了主键(PRIMARY KEY)，那么InnoDB会选择其作为聚集索引；
  • 如果没有显式定义主键，则InnoDB会选择第一个不包含有NULL值的唯一索引作为主键索引；
  • 如果也没有这样的唯一索引，则InnoDB会选择内置6字节长的ROWID作为隐含的聚集索引(ROWID随着行记录的写入而主键递增，这个ROWID不像ORACLE的ROWID那样可引用，是隐含的)。

综上总结，如果InnoDB表的数据写入顺序能和B+树索引的叶子节点顺序一致的话，这时候存取效率是最高的，也就是下面这几种情况的存取效率最高：

• 使用自增列(INT/BIGINT类型)做主键，这时候写入顺序是自增的，和B+数叶子节点分裂顺序一致；
  • 如果该表不指定自增列做主键，同时也没有可以被选为主键的唯一索引(上面的条件)，这时候InnoDB会选择内置的ROWID作为主键，写入顺序和ROWID增长顺序一致；
  • 如果一个InnoDB表又没有显示主键，又有可以被选择为主键的唯一索引，但该唯一索引可能不是递增关系时(例如字符串、UUID、多字段联合唯一索引的情况)，该表的存取效率就会比较差。

聚集索索 和 非聚集索引

每个InnoDB表都有一个称为聚集索引的特殊索引，其中存储了行的数据。通常，聚集索引与主键同义。

1. mysql的innodb表，就是索引组织表，表中的所有数据行都放在索引上，这就约定了数据是严格按照顺序存放的，所以不管插入的先后顺序，它在那个物理上的那个位置与插入的先后顺序无关。
2. 聚集索引：叶子节点存的是整行数据，直接通过这个聚集索引的键值找到某行
3. 聚集索引：数据的物理存放顺序与索引顺序是一致的，即：只要索引是相邻的，那么对应的数据一定也是相邻地存放在磁盘上的。
4. 聚集索引：数据行和相邻的键值紧凑地存储在一起，因为无法同时把数据行存放在两个不同的地方，所以一个表只能有一个聚集索引。
5. 非聚集索引，叶子节点存的是字段的值，通过这个非聚集索引的键值找到对应的聚集索引字段的值，再通过聚集索引键值找到表的某行，类似oracle通过键值找到rowid，再通过rowid找到行
6. mysql的innodb表，其聚集索引相当于整张表，而整张表也是聚集索引。默认通过主键聚集数据，如果没有定义主键，则选择第一个非空的唯一索引，如果没有非空唯一索引，则选择rowid来作为聚集索引
7. mysql的innodb表，因为整张表也是聚集索引，select出来的结果是顺序排序的，比如主键字段的数据插入顺序可以是5、3、4、2、1,查询时不带order by得出的结果也是按1、2、3、4、5排序
8. 通俗理解

• 聚集索引 ：类似新华字典正文内容本身就是一种按照一定规则排列的目录
• 非聚集索引：这种目录纯粹是目录，正文纯粹是正文的排序方式
• 每个表只能有一个聚集索引，因为目录只能按照一种方法进行排序 。
  ================

基于主键索引和普通索引（二级索引）的查询有什么区别？

主键索引的叶子节点存的是整行数据。在 InnoDB 里，主键索引也被称为聚集索引（clustered index）。
非主键索引的叶子节点内容是主键的值。在 InnoDB 里，非主键索引也被称为二级索引（secondary index）。

• 如果语句是 select * from T where ID=500，即 主键查询方式，则只需要搜索 ID 这棵 B+树 ；
• 如果语句是 select * from T where k=5， 即 普通索引查询方式，则需要先搜索 k 索引树，得到 ID的值为 500，再到 ID 索引树搜索一次。这个过程称为回表。B+树为了维护索引有序性，在插入新值的时候需要做必要的维护。以上面为例，
  • 如果插入新的行 ID 值为 700，则只只需要在 R5 的记录后面插入一个新记录。
  • 如果新插入的 ID值为 400，就相对麻烦了，需要逻辑上挪动后面的数据，空出位置。