MySQL模糊查询LIKE优化大全：7种方法+代码实测

前言

MySQL的LIKE模糊查询是高频操作，但使用不当易引发性能问题。本文将详解7种优化方案，通过代码案例演示如何提升查询效率，适用于开发者和DBA。

一、避免左通配符：巧用右匹配

1.1 问题SQL

-- 全表扫描（无法使用索引）
SELECT * FROM products WHERE name LIKE '%手机';

1.2 优化方案

反向存储+右通配符

-- 1. 添加反向存储列
ALTER TABLE products 
ADD COLUMN name_reverse VARCHAR(255) AS (REVERSE(name)) STORED;

-- 2. 创建索引
CREATE INDEX idx_name_reverse ON products(name_reverse);

-- 3. 反向查询
SELECT * FROM products 
WHERE name_reverse LIKE REVERSE('手机') || '%'; 
-- 等价于 WHERE name LIKE '%手机'

二、覆盖索引：减少IO消耗

2.1 创建联合索引

ALTER TABLE users 
ADD INDEX idx_username_email (username, email);

2.2 仅查询索引字段

-- Using index (无需回表)
SELECT username, email FROM users 
WHERE username LIKE '张%';

2.3 强制索引（慎用）

SELECT * FROM users 
FORCE INDEX(idx_username_email) 
WHERE username LIKE '张%';

三、全文索引：大文本搜索利器

3.1 创建全文索引

-- 建表时指定
CREATE TABLE articles (
  id INT PRIMARY KEY,
  content TEXT,
  FULLTEXT KEY ft_content (content)
) ENGINE=InnoDB;

-- 已有表添加索引
ALTER TABLE articles ADD FULLTEXT ft_content(content);

3.2 全文检索语法

-- 自然语言模式
SELECT * FROM articles 
WHERE MATCH(content) AGAINST('数据库优化');

-- 布尔模式（精确匹配）
SELECT * FROM articles 
WHERE MATCH(content) AGAINST('+MySQL -Oracle' IN BOOLEAN MODE);

四、函数索引：MySQL 8.0+方案

4.1 创建函数索引

-- 直接对表达式建索引
CREATE INDEX idx_lower_name ON users( (LOWER(name)) );

-- 查询时自动匹配
SELECT * FROM users 
WHERE LOWER(name) LIKE '%john%';

五、冗余存储：空间换时间

5.1 添加预处理列

-- 去除空格+小写存储
ALTER TABLE products 
ADD COLUMN name_clean VARCHAR(255) 
GENERATED ALWAYS AS (REPLACE(LOWER(name), ' ', '')) STORED;

-- 建索引
CREATE INDEX idx_name_clean ON products(name_clean);

5.2 查询优化

SELECT * FROM products 
WHERE name_clean LIKE '%iphone13%'; 
-- 原字段包含'Iphone 13'也能匹配

六、第三方工具：Elasticsearch整合

6.1 同步数据到ES

PUT /products
{
  "mappings": {
    "properties": {
      "name": { "type": "text", "analyzer": "ik_max_word" }
    }
  }
}

6.2 执行模糊查询

GET /products/_search
{
  "query": {
    "wildcard": {
      "name": "*手机*"
    }
  }
}

七、其他优化技巧

7.1 分页优化

-- 低效写法
SELECT * FROM logs 
WHERE content LIKE '%error%' 
LIMIT 100000, 10;

-- 优化写法（基于ID游标）
SELECT * FROM logs 
WHERE content LIKE '%error%' AND id > 100000 
ORDER BY id ASC 
LIMIT 10;

总结对比表

方法	适用场景	优点	缺点
右通配符	前缀搜索	简单高效	无法左右模糊
覆盖索引	查询列少	避免回表	需索引覆盖所有列
全文索引	大文本搜索	专业分词	中文需配置
函数索引	MySQL 8.0+	支持表达式	版本限制
Elasticsearch	海量数据高频模糊	毫秒级响应	架构复杂