如何加快MySQL模糊匹配查询

妖狐艹你老母 2022-05-28 05:16 374阅读 0赞

![1e9c79f0ae84fbbdeae45275aaaeb037.png_wxfrom_5_wx_lazy_1][]

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

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

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

![86fb922876bfcb9523a8acf25faef993.png_wxfrom_5_wx_lazy_1][]

**但这有用吗？**

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

![6a14c00346f95b1465515e1b89b0cc00.png_wxfrom_5_wx_lazy_1][]

表带有这样的数据：

![76f0d25af80303c878ce619e955eaa4c.png_wxfrom_5_wx_lazy_1][]

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

![8d0c144dea97bf50d8f197097bff1fd6.png_wxfrom_5_wx_lazy_1][]

![fabf0ac3574fd50560d8f1f52bf84439.png][]

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

**Trigram表**

我创建了这样的表格：

![aa4edee53e89039040abcb8c675875b1.png][]

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

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

![a64596192d775feb576b879e0425b44a.png][]

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

![ce79ab4041680714263bb3044d0b075b.png][]

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

![069ceecd963bb0a12b21a1ba33ed1ba1.png][]

![7123b2a91139ecd12d97ec3a6431f7e4.png][]

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

![a96bd8cd86fe3875fd43844b441fd351.png][]

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

让我们来看看选择性：

![e62cdad9fbe34db59b197125f3f29ed1.png][]

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

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

**短路方法**

![28e95f8219105d7eadeb73148387ede7.png][]

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

![6b1c46bf294e372066e7417b3d2c67de.png][]

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

![84822e8214e77719330b351281fd70eb.png][]

![70f564dd1752cbd608fcea7f3bdf0fd1.png][]

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

**选择性**

![ae3abd908fa3871f836d74386ddc8d6f.png][]

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

![9766e21892f1d079ad8d5c6bad74d08b.png][]

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

表统计

![687b2b7337dc2b8ef6b76b8056201d2a.png][]

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

磁盘上的大小：

![79b9eaa313b41dee76f95a73dd923328.png][]

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

**缺点**

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

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

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

**优点**

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

用户会更满意。

**结论**

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

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

[1e9c79f0ae84fbbdeae45275aaaeb037.png_wxfrom_5_wx_lazy_1]: https://img-blog.csdnimg.cn/img_convert/1e9c79f0ae84fbbdeae45275aaaeb037.png;wxfrom=5&wx_lazy=1
[86fb922876bfcb9523a8acf25faef993.png_wxfrom_5_wx_lazy_1]: https://img-blog.csdnimg.cn/img_convert/86fb922876bfcb9523a8acf25faef993.png;wxfrom=5&wx_lazy=1
[6a14c00346f95b1465515e1b89b0cc00.png_wxfrom_5_wx_lazy_1]: https://img-blog.csdnimg.cn/img_convert/6a14c00346f95b1465515e1b89b0cc00.png;wxfrom=5&wx_lazy=1
[76f0d25af80303c878ce619e955eaa4c.png_wxfrom_5_wx_lazy_1]: https://img-blog.csdnimg.cn/img_convert/76f0d25af80303c878ce619e955eaa4c.png;wxfrom=5&wx_lazy=1
[8d0c144dea97bf50d8f197097bff1fd6.png_wxfrom_5_wx_lazy_1]: https://img-blog.csdnimg.cn/img_convert/8d0c144dea97bf50d8f197097bff1fd6.png;wxfrom=5&wx_lazy=1
[fabf0ac3574fd50560d8f1f52bf84439.png]: /images/20220528/000abd5541a540198893f80bcded2cb0.png
[aa4edee53e89039040abcb8c675875b1.png]: /images/20220528/56a0201e634545ceb9f0191a60864f54.png
[a64596192d775feb576b879e0425b44a.png]: /images/20220528/28bf72836d284c52b6763ec701c96b0e.png
[ce79ab4041680714263bb3044d0b075b.png]: /images/20220528/80b48eadb31843f6889c563d241e3e5c.png
[069ceecd963bb0a12b21a1ba33ed1ba1.png]: /images/20220528/745c01408dee481fb8b76c2255403cc2.png
[7123b2a91139ecd12d97ec3a6431f7e4.png]: /images/20220528/1c868475777541c8a1852e08c4d3af83.png
[a96bd8cd86fe3875fd43844b441fd351.png]: /images/20220528/52cbe0062ae849c9a5f68acc53d14960.png
[e62cdad9fbe34db59b197125f3f29ed1.png]: /images/20220528/590bd34357b441d39a53231c1817be93.png
[28e95f8219105d7eadeb73148387ede7.png]: /images/20220528/bf16f74d691c42dea03216fcfffa41c0.png
[6b1c46bf294e372066e7417b3d2c67de.png]: /images/20220528/99da91b1815341649923b196a7d587d6.png
[84822e8214e77719330b351281fd70eb.png]: /images/20220528/a19ee8ab532c47efb66389e0080f3d19.png
[70f564dd1752cbd608fcea7f3bdf0fd1.png]: /images/20220528/71e22b4fa42b4f55bcca591541256f7c.png
[ae3abd908fa3871f836d74386ddc8d6f.png]: /images/20220528/38baf0c581494391ba5b6201120f29f2.png
[9766e21892f1d079ad8d5c6bad74d08b.png]: /images/20220528/73460afd969743979c072011801db52d.png
[687b2b7337dc2b8ef6b76b8056201d2a.png]: /images/20220528/8ff817a27dd14244bb9d6bed2b803281.png
[79b9eaa313b41dee76f95a73dd923328.png]: /images/20220528/abca2d01ab804eaebea773b900ecf066.png