查询以某事开头和结尾的字符串的性能 [英] Performance of querying for a string that starts and ends with something

问题描述

在数据库中查询以某字符开头并以某字符结尾的某个字符列是否有性能差异？

Are there any performance difference in querying a database for some character column starting with something and ending with something?

SELECT * FROM table WHERE column like '%something'
SELECT * FROM table WHERE column like 'something%'

选择一种方法代替另一种方法有什么缺点吗？忽略用户匹配单词开头或结尾的需求。

实现方式可能算法有所不同？

Any downsides to choose one approach instead of another? Disregarding the user need for matching the start or end of a word.
The way it is implemented maybe the algorithms differ in some way?

推荐答案

SQL优化器将在第二个示例中在列上查找索引，并将其范围缩小到以通配符前的字符开头的记录。在第一个示例中，它无法缩小范围，因此您需要扫描索引或表（取决于索引结构）。

The SQL optimizer will look for an index on column in the second example, and will narrow it down to the records beginning with the characters before the wildcard. In the first example, it can't narrow it down, so you'll like scan either the index or table (depending on index structure).

哪个SQL产品是您使用的是什么？

Which SQL product are you using?

这篇关于查询以某事开头和结尾的字符串的性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！