数据库核心术语知识库

定义

碎片 (Fragmentation) 是指数据库表和索引中存在的未使用或浪费的空间,这些空间由于数据的插入、更新和删除操作而产生,导致数据存储不紧凑,降低了 I/O 效率和存储空间利用率。

碎片分为两种类型:

内部碎片:页内的空闲空间
外部碎片:页之间的不连续分布

详细笔记

核心原理

碎片的产生原因

1. 页分裂 → 页内空间利用率降至 50%
2. 删除数据 → 页内留下空洞
3. 变长字段更新 → 原位置放不下,移动到其他位置
4. 随机插入 → 数据物理顺序与逻辑顺序不一致

碎片的类型

一、内部碎片 (Internal Fragmentation)

页内的浪费空间:

Page 1: [记录1, 记录2, 空闲空间..., 记录3]
    ↑          ↑
   已使用 60%        空闲 40%

原因:
- 页分裂后预留空间
- 删除记录后的空洞
- 变长字段的预留空间

二、外部碎片 (External Fragmentation)

页之间的不连续:

逻辑顺序: Page 1 → Page 2 → Page 3
物理顺序: Page 3 → Page 1 → Page 2  (分散在磁盘不同位置)

影响:
- 顺序扫描时需要随机 I/O
- 预读机制失效
- 缓存命中率降低

碎片的影响

性能影响

1. I/O 效率下降

sql

-- 场景:扫描 100 万条记录

无碎片:
  - 数据紧凑,占用 1000 个页
  - 顺序 I/O: 1000 次读取
  - 执行时间: 约 1 秒

高碎片(50% 碎片率):
  - 数据分散,占用 2000 个页
  - 随机 I/O: 2000 次读取
  - 执行时间: 约 10 秒
  
性能下降: 10 倍

2. Buffer Pool 利用率低

Buffer Pool: 8GB

无碎片:
  - 可缓存 50 万个页
  - 覆盖 5000 万条记录
  - 命中率: 95%

高碎片:
  - 同样 8GB,但只能缓存 25 万个有效页
  - 覆盖 2500 万条记录
  - 命中率: 70%
  
缓存效率下降: 50%

3. 预读失效

sql

-- InnoDB 预读机制:一次性读取相邻的 64 个页

无碎片:
  - 相邻页在磁盘上也是连续的
  - 预读命中率: 90%+
  
高碎片:
  - 相邻页在磁盘上分散
  - 预读的大部分页用不上
  - 预读命中率: 30%
  
预读效率下降: 67%

存储空间影响

表数据: 10GB

碎片率 10%:
  - 实际占用: 11GB
  - 浪费: 1GB

碎片率 50%:
  - 实际占用: 20GB
  - 浪费: 10GB

碎片率 70%:
  - 实际占用: 33GB
  - 浪费: 23GB

检测碎片

MySQL 检测方法

方法一:SHOW TABLE STATUS

sql

SHOW TABLE STATUS LIKE 'users'\G

-- 输出:
-- Name:    users
-- Data_length:   16777216  -- 数据占用字节
-- Index_length:  8388608   -- 索引占用字节
-- Data_free:   4194304   -- 空闲空间(碎片)
-- Auto_increment:  100000

-- 计算碎片率:
-- 碎片率 = Data_free / (Data_length + Index_length)
--  = 4194304 / (16777216 + 8388608)
--  = 16.7%

方法二:INFORMATION_SCHEMA

sql

SELECT 
  TABLE_SCHEMA,
  TABLE_NAME,
  ENGINE,
  DATA_LENGTH,
  INDEX_LENGTH,
  DATA_FREE,
  ROUND(DATA_FREE / (DATA_LENGTH + INDEX_LENGTH) * 100, 2) AS fragmentation_pct,
  TABLE_ROWS,
  ROUND(DATA_LENGTH / TABLE_ROWS, 2) AS avg_row_length
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys')
  AND DATA_LENGTH > 0
ORDER BY fragmentation_pct DESC
LIMIT 20;

-- 输出示例:
-- +--------------+------------+--------+-------------+----------------+-----------+---------------------+------------+----------------+
-- | TABLE_SCHEMA | TABLE_NAME | ENGINE | DATA_LENGTH | INDEX_LENGTH | DATA_FREE | fragmentation_pct | TABLE_ROWS | avg_row_length |
-- +--------------+------------+--------+-------------+----------------+-----------+---------------------+------------+----------------+
-- | shop   | orders   | InnoDB | 1073741824  | 536870912  | 536870912 | 33.33     | 10000000 | 107.37   |
-- | shop   | logs   | InnoDB | 2147483648  | 1073741824   | 1073741824| 33.33     | 50000000 | 42.95    |
-- +--------------+------------+--------+-------------+----------------+-----------+---------------------+------------+----------------+

