聊聊MySQL的聚簇索引和非聚簇索引

2023-10-07 13:45

本文主要是介绍聊聊MySQL的聚簇索引和非聚簇索引,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

文章目录

  • 1. 索引的分类
    • 1. 存储结构维度
    • 2. 功能维度
    • 3. 列数维度
    • 4. 存储方式维度
    • 5. 更新方式维度
  • 2. 聚簇索引
    • 2.1 什么是聚簇索引
    • 2.2 聚簇索引的工作原理
  • 3. 非聚簇索引(MySQL官方文档称为`Secondary Indexes`)
    • 3.1 什么是非聚簇索引
    • 3.2 非聚簇索引的工作原理
  • 4. 聚簇索引与非聚簇索引的区别

在这里插入图片描述
MySQL的聚簇索引和非聚簇索引翻译为中文也叫聚集索引,非聚集索引。英文有两种叫法 Clustered indexnon-Clustered index 。MySQL官方对 非聚集索引称之为 Secondary Indexes。所以遇到 Secondary Indexesnon-Clustered index等价。
MySQL官方文档介绍在 《15.6.2.1 Clustered and Secondary Indexes》https://dev.mysql.com/doc/refman/8.0/en/innodb-index-types.html

1. 索引的分类

在了解聚簇索引和非聚簇索引之前,我们先对数据库索引的分类进行一个了解,不然繁杂的概念和分类会使得搞混,通常我们可以听到B-Tree索引、全文索引 、复合索引、 聚簇索引 、静态索引,其实这些描述都是站在不同维度的描述,B-Tree索引是站在存储结构的维度,全文索引是站在功能维度描述,所以我们先了解一下不同维度的索引描述。
以下是常见的一些维度:

1. 存储结构维度

索引类型描述适用场景
B-Tree索引基于平衡多路搜索树的索引结构,维护数据有序性范围查询、排序操作
Hash索引基于哈希表的索引结构,适合等值查询等值查询
R-Tree索引适用于存储和查询空间数据的树形结构地理空间数据的查询
Bitmap索引适用于低基数列的索引,占用空间小且查询效率高低基数列(不同值的数目较少)的索引

2. 功能维度

索引类型描述适用场景
普通索引基本的索引类型,无任何限制通用
唯一索引类似于普通索引,要求索引列的值必须唯一,通常用于主键确保索引列的唯一性
全文索引用于全文搜索全文搜索
空间索引用于地理空间数据的索引地理空间数据的查询

3. 列数维度

索引类型描述
单列索引仅包含一个字段的索引
复合索引包含多个字段的索引,可以是普通索引、唯一索引等

这些索引类型的选择取决于需要索引的列的组合和查询需求。复合索引可以更好地支持涉及多个列的查询,但需要权衡索引的大小和维护成本。

4. 存储方式维度

聚簇索引将数据行直接存储在索引中,因此范围查询和排序操作效率较高,而非聚簇索引则需要通过指针访问数据行。选择适当的索引类型取决于数据访问模式和查询需求。

索引类型描述
聚簇索引数据行存储在索引中,数据行的物理顺序与索引中的键值顺序一致
非聚簇索引索引中的键值顺序与数据行的物理顺序不一致,索引中包含指向数据行的指针

5. 更新方式维度

索引类型描述
静态索引仅在数据被插入、删除或更新时更新索引
动态索引在查询时实时更新索引

静态索引在数据被修改时才更新,因此可能存在索引与实际数据不一致的情况。它适用于数据变动较少的场景,可以提高插入、删除和更新操作的性能。动态索引则在查询时实时更新,确保索引与实际数据保持一致,适用于频繁变动的数据环境,但可能对写入操作的性能有一定影响。选择适当的索引类型应考虑数据变动频率和查询性能需求。

本章我们着重了解聚簇索引和非聚簇索引的工作原理

2. 聚簇索引

2.1 什么是聚簇索引

