当前位置: 首页 > news >正文

MySQL的聚簇索引与非聚簇索引

前言

  首先我们要了解到,聚簇索引只能有一个,而非聚簇可以有多个。在本文中可以了解到,范围查询时聚簇索引的优势,以及非聚簇索引在频繁更新时的劣势。
  在MySQL中,主键索引通常就是聚簇索引,如果没有显式定义主键,InnoDB会选择一个唯一非空索引代替,如果都没有,会生成隐藏的ROWID作为聚簇索引。

  一个用户表,id是主键,作为聚簇索引,数据按id顺序存储。而如果有一个非聚簇索引在email字段上,索引存储的是email和对应的主键id,查询时需要回表操作,即通过email索引找到id,再通过id找到完整数据行。这样回表会影响性能,尤其是当需要大量数据时。
  这就要提到覆盖索引的概念,即如果非聚簇索引包含了查询所需的所有字段,就可以避免回表,提升性能。比如,如果查询只需要email和id,那么email索引已经包含这些信息,无需回表。
  另外,本文需要讨论不同存储引擎的情况。比如,MyISAM使用的是非聚簇索引,而InnoDB使用的是聚簇索引。这直接影响了选择存储引擎时的考量,比如MyISAM在查询时可能需要更多的磁盘I/O,而InnoDB在范围查询时更高效。

  还需要解释索引的结构,比如B+树,以及聚簇索引和非聚簇索引在B+树中的不同组织形式。聚簇索引的叶子节点直接存储数据行,而非聚簇索引的叶子节点存储的是主键值或指向数据行的指针。

  在实际的应用场景中,高并发写入的环境下,聚簇索引的顺序插入可能导致页分裂,影响性能,而非聚簇索引的更新可能更频繁,需要维护多个索引结构,增加写操作的开销。这时候可能需要权衡索引的数量和类型,以优化整体性能。

  在最后一章节中,本文会总结聚簇索引和非聚簇索引的优缺点,以及适用场景。比如,聚簇索引适合主键查询和范围查询,而非聚簇索引适合辅助查询,但需要注意回表带来的性能问题。以及给出相应的优化建议。

  MySQL中的索引是优化查询性能的关键工具,而聚簇索引(Clustered Index)和非聚簇索引(Non-Clustered Index)是两种核心索引类型。它们的实现原理和适用场景有显著差异,理解这些差异对数据库设计和性能优化至关重要。以下章节从存储结构、工作原理、优缺点及实际应用进行全面分析。

一、聚簇索引(Clustered Index)

1. 定义与特性
  • 存储方式:数据行的物理存储顺序与索引顺序完全一致。
  • 唯一性:每个表只能有一个聚簇索引(因为数据只能按一种方式物理排序)。
  • 默认行为:在InnoDB引擎中,主键(Primary Key)自动成为聚簇索引。若未定义主键,InnoDB会选择第一个唯一非空索引(UNIQUE NOT NULL)作为聚簇索引;若两者均无,则隐式生成一个6字节的ROWID作为聚簇索引。
2. 数据结构
  • B+树结构

    • 叶子节点:直接存储完整数据行(即数据页)。
    • 非叶子节点:存储索引键值和指向子节点的指针。
    B+树示意图(聚簇索引):
    根节点
    ├── 索引键值区间1 → 中间节点
    │    ├── 索引键值区间1.1 → 叶子节点(数据行)
    │    └── 索引键值区间1.2 → 叶子节点(数据行)
    └── 索引键值区间2 → 中间节点
    
3. 优点
  • 高效范围查询:相邻数据物理上连续存储,减少磁盘I/O。
  • 主键查询极快:直接通过主键定位到数据行。
  • 覆盖索引优化:若查询仅涉及索引列,无需回表。
4. 缺点
  • 插入速度依赖顺序:若主键非自增,随机插入可能导致页分裂(Page Split),影响性能。
  • 更新代价高:主键更新需移动数据行,导致额外开销。
