mysql聚集索引与非聚集索引

一、索引类型

1、从物理存储角度上，索引可以分为聚集索引和非聚集索引。

1. 聚集索引，聚集索引决定数据在磁盘上的物理排序，一个表只能有一个聚集索引。
2. 非聚集索引，非聚集索引并不决定数据在磁盘上的物理排序，索引上只包含被建立索引的数据，以及一个行定位符 row-locator，这个行定位符，可以理解为一个聚集索引物理排序的指针，通过这个指针，可以找到行数据。

2、从逻辑角度，索引可以分为以下几种

1. 普通索引：最基本的索引，它没有任何限制。
2. 唯一索引：与普通索引类似，不同的就是索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。
3. 主键索引：它是一种特殊的唯一索引，用于唯一标识数据表中的某一条记录，不允许有空值，一般用     primary key 来约束。
4. 联合索引（又叫复合索引）：多个字段上建立的索引，能够加速复合查询条件的检索。
5. 全文索引：老版本     MySQL 自带的全文索引只能用于数据库引擎为 MyISAM 的数据表，新版本 MySQL 5.6 的 InnoDB 支持全文索引。默认 MySQL 不支持中文全文检索，可以通过扩展 MySQL，添加中文全文检索或为中文内容表提供一个对应的英文索引表的方式来支持中文。

本质都是：通过不断地缩小想要获取数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是说，有了这种索引机制，我们可以总是用同一种查找方式来锁定数据。

二、索引优缺点

1、优点

1. 索引大大减小了服务器需要扫描的数据量，从而大大加快数据的检索速度，这也是创建索引的最主要的原因。
2. 索引可以帮助服务器避免排序和创建临时表
3. 索引可以将随机IO变成顺序IO
4. 索引对于InnoDB（对索引支持行级锁）非常重要，因为它可以让查询锁更少的元组，提高了表访问并发性
5. 关于InnoDB、索引和锁：InnoDB在二级索引上使用共享锁（读锁），但访问主键索引需要排他锁（写锁）
6. 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
7. 可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
8. 在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。
9. 通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。
    

2、缺点

1. 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加
2. 索引需要占物理空间，除了数据表占用数据空间之外，每一个索引还要占用一定的物理空间，如果需要建立聚簇索引，那么需要占用的空间会更大
3. 对表中的数据进行增、删、改的时候，索引也要动态的维护，这就降低了整数的维护速度
4. 如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。
5. 对于非常小的表，大部分情况下简单的全表扫描更高效；
    

三、聚簇索引和非聚簇索引

1、前言

1. 聚集索引定义：根据数据行的键值在表中排序存储数据行。索引定义中包含聚集索引列。每个表只能有一个聚集索引。只有当表包含聚集索引时，表中的数据行才按排序顺序存储。如果表具有聚集索引，则该表称为聚集表。集索引决定了表数据的存储顺序,如果表没有聚集索引，则其数据行存储在一个称为堆的无序结构中。
2. mysql，当你定义一个主键时，InnnodDB存储引擎则把它当做聚集索引
3. 如果你没有定义一个主键，则InnoDB定位到第一个唯一索引，且该索引的所有列值均非空的，则将其当做聚集索引。
4. 如果表没有主键或合适的唯一索引INNODB会产生一个隐藏的行ID值6字节的行ID聚集索引，
5. 一个表中的所有索引除了聚集索引，其他的都是二级索引（辅助索引）
6. 辅助索引，其叶子节点并不包含行记录的全部数据，叶子结点除了包含键值以外，每个叶子结点中的索引行还包含了一个书签，该书签用来告诉存储引擎可以在哪找到相应的数据行，由于innodb引擎表是索引组织表，因此innodb存储引擎的辅助索引的书签就是相应行数据的聚集索引键。

1、非聚簇索引

