mysql中%前置模糊查询怎么优化

原创于 2025-09-11 21:46:44 发布 · 1.1k 阅读

23 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #数据库

mysql 专栏收录该内容

37 篇文章

订阅专栏

文章目录

MySQL中%前置的模糊查询（如WHERE name LIKE '%abc'）由于无法使用普通索引，容易效率通常较低，尤其在大数据量表中可能导致全表扫描。以下是针对性的优化方案，按优先级从高到低排序：

一、使用反转字段+普通索引（推荐）

原理：

将原字段值反转后存储，并建立索引，将%前置查询转为%后置查询（可利用索引）。
例如：查询name LIKE '%abc' 等价于查反转后的reverse_name LIKE 'cba%'。

步骤：

新增反转字段：

ALTER TABLE user ADD COLUMN reverse_name VARCHAR(255) NOT NULL DEFAULT '';

更新反转字段：

UPDATE user SET reverse_name = REVERSE(name); -- 初始化已有数据

建立索引：

CREATE INDEX idx_reverse_name ON user(reverse_name);

查询改写：

-- 原查询（无法走索引）
SELECT * FROM user WHERE name LIKE '%abc';

-- 优化后（走idx_reverse_name索引）
SELECT * FROM user WHERE reverse_name LIKE CONCAT(REVERSE('abc'), '%');
-- 等价于 WHERE reverse_name LIKE 'cba%'

适用场景：

固定前缀模糊查询（如%后缀），且字段长度适中（避免反转后索引过大）。
写入频率不高的表（需维护反转字段的一致性，可通过触发器自动更新）。

二、使用全文索引（适合长文本）

原理：

MySQL的FULLTEXT全文索引支持自然语言搜索，可替代部分模糊查询场景，尤其适合长文本（如商品描述、文章内容）。

步骤：

创建全文索引：

-- 对name字段创建全文索引
CREATE FULLTEXT INDEX idx_ft_name ON user(name);

查询改写：

-- 原查询
SELECT * FROM user WHERE name LIKE '%abc%';

-- 优化后（全文索引查询）
SELECT * FROM user WHERE MATCH(name) AGAINST('abc' IN BOOLEAN MODE);

注意：

全文索引对短文本（如姓名、手机号）效果较差（默认忽略太短的词，可通过ft_min_word_len调整）。
支持*通配符（如'abc*'匹配以abc开头的词），但不直接支持%abc，需结合业务语义调整查询逻辑。
MyISAM和InnoDB均支持，但InnoDB的全文索引在MySQL 5.6+才支持。

三、使用数据预处理+前缀索引（折中方案）

原理：

若查询的后缀长度固定（如最后3位），可提前提取后缀并建立前缀索引。
例如：查询phone LIKE '%123'（匹配手机号最后3位为123），可新增phone_suffix字段存储后3位。

步骤：

新增后缀字段：

ALTER TABLE user ADD COLUMN phone_suffix VARCHAR(3) NOT NULL DEFAULT '';

更新后缀字段：

UPDATE user SET phone_suffix = RIGHT(phone, 3); -- 提取最后3位

建立索引：

CREATE INDEX idx_phone_suffix ON user(phone_suffix);

查询改写：

-- 原查询
SELECT * FROM user WHERE phone LIKE '%123';

-- 优化后（走索引）
SELECT * FROM user WHERE phone_suffix = '123';

适用场景：

后缀长度固定的场景（如手机号后N位、日期中的月份/日）。
需精确匹配后缀，而非模糊匹配任意长度的后缀。

四、使用外部搜索引擎（大规模数据）

原理：

对于超大数据量表（千万级以上）或复杂模糊查询，可将数据同步到Elasticsearch等搜索引擎，利用其倒排索引高效支持任意模糊查询。

步骤：

数据同步：通过CDC工具（如Canal）将MySQL数据实时同步到Elasticsearch。
创建索引：在ES中对目标字段创建text类型索引（默认分词，支持前缀、后缀、中间模糊查询）。
查询改写：通过ES的wildcard查询实现%abc效果：

{
  "query": {
    "wildcard": {
      "name": { "value": "*abc" }
    }
  }
}

适用场景：

全量数据需支持复杂模糊查询（如电商商品搜索、日志检索）。
MySQL查询性能无法满足需求，且可接受一定的数据同步延迟（毫秒级至秒级）。

五、其他辅助优化手段

限制返回结果：

加上LIMIT减少扫描行数（如LIMIT 100），尤其适合只需要部分匹配结果的场景。

分区表过滤：

若表已按时间或其他维度分区，可先通过分区键过滤缩小范围，再执行模糊查询。

-- 先按分区键（如create_time）过滤，再查模糊匹配
SELECT * FROM user 
WHERE create_time >= '2023-01-01' 
  AND name LIKE '%abc';

避免SELECT *：

只查询必要字段，减少IO开销，若字段较少可使用覆盖索引进一步优化。

总结：优化方案选择建议

场景	最优方案	性能提升幅度
固定后缀模糊查询（如`%abc`）	反转字段+普通索引	10~100倍（视数据量）
长文本模糊查询（如文章内容）	全文索引	5~50倍
固定长度后缀匹配（如后3位）	预处理后缀+前缀索引	10~100倍
超大数据量+复杂模糊查询	外部搜索引擎（Elasticsearch）	100~1000倍