精通 DevOps 工具与最佳实践 - DevOps 知识中心 精通 DevOps 工具与最佳实践 - DevOps 知识中心

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

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

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

索引通常是PostgreSQL能在毫秒内响应查询而非扫描整个表的原因。它们也很容易被遗忘，直到某个索引变得膨胀、无效或疑似损坏。起初，症状看起来像普通的性能问题：某个查询变慢，磁盘读取增加，一个原本安静的表变得昂贵，或者查询计划变得不合理。

重建索引并不困难。知道何时重建才是更难的部分。膨胀的索引可以通过REINDEX修复，但根本原因可能是自动清理设置薄弱，或者工作负载整天更新同一行。损坏的索引可能需要紧急修复，但你也应该问为什么会出现损坏：存储、内存、内核错误、不安全的硬件设置或罕见的软件错误。

本指南专注于实用的PostgreSQL命令：如何发现可疑索引，如何在有或没有停机时间的情况下重建它们，以及在生产数据库上运行维护之前需要检查什么。

理解PostgreSQL索引及其常见问题

PostgreSQL索引，最常见的是B-tree索引，是帮助规划器避免扫描每一行的查找结构。当索引健康且有选择性时，PostgreSQL可以跳转到表所需的小部分。当索引膨胀或无效时，规划器可能仍会使用它，但数据库需要做额外的工作才能得到相同的结果。

索引可能主要因两个原因出现问题：膨胀和损坏。

索引膨胀

索引膨胀指的是索引结构中“死元组”（过时的数据版本）的积累。在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、invalid page或bad block等短语的消息。没有单个内置SQL命令可以证明数据库中的每个索引都是健康的。对于更深入的检查，团队通常在维护窗口期间使用PostgreSQL的amcheck扩展，特别是针对B-tree索引的bt_index_check和bt_index_parent_check。

CREATE EXTENSION IF NOT EXISTS amcheck;

SELECT bt_index_check('public.idx_products_name'::regclass);

amcheck是一个诊断工具，而不是修复工具。如果它报告问题，如果你还没有最近的备份，请进行备份，检查PostgreSQL和系统日志，并计划重建。

提示： 定期监控PostgreSQL日志中的错误消息。早期检测损坏可以防止更大的问题。

REINDEX命令：你的主要工具

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

REINDEX的工作原理

当执行REINDEX时，PostgreSQL从当前表数据重建索引。结果是一个新的、紧凑的索引结构。对于膨胀，这意味着索引内部的死空间被移除。对于许多索引级别的损坏情况，它为PostgreSQL提供了一个从表构建的新鲜结构。

REINDEX语法和用法

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

  1. 重建特定索引：

    REINDEX INDEX index_name;

    这是最常见的用例，针对单个有问题的索引。

  2. 重建表上的所有索引：

    REINDEX TABLE table_name;

    当一个表有多个膨胀或损坏的索引时很有用。

  3. 重建数据库中的所有索引：

    REINDEX DATABASE database_name;

    这是一个更激烈的措施，通常用于怀疑广泛损坏或膨胀的情况。它可能导致显著的停机时间。

  4. 重建数据库中的系统目录：

    REINDEX SYSTEM database_name;

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

警告： 在没有CONCURRENTLY的情况下运行REINDEX会占用更强的锁，并可能阻塞受影响对象上的正常应用程序流量。除非你已针对PostgreSQL版本和对象类型测试了确切的命令和锁行为，否则将其视为停机操作。

使用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-tree索引：
REINDEX INDEX CONCURRENTLY idx_products_name;

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

重建表上的所有索引

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

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

现代PostgreSQL版本支持并发表重建：

REINDEX TABLE CONCURRENTLY products;

这通常比手动重建每个索引更容易，但它仍然消耗I/O、CPU和临时磁盘空间。在不支持此语法的旧PostgreSQL版本上，识别表的索引并使用REINDEX INDEX CONCURRENTLY重建每个索引。

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

SELECT indexname FROM pg_indexes WHERE tablename = 'products';

为了手动控制，先列出索引：

SELECT indexname
FROM pg_indexes
WHERE schemaname = 'public'
  AND tablename = 'products'
ORDER BY indexname;

重建数据库中的所有索引

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

REINDEX DATABASE your_database_name;

或者，在支持的PostgreSQL版本上，你可以使用REINDEX DATABASE CONCURRENTLY your_database_name;。它避免了最糟糕的阻塞行为，但它仍然是一个主要的维护操作，并且不能在事务块内运行。

相关维护命令和最佳实践

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

VACUUM和VACUUM FULL

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

ANALYZE

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

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

监控自动清理

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

定期维护计划

主动的索引维护优于被动的故障排除。建立一个计划，包括：

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

测试和备份

  • 始终测试：在生产数据库上执行任何主要维护操作之前，在镜像生产设置的暂存或开发环境中彻底测试它们。
  • 始终备份：在启动任何REINDEX操作之前，尤其是非并发操作或针对整个表/数据库的操作，拥有一个最近、可靠的数据库备份。虽然REINDEX通常是安全的，但损坏的数据库备份是无用的。

故障排除提示和警告

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

良好的索引维护部分在于命令知识，部分在于克制。REINDEX CONCURRENTLY是一个有用的修复工具，但在不了解工作负载的情况下重复重建索引通常意味着同样的膨胀会再次出现。使用上述命令确认问题，重建你能重建的最小受影响对象，然后检查自动清理、更新模式、磁盘健康和查询计划，这样你下个月就不必再做同样的紧急修复。