聊聊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

相关文章

Ubuntu中远程连接Mysql数据库的详细图文教程

《Ubuntu中远程连接Mysql数据库的详细图文教程》Ubuntu是一个以桌面应用为主的Linux发行版操作系统,这篇文章主要为大家详细介绍了Ubuntu中远程连接Mysql数据库的详细图文教程,有... 目录1、版本2、检查有没有mysql2.1 查询是否安装了Mysql包2.2 查看Mysql版本2.

基于SpringBoot+Mybatis实现Mysql分表

《基于SpringBoot+Mybatis实现Mysql分表》这篇文章主要为大家详细介绍了基于SpringBoot+Mybatis实现Mysql分表的相关知识,文中的示例代码讲解详细,感兴趣的小伙伴可... 目录基本思路定义注解创建ThreadLocal创建拦截器业务处理基本思路1.根据创建时间字段按年进

Python3.6连接MySQL的详细步骤

《Python3.6连接MySQL的详细步骤》在现代Web开发和数据处理中,Python与数据库的交互是必不可少的一部分,MySQL作为最流行的开源关系型数据库管理系统之一,与Python的结合可以实... 目录环境准备安装python 3.6安装mysql安装pymysql库连接到MySQL建立连接执行S

MySQL双主搭建+keepalived高可用的实现

《MySQL双主搭建+keepalived高可用的实现》本文主要介绍了MySQL双主搭建+keepalived高可用的实现,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,... 目录一、测试环境准备二、主从搭建1.创建复制用户2.创建复制关系3.开启复制,确认复制是否成功4.同

MyBatis 动态 SQL 优化之标签的实战与技巧(常见用法)

《MyBatis动态SQL优化之标签的实战与技巧(常见用法)》本文通过详细的示例和实际应用场景,介绍了如何有效利用这些标签来优化MyBatis配置,提升开发效率,确保SQL的高效执行和安全性,感... 目录动态SQL详解一、动态SQL的核心概念1.1 什么是动态SQL?1.2 动态SQL的优点1.3 动态S

Mysql表的简单操作(基本技能)

《Mysql表的简单操作(基本技能)》在数据库中,表的操作主要包括表的创建、查看、修改、删除等,了解如何操作这些表是数据库管理和开发的基本技能,本文给大家介绍Mysql表的简单操作,感兴趣的朋友一起看... 目录3.1 创建表 3.2 查看表结构3.3 修改表3.4 实践案例:修改表在数据库中,表的操作主要

mysql出现ERROR 2003 (HY000): Can‘t connect to MySQL server on ‘localhost‘ (10061)的解决方法

《mysql出现ERROR2003(HY000):Can‘tconnecttoMySQLserveron‘localhost‘(10061)的解决方法》本文主要介绍了mysql出现... 目录前言:第一步:第二步:第三步:总结:前言:当你想通过命令窗口想打开mysql时候发现提http://www.cpp

MySQL大表数据的分区与分库分表的实现

《MySQL大表数据的分区与分库分表的实现》数据库的分区和分库分表是两种常用的技术方案,本文主要介绍了MySQL大表数据的分区与分库分表的实现,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有... 目录1. mysql大表数据的分区1.1 什么是分区?1.2 分区的类型1.3 分区的优点1.4 分

MySQL错误代码2058和2059的解决办法

《MySQL错误代码2058和2059的解决办法》:本文主要介绍MySQL错误代码2058和2059的解决办法,2058和2059的错误码核心都是你用的客户端工具和mysql版本的密码插件不匹配,... 目录1. 前置理解2.报错现象3.解决办法(敲重点!!!)1. php前置理解2058和2059的错误

Mysql删除几亿条数据表中的部分数据的方法实现

《Mysql删除几亿条数据表中的部分数据的方法实现》在MySQL中删除一个大表中的数据时,需要特别注意操作的性能和对系统的影响,本文主要介绍了Mysql删除几亿条数据表中的部分数据的方法实现,具有一定... 目录1、需求2、方案1. 使用 DELETE 语句分批删除2. 使用 INPLACE ALTER T