二月 21, 2025
摘要：在本教程中，您将学习如何在 PostgreSQL 中使用索引优化 LIKE 筛选条件。
目录
介绍
SQL 中的LIKE操作符经常会引起意外的性能下降，因为某些搜索词会阻止索引的有效使用。这意味着有些搜索词可以很好地进行索引，但其他搜索词则不能。通配符的位置会让一切变得不同。
示例
下面的示例在搜索词的中间使用了%通配符：
SELECT first_name, last_name, date_of_birth
FROM employees
WHERE UPPER(last_name) LIKE 'WIN%D'
QUERY PLAN
----------------------------------------------------------------------------
Index Scan using emp_up_name on employees (cost=0.01..8.29 rows=1 width=17)
Index Cond: (upper((last_name)::text) ~>=~ 'WIN'::text)
AND (upper((last_name)::text) ~<~ 'WIO'::text)
Filter: (upper((last_name)::text) ~~ 'WIN%D'::text)
在遍历索引树的过程中，LIKE筛选条件只能使用到第一个通配符之前的字符。其余字符只是过滤谓词，不会缩小扫描的索引范围。因此，单个LIKE表达式可以包含两种谓词类型：（1）第一个通配符之前的部分作为访问谓词；（2）其他字符作为一个过滤谓词。
警告
LIKE运算符会逐个字符进行处理，而排序规则可以将多个字符视为单个排序项。因此，某些排序规则会阻止LIKE使用索引。
工作原理
第一个通配符之前的前缀越有选择性，扫描的索引范围就越小。这样也会使得索引查找更快。下图使用了三种不同的LIKE表达式，来说明这种关系。这三种方式都获得了同一行，但扫描的索引范围（以及性能）却大不相同。
各种 LIKE 的检索
第一个表达式在通配符前有两个字符。它们将扫描的索引范围限制到了 18 行。其中只有一行与整个LIKE表达式匹配，其余的 17 行被读取后丢弃了。第二个表达式具有较长的前缀，可将扫描的索引范围缩小到两行。使用该表达式，数据库只额外读取了一行与结果无关的记录。最后一个表达式根本没有过滤谓词：数据库只读取了与整个LIKE表达式匹配的条目。
重要
只有第一个通配符之前的部分，会用作访问谓词。
其余字符不会缩小扫描的索引范围，不匹配的条目只会从结果中排除掉。
反面的用法是：以一个通配符开头的LIKE表达式。这样的LIKE表达式不能用作访问谓词。如果没有其他提供访问谓词的条件，则数据库必须扫描整个表。
提示
请避免使用带有通配符前缀的
LIKE表达式（例如'%TERM'）。
带绑定参数的 LIKE 条件
通配符的位置会影响索引的使用，至少在理论上是这样。实际上，当通过绑定参数提供搜索词时，优化器会创建一个通用的执行计划。在这种情况下，优化器必须猜测大多数执行是否带有一个通配符前缀。
在优化一个带绑定参数的LIKE条件时，PostgreSQL 会假定参数值有一个通配符前缀。在这种情况下，PostgreSQL 只是不使用索引。要让一个LIKE表达式能够用上索引的唯一方法是，让优化器能够见到实际的搜索词。如果不使用绑定参数，而是将搜索词直接放入 SQL 语句中，则必须采取其他预防措施来防范 SQL 注入攻击！
即使数据库优化了有通配符前缀的执行计划，它仍然无法提供性能。在这种情况下，您可以使用WHERE子句的另一部分来有效地访问数据。如果没有其他访问的路径，则可以使用全文索引作为解决方案。
PostgreSQL 提供了@@运算符来实现全文搜索。请参阅 PostgreSQL 文档中的“全文搜索”。