5. 应用场景
  • 主键查询:如SELECT * FROM users WHERE id = 100
  • 范围查询:如SELECT * FROM orders WHERE date BETWEEN '2023-01-01' AND '2023-01-31'

二、非聚簇索引(Non-Clustered Index)

1. 定义与特性
  • 存储方式:索引结构与数据行物理存储分离。
  • 多索引支持:一个表可创建多个非聚簇索引。
  • InnoDB实现:非聚簇索引的叶子节点存储主键值,而非直接指向数据行(需要回表查询)。
2. 数据结构
  • B+树结构

    • 叶子节点:存储索引键值 + 主键值(InnoDB)或数据行指针(MyISAM)。
    • 非叶子节点:存储索引键值和指向子节点的指针。
    B+树示意图(非聚簇索引):
    根节点
    ├── 索引键值区间1 → 中间节点
    │    ├── 索引键值1.1 → 叶子节点(主键值)
    │    └── 索引键值1.2 → 叶子节点(主键值)
    └── 索引键值区间2 → 中间节点
    
3. 优点
  • 灵活索引设计:可针对不同查询需求创建多个辅助索引。
  • 减少写开销:更新非聚簇索引不影响数据行物理位置。
4. 缺点
  • 回表查询:通过非聚簇索引找到主键后,需二次查询聚簇索引获取完整数据,增加I/O。
  • 范围查询效率低:非连续存储需多次磁盘寻址。
5. 应用场景
  • 辅助查询:如通过email字段快速定位用户ID,再通过主键获取完整数据。
  • 覆盖索引优化:若索引包含查询所需所有字段,避免回表(如SELECT email FROM users WHERE email = 'user@example.com')。

三、聚簇索引 vs 非聚簇索引对比

特性聚簇索引非聚簇索引
索引数量每个表仅一个可创建多个
存储内容数据行与索引一体索引键值 + 主键(InnoDB)或指针(MyISAM)
查询性能主键/范围查询快,避免回表需回表,覆盖索引时高效
插入性能主键顺序插入快,随机插入可能页分裂无数据移动,插入较快
更新代价主键更新代价高仅更新索引结构,代价较低
适用场景主键查询、范围扫描辅助查询、覆盖索引

四、存储引擎差异

1. InnoDB
  • 聚簇索引:主键索引为聚簇索引,数据按主键顺序存储。
  • 非聚簇索引:叶子节点存储主键值,需回表查询。
2. MyISAM
  • 无聚簇索引:所有索引均为非聚簇索引。
  • 数据存储:数据行独立存储(.MYD文件),索引存储指向数据行的指针(.MYI文件)。

五、实战优化建议

  1. 合理设计主键

    • 使用自增整数(AUTO_INCREMENT)减少页分裂。
    • 避免使用频繁更新的字段作为主键。
  2. 覆盖索引优化

    • 将查询字段包含在索引中,避免回表。
    -- 创建覆盖索引
    CREATE INDEX idx_user_email ON users(email, name);
    -- 查询仅需索引即可完成
    SELECT email, name FROM users WHERE email = 'user@example.com';
    
  3. 控制索引数量

    • 非聚簇索引过多会增加写操作开销,需权衡读写比例。
  4. 范围查询优先聚簇索引

    • 若查询条件涉及范围,尽量使用聚簇索引字段。

六、示例分析

场景:用户表查询
  • 表结构

    CREATE TABLE users (id INT PRIMARY KEY AUTO_INCREMENT,  -- 聚簇索引email VARCHAR(100) UNIQUE,          -- 非聚簇索引(UNIQUE约束)name VARCHAR(100),INDEX idx_name (name)               -- 非聚簇索引
    ) ENGINE=InnoDB;
    
  • 查询1SELECT * FROM users WHERE id = 100;

    • 路径:直接通过聚簇索引找到数据行,无需回表,效率高。
  • 查询2SELECT * FROM users WHERE email = 'user@example.com';

    • 路径:通过email索引找到主键id,再通过聚簇索引回表查询,效率较低。
  • 查询3SELECT name FROM users WHERE name LIKE 'John%';

    • 路径:若idx_name包含name字段,直接通过索引返回结果,避免回表。

