全模糊查询LIKE '%keyword%'）是索引优化的经典难题。因为它破坏了索引的**有序性前缀**，导致最左匹配失效。不过，通过架构、索引策略和数据库高级功能的组合，仍然有系统的优化方案。
接下来，我们深入每种方案的具体实施方法。
🔍 方案一：覆盖索引（减少回表，缓解问题）
这是最易实施的方案。核心思想是：**虽然索引失效导致全索引扫描，但扫描索引本身比扫描全表快得多**。
- 原理：创建一个覆盖查询所有字段的联合索引，让查询只需要扫描索引，无需“回表”查询数据行，极大减少I/O。
- 实施：
-- 假设原查询为: SELECT id, name, bio FROM users WHERE bio LIKE '%Java%';
-- 创建覆盖索引
CREATE INDEX idx_bio_covering ON users(bio, id, name);
-- 注意: bio必须放在最左，但即便如此，全模糊查询仍无法快速定位，
-- 这个索引的意义在于让查询变成“仅扫描索引树”，而无需访问数据表。
- 适用场景：查询字段较少、数据量中等、无法改造查询语句的场景。这是**治标不治本**的方案，但能带来显著提升。
✂️ 方案二：改写查询与反向索引（变不可能为可能）
这是利用业务特性和数据结构进行的巧妙改造。
- 原理：如果查询字段有固定前缀或后缀，可通过改写查询，使其满足最左匹配。
- 实施：
增加前缀列：若业务允许，增加一
first_char列存储首字符或固定前缀，并建立联合索引。
ALTER TABLE users ADD COLUMN name_first_char CHAR(1) AS (LEFT(name, 1)) STORED;
CREATE INDEX idx_firstchar_name ON users(name_first_char, name);
-- 查询改写为
SELECT * FROM users WHERE name_first_char = '张' AND name LIKE '%伟%';使用反转函数：针对后缀查询
LIKE '%com'），可利用**反向索引（Reverse Key）**。
-- 存储反转值
ALTER TABLE domains ADD COLUMN url_reverse VARCHAR(255) AS (REVERSE(url)) STORED;
CREATE INDEX idx_url_reverse ON domains(url_reverse);
-- 查询“以.com结尾”改为“以 moc. 开头”
SELECT * FROM domains WHERE url_reverse LIKE REVERSE('%.com') + '%';- 适用场景：查询模式相对固定的业务，如按固定前缀、后缀、域名的查找。
📄 方案三：使用全文索引（MySQL内置的专业方案）
这是处理文本搜索的“正规军”。
- 原理：全文索引基于**倒排索引**实现，它将文本分解为词元（Token），并记录每个词元出现在哪些文档中。查询时直接定位词元，**完全不受“最左匹配”限制**，且支持自然语言搜索、布尔搜索等多种模式。
- 实施：
-- 1. 创建全文索引 (以InnoDB为例)
CREATE FULLTEXT INDEX ft_idx_bio ON users(bio);
-- 2. 使用 MATCH ... AGAINST 语法进行查询
SELECT * FROM users WHERE MATCH(bio) AGAINST('+Java' IN BOOLEAN MODE);
-- 这将高效地找到所有包含“Java”的记录，无论它在字段的什么位置。- 优势：
- 速度快：基于词元的查找效率远高LIKE的逐字符匹配。
- 功能强：支持分词、停用词、相关性评分、布尔运算+Java -Python）。
- 结果优：可按相关性排序，返回更符合语义的结果。
- 限制：
- 有最小词长限制（通常为3或4个字符）。
- 对中文分词支持较弱（MySQL 5.7+ 内置ngram解析器可支持，或依赖外部分词插件）。
🚀 方案四：引入外部搜索引擎（终极解决方案）
当数据量巨大、搜索需求复杂时，必须将搜索职责从OLTP数据库中剥离。
- 原理：使用 Elasticsearch 或 OpenSearch 这类专业的搜索引擎。它们专为海量数据的实时检索而设计。
- 架构：采用 “双写” 或 “CDC（变更数据捕获）同步” 将MySQL数据同步到搜索引擎。
- 优势：
- 性能极致：毫秒级响应，应对亿级数据毫无压力。
- 功能全面：支持精准匹配、模糊匹配（Fuzzy）、同义词、拼音搜索、复杂聚合、高亮显示等。
- 减轻数据库压力：将昂贵的文本搜索计算转移到专门的搜索集群。
- 成本：架构复杂度高，需要维护另一套系统。
💎 总结与决策建议
为了帮你快速决策，这里有一个方案对比总结：
| 方案 | 核心原理 | 优点 | 缺点 | 适用场景 |
| :--- | :--- | :--- | :--- | :---|
| 覆盖索引 | 避免回表，扫描索引 | 实现简单，快速见效 | 仍是全索引扫描，治标不治本 | 临时优化，字段少，无法改动的遗留系统 |
| 改写查询与反向索引 | 变全模糊为后模糊，满足最左匹配 | 能利用B+树索引高效定位 | 依赖业务特性，通用性差 | 查询模式固定（如按后缀、固定前缀查找） |
| 全文索引 | 倒排索引，关键词检索 | 功能强大，性能好，MySQL内置 | 有词长限制，中文分词需配置 | 中等数据量，以文本搜索为主的业务（文章、简介） |
| 外部搜索引擎 | 专用搜索架构，倒排索引集群 | 性能最强，功能最全，扩展性好 | 架构复杂，维护成本高 | 海量数据，高并发，复杂搜索需求（电商、内容平台） |
行动建议：
先评估：分析你的数据量、查询频率、响应时间要求。
从简单开始：尝试**方案一（覆盖索引）** 和 方案三（全文索引），它们改造难度较低，往往能解决80%的问题。
考虑未来：如果业务在快速增长，且搜索是核心功能，尽早规划 方案四（Elasticsearch）。