主页 / dba / 问题 / 10694
Accepted
Lucas Kauffman
Asked: 2012-01-16 03:24:02 +0800 CST

在 PostgreSQL 中使用 LIKE、SIMILAR TO 或正则表达式进行模式匹配

  • 772

我必须编写一个简单的查询，在其中查找以 B 或 D 开头的人名：

SELECT s.name 
FROM spelers s 
WHERE s.name LIKE 'B%' OR s.name LIKE 'D%'
ORDER BY 1

我想知道是否有办法重写它以提高性能。所以我可以避免or和/或like？

postgresql index

8 个回答

  1. Best Answer

    模式匹配运算符

    • LIKE( ~~) 简单快速，但功能有限。
      ILIKE( ~~*) 不区分大小写的变体。

    • ~（正则表达式匹配）功能强大但更复杂，并且对于基本表达式以外的任何内容都可能很慢。
      ~*是不区分大小写的变体。

    • SIMILAR TO只是毫无意义。LIKE正则表达式的特殊混合。我从不使用它。见下文。

    以上所有都可以使用三元索引。见下文。
    对于左锚模式，还有一个 B-tree 索引，使用COLLATE "C"或 operator class text_pattern_ops。见下文。

    手册中有关模式匹配的基础知识。

    相关运算符

    • ^@is "starts with" 运算符（用于前缀匹配），相当于starts_with()函数。
      与 Postgres 11 一起添加，可以使用SP-GiST 索引。由于Postgres 15也是使用“C”排序规则的 B-tree 索引。见下文。

    • %是附加模块提供的“相似性”运算符pg_trgm。见下文。

    • @@是文本搜索运算符。见下文。

    您的查询

    ...几乎是最佳选择。语法不会变得更短，查询不会变得更快：

    SELECT name FROM spelers
WHERE  name LIKE 'B%' OR name LIKE 'D%'
ORDER  BY 1;

    或等价物（稍微贵一点）：

    ... WHERE name ~ '^B' OR name LIKE '^D'

    有点短，但不能使用索引：

    ... WHERE name LIKE ANY ('{B%,D%}')
... WHERE name ~ ANY ('{^B,^D}')

    带有分支的正则表达式进一步缩短了语法：

    ... WHERE name ~ '^(B|D).*'

    或字符类（仅适用于单个字符的情况）：

    ... WHERE name ~ '^[BD].*'

    对于更大的表，索引支持将性能提高了几个数量级。

    在 Postgres 11 或更高版本中，新^@的更方便，因为我们可以直接使用未修饰的前缀 - 并且在支持 SP-GiST 索引时快速：

    ... WHERE name ^@ 'B' OR name ^@ 'D'

    或者：

    ... WHERE name ^@ ANY ('{B,D}')

    在 Postgres 15 中，第一个变体也可以使用 B 树索引，使用COLLATE "C".

    db<>在这里摆弄

    指数

    如果关心性能，请为更大的表创建这样的索引以支持左锚定搜索模式（从字符串的开头匹配）：

    CREATE INDEX spelers_name_special_idx ON spelers (name COLLATE "C");

    需要在 Postgres 9.1 中添加每列排序规则支持。

    看：

    在使用“C”语言环境（非典型）运行的数据库中，普通的 B 树索引可以完成这项工作。

    在旧版本中（或今天仍然存在），您可以将特殊运算符类text_pattern_ops用于相同目的：

    CREATE INDEX spelers_name_special_idx ON spelers (name text_pattern_ops);

    SIMILAR TO或具有基本左锚表达式的正则表达式也可以使用此索引。但不能使用分支(B|D)或字符类[BD]。并且目前没有索引支持LIKE ANY.

    三字匹配

    Trigram 匹配或文本搜索使用特殊的 GIN 或 GiST 索引。

    从Postgres 9.1开始，您可以安装附加模块pg_trgm以使用 GIN 或 GiST 索引为任何LIKE/ILIKE模式（以及带有~/的简单正则表达式模式）提供索引支持。~*

    详细信息、示例和链接：

    pg_trgm提供额外的运算符，例如：

    • %- “相似性”运算符
    • <%(commutator: %>) - Postgres 9.6 或更高版本中的“word_similarity”运算符
    • <<%(commutator: %>>) - Postgres 11 或更高版本中的“strict_word_similarity”运算符

    文本搜索

    是一种特殊类型的模式匹配，具有单独的基础结构和索引类型。它使用字典和词干提取，是在文档中查找单词的好工具，尤其是对于自然语言。

    还支持前缀匹配：

    以及自 Postgres 9.6 以来的短语搜索：

    考虑手册中的介绍以及运算符和功能的概述。

    用于模糊字符串匹配的附加工具

    附加模块fuzzystrmatch提供了更多选项，但性能通常不如上述所有选项。

    特别地，该levenshtein()功能的各种实现可能是有帮助的。

    为什么正则表达式 ( ~) 总是比 快SIMILAR TO？

    SIMILAR TO表达式在内部被重写为正则表达式。对于每个SIMILAR TO表达式，至少有一个更快的正则表达式（节省重写表达式的开销）。SIMILAR TO 使用ever没有性能提升。

    无论如何，可以使用LIKE( ~~) 的简单表达式更快LIKE。

    SIMILAR TO仅在 PostgreSQL 中受支持，因为它最终出现在 SQL 标准的早期草案中。他们仍然没有摆脱它。但是有计划将其删除并包含正则表达式匹配 - 至少我听说过。

    EXPLAIN ANALYZE揭示它。自己试试任何桌子！

    EXPLAIN ANALYZE SELECT * FROM spelers WHERE name SIMILAR TO 'B%';

    揭示：

    ...  
