sql - 用于 250K+ 字符串的通配符搜索的 Fast(er) 方法

Question

我在 MySQL 数据库中有一个英语词典，其中只有超过 250K 个条目，我正在使用一个简单的 ruby​​ 前端在字符串的开头使用通配符来搜索它。到目前为止，我一直这样做:

SELECT * FROM words WHERE word LIKE '_e__o'

甚至

SELECT * FROM words WHERE word LIKE '____s'

我总是知道单词的确切长度，但除了一个字符之外的所有字符都可能是未知的。

这比糖蜜慢，比没有前导通配符的类似查询慢大约 15 倍，因为无法使用该列的索引。

我尝试了几种方法来缩小搜索范围。例如，我添加了 26 个额外的列，其中包含每个单词的单个字母计数，并首先使用这些列来缩小搜索范围。我也试过按字长缩小。由于前导通配符搜索固有的低效率，这些方法几乎没有任何区别。我试验过 REGEXP 语句，它甚至更慢。

SQLite 和 PostgreSQL 与 MySQL 一样有限，虽然我对 NoSQL 系统的经验有限，但我的研究给我的印象是它们在可伸缩性方面表现出色，而不是我需要的那种性能。

那么我的问题是，我应该在哪里寻找解决方案？我是否应该继续尝试寻找一种方法来优化我的查询或添加可以缩小我的潜在记录集的补充列？是否有专门设计用于完成这种快速通配符搜索的系统？

Answer 1

使用 PostgreSQL 9.1 和 pg_trgm 扩展，您可以创建可用于您所描述的类似条件的索引。

有关示例，请参见此处:http://www.depesz.com/2011/02/19/waiting-for-9-1-faster-likeilike/

我使用 LIKE '____1' 在一个有 300k 行的表上验证了它，它确实使用了这样一个索引。计算该表中的行数大约需要 120 毫秒(在旧笔记本电脑上)。有趣的是，表达式 LIKE 'd___1' 并不快，速度差不多。

这还取决于搜索词中的字符数，据我所知，它变得越长，速度就越慢。

如果性能可以接受，您需要检查您的数据。