在这篇文章中，我将对应用程序开发中一个非常常见的模式进行简要介绍：全文索引和搜索。 我首先会介绍一下动机，是什么促使我使用CockroachDB来探索这个问题。 接下来，我将介绍最初通过的解决方案，然后深入解释是如何做到的，然后我将通过添加 "分数 "来改进这个结果。 最后，我将在信息检索的背景下讨论这种简单化方法的局限性，最后以我对 "那么，你为什么要这样做？"的回答结束。 让我们开始吧。
实验：用CockroachDB建立全文索引
昨天，在对CockroachDB中的 "追随者阅读 "的想法产生兴趣后，我使用谷歌的搜索来帮助我找到关于这个主题的材料。 在排名的搜索结果中，如下图所示，请注意顺序："追随者阅读...... "的是第一个（在广告之后），然后是一篇博文，最后是 "追随者阅读拓扑学 "开头的。
很自然地，我的思绪飘了起来，我猜测我可能会尝试使用我搜索的主题技术来实现文本搜索功能。CockroachDB。正如我们所知，CockroachDB是一个水平可扩展的、地理分布的、符合ACID标准的数据库，为OLTP工作负载进行了优化；但我还是很想知道它如何处理这个分析用例。嗯，事实证明，这是一个可操作的问题，如果你跳过建立漂亮的用户界面和其他设施，那就更容易了。 下面的图片描述了我在这个领域的初步尝试。 这是我的DbVisualizer 窗口的截图，我在那里进行了相当于上面所示的谷歌搜索，尽管这里我的数据集仅限于CockroachDB的20.2版本的文档。 我发现令人鼓舞的是：（1）前两个结果与谷歌搜索的结果一致；（2）查询在40毫秒内返回结果。
我也许应该解释一下DbVisualizer窗口内发生了什么。 总的来说，这是一个SQL查询，它混合了两个独立的查询结果，使用一个通用表表达式（CTE），最终产生了一个排序的搜索结果列表。 在顶部的查询中，第2-4行，目标是找到 "docs "表中内容列包含给定三个术语的行。"crdb_docs"，"follow"，和 "read"。 这是通过使用"@>"操作符实现的，它确保查询优化器选择内容列上的倒置索引（又称通用倒置索引，或 "GIN"），从而加快了速度。 就其本身而言，这个初始查询（这里标为 "d"）提供了一个无序的搜索结果列表。 不算太差。 下面是这个初始查询的样子。
SELECT idx_name, uri, n_words
FROM docs
WHERE content @> '{crdb_docs, follow, read}';
让我倒退一下。 为了达到这一点，我必须建立一个本地的CockroachDB集群，创建两个表，为这些表中的某些列建立索引，然后将我的HTML数据载入其中。 这里有一个关于如何启动CockroachDB集群的链接，我也会把我的DDL放在这里。
CREATE TABLE docs
(
idx_name TEXT
, uri TEXT
, content TEXT[]
, n_words INT
, PRIMARY KEY (idx_name, uri)
);
CREATE TABLE words
(
idx_name TEXT
, uri TEXT
, word TEXT
, cnt INT
);
-- Secondary indexes
CREATE INDEX ON docs USING GIN(content);
CREATE INDEX ON words (idx_name, word) STORING (cnt);
尽管我对这些仅基于 "d "查询的初步结果感到兴奋，但这种新鲜感很快就消失了。 这里面缺少某种排名机制，将结果按相关度降序排列。 这就是标记为 "w "的查询的作用。 这个查询是针对一个 "单词 "表进行的，该表跟踪每个单词在每个文档中出现的总数（以及在每个 "索引 "中，因为我预计有多个索引）。 这个查询通过结合SUM 和GROUP BY ，计算出一个总量。 在第11行，来自两个查询的元素被结合起来，为每个搜索结果生成一个标准化的分数。 这就是CTE的用武之地，因为它使我能够在查询 "w "中计算聚合，并在以后的分数计算中引用它。 最后，在第13行，搜索结果按分数从高到低进行排序。 这是最后的查询，以CTE为特色。
WITH d AS (
SELECT idx_name, uri, n_words
FROM docs
WHERE content @> '{crdb_docs, follow, read}'
), w AS (
SELECT idx_name, uri, SUM(cnt) n
FROM words
WHERE idx_name = 'crdb_docs' AND word IN ('follow', 'read')
GROUP BY (idx_name, uri)
)
SELECT w.uri, (100.0 * n/n_words)::NUMERIC(9, 3) score FROM w
JOIN d ON d.idx_name = w.idx_name AND d.uri = w.uri
ORDER BY score DESC
LIMIT 12;
现在是不可避免的 "限制和免责声明"。 如果你看一下这个小实验的GitHub仓库中的代码，你会注意到这是一个相当简单的全文索引的方法。 这有点像信息检索的经典 "词包 "方法。 而且，它还做了词根处理和案例折叠（你可能已经注意到SQL查询中的术语 "跟随 "和 "阅读"）。 尽管这段代码确实包含了 "术语频率"（TF），但它忽略了与之密切相关的 "反文档频率"（IDF）的概念。 缓解这一问题的方法是使用一个 "停止词 "列表来帮助识别和删除经常遇到的、对文本的意义贡献不大的词（"the"、"a"、"an "等）。 由于我希望保持词汇总量的合理性，我使用了一个英语单词列表，并且只索引出现在这个列表中的术语。 这样做的影响是，搜索 "CDC "会得到零个结果，尽管搜索 "变化数据采集"会很顺利。 另一个缺点是，每个词的 "术语偏移量 "没有被存储，所以不可能进行短语查询。 哦，搜索结果的高亮和/或列表，向用户显示在搜索中触发 "命中 "的文件的上下文 -- 这里也没有。 最后一点是，这只限于英文文本，尽管不难想象它可以扩展到处理其他语言。
建立一个成功的全文索引和搜索引擎是一个相当大的任务。 我过去曾使用过Elasticsearch（ES）和Solr，以及Lucene，它构成了ES和Solr的基础。 使用ES或Solr，你可以得到一个文本索引和搜索体验，它提供了我上面提到的所有内容，还有更多。 如果你的目标是搜索，我强烈推荐它们；你甚至可以使用CockroachDB的变化数据捕获（CDC）功能来设置变化源，这将使你的Elasticsearch或Solr索引与CockroachDB表保持同步。
为什么要在CockroachDB中建立全文索引？
说了这么多，不可避免的问题是："那么，你为什么要这样做？" 两个原因：（1）我喜欢文本搜索；（2）我刚刚完成了一些地理空间数据的实验，其中使用了GIN索引，所以我想再次使用它们。 另外，当你手中有一个像CockroachDB这样令人惊奇的云原生分布式SQL数据库时，你会很想伸展一下，看看它在应用于一些更小众的数据问题时表现如何。 我想我将无法抗拒对它的进一步实验，我将随着我的工作更新GitHub repo。 请随时分叉或克隆它，以满足你自己对文本搜索的渴望。 最后，非常感谢你在这个简短的文本搜索主题中与我一起坚持。 我希望你喜欢它。
