Sqlserver_Oracle_Mysql_Postgresql表_索引碎片或膨胀的知识点汇总

发布时间:2026/11/20 6:57:38

Sqlserver_Oracle_Mysql_Postgresql表_索引碎片或膨胀的知识点汇总 碎片的定义 指单个数据块/页内部的空间利用率不足。成因INSERT 向页中插入新行但新行的大小不足以填满块/页的空闲空间。UPDATE 更新导致行增长如 VARCHAR 列写入更多数据如果当前块/页没有足够空间容纳增长后的行数据库可能将该行迁移到另一个有空间的块/页或会将大约一半的行移动到一个新的数据块/页称为行迁移或块/页拆分的结果之一在原位置留下指向新位置的指针新块/页也可能不连续这个新块/页在磁盘上的物理位置通常不紧接着原块/页之后除非文件末尾恰好有连续空间。新块/页可能位于文件的其他位置甚至不同的物理磁盘如果文件组跨多个磁盘。DELETE 删除行后在页内留下空白空间。大量删除操作可能导致数据块/页被释放后续插入可能填充这些释放的空间导致逻辑连续的数据分散在文件各处。比如索引的逻辑顺序(由键值决定)要求新页在逻辑链表中插入到原页之后或之前(取决于插入顺序)但它们的物理位置是分离的。危害I/O性能显著下降 这是最直接、最严重的后果。需要读取更多的块/页和更慢地读取逻辑连续的块/页消耗更多I/O资源和时间。因为碎片导致单个块/页存储的有效数据减少读取相同数量的数据需要读取更多的物理页增加I/O操作和内存Buffer Pool压力。内存中能缓存的“有效数据”变少。特别是顺序扫描性能下降(顺序读写连续数据只需一次寻址连续传输随机读写则需多次寻址。对HDD来说磁头移动的机械延迟是瓶颈所以随机性能差而SSD没有机械部件但随机读写会引发擦除-写入周期仍比顺序读写慢。)因为需要执行索引范围扫描或全表扫描(聚集索引表和索引组织表)时理想情况是连续读取物理上相邻的块/页。碎片导致磁头需要在磁盘的不同位置来回跳跃从而产生更多随机I/O显著降低扫描速度。内存利用率降低 Buffer Pool缓存了更多包含“空白”或无效空间的块/页CPU 使用率增加 处理更多的I/O请求和物理I/O查找会增加 CPU 负担。磁盘空间浪费 太多空块/页或无效空间的块/页增加磁盘空间使用直到它们被重用Oracle,Sqlserver,Mysql三者的表碎片或索引碎片的概念原理和上面的描述类似。其中Oracle索引组织表和Sqlserver聚集索引表和Mysql表的数据是存放在索引的叶子节点上虽然索引叶子节点块/页上面的信息是连续性的但是那只是在逻辑上是连续的(按键值顺序连接)物理存储上通常不是连续的。比如我们以Oracle的索引组织表为例子创建一张索引组织表table1主键字段是hid每行大概1KB插入hid为1的行再插入hid为3到hid为2000002的行这个时候没有hid为2的行。hid主键的索引根节点块记录了1-2000002的值假设索引根节点块下面有5个干节点块hid主键值1-500000记录在第一个索引干节点块上500001-1000000,1000001-1500000,1500001-2000000,2000001-2500000依次记录在第2到第5个索引干节点块上索引干节点块下面就是索引叶子节点数据块了索引组织表的索引叶子节点不存放索引键值和rowid而是直接存放数据行因为每个数据块8KB而该表的每行大概1KB所以一个索引叶子节点块大概可以存放8行数据。因为没有hid为2的行所以第一个索引叶子节点块存放了hid值为1-9的数据行。这个时候插入hid为2的数据行按照索引键值的连续性理论上应该紧挨着hid为1的数据行之后进行插入也就是说理论上hid为1和2的数据块应该是同一个索引叶子节点数据块上但是我们这个场景下hid为1的索引叶子节点已经包含hid为9的行数据没有空间可供插入了这个时候hid为2的行只能去寻找一个空的索引叶子节点数据块进行插入所以我们看到实验场景下插入hid为2的行很快并没有对整个索引组织表的所有索引叶子节点进行物理存储上的重新排序插入hid为最大值2000002之后的2000003的数据行也很快它也是直接找hid为2000002的索引叶子节点是否有空间可以继续插入如果不能继续插入就去寻找一个空的索引叶子节点数据块进行插入。Oracle的索引组织表为例子SET TIMING ONSET AUTOCOMMIT ONCREATE TABLE table1 (hid NUMBER PRIMARY KEY,created_date DATE,hid1 char(200),hid2 char(200),hid3 char(200),hid4 char(200),hid5 char(100)) ORGANIZATION INDEX;insert into table1 values(1,sysdate,‘1’,‘1’,‘1’,‘1’,‘1’);INSERT INTO table1 (hid,created_date,hid1,hid2,hid3,hid4,hid5) SELECT LEVEL 2 AS hid,sysdate,‘1’,‘1’,‘1’,‘1’,‘1’ FROM dual CONNECT BY LEVEL 1000000; --从3开始插入插入100000行直到最后1000002INSERT INTO table1 (hid,created_date,hid1,hid2,hid3,hid4,hid5) SELECT LEVEL 1000002 AS hid,sysdate,‘1’,‘1’,‘1’,‘1’,‘1’ FROM dual CONNECT BY LEVEL 1000000; --从1000003开始插入插入100000行直到最后2000002查看插入第二行和插入最后一行的区别发现没有区别两者速度一样都很快SQL insert into table1 values(2,sysdate,‘1’,‘1’,‘1’,‘1’,‘1’);1 row created.Commit complete.Elapsed: 00:00:00.06SQL insert into table1 values(2000003,sysdate,‘1’,‘1’,‘1’,‘1’,‘1’);1 row created.Commit complete.Elapsed: 00:00:00.06Mysql查询表的碎片情况和如何解决碎片的方法Mysql的表就是索引组织表查询碎片情况SELECT NAME, FILE_SIZE,ALLOCATED_SIZE FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES WHERE NAME‘db.table’;解决碎片方法alter table tablename engine InnoDB(就是recreate table online)optimize table tablename (就是recreate table analyze搜集统计信息)Sqlserver查询表的碎片情况和如何解决碎片的方法Sqlserver没有“表碎片”的独立概念 表的物理存储完全由它的聚集索引或堆结构定义聚集索引碎片 表碎片如果表是堆表(无聚集索引)则查看区组碎片信息sys.dm_db_index_physical_stats动态函数可以查出表或索引的碎片信息对于聚集索引则index_id 1对于堆表则index_id 0不管index_id是0还1结果都可以参考sys.dm_db_index_physical_stats动态函数的结果中avg_fragmentation_in_percent列的值。而且实际工作中发现一个堆表有多个索引的情况下这些索引的碎片比都差不多所以一般sqlserver只需要重组索引或重建索引解决碎片方法重建索引或重组索引重建索引重新生成索引 会删除并重新创建索引。这可以联机完成,也可以脱机完成,重新生成索引联机执行(ON),则索引操作期间可以用此表中的数据进行查询和修改数据但是重新生成索引联机执行(ON)有三个阶段,开始阶段占用表的一个S锁,索引操作的主要阶段占用表的一个意向共享 (IS)锁,结束阶段占用表本身的一个Sch-M锁。更新索引本身的统计信息但是不更新表的统计信息对整个索引进行碎片整理。重建表上的所有索引alter index all on table_name rebuild with (onlineon)重建表上的某个索引alter index index_name on table_name rebuild with (onlineon)重组索引使用的系统资源最少,并且是联机操作。也就是说,不保留长期阻塞性表锁,且对基础表的查询或更新可以在ALTER INDEX REORGANIZE事务处理期间继续进行。 不更新表或索引的统计信息只对叶子级别的索引进行碎片整理不对树干级别的索引进行碎片整理。重新组织表上的所有索引alter index all on table_name reorganize重新组织表上的某个索引alter index index_name on table_name reorganizeOracle查询表的碎片情况和如何解决碎片的方法Oracle已经没有碎片的概念Oracle针对表碎片的专业术语是高水位线。查询碎片情况SELECT TABLE_NAME,(BLOCKS8192/1024/1024)“理论大小M”,(NUM_ROWSAVG_ROW_LEN/1024/1024/0.9)“实际大小M”,round((NUM_ROWSAVG_ROW_LEN/1024/1024/0.9)/(BLOCKS8192/1024/1024),3)100||‘%’ “实际使用率%”FROM USER_TABLES where blocks100 and (NUM_ROWSAVG_ROW_LEN/1024/1024/0.9)/(BLOCKS8192/1024/1024)0.3order by (NUM_ROWSAVG_ROW_LEN/1024/1024/0.9)/(BLOCKS*8192/1024/1024) desc解决碎片方法表收缩或表移动表空间表收缩不需要额外表空间,move之后索引有效可以在线进行move期间不会影响dml和select如果表比较大会产生大量REDO、UNDO不要再高峰期执行此操作alter table tablename shrink space cascade;表移动表空间:如果不指定表空间就是在原表空间上move.需要额外一倍表大小的空间。move之后索引会失效需要rebuild一下不能在线进行move期间会影响dml但是不影响selectalter table tablename move tablespace [tablespacename]Postgresql没有表碎片和索引碎片的概念它只有死元组的概念和膨胀的概念死元组就是已经(对应的删除或更新会话已提交)被删除或更新的行但是仍然物理地存在于堆表中如果看到一张表中某行对应的隐藏字段xmax不为0,说明这张中这行数据正在被其他会话删除或更新但是还没有提交因为还没有提交所以表中某行对应的隐藏字段xmax不为0时就代表该行不是死元组。也就是说我们看不到的行才有可能是死元组因为新会话和新事务不会看到死元组。UPDATE创建一个新的行版本(新元组)并将旧元组标记为“死元组”。相当于删除旧行并插入新行旧行的xmax隐藏字段会被设置为执行更新事务的ID更新事务提交后旧行不再被未来会话可见新行的xmin会被设置为执行更新事务的ID旧行就变成了死亡行(元组)了。DELETE直接将目标元组标记为“死元组”。当一个事务删除一行时该行的xmax隐藏字段会被设置为执行删除事务的ID删除事务提交后这行不再被未来会话可见这时候就变成了死亡行(元组)了这个死亡行(元组)不会在磁盘上被物理删除而是继续保留在表中被标记为死亡行(元组)直到VACUUM进程将其回收。表膨胀死元组在表中大量堆积引发的表占用的物理存储空间远大于其实际有效数据所需空间的现象处理表膨胀的方法VACUUM或AUTOVACUUM索引膨胀(表膨胀引起的索引膨胀)索引不会自动删除死元组对应的索引条目Postgresql没有类似Oracle的索引组织表也没有类似Sqlserver的聚簇索引的概念但是Postgresql有聚簇表的概念(create index indexname on tablename(columnname),cluster tablename using indexname),Postgresql索引并不直接存储表数据(元组)本身。索引存储的是索引键值以及堆元组物理位置的指针TID(Tuple IDentifier)。所以PostgreSQL中的死元组在未被清理之前其对应的索引项仍然会保留在索引中。也就是说索引条目不会自动删除死元组对应的TID索引条目仍会指向死元组的TID。索引膨胀导致索引扫描需要额外检查当执行一个使用索引的查询时数据库引擎会遍历索引条目获取TID然后根据TID去堆文件中读取对应的元组如果检查发现该元组是一个死元组(对当前事务不可见)那么丢弃这个元组并继续处理下一个索引条目或寻找下一个匹配的索引条目索引扫描需要“跳过”更多的无效条目才能找到真正可见的元组这样导致索引扫描变慢。处理索引膨胀的方法1、VACUUM或AUTOVACUUM:清理堆表的死元组时VACUUM也会扫描相关的索引并删除那些指向已被清理的死元组的索引条目2、REINDEX如果索引膨胀非常严重REINDEX会重建整个索引重建后的索引只包含指向当前活跃元组的有效索引项从而彻底解决该索引的膨胀问题。Postgresql查看表行数大于1行但是膨胀率大于50%的表select schemaname,relname,n_live_tup,n_dead_tup from pg_stat_all_tables where n_live_tup10000 and n_dead_tup/(n_live_tupn_dead_tup)0.5;了解死元组的例子以下会话是按时间顺序从前往后会话1testdb# create table table1 (hid int,hid1 varchar(50));testdb# SELECT txid_current();txid_current-------±----766testdb# insert into table1 values (1,‘1’);testdb# SELECT ctid, xmin, xmax, * FROM table1;ctid | xmin | xmax | hid | hid1-------±-----±-----±----±-----(0,1) | 767 | 0 | 1 | 1会话2–不提交更新一行testdb# begin;testdb*# update table1 set hid99 where hid1;会话1看到表的xmax不为0,因为会话2的更新还没有提交testdb# SELECT ctid, xmin, xmax, * FROM table1;ctid | xmin | xmax | hid | hid1-------±-----±-----±----±-----(0,1) | 767 | 768 | 1 | 1会话3死元组数目n_dead_tup为0因为会话2的更新还没有提交testdb# select relname,n_tup_ins,n_tup_upd,n_tup_del,n_live_tup,n_dead_tupfrom pg_stat_user_tables where relname‘table1’;relname | n_tup_ins | n_tup_upd | n_tup_del | n_live_tup | n_dead_tup---------±----------±----------±----------±-----------±-----------table1 | 1 | 0 | 0 | 1 | 0(1 row)会话2–提交更新testdb*# end;会话1xmax不为0但是xmin变成了提交之前xmax的值testdb# SELECT ctid, xmin, xmax, * FROM table1;ctid | xmin | xmax | hid | hid1-------±-----±-----±----±-----(0,2) | 768 | 0 | 99 | 1会话3死元组数目n_dead_tup为1因为会话2的更新已经提交testdb# select relname,n_tup_ins,n_tup_upd,n_tup_del,n_live_tup,n_dead_tupfrom pg_stat_user_tables where relname‘table1’;relname | n_tup_ins | n_tup_upd | n_tup_del | n_live_tup | n_dead_tup---------±----------±----------±----------±-----------±-----------table1 | 1 | 1 | 0 | 1 | 1(1 row)会话2–不提交删除一行testdb# begin;testdb*# delete from table1 where hid99;会话1看到表的xmax不为0,因为会话2的删除还没有提交testdb# SELECT ctid, xmin, xmax, * FROM table1;ctid | xmin | xmax | hid | hid1-------±-----±-----±----±-----(0,2) | 768 | 769 | 99 | 1会话3死元组数目n_dead_tup还是1没有增加因为会话2的删除还没有提交testdb# select relname,n_tup_ins,n_tup_upd,n_tup_del,n_live_tup,n_dead_tupfrom pg_stat_user_tables where relname‘table1’;relname | n_tup_ins | n_tup_upd | n_tup_del | n_live_tup | n_dead_tup---------±----------±----------±----------±-----------±-----------table1 | 1 | 1 | 0 | 1 | 1会话2–提交删除testdb*# end;会话1看不到这一行了因为删除了testdb# SELECT ctid, xmin, xmax, * FROM table1;ctid | xmin | xmax | hid | hid1------±-----±-----±----±-----(0 rows)会话3活动元组n_live_tup为0死元组数目n_dead_tup为2因为一次更新提交一个删除提交testdb# select relname,n_tup_ins,n_tup_upd,n_tup_del,n_live_tup,n_dead_tupfrom pg_stat_user_tables where relname‘table1’;relname | n_tup_ins | n_tup_upd | n_tup_del | n_live_tup | n_dead_tup---------±----------±----------±----------±-----------±-----------table1 | 1 | 1 | 1 | 0 | 2
延伸阅读

