DevOps知识中心
DevOps知识中心

索引损坏故障排除：如何重建和修复 PostgreSQL 索引

通过这份全面的指南，掌握排除和修复 PostgreSQL 索引的技巧。学习如何使用 `pg_stat_user_indexes` 和 `EXPLAIN ANALYZE` 等内置工具识别膨胀或损坏的索引。本文提供了使用 `REINDEX` 命令（包括其 `CONCURRENTLY` 选项）的详细分步说明，以在最小停机时间内高效重建索引。了解相关的维护命令、主动维护的最佳实践以及确保最佳查询性能和数据库健康的关键警告。

55 浏览量

故障排除损坏的索引：如何重建和修复 PostgreSQL 索引

PostgreSQL 以其健壮性和高性能而闻名，是一个先进的开源关系型数据库。其性能架构的一个关键组成部分是索引，它允许数据库快速定位数据，而无需扫描表中的每一行。然而，索引可能会随着时间的推移变得低效甚至损坏，从而导致查询性能和整体数据库健康状况显著下降。识别和修复这些问题是任何 PostgreSQL 管理员必备的技能。

这份综合指南将引导您了解诊断、重建和修复有问题的 PostgreSQL 索引所需的实用命令和策略。我们将探讨索引低效和损坏的原因，讨论如何使用内置工具识别此类索引，并提供使用 REINDEX 命令（包括其强大的 CONCURRENTLY 选项）以及其他相关维护命令的逐步说明。通过阅读本文，您将清楚地了解如何保持最佳索引健康状况，并确保您的 PostgreSQL 数据库以最高效率运行。

理解 PostgreSQL 索引及其常见问题

PostgreSQL 索引（最常见的是 B-树索引）是数据库搜索引擎可用于加速数据检索的专用查找表。可以将它们想象成书本后面的索引；您无需阅读整本书来查找某个主题，而是可以直接转到索引中列出的页码。当这些索引健康时，使用它们的查询运行速度极快。当它们不健康时，查询性能可能会急剧下降。

索引出现问题主要有两个原因：膨胀（bloat）和损坏（corruption）。

索引膨胀

索引膨胀是指索引结构中“死元组”（过时数据版本）的累积。在 PostgreSQL 中，当行被更新或删除时，数据的旧版本（及其对应的索引条目）不会立即删除。相反，它们被标记为“死的”，并最终通过 VACUUM 进程回收。如果 VACUUM 运行不够频繁或不够有效，或者更新/删除的速率很高，这些死元组就会积累，导致索引变得比实际需要更大。一个膨胀的索引会占用更多的磁盘空间，需要更多的 I/O 操作来扫描，甚至会降低查询加速的效率。

索引损坏

索引损坏是一个更严重的问题，索引的内部结构变得逻辑上不一致或物理损坏。这可能由多种因素引起，包括：

  • 硬件故障：磁盘错误、内存问题或断电。
  • 软件错误：罕见但可能存在的 PostgreSQL 本身或底层操作系统组件的缺陷。
  • 系统突然崩溃：PostgreSQL 服务器在没有正确关闭程序的情况下突然终止。

损坏的索引可能导致不正确的查询结果，出现“索引包含意外数据”等错误，甚至阻止查询完成。识别和修复损坏对于数据完整性和数据库稳定性至关重要。

索引问题的症状通常包括特定查询突然变慢、无故增加 I/O 活动，或与索引扫描相关的错误消息。

识别有问题的索引

在修复索引之前，您需要识别哪些索引正在引起麻烦。PostgreSQL 提供了几种方法来完成此操作。

检查未使用或低效的索引

pg_stat_user_indexes 视图提供了关于索引使用情况的统计信息。您可以查询它来查找很少或从未使用过的索引，这些索引可能是删除或重新评估的候选对象。

SELECT
    relname AS table_name,
    indexrelname AS index_name,
    idx_scan AS index_scans,
    idx_tup_read AS tuples_read,
    idx_tup_fetch AS tuples_fetched
FROM
    pg_stat_user_indexes
WHERE
    idx_scan = 0 -- 从未被扫描过的索引
    AND schemaname = 'public'
ORDER BY
    pg_relation_size(indexrelid) DESC;

尽管 idx_scan 为 0 可能表示未使用索引，但需要注意的是，有些索引用于约束（例如 UNIQUE、PRIMARY KEY）或不常访问的报告。在删除之前务必进行调查。

检测索引膨胀

膨胀更难直接检测，但与表相比索引大小过大，或索引在数据没有相应增长的情况下过度增长，都可能表明存在膨胀。您可以比较表及其索引的大小：

SELECT
    relname AS table_name,
    pg_size_pretty(pg_relation_size(relid)) AS table_size,
    pg_size_pretty(pg_indexes_size(relid)) AS indexes_size,
    pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM
    pg_stat_user_tables
ORDER BY
    pg_total_relation_size(relid) DESC;

