当前位置: 首页 > 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再做本题 滑动窗口&…...

KubeSphere 容器平台高可用:环境搭建与可视化操作指南

Linux_k8s篇 欢迎来到Linux的世界&#xff0c;看笔记好好学多敲多打&#xff0c;每个人都是大神&#xff01; 题目&#xff1a;KubeSphere 容器平台高可用&#xff1a;环境搭建与可视化操作指南 版本号: 1.0,0 作者: 老王要学习 日期: 2025.06.05 适用环境: Ubuntu22 文档说…...

深入浅出Asp.Net Core MVC应用开发系列-AspNetCore中的日志记录

ASP.NET Core 是一个跨平台的开源框架&#xff0c;用于在 Windows、macOS 或 Linux 上生成基于云的新式 Web 应用。 ASP.NET Core 中的日志记录 .NET 通过 ILogger API 支持高性能结构化日志记录&#xff0c;以帮助监视应用程序行为和诊断问题。 可以通过配置不同的记录提供程…...

循环冗余码校验CRC码 算法步骤+详细实例计算

通信过程&#xff1a;&#xff08;白话解释&#xff09; 我们将原始待发送的消息称为 M M M&#xff0c;依据发送接收消息双方约定的生成多项式 G ( x ) G(x) G(x)&#xff08;意思就是 G &#xff08; x ) G&#xff08;x) G&#xff08;x) 是已知的&#xff09;&#xff0…...

大数据零基础学习day1之环境准备和大数据初步理解

学习大数据会使用到多台Linux服务器。 一、环境准备 1、VMware 基于VMware构建Linux虚拟机 是大数据从业者或者IT从业者的必备技能之一也是成本低廉的方案 所以VMware虚拟机方案是必须要学习的。 &#xff08;1&#xff09;设置网关 打开VMware虚拟机&#xff0c;点击编辑…...

Objective-C常用命名规范总结

【OC】常用命名规范总结 文章目录 【OC】常用命名规范总结1.类名&#xff08;Class Name)2.协议名&#xff08;Protocol Name)3.方法名&#xff08;Method Name)4.属性名&#xff08;Property Name&#xff09;5.局部变量/实例变量&#xff08;Local / Instance Variables&…...

MySQL中【正则表达式】用法

MySQL 中正则表达式通过 REGEXP 或 RLIKE 操作符实现&#xff08;两者等价&#xff09;&#xff0c;用于在 WHERE 子句中进行复杂的字符串模式匹配。以下是核心用法和示例&#xff1a; 一、基础语法 SELECT column_name FROM table_name WHERE column_name REGEXP pattern; …...

深度学习习题2

1.如果增加神经网络的宽度&#xff0c;精确度会增加到一个特定阈值后&#xff0c;便开始降低。造成这一现象的可能原因是什么&#xff1f; A、即使增加卷积核的数量&#xff0c;只有少部分的核会被用作预测 B、当卷积核数量增加时&#xff0c;神经网络的预测能力会降低 C、当卷…...

return this;返回的是谁

一个审批系统的示例来演示责任链模式的实现。假设公司需要处理不同金额的采购申请&#xff0c;不同级别的经理有不同的审批权限&#xff1a; // 抽象处理者&#xff1a;审批者 abstract class Approver {protected Approver successor; // 下一个处理者// 设置下一个处理者pub…...

接口自动化测试:HttpRunner基础

相关文档 HttpRunner V3.x中文文档 HttpRunner 用户指南 使用HttpRunner 3.x实现接口自动化测试 HttpRunner介绍 HttpRunner 是一个开源的 API 测试工具&#xff0c;支持 HTTP(S)/HTTP2/WebSocket/RPC 等网络协议&#xff0c;涵盖接口测试、性能测试、数字体验监测等测试类型…...

嵌入式学习之系统编程(九)OSI模型、TCP/IP模型、UDP协议网络相关编程(6.3)

目录 一、网络编程--OSI模型 二、网络编程--TCP/IP模型 三、网络接口 四、UDP网络相关编程及主要函数 ​编辑​编辑 UDP的特征 socke函数 bind函数 recvfrom函数&#xff08;接收函数&#xff09; sendto函数&#xff08;发送函数&#xff09; 五、网络编程之 UDP 用…...