【聚集索引、辅助索引、覆盖索引、联合索引、filesort过程】

【数据库原理系列】索引存储及分类 索引 概述 索引 索引是定义在存储表(Table)基础之上,有助于无需检查所有记录而快速定位所需记录的一种辅助存储结构,由一系列存储在磁盘上的索引项(indexentries)组成,每一索引项又由两部分构成: 索引字段:由Table中某些列(通常是一列)中的值串接而成。索引中通常存储了索引字段的每一个值(也有不是这样的)。 行指针:指向Table中包含索引字段值的记录在磁盘上的存储位置。 存储索引项的文件为索引文件,相对应,存储表又称为主文件 索引文件比主文件小很多。通过检索一个小的索引 阅读详情

廖志伟的自我介绍

什么是聚集索引

InnoDB存储引擎表是索引组织表,即表中数据按照主键顺序存放。而聚集索引(clusteredindex)就是按照每张表的主键构造一棵B+树,同时叶子节点中存放的即为整张表的行记录数据,也将聚集索引的叶子节点称为数据页。每个数据页都通过一个双向链表来进行链接由于实际的数据页只能按照一棵B+树进行排序,因此每张表只能拥有一个聚集索引。在多数情况下,查询优化器倾向于采用聚集索引。因为聚集索引能够在B+树索引的叶子节点上直接找到数据。此外,由于定义了数据的逻辑顺序,它对于主键的排序查找和范围查找速度非常快。叶子节点的数据就是用户所要查询的数据如:用户需要查询一张注册用户的表,查询最后注册的10位用户,由于B+树索引是双向链表的,用户可以快速找到最后一个数据页,并取出10条记录SELECT * FROM Profile ORDER BY id LIMIT 10;虽然使用ORDER BY对主键id记录进行排序,但是在实际过程中并没有进行所谓的filesort操作,而这就是因为聚集索引的特点。另一个是范围查询(range query),即如果要查找主键某一范围内的数据,通过叶子节点的上层中间节点就可以得到页的范围,之后直接读取数据页即可。如:SELECT * FROM Profile where id>1 and id < 100;

什么是辅助索引

对于辅助索引(Secondary Index,也称非聚集索引),叶子节点并不包含行记录的全部数据。叶子节点除了包含键值以外,每个叶子节点中的索引行中还包含了一个书签(bookmark)。该书签用来告诉InnoDB存储引擎哪里可以找到与索引相对应的行数据。由于InnoDB存储引擎表是索引组织表,因此InnoDB存储引擎的辅助索引的书签就是相应行数据的聚集索引键。辅助索引的存在并不影响数据在聚集索引中的组织,因此每张表上可以有多个辅助索引。当通过辅助索引来寻找数据时,InnoDB存储引擎会遍历辅助索引并通过叶级别的指针获得指向主键索引的主键,然后再通过主键索引来找到一个完整的行记录。举例来说,如果在一棵高度为3的辅助索引树中查找数据,那需要对这棵辅助索引树遍历3次找到指定主键,如果聚集索引树的高度同样为3,那么还需要对聚集索引树进行3次查找,最终找到一个完整的行数据所在的页,因此一共需要6次逻辑IO访问以得到最终的一个数据页。

什么是覆盖索引,什么情况下优化器会选择使用覆盖索引

InnoDB存储引擎支持覆盖索引(covering index,或称索引覆盖),即从辅助索引中就可以得到查询的记录(此时不能够使用select * 操作,只能对特定的索引字段进行select),而不需要查询聚集索引中的记录。使用覆盖
索引的一个好处是辅助索引不包含整行记录的所有信息,故其大小要远小于聚集索引,因此可以减少大量的IO操作。对于InnoDB存储引擎的辅助索引而言,由于其包含了主键信息,因此其叶子节点存放的数据为(primarykey1,primary key2,…,key1,key2,…)。例如,下列语句都可仅使用一次辅助联合索引来完成查询:

