【面试题】MySQL的聚簇索引与非聚簇索引与主键索引:深入理解与应用

本文主要是介绍【面试题】MySQL的聚簇索引与非聚簇索引与主键索引:深入理解与应用,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

文章目录

        • 引言
        • 基础知识
        • 核心概念
        • 引擎上的区别
          • InnoDB
          • MyISAM
        • 示例演示
        • 实际应用
        • 深入与最佳实践
        • 常见问题解答
        • 结语
        • 学习资源
        • 互动环节

引言

聚簇索引、非聚簇索引和主键索引的有什么区别你知道吗

在数据库设计中,索引是提高查询性能的关键。MySQL中的聚簇索引和非聚簇索引是两种不同的索引类型,它们在数据存储和检索方面有着显著的差异。理解这些差异对于优化数据库性能至关重要。

基础知识
  • 聚簇索引(Clustered Index):数据行的物理顺序与索引顺序相同的索引。
  • 非聚簇索引(Non-Clustered Index):数据行的物理顺序与索引顺序不同的索引。
核心概念
  • 数据存储:聚簇索引将数据存储与索引结构结合在一起,非聚簇索引则将数据存储与索引分开。
  • 查询性能:聚簇索引通常在查询时提供更快的访问速度,非聚簇索引可能需要额外的查找步骤。

聚簇索引、非聚簇索引和主键索引的主要区别
在于它们的存储方式、数据组织结构以及查询效率。‌

  • ‌聚簇索引‌(Clustered Index):
  • 聚簇索引实际上并不是一种单独的索引类型,而是一种数据存储方式。在InnoDB存储引擎中,聚簇索引的叶子节点包含了完整的记录行,这意味着表的数据行都存放在索引树的叶子页中。
  • 由于聚簇索引决定了表中数据的物理存储顺序,因此一张表只能有一个聚簇索引。InnoDB的聚簇索引实际上是将索引和数据保存在同一个B-Tree中,如果没有定义主键,InnoDB会选择一个合适的列作为聚簇索引,如果找不到合适的列,会使用一列隐藏的列DB_ROW_ID作为聚簇索引‌.
  • 非聚簇索引‌( n-clustered Index):
  • 非聚簇索引,也称为二级索引或辅助索引,其叶子节点仅包含主键值,而不包含完整的记录信息。通过非聚簇索引查找记录时,需要先通过非聚簇索引找到主键值,然后再通过主键值到聚簇索引中找到对应的记录行,这个过程称为回表查询。由于非聚簇索引的逻辑顺序与磁盘上行的物理存储顺序不同,因此一个表中可以拥有多个非聚簇索引。非聚簇索引的叶子节点中保存的不是指向行的物理指针,而是行的主键值,这有助于减少移动数据或分裂时维护非聚簇索引的开销‌
  • 主键索引‌:

主键索引是一种特殊的聚簇索引,它确保了数据的唯一性。在关系数据库中,主键是唯一标识表中每一行的列。当为表设置主键时,MySQL会自动为该主键创建一个聚簇索引。因此,主键索引既是聚簇索引的一种,也是数据库表中数据组织的核心‌

面试常问:聚簇索引不一定是主键索引,而主键索引一定是聚簇索引。

可以理解成在聚簇索引上建立的索引,都是非聚簇索引(也称为二级索引或辅助索引)。因为一个表中只能有一个聚簇索引,其他都是非聚簇索引。

引擎上的区别
InnoDB

使用的是聚簇索引,比如若使用“where id=5“的条件查找主键,则按照B+树的检索算法,即可查找到对应的叶子节点,之后获得行数据。

若对employee_name 列进行条件搜索,则需要两个步骤:
1.在辅助索引B+树中检索非主键列(eg:employee_name ),到达其叶子节点获取对应的主键。
2.用主键在主索引B+树种再执行一次B+树检索操作,最终到达叶子节点获取整行数据。(重点在于通过其他键需要建立辅助索引)

聚簇索引默认是主键,若表中无定义主键,InnoDB会选择一唯一且非空的索引代替。若无这样的索引,InnoDB会隐式定义一个主键(类似 oracle中的Rowld,使用一列隐藏的列DB_ROW_ID作为聚簇索引‌)作为聚簇索引。

若已设了主键为聚簇索引又希望再单独设置聚簇索引,必须先删除原主键,然后添加,最后恢复设置主键即可。

MyISAM

便用的是非聚簇索引,只是存储的内容不同,主键索引B+树的节点存储了主键, 辅助键索引IB+树存储了辅助键。

表数据存储在独立的地方(MYN文件),这两颗B+树的叶子节点都使用一个地址指向真正的表数据,对于表数据来说,这两个键没有任何差别。
由于索引树是独立的,通过辅助键检索无需访问主键的索引树。

示例演示
  1. 聚簇索引的使用

    CREATE TABLE employees (employee_id INT PRIMARY KEY,employee_name VARCHAR(100),department_name VARCHAR(100)
    );
    

    在这个例子中,employee_id是聚簇索引,因为它直接存储了数据行。

  2. 非聚簇索引的使用

    CREATE INDEX idx_department ON employees (department_name);
    

    idx_department是非聚簇索引,因为它指向数据行的位置。