方法三:Performance Schema

sql

-- 查看表的 I/O 统计
SELECT 
  OBJECT_NAME,
  COUNT_READ,
  COUNT_WRITE,
  SUM_NUMBER_OF_BYTES_READ,
  SUM_NUMBER_OF_BYTES_WRITE
FROM performance_schema.table_io_waits_summary_by_table
WHERE OBJECT_NAME = 'users';

-- 碎片多的表通常:
-- - COUNT_READ 很高(需要读取更多页)
-- - SUM_NUMBER_OF_BYTES_READ 很大

PostgreSQL 检测方法

sql

-- 查看表的膨胀情况
SELECT 
  schemaname,
  relname AS table_name,
  n_live_tup,     -- 活跃元组数
  n_dead_tup,     -- 死元组数(删除或更新后留下的)
  ROUND(n_dead_tup::numeric / NULLIF(n_live_tup + n_dead_tup, 0) * 100, 2) AS dead_tuple_pct,
  pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

-- 输出示例:
-- +------------+------------+------------+------------+------------------+------------+
-- | schemaname | table_name | n_live_tup | n_dead_tup | dead_tuple_pct | total_size |
-- +------------+------------+------------+------------+------------------+------------+
-- | public   | orders   | 10000000 | 3000000  | 23.08    | 15 GB  |
-- +------------+------------+------------+------------+------------------+------------+

-- dead_tuple_pct > 20% 表示需要清理

SQL Server 检测方法

sql

-- 查看索引碎片率
SELECT 
  t.name AS table_name,
  i.name AS index_name,
  ips.index_type_desc,
  ips.avg_fragmentation_in_percent,
  ips.fragment_count,
  ips.avg_fragment_size_in_pages,
  ips.page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') ips
JOIN sys.tables t ON ips.object_id = t.object_id
JOIN sys.indexes i ON ips.object_id = i.object_id AND ips.index_id = i.index_id
WHERE ips.avg_fragmentation_in_percent > 10
  AND ips.page_count > 100
ORDER BY ips.avg_fragmentation_in_percent DESC;

-- 建议:
-- avg_fragmentation_in_percent > 30%: 重建索引
-- 10% - 30%: 重组索引
-- < 10%: 无需处理

碎片阈值参考

碎片率	评价	建议操作
0-10%	优秀	无需处理
10-20%	良好	监控即可
20-30%	一般	计划维护
30-50%	较差	尽快优化
> 50%	严重	立即处理

消除碎片的策略

策略一:OPTIMIZE TABLE(MySQL)

sql

-- 重建表和索引,消除碎片
OPTIMIZE TABLE users;

-- 执行过程:
-- 1. 创建新的临时表
-- 2. 按主键顺序复制数据
-- 3. 重建所有索引
-- 4. 删除旧表,重命名新表
-- 5. 更新统计信息

-- 效果:
-- - 碎片率降至 0%
-- - 数据完全紧凑
-- - 索引重新平衡

-- 注意事项:
-- 1. 会锁表(ALTER TABLE 方式)
-- 2. 需要额外的磁盘空间(约等于表大小)
-- 3. 大表可能需要很长时间

自动化脚本:

sql

-- 自动优化碎片率超过 30% 的表
DELIMITER $$
CREATE PROCEDURE optimize_fragmented_tables()
BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE tbl_schema VARCHAR(64);
  DECLARE tbl_name VARCHAR(64);
  DECLARE frag_pct DECIMAL(5,2);
  
  DECLARE cur CURSOR FOR
    SELECT TABLE_SCHEMA, TABLE_NAME,
     ROUND(DATA_FREE / (DATA_LENGTH + INDEX_LENGTH) * 100, 2)
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_SCHEMA NOT IN ('mysql', 'information_schema', 
             'performance_schema', 'sys')
    AND DATA_LENGTH > 0
    AND DATA_FREE / (DATA_LENGTH + INDEX_LENGTH) > 0.3;
  
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
  
  OPEN cur;
  
  read_loop: LOOP
    FETCH cur INTO tbl_schema, tbl_name, frag_pct;
    IF done THEN
    LEAVE read_loop;
    END IF;
    
    -- 记录日志
    INSERT INTO maintenance_log(table_name, fragmentation, action, execute_time)
    VALUES(CONCAT(tbl_schema, '.', tbl_name), frag_pct, 'OPTIMIZE', NOW());
    
    -- 执行优化
    SET @sql = CONCAT('OPTIMIZE TABLE ', tbl_schema, '.', tbl_name);
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
  END LOOP;
  
  CLOSE cur;
