分享人：Digoal，阿里云数据库产品经理

正文：

本篇内容将从6个部分为读者介绍在数据库中跑全文检索、模糊查询SQL会不会被开除，应该采用什么方案才能解决问题，并且从中学会如何保护自己和公司业务。

Ÿ 为什么要讨论这个问题？到底会不会被开除?

Ÿ 问题在哪？原因是什么？怎么办？

Ÿ 搜来的方案靠谱吗?

Ÿ 什么才是经过思考的牛逼方案？牛逼的方案就没漏洞吗?

Ÿ 更牛逼的方案是什么?

Ÿ 如何学会保护自己和公司业务？

 

一、为什么要讨论这个问题？到底会不会被开除?

在数据库中跑全文检索、模糊查询SQL会不会被开除这个话题非常值得探讨，因为这关系到我们的个人职业发展。那这么干到底会不会被开除呢？我们先来看几份数据：100万条记录(1.1GB) — 300毫秒；1000万条记录(11GB) — 3500毫秒；1亿条记录(110GB) — 85秒；32 Core 的数据库, 32个并发足以引起数据库雪崩。一旦引起数据雪崩势必会造成业务上的损失，那被开除的几率就相当大了。

二、问题在哪？原因是什么？怎么办？

问题出在没有创建索引。原因是DBA不创建索引吗？但是DBA表示没有哪个数据库的索引支持模糊查询。那这时候该怎么办呢？第一个方法就是多创建几个只读实例, 顶多把只读实例搞崩溃；第二就是砍需求、定数据库规范.，不允许在数据库中执行模糊查询、全文检索；第三是DBA增加创建索引这方面的需求。

三、搜来的方案靠谱吗?

当我们自己处理不了的时候会在网上寻求解决方案，搜索到方案一般是“需要搜索的数据, 一份写入关系数据库， 再同步一份同步到搜索引擎”。这个方案很容易搜索出来，也是比较流行的一种解决方案。但是这个方案靠谱吗?答案显然是不靠谱的，其中有几个痛点：同步延迟，数据写入后无法实时被搜索到.；跨产品同步引入的一致性问题，每天刷一遍全量再继续增量同步。一致性与查询时延要求高的场景用这个方案显然不行。

四、什么才是经过思考的牛逼方案？牛逼的方案就没漏洞吗?

我们需要思考一个相对完美的解决方案，首先肯定是要解决延迟问题与一致性问题。

如果要设计一款数据库索引来支持全文检索、模糊查询甚至正则表达式，应该如何设计？什么指标是重要的？索引构建的实时性；查询的实时性；查询性能；内容写入、变更性能；可以按相似度排序返回；全文检索：分词正确性， (全文检索不讨论，PG已经内置，安装各国语言插件可以实现索引加速，同时支持扩展字典)；全文检索：字典可自定义。

向大家隆重推出这个数据库PG，它有倒排索引接口GIN，能够跟搜索引擎一样支持多值类型的多数索引的构建。以及pg_trgm模块，有了这样的模块之后，我们就能够在数据库去支持上述说的需求。那么我们简单看一下它的原理：

 

为了支持模糊查询，利用了pg_trgm模块。对于要查的这个词前面加两个空格，后面加一个空格，然后把它切成三个连续的小token，然后用这个小的token再到这个树里面去搜索来去匹配需要的记录。

 

 

那么这种方案也会出现以下几个问题的。

1．pg_trgm采用3-grams切分粒度，1个或2个字的模糊查询性能很差；

2．当匹配结果非常非常多时， 即时LIMIT返回依旧有较大启动成本. bitmap scan造成；

3．在开启fastupdate的情况下， 优先将数据写入pending list, autovacuum异步合并到gin树。 查询时需要查询pengding list以及gin索引树，会导致搜索性能降低。特别是在大量数据高并发写入后，全文检索、模糊查询的性能都会下降，pending list合并到gin树后性能恢复。

4．lc_ctyp=C时无法切分wchar，例如中文模糊查询，千万要注意。

五、更牛逼的方案是什么?

与pg_trgm相比，更厉害的是MyBase PG pg_bigm，利用它就能解决上述问题。详见下面的表格，仔细比较了pg_bigm与pg_trgm。

 

pg_bigm 采用2-grams粒度切词， 从功能上讲， 比pg_trgm优势明显，支持高性能1或2个字的模糊和相似搜索。同时增加了非严谨查询的开关，在某些特定场合或者用户为性能可以妥协一定精准度时， 好处多多。同时对wchar友好， 任何lc_ctype的数据库都能支持wchar切词， pg_trgm则需要修改头文件或者使用lc_cypte<>C的数据库来支持wchar的切词以及wchar的模糊查询 , 相似查询。

 

六、如何学会保护自己和公司业务？

1、防止雪崩— 前端保护, 避免重复请求：防止在后端响应慢时，前端用户不断点击， 导致雪崩；

2、防止雪崩—降级、疏导：设置语句超时， 避免慢SQL击破数据库资源；

3、规范：规范相关的行为准则；

4、有强大的后盾：阿里云AliPG， 全兼容PostgreSQL,，2015年投入商用是数万用户的坚强后盾。不仅仅是PG内核代码， 还有插件代码，MyBase PG已集成pg_trgm, pg_bigm等高级模块。