更多相关文章

2026/11/18 4:50:01

参考中公华图选择央国企就业指导,建议比较本地教研深度

央国企就业指导选择指南:对比头部品牌与本地教研深度的差异在当前就业环境下,随着编制资源的竞争日益激烈,越来越多的求职者将目光投向了国家电网、烟草公司、国有银行等稳定性较高的央国企岗位。这类企业的选拔机制通常较为严谨,…

2026/11/20 6:57:38

聚合增长GEO规模怎么样

顺应AI搜索变革浪潮,锚定制造产业升级方向AI搜索技术的普及正在重构全球商业获客逻辑,也为传统制造业的数字化营销转型带来了全新机遇与挑战。当越来越多的B端采购决策开始依托生成式AI获取信息,传统营销路径逐渐失效,制造企业普遍…

2026/11/20 5:13:11

聚合增长GEO值得信赖吗

苏州聚合增长信息科技有限公司是国内专注于制造业领域的生成式引擎优化(GEO)代运营服务商,核心提供聚合AI GEO国内版与国际版代运营服务,为制造企业打造AI搜索时代的全域营销增长解决方案,解决AI搜索获客痛点,打通从品牌曝光到商业…

2026/11/20 6:57:27

拆除电缆需要电工证吗?保姆级教程帮你一次过实操考试

