一、引言

在数据库日常运维中，索引性能下降是一个常见但容易被忽视的问题。很多时候发现，明明表的数据量没有大幅增长，但查询却越来越慢。这往往是由索引碎片。
本文将从索引碎片的概念、产生原因、到优化索引的常见操作，带你理解索引碎片化。

二、什么是索引碎片？

2.1 索引页与页分裂

数据库索引通常采用B+树结构，数据存储在固定大小的页中。一个页可以存放多条索引记录，但页的容量是有限的。

当需要向一个已经写满的页插入新记录时，数据库会触发页分裂：

将原页的一部分数据移动到新页中；
新页的位置通常不是固定的，可能分配在表空间的任意位置。

2.2 碎片是如何产生的？

页分裂后，原页和新页都可能只填充了部分数据，留下空闲空间。如果后续数据插入模式是随机的，或者频繁发生删除、更新索引列的操作，就会导致：

大量页只填充了50%~70%的数据；
页与页之间物理顺序不连续，逻辑上相邻的索引键可能分布在相距很远的页上；
总的索引页数不断增加，但实际有效数据并未增多。

这就是索引碎片。

2.3 碎片的危害

扫描更多页：查询需要扫描的索引页变多，逻辑读增加；
IO放大：磁盘IO压力上升；
缓存效率下降：同样大小的Buffer Pool能缓存的索引数据变少；
查询变慢：范围扫描、全索引扫描性能明显下降。

三、碎片是怎么产生的？

3.1 常见产生碎片的操作

操作类型	说明
随机插入	主键不是顺序增长（如UUID），导致插入位置分散
更新索引列	更新索引列值相当于删除+插入，频繁更新造成页分裂
批量删除	删除后留下空洞，后续插入不一定能填满
批量更新索引列	一次更新大量索引键，引发大量页分裂和移动
分区表数据重分布	分区键更新导致数据跨分区移动

3.2 一个真实的膨胀过程

在一次测试中，我对一张1000万行的分区表TEST2的索引IDX_TEST2_ID进行监控：
初始健康状态：索引使用页数 29,776

SELECT INDEX_USED_SPACE('SYSDBA','IDX_TEST2_ID') FROM DUAL;

行号     INDEX_USED_SPACE('SYSDBA','IDX_TEST2_ID')
---------- -----------------------------------------
1          29776

经过500轮随机更新（批量更新索引列）

DECLARE
    VAR1 VARCHAR2(20);
    VAR2 VARCHAR2(20);
BEGIN
    FOR AA IN (SELECT PARTITION_NAME FROM DBA_TAB_PARTITIONS 
               WHERE TABLE_NAME = 'TEST2' AND TABLE_OWNER = 'SYSDBA')
    LOOP
        -- 提取分区序号（如 '01', '02' ... '010'）
        EXECUTE IMMEDIATE 'SELECT SUBSTR(''' || AA.PARTITION_NAME || ''', 3, LENGTH(''' || AA.PARTITION_NAME || ''') - 2) FROM DUAL' INTO VAR2;
        
        FOR I IN 1..500 LOOP
            -- 随机选取起始 ID（1 到 100万）
            SELECT ROUND(DBMS_RANDOM.VALUE(1, 1000000), 0) INTO VAR1 FROM DUAL;
            
            -- 1. 将选中范围的数据（约10000行）插入临时表
            EXECUTE IMMEDIATE 'INSERT INTO TMP SELECT ID FROM TEST2 PARTITION(' || AA.PARTITION_NAME || ') WHERE ID >= ''' || VAR1 || ''' AND ID < ''' || (TO_NUMBER(VAR1) + 10000) || '''';
            
            -- 2. 从原表删除这些行
            EXECUTE IMMEDIATE 'DELETE FROM TEST2 PARTITION(' || AA.PARTITION_NAME || ') WHERE ID IN (SELECT ID FROM TMP)';
            
            -- 3. 重新插入（修改 c1 为分区序号，c3 改为 '21'，模拟数据变更）
            EXECUTE IMMEDIATE 'INSERT INTO TEST2 PARTITION(' || AA.PARTITION_NAME || ') SELECT ID, ''' || VAR2 || ''', ''03'', ''21'' FROM TMP';
            
            -- 4. 清空临时表并提交
            DELETE FROM TMP;
            COMMIT;
        END LOOP;
    END LOOP;
END;
/

索引页数飙升至 245,584（膨胀8倍以上）

SELECT INDEX_USED_SPACE('SYSDBA','IDX_TEST2_ID') FROM DUAL;

行号     INDEX_USED_SPACE('SYSDBA','IDX_TEST2_ID')
---------- -----------------------------------------
1          245584

查询变慢、逻辑读急剧上升，碎片导致索引扫描成本急剧增加。

四、索引优化：常见操作

4.1 查看索引使用页数

-- 查询索引使用的页数
SELECT INDEX_USED_SPACE('SYSDBA','IDX_TEST2_ID') FROM DUAL;

-- 或使用索引ID
SELECT INDEX_USED_SPACE(33556749) FROM DUAL;

4.2 索引重组

重组是原地整理索引结构，合并空闲空间，使页填充度提高，但不改变索引对象ID：

SP_REORGANIZE_INDEX('SYSDBA', 'IDX_TEST2_ID');

特点：

耗时与碎片程度相关，碎片越严重越慢；
不回收表空间，只整理索引内部。

4.3 索引重建

重建是创建一个全新的索引对象，然后删除旧索引，彻底消除碎片。

-- 方式一：通过索引ID
CALL SP_REBUILD_INDEX('SYSDBA', 33556749);

-- 方式二：ALTER语句
ALTER INDEX SYSDBA.IDX_TEST2_ID REBUILD ONLINE;

特点：

完全重建B+树，效果最彻底；
耗时相对稳定，不受碎片程度影响；
可能短暂持有DDL锁，但ONLINE模式对DML影响较小。

五、小结

索引碎片是数据库性能的隐形杀手，它源于页分裂和随机DML，导致索引页数膨胀、IO增加。通过定期监控索引页数和逻辑读，可以及时发现碎片问题。
治理碎片的两大手段——重组和重建——各有优劣：

重组适合日常轻量维护，成本低；
重建适合碎片严重或高并发场景，效果彻底且稳定。

无论选择哪种方式，都应在业务低峰期执行，并确保应用层具备错误重试机制。只有这样，才能在保证业务连续性的同时，让索引始终保持高效状态。

索引碎片化与重组索引：从页分裂到索引优化