Oracle 与 MySQL 索引重建及表健康性检测

一、概念定义

索引重建（Index Rebuild）：指重新构造索引的物理存储结构——读取索引/表数据、重新排序并生成一棵全新的 B-tree 段，完成后用新段替换旧段。它不同于"索引分析（统计信息收集）"：前者重建物理结构，后者只更新优化器使用的统计信息。

表健康性检测：指对表的物理完整性、空间利用率、统计信息准确性、行迁移/链接、碎片化程度等维度进行体检，判断表是否存在损坏、膨胀或统计失真。它检测的是"表本身是否健康"，不等同于单纯收集统计信息。

边界澄清：索引重建 ≠ 表重组（reorg），表健康检测 ≠ 性能压测。两者关注点不同。

二、作用与适用场景

场景	关注点
索引状态异常（如 `ALTER TABLE ... MOVE` 后索引变 UNUSABLE）	重建恢复可用性
索引碎片化、B-tree 层级偏高、删除行占比大	重建/合并以提升访问效率
索引逻辑损坏（页结构错乱，非物理坏页）	重建修复
表存在大量页分裂、随机删除留下的空页	表重组回收空间
统计信息过期导致执行计划失真	分析（ANALYZE）刷新
怀疑表/索引物理损坏、升级迁移后	校验（CHECK/VALIDATE）

三、工作机制（含详细命令与步骤）

3.1 Oracle —— 索引重建

第 1 步：确认哪些索引需要重建

-- 普通索引：查看状态是否 UNUSABLE
SELECT owner, index_name, table_name, status
FROM   dba_indexes
WHERE  status = 'UNUSABLE';

-- 分区索引：DBA_INDEXES.STATUS 对分区索引恒为 'N/A'，须查分区级视图
SELECT index_owner, index_name, partition_name, status
FROM   dba_ind_partitions
WHERE  status = 'UNUSABLE';

关键点：分区索引在 DBA_INDEXES 中 STATUS 显示为 N/A，这是正常现象（表示"状态在分区级"），不是异常；真实状态要看 DBA_IND_PARTITIONS。

第 2 步：重建命令（按需选择）

-- 离线重建（默认，锁表，最快，但阻塞 DML）
ALTER INDEX schema.idx_name REBUILD;

-- 在线重建（重建期间允许 DML，靠 journal 表记录变更）
ALTER INDEX schema.idx_name REBUILD ONLINE;

-- 重建同时收集统计信息
ALTER INDEX schema.idx_name REBUILD ONLINE COMPUTE STATISTICS;

-- 分区索引重建单个分区
ALTER INDEX schema.idx_name REBUILD PARTITION p1 ONLINE;

-- 仅合并 B-tree 同级块碎片（不改变索引逻辑结构，代价小于 REBUILD）
ALTER INDEX schema.idx_name COALESCE;

-- 回收索引尾部未用空间
ALTER INDEX schema.idx_name DEALLOCATE UNUSED;

第 3 步：校验重建效果

ANALYZE INDEX schema.idx_name VALIDATE STRUCTURE;
SELECT name, height, blocks, br_blks, lf_blks, lf_rows, del_lf_rows,
       pct_used
FROM   index_stats;

原理：REBUILD 读取原索引或表数据，在索引所在表空间内先创建一个临时段存放新索引；重建完成后再丢弃旧段、把临时段重定义为正式索引段。因此重建需要索引表空间能同时容纳旧索引 + 新索引，空间不足会报 ORA-01652（注意报错指向的是索引表空间，不是用户默认临时表空间）。ONLINE 模式额外用日志表记录重建期间的 DML，最后合并，代价是额外开销与可能的字典不一致风险。COALESCE 只合并同层块、不改变树结构，所以不能用于 bitmap 索引。

3.2 MySQL —— 索引重建

方式一：重建单个二级索引（代价最小）

ALTER TABLE db.t DROP INDEX idx_x, ADD INDEX idx_x (col1, col2);

原理：二级索引不存储行数据，删除并重建不会重写表数据，因此代价最小、影响可控，是索引损坏/碎片的首选修复手段。

方式二：重建整表（连带重建所有索引）

OPTIMIZE TABLE db.t;

-- 等价/更明确的重建写法
ALTER TABLE db.t FORCE;
ALTER TABLE db.t ENGINE=InnoDB;