七、总结

  • 聚簇索引:数据与索引一体,适合主键和范围查询,但需注意插入顺序。
  • 非聚簇索引:独立存储,适合辅助查询和覆盖索引,但可能需回表。
  • 优化核心:根据查询模式设计索引,减少回表操作,平衡读写性能。

相关文章:

MySQL的聚簇索引与非聚簇索引

前言 首先我们要了解到,聚簇索引只能有一个,而非聚簇可以有多个。在本文中可以了解到,范围查询时聚簇索引的优势,以及非聚簇索引在频繁更新时的劣势。   在MySQL中,主键索引通常就是聚簇索引,如果没有显式…...

vscode的一些实用操作

1. 焦点切换(比如主要用到使用快捷键在编辑区和终端区进行切换操作) 2. 跳转行号 使用ctrl g,然后输入指定的文件内容,即可跳转到相应位置。 使用ctrl p,然后输入指定的行号,回车即可跳转到相应行号位置。...

C++11 thread

文章目录 C11 线程库线程对象的构造方式无参的构造函数调用带参的构造函数调用移动构造函数thread常用成员函数 this_thread命名空间join && detachmutex C11 线程库 线程对象的构造方式 无参的构造函数 1、调用无参的构造函数,调用无参的构造函数创建出来的线程对象…...

rabbitmq五种模式的总结——附java-se实现(详细)

rabbitmq五种模式的总结 完整项目地址:https://github.com/9lucifer/rabbitmq4j-learning 一、简单模式 (一)简单模式概述 RabbitMQ 的简单模式是最基础的消息队列模式,包含以下两个角色: 生产者:负责发…...

Qt中基于开源库QRencode生成二维码(附工程源码链接)

目录 1.QRencode简介 2.编译qrencode 3.在Qt中直接使用QRencode源码 3.1.添加源码 3.2.用字符串生成二维码 3.3.用二进制数据生成二维码 3.4.界面设计 3.5.效果展示 4.注意事项 5.源码下载 1.QRencode简介 QRencode是一个开源的库,专门用于生成二维码&…...

Java数据结构---链表

目录 一、链表的概念和结构 1、概念 2、结构 二、链表的分类 三、链表的实现 1、创建节点类 2、定义表头 3、创建链表 4、打印链表 5、链表长度 6、看链表中是否包含key 7、在index位置插入val(0下标为第一个位置) 8、删除第一个关键字key …...

mongodb是怎么分库分表的

