不要创建无用的索引
摘要： 本文讨论了创建过多数据库索引这一常见的性能陷阱，解释为何过多的索引会损害写入性能，并提供了实用的脚本来识别和删除 PostgreSQL 中未使用或重复的索引。
2023 年 1 月 2 日 · 802 字 · 4 分钟阅读
什么是索引？
我不知道你是否和我一样热爱烹饪，但我最有用的烹饪书在开头有目录，在最后有 2 个有用的索引。当我知道自己想做什么菜且有时间在书页间浏览时，我会使用目录轻松找到相应的章节，但如果我想节省时间，我通常会查阅索引。第一个索引是按菜谱名称排列的。菜谱按字母顺序排列，这样很容易找到所需的菜谱。第二个是我喜欢的：按配料排列的索引！对于每种配料，你可以找到使用它的菜谱列表和对应的页码。
数据库中的索引不过就是这样的东西。它是用来在有特定过滤条件时加速某些查询的。
索引过多有什么问题？
首先，让我给你介绍一些年前在某博客上发现的最糟糕的建议之一：一位开发者分享了他使用 Django ORM（或任何 ORM）的最佳建议。他的其中一个技巧是将所有可能用于过滤的列都建立索引！Django ORM 甚至有一个选项可以自动创建所有索引（db_index=True）。
这就是为什么当我被请去解决性能问题（或进行审计）时，我首先会查看数据大小与索引大小的对比。如果你在一个事务型工作负载中存储的索引比数据还多，那就糟糕了。我见过的最离谱的情况是，一个数据库存储在磁盘上的索引是数据的 12 倍！当然，那是一个事务型工作负载……你会去买一本有 10 页菜谱却附有 120 页索引的烹饪书吗？
索引的问题在于，每次你进行写操作（插入、更新、删除）时，同时也必须写入索引！这会消耗大量的资源和时间。
你能做什么？
首先，确保你没有创建重复的索引。出于内部目的，PostgreSQL 会为每个唯一约束创建一个索引（这也包括主键）。PostgreSQL 不会阻止你在同一列上创建 3 或 4 个索引。要检查是否有重复索引，你可以使用 PGExperts 的这个脚本。
我很喜欢 PGExperts 的脚本。每次我想到要为 Postgres 写一个有用的脚本时，我都会去看看，而且不得不承认十之八九我都不需要再写脚本了！
PostgreSQL 也会跟踪索引的使用情况以及列的使用情况（写、读），所以这个脚本会对你现有的索引给出建议：它们是否未使用？它们是否很少被使用但那些列却在高写入负载下？他们是否很大却没那么有用？
关于索引的最后一个有用的脚本是这两个：需要的索引和外键无索引。第一个会查看列使用情况来建议创建索引（仅适用于单列索引），而第二个会列出所有没有索引的外键。由于外键在连接中频繁使用，它们是非常好的索引候选。PostgreSQL 不会自动为外键创建索引。
索引并不糟糕！
索引本身不糟糕，但如果使用不当，确实会严重损害你的工作负载。制定一个好的索引策略是日常性的工作，因为应用程序查询、数据和数据模型都会发生变化。另外，请记住 PostgreSQL 默认创建 B 树索引，但实际上 PostgreSQL 中的索引类型远不止单列 B 树索引。阅读 PostgreSQL 文档的第 11 章可以了解更多关于 PostgreSQL 中索引的信息！
如果你想知道为什么这个愚蠢的规划器拒绝使用你的索引，你可能会对 Markus Winand 的这本书感兴趣：《Use the index, Luke》！另外，如果你想测试索引对特定查询计划的影响，你应该看看 hypopg 扩展，它会创建"假想索引"。
但是，正如移除代码的开发者比添加代码的更有价值一样，能够删除索引的 DBA 比添加索引的更有价值。
标签：PostgreSQL 管理 PGSQL Friday
