如何加快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 条评论
登录 后参与评论

相关文章

来自专栏从ORACLE起航,领略精彩的IT技术。

警示:AL32UTF8字符集不是ZHS16GBK字符集的超集

3576
来自专栏北京马哥教育

记一次调试python内存泄露的问题

这两天由于公司需要, 自己编写了一个用于接收dicom文件(医学图像文件)的server. 经过各种coding-debuging-coding-debugi...

3267
来自专栏大数据文摘

将Python和R整合进一个数据分析流程

2258
来自专栏帘卷西风的专栏

mysql 大小写敏感的一个解决方案

     今天,有同事告诉我,我们游戏登陆的时候,账号和密码没有区分大小写,后来又发现创建账号和角色也没有区分大小写。思考登陆流程之后,应该是Mysql没有区分...

761
来自专栏python百例

103-多进程的效率

没有多进程,即使CPU有多个核心,程序只是运行在一个核心上,无法利用多进程提升效率。5000万次加法,如果需要2.5秒,调用两次共花费5秒。

602
来自专栏玄魂工作室

Hacker基础之Python篇:一、环境安装和基础知识

0x01. 前言 emmmmmmm...你只需知道这是一门用途很广的语言,上到大数据AI,下到Linux运维,都可以使用Python,当然,黑客也用Pyth...

2516
来自专栏玩转JavaEE

Redis字符串(STRING)中BIT相关命令

上篇文章我们对STRING数据类型中一些基本的命令进行了介绍,但是没有涉及到BIT相关的命令,本文我们就来看看几个和BIT相关的命令。 本文是Redis系列的第...

3338
来自专栏惨绿少年

Shell编程基础篇-上

1.1 前言 1.1.1 为什么学Shell Shell脚本语言是实现Linux/UNIX系统管理及自动化运维所必备的重要工具, Linux/UNIX系统的底层...

1750
来自专栏有趣的Python

1-Java基础语法-Java初识

解释执行由java虚拟机完成,将字节码文件解释为具体平台上的机器指令。实现一次编译,到处运行

613
来自专栏猿人谷

C++容器和算法

容器:某一类型数据的集合。 C++标准顺序容器包括:vector,list,queue 容器初始化 vector<int> t; ...

1717

扫描关注云+社区