首页 文章

SQL性能，在SELECT和WHERE条件下执行得更快

提问于
浏览 次
2

更新：废话！它不是一个整数，它的性格各不相同（10）

像这样执行查询使用索引

SELECT t."FieldID"
FROM table t
WHERE t."FieldID" = '0123456789'

但如果我执行此操作，则不使用索引

SELECT t."FieldID"
FROM table t
WHERE t."FieldID" LIKE '01%'

或这个

SELECT t."FieldID"
FROM table t
WHERE "substring"(t."FieldID", 0, 3) = '01'

这也是

SELECT t."FieldID"
FROM table t
WHERE t."FieldID" ~ '^01'

我的索引看起来像这样

CREATE UNIQUE INDEX fieldid_index
  ON "table"
  USING btree
  ("FieldID");

运行PostgreSQL 7.4（是的升级）

我正在优化我的查询，并想知道在语句的SELECT或WHERE子句中使用三种类型的表达式之一是否有任何性能提升 .

注意：使用这些约束样式执行的查询返回大约200,000条记录

示例数据是一个不同的字符（10）： 0123456789 ，它也被编入索引

1. (Substring)

SELECT CASE
    WHEN "substring"(t."FieldID"::text, 0, 3) = '01'::text         
    THEN 'Found Match'::text
    ELSE NULL::text
END AS matching_group

2. (Like)

SELECT CASE
    WHEN t."FieldID"::text LIKE '01%'         
    THEN 'Found Match'::text
    ELSE NULL::text
END AS matching_group

3. (RegEx)

SELECT CASE
    WHEN t."FieldID" ~ '^01'         
    THEN 'Found Match'::text
    ELSE NULL::text
END AS matching_group

在WHERE子句中使用一个优于另一个还有任何性能优势吗？

1. (Substring)

WHERE CASE
    WHEN "substring"(t."FieldID"::text, 0, 3) = '01'::text         
    THEN 1
    ELSE 0
END = 1

2. (Like)

WHERE CASE
    WHEN t."FieldID"::text LIKE '01%'         
    THEN 1
    ELSE 0
END = 1

3. (RegEx)

WHERE CASE
    WHEN t."FieldID" ~ '^01'         
    THEN 1
    ELSE 0
END = 1

在SELECT中使用一个选项和在WHERE子句中使用不同的选项可以提高性能吗？

sql postgresql query-optimization query-performance

4 回答

  • 3

    在SQL Server中，具有 LIKE '01%' 的版本将是可搜索的 . 它实际上将这些 LIKE 查询转换为范围查询而不引导通配符 .

    执行计划显示搜索谓词为 YourCol >= '01' AND YourCol < '02' 也许类似的重写可能有助于Postgresql？

    回复于
  • 1

    就个人而言，我认为不应该允许创建此类问题的人使用"performance"这个词 . 数字字段（甚至是关键字段）内容的 text representation 上的限制（如WHERE子句中的限制）表示设计不良，恕我直言 .

    如果这是我的数据，我会在记录中添加一个flagfield，表示在查询xyz中想要/不想要 . 甚至可以把它放在一个单独的表中 . 我更喜欢添加一个（冗余？）列来创建基于GW-basic-substring rubbish的整个索引 .

    回复于
  • 3

    影响最大的两件事是索引和sargability . 可伸缩性意味着使用可以利用索引的表达式 . 你可以用它来测量它们的效果

    ANALYZE your_first_table;
-- ANALYZE other tables used in this query.
EXPLAIN ANALYZE
SELECT ...

    请参阅Examining index usage的文档 .

    您可以利用indexes on expressions或partial indexes . PostgreSQL 7.4支持表达式和部分索引的索引 . 对于测试，您可以discourage certain kinds of query plans . （也是7.4 . ）

    基于表达式的索引可能适合您：

    create index firsttwochars
on your-table-name (substring(your-column-name from 1 for 2));

    但是您仍然需要测试查询以查看它们是否实际使用了索引 . （他们是否可以苛刻 . ）这个可能会奏效 .

    select your-column-name 
from your-table-name 
where substring(your-column-name from 1 for 2) = '01'

    查询计划没有前两个字符的索引 . （我的测试表使用随机文本用户名，这就是我搜索'ab'而不是'01'的原因 . ）

    Seq Scan on substring  (cost=0.00..205.00 rows=50 width=11) (actual time=0.315..4.377 rows=14 loops=1)
  Filter: (substring((username)::text, 1, 2) = 'ab'::text)
Total runtime: 4.414 ms

    查询计划，前两个字符的索引 .

    Bitmap Heap Scan on substring  (cost=4.36..37.61 rows=14 width=11) (actual time=0.036..0.056 rows=14 loops=1)
  Recheck Cond: (substring((username)::text, 1, 2) = 'ab'::text)
  ->  Bitmap Index Scan on firsttwochars  (cost=0.00..4.36 rows=14 width=0) (actual time=0.028..0.028 rows=14 loops=1)
        Index Cond: (substring((username)::text, 1, 2) = 'ab'::text)
Total runtime: 0.098 ms
    回复于
  • 1

    在选择列表中，三个表达式之间可能没有太大区别 . 这都是CPU时间 .

    对于 WHERE 子句，您可以添加表达式索引，例如

    CREATE INDEX foo ON sometable ((
CASE
    WHEN "substring"("FieldID"::text, 0, 3) = '01'::text         
    THEN 1
    ELSE 0
END
));

    但是这样的布尔索引的选择性可能已经足够糟糕，不会引起规划者的兴趣 . 将 WHERE 子句重写为just会更好

    WHERE "substring"("FieldID"::text, 0, 3) = '01'::text

    然后索引那个 .

    对于 LIKE 和正则表达式案例，您也可以考虑 text_pattern_ops 索引;见documentation .

    总而言之，我认为你有一些关于该查询的清理工作 .

    回复于

相关问题