分享人：Digoal 阿里云资深数据库专家

正文：本文从四方面介绍了MySQL具体内容。

Ÿ 全文检索

Ÿ 索引

Ÿ 模糊查询

Ÿ 相似文本

一、全文检索

首先介绍全文检索的原理，不管是在PG里面，还是在搜索引擎里面，很多产品是支持全国连锁的，全文检索核心用到的是：一个是分词，就是把文本转换成为文本特征向量。另一个是基于文本特征向量的索引，PG里面倒排索引、rom索引，这两个索引都可以用在全文检索里面。全文检索本质上是文本特征向量，是类似于数组一样的value，在文本特征向量的基础上，还支持向量出现的位置，比如“中国”这个词在一个文本当中，它出现过的位置，这是位置信息。

第二个是全文检索时会指定，比如这个词出现在标题里、在正文里、摘要或者其它地方，还有包含、不包、各种查询，从本质上来说一块是转换，另外一块是索引。全文检索里的中文分词，就会涉及到在某一些特殊行业里用这种通用的词组，可能就不能满足分词的要求。比如公司名，不管去选科技行业的字典，还是选通用互联网的字典，或者医疗行业的字典等，这种字典很多企业名称不能正确的被切分出来，因为企业名称不是大众词或行业词，这时就用到自定义的词组。在阿里云RDSPG里支持自定义词组，可以根据需求，把词组写入配置文件里，有了自定义词组后，就能在文本中出现自定义的词组，它就会正确的把这个词切出来。

Ÿ 首先是tsvector文本特征向量的类型，是把一个文本转换成一个特征向量，传承tsvector，就像数值类型、number类型一样，它是文本特征向量类型。

Ÿ 我们检索语句或者语法时，输入一个语法的时候，用到了第二个类型tsquery，tsquery听这个名字你就知道如何查，比如要查包含alibaba又含pg的词，在tsquery里就是alibaba中间是一个与的符号，表示包含alibaba同时包含pg，这就是查询语法，等会儿我们会有例子来就讲。

Ÿ 中文分词就是把文本里的中文正确的切出来，因为中文和西方语言不一样，西方语言的词本身都是空格隔开的很容易切出来，一个单词里有很多个字母，有空格隔开很容易切出来。它的词类，复数、单数、变种等，都有规则能够直接解析，所以西方语言的分词比较简单。中文分词的一句话除了标点符号，中间可能是一句很长的话，如何把这种长句的关键词提取出来呢？这时就得用中文分词的插件了，在pg里中文分词是常用的插件，包括pg_scws、pg_jieba、zhparser，这些都是中文分词的插件。

二、全文检索技术

1）什么是全文检索？

比如用户输入了一个字符串，这个字符串经过parser说词法解析，把它转换成token就会有词性。比如一句话里面包括中文、英文、阿拉伯数字网址、信息等，切词时会切成若干个token，每一个token会对应一种类型，比如是动词、形容词、介词等。每一种词性有对应的字典，比如中文字典、英文字典等，字典最核心的两列是：

第一个核心是是原始值，它跟token做匹配，比如中国，在中文字典里有中国两个字组成的原始值，token切出来有中国的话，就跟它匹配了。

第二个核心是token对应的特征向量，比如我们自己定义了一个分词的词组，比如刘德华，第一列就是原始值，右边定义成四大天王，张学友，右边我也定义成四大天王，如果一句话包含刘德华、张学友、黎明、郭富城，最后转换成特征向量是四大天王，在字典里匹配到这个词，然后转换成特征向量的value存下来，字典第二列是vector，接着配置词性，刚刚说的动词，形容词什么网址等那种词性，每一种词性都可以配一个或者是多个字典映射起来，把词性和字典建立起映射关系，字符串经过关系映射后每切出一个token，就会扫描对应的字典。如果有匹配就转换成vector、 token、vet。一个完整的文本字段的内容做完解析匹配后，就会存成包含多个vector的文本向量，意味着表里存的是tsvector或者其他字段，索引是倒排索引，可以用rom或者倒排。

