通俗易懂 索引、单列索引、复合索引、主键、唯一索引、聚簇索引、非聚簇索引、唯一聚簇索引 的区别与联系
索引
数据库只做两件事情:存储数据、检索数据。而索引是在你存储的数据之外,额外保存一些路标(一般是B+树),以减少检索数据的时间。所以索引是主数据衍生的附加结构。
一张表可以建立任意多个索引,每个索引可以是任意多个字段的组合。索引可能会提高查询速度(如果查询时使用了索引),但一定会减慢写入速度,因为每次写入时都需要更新索引,所以索引只应该加在经常需要搜索的列上,不要加在写多读少的列上。
单列索引 与 复合索引
只包含一个字段的索引叫做单列索引,包含两个或以上字段的索引叫做复合索引(或组合索引)。建立复合索引时,字段的顺序极其重要。
下面这个SQL语句在 列X,列Y,列Z 上建立了一个复合索引。
CREATE INDEX 索引名 ON 表名(列名X, 列名Y, 列名Z);
其实这相当于建立了三个索引,分别是:
1、单列索引(列X) 2、复合索引(列X, 列Y) 3、复合索引(列X,列Y,列Z)。
如何理解呢?
我们可以把多个字段组合的索引比喻成一个老式的纸质电话簿,三列分别是:
姓 - 名 - 电话号码
电话簿中的内容先按照姓氏的拼音排序,相同姓氏再按名字的拼音排序,这相当于在(姓,名)上建立了一个复合索引。你可以通过这个索引快速找到所有具有特定姓氏的人的电话号码,也可以快速找到具有特定 姓-名 组合的人的电话号码。然而,想象一下,如果你想找到某个特定名字的人,其实这个索引是没有用的,你只能从头到尾遍历整个电话簿。
唯一索引 与 主键
唯一索引是在表上一个或者多个字段组合建立的索引,这个(或这几个)字段的值组合起来在表中不可以重复。一张表可以建立任意多个唯一索引,但一般只建立一个。
主键是一种特殊的唯一索引,区别在于,唯一索引列允许null值,而主键列不允许为null值。一张表最多建立一个主键,也可以不建立主键。
聚簇索引、非聚簇索引、主键
在《数据库原理》一书中是这么解释聚簇索引和非聚簇索引的区别的:
聚簇索引的叶子节点就是数据节点,而非聚簇索引的叶子节点仍然是索引节点,只不过有指向对应数据块的指针。
怎么理解呢?
聚簇索引的顺序,就是数据在硬盘上的物理顺序。一般情况下主键就是默认的聚簇索引。
一张表只允许存在一个聚簇索引,因为真实数据的物理顺序只能有一种。如果一张表上还没有聚簇索引,为它新创建聚簇索引时,就需要对已有数据重新进行排序,所以对表进行修改速度较慢是聚簇索引的缺点,对于经常更新的列不宜建立聚簇索引。
聚簇索引性能最好,因为一旦具有第一个索引值的记录被找到,具有连续索引值的记录也一定物理地紧跟其后。一张表只能有一个聚簇索引,所以非常珍贵,必须慎重设置,一般要根据这个表最常用的SQL查询方式选择某个(或多个)字段作为聚簇索引(或复合聚簇索引)。
聚簇索引默认是主键,如果表中没有定义主键,InnoDB[1]会选择一个唯一的非空索引代替(“唯一的非空索引”是指列不能出现null值的唯一索引,跟主键性质一样)。如果没有这样的索引,InnoDB会隐式地定义一个主键来作为聚簇索引。
聚簇索引 与 唯一索引
严格来说,聚簇索引不一定是唯一索引,聚簇索引的索引值并不要求是唯一的,唯一聚簇索引才是!在一个有聚簇索引的列上是可以插入两个或多个相同值的,这些相同值在硬盘上的物理排序与聚簇索引的排序相同,仅此而已。