大家好，欢迎来到IT知识分享网。
活动要写在最前面，因为今天的干货文章实在太长了！：）提醒超过20000字了。只好删减一些，详细阅读请看云栖社区头条。
云栖社区也有”小金人“了！送给3月云栖社区2016年第2-10期在线培训的8位CTO大神。目前，3月4日10：00-10:40，第2期《游族网络：如何运维千台以上游戏云服务器》正在火热报名。转发”小金人“到朋友圈，并在直播期间积极留言，我们将挑选优秀留言提问者赠送技术书籍哦！
![大数据最大难关之模糊检索，PostgreSQL如何攻克！[通俗易懂]](https://yundeesoft.com/wp-content/uploads/2022/11/2022112316405970.jpg "大数据最大难关之模糊检索，PostgreSQL如何攻克！[通俗易懂]插图1")
正文来了！感谢德哥的分享。重要的事情重复三遍：技术干货，技术干货，技术干货！
作者：阿里云数据库专家 德哥
来源：云栖社区
链接：https://yq.aliyun.com/articles/7444
转载分享请带上述版权声明！
大数据正在向我们奔来。尽管业务场景不会完全相同，但在其中一个最典型场景——模糊检索中，技术需求却出奇的一致。
比如说：
物联网，往往会产生大量的数据，除了数字数据，还有字符串类的数据，例如条形码，车牌，手机号，邮箱，姓名等。假设用户需要在大量的传感数据中进行模糊检索，甚至规则表达式匹配，有什么高效的方法呢？
医药，市面上发现了一批药品可能有问题，需要对药品条码进行规则表达式查找，找出复合条件的药品流向。但怎么才能在如此复杂的系统中，用高效方法来实现？
公安，侦查行动时，有可能需要线索的检索。如用户提供的残缺的电话号码，邮箱，车牌，IP地址，QQ号码，微信号码等进行交叉搜索，根据这些信息加上时间的叠加，模糊匹配和关联，最终找出罪犯。但这个流程，可有高效方法？
相同的需求还有很多。几乎每一个模糊匹配的场景下，都需要正则表达式匹配，这和人脸拼图有点类似，我们已经看到强烈的需求已经产生。但技术方面，要怎么做更好？
在我看来：正则匹配和模糊匹配通常是搜索引擎的特长，但是如果你使用的是PostgreSQL数据库照样能实现，并且性能不赖，加上分布式方案 (譬如 plproxy, pg_shard, fdw shard, pg-xc, pg-xl, greenplum)，处理百亿以上数据量的正则匹配和模糊匹配效果杠杠的，同时还不失数据库固有的功能，绝对是一举多得。
首先对应用场景进行一下分类，以及现有技术下能使用的优化手段。
.1. 带前缀的模糊查询，例如 like ‘ABC%’，在PG中也可以写成 ~ ‘^ABC’
可以使用btree索引优化，或者拆列用多列索引叠加bit and或bit or进行优化（只适合固定长度的端字符串，例如char(8)）。
.2. 带后缀的模糊查询，例如 like ‘%ABC’，在PG中也可以写成 ~ ‘ABC
可以使用reverse函数btree索引，或者拆列用多列索引叠加bit and或bit or进行优化（只适合固定长度的端字符串，例如char(8)）。
.3. 不带前缀和后缀的模糊查询，例如 like ‘%AB_C%’，在PG中也可以写成 ~ ‘AB.C’
可以使用pg_trgm的gin索引，或者拆列用多列索引叠加bit and或bit or进行优化（只适合固定长度的端字符串，例如char(8)）。
.4. 正则表达式查询，例如 ~ ‘[\d]+def1.?[a|b|0|8]{1,3}’
可以使用pg_trgm的gin索引，或者拆列用多列索引叠加bit and或bit or进行优化（只适合固定长度的端字符串，例如char(8)）。
PostgreSQL pg_trgm插件自从9.1开始支持模糊查询使用索引，从9.3开始支持规则表达式查询使用索引，大大提高了PostgreSQL在刑侦方面的能力。
代码见 https://github.com/postgrespro/pg_trgm_pro
pg_trgm插件的原理，将字符串前加2个空格，后加1个空格，组成一个新的字符串，并将这个新的字符串按照每3个相邻的字符拆分成多个token。
当使用规则表达式或者模糊查询进行匹配时，会检索出他们的近似度，再进行filter。
GIN索引的图例：
从btree检索到匹配的token时，指向对应的list, 从list中存储的ctid找到对应的记录。
因为一个字符串会拆成很多个token，所以没插入一条记录，会更新多条索引，这也是GIN索引需要fastupdate的原因。
正则匹配是怎么做到的呢？
详见 https://raw.githubusercontent.com/postgrespro/pg_trgm_pro/master/trgm_regexp.c
实际上它是将正则表达式转换成了NFA格式，然后扫描多个TOKEN，进行bit and|or匹配。
正则组合如果转换出来的的bit and|or很多的话，就需要大量的recheck，性能也不能好到哪里去。
下面针对以上四种场景，实例讲解如何优化。
-
带前缀的模糊查询，例如 like ‘ABC%’，在PG中也可以写成 ~ ‘^ABC’
可以使用btree索引优化，或者拆列用多列索引叠加bit and或bit or进行优化（只适合固定长度的端字符串，例如char(8)）。
例子，1000万随机产生的MD5数据的前8个字符。
带前缀的模糊查询，不使用索引需要5483毫秒。
带前缀的模糊查询，使用索引只需要0.5毫秒。
.2. 带后缀的模糊查询，例如 like ‘%ABC’，在PG中也可以写成 ~ ‘ABC
免责声明：本站所有文章内容,图片，视频等均是来源于用户投稿和互联网及文摘转载整编而成，不代表本站观点，不承担相关法律责任。其著作权各归其原作者或其出版社所有。如发现本站有涉嫌抄袭侵权/违法违规的内容,侵犯到您的权益，请在线联系站长,一经查实,本站将立刻删除。 本文来自网络,若有侵权，请联系删除，如若转载，请注明出处：https://yundeesoft.com/6549.html
