聚集索引和非聚集索引的区别 mysql_聚集索引和非聚集索引区别

概述：

汉语字典的正文本身就是一个聚集索引。

比如要查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密集型的应用。因为随机插入的时候，需要为新的行寻找合适的位置——通常是已有数据的中间位置——并且分配空间。这回增加很多额外的工作，并导致分布不够优化。