PostgreSQL RDS索引陷阱 - nanit


提高 RDBMS 查询性能的最常见方法之一是根据查询的选定字段和条件添加索引。但有时索引可能会降低数据库性能而不是提高它。在这篇博文中，我将描述删除索引如何帮助我们提高性能并减轻 RDS 实例的负载。
几周前，我们收到了一条 CloudWatch 警报，指出我们的一个 RDS 实例的突发余额不足。在我们深入细节之前，让我先解释一下这个RDS实例的用法以及什么是突发平衡。
RDS 实例正由名为 Inbox 的服务使用。Inbox 是一项 HTTP 服务，为 Nanit 的客户提供运动检测和高温等事件通知。Inbox 使用 RDS 进行存储，实例使用通用 (gp2) SSD 卷。
为了解释突发平衡，让我们首先深入了解 RDS 卷性能。
IOPS、IO 积分和突发余额

  1. RDS 卷性能通过每秒 IO 操作数 (IOPS) 来衡量：基准性能确定为以 GiB x 3 为单位的卷大小，例如，500 GiB 卷的基准性能为 1500 IOPS。当基准性能不足时，卷可能会消耗 IO 积分。只要卷性能低于基线，积分就会以每 GiB 3 IOPS 的速率累积。这意味着 500 GiB 的卷以 1500 IOPS 的速率累积积分。
  2. 需要注意的是，低于 1 TiB 的卷在 30 分钟内使用积分的限制为 3000 IOPS。
  3. 突发余额是可用积分的百分比。

 。。。
 

解释分析
为了找出查询执行缓慢的原因，我们使用了EXPLAIN ANALYZE命令。EXPLAIN命令显示查询的执行计划而不实际运行它。
使用 ANALYZE 选项执行查询，结果包括实际运行时间。
在慢查询上运行EXPLAIN ANALYZE的结果。我们发现索引扫描需要 10 秒（准确地说是 10444.278），它返回 9019 行，然后由数据库在内存中排序。如果我们只需要一行，从索引扫描返回 9019 行意味着大量冗余读取 IOPS，我们发现这可能是我们在看到的 IOPS 激增的原因。
 
索引使用
接下来是了解查询计划器为何使用此索引以及我们如何减少查询时间。
下一件事是了解为什么查询计划器使用这个索引，以及我们如何能减少查询时间。我们对索引的结构很感兴趣，看看它是否符合查询的结构（选定的列、where子句、限制），所以我们在psql客户端运行了\d命令，该命令返回一个表的所有索引及其结构。现在我们有了索引的结构，但令人惊讶的是，列表中包括另一个索引，其结构包括查询中使用的确切字段和标准。我们的问题是为什么查询计划器没有使用正确的索引？我们接下来做的事情是通过使用下面的查询来看看是否使用了适当的索引。

SELECT
    pg_t.tablename,
    pg_size_pretty(pg_relation_size(pg_c.oid)) AS table_size,
    pg_sai.indexrelname AS index_name,
    pg_sai.idx_scan AS number_of_scans,
    pg_sai.idx_tup_read AS tuples_read,
    pg_sai.idx_tup_fetch AS tuples_fetched
FROM
    pg_tables pg_t
    LEFT JOIN pg_class pg_c ON pg_t.tablename = pg_c.relname
    LEFT JOIN pg_index pg_i ON pg_c.oid = pg_i.indrelid
    LEFT JOIN pg_stat_all_indexes pg_sai ON pg_i.indexrelid = pg_sai.indexrelid
WHERE
    pg_t.schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY 
    pg_t.tablename, 
    table_size;

该查询输出在一个索引上启动的扫描次数。通过多次运行这个查询，我们可以通过比较两次执行之间的number_of_scans来确定哪个索引正在被使用，并看到它在增加。
我们注意到，合适的索引根本没有被使用，也就是说，扫描次数保持不变，而不太合适的索引的扫描次数在我们每次执行查询时都会增加。令我们惊讶的是，不太优化的索引的扫描次数比适当索引的扫描次数要低得多。这个数据让我们意识到，索引的使用从合适的索引转移到了不太优化的索引，从而增加了读取IOPS。
 
索引的删除
接下来我们做的事情是使用DROP INDEX CONCURRENTLY查询来删除这个不太优化的索引。我们使用CONCURRENTLY选项来避免锁定表。一旦索引被删除，查询计划器就开始使用适当的索引。
，现在的总执行时间是1ms。此外，索引的使用也从索引扫描变成了只扫描索引。索引扫描从索引和表的堆中获取行，而仅索引扫描则直接从索引的数据中返回行，而不访问表的堆。
但最重要的是，只用索引扫描只返回1条记录，而使用以前的索引则返回9019条记录。
现在，查询计划员开始使用适当的索引，读取IOPS下降，收件箱完全恢复。
 
总结
一段索引误区让我们开始了一段旅程，从前端应用程序开始，访问了后端代码，最后在数据库本身结束。我们从中学到了很多东西，特别是在调试计划器查询执行输出和索引使用方面。