我的编程空间,编程开发者的网络收藏夹
学习永远不晚

MySQL之聚簇索引与非聚簇索引

短信预约 -IT技能 免费直播动态提醒
省份

北京

  • 北京
  • 上海
  • 天津
  • 重庆
  • 河北
  • 山东
  • 辽宁
  • 黑龙江
  • 吉林
  • 甘肃
  • 青海
  • 河南
  • 江苏
  • 湖北
  • 湖南
  • 江西
  • 浙江
  • 广东
  • 云南
  • 福建
  • 海南
  • 山西
  • 四川
  • 陕西
  • 贵州
  • 安徽
  • 广西
  • 内蒙
  • 西藏
  • 新疆
  • 宁夏
  • 兵团
手机号立即预约

请填写图片验证码后获取短信验证码

看不清楚,换张图片

免费获取短信验证码

MySQL之聚簇索引与非聚簇索引

索引属于存储引擎级别的概念,不同存储引擎对索引的实现方式是不同的,主要讨论InnoDB和MyISAM两个存储引擎的索引实现方式。

聚簇索引并不是一种单独的索引类型,而是一种数据存储方式。具体细节依赖于其实现方式。

  • 定义:索引中键值的逻辑顺序与数据行的物理顺序相同,一个表中只能有一个聚簇索引。

就比如,聚集索引就像是新华字典的拼音目录,而每个字存放的页码就是我们的数据物理地址;拼音目录对应的A-Z的字顺序,和字典实际存储的字的顺序A-Z也是一样的。

  • 定义:索引的逻辑顺序与磁盘上行的物理存储顺序不同,一个表中可以拥有多个非聚集索引。

非聚集索引也叫辅助索引就像新华字典的偏旁字典,他结构顺序与实际存放顺序不一定一致。

辅助索引访问数据总是需要二次查找。辅助索引叶子节点存储的是主键值。通过辅助索引首先找到的是主键值,再通过主键值找到数据行的数据页,再通过数据页中的Page Directory找到数据行。

  • 何时使用聚簇索引与非聚簇索引
    在这里插入图片描述
  • 聚簇索引和非聚簇索引的关系
    聚簇索引是将数据存储与索引放到了一块,索引结构的叶子节点保存了行数据。
    非聚簇索引是将数据与索引分开存储,索引结构的叶子节点指向了数据对应的位置。
    聚簇索引是物理有序的;非聚簇索引是逻辑有序,物理无序,在mysql中数据存储顺序就是聚簇索引的顺序,所以一个表只有一个聚簇索引,其他索引都是非聚簇的
  • 聚簇索引的优缺点
      优点:
        1.数据访问更快,因为聚簇索引将索引和数据保存在同一个B+树中,因此从聚簇索引中获取数据比非聚簇索引更快
        2.聚簇索引对于主键的排序查找和范围查找速度非常快
      缺点:
        1.插入速度严重依赖于插入顺序,按照主键的顺序插入是最快的方式,否则将会出现页分裂,严重影响性能。因此,对于InnoDB表,我们一般都会定义一个自增的ID列为主键
        2.更新主键的代价很高,因为将会导致被更新的行移动。因此,对于InnoDB表,我们一般定义主键为不可更新。
        3.二级索引访问需要两次索引查找,第一次找到主键值,第二次根据主键值找到行数据。
    MySQL之聚簇索引与非聚簇索引

InnoDB索引实现

InnoDB使用B+Tree作为索引结构。

  • InnoDB的主键索引
    表数据文件本身就是按B+Tree组织的一个索引结构,聚簇索引就是按照每张表的主键构造一棵B+树,同时叶子节点中存放的就是整张表的行记录数据,也将聚集索引的叶子节点称为数据页。这个特性决定了索引组织表中数据也是索引的一部分;
    Innodb通过主键聚集数据,如果没有定义主键,innodb会选择非空的唯一索引代替。如果没有这样的索引,innodb会隐式的定义一个主键来作为聚簇索引。如果你已经设置了主键为聚簇索引,必须先删除主键,然后添加我们想要的聚簇索引,最后恢复设置主键即可。
    在这里插入图片描述
     (图inndb主键索引)是InnoDB主索引(同时也是数据文件)的示意图,可以看到叶节点包含了完整的数据记录。这种索引叫做聚集索引。因为InnoDB的数据文件本身要按主键聚集,所以InnoDB要求表必须有主键(MyISAM可以没有),如果没有显式指定,则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键,如果不存在这种列,则MySQL自动为InnoDB表生成一个隐含字段作为主键,这个字段长度为6个字节,类型为长整形。

  • InnoDB的辅助索引
    InnoDB的所有辅助索引都引用主键作为data域。例如:
    在这里插入图片描述
    InnoDB 表是基于聚簇索引建立的。因此InnoDB 的索引能提供一种非常快速的主键查找性能。不过,它的辅助索引(Secondary Index, 也就是非主键索引)也会包含主键列,所以,如果主键定义的比较大,其他索引也将很大。如果想在表上定义 、很多索引,则争取尽量把主键定义得小一些。InnoDB 不会压缩索引。

