浅谈聚簇索引与非聚簇索引

  • 聚簇索引:将数据存储与索引放到了一块,找到索引也就找到了数据。表数据按照索引的顺序来存储的,也就是说索引项的顺序与表中记录的物理顺序一致。
  • 非聚簇索引:将数据存储与索引分开,叶结点包含索引字段值及指向数据页数据行的逻辑指针,其行数量与数据表行数据量一致。

查看数据库下存储数据的文件夹,除去.frm代表的表结构文件,使用InnoDB聚簇索引的表还可以看到一个.ibd文件,而使用MYISAM非聚簇索引的表可以看到一个.MYI和.MYD的文件,分别是索引和数据。

在innodb中,在聚簇索引之上创建的索引称之为辅助索引,非聚簇索引都是辅助索引,像复合索引、前缀索引、唯一索引。辅助索引叶子节点存储的不再是行的物理位置,而是主键值,辅助索引访问数据总是需要二次查找,通过辅助索引首先找到的是主键值,再通过主键值找到数据行的数据页,再通过数据页中的Page Directory找到数据行。

何时使用

情景 使用聚簇索引 使用非聚簇索引
列经常被分组排序
返回某范围内的数据 ×
一个或极小不同值 × ×
小数目的不同值 ×
大数目的不同值 ×
频繁更新的列 ×
外键列
主键列
频繁修改索引列 ×

怎么使用

聚簇索引需要具有唯一性,一般要根据这个表最常用的SQL查询方式来进行选择,某个字段作为聚簇索引,或组合聚簇索引。

聚簇索引默认是主键,如果表中没有定义主键,InnoDB 会选择一个唯一的非空索引代替。如果没有这样的索引,InnoDB 会隐式定义一个主键来作为聚簇索引。InnoDB 只聚集在同一个页面中的记录。包含相邻健值的页面可能相距甚远。

如果你想重建索引并且已经设置了主键为聚簇索引,必须先删除主键,然后添加我们想要的聚簇索引,最后恢复设置主键

此时其他索引只能被定义为非聚簇索引。

聚簇索引的优缺点

优点

1.数据访问更快,因为聚簇索引将索引和数据保存在同一个B+树中,因此从聚簇索引中获取数据比非聚簇索引更快

2.聚簇索引对于主键的排序查找和范围查找速度非常快

缺点

1.插入速度严重依赖于插入顺序,按照主键的顺序插入是最快的方式,否则将会出现页分裂,严重影响性能。因此,对于InnoDB表,我们一般都会定义一个自增的ID列为主键
2.更新主键的代价很高,因为将会导致被更新的行移动。因此,对于InnoDB表,我们一般定义主键为不可更新。
3.二级索引访问需要两次索引查找,第一次找到主键值,第二次根据主键值找到行数据。

为什么主键建议使用自增id

mysql InnoDB 引擎底层数据结构是 B+ 树,表中的数据都是按顺序保存在 B+ 树上的(所以说索引本身是有序的)。

mysql 在底层以默认大小为 16k(可以自定义)的数据页为单位来存储数据的,如果一个数据页存满了,mysql 就会去申请一个新的数据页来存储数据。

当主键为自增 id ,每次插入新的记录时,记录就会顺序添加到当前索引节点的后续位置,当一页写满,就会自动开辟一个新的页。

但是主键为非自增 id时,为了确保索引有序,就需要将每次插入的数据都放到合适的位置上。当往一个快满或已满的数据页中插入数据时,新插入的数据会将数据页写满,就需要申请新的数据页,并且把上个数据页中的部分数据挪到新的数据页上。这就造成了页分裂,这种大量移动数据的过程是会严重影响插入效率的。

另外,在满足业务需求的情况下,尽量使用占空间更小的主键 id,比如int,有以下好处:

1、int 相比varchar、char、text使用更少的存储空间,而且数据类型简单,可以节约CPU的开销,更便于表结构的维护

2、默认都会在主键上建立主键索引,使用整型作为主键可以将更多的索引载入内存,提高查询性能。

3、对于InnoDB存储引擎而言,每个二级索引都会使用主键作为索引值的后缀,使用自增主键可以减少索引的长度(大小),方便更多的索引数据载入内存。
4、可以使索引数据更加紧凑,在数据插入、删除、更新时可以做到索引数据尽可能少的移动、分裂页,减少碎片的产生(可以通过optimize table 来重建表),减少维护开销。
5、在数据插入时,可以保证逻辑相邻的元素物理也相邻,便于范围查找。

MySQL 使用自增ID(int)主键和UUID(varchar)作为主键的优劣比较

(1)单实例或者单节点组:

经过500W、1000W的单机表测试,自增ID相对UUID来说,自增ID主键性能高于UUID,磁盘存储费用比UUID节省一半的钱。所以在单实例上或者单节点组上,使用自增ID作为首选主键。

(2)分布式架构场景:

20个节点组下的小型规模的分布式场景,为了快速实现部署,可以采用多花存储费用、牺牲部分性能而使用UUID主键快速部署;

20到200个节点组的中等规模的分布式场景,可以采用自增ID+步长的较快速方案。

200以上节点组的大数据下的分布式场景,可以借鉴类似twitter雪花算法构造的全局自增ID作为主键。

Reference

MySQL 使用自增ID(int)主键和UUID(varchar)作为主键的优劣比较

mysql为什么建议使用自增主键

聚簇索引与非聚簇索引(也叫二级索引)

聚簇索引和非聚簇索引

聚簇索引与非聚簇索引的区别

本作品采用《CC 协议》,转载必须注明作者和本文链接
讨论数量: 0
(= ̄ω ̄=)··· 暂无内容!

讨论应以学习和精进为目的。请勿发布不友善或者负能量的内容,与人为善,比聪明更重要!