Seq Scan on spelers  (cost= ...  
  Filter: (name ~ '^(?:B.*)$'::text)

    SIMILAR TO已用正则表达式 ( ~) 重写。

    • 213

  2. 如何在表中添加一列。根据您的实际要求：

    person_name_start_with_B_or_D (Boolean)

person_name_start_with_char CHAR(1)

person_name_start_with VARCHAR(30)

    PostgreSQL 不支持像 SQL Server 一样的基表中的计算列，但可以通过触发器维护新列。显然，这个新列将被索引。

    或者，表达式上的索引会给你同样的，更便宜的。例如：

    CREATE INDEX spelers_name_initial_idx ON spelers (left(name, 1));

    与条件中的表达式匹配的查询可以使用此索引。

    这样，在创建或修改数据时会影响性能，因此可能只适用于低活动环境（即写入比读取少得多）。

    • 13

  3. 你可以试试 

    SELECT s.name
FROM   spelers s
WHERE  s.name SIMILAR TO '(B|D)%' 
ORDER  BY s.name

    不过，我不知道上述内容或您的原始表达在 Postgres 中是否可以使用。

    如果您创建建议的索引，也会有兴趣了解它与其他选项的比较。

    SELECT name
FROM   spelers
WHERE  name >= 'B' AND name < 'C'
UNION ALL
SELECT name
FROM   spelers
WHERE  name >= 'D' AND name < 'E'
ORDER  BY name
    • 8

  4. 我过去所做的，面对类似的性能问题，是增加最后一个字母的 ASCII 字符，然后做一个 BETWEEN。然后，对于 LIKE 功能的子集，您可以获得最佳性能。当然，它只在某些情况下有效，但是对于您正在搜索名称的超大型数据集，它会使性能从糟糕到可以接受。

    • 2

  5. 非常老的问题，但我找到了另一个快速解决这个问题的方法：

    SELECT s.name 
FROM spelers s 
WHERE ascii(s.name) in (ascii('B'),ascii('D'))
ORDER BY 1

    由于函数 ascii() 仅查看字符串的第一个字符。

    • 2

  6. 为了检查首字母，我经常使用转换为"char"（使用双引号）。它不是便携式的，但非常快。在内部，它只是简单地去除文本并返回第一个字符，并且“char”比较操作非常快，因为类型是 1 字节固定长度：

    SELECT s.name 
FROM spelers s 
WHERE s.name::"char" =ANY( ARRAY[ "char" 'B', 'D' ] )
ORDER BY 1

    请注意，转换为"char"比ascii()@Sole021 的解决方案更快，但它与 UTF8 不兼容（或任何其他编码），仅返回第一个字节，因此仅应在与普通旧 7 进行比较的情况下使用位 ASCII 字符。

    • 2

  7. 有两种方法尚未提及处理此类情况：

    1. 部分（或分区 - 如果手动为全范围创建）索引 - 当只需要数据子集时最有用（例如在某些维护期间或某些报告的临时）：

      CREATE INDEX ON spelers WHERE name LIKE 'B%'

    2. 对表本身进行分区（使用第一个字符作为分区键）——这种技术在 PostgreSQL 10+（不那么痛苦的分区）和 11+（查询执行期间的分区修剪）中特别值得考虑。

    此外，如果表中的数据已排序，则可以从使用BRIN 索引（超过第一个字符）中受益。

    • 1

  8. 进行单个字符比较可能更快：

    SUBSTR(s.name,1,1)='B' OR SUBSTR(s.name,1,1)='D'
    • -4

相关问题