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

相关文章

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

MySQL删除数据的简单尝试 (r7笔记第98天)

在Oracle里面对于数据清理,如果是非分区表,目前我经常的处理思路是下面三个。 第一种是中规中矩,做好备份,然后开始清理,当然这种情况只是说明数据清理的部分,...

2875
来自专栏Debian社区

Postgres 10 开发者新特性

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

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

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

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

2247
来自专栏数据库新发现

MySQL 8.0.12 有什么新特性?

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

830
来自专栏菩提树下的杨过

mac 下卸载mysql的方法

今天在mac上瞎折腾时,把mysql玩坏了,想卸载重装,却发现找不到卸载程序,百度了下,将操作步骤备份于此: cd ~/ sudo rm /usr/local...

1857
来自专栏芋道源码1024

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

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

3589
来自专栏数据和云

使用 Direct Initial Load 初始化 GoldenGate 同步数据

作者简介 ? 桑凯 现任职于云和恩墨,具有多年 Oracle 数据库企业级运维经验,擅长容灾项目解决方案设计,作为项目经理负责多个基于 Oracle DataG...

3135
来自专栏PingCAP的专栏

TiDB 2.1 GA Release Notes

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

410
来自专栏数据和云

Library Cache优化与SQL游标

? 冷菠 冷菠,网名悠然(个人主页http://www.orasky.net),资深DBA,著有《Oracle高性能自动化运维》,有近10年的数据库运维、团队...

2755
来自专栏闵开慧

mysql性能调优

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

3315

扫码关注云+社区