SQL like '%Query%' 与全文搜索 CONTAINS 查询的性能
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3213998/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Performance of like '%Query%' vs full text search CONTAINS query
提问by dotnetguts
I have a situation where I would like to search a single word.
我有一种情况，我想搜索一个词。
For that scenario, which query would be good from a performance point of view?
对于这种情况，从性能的角度来看，哪个查询会更好？
Select Col1, Col2 from Table Where Col1 Like '%Search%'
or
或者
Select Col1, Col2 from Table Where Col1 CONTAINS(Col1,'Search')
?
?
采纳答案by OMG Ponies
Full Text Searching (using the CONTAINS) will be faster/more efficient than using LIKE with wildcarding. Full Text Searching (FTS) includes the ability to define Full Text Indexes, which FTS can use. Dunno why you wouldn't define a FTS index if you intended to use the functionality...
全文搜索（使用 CONTAINS）将比使用带有通配符的 LIKE 更快/更有效。全文搜索 (FTS) 包括定义 FTS 可以使用的全文索引的能力。不知道如果您打算使用该功能，为什么不定义 FTS 索引...
LIKE with wildcarding on the left side (IE: LIKE '%Search') can not use an index (assuming one exists for the column), guaranteeing a table scan. I haven't tested & compared, but regex has the same pitfall. To clarify, LIKE '%Search'and LIKE '%Search%'can notuse an index; LIKE 'Search%'can use an index.
左侧带有通配符的 LIKE (IE:)LIKE '%Search'不能使用索引（假设该列存在索引），从而保证表扫描。我还没有测试和比较，但正则表达式有同样的陷阱。为了澄清，LIKE '%Search'并且LIKE '%Search%'可以不使用索引; LIKE 'Search%'可以使用索引。
回答by Mark Wilkins
For a typical database, the CONTAINS search can be much faster assuming the appropriate full text search index is built on the field being searched. The evaluation of the LIKEoperator generally doesn't use an index and thus must read all the data.
对于典型的数据库，假设适当的全文搜索索引建立在要搜索的字段上，则 CONTAINS 搜索会快得多。LIKE运算符的评估通常不使用索引，因此必须读取所有数据。
回答by Agrawars
Like search on Table it self, will kill the performance. Better to apply like search on CTE.
就像在 Table 上搜索它自己一样，会杀死性能。最好在 CTE 上像搜索一样应用。
