一次数据清理引发的教训：缺失外键索引导致 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.
Comments
Post a Comment