问题的答案

问题的历史

软删除模式作为硬删除的替代方案，用于审计跟踪和数据恢复。早期实现使用简单的布尔标志或时间戳列，但开发人员在将这些标志纳入标准B树索引时很快遇到了性能下降的问题。随着部分索引（PostgreSQL）和过滤索引（SQL Server）在2000年代中期的广泛采用，这一问题变得尤为突出，这些索引允许仅对活动记录进行索引。了解选择性估算——查询计划器如何预测满足条件的行的百分比——在比较完整的组合索引和部分索引策略时变得至关重要。

问题

当将软删除标志（例如is_deleted）添加到类似于(is_deleted, user_id, created_at)的组合索引时，数据库优化器可能会错误计算过滤WHERE is_deleted = false的查询的行选择性。如果90%的行是活动的，优化器可能会选择顺序扫描而不使用索引扫描；反之，如果分布不均，它可能不适当地偏好索引。部分索引 (WHERE is_deleted = false) 仅存储活动行，保证了高选择性，但标准组合索引存储所有行，导致索引膨胀和模糊的基数估算，当统计信息不能准确反映软删除分布时。

解决方案

在PostgreSQL中实现部分索引或在SQL Server中实现过滤索引，以完全排除软删除的行，如果需要，可为已删除的数据使用单独的索引。对于缺乏部分索引的MySQL或数据库，仅在活动数据集较小的情况下，使用组合索引将软删除标志作为首列；否则，根据删除状态对表进行分区。对批量删除后明确分析表统计信息，以防止过时的直方图。在查询活动记录时，使用来自部分索引定义的确切谓词 (WHERE is_deleted = false) 以确保优化器识别索引的适用性。

代码示例

-- PostgreSQL: 仅针对活动记录的部分索引
CREATE INDEX idx_active_users_email 
ON users(email) 
WHERE is_deleted = false;

-- SQL Server: 过滤索引等效
CREATE INDEX IX_Active_Users_Email 
ON Users(Email) 
WHERE IsDeleted = 0;

-- 利用部分索引的查询
SELECT * FROM users 
WHERE email = 'alice@example.com' 
AND is_deleted = false;

生活中的案例

一家管理1000万用户记录的SaaS平台在按创建日期过滤活动用户时，其管理面板遭遇严重慢速。最初，他们在PostgreSQL上使用组合索引(is_deleted, created_at)，假设它能加速WHERE is_deleted = false ORDER BY created_at的查询。然而，随着数据集增长到80%的软删除历史账户，查询开始耗时8-12秒，因为计划者低估了扫描膨胀索引的成本。

解决方案A：维护组合索引并强制索引使用。 这种方法使用SET enable_seqscan = off或查询计划提示来强制使用索引。虽然它在特定查询上暂时改善了性能，但它增加了维护负担，并且在数据分布变化时，通常会强迫对其他访问模式的不理想计划。该解决方案也未能解决导致存储和VACUUM开销增加的基础索引膨胀问题。

解决方案B：为活动和已删除记录创建单独的部分索引。 实现CREATE INDEX idx_active_created ON users(created_at) WHERE is_deleted = false使索引大小减少了80%，并允许计划者准确估算200万活动行对比800万已删除行。查询时间减少到40毫秒，但需要重构所有应用查询，以确保is_deleted = false谓词仍保持显式，而不是被包裹在函数中或被隐藏在模糊条件的视图后。

团队选择了解决方案B，因为它提供了可持续的性能，而无需维护查询提示。结果是查询延迟减少了95%，并消除了由先前过大组合索引引起的周期性VACUUM膨胀问题。监控确认了仪表板主要用例的响应时间保持在亚秒级。

候选人通常忽视的内容

软删除时间戳列中的NULL值（使用NULL表示活动，使用时间戳表示已删除）对部分索引使用与布尔标志方法的影响是什么？

当使用可空的deleted_at时间戳时，部分索引如WHERE deleted_at IS NULL在PostgreSQL处理NULL可索引性方面面临挑战。与布尔标志= false显式且可索引不同，IS NULL条件要求计划者识别索引适用性，如果查询使用参数化语句，则无法证明参数将始终为NULL。此外，将deleted_at = CURRENT_TIMESTAMP的更新导致活动记录的部分索引中出现索引膨胀，因为行从中被移除，而布尔标志的更新只是翻转位，但仍然在完整的组合索引中。可空的方法需要更频繁的ANALYZE调用，并仔细考虑索引填充因素，以应对删除状态变化的高流动性。

为什么即使删除频率较低，包含软删除列的覆盖索引也可能导致写入速度比预期慢？

覆盖索引（在PostgreSQL 11+或SQL Server中使用INCLUDE子句）附加is_deleted以避免表查找，实际上会降低写入性能，因为每个软删除操作（即UPDATE）都必须修改多个索引结构。当用户被软删除时，数据库必须在活动部分索引中将旧索引条目标记为无效，插入已删除记录索引中的新条目，并更新覆盖索引的堆指针。候选人常常忽视部分索引隔离了这种波动——只有特定的部分索引用于活动或已删除状态被修改——而覆盖索引则需要更新主表的主要索引结构，无论软删除状态如何，从而造成写入放大，影响事务吞吐量。

什么时候查询优化器即使在查询明确过滤已删除记录时也会忽略部分索引？

如果部分索引被定义为WHERE is_deleted = true用于审计查询，但应用程序使用预处理语句并为活动和已删除查询都使用参数$1，PostgreSQL可能会缓存一个通用计划，这个计划无法识别针对特定true情况的部分索引。这是因为预处理语句在参数值绑定之前生成计划，优化器无法证明$1 = true将始终匹配索引谓词。候选人忽视了动态SQL或重编译提示（在SQL Server中使用OPTION (RECOMPILE)，在PostgreSQL中使用字面值执行）是确保计划者看到具体值并将其与部分索引谓词匹配所必需的，而不是依赖于由于参数值的不确定性而默认为顺序扫描的通用计划。

当使用布尔标志实现软删除模式时，为什么将该标志添加到组合索引中有时会降低活动记录查询的性能，相比之下，使用部分索引策略表现如何？查询计划器的选择性估算在这两种方法之间有什么不同？