如何加快MySQL模糊匹配查询

有时我会看到条件如下的模式匹配查询：“其中的字段名像'％something％'”。 MySQL不能为这些查询使用到索引，这意味着它必须每次都进行一次全表扫描。

（这真的只有一半是真的 - 因为还有FullText索引可利用。）

我最近试图找到一个解决方案，我的朋友告诉我Trigrams可以帮助到我们。 让我演示给你看下名字为Daniel的Trigram：

但这有用吗？

让我给你看一个例子。 您有以下email的schema：

表带有这样的数据：

我们正在寻找诸如'％n.pierre％'之类的email地址：

找到11个电子邮件地址，但它必须扫描整个索引（318458行）。 这不好！ 让我们试着让它变得更好。

Trigram表

我创建了这样的表格：

我们可以看到，有一个名为“trigram”的索引。

计划是为每个电子邮件地址创建一个trigram。 我写了以下触发器：

当有插入时，它创建并将trigrams插入到email_trigram表中。 anderson.pierre的Trigram：

通过以下查询，我们可以使用n.pierre查找所有email地址：

它不必读取整个表格，但仍需要读取很多行，甚至使用filesort。 我不想手动创建trigrams，所以我写了下面的procedure

由于使用了Trigram,我们正在寻找单词的一部分（如err或ier），可以有很多匹配。 如果我们使用像derson.pierre这样的更长的条件，那么这个procedure需要读取65722行的过程。 还是太多了。

让我们来看看选择性：

有些部分会返回许多行。 正如我所说，更多的部分意味着更多的行。

我希望有更大的改进，所以我想知道我们还能做些什么。 由于前导％，MySQL不能使用索引。 我们如何避免这种情况？ 让我们保存我们可能要查找的email地址的所有可能版本。

短路方法

嗯...可以工作吗？ 我们来测试一下。 我创建了以下这个表并触发：

让我们找到包含n.pierre的email地址：

哇，这比以前好多了！ 它速度超过100倍！ 现在你可以喝一杯啤酒，因为这是你应得的。

选择性

还有一些部分也会导致很多读数，但现在我们正在使用更长的模式：

使用六个以上的字符为我们提供了更好的选择性。

表统计

在此测试中，我使用了318458个随机email地址，并且这两种方法创建了2749000个附加行。

磁盘上的大小：

正如我们预期的那样，他们将使用比原始表更多的空间。

缺点

两种解决方案都需要额外的表

该表包含数百万行的短行，并且可以使用几个空格。

需要三个触发器（插入，更新和删除，这可能会影响表上的写入性能），或者应用程序必须使该表保持最新状态。

优点

找到一个email地址将会更快，并需要更少的读取。

用户会更满意。

结论

如果MySQL中没有内置的解决方案或索引可以帮助或解决您的问题，请不要放弃。很多时候，只需稍作修改，您就可以创建自己的索引表或使用其他技巧。

在这种特殊情况下，如果您愿意牺牲一些额外的磁盘空间，您可以使用正确的方法加快查询速度。 Trigram并不是最好的选择，但我可以看到可能更好的用例。