牛！单表千万行数据库： LIKE 搜索优化手记

最新推荐文章于 2024-07-29 04:01:43 发布

JavaScript高级

最新推荐文章于 2024-07-29 04:01:43 发布

阅读量1.1k

点赞数 20

分类专栏： Java程序员文章标签：数据库 c# 开发语言

本文链接：https://blog.csdn.net/m0_60388419/article/details/136818565

版权

Java程序员专栏收录该内容

8 篇文章 0 订阅

订阅专栏

模糊查询优化

在查询所有包含“悟空”的语句时，我们使用以下的 SQL 语句：

SELECT * FROM tbl_like WHERE txt LIKE ‘%悟空%’

该语句无法利用到索引，所以查询非常慢，需要 2.7 秒：

遗憾的是，我们并没有一个简单的办法可以优化这个查询。但没有简单的办法，并不代表没有办法。解决办法之一就是：分词+倒排索引。

分词就是将连续的字序列按照一定的规范重新组合成词序列的过程。我们知道，在英文的行文中，单词之间是以空格作为自然分界符的，而中文只是字、句和段能通过明显的分界符来简单划界，唯独词没有一个形式上的分界符，虽然英文也同样存在短语的划分问题，不过在词这一层上，中文比之英文要复杂得多、困难得多。

倒排索引源于实际应用中需要根据属性的值来查找记录。这种索引表中的每一项都包括一个属性值和具有该属性值的各记录的地址。由于不是由记录来确定属性值，而是由属性值来确定记录的位置，因而称为倒排索引(inverted index)。带有倒排索引的文件我们称为倒排索引文件，简称倒排文件(inverted file)。

以上两段让人摸不着头脑的文字来自百度百科，你可以和我一样选择忽略他。

我们不需要特别高超的分词技巧，因为汉语的特性，我们只需“二元”分词即可。

所谓二元分词，即将一段话中的文字每两个字符作为一个词来分词。还是以“防着古海回来再抓孙悟空”这句话为例，进行二元分词之后，得到的结果是：防着、着古、古海，海回，回来，来再，再抓，抓孙，孙悟，悟空。使用 C# 简单实现一下：

public static List Cut(String str)

{

var list = new List();

var buffer = new Char[2];

for (int i = 0; i < str.Length - 1; i++)

{

buffer[0] = str[i];

buffer[1] = str[i + 1];

list.Add(new String(buffer));

}

return list;

}

测试一下结果：

我们需要一张数据表，把分词后的词条和原始数据对应起来，为了获得更好的效率，我们还用到了覆盖索引：

CREATE TABLE tbl_like_word (

[id] int identity,

[rid] int NOT NULL,

[word] nchar(2) NOT NULL,

PRIMARY KEY CLUSTERED ([id])

);

CREATE INDEX tbl_like_word_word_idx ON tbl_like_word(word,rid);-- 覆盖索引（Covering index）

以上 SQL 语句创建了一张名为 ”tbl_like_word“的数据表，并为其 ”word“和“rid”列增加了联合索引。这就是我们的倒排表，接下来就是为其填充数据。

我们需要先用 LINQPad 自带的数据库链接功能链接至数据库，之后就可以在 LINQPad 中与数据库交互了。首先按 Id 顺序每 3000 条一批读取 tbl_like 表中的数据，对 txt 字段的值分词后生成 tbl_like_word 所需的数据，之后将数据批量入库。完整的 LINQPad 代码如下：

void Main()

{

var maxId = 0;

const int limit = 3000;

var wordList = new List<Tbl_like_word>();

while (true)

{

$“开始处理：{maxId} 之后 {limit} 条”.Dump(“Log”);

//分批次读取

var items = Tbl_likes

.Where(i => i.Id > maxId)

.OrderBy(i => i.Id)