字符串列索引对含部分字符串的WHERE子句性能影响及LIKE查询疑问

问题1：字符串列上创建的索引，在WHERE子句仅指定部分字符串时是否会产生影响？ #

这得分情况讨论，核心看你用的匹配模式和索引类型：

• 要是是前缀匹配（比如WHERE name LIKE 'J%'）：普通B-tree索引（多数数据库默认的字符串索引类型）会直接生效。因为B-tree是按字符串字典序排序的，数据库能快速定位到以指定前缀开头的所有行，不用做全表扫描。
• 要是是后缀匹配（WHERE name LIKE '%k'）或者包含匹配（WHERE name LIKE '%ar%'）：普通B-tree索引就没用了。这种匹配没法利用B-tree的有序性，数据库不知道哪些行的结尾或中间包含指定字符串，只能挨个扫全表。
• 特殊优化方案：如果经常要做这类后缀/包含匹配，可以试试专门的索引类型——比如PostgreSQL里的GIN/GIST索引（配合全文检索类型）、MySQL的全文索引；或者自己加个反向存储的列（比如把name反转存成name_reversed，然后用WHERE name_reversed LIKE 'k%'来匹配原字符串结尾是k的情况），这样就能重新用上B-tree索引了。

问题2：执行查询语句select title from books where name ilike '% of the rings'; 若在title列创建索引，是否能提升该查询的性能？还是仅当WHERE子句提供完整字符串时索引才生效？ #

先纠正一个关键误区：这个查询的过滤条件是name列，但你在title列建的索引，完全不会被用到——数据库只会用过滤条件对应列的索引来优化查询，和你要返回的title列没关系。

假设我们把索引建在name列上，再看情况：

• 这个查询是ilike '% of the rings'，属于后缀匹配（%在开头），普通B-tree索引依然不生效。不管你给的是完整字符串还是部分字符串，只要是%开头的模糊匹配，B-tree都帮不上忙，还是得全表扫描。
• 只有当你用前缀匹配（比如ilike 'The Lord%'）或者完整字符串匹配（ilike 'The Lord of the Rings'）时，普通B-tree索引才会生效，能明显提升查询速度。
• 要是想优化这种后缀匹配的查询，还是得用前面说的特殊方案：全文索引、反向列+前缀匹配，或者专门的文本索引类型。

内容的提问来源于stack exchange，提问作者Jarek Sularz