如何为带LIKE语句的查询强制执行合理的执行计划？

我太懂这种临时查询（ad-hoc queries）碰到的性能波动问题了！先理清楚你的场景：你有一张100万条记录的表，字段包括id（int）、createddatetime（timestamp）、category（varchar(50)）和content（varchar(max)），需要找出最近一天内content包含特定字符串的所有记录。你写的查询：

select * from table where createddatetime > '2018-1-31' and content like '%something%'

有时候能1秒跑完，但时不时就会卡很久对吧？下面给你几个实用的优化方案，亲测有效：

先抓核心问题：为什么会波动？ #

大概率是因为createddatetime没建索引，或者数据库执行计划跑偏了——当它先扫整个表做LIKE匹配再过滤时间时，速度就会爆炸；如果先通过时间筛选出小范围数据再做文本匹配，速度就快。所以优化的核心就是缩小文本查询的数据集，再解决文本匹配的效率问题。

具体优化方案 #

1. 给时间字段建索引，强制先过滤时间 #

首先给createddatetime加个非聚集索引：

CREATE NONCLUSTERED INDEX IX_table_createddatetime ON table(createddatetime);

有了这个索引，数据库能快速定位到最近一天的记录（百万级表里，最近一天的数据估计也就几万条顶天），再在这个小数据集里做模糊查询，速度会稳定很多。

如果担心执行计划还是不按套路来，可以用子查询强制先过滤时间：

SELECT *
FROM (
    SELECT * FROM table 
    WHERE createddatetime > DATEADD(day, -1, GETDATE()) -- 用动态日期代替固定值，不用每次改日期
) AS recent_records
WHERE content LIKE '%something%'

2. 用全文索引替代LIKE '%xxx%' #

LIKE带前缀通配符（%xxx）的查询是完全用不上普通索引的，对varchar(max)这种大文本字段来说，简直是性能杀手。如果你的数据库支持全文索引（比如SQL Server、MySQL、PostgreSQL都支持），这是最优解：

以SQL Server为例，步骤如下：

• 先创建全文目录：

  CREATE FULLTEXT CATALOG ft_table_content AS DEFAULT;
  

• 给content字段创建全文索引（需要用到表的主键，比如你的id字段）：

  CREATE FULLTEXT INDEX ON table(content)
  KEY INDEX PK_table_id; -- 这里替换成你表的主键索引名
  

• 之后查询就可以用全文搜索语法，结合时间条件：

  SELECT * FROM table
  WHERE createddatetime > DATEADD(day, -1, GETDATE())
  AND CONTAINS(content, 'something');
  

全文索引会对文本做分词处理，查询速度比LIKE快N倍，而且支持更复杂的文本搜索（比如同义词、短语匹配）。

3. 临时查询的应急方案：用临时表过渡 #

如果暂时没法建索引或全文索引，比如权限不够，可以先把最近一天的数据导到临时表，再在临时表上查：

-- 把最近一天的数据导入临时表
SELECT * INTO #temp_recent FROM table WHERE createddatetime > DATEADD(day, -1, GETDATE());
-- 在临时表上做模糊查询
SELECT * FROM #temp_recent WHERE content LIKE '%something%';
-- 用完删掉临时表
DROP TABLE #temp_recent;

临时表的数据量小，查询起来自然快很多。

4. 别用SELECT *，只查需要的字段 #

如果你的查询不需要所有字段，只选你实际要用的列，能减少数据传输和内存占用，速度也会有提升：

SELECT id, createddatetime, category, content -- 比如只查这几个字段
FROM table
WHERE createddatetime > DATEADD(day, -1, GETDATE())
AND CONTAINS(content, 'something');

额外小技巧 #

每次碰到性能问题，先看执行计划！看看有没有出现表扫描或聚集索引扫描——如果有，说明索引没生效，要么是索引建错了，要么是查询语句需要调整。

内容的提问来源于stack exchange，提问作者Dan Roberts