1. 非聚集索引定义：非聚集索引并不是在物理上排列数据,即索引中的逻辑顺序并不等同于表中行的物理顺序,索引是指向表中行的位置的指针,这些指针本身是有序的,通过这些指针可以在表中快速定位数据。
2. MyISAM的是非聚簇索引，B+Tree的叶子节点上的data，并不是数据本身，而是数据存放的地址。主索引和辅助索引没啥区别，只是主索引中的key一定得是唯一的。这里的索引都是非聚簇索引。非聚簇索引的两棵B+树看上去没什么不同，节点的结构完全一致只是存储的内容不同而已，主键索引B+树的节点存储了主键，辅助键索引B+树存储了辅助键。表数据存储在独立的地方， 这两颗B+树的叶子节点都使用一个地址指向真正的表数据，对于表数据来说，这两个键没有任何差别。由于索引树是独立的，通过辅助键检索无需访问主键的索引树。InnoDB的数据文件本身就是索引文件，B+Tree的叶子节点上的data就是数据本身，key为主键，这是聚簇索引。聚簇索引，叶子节点上的data是主键(所以聚簇索引的key，不能过长)。 

2、聚簇索引

1. 聚集索引定义：根据数据行的键值在表中排序存储数据行。索引定义中包含聚集索引列。每个表只能有一个聚集索引。只有当表包含聚集索引时，表中的数据行才按排序顺序存储。如果表具有聚集索引，则该表称为聚集表。集索引决定了表数据的存储顺序,如果表没有聚集索引，则其数据行存储在一个称为堆的无序结构中。
2. mysql，当你定义一个主键时，InnnodDB存储引擎则把它当做聚集索引
3. 如果你没有定义一个主键，则InnoDB定位到第一个唯一索引，且该索引的所有列值均非空的，则将其当做聚集索引。
4. 如果表没有主键或合适的唯一索引INNODB会产生一个隐藏的行ID值6字节的行ID聚集索引，
5. 一个表中的所有索引除了聚集索引，其他的都是辅助索引，其叶子节点并不包含行记录的全部数据，叶子结点除了包含键值以外，每个叶子结点中的索引行还包含了一个书签，该书签用来告诉存储引擎可以在哪找到相应的数据行，由于innodb引擎表是索引组织表，因此innodb存储引擎的辅助索引的书签就是相应行数据的聚集索引键。
6. InnoDB使用的是聚簇索引，将主键组织到一棵B+树中，而行数据就储存在叶子节点上，若使用"where id = 14"这样的条件查找主键，则按照B+树的检索算法即可查找到对应的叶节点，之后获得行数据。若对Name列进行条件搜索，则需要两个步骤：第一步在辅助索引B+树中检索Name，到达其叶子节点获取对应的主键。第二步使用主键在主索引B+树种再执行一次B+树检索操作，最终到达叶子节点即可获取整行数据。

 

 

 

聚簇索引的数据的物理存放顺序与索引顺序是一致的，即：只要索引是相邻的，那么对应的数据一定也是相邻地存放在磁盘上的。聚簇索引要比非聚簇索引查询效率高很多。聚集索引这种主+辅索引的好处是，当发生数据行移动或者页分裂时，辅助索引树不需要更新，因为辅助索引树存储的是主索引的主键关键字，而不是数据具体的物理地址。

非聚集索引，类似于图书的附录，那个专业术语出现在哪个章节，这些专业术语是有顺序的，但是出现的位置是没有顺序的。每个表只能有一个聚簇索引，因为一个表中的记录只能以一种物理顺序存放。但是，一个表可以有不止一个非聚簇索引。

通常来说，表的索引越多，其查询的速度也就越快。但是，表的更新速度则会降低。这主要是因为表的更新同时也是索引的更新。到底在表中创建多少索引合适，就需要在这个更新速度与查询速度之间取得一个均衡点。如对于一些数据仓库或者决策型数据库系统，其主要用来进行查询。相关的记录往往是在数据库初始化的时候导入。此时，设置的索引多一点，可以提高数据库的查询性能。同时因为记录不怎么更新，所以索引比较多的情况下，也不会影响到更新的速度。相反，如果那些表中经常需要更新记录，如一些事务型的应用系统，数据更新操作是家常便饭的事情。此时如果在一张表中建立过多的索引，则会影响到更新的速度。由于更新操作比较频繁，所以对其的负面影响，要比查询效率提升要大的多。此时就需要限制索引的数量，只在一些必要的字段上建立索引。
 

参考：

https://blog.csdn.net/lisuyibmd/article/details/53004848

https://blog.csdn.net/alexdamiao/article/details/51934917

https://www.cnblogs.com/jmcui/archive/2020/03/16/12502828.html