聚簇索引是一种特殊类型的索引,在存储引擎中,数据记录实际的存放方式会根据聚簇索引来组织。一个表中只能有一个聚簇索引,但可以有多个非聚簇索引。

在许多数据库系统中,聚簇索引通常就是主键索引。例如:

  1. 在MySQL的InnoDB引擎中,聚簇索引默认是主键,如果没有定义主键,MySQL会选择一个非空唯一索引代替,如果没有非空唯一索引,MySQL会自动创建一个隐藏的聚簇索引。

  2. 在SQL Server中,也可以选择用哪个列作为聚簇索引,但一般推荐使用主键。

  3. 在Oracle中,可以明确指定创建聚簇索引。

虽然聚簇索引在很多情况下被设置为主键,但并不意味着聚簇索引一定是主键。聚簇索引应该选择最能代表数据存储特征的那一列或几列,例如,如果一个表的数据经常按照某一列的顺序进行查找,那么这一列就非常适合做聚簇索引。
在这里插入图片描述

2.2 聚簇索引的工作原理

MySQL 8中的聚簇索引原理与之前版本类似,主要的工作原理是基于B+树数据结构进行排序和检索操作。

对于MySQL的InnoDB存储引擎,聚簇索引是按照主键的顺序来存储数据的。这意味着每个表只能有一个聚簇索引,如果没有明确指定主键,InnoDB会自动选择一个能作为主键的列或者自行生成一个。

以下是聚簇索引的工作原理:

  1. 查询操作:当执行查询操作时,InnoDB引擎会利用B+树的特性,从根节点开始,通过比较索引的键值找到对应的叶子节点(数据页),从而快速找到需要的数据。因为索引的键值和数据是在一起的,所以查询效率非常高。

  2. 插入和删除操作:当进行插入或删除操作时,InnoDB引擎需要找到对应的索引键值,然后在对应的位置插入新的数据或删除旧的数据。因为数据是按照键的顺序存储的,所以插入和删除操作可能会引发数据的移动,尤其是在插入时如果插入的数据键值在当前键值范围内则可能会触发数据页的分裂。

  3. 更新操作:当进行更新操作时,如果更新的是非索引列,那么只需定位到数据页并进行更新即可;但是如果更新的是索引列,那么可能会引发数据的移动,因为要保持数据的有序性。

设计聚簇索引时要尽可能选择稳定且不频繁变动的列作为主键,这样可以减少因为插入、删除和更新操作引发的数据移动,提高数据库的性能。

聚簇索引可以提高大多数查询操作的性能,因为它们为数据提供了更线性的访问路径,数据存储在页面中。此外,由于具有相似索引键值的行存储在一起,使用聚簇索引时,顺序检测预取更高效。
在这里插入图片描述

示例
假设我们有一个学生信息表,我们可以通过以下SQL语句创建这个表,并且设置id为主键,也就是聚簇索引:

CREATE TABLE students (id INT PRIMARY KEY,name VARCHAR(100),age INT
);

假设我们现在要查询id为100的学生的信息,我们可以通过以下SQL语句进行查询:

SELECT * FROM students WHERE id = 100;

在执行这个查询操作时,因为id是聚簇索引,所以MySQL会通过B+树的检索算法,从根节点开始,比较索引的键值,找到对应的叶子节点(数据页),然后读取该数据页,找到id为100的学生的信息,这个过程的效率非常高。

同样地,如果我们要更新id为100的学生的年龄,我们可以通过以下SQL语句进行更新:

UPDATE students SET age = 20 WHERE id = 100;

在执行这个更新操作时,MySQL也会先通过聚簇索引找到id为100的学生的信息,然后直接在数据页上进行更新操作。如果更新的是非索引列(在这个例子中,是age列),那么更新操作的效率也是非常高的。

如果更新的是索引列,那么可能会引发数据的移动,因为要保持数据的有序性。例如,如果我们要改变id为100的学生的id,那么可能就会引发数据的移动,因此,设计聚簇索引时,我们应该尽可能选择稳定且不频繁变动的列作为主键。