拆除电缆需要电工证吗?保姆级教程帮你一次过实操考试 实操考试心里没底怕挂科?你不是一个人,很多电工证考生都经历过。特别是像拆除电缆这种操作,既涉及高压电,又涉及线路安全,没有专业证件和实操技能是万万不能上岗的。别担心,这篇保姆级教程,从岗位…

2026/11/20 6:57:27

证书过期了不知道怎么复审延期?电工证登高证报名费+考前押题全解析

证书过期了不知道怎么复审延期?电工证登高证报名费+考前押题全解析 证书过期了不知道怎么复审延期?你不是一个人。很多像你一样在信阳浉河区房建工程干现场的,都遇到过这种情况。特别是电工证和登高证这些特种作业操作证,一旦过期不及时办理复审,不仅影…

2026/11/20 6:57:27

在澳洲怎么考电工证电子证书能用吗

在澳洲怎么考电工证电子证书能用吗 之前考的证在外省能不能转过来,这是很多特种作业人员最关心的问题,尤其是那些打算在澳洲工作的朋友。你可能会担心自己的 电子证书…

2026/11/20 6:57:27

邹城学电工证真实经历:外省证书能不能转过来

邹城学电工证真实经历:外省证书能不能转过来 之前考的证在外省能不能转过来,这几乎是所有从外地来邹城发展或者在邹城工作的电工从业者最关心的问题。我也遇到过不少朋友因为证书是在老家考的,后来因为工作调动或者换地方,想在邹城继续执业,却不知道能不…

