如何优化SQL中的LIKE查询？使用全文索引替换通配符查询提升性能

使用全文索引替代LIKE '%关键词%'查询可显著提升性能，因其基于倒排索引实现高效检索，避免全表扫描，同时支持词干提取、同义词、相关性排序等高级功能，适用于SQL Server、MySQL和PostgreSQL等主流数据库。

优化SQL中的

LIKE

%

LIKE '%关键词%'

当我们谈到SQL查询优化，

LIKE

%

LIKE '%关键词%'

全文索引的工作原理与普通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 ProductCatalog
  WITH CHANGE_TRACKING AUTO;

  登录后复制

  查询时，使用

  CONTAINS

  登录后复制
  或

  FREETEXT

  登录后复制
  ：

  -- 替换 LIKE '%手机%'
  SELECT ProductID, ProductName
  FROM Products
  WHERE CONTAINS(ProductName, '手机');
  
  -- 多个关键词，类似 AND 关系
  SELECT ProductID, ProductName
  FROM Products
  WHERE 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, body
  FROM articles
  WHERE MATCH(title, body) AGAINST('MySQL');
  
  -- 布尔模式，更灵活，例如搜索包含MySQL但不包含InnoDB的
  SELECT id, title, body
  FROM articles
  WHERE 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);

  登录后复制

  查询时，使用

  @@

  登录后复制
  操作符：
  

  蓝心千询

  蓝心千询是vivo推出的一个多功能AI智能助手

  34

  查看详情

  -- 替换 LIKE '%苹果%'
  SELECT product_id, product_name
  FROM products
  WHERE textsearchable_index_col @@ to_tsquery('chinese', '苹果');

  登录后复制

通过这种方式，查询不再是逐行匹配字符串，而是利用高度优化的倒排索引，即便数据量巨大，也能在毫秒级内返回结果。这不仅仅是性能的提升，更是查询能力的一次升级，因为全文索引通常还支持词干提取、同义词、相关性排序等高级功能。

为什么传统的LIKE '%keyword%'查询效率低下？深入剖析其性能瓶颈

说实话，每次看到

LIKE '%关键词%'

想象一下，你有一本按姓氏首字母排序的电话簿。如果你想找所有姓“张”的人，那很容易，直接翻到“Z”开头的页面就行。这就是

LIKE '张%'

LIKE '%小明%'

当通配符

%

即使在某些情况下，数据库可能会尝试使用一些特殊的索引策略，比如在PostgreSQL中可能利用

pg_trgm

GIN

GIST

LIKE

LIKE '%keyword%'

全文索引相比传统LIKE查询有哪些核心优势？不仅仅是速度

在我看来，全文索引的引入，不仅仅是解决了

LIKE

首先，压倒性的性能优势是显而易见的。就像前面提到的，全文索引通过构建倒排索引，将查找从“大海捞针”变成了“按图索骥”。当你的查询是

LIKE '%手机%'

其次，更智能、更丰富的搜索功能是传统

LIKE

• 词干提取（Stemming）：比如你搜索“running”，它也能匹配到“run”、“runs”。这在自然语言处理中非常重要，让搜索结果更全面。
• 同义词（Synonyms）：你可以配置“手机”和“移动电话”是同义词，用户搜其中一个，都能找到相关内容。
• 停用词（Stop Words）：像“的”、“是”、“一个”这类常见但无意义的词，全文索引会自动忽略，避免它们干扰搜索结果的相关性。
• 相关性排序（Relevance Ranking）：全文索引通常会根据词频、词的位置等因素，给搜索结果一个相关性分数，让你能将最匹配的结果排在前面，这对于用户来说太重要了。传统

  LIKE

  登录后复制
  只能告诉你“有”或“没有”。

以上就是如何优化SQL中的LIKE查询？使用全文索引替换通配符查询提升性能的详细内容，更多请关注php中文网其它相关文章！