聚簇索引和非聚簇索引
https://www.cnblogs.com/jiawen010/p/11805241.html
我们日常工作中,根据实际情况自行添加的索引都是辅助索引,辅助索引就是一个为了需找主键索引的二级索引,现在找到主键索引再通过主键索引找数据;
聚簇索引:将数据存储与索引放到了一块,找到索引也就找到了数据。表数据按照索引的顺序来存储的,也就是说索引项的顺序与表中记录的物理顺序一致。
非聚簇索引:将数据存储与索引分开,叶结点包含索引字段值及指向数据页数据行的逻辑指针,其行数量与数据表行数据量一致。
查看数据库下存储数据的文件夹,除去.frm 代表的表结构文件,使用 InnoDB 聚簇索引的表还可以看到一个.ibd 文件,而使用 MYISAM 非聚簇索引的表可以看到一个.MYI 和.MYD 的文件,分别是索引和数据。
在 innodb 中,在聚簇索引之上创建的索引称之为辅助索引,像复合索引、前缀索引、唯一索引。辅助索引叶子节点存储的不再是行的物理位置,而是主键值,辅助索引访问数据总是需要二次查找,通过辅助索引首先找到的是主键值,再通过主键值找到数据行的数据页,再通过数据页中的 Page Directory 找到数据行。
1、聚簇索引
聚簇索引就是按照每张表的主键构造一颗B+树,同时叶子节点中存放的就是整张表的行记录数据,也将聚集索引的叶子节点称为数据页。这个特性决定了索引组织表中数据也是索引的一部分,每张表只能拥有一个聚簇索引。
Innodb通过主键聚集数据,如果没有定义主键,innodb会选择非空的唯一索引代替。如果没有这样的索引,innodb会隐式的定义一个主键来作为聚簇索引。
聚簇索引的优缺点:
优点:
1.数据访问更快,因为聚簇索引将索引和数据保存在同一个B+树中,因此从聚簇索引中获取数据比非聚簇索引更快。
2.聚簇索引对于主键的排序查找和范围查找速度非常快。
缺点:
1.插入速度严重依赖于插入顺序,按照主键的顺序插入是最快的方式,否则将会出现页分裂,严重影响性能。因此,对于InnoDB表,我们一般都会定义一个自增的ID列为主键
2.更新主键的代价很高,因为将会导致被更新的行移动。因此,对于InnoDB表,我们一般定义主键为不可更新。
3.二级索引访问需要两次索引查找,第一次找到主键值,第二次根据主键值找到行数据。
2、辅助索引
在聚簇索引之上创建的索引称之为辅助索引,辅助索引访问数据总是需要二次查找。辅助索引叶子节点存储的不再是行的物理位置,而是主键值。通过辅助索引首先找到的是主键值,再通过主键值找到数据行的数据页,再通过数据页中的Page Directory找到数据行。
Innodb辅助索引的叶子节点并不包含行记录的全部数据,叶子节点除了包含键值外,还包含了相应行数据的聚簇索引键。
辅助索引的存在不影响数据在聚簇索引中的组织,所以一张表可以有多个辅助索引。在innodb中有时也称辅助索引为二级索引。
3、InnoDB索引实现
InnoDB也使用B+Tree作为索引结构,但具体实现方式却与MyISAM截然不同.
1)主键索引:
MyISAM索引文件和数据文件是分离的,索引文件仅保存数据记录的地址。而在InnoDB中,表数据文件本身就是按B+Tree组织的一个索引结构,这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键,因此InnoDB表数据文件本身就是主索引。
(图inndb主键索引)是InnoDB主索引(同时也是数据文件)的示意图,可以看到叶节点包含了完整的数据记录。这种索引叫做聚集索引。因为InnoDB的数据文件本身要按主键聚集,所以InnoDB要求表必须有主键(MyISAM可以没有),如果没有显式指定,则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键,如果不存在这种列,则MySQL自动为InnoDB表生成一个隐含字段作为主键,这个字段长度为6个字节,类型为长整形。
2)InnoDB的辅助索引
InnoDB的所有辅助索引都引用主键作为data域。例如,下图为定义在Col3上的一个辅助索引:
InnoDB 表是基于聚簇索引建立的。因此InnoDB 的索引能提供一种非常快速的主键查找性能。不过,它的辅助索引(Secondary Index, 也就是非主键索引)也会包含主键列,所以,如果主键定义的比较大,其他索引也将很大。如果想在表上定义 、很多索引,则争取尽量把主键定义得小一些。InnoDB 不会压缩索引。
文字符的ASCII码作为比较准则。聚集索引这种实现方式使得按主键的搜索十分高效,但是辅助索引搜索需要检索两遍索引:首先检索辅助索引获得主键,然后用主键到主索引中检索获得记录。
不同存储引擎的索引实现方式对于正确使用和优化索引都非常有帮助,例如知道了InnoDB的索引实现后,就很容易明白:
1、为什么不建议使用过长的字段作为主键,因为所有辅助索引都引用主索引,过长的主索引会令辅助索引变得过大。再例如,用非单调的字段作为主键在InnoDB中不是个好主意,因为InnoDB数据文件本身是一颗B+Tree,非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整,十分低效,而使用自增字段作为主键则是一个很好的选择。
2、InnoDB使用的是聚簇索引,将主键组织到一棵B+树中,而行数据就储存在叶子节点上,若使用"where id = 14"这样的条件查找主键,则按照B+树的检索算法即可查找到对应的叶节点,之后获得行数据。若对Name列进行条件搜索,则需要两个步骤:第一步在辅助索引B+树中检索Name,到达其叶子节点获取对应的主键。第二步使用主键在主索引B+树种再执行一次B+树检索操作,最终到达叶子节点即可获取整行数据。
4、MyISAM索引实现
MyISAM索引文件和数据文件是分离的,索引文件仅保存数据记录的地址
1)主键索引:
MyISAM引擎使用B+Tree作为索引结构,叶节点的data域存放的是数据记录的地址。下图是MyISAM主键索引的原理图:
这里设表一共有三列,假设我们以Col1为主键,图myisam1是一个MyISAM表的主索引(Primary key)示意。可以看出MyISAM的索引文件仅仅保存数据记录的地址。
2)辅助索引(Secondary key)
在MyISAM中,主索引和辅助索引(Secondary key)在结构上没有任何区别,只是主索引要求key是唯一的,而辅助索引的key可以重复。如果我们在Col2上建立一个辅助索引,则此索引的结构如下图所示:
同样也是一颗B+Tree,data域保存数据记录的地址。因此,MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引,如果指定的Key存在,则取出其data域的值,然后以data域的值为地址,读取相应数据记录。
MyISAM的索引方式也叫做“非聚集”的,之所以这么称呼是为了与InnoDB的聚集索引区分。
MyISM使用的是非聚簇索引,非聚簇索引的两棵B+树看上去没什么不同,节点的结构完全一致只是存储的内容不同而已,主键索引B+树的节点存储了主键,辅助键索引B+树存储了辅助键。表数据存储在独立的地方,这两颗B+树的叶子节点都使用一个地址指向真正的表数据,对于表数据来说,这两个键没有任何差别。由于索引树是独立的,通过辅助键检索无需访问主键的索引树。
为了更形象说明这两种索引的区别,我们假想一个表如下图存储了4行数据。其中Id作为主索引,Name作为辅助索引。图示清晰的显示了聚簇索引和非聚簇索引的差异。
5、补充问题
(1)innodb和myisam的主键索引是聚集索引还是非聚集索引?
在Innodb下主键索引是聚集索引,在Myisam下主键索引是非聚集索引
(2)如何使用聚簇索引?
聚簇索引需要具有唯一性,一般要根据这个表最常用的 SQL 查询方式来进行选择,某个字段作为聚簇索引,或组合聚簇索引。
聚簇索引默认是主键,如果表中没有定义主键,InnoDB 会选择一个唯一的非空索引代替。如果没有这样的索引,InnoDB 会隐式定义一个主键来作为聚簇索引。InnoDB 只聚集在同一个页面中的记录。包含相邻健值的页面可能相距甚远。
如果你想重建索引并且已经设置了主键为聚簇索引,必须先删除主键,然后添加我们想要的聚簇索引,最后恢复设置主键。
此时其他索引只能被定义为非聚簇索引。
(3)聚簇索引的优缺点?
优点
1. 数据访问更快,因为聚簇索引将索引和数据保存在同一个 B + 树中,因此从聚簇索引中获取数据比非聚簇索引更快
2. 聚簇索引对于主键的排序查找和范围查找速度非常快
缺点
1. 插入速度严重依赖于插入顺序,按照主键的顺序插入是最快的方式,否则将会出现页分裂,严重影响性能。因此,对于 InnoDB 表,我们一般都会定义一个自增的 ID 列为主键
2. 更新主键的代价很高,因为将会导致被更新的行移动。因此,对于 InnoDB 表,我们一般定义主键为不可更新。
3. 二级索引访问需要两次索引查找,第一次找到主键值,第二次根据主键值找到行数据。
(4)为什么主键建议使用自增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、在数据插入时,可以保证逻辑相邻的元素物理也相邻,便于范围查找。
(5)mysql使用自增id主键和uuid作为主键的优劣比较
(1)单实例或者单节点组:
经过 500W、1000W 的单机表测试,自增 ID 相对 UUID 来说,自增 ID 主键性能高于 UUID,磁盘存储费用比 UUID 节省一半的钱。所以在单实例上或者单节点组上,使用自增 ID 作为首选主键。
(2)分布式架构场景:
20 个节点组下的小型规模的分布式场景,为了快速实现部署,可以采用多花存储费用、牺牲部分性能而使用 UUID 主键快速部署;
20 到 200 个节点组的中等规模的分布式场景,可以采用自增 ID + 步长的较快速方案。
200 以上节点组的大数据下的分布式场景,可以借鉴类似 twitter 雪花算法构造的全局自增 ID 作为主键。
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步