【每日一记11】单表千万行数据库 LIKE 搜索优化手记

    单表千万行数据库 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 的数据表，表中包含了四大名著中的全部语句，数据条数上千万：

左前缀匹配查询优化如果要查询所有以“孙悟空”开头的句子，可以使用下面的 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 秒：

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

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

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”列增加了联合索引。这就是我们的倒排表，接下来就是为其填充数据。

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

    在实际工作中使用 PostgreSQL 数据库，那么在做倒排索引时可以直接使用数组类型并配置 GiN 索引，以获得更好的开发和使用体验。需要注意的是，虽然 PostgreSQL 支持函数索引，但是如果对函数结果进行 LIKE 筛选时，索引并不会命中。

   对于 SQLite 这种小型数据库，模糊搜索并不能使用到索引，所以左前缀搜索和右后缀搜索的优化方式对其不生效。不过，一般我们不会使用 SQLite 去存储大量的数据，尽管分词+倒排索引的优化方式也可以在 SQLite 中实现。

每天学习一点点，每天进步一点点。

每天学习一点点，每天进步一点点。

每天学习一点点，每天进步一点点。

每天学习一点点，每天进步一点点。

每天学习一点点，每天进步一点点。

每天学习一点点，每天进步一点点。

每天学习一点点，每天进步一点点。

每天学习一点点，每天进步一点点。

每天学习一点点，每天进步一点点。