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

Java 8 Stream API 入门到实践详解

一、告别 for 循环&#xff01; 传统痛点&#xff1a; Java 8 之前&#xff0c;集合操作离不开冗长的 for 循环和匿名类。例如&#xff0c;过滤列表中的偶数&#xff1a; List<Integer> list Arrays.asList(1, 2, 3, 4, 5); List<Integer> evens new ArrayList…...

tree 树组件大数据卡顿问题优化

问题背景 项目中有用到树组件用来做文件目录&#xff0c;但是由于这个树组件的节点越来越多&#xff0c;导致页面在滚动这个树组件的时候浏览器就很容易卡死。这种问题基本上都是因为dom节点太多&#xff0c;导致的浏览器卡顿&#xff0c;这里很明显就需要用到虚拟列表的技术&…...

.Net Framework 4/C# 关键字(非常用,持续更新...)

一、is 关键字 is 关键字用于检查对象是否于给定类型兼容,如果兼容将返回 true,如果不兼容则返回 false,在进行类型转换前,可以先使用 is 关键字判断对象是否与指定类型兼容,如果兼容才进行转换,这样的转换是安全的。 例如有:首先创建一个字符串对象,然后将字符串对象隐…...

DAY 26 函数专题1

函数定义与参数知识点回顾&#xff1a;1. 函数的定义2. 变量作用域&#xff1a;局部变量和全局变量3. 函数的参数类型&#xff1a;位置参数、默认参数、不定参数4. 传递参数的手段&#xff1a;关键词参数5 题目1&#xff1a;计算圆的面积 任务&#xff1a; 编写一…...

rm视觉学习1-自瞄部分

首先先感谢中南大学的开源&#xff0c;提供了很全面的思路&#xff0c;减少了很多基础性的开发研究 我看的阅读的是中南大学FYT战队开源视觉代码 链接&#xff1a;https://github.com/CSU-FYT-Vision/FYT2024_vision.git 1.框架&#xff1a; 代码框架结构&#xff1a;readme有…...

JavaScript 标签加载

目录 JavaScript 标签加载script 标签的 async 和 defer 属性&#xff0c;分别代表什么&#xff0c;有什么区别1. 普通 script 标签2. async 属性3. defer 属性4. type"module"5. 各种加载方式的对比6. 使用建议 JavaScript 标签加载 script 标签的 async 和 defer …...

Ubuntu 安装 Mysql 数据库

首先更新apt-get工具&#xff0c;执行命令如下&#xff1a; apt-get upgrade安装Mysql&#xff0c;执行如下命令&#xff1a; apt-get install mysql-server 开启Mysql 服务&#xff0c;执行命令如下&#xff1a; service mysql start并确认是否成功开启mysql,执行命令如下&am…...

JS设计模式(5): 发布订阅模式

解锁JavaScript发布订阅模式&#xff1a;让代码沟通更优雅 在JavaScript的世界里&#xff0c;我们常常会遇到这样的场景&#xff1a;多个模块之间需要相互通信&#xff0c;但是又不想让它们产生过于紧密的耦合。这时候&#xff0c;发布订阅模式就像一位优雅的信使&#xff0c;…...

第2篇:BLE 广播与扫描机制详解

本文是《BLE 协议从入门到专家》专栏第二篇,专注于解析 BLE 广播(Advertising)与扫描(Scanning)机制。我们将从协议层结构、广播包格式、设备发现流程、控制器行为、开发者 API、广播冲突与多设备调度等方面,全面拆解这一 BLE 最基础也是最关键的通信机制。 一、什么是 B…...

timestamp时间戳转换工具

作为一名程序员&#xff0c;一款高效的 在线转换工具 &#xff08;在线时间戳转换 计算器 字节单位转换 json格式化&#xff09;必不可少&#xff01;https://jsons.top 排查问题时非常痛的点: 经常在秒级、毫秒级、字符串格式的时间单位来回转换&#xff0c;于是决定手撸一个…...