END$$
DELIMITER ;

-- 定期执行(如每周日凌晨 3 点)

策略二:ALTER TABLE FORCE(MySQL)

sql

-- 另一种重建表的方式
ALTER TABLE users FORCE;

-- 等价于:
ALTER TABLE users ENGINE=InnoDB;

-- 与 OPTIMIZE TABLE 的区别:
-- - OPTIMIZE TABLE: 还会更新统计信息
-- - ALTER TABLE FORCE: 只重建表,更快

策略三:VACUUM FULL(PostgreSQL)

sql

-- PostgreSQL 清理死元组和碎片

-- 普通 VACUUM(不锁表,但不释放空间给操作系统)
VACUUM users;

-- VACUUM FULL(锁表,完全重建,释放空间)
VACUUM FULL users;

-- 或使用 pg_repack 扩展(在线重建,不长时间锁表)
pg_repack -d your_database -t users

策略四:重建索引(SQL Server)

sql

-- SQL Server 重组索引(在线操作)
ALTER INDEX idx_name ON users REORGANIZE;

-- SQL Server 重建索引(可能锁表)
ALTER INDEX idx_name ON users REBUILD;

-- 根据碎片率选择策略
-- 10-30%: REORGANIZE
-- > 30%: REBUILD

策略五:重新排序插入

sql

-- 对于新表,按主键顺序插入可减少碎片

-- ❌ 随机插入
INSERT INTO users (user_id, name) VALUES
  (uuid_5, '用户5'),
  (uuid_2, '用户2'),
  (uuid_9, '用户9');

-- ✅ 排序后插入
INSERT INTO users (user_id, name) 
SELECT user_id, name FROM source_users 
ORDER BY user_id;

-- 效果:
-- - 数据物理顺序与逻辑顺序一致
-- - 减少页分裂
-- - 降低碎片率

预防碎片的策略

策略一:使用顺序主键

sql

-- ✅ 推荐:自增主键
CREATE TABLE orders (
  order_id INT AUTO_INCREMENT PRIMARY KEY
);

-- ❌ 避免:UUID 主键
CREATE TABLE users (
  user_id VARCHAR(36) PRIMARY KEY DEFAULT UUID()
);

-- 原因:
-- 顺序主键:新记录总是追加到末尾,碎片少
-- UUID:随机插入,频繁页分裂,碎片多

策略二:合理设置填充因子

sql

-- MySQL 8.0+
SET GLOBAL innodb_fill_factor = 90;  -- 留 10% 空间

-- PostgreSQL
CREATE TABLE users (...) WITH (FILLFACTOR = 90);

-- 优点:
-- - 为更新预留空间
-- - 减少页分裂和碎片产生

-- 缺点:
-- - 占用更多存储空间

策略三:定期维护计划

sql

-- 建立定期维护计划

-- MySQL:每周执行
CREATE EVENT weekly_optimize
ON SCHEDULE EVERY 1 WEEK
STARTS '2024-01-07 03:00:00'
DO
  CALL optimize_fragmented_tables();

-- PostgreSQL:配置 autovacuum
-- postgresql.conf
autovacuum = on
autovacuum_vacuum_threshold = 50
autovacuum_vacuum_scale_factor = 0.1  -- 10% 变化触发

-- SQL Server:维护计划
-- 每周日凌晨 2 点重建索引

策略四:分区表管理

sql

-- 大表使用分区,便于碎片管理

CREATE TABLE logs (
  log_id BIGINT,
  create_time DATETIME,
  message TEXT,
  PRIMARY KEY (log_id, create_time)
) PARTITION BY RANGE (TO_DAYS(create_time)) (
  PARTITION p202401 VALUES LESS THAN (TO_DAYS('2024-02-01')),
  PARTITION p202402 VALUES LESS THAN (TO_DAYS('2024-03-01')),
  PARTITION p_current VALUES LESS THAN MAXVALUE
);

