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

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

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

原理：

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

步骤：

1. 新增反转字段：

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

2. 更新反转字段：

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

3. 建立索引：

   CREATE INDEX idx_reverse_name ON user(reverse_name);
   

4. 查询改写：

   -- 原查询（无法走索引）
   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全文索引支持自然语言搜索，可替代部分模糊查询场景，尤其适合长文本（如商品描述、文章内容）。

步骤：

1. 创建全文索引：

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

2. 查询改写：

   -- 原查询
   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位。

步骤：

1. 新增后缀字段：

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

2. 更新后缀字段：

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

3. 建立索引：

   CREATE INDEX idx_phone_suffix ON user(phone_suffix);
   

4. 查询改写：

   -- 原查询
   SELECT * FROM user WHERE phone LIKE '%123';
   
   -- 优化后（走索引）
   SELECT * FROM user WHERE phone_suffix = '123';
   

适用场景：

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

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

原理：

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

步骤：

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

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

适用场景：

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

五、其他辅助优化手段

1. 限制返回结果：

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

2. 分区表过滤：

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

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

3. 避免SELECT *：

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

总结：优化方案选择建议

核心思路：通过预处理将无法利用索引的%前置查询，转化为可利用索引的查询，避免全表扫描。实际应用中需结合业务场景和数据量选择最合适的方案。