digoal
2016-11-18
postgresql , 正则匹配 , 分词 , 双十一 , rum , trgm , 全文索引 , 搜索引擎 , tsvector , tsquery
<a href="https://yq.aliyun.com/articles/57857">《聊一聊双十一背后的技术 - 物流, 动态路径规划》</a>
<a href="https://yq.aliyun.com/articles/64240">《聊一聊双十一背后的技术 - 分词和搜索》</a>
<a href="https://yq.aliyun.com/articles/64351">《聊一聊双十一背后的技术 - 强奸式秒杀技术实现》</a>
<a href="https://yq.aliyun.com/articles/64670">《聊一聊双十一背后的技术 - 毫秒分词算啥, 试试正则和相似度》</a>
<a href="https://yq.aliyun.com/roundtable/44043">云栖聚能聊 - 聊一聊双十一背后的数据库技术</a>
看刑侦剧经常有看到人物拼图，然后到图库搜索的，以前可能靠的是人肉，今天，使用pg，可以靠数据库的图形近似度搜索功能。
<a href="https://yq.aliyun.com/articles/58246">《弱水三千,只取一瓢,当图像搜索遇见postgresql (haar wavelet)》</a>
但是我今天不是写图形搜索，我要写的是文本搜索，文本搜索，大家一定会想到分词。
千万不要以为分词可以搞定一切需求，比如这样的需求就搞不定。
hello world打成了hello word或者hello w0rld，你要让数据库匹配出来，怎么搞？
又或者你的业务需要写正则进行匹配，怎么搞？比如一些域名的查询，www.firefoxcn.org可能你只想输入其中的一个部分来搜索，如果firefox可以匹配。
甚至更变态的 fi[a-z]{1}e.*?.?? ，这样的查询。
数据量小，并发小时，这种查询是可以忍受全表扫描和cpu处理过滤的。
但是想想一下，你是一个日请求过亿的业务，或者数据量亿级别的，全表扫描和cpu的开销会让你疯掉的。
那么来看看postgresql是如何做到的吧。
pg就像机器猫的百宝箱，里面什么都能找到，一起来召唤。
在pg_trgm的代码注释中有详细的介绍，分为4个步骤。
1. 使用postgresql regexp库，将正则转换为nfa样式（图形化词组）。
2. 将nfa样式再进行转换，转换为扩展的图形样式(trigrams)，包括拆分后的查询词组与not词组。
3. 简化，过滤不必要的trigrams。
4. 打包为trgmpackedgraph结构，支持gin,gist索引的检索。
新建一张测试表，其中包含一个字符串类型字段，插入5000万随机字符串，要来就来大数据量，数据库可不是玩具。
创建支持正则的索引
正则查询例子，刚给一位搞其他数据库产品的同学演示过，被查询速度惊吓到，直接给跪了。
模糊查询例子(其实正则已经包含了模糊查询，这里只是再展示一下)
以上例子若全表扫描的耗时(本例未使用pg支持的cpu并行计算)
由于这里gin索引是对token做的，一般来说，如果字符串越长，token就越多，如果索引字段会更新的话，需要优化一下gin的异步合并，提升更新的性能。
相似度，其实是指输错了还能匹配，就好比人物拼图，或者回忆录，你总不能每个细节都记得清楚吧，所以相似度匹配的功能就展露头角了。
<a href="https://yq.aliyun.com/articles/59212">《postgresql 文本数据分析实践之 - 相似度分析》</a>
<a href="https://www.postgresql.org/docs/9.6/static/sql-createindex.html">https://www.postgresql.org/docs/9.6/static/sql-createindex.html</a>