-- 优势:
-- 1. 可以单独优化某个分区
-- 2. 删除旧分区直接释放空间,无碎片
-- 3. 维护窗口更小

碎片的实际案例

案例一:电商订单系统

sql

-- 问题:订单查询越来越慢

-- 检查碎片率
SELECT 
  TABLE_NAME,
  ROUND(DATA_FREE / (DATA_LENGTH + INDEX_LENGTH) * 100, 2) AS fragmentation
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'orders';

-- 结果: 45% (严重碎片)

-- 分析原因:
-- 1. 订单状态频繁更新(status 字段)
-- 2. 取消的订单被物理删除
-- 3. 新订单随机插入(非严格自增)
-- 4. 从未进行过维护

-- 解决方案:
-- 1. 立即优化
OPTIMIZE TABLE orders;

-- 2. 改为软删除
ALTER TABLE orders ADD COLUMN deleted TINYINT DEFAULT 0;
UPDATE orders SET deleted = 1 WHERE ...;  -- 标记删除
DELETE FROM orders WHERE deleted = 1 AND create_time < DATE_SUB(NOW(), INTERVAL 90 DAY);

-- 3. 建立维护计划
CREATE EVENT monthly_optimize_orders
ON SCHEDULE EVERY 1 MONTH
DO
  OPTIMIZE TABLE orders;

-- 效果:
-- - 查询性能提升 3 倍
-- - 存储空间减少 30%
-- - Buffer Pool 命中率从 70% 提升至 92%

案例二:日志系统

sql

-- 问题:日志表占用空间远超预期

-- 检查
SHOW TABLE STATUS LIKE 'application_logs'\G

-- 输出:
-- Data_length:  5368709120 (5GB)
-- Data_free:  10737418240  (10GB!)
-- 碎片率: 66.7%

-- 原因:
-- 1. 每天删除 7 天前的日志
-- 2. 删除不均匀,留下大量空洞
-- 3. TEXT 字段变长,更新时移动频繁

-- 解决方案:
-- 1. 改用分区表
ALTER TABLE application_logs_v2 PARTITION BY RANGE (TO_DAYS(create_time)) (...);

-- 2. 清理时删除分区
ALTER TABLE application_logs_v2 DROP PARTITION p_old;

-- 3. 压缩 TEXT 字段
ALTER TABLE application_logs_v2 ROW_FORMAT=COMPRESSED;

-- 效果:
-- - 空间占用从 15GB 降至 3GB
-- - 无碎片问题
-- - 清理速度从小时级降至毫秒级

最佳实践

监控碎片率:每周检查一次,建立告警机制
定期维护:根据业务特点制定维护计划

高频更新表:每周优化
中频更新表:每月优化
低频更新表:每季度优化

错峰执行:在业务低峰期执行 OPTIMIZE TABLE
预留空间:设置合适的 fillfactor,减少碎片产生
使用分区:大表分区,便于管理和维护
软删除:重要数据使用逻辑删除,避免频繁物理删除
顺序插入:尽量使用顺序主键,减少随机插入

关联术语

[[页分裂]]
[[页合并]]
[[OPTIMIZE TABLE]]
[[行溢出]]

参考资料

MySQL 官方文档: Defragmenting a Table
PostgreSQL 文档: Routine Vacuuming
Microsoft Docs: Reorganize and Rebuild Indexes
《高性能 MySQL》第 3 章:Schema 优化

定义 ​

详细笔记 ​

核心原理 ​

碎片的产生原因 ​

碎片的类型 ​

碎片的影响 ​

性能影响 ​

存储空间影响 ​

检测碎片 ​

MySQL 检测方法 ​

PostgreSQL 检测方法 ​

SQL Server 检测方法 ​

碎片阈值参考 ​

消除碎片的策略 ​

策略一:OPTIMIZE TABLE(MySQL) ​

策略二:ALTER TABLE FORCE(MySQL) ​

策略三:VACUUM FULL(PostgreSQL) ​

策略四:重建索引(SQL Server) ​

策略五:重新排序插入 ​

预防碎片的策略 ​

策略一:使用顺序主键 ​

策略二:合理设置填充因子 ​

策略三:定期维护计划 ​

策略四:分区表管理 ​

碎片的实际案例 ​

案例一:电商订单系统 ​

案例二:日志系统 ​

最佳实践 ​

关联术语 ​

参考资料 ​

定义