SELECT key2 FROM table WHERE key1=xxx;
SELECT primary key2,key2 FROM table WHERE key1=xxx;
SELECT primary key1,key2 FROM table WHERE key1=xxx;
SELECT primary key1,primary key2,key2 FROM table WHERE key1=xxx;
CREATETABLEbuy_log( 
userid INT UNSIGNED NOT NULL,
buy_date DATE 
)ENGINE=InnoDB; 
ALTER TABLE buy_log ADD KEY (userid);
ALTER TABLE buy_log ADD KEY (userid,buy_date);

覆盖索引的另一个好处是对某些统计问题而言的。还是对于上题创建的表buy_log,要进行举例说明。
SELECT COUNT(*) FROM buy_log;InnoDB存储引擎并不会选择通过查询聚集索引来进行统计。由于buy_log表上还有辅助索引,而辅助索引远小于聚集索引,选择辅助索引可以减少IO操作。

在通常情况下,诸如(a,b)的联合索引,一般是不可以选择列b中所谓的查询条件。但是如果是统计操作,并且是覆盖索引的,则优化器会进行选择,如下述语句:SELECT COUNT(*)FROM buy_log WHERE buy_date>=‘2011-01-01’ANDbuy_date<’2011-02-01’
表buy_log有(userid,buy_date)的联合索引,这里只根据列b进行条件查询,一般情况下是不能进行该联合索引的,但是这句SQL查询是统计操作,并且可以利用到覆盖索引的信息,因此优化器会选择该联合索引.

什么是联合索引,联合索引的使用经验有吗?

联合索引是指对表上的多个列进行索引。

CREATE TABLE buy_log(
userid INT UNSIGNED NOT NULL,
buy_date DATE
)ENGINE=InnoDB;
ALTER TABLE buy_log ADD KEY (userid);
ALTER TABLE buy_log ADD KEY (userid,buy_date);

以上代码建立了两个索引来进行比较。两个索引都包含了userid字段。

情况1: 如果只对于userid进行查询,如:SELECT * FROM buy_log WHERE userid=2;索引选择:优化器最终的选择是索引userid,因为该索引的叶子节点包含单个键值,所以理论上一个页能存放的记录应该更多。

情况2:SELECT * FROM buy_log WHERE userid=1 ORDER BY buy_date DESC LIMIT 3;索引选择:优化器使用了(userid,buy_date)的联合索引userid_2,因为在这个联合索引中buy_date已经排序好了。根据该联合索引取出数据,无须再对buy_date做一次额外的排序操作。

情况 3:假如三个字段的联合索引。如:对于联合索引(a,b,c)来说,下列语句同样可以直接通过联合索引得到结果,不需要filesort的排序操作:

SELECT...FROM TABLE WHERE a=xxx ORDER BY b 
SELECT...FROM TABLE WHERE a=xxx AND b=xxx ORDER BY c

但是对于下面的语句,联合索引不能直接得到结果,其还需要执行一次filesort排序操作,因为索引(a,c)并未排序:

SELECT...FROM TABLE WHERE a=xxx ORDER BY c

索引总结

聚集索引的叶子节点称为数据页,每个数据页通过一个双向链表来进行链接,而且数据页按照主键的顺序进行排列。每个数据页上存放的是完整的行记录,而在非数据页的索引页中,存放的仅仅是键值及指向数据页的偏移量,而不是一个完整的行记录。如果定义了主键,InnoDB会自动使用主键来创建聚集索引。如果没有定义主键,InnoDB会选择一个唯一的非空索引代替主键。如果没有唯一的非空索引,InnoDB会隐式定义一个主键来作为聚集索引。辅助索引它叶子节点中没有行记录的全部数据,叶子节点除了包含键值以外,每个叶子节点的索引行还包含了一个书签,该书签用来告诉InnoDB哪里可以找到与索引相对应的行数据。覆盖索引先遍历辅助索引,再遍历聚集索引,而如果要查询的字段值在辅助索引上就有,就不用再查聚集索引了,这显然会减少IO操作。除了这三种索引,还有一种联合索引,它是对表上的多个列进行索引,键值都是排序的,通过叶子节点可以顺序的读出所有数据,联合索引的好处在于能起到"一个顶三个"的作用。比如建了一个(a,b,c)的复合索引,那么实际等于建了(a),(a,b),(a,b,c)三个索引,每多一个索引,都会增加写操作的开销和磁盘空间的开销,对于大数据的表,这是不小的开销。另外它还可以避免filesort排序,因为filesort的过程,一行数据会被读两次,第一次是where条件过滤时,第二个是排完序后还得用行指针去读一次。