实际应用
  • 查询优化:使用聚簇索引可以减少查询时的数据访问次数。
  • 数据插入:聚簇索引可以减少数据插入时的页分裂。
深入与最佳实践
  • 选择合适的索引类型:根据查询模式和数据访问模式选择合适的索引类型。
  • 避免过度索引:过多的索引会降低数据修改的性能。
常见问题解答
  • Q: 为什么聚簇索引通常比非聚簇索引更快?
    A: 聚簇索引的数据存储与索引结构结合在一起,减少了额外的数据查找步骤。

  • Q: 我应该在什么情况下使用非聚簇索引?
    A: 当你需要基于非主键列进行频繁查询时,非聚簇索引是一个好的选择。

结语

聚簇索引和非聚簇索引在MySQL中有着不同的应用场景和性能特点。了解它们的差异和最佳实践,可以帮助你更好地设计数据库索引,提高查询性能。

学习资源
  • MySQL官方文档:Indexes
互动环节
  • 分享你在数据库索引设计和优化方面的经验。

这篇关于【面试题】MySQL的聚簇索引与非聚簇索引与主键索引:深入理解与应用的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



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

相关文章

Springboot中分析SQL性能的两种方式详解

《Springboot中分析SQL性能的两种方式详解》文章介绍了SQL性能分析的两种方式:MyBatis-Plus性能分析插件和p6spy框架,MyBatis-Plus插件配置简单,适用于开发和测试环... 目录SQL性能分析的两种方式:功能介绍实现方式:实现步骤:SQL性能分析的两种方式:功能介绍记录

使用 sql-research-assistant进行 SQL 数据库研究的实战指南(代码实现演示)

《使用sql-research-assistant进行SQL数据库研究的实战指南(代码实现演示)》本文介绍了sql-research-assistant工具,该工具基于LangChain框架,集... 目录技术背景介绍核心原理解析代码实现演示安装和配置项目集成LangSmith 配置(可选)启动服务应用场景

oracle DBMS_SQL.PARSE的使用方法和示例

《oracleDBMS_SQL.PARSE的使用方法和示例》DBMS_SQL是Oracle数据库中的一个强大包,用于动态构建和执行SQL语句,DBMS_SQL.PARSE过程解析SQL语句或PL/S... 目录语法示例注意事项DBMS_SQL 是 oracle 数据库中的一个强大包,它允许动态地构建和执行

SQL 中多表查询的常见连接方式详解

《SQL中多表查询的常见连接方式详解》本文介绍SQL中多表查询的常见连接方式,包括内连接(INNERJOIN)、左连接(LEFTJOIN)、右连接(RIGHTJOIN)、全外连接(FULLOUTER... 目录一、连接类型图表(ASCII 形式)二、前置代码(创建示例表)三、连接方式代码示例1. 内连接(I

在MySQL执行UPDATE语句时遇到的错误1175的解决方案

《在MySQL执行UPDATE语句时遇到的错误1175的解决方案》MySQL安全更新模式(SafeUpdateMode)限制了UPDATE和DELETE操作,要求使用WHERE子句时必须基于主键或索引... mysql 中遇到的 Error Code: 1175 是由于启用了 安全更新模式(Safe Upd

轻松上手MYSQL之JSON函数实现高效数据查询与操作

《轻松上手MYSQL之JSON函数实现高效数据查询与操作》:本文主要介绍轻松上手MYSQL之JSON函数实现高效数据查询与操作的相关资料,MySQL提供了多个JSON函数,用于处理和查询JSON数... 目录一、jsON_EXTRACT 提取指定数据二、JSON_UNQUOTE 取消双引号三、JSON_KE

MySql死锁怎么排查的方法实现

《MySql死锁怎么排查的方法实现》本文主要介绍了MySql死锁怎么排查的方法实现,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友们下面随着小编来一起学习学习吧... 目录前言一、死锁排查方法1. 查看死锁日志方法 1:启用死锁日志输出方法 2:检查 mysql 错误

MySQL数据库函数之JSON_EXTRACT示例代码

《MySQL数据库函数之JSON_EXTRACT示例代码》:本文主要介绍MySQL数据库函数之JSON_EXTRACT的相关资料,JSON_EXTRACT()函数用于从JSON文档中提取值,支持对... 目录前言基本语法路径表达式示例示例 1: 提取简单值示例 2: 提取嵌套值示例 3: 提取数组中的值注意

MySQL修改密码的四种实现方式

《MySQL修改密码的四种实现方式》文章主要介绍了如何使用命令行工具修改MySQL密码,包括使用`setpassword`命令和`mysqladmin`命令,此外,还详细描述了忘记密码时的处理方法,包... 目录mysql修改密码四种方式一、set password命令二、使用mysqladmin三、修改u

查询SQL Server数据库服务器IP地址的多种有效方法

《查询SQLServer数据库服务器IP地址的多种有效方法》作为数据库管理员或开发人员,了解如何查询SQLServer数据库服务器的IP地址是一项重要技能,本文将介绍几种简单而有效的方法,帮助你轻松... 目录使用T-SQL查询方法1:使用系统函数方法2:使用系统视图使用SQL Server Configu