原理：InnoDB 中 OPTIMIZE TABLE 默认被映射为 ALTER TABLE 重建表（源码逻辑：ha_innobase::optimize() 默认返回 HA_ADMIN_TRY_ALTER），只有 innodb_optimize_fulltext_only=ON 时才真正只做全文索引优化。重建后索引页重排、目录重建、页头 PAGE_GARBAGE 归零（即页内不再有可回收的删除空间），所以它是消除碎片、修复逻辑损坏的标准手段。

⚠️ OPTIMIZE TABLE 期间会获取表级锁并产生大量 I/O，会阻塞业务读写，必须放在业务低峰窗口执行。

在线 DDL 提示：MySQL 5.6+ 对二级索引的增删支持 ALGORITHM=INPLACE 在线方式，执行前建议显式指定算法以确认是否在线：

ALTER TABLE db.t ADD INDEX idx_x (col1), ALGORITHM=INPLACE, LOCK=NONE;

3.3 Oracle —— 表健康性检测

① 统计信息（优化器健康）

-- 收集表统计信息（推荐，级联索引）
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
    ownname          => 'SCHEMA',
    tabname          => 'TABLE_NAME',
    cascade          => TRUE,          -- 同时收集索引统计
    degree           => 4,             -- 并行度
    method_opt       => 'FOR ALL COLUMNS SIZE AUTO'
  );
END;
/

-- 单独收集索引统计（不排序，资源消耗低于表分析）
BEGIN
  DBMS_STATS.GATHER_INDEX_STATS(ownname => 'SCHEMA', indname => 'IDX_NAME');
END;
/

② 行迁移/行链接检测

ANALYZE TABLE schema.t LIST CHAINED ROWS INTO chained_rows;
SELECT COUNT(*) AS chained_cnt FROM chained_rows;

③ 段/空间健康（段顾问 + 空闲空间）

-- 段顾问发现行链接等问题：查看建议
SELECT owner, task_name, message
FROM   dba_advisor_findings;

-- 表空间空闲区（判断表空间碎片）
SELECT tablespace_name, COUNT(*) AS free_extents, SUM(bytes)/1024/1024 AS free_mb
FROM   dba_free_space
GROUP  BY tablespace_name;

④ 结构校验

ANALYZE TABLE schema.t VALIDATE STRUCTURE CASCADE;

原理：表分析（ANALYZE TABLE ... COMPUTE STATISTICS）需对整表排序，消耗大量 CPU/磁盘；索引分析不排序、消耗较小——所以建议表与索引分开分析。行迁移/链接会通过 LIST CHAINED ROWS 落到指定表，配合段顾问判断是否需要 ALTER TABLE ... MOVE 重组。注意：ALTER TABLE ... MOVE 会改变行物理位置（ROWID），导致表上所有索引立即变为 UNUSABLE，MOVE 之后必须重建索引。

3.4 MySQL —— 表健康性检测

① 完整性检查

CHECK TABLE db.t;            -- 完整检查，返回 OK / Corrupt
CHECK TABLE db.t QUICK;      -- 快速巡检，只查索引统计，不逐行扫描
CHECK TABLE db.t FOR UPGRADE;-- 升级/迁移后表结构、索引兼容性巡检
CHECKSUM TABLE db.t;         -- 计算校验和（可做主从/前后对比）

② 统计信息刷新

ANALYZE TABLE db.t;                       -- 刷新索引基数统计
ANALYZE TABLE t1, t2, t3;                 -- 一次分析多表
ANALYZE TABLE db.t UPDATE HISTOGRAM ON col1, col2 WITH 100 BUCKETS; -- 8.0 直方图

③ 碎片与空间检查

SHOW TABLE STATUS LIKE 't'\G
-- 关注 Data_free（已分配未使用空间，InnoDB 中通常是碎片）、
--      Data_length、Index_length、Rows

SELECT table_name, table_rows, data_length, index_length, data_free,
       ROUND(data_free/(data_length+index_length+1)*100,2) AS frag_pct
FROM   information_schema.tables
WHERE  table_schema = 'db'
  AND  data_free > 100*1024*1024;    -- 只看碎片 >100MB 的表

-- 检查统计信息（基数）是否明显失真
SELECT table_name, index_name, seq_in_index, column_name, cardinality
FROM   information_schema.statistics
WHERE  table_schema = 'db' AND table_name = 't';