InnoDB的主键索引的叶子节点存储的是行数据,辅助索引的叶子节点存储的是主键列和索引。

MyISAM索引实现

MyISAM索引文件和数据文件是分离的,索引文件仅保存数据记录的地址

  • MyISAM的主键索引
    MyISAM引擎也使用B+Tree作为索引结构,叶节点的data域存放的是数据记录的地址。下图是MyISAM主键索引的原理图:
    在这里插入图片描述
  • MyISAM的辅助索引
    在MyISAM中,主索引和辅助索引(Secondary key)在结构上没有任何区别,只是主索引要求key是唯一的,而辅助索引的key可以重复:
    在这里插入图片描述
    同样也是一颗B+Tree,data域保存数据记录的地址。因此,MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引,如果指定的Key存在,则取出其data域的值,然后以data域的值为地址,读取相应数据记录。

MyISM使用的是非聚簇索引,非聚簇索引的两棵B+树看上去没什么不同,节点的结构完全一致只是存储的内容不同而已,主键索引B+树的节点存储了主键,辅助键索引B+树存储了辅助键。表数据存储在独立的地方,这两颗B+树的叶子节点都使用一个地址指向真正的表数据,对于表数据来说,这两个键没有任何差别。由于索引树是独立的,通过辅助键检索无需访问主键的索引树。

我们假想一个表如下图存储了4行数据。其中Id作为主索引,Name作为辅助索引。图示清晰的显示了聚簇索引和非聚簇索引的差异。
在这里插入图片描述

InnoDB中为什么不建议使用过长的字段作为主键,因为所有辅助索引都引用主索引,过长的主索引会令辅助索引变得过大。

用非单调的字段作为主键在InnoDB中不是个好主意,因为InnoDB数据文件本身是一颗B+Tree,非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整,十分低效,而使用自增字段作为主键则是一个很好的选择。

主键索引是聚集索引还是非聚集索引?
在Innodb下主键索引是聚集索引,在Myisam下主键索引是非聚集索引

为什么主键通常建议使用自增id
聚簇索引的数据的物理存放顺序与索引顺序是一致的,即:只要索引是相邻的,那么对应的数据一定也是相邻地存放在磁盘上的。如果主键不是自增id,那么可以想 象,它会干些什么,不断地调整数据的物理地址、分页,当然也有其他一些措施来减少这些操作,但却无法彻底避免。但,如果是自增的,那就简单了,它只需要一 页一页地写,索引结构相对紧凑,磁盘碎片少,效率也高。

参考:
聚簇索引和非聚簇索引详解
聚簇索引和非聚簇索引

来源地址:https://blog.csdn.net/weixin_43851772/article/details/129684626

免责声明:

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

MySQL之聚簇索引与非聚簇索引

下载Word文档到电脑,方便收藏和打印~

下载Word文档

猜你喜欢

MySQL之聚簇索引与非聚簇索引

MySQL之聚簇索引与非聚簇索引 聚簇索引非聚簇索引聚簇索引和非聚簇索引的区分MySQL索引实现InnoDB索引实现MyISAM索引实现 问答 索引属于存储引擎级别的概念,不同存储引擎对索引的实现方式是不同的,主要讨论Inn
2023-08-30

详解MySQL 聚簇索引与非聚簇索引

1、聚集索引 表数据按照索引的顺序来存储的,也就是说索引项的顺序与表中记录的物理顺序一致。对于聚集索引,叶子结点即存储了真实的数据行,不再有另外单独的数据页。 在一张表上最多只能创建一个聚集索引,因为真实数据的物理顺序只能有一种。 从物理文
2022-05-14

mysql聚簇索引和非聚簇索引区别

聚簇索引的行按索引键顺序物理存储,非聚簇索引的行不按顺序存储且包含指向数据行的指针。聚簇索引提高范围和等值查询性能,每个表只能有一个;非聚簇索引允许在多个列上创建索引,每个表可以有多个。MySQL 聚簇索引和非聚簇索引的区别聚簇索引和非聚
mysql聚簇索引和非聚簇索引区别
2024-08-01