倒排树的内容是每一个tsvector，对应包含tsvector，比如包含中国这个词的有哪些记录，记录的行号就会被存储在倒排树的叶子节点里面。比如搜索中国，在叶子节里会把返回表的行号告诉你，直接回表把行取出来就好了。那么它的语法也很简单，比如*from tbl where ts就是tsvector向量，@是操作符，特征向量要跟后面tsquery做匹配，同时包含pg双包含alibaba的词有哪些。

按照rank就是文本的相似度，包含pg又包含aibaba的rank去排序输出，这就是全文检索的技术。

2）词距离条件

在tsvector里很关键的是词的位置，比如电影《速度与激情》，我们做切词的时候，如果它不在字典里面的话，就会被切成两个词速度、激情。比如有一句话是速度与激情票房电影票房破百亿或破亿，这时速度出现在第二位，激情出现在第三位，实际这两个词之间的距离是一，因为中间是一个与。另一句话是某某出租车公司的工作很有激情，创新速度增长速度，这句话也出现了激情、速度，但是这两个词出现的位置，距离是三，中间还隔了三个词，很显然他肯定不是《速度与激情》这部电影。

所以除了要包含速度、激情这两个词，中间这两个词的距离是一，很显然在这两条记录里就会匹配到下面这条记录，上面这条记录就匹配不到。

3）按距离范围搜索

更高级一点还可以指定两个词之间的距离范围。比如两个词要求距离是2~5之间，就可以通过自定义的方法得到。

4）词距离条件

比如自定义get lexeme_pos_range这样一个函数，我们把ts输到这个函数里面，就能计算出这两个词之间的距离，做where条件筛选时要包含这两个词，距离要在落在2~5之间，就能够解决这个查询的需求。

5）内置ranking

rank内置的一些算法，比如搜索包括dark&matter的rank，包括dark以及matter这两个词的query， rank的分数越高就越相似。比如这句话里只有nico这个词的话， rank就最高，也可以理解为它是一个占比。

6）自定义ranking

我们也可以自定义rank算法，就不详细展开了。

举一个例子，比如有一个店铺标签表，店铺标签是每一个店铺会打的标签，每一个标签对应权重值或者评分。比如，有家 ktv也是一家足浴店，也提供小食，可能也算餐饮业，足浴个营业的99%，餐饮占百分之10%， ktv占45%，这时足浴0.99，餐饮就是0.1， ktv就是0.45。

比如搜索餐馆的时候，它也会被命中，但是由于它的分数很低，在做排序的时候就排到后面去，通过自定义rank的函数匹配它，还能够得到想要的目标记录，就不会把那些不是正式做餐饮的排在前面，把它排到后面去了。

7）ranking sort by index

用rank倒排索引的话，把前面where条件索引过滤出来的记录再去做显示排序，排序本身走不了索引，为了解决这个问题，我们有一个更高级的rum索引接口。首先CREAT EXTENSION创建索引的时候指定ops，就会用到rank这个索引，就不需要做强制排序了。

8）rds pg zhparser自定义分词

zhparser支持自定义分词，这里有自定义分词的使用方法。我们列出来简单的用法，首先是创建插件，后面两步是创建字典的mapping，把这些词mapping到配置文件里面去，这个配置文件parser对应的配置文件，查一下保障房资金压力，这个词切出来是保障、房、资金、压力。

假设我想做一个保障房资分词的话，就把它塞进去，塞进去后调用一下select zhprs_sync_dict_xdb，就是我们自定义这个分层生成到字典里面去，我们再对它做转换，会发现把保障房资转换出来了，它现在是一个自定义的分词。

9）关键词统计

