mysql 1千万 like优化_单表千万行数据库 LIKE 搜索优化手记

我们经常在数据库中使用 LIKE 操作符来完成对数据的模糊搜索，LIKE 操作符用于在 WHERE 子句中搜索列中的指定模式。

如果需要查找客户表中所有姓氏是“张”的数据，可以使用下面的 SQL 语句：

SELECT *FROM CustomerWHERE NameLIKE '张%'

如果需要查找客户表中所有手机尾号是“1234”的数据，可以使用下面的 SQL 语句：

SELECT *FROM CustomerWHERE PhoneLIKE '%123456'

如果需要查找客户表中所有名字中包含“秀”的数据，可以使用下面的 SQL 语句：

SELECT *FROM CustomerWHERE NameLIKE '%秀%'

以上三种分别对应了：左前缀匹配、右后缀匹配和模糊查询，并且对应了不同的查询优化方式。

数据概览

现在有一张名为 tbl_like 的数据表，表中包含了四大名著中的全部语句，数据条数上千万：

左前缀匹配查询优化

如果要查询所有以“孙悟空”开头的句子，可以使用下面的 SQL 语句：

SELECT *FROM tbl_like WHERE txt LIKE '孙悟空%'

SQL Server 数据库比较强大，耗时八百多毫秒，并不算快：

我们可以在 txt 列上建立索引，用于优化该查询：

CREATE INDEX tbl_like_txt_idx ON [tbl_like]([txt])

应用索引后，查询速度大大加快，仅需 5 毫秒：

由此可知：对于左前缀匹配，我们可以通过增加索引的方式来加快查询速度。

右后缀匹配查询优化

在右后缀匹配查询中，上述索引对右后缀匹配并不生效。使用以下 SQL 语句查询所有以“孙悟空”结尾的数据：

SELECT *FROM tbl_like WHERE txt LIKE '%孙悟空'

效率十分低下，耗时达到了 2.5秒：

我们可以采用“以空间换时间”的方式来解决右后缀匹配查询时效率低下的问题。

简单来说，我们可以将字符串倒过来，让右后缀匹配变成左前缀匹配。以“防着古海回来再抓孙悟空”为例，将其倒置之后的字符串是“空悟孙抓再来回海古着防”。当需要查找结尾为“孙悟空”的数据时，去查找以“空悟孙”开头的数据即可。

具体做法是：在该表中增加“txt_back”列，将“txt”列的值倒置后，填入“txt_back”列中，最后为 “txt_back”列增加索引。

ALTER TABLE tbl_like ADD txt_back nvarchar(1000);--增加数据列UPDATE tbl_like SET txt_back =reverse(txt);--填充txt_back 的值CREATE INDEX tbl_like_txt_back_idx ON [tbl_like]([txt_back]);--为txt_back 列增加索引

数据表调整之后，我们的 SQL 语句也需要调整：

SELECT *FROM tbl_like WHERE txt_back LIKE '空悟孙%'

此番操作下来，执行速度就非常迅速了：

由此可知：对于右后缀匹配，我们可以建立倒序字段将右后缀匹配变成左前缀匹配来加快查询速度。

模糊查询优化

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

SELECT *FROM tbl_like WHERE txt LIKE '%悟空%'

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

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

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

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

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

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

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

publicstaticListCut(Stringstr){varlist =newList();varbuffer =newChar[2];for(inti =0;i

测试一下结果：

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

CREATE TABLE tbl_like_word ([id]intidentity,[rid]intNOT NULL,[word]nchar(2)NOT NULL,PRIMARY KEY CLUSTERED ([id]));CREATE INDEX tbl_like_word_word_idx ON tbl_like_word(word,rid);--覆盖索引(Coveringindex)

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

为了便于演示，笔者使用了 LINQPad 来做数据处理，对该工具感兴趣的朋友，可以参看笔者之前的文章：《.NET 程序员的 Playground ：LINQPad》，文章中对 LINQPad 做了一个简要的介绍，链接地址是：https://www.coderbusy.com/archives/432.html 。

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

voidMain(){varmaxId =0;constintlimit =3000;varwordList =newList();while(true){$"开始处理：{maxId} 之后 {limit} 条".Dump("Log");//分批次读取varitems =Tbl_likes.Where(i =>i.Id>maxId).OrderBy(i =>i.Id).Select(i =>new{i.Id,i.Txt}).Take(limit).ToList();if(items.Count==0){break;}//逐条生产foreach(varitem initems){maxId =item.Id;//单个字的数据跳过if(item.Txt.Length<2){continue;}varwords =Cut(item.Txt);wordList.AddRange(words.Select(str =>newTbl_like_word{Rid=item.Id,Word=str }));}}"处理完毕，开始入库。".Dump("Log");this.BulkInsert(wordList);SaveChanges();"入库完成".Dump("Log");}// Define other methods, classes and namespaces herepublic