数据库中的B+树索引可以分为聚集索引和辅助索引.
聚集索引:InnoDB表 索引组织表,即表中数据按主键B+树存放,叶子节点直接存放整条数据,每张表只能有一个聚集索引。
1.当你定义一个主键时,InnnodDB存储引擎则把它当做聚集索引
2.如果你没有定义一个主键,则InnoDB定位到第一个唯一索引,且该索引的所有列值均飞空的,则将其当做聚集索引。
3如果表没有主键或合适的唯一索引INNODB会产生一个隐藏的行ID值6字节的行ID聚集索引,
补充:由于实际的数据页只能按照一颗B+树进行排序,因此每张表只能有一个聚集索引,聚集索引对于主键的排序和范围查找非常有利.
例子: 比如图书馆新进了一批书。那么这些书需要放到图书馆内。书如何放呢?一般都有一个规则,杂志类的放到101房间,文学类的放到102房间,理工类的放到103房间等等。这些存储的规则决定了每本书应该放到哪里。而这个例子中聚集索引为书的类别。
辅助索引:(也称非聚集索引)是指叶节点不包含行的全部数据,叶节点除了包含键值之外,还包含一个书签连接,通过该书签再去找相应的行数据。下图显示了InnoDB存储引擎辅助索引和聚集索引的关系:
从上图中可以看出,辅助索引叶节点存放的是主键值,获得主键值后,再从聚集索引中查找整行数据。举个例子,如果在一颗高度为3的辅助索引中查找数据,首先从辅助索引中获得主键值(3次IO),接着从高度为3的聚集索引中查找以获得整行数据(3次IO),总共需6次IO。一个表上可以存在多个辅助索引。
例子: 同学如果想去图书馆找一本书,而不知道这本书在哪里?那么这个同学首先应该找的就是 检索室吧。对于要查找一本书来说,在检索室查是一个非常快捷的的途径了吧。但是,在检索室中你查到了该书在XX室XX书架的信息。你的查询结束了吗?没有吧。你仅仅找到了目的书的位置信息,你还要去该位置去取书。
对于这种方式来说,你需要两个步骤:
1、查询该记录所在的位置。
2、通过该位置去取要找的记录。
总结二者区别:
相同的是:不管是聚集索引还是辅助索引,其内部都是B+树的形式,即高度是平衡的,叶子结点存放着所有的数据。
不同的是:聚集索引叶子结点存放的是一整行的信息,而辅助索引叶子结点存放的是单个索引列信息.
为了阐述非聚集索引写性能问题,我们先来看一个例子:
mysql>create table t (
idintauto_increment,
name varchar(30),
primary key (id));
我们创建了一个表,表的主键是id,id列式自增长的,即当执行插入操作时,id列会自动增长,页中行记录按id顺序存放,不需要随机读取其它页的数据。因此,在这样的情况下(即聚集索引),插入操作效率很高。
但是,在大部分应用中,很少出现表中只有一个聚集索引的情况,更多情况下,表上会有多个非聚集的(辅助索引)。比如,对于上一张表t,业务上还需要按非唯一的name字段查找,则表定义改为:
mysql>create table t (
idintauto_increment,
name varchar(30),
primary key (id),
key (name));
这时,除了主键聚合索引外,还产生了一个name列的辅助索引,对于该非聚集索引来说,叶子节点的插入不再有序,这时就需要离散访问非聚集索引页,插入性能变低。
辅助索引写性能问题
何时使用聚集索引或非聚集索引
下面的表总结了何时使用聚集索引或非聚集索引(很重要):
动作描述
使用聚集索引
使用非聚集索引
列经常被分组排序
应
应
返回某范围内的数据
应
不应
一个或极少不同值
不应
不应
小数目的不同值
应
不应
大数目的不同值
不应
应
频繁更新的列
不应
应
外键列
应
应
主键列
应
应
频繁修改索引列
不应
应