如何查看表碎片率最高的Top10表_系统资源视图高级查询-phpMyAdmin-PHP中文网

mysql碎片率需用data_free/(data_length+index_length)计算，oracle则关注hwm与块利用率，索引碎片需单个analyze并查index_stats，binlog/归档日志及临时段等隐形空间大户易被忽略。

mysql：查 `information_schema.tables` 算 `data_free` 占比

mysql 没有内置“碎片率”字段，得自己算：用 data_free 除以 data_length + index_length。这个比值高，说明表空间里空着的、但被预占的页多，典型就是删过大量数据又没重建的 innodb 表。

执行以下语句查碎片率 Top10：

SELECT 
  table_schema AS '数据库',
  table_name AS '表名',
  ROUND((data_length + index_length) / 1024 / 1024, 2) AS '总大小(MB)',
  ROUND(data_free / 1024 / 1024, 2) AS '空闲空间(MB)',
  ROUND(100 * data_free / (data_length + index_length + 1), 2) AS '碎片率(%)'
FROM information_schema.TABLES 
WHERE engine = 'InnoDB' 
  AND table_schema NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys')
  AND data_length > 0
ORDER BY `碎片率(%)` DESC 
LIMIT 10;

data_free + 1 是防除零，不是笔误
只查 InnoDB 表，MyISAM 的 data_free 含义不同，别混用
如果某表 data_free 是 0，但实际磁盘没释放，可能是 innodb_file_per_table=OFF，空间还在 ibdata1 里，无法单表回收

Oracle：用 `USER_TABLES` 和 `USER_SEGMENTS` 对比实际 vs 分配空间

Oracle 的“碎片”更常指高水位线（HWM）和块利用率低——即分配了大量数据块（BLOCKS），但真实数据只占一小部分。关键看 AVG_ROW_LEN × NUM_ROWS 占 BLOCKS × 8192 的比例。

下面语句直接筛选出利用率

SELECT *
FROM (
  SELECT
    t.table_name,
    t.num_rows,
    ROUND(t.avg_row_len * t.num_rows / 1024 / 1024, 2) AS "实际数据(MB)",
    ROUND(s.bytes / 1024 / 1024, 2) AS "分配空间(MB)",
    ROUND((t.avg_row_len * t.num_rows) / NULLIF(s.bytes, 0), 4) AS "利用率"
  FROM user_tables t
  JOIN user_segments s ON t.table_name = s.segment_name
  WHERE s.segment_type = 'TABLE'
    AND t.num_rows > 1000000
    AND t.avg_row_len > 0
    AND s.bytes > 0
  ORDER BY "利用率" ASC
)
WHERE ROWNUM

必须确保 user_tables 和 user_segments 统计是新鲜的，否则结果失真；执行前建议先跑 DBMS_STATS.GATHER_SCHEMA_STATS
若 AVG_ROW_LEN 为 0 或 NUM_ROWS 严重不准，说明统计信息过期，碎片判断就不可信
小表（BLOCKS ）不参与此计算——因为 Oracle 默认一次分配至少一个 extent，小表的“浪费”是设计使然，不是真碎片

Oracle：查索引碎片率要先 `ANALYZE INDEX ... VALIDATE STRUCTURE`

Oracle 索引碎片不能靠字典视图直接算，必须先对索引做结构分析，再查 index_stats。这是硬性步骤，跳过就看不到真实删除标记行（del_lf_rows）。

批量查所有索引碎片率（>30% 视为需重建）：

SELECT 
  name AS index_name,
  height,
  lf_rows,
  del_lf_rows,
  ROUND((del_lf_rows / NULLIF(lf_rows, 0)) * 100, 2) AS "碎片率(%)",
  pct_used
FROM index_stats
WHERE del_lf_rows > 0
  AND (del_lf_rows / NULLIF(lf_rows, 0)) > 0.3;

每次只能分析一个索引：ANALYZE INDEX idx_name VALIDATE STRUCTURE ONLINE;，不能通配
index_stats 是会话级临时视图，每分析一个索引，它就被覆盖一次，所以得串行处理或写 PL/SQL 批量抓取
pct_used 低于 75% 也值得警惕——说明叶子块填充率差，即使碎片率不高，也可能影响范围扫描效率

别漏掉日志和临时文件这些“隐形大户”

查表碎片只是磁盘分析的第一步。真正吃掉空间的，往往是没被纳入 information_schema.TABLES 或 USER_SEGMENTS 统计的文件：

MySQL 的 binlog：用 SHOW BINARY LOGS; 查列表，再用 ls -lh /var/lib/mysql/mysql-bin.* 看体积；清理前确认从库已拉完，再 PURGE BINARY LOGS
Oracle 的归档日志：查 RECOVERY_FILE_DEST 或 V$RECOVERY_AREA_USAGE，尤其注意 ARCHIVED LOG 占比突增
两者共有的临时段：MySQL 查 INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO（8.0+），Oracle 查 V$SORT_SEGMENT 或 DBA_TEMP_FREE_SPACE