碎片的定义： 指单个数据块/页内部的空间利用率不足。
成因：
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 ON
SET AUTOCOMMIT ON
CREATE 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行，直到最后1000002
INSERT 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.06
SQL> insert into table1 values(2000003,sysdate,‘1’,‘1’,‘1’,‘1’,‘1’);
1 row created.
Commit complete.
Elapsed: 00:00:00.06
Mysql查询表的碎片情况和如何解决碎片的方法，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 (online=on)
重建表上的某个索引
alter index index_name on table_name rebuild with (online=on)
重组索引：使用的系统资源最少,并且是联机操作。也就是说,不保留长期阻塞性表锁,且对基础表的查询或更新可以在ALTER INDEX REORGANIZE事务处理期间继续进行。 不更新表或索引的统计信息，只对叶子级别的索引进行碎片整理，不对树干级别的索引进行碎片整理。
重新组织表上的所有索引
alter index all on table_name reorganize
重新组织表上的某个索引
alter index index_name on table_name reorganize
Oracle查询表的碎片情况和如何解决碎片的方法
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 blocks>100 and (NUM_ROWSAVG_ROW_LEN/1024/1024/0.9)/(BLOCKS8192/1024/1024)<0.3
order 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但是不影响select
alter 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_tup>10000 and n_dead_tup/(n_live_tup+n_dead_tup)>0.5;
了解死元组的例子，以下会话是按时间顺序从前往后
会话1
testdb=# create table table1 (hid int,hid1 varchar(50));
testdb=# SELECT txid_current();
txid_current
-------±----
766
testdb=# 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 hid=99 where hid=1;
会话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_tup
from 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;
会话1
xmax不为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_tup
from 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 hid=99;
会话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_tup
from 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_tup
from 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
)
