一次数据清理引发的教训：缺失外键索引导致 DELETE 性能问题

一次数据清理引发的教训：缺失外键索引导致 DELETE 性能问题

最近在做一次数据清理（data purge）时，我遇到了一个之前没有预料到的问题。 从业务逻辑上看，这只是一个很普通的 DELETE 操作，根据条件删除历史数据而已。

但实际执行时，DELETE 语句几乎卡住了。 没有报错，看起来也在运行，但迟迟没有完成。

问题出在哪里？

在 DBA 的帮助下，我们开始从数据库层面分析这个问题。 最终发现，真正的原因是：

部分外键（Foreign Key）字段缺少对应的索引。

在 Oracle 中，当对父表执行 DELETE 操作时，数据库需要检查子表中是否存在 引用该记录的外键数据。 如果外键列没有索引，Oracle 就只能对子表做全表扫描。

当数据量变大时，这种全表扫描会极大地拖慢 DELETE 操作， 甚至导致长时间锁表，看起来就像 SQL “卡住”了一样。

解决方案

在确认问题后，我们为所有缺失的外键字段补充了索引（涉及多个关联表）。 效果非常明显：

• DELETE 操作执行速度大幅提升
• 锁定时间明显缩短
• 数据库整体负载显著下降

SQL 没变，数据没变，只是补了索引，结果却完全不同。

如何定位缺失的外键索引

DBA 使用下面的 SQL 来检查哪些外键约束没有对应的索引。 以下示例中，schema 名、表名和索引名均已做匿名处理。

WITH fk_constraints AS (
    SELECT
        c.table_name,
        c.constraint_name                                       AS fk_name,
        LISTAGG(c.column_name, ', ')
            WITHIN GROUP (ORDER BY c.position)                  AS fk_columns
    FROM dba_cons_columns c
    JOIN dba_constraints k
        ON k.constraint_name = c.constraint_name
    WHERE k.constraint_type = 'R'
      AND c.owner            = 'APP_SCHEMA_X'
      AND k.owner            = 'APP_SCHEMA_X'
    GROUP BY c.table_name, c.constraint_name
)
SELECT
    fk.table_name,
    fk.fk_name,
    fk.fk_columns,
    idx.index_name              AS existing_index,
    idx.index_columns           AS index_columns,
    'CREATE INDEX ' || fk.fk_name ||
    ' ON ' || fk.table_name ||
    ' (' || fk.fk_columns || ');' AS suggested_fix
FROM fk_constraints fk
LEFT JOIN (
    SELECT
        ic.table_name,
        ic.index_name,
        LISTAGG(ic.column_name, ', ')
            WITHIN GROUP (ORDER BY ic.column_position) AS index_columns
    FROM dba_ind_columns ic
    WHERE ic.index_owner = 'APP_SCHEMA_X'
    GROUP BY ic.table_name, ic.index_name
) idx
    ON idx.table_name = fk.table_name
   AND idx.index_columns LIKE fk.fk_columns || '%'
WHERE idx.index_name IS NULL
ORDER BY fk.table_name, fk.fk_name;

这段 SQL 的作用包括：

• 列出所有外键约束
• 检查是否存在以外键列开头的索引
• 找出缺失索引的外键
• 生成建议的 CREATE INDEX 语句

经验总结

• Oracle 并不会自动为外键创建索引
• 缺失外键索引会严重影响 DELETE / UPDATE 性能
• 很多问题在数据量小时不明显，数据增长后才暴露
• “能用”的数据库结构，并不等于“设计良好”

回头看，这些索引本应该在最初的表结构设计或开发阶段就补齐， 但当时被忽略了。

结语

这是一次比较痛苦的线上问题，但也是一次非常有价值的学习经历。

如果你在生产环境中遇到 DELETE 语句执行缓慢、长时间锁表的问题， 第一件事就应该检查外键是否缺少索引。

希望这次记录，能帮你少踩一个坑。

❤️ Support This Blog

---

If this post helped you, you can support my writing with a small donation. Thank you for reading.

---

Coffee ☕ Thanks 🙏 Extra Support ❤️