filesort的过程

filesort的过程是这样的:第一步先根据表的索引或者全表扫描,读取所有满足条件的记录。第二步,存储每一行排序列,就是order by用到的列值,还有行记录指针,就是指向该行数据的行指针,把这二个存储到缓冲区。第三步,当缓冲区满后,运行一个快速排序来将缓冲区中数据排序,将排序完的数据存储到一个临时文件,保存一个存储块的指针,当然如果缓冲区不满,则不会重建临时文件了。直到将所有行读完,建立相应有序的临时文件。第四步,对块级进行排序,这个类似归并排序算法,只通过两个临时文件的指针来不断交换数据,最终达到两个文件,都是有序的,直到所有的数据都排序完毕。第五步,采取顺序读的方式,将每行数据读入内存,取出数据传到客户端。

为什么要说这个filesort呢?举二个场景,第一个,如果order by的条件不在索引列上会产生filesort,第二个,排序的字段不在where的条件中,没有办法走索引排序Index,而是走的文件排序filesort 。这种概率其实还是挺高的。这个时候就需要看文件排序用的是单路排序还是双路排序,单路排序会把所有需要查询的字段都放到 sort buffer 中,而双路排序只会把主键 和需要排序的字段放到 sort buffer 中进行排序,然后再通过主键回到原表查询需要的字段。mysql优化器使用双路排序还是单路排序是有自己的算法判断的,如果查询的列字段大于max_length_for_sort_data变量,则会使用双路排序,反之则会使用单路排序,单路排序速度是更快的,不过比较占据内存,如果在内存空间允许的情况下想要使用单路排序的话,可以增加max_length_for_sort_data变量的大小,max_length_for_sort_data变量默认为1024字节。