3. 非聚簇索引(MySQL官方文档称为Secondary Indexes

MySQL官方文档介绍在《15.6.2.1 Clustered and Secondary Indexes》

3.1 什么是非聚簇索引

非聚簇索引,也被称为二级索引或辅助索引,它的工作方式与聚簇索引有所不同。在非聚簇索引中,索引的逻辑顺序与磁盘上行的物理存储顺序不同。换句话说,非聚簇索引的逻辑顺序是索引的键值顺序,但是这个顺序并不等于数据在磁盘上的物理存储顺序。

在非聚簇索引中,每一个索引条目都包含了键值和一个指向该键值对应的数据行的指针。这个指针通常是数据行的物理地址或者是一个指向数据行的其他种类的标识符。

一个表可以有多个非聚簇索引。当查询不包含聚簇索引的列时,数据库系统会使用非聚簇索引来提高查询性能。

比如在一个员工表中,聚簇索引可能会基于员工的ID进行设置,而非聚簇索引可能会基于员工的姓名或者部门来设置。这样当查询姓名或者部门时,数据库系统就可以直接利用非聚簇索引进行查找,而不需要扫描整张表,从而提高了查询效率。
在这里插入图片描述

3.2 非聚簇索引的工作原理

非聚簇索引的工作原理与聚簇索引有些不同。非聚簇索引(也称为二级索引或辅助索引)并不会影响表中数据的物理存储顺序,而是创建一个单独的数据结构(通常是B+树)来存储索引列的值和对应的行指针。如上图示例。

非聚簇索引的工作原理:

  1. 查询操作:当执行查询操作时,数据库会从非聚簇索引的B+树的根节点开始,通过比较索引的键值找到对应的叶子节点。这个叶子节点包含了键值和一个指向该键值对应的数据行的指针。数据库通过这个指针找到实际的数据行。这个过程通常需要两次磁盘I/O操作,第一次是在索引上进行查找,第二次是通过找到的指针去数据文件中获取实际的数据行。

  2. 插入和删除操作:当进行插入或删除操作时,数据库需要同时在索引结构和数据文件中进行操作。首先,数据库会在索引结构中插入或删除对应的键值和行指针,然后在数据文件中插入或删除实际的数据行。

  3. 更新操作:当进行更新操作时,如果更新的是非索引列,那么数据库只需要在数据文件中更新对应的数据行即可;如果更新的是索引列,那么数据库需要同时在索引结构和数据文件中进行更新操作。

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

聚簇索引非聚簇索引
查询速度通常较快,因为可以直接定位到数据较慢,因为需要先定位到索引,然后再通过索引找到数据
内存使用使用的内存较少,因为数据和索引在一起使用的内存较多,因为数据和索引是分开的
数据存储聚簇索引就是主数据,数据按照索引排序非聚簇索引是数据的一份索引,数据的物理排序与索引无关
索引数量一个表只能有一个聚簇索引一个表可以有多个非聚簇索引
数据存储能力聚簇索引存储数据本身非聚簇索引存储数据的指针,并不存储数据本身
存储内容聚簇索引存储实际的数据行非聚簇索引存储索引列和行指针
叶节点内容在聚簇索引中,叶节点就是实际的数据在非聚簇索引中,叶节点不是实际的数据,而只包含索引和行指针
数据顺序在聚簇索引中,数据物理存储的顺序与索引的顺序一致在非聚簇索引中,数据的物理存储顺序与索引顺序无关
索引类型聚簇索引是一种将表记录物理排序以匹配索引的索引类型非聚簇索引是一种索引的逻辑顺序与数据在磁盘上的物理存储顺序无关的索引类型
索引大小主聚簇索引的大小一般较大相对而言,非聚簇索引的大小较小

这篇关于聊聊MySQL的聚簇索引和非聚簇索引的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/158139

相关文章

MySQL 中的 JSON 查询案例详解

《MySQL中的JSON查询案例详解》:本文主要介绍MySQL的JSON查询的相关知识,本文给大家介绍的非常详细,对大家的学习或工作具有一定的参考借鉴价值,需要的朋友参考下吧... 目录mysql 的 jsON 路径格式基本结构路径组件详解特殊语法元素实际示例简单路径复杂路径简写操作符注意MySQL 的 J

Windows 上如果忘记了 MySQL 密码 重置密码的两种方法

《Windows上如果忘记了MySQL密码重置密码的两种方法》:本文主要介绍Windows上如果忘记了MySQL密码重置密码的两种方法,本文通过两种方法结合实例代码给大家介绍的非常详细,感... 目录方法 1:以跳过权限验证模式启动 mysql 并重置密码方法 2:使用 my.ini 文件的临时配置在 Wi

MySQL重复数据处理的七种高效方法

《MySQL重复数据处理的七种高效方法》你是不是也曾遇到过这样的烦恼:明明系统测试时一切正常,上线后却频频出现重复数据,大批量导数据时,总有那么几条不听话的记录导致整个事务莫名回滚,今天,我就跟大家分... 目录1. 重复数据插入问题分析1.1 问题本质1.2 常见场景图2. 基础解决方案:使用异常捕获3.

SQL中redo log 刷⼊磁盘的常见方法

《SQL中redolog刷⼊磁盘的常见方法》本文主要介绍了SQL中redolog刷⼊磁盘的常见方法,将redolog刷入磁盘的方法确保了数据的持久性和一致性,下面就来具体介绍一下,感兴趣的可以了解... 目录Redo Log 刷入磁盘的方法Redo Log 刷入磁盘的过程代码示例(伪代码)在数据库系统中,r

mysql中的group by高级用法

《mysql中的groupby高级用法》MySQL中的GROUPBY是数据聚合分析的核心功能,主要用于将结果集按指定列分组,并结合聚合函数进行统计计算,下面给大家介绍mysql中的groupby用法... 目录一、基本语法与核心功能二、基础用法示例1. 单列分组统计2. 多列组合分组3. 与WHERE结合使

Mysql用户授权(GRANT)语法及示例解读

《Mysql用户授权(GRANT)语法及示例解读》:本文主要介绍Mysql用户授权(GRANT)语法及示例,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录mysql用户授权(GRANT)语法授予用户权限语法GRANT语句中的<权限类型>的使用WITH GRANT

C# foreach 循环中获取索引的实现方式

《C#foreach循环中获取索引的实现方式》:本文主要介绍C#foreach循环中获取索引的实现方式,本文给大家介绍的非常详细,对大家的学习或工作具有一定的参考借鉴价值,需要的朋友参考下吧... 目录一、手动维护索引变量二、LINQ Select + 元组解构三、扩展方法封装索引四、使用 for 循环替代

Mysql如何解决死锁问题

《Mysql如何解决死锁问题》:本文主要介绍Mysql如何解决死锁问题,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录【一】mysql中锁分类和加锁情况【1】按锁的粒度分类全局锁表级锁行级锁【2】按锁的模式分类【二】加锁方式的影响因素【三】Mysql的死锁情况【1

SQL BETWEEN 的常见用法小结

《SQLBETWEEN的常见用法小结》BETWEEN操作符是SQL中非常有用的工具,它允许你快速选取某个范围内的值,本文给大家介绍SQLBETWEEN的常见用法,感兴趣的朋友一起看看吧... 在SQL中,BETWEEN是一个操作符,用于选取介于两个值之间的数据。它包含这两个边界值。BETWEEN操作符常用

MySQL索引的优化之LIKE模糊查询功能实现

《MySQL索引的优化之LIKE模糊查询功能实现》:本文主要介绍MySQL索引的优化之LIKE模糊查询功能实现,本文通过示例代码给大家介绍的非常详细,感兴趣的朋友一起看看吧... 目录一、前缀匹配优化二、后缀匹配优化三、中间匹配优化四、覆盖索引优化五、减少查询范围六、避免通配符开头七、使用外部搜索引擎八、分