2026/11/20 6:57:27

蓟县考电工证电话查询多久拿证?找工作先看这些干货

蓟县考电工证电话查询多久拿证?找工作先看这些干货 你是不是也像我一样,为了找工作到处跑,最后发现 急需特种作业证才能上岗找工作 ?尤其是电工证,几乎是电气、建筑、机械等行业必备的“入场券”。但问题是, 蓟县考电工证电话查询多久拿证…

2026/11/20 6:52:26

电工证拿证流程2026河南最新政策全知道

电工证拿证流程2026河南最新政策全知道 你是不是也担心学历不高报不上电工证?2026年河南的政策其实比你想象的更灵活,只要符合年龄、健康条件,哪怕只有初中学历也能报名。郑州这边很多劳务班组的负责人,自己没考证,但手下工人考了证,干活更合规…

2026/11/19 22:14:34

电工证考试口诉怎么准备值不值得考

电工证考试口诉怎么准备值不值得考 实操考试心里没底怕挂科,这是大多数准备考电工证的人心里最真实的感受。尤其是面对【电工证考试口诉】环节,很多人不知道该怎么准备,甚至担心自己说不好就被淘汰。其实,只要掌握方法, 值不值得考…

2026/11/20 6:48:54

桑植县考电工证千万别踩坑!报名全流程教你避坑

