通过搜索优化加快子字符串和正则表达式查询

搜索优化可以提高使用谓词的查询的性能，这些谓词在文本或半结构化数据中搜索子字符串或使用正则表达式。有关子字符串搜索如何搜索半结构化数据的详细信息，请参见 通过搜索优化加快半结构化数据的查询速度。

此功能包括使用以下元素的谓词：

备注

必须使用 ON 子句为特定列启用此功能，该子句位于 ALTER TABLE ...ADD SEARCH OPTIMIZATION 命令中。（在表级别启用搜索优化不会优化子字符串搜索。）例如：

ALTER TABLE mytable ADD SEARCH OPTIMIZATION ON SUBSTRING(mycol);
Copy

如果省略 ON 子句，则不会优化子字符串搜索。

搜索优化服务可以提高搜索长度为 5 个或更多字符的子字符串时的性能。（更具选择性的子字符串可以带来更好的性能。） 搜索优化服务 不 对以下谓词使用搜索访问路径，因为子字符串短于 5 个字符：

LIKE '%TEST%'
Copy

对于以下谓词，搜索优化服务可以使用搜索访问路径搜索 SEARCH 和 OPTIMIZED 的子字符串来优化此查询。但是，由于子字符串短于 5 个字符，因此不对 IS 使用搜索访问路径。

LIKE '%SEARCH%IS%OPTIMIZED%'
Copy

对于针对文本使用 RLIKE、REGEXP 和 REGEXP_LIKE 的查询：

  • subject 实参必须是表中启用了搜索优化的 TEXT 列。

  • pattern 实参必须是字符串常量。

对于正则表达式，搜索优化服务在以下情况下效果最佳：

  • 该模式包含至少一个长度为 5 个或更多字符的子字符串字面量。

  • 该模式指定子字符串应至少出现一次。

例如，以下模式指定 string 应在目标文本中出现一次或多次：

RLIKE '(string)+'
Copy

搜索优化服务可以使用以下模式提高查询的性能，因为每个谓词都指定必须至少出现一次 5 个或更多字符的子字符串。（请注意，第一个示例使用 美元符号引用的字符串常量 来避免对反斜杠字符进行转义。）

RLIKE $$.*email=[\w\.]+@snowflake\.com.*$$
Copy
RLIKE '.*country=(Germany|France|Spain).*'
Copy
RLIKE '.*phone=[0-9]{3}-?[0-9]{3}-?[0-9]{4}.*'
Copy

相比之下，搜索优化不会对具有以下模式的查询使用搜索访问路径：

  • 没有任何子字符串的模式：

    RLIKE '.*[0-9]{3}-?[0-9]{3}-?[0-9]{4}.*'
    Copy

  • 仅包含短于 5 个字符的子字符串的模式：

    RLIKE '.*tel=[0-9]{3}-?[0-9]{3}-?[0-9]{4}.*'
    Copy

  • 使用交替运算符的模式，其中一个选项是短于 5 个字符的子字符串：

    RLIKE '.*(option1|option2|opt3).*'
    Copy

  • 子字符串为可选的模式：

    RLIKE '.*[a-zA-z]+(string)?[0-9]+.*'
    Copy

即使子字符串字面量短于 5 个字符，如果扩展正则表达式生成 5 个字符或更长的子字符串字面量，则搜索优化服务仍可以提高查询性能。

例如，考虑以下模式：

.*st=(CA|AZ|NV).*(-->){2,4}.*
Copy

在此示例中：

  • 尽管子字符串字面量（例如 st=、CA 等）短于 5 个字符，但搜索优化服务可识别子字符串 st=CA、st=AZ 或 ``st=NV``（每个子字符串的长度为 5 个字符）必须出现在文本中。

  • 同样，即使子字符串字面量 --> 短于 5 个字符，搜索优化服务也确定子字符串 ``-->-->``（长度超过 5 个字符）必须出现在文本中。

搜索优化服务可以使用搜索访问路径来匹配这些子字符串，这可以提高查询的性能。

语言: 中文