使用全文索引替代LIKE ‘%关键词%’查询可显著提升性能，因其基于倒排索引实现高效检索，避免全表扫描，同时支持词干提取、同义词、相关性排序等高级功能，适用于SQL Server、MySQL和PostgreSQL等主流数据库。
优化SQL中的
LIKE
查询，尤其是当通配符
%
出现在搜索模式开头时（例如
LIKE '%关键词%'
），最有效且推荐的方法是使用全文索引（Full-Text Index）来替代传统的通配符查询。这种方法能显著提升查询性能，将原本可能导致全表扫描的低效操作，转变为基于倒排索引的高效文本检索。
当我们谈到SQL查询优化，
LIKE
操作符，特别是那种通配符
%
放在开头的模式，比如
LIKE '%关键词%'
，几乎是性能杀手。它通常会强制数据库进行全表扫描，即使有索引也可能无法有效利用。我的经验告诉我，很多时候，这种模糊查询的需求，其实更适合用全文索引（Full-Text Index）来解决。
全文索引的工作原理与普通B树索引完全不同。它不是按字段值的顺序存储，而是将文本内容拆分成独立的词（token），并记录每个词出现在哪个文档（行）的哪个位置。这就像书的末尾的索引，你能快速找到某个词在哪几页出现过。
实现上，不同数据库系统有各自的全文索引方案：
SQL Server: 需要安装Full-Text Search组件，然后创建全文目录（Full-Text Catalog）和全文索引。
-- 假设我们有一个Products表，想对ProductName字段进行全文搜索-- 1. 创建全文目录 (如果还没有)CREATE FULLTEXT CATALOG ProductCatalog AS DEFAULT;-- 2. 创建全文索引CREATE FULLTEXT INDEX ON Products(ProductName LANGUAGE 'Chinese')KEY INDEX PK_Products -- 你的表主键索引名，替换成实际的主键索引名ON ProductCatalogWITH CHANGE_TRACKING AUTO;
查询时，使用
CONTAINS
或
FREETEXT
：
-- 替换 LIKE '%手机%'SELECT ProductID, ProductNameFROM ProductsWHERE CONTAINS(ProductName, '手机');-- 多个关键词，类似 AND 关系SELECT ProductID, ProductNameFROM ProductsWHERE CONTAINS(ProductName, ' "智能" AND "手机" ');
MySQL (InnoDB): 从MySQL 5.6开始，InnoDB存储引擎支持全文索引。
-- 1. 创建全文索引ALTER TABLE articles ADD FULLTEXT(title, body);-- 或者在创建表时定义CREATE TABLE articles ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, title VARCHAR(200), body TEXT, FULLTEXT (title, body)) ENGINE=InnoDB;
查询时，使用
MATCH AGAINST
：
-- 替换 LIKE '%MySQL%'SELECT id, title, bodyFROM articlesWHERE MATCH(title, body) AGAINST('MySQL');-- 布尔模式，更灵活，例如搜索包含MySQL但不包含InnoDB的SELECT id, title, bodyFROM articlesWHERE MATCH(title, body) AGAINST('+MySQL -InnoDB' IN BOOLEAN MODE);
PostgreSQL: 使用
tsvector
和
tsquery
类型，结合
GIN
或
GIST
索引。
-- 1. 添加tsvector列，用于存储文本向量ALTER TABLE products ADD COLUMN textsearchable_index_col tsvector;-- 2. 创建触发器，在数据插入或更新时自动更新tsvector列CREATE FUNCTION products_tsvector_trigger() RETURNS trigger AS $$BEGIN NEW.textsearchable_index_col := to_tsvector('chinese', NEW.product_name || ' ' || NEW.description); -- 假设对product_name和description字段进行索引 RETURN NEW;END$$ LANGUAGE plpgsql;CREATE TRIGGER tsvectorupdate BEFORE INSERT OR UPDATE ON products FOR EACH ROW EXECUTE FUNCTION products_tsvector_trigger();-- 3. 创建GIN索引，加速tsvector列的查询CREATE INDEX products_tsvector_idx ON products USING GIN (textsearchable_index_col);
查询时，使用
@@
操作符：
TextCortex
AI写作能手，在几秒钟内创建内容。
62 查看详情
-- 替换 LIKE '%苹果%'SELECT product_id, product_nameFROM productsWHERE textsearchable_index_col @@ to_tsquery('chinese', '苹果');
通过这种方式，查询不再是逐行匹配字符串，而是利用高度优化的倒排索引，即便数据量巨大，也能在毫秒级内返回结果。这不仅仅是性能的提升，更是查询能力的一次升级，因为全文索引通常还支持词干提取、同义词、相关性排序等高级功能。
为什么传统的LIKE ‘%keyword%’查询效率低下？深入剖析其性能瓶颈
说实话，每次看到
LIKE '%关键词%'
这样的查询出现在生产环境的慢查询日志里，我都会本能地皱眉。它慢，不是偶然，而是其工作机制决定的。我们都知道，数据库中的B-tree索引是提升查询速度的利器，它将数据按序存储，让查找变得像翻字典一样快。但问题就出在这里：B-tree索引是基于前缀匹配的。
想象一下，你有一本按姓氏首字母排序的电话簿。如果你想找所有姓“张”的人，那很容易，直接翻到“Z”开头的页面就行。这就是
LIKE '张%'
能利用索引的原因。但如果你想找名字里包含“小明”的人，而你不知道他姓什么，你总不能从头到尾把电话簿每一页都翻一遍吧？这就是
LIKE '%小明%'
的困境。
当通配符
%
出现在搜索模式的开头时，数据库的查询优化器就傻眼了。它无法预测哪个词会包含这个子串，所以它唯一的选择就是：全表扫描（Full Table Scan）。它会一行一行地读取数据，然后对每一行的指定列进行字符串匹配。数据量小的时候可能不明显，一旦表里有几十万、上百万甚至更多行数据，这种操作就会迅速耗尽I/O资源、CPU，并可能导致长时间的表锁，直接影响到并发性能。
即使在某些情况下，数据库可能会尝试使用一些特殊的索引策略，比如在PostgreSQL中可能利用
pg_trgm
模块创建
GIN
或
GIST
索引来加速部分
LIKE
查询，但这并非所有数据库的默认行为，也不是解决所有模糊匹配问题的银弹。所以，从根本上理解，
LIKE '%keyword%'
的低效在于其无法有效利用大多数关系型数据库默认的B-tree索引结构，导致计算成本随着数据量的增长而呈线性甚至指数级上升。
全文索引相比传统LIKE查询有哪些核心优势？不仅仅是速度
在我看来，全文索引的引入，不仅仅是解决了
LIKE
查询慢的问题，它更像是一次搜索能力的“维度升级”。它的优势远不止于速度。
首先，压倒性的性能优势是显而易见的。就像前面提到的，全文索引通过构建倒排索引，将查找从“大海捞针”变成了“按图索骥”。当你的查询是
LIKE '%手机%'
时，数据库可能要遍历百万行；而使用全文索引，它直接查找到“手机”这个词对应的文档ID列表，然后直接取回这些文档，这个过程通常是毫秒级的。这种效率上的飞跃，对于用户体验和系统吞吐量来说，是质的提升。
其次，更智能、更丰富的搜索功能是传统
LIKE
望尘莫及的。
词干提取（Stemming）：比如你搜索“running”，它也能匹配到“run”、“runs”。这在自然语言处理中非常重要，让搜索结果更全面。同义词（Synonyms）：你可以配置“手机”和“移动电话”是同义词，用户搜其中一个，都能找到相关内容。停用词（Stop Words）：像“的”、“是”、“一个”这类常见但无意义的词，全文索引会自动忽略，避免它们干扰搜索结果的相关性。相关性排序（Relevance Ranking）：全文索引通常会根据词频、词的位置等因素，给搜索结果一个相关性分数，让你能将最匹配的结果排在前面，这对于用户来说太重要了。传统
LIKE
只能告诉你“有”或“没有”。
以上就是如何优化SQL中的LIKE查询？使用全文索引替换通配符查询提升性能的详细内容，更多请关注创想鸟其它相关文章！
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。
如发现本站有涉嫌抄袭侵权/违法违规的内容， 请发送邮件至 chuangxiangniao@163.com 举报，一经查实，本站将立刻删除。
发布者：程序猿，转转请注明出处：https://www.chuangxiangniao.com/p/964125.html
微信扫一扫 
支付宝扫一扫 