通过ts_stat函数就可以实现，第一个参数是query，query的结果要求是vector文本特征向量的类型，select这个类型就好了，返回的结果去做统计，做统计输出的结果就是word，就是查到文本向量里面的lex或者叫向量值，向量值，word在多少篇文档里面出现过，在多少条记录里面出现过，然后它总共出现了多少次？它会返回这三个内容，拿到内容去order by，比如总共出现多少次倒排，我们就能知道这个word在多少篇文章里出现过，总共出现了多少次，就能知道热点词是什么了。

二、索引

1）gin索引结构

倒排索引存的是什么？向量数组、特征向量、数组都是多值类型，一个 value里面有好多个小的元素，比如数组，一个数组在数据库里面是一个字段，我们往里面塞一条记录，这条记录里就有好多个元素。倒排数存的是数组里面元素为k的构建出来的数，比如全文检索中国这个词，中国这个词可能出现叶子节点里面，出现了中国这个词有多少行，行号会存在post list或者post区里面。

搜索的时候搜中国，就会找到包含中国的有多少行？有哪些行？行号是什么？基于行号去表里面查询就好了，这个在数据库内部实现，不用拿到行号再去查，数据库在索引扫描的时候会自动完成。

2）rum索引结构

它能够支持的比倒排更高级的地方在哪里？上面是倒排索引的某一个key，对应的行号是point，比如中国这个词总共有五行包括了，这个时候除了要查询包括中国又order by了，比如说档有一个创建时间，我想把最新的文档排的更前，比如最近写的包括中国这个词的文章，倒排索引把五条全部返回，再order b个时间戳，再limit 1返回给你，它会回表。rum索引他要回表，它不用回表。每一行都有一个时间戳，五行包括中国的时间点也写进去，时间戳的字段也会存在这里，排序的时候就不用回表，直接在这里拿到，它的顺序也是在创建索引的时候可以指定的，存的时候就会直接按这个顺序来存。

举个例子，这个是order by rank的，也是用了rum索引，就不需要二次排序了。

我们把d字段attach，d是一个时间戳，把第一字段作为additional塞到了这里来。文本向量的字段的名字叫t，d是时间戳。我们索引是create index on table using rum索引的方法，然后t后面跟了一个add on，attach是d，把d的内容放进来，这时候直接走索引，不会查询后再走索引。我们在order by的时候持的也是这样的操作服务，就是你要你要输入一个时间，它越靠近这个时间越优先反馈给你，比如拿一个未来的值，它会取一个最新的给你。

3)rum-全文检索+附加属性查询

这个就是我们刚才讲的那个例子。

三、模糊查询

1）前缀模糊查询背景技术

在pg里做模糊查询是能走索引，比如包含前缀的模糊查询，还有包含后缀的模糊查询，那前缀和后缀都比较容易解决，因为低区索引可以直接用prefix大于、等于、小于来实现前缀查询。

比如在tbl字段上创建了一个索引，再where col like前缀百分号的话，它会走索引，或者是用尖括号加前缀也会走索引，就像下面这条SQL走了索引。

2）后缀模糊查询背景技术

接下来看后缀能不能走索引，比如select*from这张表，我们想要查以什么结尾的词，可以用数据库函数索引或者表达式索引来支持。表达式索引是reverse c1，我们来看创建索引的时候create index idx_pre1 on pre reverse c1，它会干一件什么事情呢？比如说你好abcd，它会把它倒过来，dcba，如果要查末尾包括cd的词，其实就会变成要查的是转换之后的头部包括dc的，其实就把它转成了前缀查询。

3）前后模糊查询背景技术

如果前缀和后缀都没有怎么办呢？在pg里面有倒排索引，比如词abcde，对不对？通过插件trgm把abcde转换成连续三个word组成的小token，在token上面构建倒排索引，当我们做like前后百分之查询的时候，也会把要查的词切成小token，做匹配后看一下在倒排数里是否有小token，做一遍过滤后再做第二遍过滤，相当于用索引帮你做了一次收敛。如果是包含中文的文本， lc type不能用c，创建database的时候，如果lc type选c的话，它就无法识别w是多字节字符。