MySQL中的B+树索引(聚簇索引/聚集索引、辅助索引/二级索引、联合索引/复合索引、覆盖索引、回表)详解 聚集索引/聚簇索引--主键 所有的数据辅助索引/二级索引--索引列—+主键。查看其他列–>回表(聚集索引/聚簇索引联合索引/复合索引--(多个列+主键)->覆盖索引(不是索引) 阅读详情

相关推荐

mysql聚合索引、组合索引

索引 排好序的快速查找数据结构。在数据之外,数据库系统还维护着满足特定查找算法的数据结构,这些数据结构以某种方式引用(指向)数据,这样就可以在这些数据结构上实现高级查找算法。这种数据结构,就是索引。下图就是一种可能的索引方式示例: 数据本身之外,数据库还维护着一个满足特定查找算法的数据结构,这些数据结构以某种方式指向数据, 这样就可以在这些数据结构的基础上实现高级查找算法,这种数据结构就是索引。 聚合索引 聚集索引一个表只能有一个,而非聚集索引一个表可以存在多个 聚集索引存储记录是物理上连续存在,.

cyt 5425

Mysql - 聚簇索引和辅助索引

聚簇索引和辅助索引 聚簇索引:数据是存放在叶子节点上。 辅助索引:叶子节点上是不存放真实数据的,而是存放聚簇索引的索引值。 首先说明一下,聚簇索引和辅助索引都是B+树的结构,区别就是叶子节点上是否存放真实的数据。那么为什么有这个区分呢。其实也很好理解,我们都知道,Mysql中的每一个索引都是一颗B+树来存储的,如果我每一颗B+树的叶子节点上面都存储数据的话,这个空间的要求是不是很大呢?当然,也可以所有的索引都不存储真实的数据,都存储指向真实数据的索引,这是MyISAM存储引擎的做法。 在Innodb存储引擎

程序员大航子 3015

MySQL覆盖索引深度解析:从原理到实践的性能优化之道

MySQL覆盖索引是一种高效查询优化技术,通过索引直接包含查询所需字段,避免回表操作,大幅提升性能。本文详细解析了覆盖索引的原理、优势(降低IO、优化缓存、高效排序等)、创建策略(精准匹配查询字段)及常见误区(索引膨胀、忽略最左前缀等)。通过EXPLAIN分析可判断是否生效,建议结合业务高频查询场景精准设计索引。覆盖索引不是万能方案,合理使用才能发挥最大效用,实现查询性能的数量级提升。

weixin_57681114的博客 640

MySQL--聚集索引、辅助索引、回表查询和覆盖索引的原理

在MySQL中,索引是提高查询性能的核心工具。理解和的原理,对于优化数据性能至关重要。以下是对这些概念的详细解释以及优化方法。

zx733988的博客 1062

数据库索引相关总结

索引是帮助mysql高效获取数据的排好序的数据结构。

Rk的博客 938

EER模型到MySQL落地:泛化、弱实体与聚集关系的实战映射

Enhanced Entity-Relationship(EER)是数据库概念建模的核心方法,其泛化、弱实体和聚集等扩展要素,直接决定物理表结构的合理性与可维护性。在MySQL中,由于缺乏原生继承、依赖联系和组合语义支持,必须通过单表/类表/主子表策略、复合主键+级联外键、显式关联表+聚集属性拆解等方式进行工程化翻译。这一过程不仅关乎范式合规,更直接影响查询性能、事务一致性与索引效率。典型应用场景包括多角色用户系统、订单商品关系、课程章节视频层级等需要精准表达业务语义的数据模型。本文聚焦MySQL 5.7/

weixin_30363509的博客 359

MySQL聚集索引与非聚集索引核心原理与实战设计指南

在数据库性能优化领域,索引是提升查询效率的核心技术。其本质是一种通过特定数据结构(如B+树)组织数据,以实现快速数据检索的机制。从原理上讲,索引通过建立数据键值与物理存储位置的映射关系,将随机I/O转化为顺序或近似顺序的访问,从而大幅降低查询延迟,其技术价值在于平衡读写开销、支撑高并发访问。在应用场景上,无论是OLTP系统的点查、范围查询,还是OLAP系统的聚合分析,高效的索引设计都是保障系统响应速度的关键。本文聚焦于MySQL InnoDB引擎中最核心的**聚集索引**与**非聚集索引**,深入剖析两者在

weixin_30873847的博客 303

MySQL索引底层原理:从图书馆找书到B+Tree与InnoDB页结构

数据库索引是提升查询性能的核心机制,其本质是通过有序数据结构减少磁盘IO次数。B+Tree作为MySQL InnoDB默认索引结构,以‘矮胖’形态优化树高,显著降低范围查询和排序操作的IO开销;而InnoDB页结构(如Page Directory、Infimum/Supremum、n_owned字段)则在物理层面保障高并发写入下的稳定性与局部性。理解索引失效(如LIKE '%abc')、回表代价、页分裂逻辑等现象,需回归到B+Tree的搜索路径与页内数据组织方式。本文结合生活类比与生产故障复现,深入解析索引

aobai7842的博客 453

Mysql索引篇(四) 覆盖索引(Using index)、文件排序(Using filesort)和临时表(Using temporary)

本系列文章目录 展开/收起 Mysql索引篇(一) 索引的数据结构B+树 Mysql索引篇(二) Myisam和Innodb的索引 Mysql索引篇(三) 善于explain分析sql语句 Mysql索引篇(四) 覆盖索引(Using index)、文件排序(Using filesort)和临时表(Using temporary) ...

jqsfjqsf的博客 3369

基于单片机红外线遥控Proteus仿真电路发射接收

*单片机设计介绍,基于单片机红外线遥控Proteus仿真电路发射接收。

2201_75940997的博客 3943

冷源法测试噪声系数(Noise Figure)详细步骤

背景知识 电阻负载的功率谱密度 噪声等效模型 3 4 冷源法测试步骤 注意事项

eleday的电子世界 6874

索引(B+tree、联合唯一索引、)

参考博客:https://blog.youkuaiyun.com/u013967628/article/details/84305511#索引的本质

军威兄的博客 2166

保姆级教程:Synology NAS共享文件夹从创建到权限设置的完整流程

本文提供了一份详尽的Synology NAS共享文件夹创建与权限设置指南。重点讲解了如何将NAS接入企业AD域,并利用域管理员账户进行集中身份验证和权限管理。文章从基础概念、域环境配置、文件夹创建、精细化权限分配到客户端访问与维护,完整覆盖了构建安全高效企业级文件共享空间的全流程,旨在帮助管理员实现便捷的团队协作与数据安全管理。

u9v0w的博客 559

MySQL - 索引优化

MySQL 索引优化 即不重复的索引值和表记录总数的比值,索引的选择性越高则查询效率越高,因为选择性高的索引可以让 MySQL 在查找时过滤掉更多的行。

迪曼奥特迦-博客 1505

解决vLLM安装卡在vllm-nccl-cu12依赖项的实战指南

本文提供了解决vLLM安装过程中卡在vllm-nccl-cu12依赖项的详细指南。通过分析CUDA版本兼容性问题,介绍了三种实用解决方案:安装指定版本vLLM、手动安装nccl-cu12依赖以及升级CUDA到12.x版本,并特别针对离线环境提供了安装建议,帮助开发者顺利完成vLLM的安装部署。

weixin_29098367的博客 516

STM32F103跑LVGL,用DMA加速刷屏,800*480大屏也能丝滑

本文详细介绍了如何在STM32F103芯片上通过DMA加速和内存优化,实现800*480大屏的LVGL流畅运行。通过FSMC+DMA硬件架构优化、双缓冲机制和LVGL参数调优,显著提升了界面刷新率和响应速度,适用于嵌入式GUI开发中的高性能需求场景。

weixin_30357231的博客 378

MySQL实战:索引优化策略有哪些?

索引的种类 众所周知,索引类似于字典的目录,可以提高查询的效率。 索引从物理上可以分为:聚集索引,非聚集索引 从逻辑上可以分为:普通索引,唯一索引,主键索引,联合索引,全文索引 索引优化策略 不要在索引列上进行运算或使用函数 在列上进行运算或使用函数会使索引失效,从而进行全表扫描。如下面例子在publish_time,id列上分别加上索引,publish_time为datetime类型,id为int类型 -- 全表扫描 select * from article where year(publish_ti.

小识的博客 1413

【25届数字IC秋招总结】面试经验13——豪威科技面试

25届数字ic面试记录———豪威科技

lalala0635的博客 365

MySQL索引管理与执行计划

MySQL索引 MySQL执行计划

hawava的博客 337

linux驱动由浅入深系列:基于高通平台分析触摸屏(TP)、虚拟按键驱动

触摸屏的触摸板(touch panel简称TP)驱动的基本架构和普通按键驱动基本一致,可以参考文章:linux驱动由浅入深系列:输入子系统之二(编写一个gpio_key驱动)。只是其功能稍稍复杂些,配置也多些,下面一起来看一下。 TP包括 触摸屏、虚拟按键、虚拟按键背光灯 TP驱动芯片厂商主要有:Goodix(汇顶),FocalTech(敦泰科技),cypress(赛普拉斯),synapt

Radia的专栏 4770

ICML-2025《Learning from Sample Stability for Deep Clustering》

用于指导深度聚类模型的训练,从而提升模型对困难样本的学习能力,最终改善整体聚类性能。首先,利用K-means在原始样本(非增强)的表征上进行聚类,得到初始聚类分配。其目的是鼓励模型对同一稳定样本在连续epoch中产生相近的表征。输出差异大)会产生更大的损失值,从而引导模型优先优化这些困难样本。此损失借鉴了NT-Xent损失的思想,但将正样本对定义为。最后,通过对比学习优化聚类中心,将同一聚类在。为了获得更具代表性的聚类中心,论文提出。为了学习更鲁棒的表征,模型在在线网络。首先,论文定义了样本。

Christo的博客 831

MySQl高级篇 -索引优化篇

InnoDB采用了一个B+数来存储索引,使得在千万级数据量的一个情况下,树的高度可以控制在3层以内,而层高代表磁盘IO的一个次数,因此基于索引查找可以减少磁盘IO的次数MySQL的索引是在存储引擎层实现的,不同的存储引擎有不同的结构,主要包含以下几种二叉树缺点: 顺序插入时,会形成一个链表,查询性能大大降低。大数据量情况下,层级较深,检索速度慢。BTree(多路平衡查找树)每个节点可以包含多个子节点。相比于二叉搜索树,B+树的节点可以存储更多的关键字,减少了树的高度,提高了检索效率。以一颗最大度数(max-

ShockChen7的博客 1212

Camera2Demo

在Camera2 的基础上进行封装,使得Camera2一堆初始化逻辑与Activity分离,降低耦合度,完成预览拍照并保存的操作

MYSQL索引与order by排序关系总结

MySQL InnoDB B-Tree索引使用Tips 这里主要讨论一下InnoDB B-Tree索引的使用,不提设计,只管使用。B-Tree索引主要作用于WHERE和ORDER BY子句。这里讨论的均在MySQL-Server-5.1.42测试 CREATE TABLE `friends` ( `ID` i...

weixin_43770941的博客 304

IDEA的使用指南

IDEA基于java的使用手册,可以最快速的上手,在使用中不断的丰富自己的使用技巧.

IEM模型,地表粗糙度模拟

利用微波反演土壤水分时候,利用IEM模型可以实现对随机地表的模拟

MySql调优(三)Query SQL优化(2)explain优化

6、索引优化:上面讲到子查询的优化,以及如何建立索引,而且在多个字段索引时,分别对字段建立了单个索引,后面发现其实建立联合索引效率会更高,尤其是在数据量较大,单个列区分度不高的情况下。这一条虽然order by后面的字段和索引中字段顺序相同,但是一个是降序,一个是升序,所以也会产生using filesort,同时升序和同时降序就不会产生using filesort了。order by后面的字段顺序不符合组合索引中的顺序,所以order by后面的不会走索引,即会产生using filesort。

w_t_y_y的博客 2124

Statistics and Machine Learning Toolbox

Statistics and Machine Learning Toolbox(带书签),MATLAB统计学习工具

mysql date 索引_Mysql InnoDB索引原理

Mysql InnoDB索引原理理解Mysql索引的原理和数据结构有助于我们更好的使用索引以及进行SQL优化,索引是在存储引擎层面实现的,所以不同的引擎实现的索引也有一定的区别,但是在生产环境中,我们最常用的就是InnoDB引擎和B树索引,OK,那本文要讨论的重点也同样是InnoDB引擎下的B树索引。我们建立一个表来进行测试,表的DDL如下所示,我们要关注的是表t_book上的主键索引id和nam...

weixin_39838328的博客 245

(转)MYSQL order by排序与索引关系总结

转自:http://phpdo.com/post/2013-02-26/40049082902 这里主要讨论一下InnoDB B-Tree索引的使用,不提设计,只管使用。B-Tree索引主要作用于WHERE和ORDER BY子句。这里讨论的均在MySQL-Server-5.1.42测试 CREATE TABLE `friends`( `ID` INT(10)UNSIGNED NOT NUL...

AAA821的专栏 420
上一篇: 【SQL优化/索引失效的几种情况/FIC/OnlineDDL】
下一篇: 【离散读、ICP优化、全文检索】
Java程序员廖志伟
Java程序员廖志伟 优质创作者: Java技术领域 优质创作者: Java技术领域 领域专家: 后端开发技术领域 领域专家: 后端开发技术领域
博客等级 码龄8年 2万+粉丝 1902原创
评论 10
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符
 
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付:10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

Java程序员廖志伟

赏我包辣条呗

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值