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

相关文章

来自专栏L宝宝聊IT

MySQL架构组成、逻辑模块组成

Mysql逻辑结构可以看成是二层架构,第一层通常叫做SQL Layer,在mysql数据库系统处理底层数据之前的所有工作都在这一层完成的,包括权...

813
来自专栏闵开慧

mysql性能调优

mysql调优思路: 1.数据库设计与规划--以后再修该很麻烦,估计数据量,使用什么存储引擎  2.数据的应用--怎样取数据,sql语句的优化  3.mysql...

3585
来自专栏数据库新发现

数据块转储及RDBA的转换

723
来自专栏文渊之博

关于tempdb的一些注意事项

    由于数据库的文件的位置对于I/O性能如此重要,以至于在创建主数据文件的文职时,需要考虑tempdb性能对系统性的影响,因为它是最动态的数据库,速度还需要...

2066
来自专栏hanlp学习笔记

如何在ubuntu使用hanlp

  以前,我对大部分的处理中文分词都是使用python的结巴分词工具,该分词工具是在线调用API, 关于这个的分词工具的原理介绍,我推荐一个好的博客:

250
来自专栏数据库新发现

MySQL 8.0.12 有什么新特性?

原文链接:http://enmotech.com/web/detail/1/577/1.html

1110
来自专栏杨建荣的学习笔记

海量数据迁移之传输表空间(一) (r5笔记第71天)

在自己接触的很多的数据迁移工作中,使用外部表在一定程度上达到了系统的预期,对于增量,批量的数据迁移效果还是不错的,但是也不能停步不前,在很多限定的场景中,有很多...

2317
来自专栏铭毅天下

干货 | Elasticsearch索引生命周期管理探索

Elasticsearch上海Meetup中ebay工程师提了索引生命周期管理的概念。的确,在Demo级别的验证阶段我们数据量比较小,不太需要关注索引的生命周期...

1432
来自专栏Debian社区

Postgres 10 开发者新特性

目前非常流行的RDBMS PostgresSQL已经在几周前发布了它的第10个版本。由于Postgres的可靠性、节约成本、成熟,当然还有它的开源,已经21岁的...

872
来自专栏数据和云

案例分析:程序媛记一次特殊的“故障”处理

作者 | 兰珊,多年数据库服务经验、主要服务于政府、电网等企。擅长数据库升级、迁移、故障处理。

812

扫码关注云+社区