使用"like“和通配符优化MySQL搜索

Question

像这样的查询如何

SELECT * FROM sometable WHERE somefield LIKE '%value%'

会被优化吗？

这里的主要问题是第一个通配符，它阻止DBMS使用索引。

编辑:更重要的是，某些字段的值是实字符串(而不是一段文本)，因此无法执行全文搜索。

Answer 1

两种方式：

(1)使用内存中的表，这样它就会运行得非常快。

(2)提出了比foo LIKE '%bar%'更好的索引和搜索算法。如果不更多地了解你的问题，就不可能对此提出任何建议。

正如您已经指出的，%bar%模式保证对每个查找执行表扫描，这会使数据库软件中任何可能的搜索独创性化为乌有。

Answer 2

你的弦有多长？

如果它们相对较短(例如，英语单词；avg_len=5)，并且您有多余的数据库存储，请尝试此方法：

• 对于要存储在表中的每个单词，取而代之的是该单词的每个可能的后缀。换句话说，您一直剥离第一个字符，直到什么都没有留下。例如，单词
  • value
  • alue
  • lue
  • ue
  • e

提供：value

​

在值中存储这些后缀中的每一个现在都可以使用LIKE 'alu%'搜索子字符串(这将查找'alu‘作为’

• ‘的一部分)。

通过存储所有后缀，您已经消除了对前导通配符的需要(允许使用索引进行快速查找)，但代价是存储空间。

存储成本

在每个单词的基础上，存储单词所需的字符数变为二次( word_len*word_len / 2 )，即单词长度为二次。下面是不同字长的增长因子：

单词3个字母的单词：(3*3/2) / 3 = 1.5

• 5-letter word：(5*5/2) / 5 = 2.5

• 7-letter word：(7*7/2) / 7 = 3.5

• 12-letter word：(12*12/2) / 12 = 6

存储一个单词所需的行数从1增加到word_len。注意这个开销。额外的列应该保持在最低限度，以避免存储大量冗余数据。例如，最初找到单词的页码应该没问题(考虑无符号的小整数)，但是单词的大量元数据应该以单词为单位存储在单独的表中，而不是每个后缀。

Considerations

在我们拆分“单词”(或片段)时，有一个权衡。作为一个真实世界的例子:我们如何处理连字符？我们将形容词five-letter存储为一个单词还是两个单词？

权衡如下：

• 任何已分解的元素都不能作为单个元素找到。如果我们分开存储five和letter，搜索five-letter或fiveletter将会占用更多的存储空间。请记住，存储需求在字长中呈二次曲线增长。

为方便起见，您可能希望删除连字符并存储fiveletter。现在可以通过搜索five、letter和fiveletter找到该单词。(如果还从任何搜索查询中去掉连字符，用户仍然可以成功查找five-letter。)

最后，有一些存储后缀数组的方法不会产生太多开销，但我还不确定它们是否能很好地转换到数据库中。

Answer 3

使用Full Text Search。"Initial Idea“标题具有相同的示例，并指向工作示例解决方案。

And the MySQL docs

编辑:它不能在SQL本身中进行调整。使用LOCATE或PATINEX这样的函数也不会有帮助。