关于Postgres索引需要记住的三件事

2020-09-08 11:13:28

如果您是一名应用程序开发人员、分析师、数据科学家或任何必须弄清楚如何使用关系数据库的人,那么您很有可能熟悉索引。至少在一定程度上,您知道它们以某种方式帮助加快了您的查询速度。(这就是我在很长一段时间内对索引的理解之处)。您可能已经看到,PostgreSQL会自动为具有主键或唯一约束的列创建索引。但只要再深入一点,你就会很快意识到,这不仅仅是为了让搜索速度更快!

如果您正开始学习索引,这里有几件事有望帮助您完善理解。

数据库可以通过逐个扫描员工行来查找符合筛选条件(其中Department=#39;Accounting';)的记录。您可以想象一下,一个接一个地检查每一行可能会非常慢,尤其是对于较大的表。索引有助于加快搜索速度,因为它提供指向数据值满足筛选条件的表行的指针。

简单的解释是,如果连接条件中使用的列上有索引(SELECT*FROM EMPLOYES INNER JOIN DEPARTMENT ON Employees.Department=department.name),它可能会帮助数据库更快地找到匹配的行。这确实取决于数据库如何决定执行连接-这个关于SQL连接的博客系列更详细地介绍了连接算法。

有许多不同类型的数据库索引(请查看PostgreSQL索引类型的官方文档),但一般而言,它们以某种有序结构表示索引数据。如果数据库能够使用索引以特定顺序检索表行,这将有助于减少满足查询中的ORDER BY或GROUP BY子句所需的时间。

创建索引并不能保证查询确实会使用它--这可能会受到许多不同因素的影响。

首先,如果满足条件的行数足够大,查询计划可以跳过检查索引的中间步骤,直接读取表。此外,查询类型本身也很重要。使用通配符的查询,例如…。其中,像';ma%';这样的名称可以利用B树索引(";默认和最常见的Postgres索引类型),但是您可能需要指定一个操作符类才能使索引生效。

这篇关于为什么在desesz.com上不能使用索引的博客帖子发表了一段时间,但它仍然是一个相当有趣的探索和有趣的阅读。

索引是单独的数据结构,也存储在磁盘上,因此不幸的是,我们不能像数据表那样认为它们不占用空间。毕竟,它们就像一本书的索引:

这是我的一本旧SQL教科书,索引占据了430页中的16页。但我很高兴这本书把它包括在内!

如果索引列必须插入新值,或者更新或删除现有值,则相应的索引也会更新。具有讽刺意味的是,这可能会使查询需要更多时间进行评估。如果经常对特定列运行写操作,那么您可能需要更仔细地评估在此创建索引。

如果你从这篇博文中只学到了一件事,我希望它是这样的:索引的成功需要一些规划、调查和维护!

到目前为止,您已经意识到在任何可能的位置创建索引都是一个糟糕的策略(说真的,不要这么做)。您可能也不想把索引当作一件事来设置,然后就忘了它。因此,所有这些最终都会以成本的形式收回,无论是时间、资源,还是必须雇佣专业知识来帮助您优化数据库运行的方式。

有几个工具可以帮助您制定索引策略:解释(特别是解释分析)和监视索引统计信息。这些至少可以帮助您收集有关您的查询是否可以从索引中受益,或者您的索引是否确实有预期的帮助的信息。

数据库索引(以及一般的调优和优化!)。是一个相当沉重的话题,但在Crunchy Data,我们汇集了一些资源,即使你是Postgres的新手,这些资源也会对你有所帮助:

接下来,请查看PostgreSQL中的索引类型,以了解有关B树之外的索引类型的更多信息。

如果您正在寻找更高级的读物,我推荐以下来自我们的Crunchy博客的帖子: