1. 问题背景：为什么LIKE '%abc%'查询慢如蜗牛？
当我们在MySQL中执行包含前导通配符的LIKE查询时（如WHERE column LIKE '%abc%'），数据库引擎无法有效利用B+树索引的结构特性。这是因为：
- B+树索引是按照字段值的从左到右顺序构建的
- 前导通配符
%使得查询条件失去了最左匹配原则的基础 - 引擎不得不进行全表扫描，逐行检查每条记录是否匹配模式
我曾在实际项目中遇到一个用户表查询案例：500万数据量下，WHERE username LIKE '%admin%'查询耗时达到惊人的12秒。通过EXPLAIN分析可见type列为ALL，证实了全表扫描的发生。
2. 反向存储方案原理剖析
2.1 核心思路：逆转字符串建立索引
解决方案的核心在于创建反向存储列：
- 原始数据："database"
- 反向存储："esabatad"
- 查询
LIKE 'cba%'转换为LIKE REVERSE('abc') + '%'
这样就能利用B+树索引的最左匹配特性。以查询"abc"结尾的记录为例：
-- 原始低效查询 SELECT * FROM table WHERE column LIKE '%abc'; -- 优化后查询 SELECT * FROM table WHERE reversed_column LIKE REVERSE('abc') || '%';2.2 实现方案对比
| 方案类型 | 写入性能 | 查询性能 | 存储开销 | 维护复杂度 |
|---|---|---|---|---|
| 反向存储列 | 中 | 高 | 低 | 中 |
| 全文索引 | 低 | 中 | 高 | 高 |
| 触发器维护 | 低 | 高 | 低 | 高 |
| 应用层处理 | 高 | 高 | 无 | 高 |
3. 完整实现步骤
3.1 方案一：虚拟生成列（MySQL 5.7+）
-- 创建测试表 CREATE TABLE products ( id INT PRIMARY KEY, name VARCHAR(100), name_reversed VARCHAR(100) GENERATED ALWAYS AS (REVERSE(name)) STORED ); -- 创建反向索引 CREATE INDEX idx_name_reversed ON products(name_reversed); -- 优化后查询 SELECT * FROM products WHERE name_reversed LIKE CONCAT(REVERSE('abc'), '%');注意：必须使用STORED类型生成列，VIRTUAL生成列无法创建索引
3.2 方案二：触发器自动维护
CREATE TABLE customers ( id INT PRIMARY KEY, email VARCHAR(255), email_reversed VARCHAR(255) ); -- 创建双向触发器 DELIMITER // CREATE TRIGGER before_customer_insert BEFORE INSERT ON customers FOR EACH ROW BEGIN SET NEW.email_reversed = REVERSE(NEW.email); END// CREATE TRIGGER before_customer_update BEFORE UPDATE ON customers FOR EACH RACH ROW BEGIN SET NEW.email_reversed = REVERSE(NEW.email); END// DELIMITER ; -- 创建索引 CREATE INDEX idx_email_reversed ON customers(email_reversed);4. 性能实测对比
使用sysbench构造100万条测试数据：
| 查询类型 | 平均耗时(ms) | 扫描行数 | 索引使用情况 |
|---|---|---|---|
| LIKE '%abc%' | 1250 | 1000000 | 未使用 |
| 反向列LIKE 'cba%' | 12 | 253 | 使用 |
| 全文索引 | 85 | 1000000 | 使用 |
实测显示反向存储方案比原生LIKE查询快约100倍，且随着数据量增大优势更加明显。
5. 特殊场景处理技巧
5.1 中文等多字节字符处理
对于UTF-8等多字节编码，需使用专门的逆转函数：
-- MySQL多字节安全逆转函数 CREATE FUNCTION reverse_utf8(s VARCHAR(1000)) RETURNS VARCHAR(1000) DETERMINISTIC BEGIN DECLARE result VARCHAR(1000) DEFAULT ''; DECLARE i INT DEFAULT CHAR_LENGTH(s); WHILE i > 0 DO SET result = CONCAT(result, SUBSTRING(s, i, 1)); SET i = i - 1; END WHILE; RETURN result; END5.2 模糊查询优化组合拳
结合其他优化手段效果更佳：
- 固定前缀查询：
LIKE 'abc%'（原生支持索引） - 后缀查询：使用反向存储方案
- 包含查询：
LIKE '%abc%'可拆分为REVERSE(col) LIKE 'cba%' OR col LIKE 'abc%'
6. 生产环境注意事项
- 写入性能影响：反向列会带来约15%的写入性能下降，高频写入场景需测试
- 存储空间：额外列需要约1:1的存储空间
- 查询改造：所有相关查询都需要重写
- 事务一致性：确保触发器或应用层维护的数据一致性
- 历史数据迁移：已有数据需要批量初始化反向列
我在电商项目中的实际应用表明，该方案特别适合：
- 用户行为日志分析（搜索行为追踪）
- 商品名称模糊查询
- 联系方式后缀匹配
7. 替代方案对比
当反向存储方案不适用时，可考虑：
全文索引：适合自然语言搜索，但配置复杂
ALTER TABLE products ADD FULLTEXT INDEX ft_idx_name(name); SELECT * FROM products WHERE MATCH(name) AGAINST('+abc' IN BOOLEAN MODE);N-gram分词：MySQL 5.7+支持
CREATE TABLE docs ( id INT PRIMARY KEY, text TEXT, FULLTEXT INDEX ft_idx (text) WITH PARSER ngram );专用搜索引擎：Elasticsearch等更适合复杂搜索场景
8. 常见问题排查
Q1：索引未生效怎么办？
- 检查EXPLAIN确认索引使用情况
- 确保反向列是STORED类型
- 确认查询条件格式正确
Q2：性能提升不明显？
- 检查数据基数（Cardinality）
- 确认没有隐式类型转换
- 检查索引统计信息是否过期
Q3：内存不足错误
- 调整innodb_buffer_pool_size
- 考虑使用前缀索引
CREATE INDEX idx_name_reversed ON products(name_reversed(20));
通过实际项目验证，这套方案在5000万级别的用户行为日志表中，将模糊查询耗时从分钟级降低到秒级，配合前端加载动画，用户体验得到显著提升。
