如何加快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并不是最好的选择,但我可以看到可能更好的用例。

原文发布于微信公众号 - IT技术精选文摘(ITHK01)

原文发表时间:2018-03-30

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏PHP技术

MySQL时间函数,用起来比PHP还爽

前一篇写了PHP的时间函数(还是草稿),这一篇就写Mysql的时间函数吧。最近做的项目,关乎权限,于是自然而然的就与有效期联系在了一起。其 中有一个功能是生成特...

2856
来自专栏Java技术

一步步带你了解ID发号器是什么、为什么、如何做!

上一篇文章《面试必备:如何将一个长URL转换为一个短URL?》中谈到如何将长地址URL转换为短地址URL,其中谈到了一个比较理想的解决方案就是使用发号器生成一个...

932
来自专栏微信终端开发团队的专栏

微信ANDROID客户端-会话速度提升70%的背后

image.png 背景 打开会话速度慢 在同一个会话有较多的历史消息下,各种查询,更新,删除等操作,速度明显下降。 在会话内有较大量历史消息情况下,进入...

3687
来自专栏数据和云

【新特性视频第2期】关于IMEU与Expression Statistics Store

新特性5分钟,带你循序渐进了解Oracle 12.2的最新特性。优秀就是每天进步一点点。 上一期视频回顾: 【视频】In Memory的内部结构和实现机制 {...

2619
来自专栏DOTNET

Entity Framework——性能测试

内容提要 一、对EF框架的性能测试 增、删、改,查测试及性能优化 二、使用sql执行 增、删、改,查测试 三、对以上两种方式对比分析 一 对EF框架的测试 1...

4606
来自专栏我的博客

Elasticsearch学习搜索的笔记

1.普通查询(全文搜索) 查询name=Smith的文档数据 GET /megacorp/employee/_search { "query" : { ...

1865
来自专栏程序员的SOD蜜

隐藏在程序旮旯中的“安全问题”

    作为一个真正的程序员,必须有高度的“安全意识”,因为我们作出的软件运行在复杂的环境中,不能把不该有异常抛给用户,更不能把漏洞留给“黑客”,当然也不能把“...

2045
来自专栏CSDN技术头条

解析大型.NET ERP系统 20条数据库设计规范

数据库设计规范是个技术含量相对低的话题,只需要对标准和规范的坚持即可做到。当系统越来越庞大,严格控制数据库的设计人员,并且有一份规范书供执行参考。在程序框架中,...

2207
来自专栏张善友的专栏

SQL Server数据库碎片

当索引所在页面的基于主关键字的逻辑顺序,和数据文件中的物理顺序不匹配时,碎片就产生了。所有的叶级页包含了指向前一个和后一个页的指针。这样就形成一个双链表。理想情...

1758
来自专栏玩转JavaEE

使用MyBatis轻松实现递归查询与存储过程调用

vhr部门管理模块更新啦!为了让小伙伴们快速理解部门管理模块实现思路,我想通过3篇短文来给大家介绍下大致的实现思路和核心代码。 项目地址:https://git...

3256

扫码关注云+社区