时间戳

首页 > 解决方案 > 一个表可以拥有的最大有用索引数？

sql - 一个表可以拥有的最大有用索引数？

问题描述

会议

在上周的一次会议上，客户正在讨论如何更快地制作重要的搜索页面。该页面通过询问任何字段的值（字符串）来搜索单个表（12 列，2000 万行）；它返回 50 行（分页），从指定的条件开始（每列可以升序或降序）。当条件与现有索引不匹配时，搜索会变慢，客户端会不高兴。

然后——在会议进行到一半——半技术分析师把这个抛到了空中：为什么我们不在桌子上创建所有可能的索引来让一切都快呢？

我立即回答“不，太多了，修改表会很慢，所以我们需要创建一些巧妙选择的索引来做到这一点”。我们最终创建了最有用的页面，页面现在更快了。问题解决了。

问题

但仍然......我一直在思考这个问题，我想更好地理解它，所以这里是：

理论上，我可以在具有 N 列的表上创建多少个可能的有用索引？

我认为通过有用我们应该考虑（我可能是错的）：

因此，具有单列 (a) 的表只能有一个索引：

(a)

使用两列 (a, b) 我可以有四个有用的索引：

(a, b)
(b, a)
(a DESC, b)
(b DESC, a)
(a) -- already covered by #1
(b) -- already covered by #2
(a, b DESC) -- already coverred by #1 (reading index in reverse)
(b, a DESC) -- already covered by #2
(a DESC, b DESC) -- already covered by #3
(b DESC, a DESC) -- already covered by #4
(a DESC) -- already covered by #3
(b DESC) -- already covered by #4

三列（a，b，c）：

(a, b, c)
(a, c, b)
(b, c, a)
(b, a, c)
(c, a, b)
(c, b, a)
...

标签： sqlpostgresqlquery-optimization

解决方案

所有其他答案都包含一些有价值的东西，但是我不得不说的足够多，以保证第三个答案。

像你说的那样，这个问题没有确切的答案。在某种程度上，这就像问“你认为一个人疯了的极限是多少？” 有很大的灰色地带。

我的观点是：

  • 如果添加太多索引会发生什么：

    • 修改表变得非常慢。即使索引很少，数据操作也会变得慢一个数量级。如果您想要INSERT，UPDATE或者DELETE，具有所有可以想象的索引的表会使这样的操作非常缓慢。

    • 对于许多索引，查询规划器必须考虑许多不同的访问路径，因此添加任何索引都会使查询规划变得稍微慢一些。对于非常多的索引，甚至在执行程序开始工作之前，计划开销很可能会使查询变得太慢。

  • 您可以做些什么来减少所需的索引数量：

    • 看看运营商。如果从不使用运算符<、和<=，则添加具有降序列的索引是没有意义的。>=>

    • 请记住，索引 on(a, b, c)也可用于仅a在其条件中使用的查询，因此您不需要额外的索引 on (a)。

  • 对您来说，可行的前进方向是什么？

    我有两个建议：

    1. 一种方法是在十二列中的每一列上添加一个简单的索引。

      十二个指数已经不少了，但你还没有在疯狂的范围内。

      PostgreSQL 可以在具有多个列的条件的查询中有效地使用这些索引，即使单独的条件都没有足够的选择性来保证索引扫描。

      这是因为 PostgreSQL 有位图索引扫描。请参阅文档中的此示例：

      EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 100 AND unique2 > 9000;

                                     QUERY PLAN
-------------------------------------------------------------------------------------
 Bitmap Heap Scan on tenk1  (cost=25.08..60.21 rows=10 width=244)
   Recheck Cond: ((unique1 < 100) AND (unique2 > 9000))
   ->  BitmapAnd  (cost=25.08..25.08 rows=10 width=0)
         ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..5.04 rows=101 width=0)
               Index Cond: (unique1 < 100)
         ->  Bitmap Index Scan on tenk1_unique2  (cost=0.00..19.78 rows=999 width=0)
               Index Cond: (unique2 > 9000)

      扫描每个索引并形成一个位图，其中包含匹配条件的每一行的 1。然后组合位图，最后从表中取出行。

    2. 另一个想法是使用布隆过滤器。

      如果您的条件中唯一的运算符是=，您可以

      CREATE EXTENSION bloom;

      并在所有表列上创建一个索引USING bloom。

      这样的索引可用于在子句中具有任意列组合的查询。WHERE不利的一面是它是一个有损索引，因此您会得到必须提取和过滤掉的误报结果。

      这取决于您的情况，但这可能是一个优雅的（并且被低估了！）解决方案，可以平衡查询和更新速度。

推荐阅读