MySQL聚集索引和非聚集索引

发布时间:2018-07-11 22:29:50编辑:丝画阁阅读(278)

声明:本文中提到的索引都是以B+ tree组织的

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

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

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

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

举个例子说明下:

crea te table student (

`id` INT UNSIGNED AUTO_INCREMENT,

`name` VARCHAR(255),

PRI MARY 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的聚集索引找到相应的行记录;

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

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

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

(1)sel ect * from student where id >5000 and id <20000;

(2)sel ect * from student where name > 'Alie' and name < 'John';

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

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

关键字