碎片的定义 指单个数据块/页内部的空间利用率不足。成因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