单表千万行数据库 LIKE 搜索优化手记（转载）

我们经常在数据库中使用 LIKE 操作符来完成对数据的模糊搜索，LIKE 操作符用于在 WHERE 子句中搜索列中的指定模式。
如果需要查找客户表中所有姓氏是“张”的数据，可以使用下面的 SQL 语句：

SELECT * FROM Customer WHERE Name LIKE '张%'

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

SELECT * FROM Customer WHERE Phone LIKE '%123456'

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

SELECT * FROM Customer WHERE Name LIKE '%秀%'

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

 

数据概览

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

<图1>

 

左前缀匹配查询优化

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

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

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

<图2>

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

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

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

<图3>

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

 

右后缀匹配查询优化

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

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

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

<图4>

我们可以采用“以空间换时间”的方式来解决右后缀匹配查询时效率低下的问题。
简单来说，我们可以将字符串倒过来，让右后缀匹配变成左前缀匹配。以“防着古海回来再抓孙悟空”为例，将其倒置之后的字符串是“空悟孙抓再来回海古着防”。当需要查找结尾为“孙悟空”的数据时，去查找以“空悟孙”开头的数据即可。
具体做法是：在该表中增加“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 '空悟孙%'

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

<图5>

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

 

模糊查询优化

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

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

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

<图6>

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

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

 

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

以上两段让人摸不着头脑的文字来自百度百科，你可以和我一样选择忽略他。
我们不需要特别高超的分词技巧，因为汉语的特性，我们只需“二元”分词即可。
所谓二元分词，即将一段话中的文字每两个字符作为一个词来分词。还是以“防着古海回来再抓孙悟空”这句话为例，进行二元分词之后，得到的结果是：防着、着古、古海，海回，回来，来再，再抓，抓孙，孙悟，悟空。使用 C# 简单实现一下：

public static List<String> Cut(String str)
{
       var list = new List<String>();
       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;
}

测试一下结果：

<图7>

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

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 来做数据处理，对该工具感兴趣的朋友，可以参看笔者之前的文章：《.NET 程序员的 Playground ：LINQPad》，文章中对 LINQPad 做了一个简要的介绍，链接地址是：https://www.coderbusy.com/archives/432.html 。
我们需要先用 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)
             .Select(i => new { i.Id, i.Txt })
             .Take(limit)
             .ToList();
             if (items.Count == 0)
             {
                    break;
             }
             //逐条生产
             foreach (var item in items)
             {
                    maxId = item.Id;
                    //单个字的数据跳过
                    if (item.Txt.Length < 2)
                    {
                           continue;
                    }
                    var words = Cut(item.Txt);
                    wordList.AddRange(words.Select(str => new Tbl_like_word {  Rid = item.Id, Word = str }));
             }
       }
       "处理完毕，开始入库。".Dump("Log");
       this.BulkInsert(wordList);
       SaveChanges();
       "入库完成".Dump("Log");
}
// Define other methods, classes and namespaces here
public static List<String> Cut(String str)
{
       var list = new List<String>();
       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;
}

以上 LINQPad 脚本使用 Entity Framework Core 连接到了数据库，并引用了 NuGet 包“EFCore.BulkExtensions”来做数据批量插入。
之后，就可以把查询安排上，先查询倒排索引，然后关联到主表：

SELECT TOP 10 * FROM tbl_like WHERE id IN (
SELECT rid FROM tbl_like_word WHERE word IN ('悟空'))

查询速度很快，仅需十几毫秒：

<图8>

因为我们将所有的语句分成了二字符词组，所以当需要对单个字符模糊查询时，直接使用 LIKE 是一个更加经济的方案。如果需要查询的字符多于两个时，就需要对查询词进行分词。如需查询“东土大唐”一词，构造出的查询语句可能会是这样：

SELECT TOP 10*FROM tbl_like WHERE id IN (
SELECT rid FROM tbl_like_word WHERE word IN ('东土','土大','大唐'))

但是，该查询并不符合我们的预期，因为其将只包含“土大”的语句也筛选了出来：

<图9>

我们可以采取一些技巧来解决这个问题，比如先 GROUP 一下：

SELECT TOP
    10 *
FROM
    tbl_like
WHERE
    id IN (
    SELECT
        rid
    FROM
        tbl_like_word
    WHERE
        word IN ( '东土', '土大', '大唐' )
    GROUP BY
        rid
    HAVING
    COUNT ( DISTINCT ( word ) ) = 3
    )

在上述 SQL 语句中，我们对 rid 进行了分组，并筛选出了不重复的词组数量是三个（即我们的查询词数量）的。于是，我们可以得到正确的结果：

<图10>

由此可知：对于模糊查询，我们可以通过分词+倒排索引的方式优化查询速度。

 

后记
虽然在讲述时使用的是 SQL Server 数据库，但是以上优化经验对大部分关系型数据库来说是通用的，比如 MySQL、Oracle 等。
如果你和笔者一样在实际工作中使用 PostgreSQL 数据库，那么在做倒排索引时可以直接使用数组类型并配置 GiN 索引，以获得更好的开发和使用体验。需要注意的是，虽然 PostgreSQL 支持函数索引，但是如果对函数结果进行 LIKE 筛选时，索引并不会命中。
对于 SQLite 这种小型数据库，模糊搜索并不能使用到索引，所以左前缀搜索和右后缀搜索的优化方式对其不生效。不过，一般我们不会使用 SQLite 去存储大量的数据，尽管分词+倒排索引的优化方式也可以在 SQLite 中实现。

 

原文链接