MySQL LIKE模糊查询优化是一个常见问题，主要原因是LIKE以通配符开头的查询无法使用B-Tree索引。以下是详细的优化方案：

一、索引优化方案

1. 前缀查询优化（LIKE 'prefix%'）

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

-- 创建索引
CREATE INDEX idx_name ON users(name);

2. 后缀查询优化（LIKE '%suffix'）

-- 方案1：反转字符串+前缀查询
CREATE INDEX idx_name_reverse ON users(REVERSE(name));
SELECT * FROM users WHERE REVERSE(name) LIKE REVERSE('%明');

-- 方案2：使用虚拟列（MySQL 5.7+）
ALTER TABLE users 
ADD COLUMN name_reverse VARCHAR(255) AS (REVERSE(name)) STORED,
ADD INDEX idx_name_reverse(name_reverse);

3. 全模糊查询优化（LIKE '%keyword%'）

方案A：全文检索（MySQL 5.6+）

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

-- 使用MATCH AGAINST（布尔模式）
SELECT * FROM articles 
WHERE MATCH(content) AGAINST('+关键词' IN BOOLEAN MODE);

-- 自然语言模式
SELECT * FROM articles 
WHERE MATCH(content) AGAINST('关键词' IN NATURAL LANGUAGE MODE);

方案B：N-gram分词（中文场景）

-- 创建N-gram全文索引
CREATE FULLTEXT INDEX ft_content_ngram ON articles(content) 
WITH PARSER ngram;

-- 查询
SELECT * FROM articles 
WHERE MATCH(content) AGAINST('关键词' IN NATURAL LANGUAGE MODE);

二、架构层面优化

1. 使用搜索引擎

• Elasticsearch：适合全文搜索场景
• Apache Solr：企业级搜索解决方案
• 阿里云OpenSearch：云原生搜索服务

2. 倒排索引方案

-- 创建关键词映射表
CREATE TABLE keyword_mapping (
    keyword VARCHAR(50),
    record_id INT,
    PRIMARY KEY(keyword, record_id)
);

-- 查询时先查映射表
SELECT DISTINCT m.record_id 
FROM keyword_mapping m 
WHERE m.keyword LIKE '用户%';

三、查询技巧优化

1. 减少扫描范围

-- 不好：全表扫描
SELECT * FROM large_table WHERE content LIKE '%keyword%';

-- 较好：先缩小范围
SELECT * FROM large_table 
WHERE create_time > '2023-01-01'  -- 利用时间索引
  AND content LIKE '%keyword%';    -- 再模糊查询

2. 使用覆盖索引

-- 创建覆盖索引
CREATE INDEX idx_covering ON users(name, email, phone);

-- 只查询索引列
SELECT name, email FROM users WHERE name LIKE '张%';

3. 分页优化

-- 低效
SELECT * FROM users WHERE name LIKE '%张%' LIMIT 1000, 20;

-- 高效：使用延迟关联
SELECT * FROM users u
JOIN (
    SELECT id FROM users 
    WHERE name LIKE '%张%'
    LIMIT 1000, 20
) AS tmp ON u.id = tmp.id;

四、特殊场景优化

1. 短文本精确匹配

-- 使用HASH列
ALTER TABLE products
ADD COLUMN name_hash BINARY(16) AS (UNHEX(MD5(name))) STORED,
ADD INDEX idx_name_hash(name_hash);

-- 查询
SELECT * FROM products 
WHERE name_hash = UNHEX(MD5('商品名称'))
  AND name LIKE '%商品名称%';  -- 防止哈希冲突

2. 枚举值查询

-- 不好
SELECT * FROM logs WHERE level LIKE '%ERROR%';

-- 好：使用固定值
SELECT * FROM logs WHERE level IN ('ERROR', 'FATAL_ERROR');

五、配置参数优化

-- 调整InnoDB缓冲池
SET GLOBAL innodb_buffer_pool_size = 大小;

-- 调整全文检索参数
ft_min_word_len = 1          -- 最小词长
innodb_ft_min_token_size = 1  -- InnoDB最小词长
ngram_token_size = 1         -- N-gram分词长度

六、最佳实践建议

1. 设计阶段
   • 尽量避免全模糊查询需求
   • 明确区分前缀、后缀、全模糊场景
   • 合理设计数据表结构
2. 索引策略
   • 前缀查询：使用普通B-Tree索引
   • 后缀查询：使用反转字符串索引
   • 全模糊：使用全文索引或搜索引擎
3. 查询优化
   • 使用EXPLAIN分析执行计划
   • 避免在WHERE子句中对索引列使用函数
   • 合理使用覆盖索引
4. 监控与调优 -- 监控慢查询 SHOW VARIABLES LIKE 'slow_query_log%'; -- 分析查询性能 EXPLAIN SELECT * FROM users WHERE name LIKE '%张%';

总结建议

场景	推荐方案	说明
前缀查询	B-Tree索引	最有效，直接利用索引
后缀查询	反转列索引	转换后缀为前缀查询
全模糊+文本搜索	全文索引	MySQL内置，支持中文用N-gram
复杂搜索需求	Elasticsearch	功能强大，支持分词、同义词等
实时性要求高	内存数据库+索引	Redis、Memcached缓存热点数据

选择原则：根据数据量、查询频率、实时性要求、开发成本综合选择。小数据量用MySQL优化即可，大数据量或复杂需求考虑专业搜索引擎。