为什么 PostgreSQL LIKE 查询如此慢，以及如何使它们更快？

优化 PostgreSQL LIKE 查询：解决性能问题

PostgreSQL 的 LIKE 查询用于子字符串匹配，通常表现出不可预测的性能，范围从毫秒到秒。 本文探讨了根本原因和有效的优化策略。

了解性能变化

性能不一致源于LIKE查询的固有资源需求。 与有效利用索引的相等检查不同，LIKE 查询通常需要全表扫描。

索引限制

虽然 B 树索引对于性能至关重要，但它们本质上并不适合 LIKE 操作。 列上的索引（例如 owner1）增强了精确匹配 (SELECT * FROM parcels WHERE owner1 = 'John Doe')，但对 LIKE 查询的好处有限。

性能增强技巧

几种技术可以显着提高LIKE查询性能：

全文搜索 (FTS)

PostgreSQL 的 FTS 提供高效的文本搜索。 但是，它不直接支持 LIKE 运算符，并且对单词进行操作，而不是任意子字符串。

前缀匹配优化

对于没有前导通配符的模式（例如 'foo%'），请使用带有 B 树索引的运算符类，例如 text_pattern_ops 或 varchar_pattern_ops。这些优化了左锚定模式匹配。

灵活匹配的三元组索引

pg_trgm 扩展与 GIN 或 GiST 索引相结合，提供 trigram 索引。这支持所有 LIKE 和 ILIKE 模式，无论通配符位置如何。

进一步考虑

• 字符串长度：短于三个字符的索引单词将起作用，但少于三个字符的模式仍可能会触发完整索引扫描。
• PostgreSQL 11 及更高版本： PostgreSQL 11 引入了 ^@ 运算符和 starts_with() 函数，用于与 SP-GiST 索引进行高效前缀匹配。
• 排序规则和索引：使用 COLLATE "C" 以及针对特定排序规则定制的索引或运算符类可以优化特定场景的前缀匹配和 LIKE 查询性能。

以上是为什么 PostgreSQL LIKE 查询如此慢，以及如何使它们更快？的详细内容。更多信息请关注PHP中文网其他相关文章！