MySQL是如何优化模糊匹配like的SQL？

在数据库查询中，模糊匹配（LIKE操作）是常见的需求，尤其在搜索、日志分析等场景中不可或缺。然而，LIKE操作往往因全表扫描或低效索引利用导致性能瓶颈。MySQL通过多种机制优化LIKE查询，本文将从索引、查询重写、执行计划调整等角度深入解析其优化策略。

一、索引优化：从B-Tree到倒排索引

1. B-Tree索引的局限性

传统B-Tree索引对LIKE查询的支持有限，尤其是以通配符%开头的模式（如LIKE '%abc'）。由于B-Tree按列值完整内容排序，而非按部分内容排序，此类查询无法利用索引的有序性，导致全表扫描。例如：

-- 无法使用索引
SELECT * FROM users WHERE name LIKE '%张%';

2. 前缀匹配的索引利用

当LIKE模式以固定字符串开头（如LIKE '张%'）时，B-Tree索引可发挥优势。MySQL通过二分查找定位到以'张'开头的记录范围，大幅减少扫描行数：

-- 可使用索引（假设name列有索引）
SELECT * FROM users WHERE name LIKE '张%';

优化建议：

• 对频繁前缀匹配的列建立索引。
• 避免在查询条件左侧使用通配符。

3. 倒排索引与全文索引

对于复杂模糊匹配（如多关键词、任意位置匹配），MySQL提供全文索引（FULLTEXT）。它通过倒排索引（Inverted Index）存储词项与文档的映射关系，支持高效的全文搜索：

-- 创建全文索引
ALTER TABLE articles ADD FULLTEXT(title, content);
-- 使用MATCH AGAINST替代LIKE
SELECT * FROM articles 
WHERE MATCH(title, content) AGAINST('数据库优化' IN NATURAL LANGUAGE MODE);

优势：

• 支持自然语言搜索、布尔模式等高级功能。
• 避免LIKE的性能问题，尤其适合长文本搜索。

二、查询重写与函数索引

1. 反向列与函数索引

MySQL 8.0+支持函数索引，可通过创建反向列或哈希列优化任意位置匹配：

-- 创建反向列并建立索引
ALTER TABLE users ADD COLUMN name_reverse VARCHAR(255);
UPDATE users SET name_reverse = REVERSE(name);
CREATE INDEX idx_name_reverse ON users(name_reverse);
-- 查询时反向匹配
SELECT * FROM users 
WHERE name_reverse LIKE REVERSE('%张%');

适用场景：

• 固定模式的任意位置匹配（如包含某子串）。
• 需结合应用层逻辑维护反向列。

2. 正则表达式优化

MySQL的REGEXP或RLIKE操作符可通过正则引擎优化部分模式，但性能通常弱于LIKE。建议仅在复杂模式时使用，并确保正则表达式高效：

-- 谨慎使用正则
SELECT * FROM products WHERE name REGEXP '^苹果.*手机$';

三、执行计划调整与参数优化

1. 强制索引提示

当优化器未选择正确索引时，可通过FORCE INDEX或USE INDEX提示：

SELECT * FROM logs FORCE INDEX(idx_message) 
WHERE message LIKE '%error%';

注意：

• 需通过EXPLAIN确认执行计划是否合理。
• 避免过度使用提示，可能适得其反。

2. 调整查询重写参数

MySQL的optimizer_switch参数可控制查询重写行为，例如启用condition_fanout_filter优化多表JOIN中的LIKE条件：

SET optimizer_switch='condition_fanout_filter=on';

3. 内存与并行查询

• 增大sort_buffer_size：对ORDER BY + LIKE的组合查询有益。
• 并行查询（MySQL 8.0+）：通过innodb_parallel_read_threads参数启用并行扫描。

四、实际应用中的优化策略

1. 分库分表与数据分片

对超大规模数据，可通过分库分表减少单表数据量。例如按用户ID哈希分片后，LIKE查询仅需扫描部分分片。

2. 缓存与结果集复用

• 对高频LIKE查询，使用Redis等缓存结果。
• 通过SQL_NO_CACHE避免缓存污染：

  SELECT SQL_NO_CACHE * FROM products WHERE name LIKE '%促销%';

3. 应用层优化

• 前端模糊搜索：使用Elasticsearch等专用搜索引擎。
• 分页优化：避免LIMIT 10000, 10的深分页，改用WHERE id > last_id LIMIT 10。

五、案例分析：电商搜索优化

场景：用户搜索商品名称包含“无线耳机”的记录。
原始SQL：

SELECT * FROM products WHERE name LIKE '%无线耳机%';

问题：全表扫描，响应慢。
优化方案：

1. 建立全文索引：

   ALTER TABLE products ADD FULLTEXT(name);
   SELECT * FROM products 
   WHERE MATCH(name) AGAINST('无线耳机' IN BOOLEAN MODE);

2. 结合分类过滤：

   SELECT * FROM products 
   WHERE category_id = 5 AND MATCH(name) AGAINST('无线耳机');

3. 使用专用搜索引擎：将商品数据同步至Elasticsearch，支持更复杂的搜索语法。

六、总结与建议

MySQL对LIKE的优化需结合场景选择策略：

1. 前缀匹配：优先使用B-Tree索引。
2. 任意位置匹配：考虑全文索引或反向列。
3. 大规模数据：分库分表或引入外部搜索引擎。
4. 监控与调优：定期通过EXPLAIN、慢查询日志分析性能瓶颈。

最终建议：

• 避免在WHERE条件左侧使用%通配符。
• 对文本搜索需求，优先评估全文索引的适用性。
• 复杂场景下，考虑“MySQL + 专用搜索引擎”的混合架构。

通过合理应用上述策略，可显著提升LIKE查询的性能，平衡功能与效率。