在构建高性能的数据库架构时,MongoDB的分库分表策略扮演着至关重要的角色,它通过一系列精细的步骤确保了数据的高效分布与访问。以下是对这一过程的详尽阐述,旨在提供一个清晰且优化过的理解框架。 确定分片键(Shard Key&#xf…...

C++自研游戏引擎-碰撞检测组件-八叉树AABB检测算法实现

八叉树碰撞检测是一种在三维空间中高效处理物体碰撞检测的算法,其原理可以类比为一个管理三维空间物体的智能系统。这个示例包含两个部分:八叉树部分用于宏观检测,AABB用于微观检测。AABB可以更换为均值或节点检测来提高检测精度。 八叉树的…...

spring boot对接clerk 实现用户信息获取

在现代Web应用中,用户身份验证和管理是一个关键的功能。Clerk是一个提供身份验证和用户管理的服务,可以帮助开发者快速集成这些功能。在本文中,我们将介绍如何使用Spring Boot对接Clerk,以实现用户信息的获取。 1.介绍 Clerk提供…...

一种动态地址的查询

背景 当我们注入一个进程,通过函数地址进行call时经常会遇到这样的一个问题。对方程序每周四会自动更新。更新后之前的函数地址就变化了,然后需要重新找地址。所以,我就使用了一个动态查询的方式。 第一步:先为需要call的函数生…...

周雨彤:用角色与生活,诠释审美的艺术

提到内娱审美优秀且持续在线的女演员,周雨彤绝对是其中最有代表性的一个。 独树一帜的表演美学 作为新生代演员中的实力派代表,周雨彤凭借细腻的表演和对角色的深度共情,在荧幕上留下了多个令人难忘的“出圈”形象。在《我在他乡挺好的》中…...

使用jks给空apk包签名

1、在平台官方下载空的apk包(上传应用时有提醒下载) 2、找到jdk目录,比如C:\Program Files\Java\jdk1.8\bin,并把下载的空包apk和jks文件放到bin下 3、以管理员身份运行cmd,如果不是管理员会签名失败 4、用cd定位到…...

500. 键盘行 771. 宝石与石头 简单 find接口的使用

500. 键盘行1 给你一个字符串数组 words ,只返回可以使用在 美式键盘 同一行的字母打印出来的单词。键盘如下图所示。 请注意,字符串 不区分大小写,相同字母的大小写形式都被视为在同一行。 美式键盘 中: 第一行由字符 "qwer…...

仙剑世界手游新手攻略 仙剑世界能用云手机玩吗

欢迎来到《仙剑世界》手游的仙侠世界!作为新手玩家,以下是一些详细的攻略和建议,帮助你快速上手并享受游戏的乐趣。 一、新手职业推荐 1.轩辕:这是一个偏辅助的职业,可以给队友提供输出加成等增益效果,不过…...

[题解]2024CCPC重庆站-小 C 的神秘图形

Sources:K - 小 C 的神秘图形Abstract:给定正整数 n ( 1 ≤ n ≤ 1 0 5 ) n(1\le n\le 10^5) n(1≤n≤105),三进制字符串 n 1 , n 2 ( ∣ n 1 ∣ ∣ n 2 ∣ n ) n_1,n_2(|n_1||n_2|n) n1​,n2​(∣n1​∣∣n2​∣n),按如下方法…...

NPS内网穿透SSH使用手册

1、说明 nps-一款轻量级、高性能、功能强大的内网穿透代理服务器 github地址:https://github.com/ehang-io/nps 官网文档地址:https://ehang-io.github.io/nps/#/?idnps 2、服务端 下载地址:https://github.com/ehang-io/nps/releases 下…...

大幂计算和大阶乘计算【C语言】

大幂计算&#xff1a; #include<stdio.h> long long int c[1000000]{0}; int main() {long long a,b,x1;c[0]1;printf("请输入底数&#xff1a;");scanf("%lld",&a);printf("请输入指数&#xff1a;");scanf("%lld",&b…...

【Linux】详谈 进程控制

目录 一、进程是什么 二、task_struct 三、查看进程 四、创建进程 4.1 fork函数的认识 4.2 2. fork函数的返回值 五、进程终止 5.1. 进程退出的场景 5.2. 进程常见的退出方法 5.2.1 从main返回 5.2.1.1 错误码 5.2.2 exit函数 5.2.3 _exit函数 5.2.4 缓冲区问题补…...

Linux top 命令

作用 top 是一个实时系统监控工具&#xff0c;用于查看系统的资源使用情况和进程状态。 示例 以下是一些常用的 top 命令示例&#xff1a; top &#xff1a;动态显示结果&#xff0c;每 3 秒刷新一次。 top -d 2&#xff1a;动态显示结果&#xff0c;每 2 秒刷新一次。 top …...

Leetcode 424-替换后的最长重复字符

给你一个字符串 s 和一个整数 k 。你可以选择字符串中的任一字符&#xff0c;并将其更改为任何其他大写英文字符。该操作最多可执行 k 次。 在执行上述操作后&#xff0c;返回 包含相同字母的最长子字符串的长度。 题解 可以先做LCR 167/Leetcode 03再做本题 滑动窗口&…...

label-studio的使用教程(导入本地路径)

文章目录 1. 准备环境2. 脚本启动2.1 Windows2.2 Linux 3. 安装label-studio机器学习后端3.1 pip安装(推荐)3.2 GitHub仓库安装 4. 后端配置4.1 yolo环境4.2 引入后端模型4.3 修改脚本4.4 启动后端 5. 标注工程5.1 创建工程5.2 配置图片路径5.3 配置工程类型标签5.4 配置模型5.…...

学校招生小程序源码介绍

基于ThinkPHPFastAdminUniApp开发的学校招生小程序源码&#xff0c;专为学校招生场景量身打造&#xff0c;功能实用且操作便捷。 从技术架构来看&#xff0c;ThinkPHP提供稳定可靠的后台服务&#xff0c;FastAdmin加速开发流程&#xff0c;UniApp则保障小程序在多端有良好的兼…...

《通信之道——从微积分到 5G》读书总结

第1章 绪 论 1.1 这是一本什么样的书 通信技术&#xff0c;说到底就是数学。 那些最基础、最本质的部分。 1.2 什么是通信 通信 发送方 接收方 承载信息的信号 解调出其中承载的信息 信息在发送方那里被加工成信号&#xff08;调制&#xff09; 把信息从信号中抽取出来&am…...

BCS 2025|百度副总裁陈洋:智能体在安全领域的应用实践

6月5日&#xff0c;2025全球数字经济大会数字安全主论坛暨北京网络安全大会在国家会议中心隆重开幕。百度副总裁陈洋受邀出席&#xff0c;并作《智能体在安全领域的应用实践》主题演讲&#xff0c;分享了在智能体在安全领域的突破性实践。他指出&#xff0c;百度通过将安全能力…...

select、poll、epoll 与 Reactor 模式

在高并发网络编程领域&#xff0c;高效处理大量连接和 I/O 事件是系统性能的关键。select、poll、epoll 作为 I/O 多路复用技术的代表&#xff0c;以及基于它们实现的 Reactor 模式&#xff0c;为开发者提供了强大的工具。本文将深入探讨这些技术的底层原理、优缺点。​ 一、I…...

Hive 存储格式深度解析:从 TextFile 到 ORC,如何选对数据存储方案?

在大数据处理领域&#xff0c;Hive 作为 Hadoop 生态中重要的数据仓库工具&#xff0c;其存储格式的选择直接影响数据存储成本、查询效率和计算资源消耗。面对 TextFile、SequenceFile、Parquet、RCFile、ORC 等多种存储格式&#xff0c;很多开发者常常陷入选择困境。本文将从底…...

Selenium常用函数介绍

目录 一&#xff0c;元素定位 1.1 cssSeector 1.2 xpath 二&#xff0c;操作测试对象 三&#xff0c;窗口 3.1 案例 3.2 窗口切换 3.3 窗口大小 3.4 屏幕截图 3.5 关闭窗口 四&#xff0c;弹窗 五&#xff0c;等待 六&#xff0c;导航 七&#xff0c;文件上传 …...

【Linux】自动化构建-Make/Makefile

前言 上文我们讲到了Linux中的编译器gcc/g 【Linux】编译器gcc/g及其库的详细介绍-CSDN博客 本来我们将一个对于编译来说很重要的工具&#xff1a;make/makfile 1.背景 在一个工程中源文件不计其数&#xff0c;其按类型、功能、模块分别放在若干个目录中&#xff0c;mak…...

抽象类和接口(全)

一、抽象类 1.概念&#xff1a;如果⼀个类中没有包含⾜够的信息来描绘⼀个具体的对象&#xff0c;这样的类就是抽象类。 像是没有实际⼯作的⽅法,我们可以把它设计成⼀个抽象⽅法&#xff0c;包含抽象⽅法的类我们称为抽象类。 2.语法 在Java中&#xff0c;⼀个类如果被 abs…...

渗透实战PortSwigger靶场:lab13存储型DOM XSS详解

进来是需要留言的&#xff0c;先用做简单的 html 标签测试 发现面的</h1>不见了 数据包中找到了一个loadCommentsWithVulnerableEscapeHtml.js 他是把用户输入的<>进行 html 编码&#xff0c;输入的<>当成字符串处理回显到页面中&#xff0c;看来只是把用户输…...