聚集索引和非聚集索引区别
概述:
汉语字典的正文本身就是一个聚集索引。
比如要查z开头的,我们会自然翻到字典的后半部分,而不是从前往后一页一页翻,避免了全表扫描。
而当我们翻到那一页,就能得到内容,而不需要再去查其他目录来找到需要找的内容。
字典正文内容本身就是一种按照一定规则排列的索引,索引中的叶子节点保存的就是要查找的数据,这种称为聚集索引。
如果是用偏旁部首来查,会得到某个字在哪一页,索引中的叶子节点保存的是索引,这种称为非聚集索引。
建立索引:
在SQL语言中,建立聚簇索引使用CREATE INDEX语句,
格式为:CREATE CLUSTER INDEX index_name
ON table_name(column_name1,column_name2,...);
聚集索引:
表数据按照索引的顺序来存储的,也就是说索引项的顺序与表中记录的物理顺序一致。
对于聚集索引, 叶子结点即存储了真实的数据行,不再有另外单独的数据页 。
在一张表上最多只能创建一个聚集索引,因为真实数据的物理顺序只能有一种。
若一张表没有聚集索引,则他被称为堆集,这样表的数据行无特定的顺序,所有新行将被添加到表的末尾。
优点:
查询速度快,因为一旦具有第一个索引值的纪录被找到,具有连续索引值的记录也一定物理的紧跟其后。
缺点:
是对表进行修改速度较慢,这是为了保持表中的记录的物理顺序与索引的顺序一致,而把记录插入到数据页的相应位置,必须在数据页中进行数据重排, 降低了执行速度。
所以不适用于频繁更改的列 。这将导致整行移动。
非聚集索引 :
表数据存储顺序与索引顺序无关。
对于非聚集索引,叶结点包含索引字段值及指向数据页数据行的逻辑指针,其行数量与数据表行数据量一致。
以下摘自《高性能MySQL》:
聚簇索引:
聚簇索引不是一种单独的索引类型,而是一种数据存储方式。
InnoDB的聚簇索引实际上是在同一个结构中保存了B-Tree索引和数据行。
因为无法同时把数据行存放在两个不同的地方,所以一个表只能有一个聚簇索引。
叶子页包含了行的全部数据,但是节点列只包含索引列。
InnoDB通过主键聚集数据,如果没有主键就选择一个唯一的非空索引,如果没有这样的索引,就隐式定义一个主键作为聚簇索引。
聚簇的优点:
-
将相关数据保存在一起,减少磁盘I/O。
-
数据访问更快。因为数据和索引保存在一起。
-
使用覆盖扫描的查询可以直接使用页结点中的主键值。
缺点:
-
限制了提高IO密集型应用的性能,但如果数据全部放在内存中,则访问顺序就没那么重要了,聚簇的优势也没了;
-
插入速度严重依赖于插入顺序,按主键的顺序插入是最快的方式,不然就应该在加载完后用opeimize table重新组织一下表;
-
代价更高:限制innoDB将被更新的行移动到新的位置;
-
当主键被更新或者新数据插入导致行移动的时候,可能面临“页分裂”问题。
-
可能导致全表扫描变慢,尤其树数据比较稀疏,且数据不连续时;
-
二级索引可能比想象更大,因其包含了引用行的主键列;
-
二级索引需要两次查找,而不是一次。
使用InnoDB应当尽可能地按主键顺序插入数据,并尽可能地使用单调的聚簇键的值来来插入新行。
最好避免随机的聚簇索引,特别是对于IO密集型的应用。因为随机插入的时候,需要为新的行寻找合适的位置——通常是已有数据的中间位置——并且分配空间。这回增加很多额外的工作,并导致分布不够优化。