对于更高级的膨胀检测，您可以考虑使用社区贡献的脚本或扩展，如 pg_repack 或 pgstattuple（它们可以通过查看元组密度来估算膨胀）。

使用 EXPLAIN ANALYZE 识别慢查询

当特定查询变慢时，EXPLAIN ANALYZE 是您最好的朋友。它显示查询执行计划和实际运行时统计信息，包括如何使用（或不使用）索引。

EXPLAIN ANALYZE
SELECT * FROM your_table WHERE your_column = 'some_value';

如果计划显示预期应进行索引扫描的地方进行了顺序扫描，或者索引扫描花费了异常长的时间，这可能表明索引效率低下或存在问题。

检查索引损坏

索引损坏通常表现为 PostgreSQL 日志中的错误或查询意外失败。查找包含 corruption、unexpected data 或 bad block 等短语的消息。不幸的是，没有直接的 SQL 命令可以在不尝试使用索引的情况下“检查损坏”。确认损坏的最佳方式是当查询失败并明确引用某个索引时。

提示： 定期监控您的 PostgreSQL 日志以获取错误消息。早期发现损坏可以防止更大的问题。

REINDEX 命令：您的主要工具

REINDEX 命令是重建 PostgreSQL 索引的主要工具。它从头开始重建索引，通过删除死元组有效地修复膨胀，并通过基于表当前数据构建一个新的有效结构来修复损坏。

REINDEX 的工作原理

当执行 REINDEX（不带 CONCURRENTLY）时，它本质上会删除现有索引，然后使用当前表数据重新创建它。此过程会创建一个新的、紧凑且有效的索引结构。然后删除原始索引。

REINDEX 语法和用法

REINDEX 可以应用于不同的粒度：

  1. 重建特定索引：
    sql REINDEX INDEX index_name;
    这是最常见的用例，针对单个有问题索引。

  2. 重建表上的所有索引：
    sql REINDEX TABLE table_name;
    当一个表有多个膨胀或损坏的索引时很有用。

  3. 重建数据库中的所有索引：
    sql REINDEX DATABASE database_name;
    这是一种更彻底的措施，通常用于怀疑存在广泛损坏或膨胀的情况。它可能导致显著的停机时间。

  4. 重建数据库中的系统目录：
    sql REINDEX SYSTEM database_name;
    这会重建指定数据库中系统目录表上的所有索引。应极其谨慎使用，并且仅在您怀疑系统目录索引存在问题时使用，因为它会影响整个数据库的功能并需要独占访问。

警告： 运行 REINDEX（不带 CONCURRENTLY）会获取正在重建的索引或表的 ACCESS EXCLUSIVE 锁。这意味着在重建索引过程中，受影响的对象上不能发生任何读取或写入操作，从而导致停机。对于一个表，所有关联的索引将被锁定。对于一个数据库，所有表及其索引将被锁定。

使用 REINDEX CONCURRENTLY 最小化停机时间

对于停机时间不可接受的生产系统，REINDEX CONCURRENTLY 是一个非常有价值的选项。它允许在不阻塞表上的并发读写操作的情况下重建索引。

REINDEX CONCURRENTLY 的工作原理：

  1. 它与正常操作并发地构建新的索引定义。
  2. 它在表上获取一个短暂的 SHARE UPDATE EXCLUSIVE 锁，该锁会阻塞 DDL（如 ALTER TABLE），但允许 DML（INSERT、UPDATE、DELETE）和 SELECT 语句。
  3. 然后它扫描表以构建新索引。
  4. 在初始构建之后，它会获取另一个非常短的 SHARE UPDATE EXCLUSIVE 锁，以应用在构建过程中发生的更改。
  5. 最后，它用新索引替换旧索引并删除旧索引。

语法：

REINDEX INDEX CONCURRENTLY index_name;

REINDEX CONCURRENTLY 的重要注意事项：

  • 执行速度较慢：因为它需要处理并发更改，所以 REINDEX CONCURRENTLY 通常比非并发的 REINDEX 慢。
  • 磁盘空间：它暂时需要旧的和新索引结构两者的磁盘空间。
  • 不支持事务：REINDEX CONCURRENTLY 不能在事务块内执行。
  • 错误处理：如果 REINDEX CONCURRENTLY 失败（例如，由于唯一索引上的唯一约束冲突），它会留下一个无效索引。您必须 DROP 这个无效索引，然后重新运行 REINDEX CONCURRENTLY 命令。

重建索引的实际示例

假设我们有一个 products 表，其中包含一个索引 idx_products_name。

重建单个索引（有停机时间）

如果您的受影响索引可以承受短暂中断：

REINDEX INDEX idx_products_name;

重建单个索引（并发，最小停机时间）

对于需要保持 products 表可访问的生产系统：

-- 对于 B-树索引：
REINDEX INDEX CONCURRENTLY idx_products_name;

