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

相关文章

来自专栏hanlp学习笔记

如何在ubuntu使用hanlp

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

520
来自专栏PingCAP的专栏

TiDB 2.1 GA Release Notes

2018 年 11 月 30 日,TiDB 发布 2.1 GA 版。相比 2.0 版本,该版本对系统稳定性、性能、兼容性、易用性做了大量改进。

1690
来自专栏闵开慧

mysql性能调优

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

4075
来自专栏数据和云

细致入微:Oracle中执行计划在Shared Pool中的存储位置探秘

这两天我一直在想一个问题,那就是 Oracle 的执行计划到底存储在什么地儿?它会是一种什么样的格式? 这里我试图对这个问题做一点我自己认为的解释,这个解释可能...

3105
来自专栏数据库新发现

数据块转储及RDBA的转换

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

一则orabbix报警的分析(r6笔记第65天)

最近使用zabbix监控之后,都会在凌晨收到1台数据库服务器的报警短信,报警的内容为: No data received from Orabbix 这个错误其实...

3258
来自专栏铭毅天下

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

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

2802
来自专栏数据和云

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

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

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

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

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

2407
来自专栏芋道源码1024

数据库中间件 MyCAT源码分析 —— XA分布式事务

---- 1. 概述 2. XA 概念 3. MyCAT 代码实现 3.1 JDBC Demo 代码 3.2 MyCAT 开启 XA 事务 3.3 MyCAT...

4479

扫码关注云+社区

领取腾讯云代金券