MySQL中聚簇索引与非聚簇索引的实现

目录基本概念和作用说明聚簇索引非聚簇索引示例一:创建索引示例二:索引的选择示例三:索引的维护示例四:索引与性能http://www.lsjlt.com优化示例五:索引的限制结论与讨论引发点在mysql数据库中,索引是提高查询性能的关键工具。
MySQL中聚簇索引与非聚簇索引的实现
2024-09-18

MySQL聚簇索引和非聚簇索引的区别详情

目录聚簇索引非聚簇索引总结前言:在 mysql 默认引擎 InnoDB 中,索引大致可分为两类:聚簇索引和非聚簇索引,它们的区别也是常见的面试题,所以我们今天就来盘它们。聚簇索引聚簇索引(Clustered Index)一般指的是主键
2022-06-14

mysql聚簇索引和非聚簇索引有什么区别

MySQL中的聚簇索引和非聚簇索引是两种不同的索引类型,它们在存储和查询数据时有一些区别:聚簇索引:聚簇索引将数据行存储在索引的叶子节点中,而不是单独的数据页中。这意味着索引本身就是数据的一部分,可以通过索引直接访问数据,而不需要额外的查找
mysql聚簇索引和非聚簇索引有什么区别
2024-04-09

mysql聚簇索引和非聚簇索引有什么不同

MySQL中的聚簇索引和非聚簇索引有以下不同点:1. 存储方式:聚簇索引中,数据行的顺序与索引的顺序一致,索引直接指向数据行。非聚簇索引中,索引是独立于数据行的,索引中的每个条目都包含一个指向对应数据行的指针。2. 数据访问:使用聚簇索引进
2023-09-15

MySQL聚簇索引和非聚簇索引的区别是什么

这篇文章主要介绍“MySQL聚簇索引和非聚簇索引的区别是什么”,在日常操作中,相信很多人在MySQL聚簇索引和非聚簇索引的区别是什么问题上存在疑惑,小编查阅了各式资料,整理出简单好用的操作方法,希望对大家解答”MySQL聚簇索引和非聚簇索引
2023-07-02

MySQL中的聚簇索引、非聚簇索引、联合索引和唯一索引是什么

今天小编给大家分享一下MySQL中的聚簇索引、非聚簇索引、联合索引和唯一索引是什么的相关知识点,内容详细,逻辑清晰,相信大部分人都还太了解这方面的知识,所以分享这篇文章给大家参考一下,希望大家阅读完这篇文章后有所收获,下面我们一起来了解一下
2023-04-21

解开索引迷局:聚簇索引与非聚簇索引的差异大揭秘!

今天我们对聚簇索引和非聚簇索引进行了详细的解释,并通过电商实际案例来说明它们的应用场景。希望这篇文章能够帮助大家更好地理解索引在数据库中的作用和应用。

MySQL中的聚簇索引、非聚簇索引、联合索引和唯一索引详细介绍

目录一、索引类型二、聚簇索引和非聚簇索引三、覆盖索引四、联合索引最左前缀匹配原则索引下推五、唯一索引一、索引类型索引根据底层实现可分为B-Tree索引和哈希索引,大部分时候我们使用的都是B-Tree索引,因为它良好的性能和特性更适合于构建
2023-04-19

【SQL应知应会】索引(三)• MySQL版:聚簇索引与非聚簇索引;查看索引与删除索引;索引方法

欢迎来到爱书不爱输的程序猿的博客, 本博客致力于知识分享,与更多的人进行学习交流 本文收录于SQL应知应会专栏,本专栏主要用于记录对于数据库的一些学习,有基础也有进阶,有MySQL也有Oracle 索引 • MySQL
2023-08-24

什么是聚簇索引和非聚簇索引,如何理解回表、索引下推

如果没有使用索引下推技术,MySQL 会通过 zipcode=\'95054\'​从存储引擎中查询对应的数据,然后将结果返回到 MySQL 服务端,接着 MySQL 服务端再基于lastname LIKE \'%etrunia%\' 和 addre

浅谈MySQL聚簇索引

目录1. 什么是聚簇索引2. 聚编程客栈簇索引和主键3. 聚簇索引优缺点4. 最佳实践1. 什么是聚簇索引数据库的索引从不同的角度可以划分成不同的类型,聚簇索引便是其中一种。聚簇索引英文是 Clustered Ind编程客栈ex,有时候
2023-04-19

再聊 MySQL 聚簇索引

聚簇索引英文是 Clustered Index,有时候小伙伴们可能也会看到有人将之称为聚集索引等,与之相对的是非聚簇索引或者二级索引。

编程热搜

目录