主页 / dba / 问题 / 346046
Accepted
Alex
Asked: 2025-04-06 01:19:21 +0800 CST

使用复合键，我是否仍然需要为所有列分别建立索引？

  • 772

如果我有一个由 3 列组成的复合键，例如

constraint some_index unique (parent_store_id, store_type, store_name),

并且我还需要能够使用where store_type = ...或进行选择查询where store_name = ...，我是否仍然需要为每一列创建单独的索引？或者上面的唯一索引可以处理这些情况吗？

postgresql

2 个回答

  1. 如果您有一个复合唯一索引：

    UNIQUE (parent_store_id, store_type, store_name)

    仅当查询按照精确顺序从左到右过滤列时，此索引才最有效。

    因此，在这里：这些查询将受益于索引：

    WHERE parent_store_id = ...

WHERE parent_store_id = ... AND store_type = ...

WHERE parent_store_id = ... AND store_type = ... AND store_name = ...

    并且，这些查询不会有效地使用索引：

    WHERE store_type = ...

WHERE store_name = ...

WHERE store_type = ... AND store_name = ...

    对于此类查询，您应该根据用途在 store_type 或 store_name 上创建单独的索引。

    注意：添加索引会产生存储和更新开销。因此，请仅添加查询中实际用到的索引。

    • 4
  2. Best Answer

    默认索引类型是BTree ，对parent_store_ids 进行排序，每个 s 保存其已排序的store_types，每个 s 保存其已排序的store_names。基于所有列或前导列的搜索效果很好。其他组合，尤其是最右边的列，效果不佳，因为您需要沿着树向下搜索并从每个分支收集它们，它们彼此独立排序，并且仅在内部进行重复数据删除。

    根据您的架构、用例和数据量，与其尝试预测所有类型的查询，也不必为每种列组合在数据库中填充一个单独优化的 BTree，不如考虑使用布隆过滤器索引——这正是它的用途。文档中引用：

    当表包含许多属性，且查询会测试这些属性的任意组合时，这种类型的索引最为有用。传统的 B 树索引比 B 树索引速度更快，但它可能需要多个 B 树索引来支持所有可能的查询，而 B 树索引只需要一个 B 树索引。需要注意的是，B 树索引仅支持相等性查询，而 B 树索引还可以执行不等性查询和范围搜索。

    如果我在 100k 行的测试设置上创建索引：
    db<>fiddle 上的演示

    select setseed(.42);
create table t(parent_store_id, store_type, store_name)as
select (random()*1e5)::int
     , (random()*1e4)::int
     , md5((random()*9e4)::int::text)
from generate_series(1,1e5);

create unique index some_index on t(parent_store_id, store_type, store_name);
select pg_size_pretty(pg_relation_size('some_index'));
    pg_size_pretty
    6600 千字节

    我分别在第一列、第二列和第三列搜索时，分别耗时0.3ms、6ms和9ms 。如果我将其替换为布隆过滤器索引：

    create index bloomidx on t using bloom(parent_store_id, store_type, store_name);
select pg_size_pretty(pg_relation_size('bloomidx'));
    pg_size_pretty
    1584 千字节

    这三次搜索大约耗时0.9ms。具体细节如下：

    ERROR:  access method "bloom" does not support unique indexes

    这意味着您仍然需要保留 BTree 来保护唯一性，而 Bloom 只能替换附加索引。

    如果您确实只需要处理这三列，那么维护几个 BTree 应该不是什么大问题，但是您添加的列越多，看到的搜索过滤器的变化越多，您就越接近 bloom 的理想用例。

    上述唯一索引是否可以处理这些情况

    有可能，也有可能不。这取决于很多因素：

    1. 列的总数、索引中有多少列以及其中有多少列被 DML 查询选择或以其他方式使用。例如，最好使用覆盖索引。
    2. 表大小 - 无论索引设置如何，小表通常都会进行顺序扫描，因为对于规划器/优化器来说，读取整个表的开销已经足够低了。如果表很大，你会看到所有与查询稍微相关的索引都会被扫描，因为它们通常要小得多，所以读取和理解它们比读取表更快。
    3. vacuum上次编辑、analyzed、 ed的时间reindex。新鲜、紧凑、整洁的表格比过时、臃肿的索引更受欢迎，反之亦然。
    4. 是否cluster由该指数
    5. 选择性 - 如果您的where条件引用第一列，但在某种程度上它处理整个表，则使用索引是没有意义的，除非它是一个覆盖索引。

    这份清单并非详尽无遗。你可以尝试一下set enable_seqscan=off;，看看 Postgres 虽然可以通过索引获取你请求的值，但顺序扫描有时确实是更快的方法。

    我是否仍需要为每一列创建单独的索引

    insert仅当您能够承受//上增加的虽小但非零的延迟（所有这些都需要反映在每个索引中），加上容纳索引的空间，以及索引维护（索引维护update不会自行发生，它们会变得臃肿并过时）时才如此。此外，如果您无法承受尾随列查询的执行时间差异。deletereindex

    • 1

相关问题