SHOW INDEX FROM db.t;

④ 命令行批量巡检工具

mysqlcheck -c  db t          # 检查（--check）
mysqlcheck -a  db t          # 分析（--analyze）
mysqlcheck -o  db t          # 优化（--optimize，即重建表）

原理：CHECK TABLE 内部调用 btr_validate_index 逐索引校验 B+ 树结构，发现损坏时输出 ER_NOT_KEYFILE（B-tree corrupted）或 ER_TABLESPACE_MISSING；配合 SHOW TABLE STATUS/错误日志可定位具体坏页的 space id + page no。ANALYZE TABLE 刷新 records_per_key 与基数，统计过期会让优化器错误选择全表扫描；MySQL 8.0 还支持列直方图改善倾斜列的选择性估计。Data_free 长期偏大说明碎片严重，需 OPTIMIZE TABLE / ALTER TABLE ... FORCE 重建回收。

四、相关对象与观察入口

Oracle

对象	用途
`DBA_INDEXES` / `USER_INDEXES`	索引状态（STATUS）、类型、所属表
`DBA_IND_PARTITIONS`	分区索引的分区级状态
`INDEX_STATS`	`VALIDATE STRUCTURE` 后查看 B-tree 高度、叶块、删除行等
`DBA_FREE_SPACE`	表空间空闲区，判断表空间碎片
`DBA_SEGMENTS` / `DBA_TABLES`	段大小与表属性
`DBA_ADVISOR_FINDINGS`	段顾问的行链接/重组建议
动态性能视图	可观察在线重建相关等待（如在线索引重建合并阶段）

MySQL

对象	用途
`information_schema.TABLES`	表大小、`DATA_FREE` 碎片、行数
`information_schema.STATISTICS`	索引基数 `CARDINALITY`、列顺序
`SHOW TABLE STATUS`	快速评估碎片与空间
`SHOW INDEX`	索引结构与基数
错误日志	坏页 space id / page no 定位

五、常见误区与边界

"索引分析"当成"索引重建"：ANALYZE 只更新统计信息，不改变物理结构；索引损坏或 UNUSABLE 时分析无效，必须 REBUILD。
Oracle 分区索引 STATUS='N/A' 被误判为异常：这是正常值，须查 DBA_IND_PARTITIONS。
ALTER TABLE ... MOVE 后忘记重建索引：MOVE 改变 ROWID，会使所有索引立即 UNUSABLE，必须随后 REBUILD。
重建不评估空间：Oracle 重建需索引表空间同时容纳新旧索引，空间不足报 ORA-01652（报错里的表空间是索引表空间）。
MySQL OPTIMIZE TABLE 当作轻量操作：它本质是重建表，会锁表并产生大量 I/O，必须在低峰执行。
SHOW TABLE STATUS 的 Rows 当真值：该行数不精确（可能偏差达 2 倍），不能作为准确基数依据；且 information_schema 统计缓存默认约 24 小时，需 ANALYZE 后核对。
国产库对比仅限功能点：本文仅对比 Oracle 与 MySQL 的功能与命令，不评判优劣。
COALESCE 适用范围：仅合并同层块，不能用于 bitmap 索引；它不改变逻辑结构，不能替代真正的 REBUILD。

六、延伸知识

Oracle 表/索引重组：ALTER TABLE ... MOVE、DBMS_REDEFINITION 在线重定义、段顾问（Segment Advisor）配合使用。
Oracle 在线重建风险：REBUILD ONLINE 有字典不一致、进程被 kill 后残留在线重建临时对象等已知边界，生产上需评估。
MySQL 索引损坏分级处置：二级索引损坏 → drop/add 重建；聚簇索引/数据字典损坏 → 导出重建或 innodb_force_recovery 处理。
碎片化治理：定期 ALTER TABLE ... FORCE / OPTIMIZE 可将碎片归零、整理半空页、回收 PAGE_FREE 删除垃圾空间，降低索引空间膨胀。
统计信息与执行计划：统计过期会直接影响优化器选择，可结合执行计划与基数视图联合判断。

posted on 2026-10-15 17:28 刘子毅阅读(23) 评论(0) 收藏举报

刷新页面返回顶部

liuziyi