【面试题】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

相关文章

SQL中的外键约束

外键约束用于表示两张表中的指标连接关系。外键约束的作用主要有以下三点: 1.确保子表中的某个字段(外键)只能引用父表中的有效记录2.主表中的列被删除时,子表中的关联列也会被删除3.主表中的列更新时,子表中的关联元素也会被更新 子表中的元素指向主表 以下是一个外键约束的实例展示

基于MySQL Binlog的Elasticsearch数据同步实践

一、为什么要做 随着马蜂窝的逐渐发展,我们的业务数据越来越多,单纯使用 MySQL 已经不能满足我们的数据查询需求,例如对于商品、订单等数据的多维度检索。 使用 Elasticsearch 存储业务数据可以很好的解决我们业务中的搜索需求。而数据进行异构存储后,随之而来的就是数据同步的问题。 二、现有方法及问题 对于数据同步,我们目前的解决方案是建立数据中间表。把需要检索的业务数据,统一放到一张M

如何去写一手好SQL

MySQL性能 最大数据量 抛开数据量和并发数,谈性能都是耍流氓。MySQL没有限制单表最大记录数,它取决于操作系统对文件大小的限制。 《阿里巴巴Java开发手册》提出单表行数超过500万行或者单表容量超过2GB,才推荐分库分表。性能由综合因素决定,抛开业务复杂度,影响程度依次是硬件配置、MySQL配置、数据表设计、索引优化。500万这个值仅供参考,并非铁律。 博主曾经操作过超过4亿行数据

中文分词jieba库的使用与实景应用(一)

知识星球:https://articles.zsxq.com/id_fxvgc803qmr2.html 目录 一.定义: 精确模式(默认模式): 全模式: 搜索引擎模式: paddle 模式(基于深度学习的分词模式): 二 自定义词典 三.文本解析   调整词出现的频率 四. 关键词提取 A. 基于TF-IDF算法的关键词提取 B. 基于TextRank算法的关键词提取

水位雨量在线监测系统概述及应用介绍

在当今社会,随着科技的飞速发展,各种智能监测系统已成为保障公共安全、促进资源管理和环境保护的重要工具。其中,水位雨量在线监测系统作为自然灾害预警、水资源管理及水利工程运行的关键技术,其重要性不言而喻。 一、水位雨量在线监测系统的基本原理 水位雨量在线监测系统主要由数据采集单元、数据传输网络、数据处理中心及用户终端四大部分构成,形成了一个完整的闭环系统。 数据采集单元:这是系统的“眼睛”,

【前端学习】AntV G6-08 深入图形与图形分组、自定义节点、节点动画(下)

【课程链接】 AntV G6:深入图形与图形分组、自定义节点、节点动画(下)_哔哩哔哩_bilibili 本章十吾老师讲解了一个复杂的自定义节点中,应该怎样去计算和绘制图形,如何给一个图形制作不间断的动画,以及在鼠标事件之后产生动画。(有点难,需要好好理解) <!DOCTYPE html><html><head><meta charset="UTF-8"><title>06

认识、理解、分类——acm之搜索

普通搜索方法有两种:1、广度优先搜索;2、深度优先搜索; 更多搜索方法: 3、双向广度优先搜索; 4、启发式搜索(包括A*算法等); 搜索通常会用到的知识点:状态压缩(位压缩,利用hash思想压缩)。

性能分析之MySQL索引实战案例

文章目录 一、前言二、准备三、MySQL索引优化四、MySQL 索引知识回顾五、总结 一、前言 在上一讲性能工具之 JProfiler 简单登录案例分析实战中已经发现SQL没有建立索引问题,本文将一起从代码层去分析为什么没有建立索引? 开源ERP项目地址:https://gitee.com/jishenghua/JSH_ERP 二、准备 打开IDEA找到登录请求资源路径位置

深入探索协同过滤:从原理到推荐模块案例

文章目录 前言一、协同过滤1. 基于用户的协同过滤(UserCF)2. 基于物品的协同过滤(ItemCF)3. 相似度计算方法 二、相似度计算方法1. 欧氏距离2. 皮尔逊相关系数3. 杰卡德相似系数4. 余弦相似度 三、推荐模块案例1.基于文章的协同过滤推荐功能2.基于用户的协同过滤推荐功能 前言     在信息过载的时代,推荐系统成为连接用户与内容的桥梁。本文聚焦于

MySQL数据库宕机,启动不起来,教你一招搞定!

作者介绍:老苏,10余年DBA工作运维经验,擅长Oracle、MySQL、PG、Mongodb数据库运维(如安装迁移,性能优化、故障应急处理等)公众号:老苏畅谈运维欢迎关注本人公众号,更多精彩与您分享。 MySQL数据库宕机,数据页损坏问题,启动不起来,该如何排查和解决,本文将为你说明具体的排查过程。 查看MySQL error日志 查看 MySQL error日志,排查哪个表(表空间