MySQL中Like模糊查询速度太慢该怎么进行优化

# MySQL中Like模糊查询速度太慢该怎么进行优化

## 前言

在数据库查询中，`LIKE`模糊查询是最常用的操作之一。但当数据量达到百万甚至千万级别时，`LIKE`查询的性能问题会变得非常突出。本文将从多个角度分析`LIKE`查询慢的原因，并提供详细的优化方案。

---

## 一、为什么LIKE查询慢？

### 1. 全表扫描问题
`LIKE '%keyword%'`这种前后模糊匹配方式会导致：
- 无法使用B-Tree索引（最左前缀原则）
- 必须进行全表扫描（Full Table Scan）
- 数据量越大性能下降越明显

### 2. 字符集和排序规则影响
- UTF8MB4等变长字符集比定长字符集更耗资源
- 复杂的排序规则（如`utf8mb4_unicode_ci`）比简单规则（如`utf8mb4_general_ci`）计算成本更高

### 3. 通配符位置
性能排序：`LIKE 'keyword%'` > `LIKE '%keyword'` > `LIKE '%keyword%'`

---

## 二、核心优化方案

### 1. 使用索引优化前缀匹配
```sql
-- 只有这种形式能利用索引
ALTER TABLE products ADD INDEX idx_name(name(20));
SELECT * FROM products WHERE name LIKE 'apple%';

注意： - 需指定前缀长度（如name(20)） - 适用于LIKE 'prefix%'形式

2. 全文索引（FULLTEXT）

-- 创建全文索引
ALTER TABLE articles ADD FULLTEXT INDEX ft_index(content);

-- 使用全文检索
SELECT * FROM articles 
WHERE MATCH(content) AGNST('+database -mysql' IN BOOLEAN MODE);

优势： - 专为文本搜索设计 - 支持布尔搜索、相关性排序

限制： - 仅适用于MyISAM和InnoDB（MySQL 5.6+） - 默认最小词长4字符（可通过ft_min_word_len调整）

3. 使用反向索引+倒排表（专业方案）

-- 创建关键词表
CREATE TABLE keywords (
    id INT PRIMARY KEY,
    keyword VARCHAR(50),
    INDEX(keyword)
);

-- 创建关联表
CREATE TABLE product_keywords (
    product_id INT,
    keyword_id INT,
    PRIMARY KEY(product_id, keyword_id)
);

-- 查询示例
SELECT p.* FROM products p
JOIN product_keywords pk ON p.id = pk.product_id
JOIN keywords k ON pk.keyword_id = k.id
WHERE k.keyword = 'apple';

适用场景： - 电商平台的商品搜索 - 内容管理系统

三、辅助优化技巧

1. 使用覆盖索引

-- 创建包含常用查询字段的复合索引
ALTER TABLE users ADD INDEX idx_cover(first_name, last_name, email);

-- 查询时只返回索引列
SELECT first_name, last_name FROM users 
WHERE first_name LIKE 'John%';

2. 分页缓存优化

-- 先获取ID，再关联查询
SELECT * FROM products p
JOIN (
    SELECT id FROM products
    WHERE name LIKE '%phone%'
    LIMIT 10000, 20
) AS tmp ON p.id = tmp.id;

3. 使用内存表缓存热点数据

-- 创建内存临时表
CREATE TEMPORARY TABLE temp_results (
    id INT PRIMARY KEY,
    name VARCHAR(100)
) ENGINE=MEMORY;

-- 缓存结果
INSERT INTO temp_results
SELECT id, name FROM products WHERE name LIKE '%新品%';

4. 函数索引（MySQL 8.0+）

-- 创建函数索引
ALTER TABLE products ADD INDEX idx_reverse_name((REVERSE(name)));

-- 使用反向查询优化后缀匹配
SELECT * FROM products 
WHERE REVERSE(name) LIKE REVERSE('%.com');

四、架构级解决方案

1. 读写分离

• 将搜索请求路由到只读副本
• 减轻主库压力

2. 使用专业搜索引擎

3. 数据预处理

• 定期生成搜索词摘要表
• 使用定时任务预计算热门查询

五、实战案例

案例1：电商商品搜索优化

原始查询：

SELECT * FROM products 
WHERE title LIKE '%智能手机%' 
AND status = 1
ORDER BY sales DESC
LIMIT 100;

优化方案： 1. 创建联合索引：(status, sales) 2. 使用Elasticsearch建立商品搜索集群 3. 结果缓存到Redis，有效期5分钟

案例2：日志内容检索

需求：在TB级日志中查找错误信息

解决方案： 1. 使用LIKE 'ERROR%'替代LIKE '%ERROR%' 2. 按日期分表（如logs_202301） 3. 使用ClickHouse列式存储

六、性能对比测试

测试环境：AWS RDS MySQL 8.0，1000万条数据

结语

优化LIKE查询需要根据具体场景选择方案： 1. 简单前缀匹配 → 普通索引 2. 复杂文本搜索 → 全文索引 3. 海量数据检索 → 专业搜索引擎 4. 实时性要求高 → 内存缓存+预计算

最终建议：在MySQL 8.0+环境下，优先考虑函数索引和倒排索引方案，配合架构层面的读写分离和缓存策略，可以显著提升模糊查询性能。 “`

注：本文实际约1500字，可根据需要补充更多具体案例或配置细节。主要优化思路已完整呈现，包含代码示例、方案对比和实战建议。