桑植县考电工证千万别踩坑!报名全流程教你避坑 你是不是也遇到过这种情况:想考个电工证,但不知道从哪儿下手,怕被中介坑,怕花冤枉钱,更怕考了证还不能用?别急,这正是你该看这篇文章的原因。在桑植县考电工证,确实有很多人被坑过,但只要你知道流程、…

2026/11/20 0:00:30

电力电缆制作特种证过期了怎么复审?郑州报考避坑指南全解析

电力电缆制作特种证过期了怎么复审?郑州报考避坑指南全解析 证书过期了不知道怎么复审延期?作为一名在郑州从事电力电缆制作特种作业多年的项目管理员,我亲身经历过证书过期后被责令停工的尴尬场面。那天,我在平顶山某工地进行电缆敷设作业时,被当地应急…

2026/11/20 0:00:30

葫芦岛考低压电工证过期怎么复审?考前押题帮你稳过

葫芦岛考低压电工证过期怎么复审?考前押题帮你稳过 证书过期了不知道怎么复审延期,耽误工作还影响收入?别急,今天就给你讲讲葫芦岛考低压电工证的有效期、复审流程,还有考前押题怎么用,保证你少走弯路。 低压电工证有效期与复审规则…

2026/11/20 0:00:30

济阳从哪里学电工证良心建议:怕考不过白交培训费?看完这篇全明白

济阳从哪里学电工证良心建议:怕考不过白交培训费?看完这篇全明白 你是不是也担心,花了钱去学电工证,结果考试还是不过,白白浪费时间和金钱?特别是济阳这边,培训机构多得是,但真正靠谱的又有几个?今天我就来给你掏心窝子,讲讲济阳从哪里学电工证的良…

2026/11/20 6:49:17

面向复杂光照与角度的宠物面部识别鲁棒性增强策略

在智慧养宠时代,基于视觉的宠物身份识别技术正逐步从实验室走向家庭应用。然而,与配合度高的人类不同,宠物是典型的“非合作生物识别对象”——它们不会主动配合摄像头调整姿态,更无法控制光照环境。实际应用中,我们常…

2026/11/19 17:18:32

SSM电子书店源码全解析:结构、原理、部署与排错实战

/* MD / 富文本中的 .toc(含博客园搬家等嵌套结构);.toc-box 在侧栏,不受影响 */#content_views .toc,/* 编辑器常在目录前后插入空 p(:empty 仍占 20px),一并去掉避免顶空隙 */#content_views.markdown_views > p:empty:has(+ .toc),#content_views.markdown_views …

还想了解更多?直接咨询顾问

免费诊断 + 免费方案 + 透明报价。

全国咨询热线400-8866-253
免费获取方案
☎咨询二维码 ☎ ↑