小于三个的也就是包括两个字的前后百分号查询，或者包括一个字的前后百分号查询应该如何来处理？可以写一个自定义的函数，把每连续的两个以及每一个字给切掉。只有小于三个的时候用pg trgm插件没办法支持，因为插件在切词的时候是每连续三个字切出来的，所以办法支持比它力度还要细的匹配。

4）支持中文的前后模糊查询背景技术

关于中文CREAT language的时候，lc type不能用c，否则trgm插件就没有办法切分w，w是多字节字符。

四、相似文本查询

1）相似算法

全文检索和模糊查询用户不能百分之百的描述准确，比如没有记住完整的英单词或者记错了，这时想要查出来就要用相似查询。比如要识别一个图片或者一个苹果，苹果可能是绿色的、红色或者其它过渡颜色，能识别出来是因为它有相似。文本也一样有相似性，如何来计算两个文本之间的相似性呢？按照pg trgm插件做拆分。

举个例子，我们创建了一个插件 abcdefg，比如说我输错了，不是abc，我输成了bca，可能用户也记错了，下面是用户输入的，用like找不到，因为它错了，用分词也找不到，因为它也错了。

2）相似度算法

要在错的当中找到比较相似的，相似度实际上就是重叠度，比如abc和abcd之间的重叠度是多少？我们可以来算一下。

后面调换了几个字母，就会发现它的相似性是0.411765，这几个词的重叠度是41.11%， word下划线seminar跟它相似，只不过算法有点不一样。

3）相似查询

比如我们再输入一些，它的相似度又会下降，如果跟它一样的多起来，它的相似度又会上来，因为它不一样的地方占比是更少。

相似的计算也支持索引。个例子，首先创建一个随机生成的中文函数，生成100个随机汉字，它每次的生成的都不一样。

我们创建一些表、一些table、插件pg_trgm，创建一堆的纸表，每一个表里都写了一些记录，写入10亿条， 64张表每个分区写入这个1.5亿，每个分区写入15625000条记录总共插入了11条，插入后创建索引，已经把索引创建好了，再去写数据，用了trgm ops索引。

接下来做相似性的检索，做检索之前有一个function的调用，比如要找出跟要输入的字符串相似度大于90%的，我们就set limit，把它设成0.9，通过query info 百分号是一个操作符，百分是一个操作符，输入搜索词，通过这个方法就能找到相似度大于90%的，输入的词要把它切分成连续的每三个这样的token。
切分成一堆有意思的token，要达到90%的相似，按照公式反推，比如这里是十个，要达到90%相似，至少得有九个或者九个以上的要匹配。

扫完后就知道有没有匹配的，相似度没有达到直接就返回空了，不需要回表去搜索，只有相似度满足的时候才需要回表把你要的记录返回，所以相似度的查询切分完的token挨个去索引里面搜一遍，总共有多少个相似的？有多少个匹配到？除以总共输入了多少个字？多少个word，百分比就出来了，百分比达到了就回表去找一下，百分比没有达到就不回表了。回表是把每个命中的数据块的ID，所有命中的数据块做交集，也就是要超过八个以上的匹配，做完相交，只有当某一个数据库上面有且超过八个token都命中了同一个数据块，这样的数据块才需要去返回。有一些数据块，可能token命中，但不足八个命中个数的话，就不用返回了，也不用去找数据块里面的数据了。所以模糊查询效率是非常的高的，要回表的数据块是很少的。

比如最后达到的效果是10亿条记录，10亿条记录单次相似查询的搜索速度是40毫秒，在10亿里面找到相似的这样的结果，我们把相似查询做成了function，比如说我要搜大于80%，第三个输入就是0.8，然后递减先收100%的，再收90%的，然后收80%的，一直是没有收到就是说明没有你要的，有的话就会按照限制的条数来返回。