主页 / dba / 问题 / 33196
Accepted
jap1968
Asked: 2013-01-20 05:36:43 +0800 CST

多列索引和性能

  • 772

我有一个带有多列索引的表，我怀疑索引的正确排序以获得查询的最大性能。

场景：

  • PostgreSQL 8.4，大约有一百万行的表

  • c1列中的值可以有大约100 个不同的值。我们可以假设这些值是均匀分布的，因此每个可能的值都有大约 10000 行。

  • c2列可以有1000 个不同的值。每个可能的值都有 1000 行。

搜索数据时，条件始终包含这两列的值，因此该表具有结合 c1 和 c2 的多列索引。如果您的查询仅使用一列进行过滤，我已经阅读了正确排序多列索引中的列的重要性。在我们的场景中情况并非如此。

我的问题是这个：

鉴于其中一个过滤器选择的数据集要小得多，如果第一个索引是最具选择性的索引（允许较小集合的索引），我可以提高性能吗？在看到引用文章中的图形之前，我从未考虑过这个问题：

在此处输入图像描述

图片取自有关多列索引的参考文章。

查询使用来自两列的值进行过滤。我没有查询，只使用一列进行过滤。他们都是：WHERE c1=@ParameterA AND c2=@ParameterB。还有这样的条件：WHERE c1 = "abc" AND c2 LIKE "ab%"

postgresql index

2 个回答

  1. Best Answer

    回答

    由于您参考了网站use-the-index-luke.com，请考虑以下章节：

    使用索引，Luke › Where 子句 › 搜索范围 › Greater、Less 和 BETWEEN

    它有一个与您的情况完美匹配的示例（两列索引，一个用于相等性测试，另一个用于range测试），解释了（带有更多漂亮的索引图形）为什么@ypercube 的建议是准确的并总结了：

    经验法则：首先是相等的索引 - 然后是范围。

    也适用于一列？

    仅针对一列的查询要做什么似乎很清楚。有关这些相关问题的更多详细信息和基准：

    首先选择较少的列？

    除此之外，如果两列只有相等条件怎么办？

    没关系。将更有可能收到自己的条件的列放在第一位，这实际上很重要。

    演示

    一个包含 100k 行的两列的简单表。一个很少，另一个有很多不同的值。2013 年使用 Postgres 9.2 进行的原始测试：

    CREATE TABLE tbl AS
SELECT (random() * 10000)::int AS lots
     , (random() * 4)::int     AS few
FROM generate_series (1, 100000);

DELETE FROM tbl WHERE random() > 0.9;  -- create some dead tuples, more "real-life"

VACUUM ANALYZE tbl;

SELECT count(distinct lots)   -- 9999
     , count(distinct few)    --    5
FROM   tbl;

    询问：

    SELECT *
FROM   tbl
WHERE  lots = 2345
AND    few = 2;

    EXPLAIN ANALYZE输出（最好的 10 以排除缓存效果）：

    tbl 上的 Seq 扫描（成本=0.00..5840.84 行=2 宽度=8）
                 （实际时间=5.646..15.535 行=2 循环=1）
  过滤器：((lots = 2345) AND (few = 2))
  缓冲区：本地命中=443
总运行时间：15.557 毫秒

    添加索引，重新测试：

    CREATE INDEX tbl_lf_idx ON tbl(lots, few);
    在 t 上使用 tbl_lf_idx 进行索引扫描（成本=0.00..3.76 行=2 宽度=8）
                                       （实际时间=0.008..0.011 行=2 循环=1）
  指数条件：((lots = 2345) AND (few = 2))
  缓冲区：本地命中=4
总运行时间：0.027 毫秒

    添加其他索引，重新测试：

    DROP INDEX tbl_lf_idx;
CREATE INDEX tbl_fl_idx ON tbl(few, lots);
    在 tbl 上使用 tbl_fl_idx 进行索引扫描（成本=0.00..3.74 行=2 宽度=8）
                                    （实际时间=0.007..0.011 行=2 循环=1）
  指数条件：((few = 2) AND (lots = 2345))
  缓冲区：本地命中=4
总运行时间：0.027 毫秒

    用 Postgres 13 重复 2021 年，同样的结论：

    db<>在这里摆弄

    • 43

  2. 如果，如您所说，涉及这 2 列的查询都是两列的相等检查，例如：

    WHERE c1=@ParameterA AND c2=@ParameterB

    不要为此烦恼。我怀疑会有任何区别，如果有区别，那将是微不足道的。当然，您始终可以使用您的数据和服务器设置进行测试。不同版本的 DBMS 在优化方面可能略有不同。

    索引内的顺序对于其他类型的查询很重要，仅检查一列，或不等式条件，或一列上的条件和另一列中的分组等。

    如果我要选择两个订单之一，我会选择将选择性较低的列放在第一位。考虑一个包含列year和的表month。您更有可能需要WHERE year = 2000条件或 aWHERE year BETWEEN 2000 AND 2013或 a WHERE (year, month) BETWEEN (1999, 6) AND (2000, 5)。

    WHERE month = 7 GROUP BY year可能需要确定类型的查询（查找出生于 7 月的人），但频率较低。这当然取决于存储在表中的实际数据。暂时选择一个订单，比如说(c1, c2)，您以后可以随时添加另一个索引(c2, c1)。

    在OP发表评论后更新：

    还有这样的条件：WHERE c1 = 'abc' AND c2 LIKE 'ab%'

    这种类型的查询如果恰好是列上的范围条件c2并且需要(c1, c2)索引。如果您还有反向类型的查询：

    WHERE c2 = 'abc' AND c1 LIKE 'ab%'

    那么如果你也有一个(c2, c1)索引就好了。

    • 11

相关问题