-- 对于主键或唯一约束索引（通常需要特殊处理，尽管 REINDEX CONCURRENTLY 可以处理）：
-- 如果您需要重建主键或唯一约束索引，您通常重建底层索引。
-- 例如，如果 'products_pkey' 是主键索引：
REINDEX INDEX CONCURRENTLY products_pkey;

重建表上的所有索引

如果您怀疑 products 表上的多个索引有问题：

-- 这将获取 'products' 表的 ACCESS EXCLUSIVE 锁。
REINDEX TABLE products;

注意：没有 REINDEX TABLE CONCURRENTLY 命令。如果您需要并发地重建表上的所有索引，您必须使用 REINDEX INDEX CONCURRENTLY 单独重建每个索引。

首先，识别表的所有索引：

SELECT indexname FROM pg_indexes WHERE tablename = 'products';

然后，对于每个索引：

REINDEX INDEX CONCURRENTLY index_name_1;
REINDEX INDEX CONCURRENTLY index_name_2;
-- 等等。

重建数据库中的所有索引

这是最后的手段，需要显著的停机时间。它应该仅在计划的维护窗口期间执行。

REINDEX DATABASE your_database_name;

或者，您可以通过迭代数据库中所有索引（不包括系统索引）并并发地重建它们，尽管这会慢得多且需要仔细编写脚本。

相关的维护命令和最佳实践

重建索引通常是更广泛维护策略的一部分。其他命令在预防索引问题方面起着至关重要的作用。

VACUUM 和 VACUUM FULL

  • VACUUM：回收死元组占用的空间，使其可供重用。它不会缩小磁盘上的表或索引文件，但对于防止膨胀至关重要。autovacuum 守护进程通常会自动处理。
    sql VACUUM your_table;
  • VACUUM FULL：将整个表及其关联索引重写到一个新的磁盘文件中，回收最大空间并消除膨胀。但是，它会获取表的 ACCESS EXCLUSIVE 锁，阻塞所有操作，应极其谨慎使用。REINDEX 通常更适合索引膨胀。
    sql VACUUM FULL your_table;

ANALYZE

ANALYZE 命令收集数据库中表内容统计信息并将其存储在 pg_statistic 中。PostgreSQL 查询优化器使用这些统计信息来做出关于如何执行查询的智能决策，包括是否使用索引。在数据发生重大更改（或重建索引后）运行 ANALYZE 可确保优化器拥有最新信息。

ANALYZE your_table;
-- 或分析整个数据库：
ANALYZE;

监控自动清理（Auto-Vacuum）

确保 autovacuum 守护进程正在运行并配置正确。它负责自动执行 VACUUM 和 ANALYZE 操作，这对于防止膨胀和保持统计信息最新至关重要。配置错误的 autovacuum 是性能下降的常见原因。

定期维护计划

主动的索引维护优于被动的故障排除。制定以下计划：

  • 监控索引使用情况和大小：识别潜在的膨胀或未使用索引。
  • 运行 REINDEX CONCURRENTLY：对于频繁更新或删除的表，或在重大数据迁移之后。
  • 审查 autovacuum 日志和设置：确保它跟上数据库活动。

测试和备份

  • 始终测试：在对生产数据库执行任何重大维护操作之前，务必在镜像生产设置的预演或开发环境中进行彻底测试。
  • 始终备份：在启动任何 REINDEX 操作之前，特别是那些非并发的或针对整个表/数据库的操作，请确保您的数据库有最近且可靠的备份。虽然 REINDEX 通常是安全的，但损坏的数据库备份是无用的。

故障排除提示和警告

  • 磁盘空间：REINDEX 操作（尤其是 CONCURRENTLY）需要大量的临时磁盘空间——可能高达重建索引大小的两倍。确保您的数据库服务器有充足的可用空间。
  • 性能影响：即使是 REINDEX CONCURRENTLY 在其运行期间也会消耗 CPU 和 I/O 资源。在它运行时仔细监控您的系统性能。
  • 识别根本原因：不要在不了解索引为何膨胀或损坏的情况下反复重建索引。调查底层问题，如低效的 VACUUM 设置、高事务率或硬件问题。
  • 索引创建与重建索引：CREATE INDEX CONCURRENTLY 相当于 REINDEX INDEX CONCURRENTLY，用于在不阻塞的情况下创建新索引。它遵循类似的原则并具有类似的限制。

结论

维护健康高效的 PostgreSQL 索引是确保最佳查询性能和数据库整体稳定性的基础。通过理解索引膨胀和损坏的原因，学习识别有问题的索引，并掌握 REINDEX 命令——尤其是其 CONCURRENTLY 选项——您将掌握 PostgreSQL 管理的基本技能。

请记住，要主动进行索引维护：监控您的索引，安排定期检查，并明智地使用 REINDEX CONCURRENTLY 和其他维护工具。始终在非生产环境中测试程序，并确保您有可靠的备份。通过这些实践，您可以使您的 PostgreSQL 索引保持精简、快速和健壮，确保您的应用程序平稳高效地运行。