sql - 在 SQL 中实现子字符串搜索的最佳方法是什么？

Question

我们这里有一个简单的 SQL 问题。在 varchar 列中，我们想要在字段中的任何位置搜索字符串。实现此性能的最佳方法是什么？显然索引在这里无济于事，还有其他技巧吗？

我们正在使用 MySQL，并且有大约 300 万条记录。我们需要每秒执行许多这样的查询，因此真正尝试以最佳性能实现这些查询。

到目前为止最简单的方法是:

Select * from table where column like '%search%'

---

我应该进一步指定该列实际上是一个长字符串，如“sadfasdfwerwe”，我必须在此列中搜索“asdf”。 所以它们不是句子并试图匹配其中的单词。全文搜索在这里仍然有用吗？

Answer 1

查看我的演示文稿 Practical Fulltext Search in MySQL .

我比较过:

• LIKE 谓词
• Regular expression谓词(不比LIKE好)
• MyISAM FULLTEXT索引
• Sphinx Search
• Apache Lucene
• 倒排索引
• 谷歌 Custom Search Engine

今天我要用的是Apache Solr ，它将 Lucene 放入具有大量额外功能和工具的服务中。

---

关于您的评论:啊哈，好的，不。我提到的全文搜索功能都无济于事，因为它们都假设有某种词边界

另一种有效查找任意子字符串的方法是 N-gram方法。基本上，创建所有可能的 N 个字母序列的索引，并指向每个相应序列出现的字符串。通常这是通过 N=3 或 trigram 完成的。 ，因为这是匹配较长子字符串和将索引保持在可管理大小之间的折衷点。

我不知道有任何 SQL 数据库透明地支持 N-gram 索引，但您可以使用倒排索引自行设置:

create table trigrams (
  trigram char(3) primary key
);

create table trigram_matches (
  trigram char(3),
  document_id int,
  primary key (trigram, document_id),
  foreign key (trigram) references trigrams(trigram),
  foreign key (document_id) references mytable(document_id)
);

现在用困难的方式填充它:

insert into trigram_matches
  select t.trigram, d.document_id
  from trigrams t join mytable d
    on d.textcolumn like concat('%', t.trigram, '%');

当然这需要相当长的时间!但是一旦完成，您就可以更快地进行搜索:

select d.*
from mytable d join trigram_matches t
  on t.document_id = d.document_id
where t.trigram = 'abc'

当然你可以搜索超过三个字符的模式，但倒排索引仍然有助于缩小你的搜索范围:

select d.*
from mytable d join trigram_matches t
  on t.document_id = d.document_id
where t.trigram = 'abc'
  and d.textcolumn like '%abcdef%';