1. 为什么LIKE查询会成为性能杀手？

当我们在用户表中执行WHERE username LIKE '%张%'这样的查询时，看似简单的模糊匹配背后隐藏着巨大的性能隐患。某电商平台曾因商品搜索接口响应时间超过5秒导致用户流失，经排查发现正是LIKE全模糊查询导致全表扫描。

传统B-Tree索引对模糊查询的支持存在天然缺陷：

  • 前导通配符%会导致索引失效
  • 中文字符需要特殊处理
  • 数据量超过百万级时性能急剧下降

我们做过一个实测：在500万用户数据表上，LIKE '%1388888%'的查询耗时达到12秒，而通过后续优化方案可以将其缩短到200毫秒以内。

2. 核心优化方案详解

2.1 索引结构的魔法改造

适用场景：固定前缀的模糊查询（如手机号前7位查询）

-- 创建前缀索引（MySQL 5.7+）
ALTER TABLE users ADD INDEX idx_phone_prefix (phone(7));

-- 优化后的查询语句
SELECT * FROM users 
WHERE phone LIKE '1388888%';  -- 正确使用索引范围扫描

技术要点：

  • 前缀长度需根据数据特征确定
  • EXPLAIN验证是否使用索引
  • 需要配合应用程序的输入校验

性能对比：

数据量 原始查询 优化后查询
50万 1.2s 0.03s
500万 15s 0.25s

2.2 全文索引的深度应用

适用场景：中文商品名称搜索、文章内容检索

-- 创建全文索引（需MyISAM或InnoDB引擎）
ALTER TABLE products 
ADD FULLTEXT INDEX ft_idx_product_name (product_name) 
WITH PARSER ngram;

-- 优化查询语句
SELECT * FROM products 
WHERE MATCH(product_name) AGAINST('+"智能手机" +"防水"' IN BOOLEAN MODE);

配置关键点：

  1. 修改my.cnf配置：
[mysqld]
ngram_token_size=2  # 适合中文分词
ft_min_word_len=2
  1. 重建索引后生效

注意事项：

  • 需要定期维护索引表
  • 停用词列表需要特别处理
  • 结果相关性排序需要二次处理

2.3 查询条件的智能改写

经典案例：将全模糊查询转换为范围查询

-- 原始低效查询
SELECT * FROM logs 
WHERE content LIKE '%error%';

-- 优化后的多条件组合查询
SELECT * FROM logs
WHERE create_time > '2023-01-01'
  AND (content LIKE 'error%' 
    OR content LIKE '% error%'
    OR content LIKE '% error');

优化原理：

  • 利用时间条件缩小数据集
  • 优先处理可索引的前缀匹配
  • 组合条件分解降低单次扫描量

2.4 空间换时间的缓存策略

实现方案：

-- 创建热点数据缓存表
CREATE TABLE hot_search_cache (
    keyword VARCHAR(50) PRIMARY KEY,
    result_ids JSON,
    expire_time DATETIME
);

-- 缓存更新触发器示例
DELIMITER //
CREATE TRIGGER update_search_cache AFTER INSERT ON products
FOR EACH ROW
BEGIN
    IF NEW.product_name LIKE '%新款%' THEN
        REPLACE INTO hot_search_cache 
        VALUES ('新款', JSON_ARRAY(NEW.id), NOW() + INTERVAL 1 HOUR);
    END IF;
END//
DELIMITER ;

缓存策略选择：

  • LRU缓存：适合高频随机查询
  • 预生成缓存：适合固定条件查询
  • 分级缓存：结合Redis+MySQL

2.5 架构层面的终极方案

Elasticsearch整合示例：

// Spring Data Elasticsearch示例
@Query("{\"multi_match\":{\"query\":\"?0\",\"fields\":[\"productName^3\",\"description\"]}}")
Page<Product> searchByKeywords(String keywords, Pageable pageable);

同步方案对比：

方案 延迟 可靠性 实现复杂度
触发器同步 <1s 高 高
Binlog订阅 <500ms 极高 中
双写机制 实时 中 低

2.6 冷门但有效的特殊技巧

哈希辅助列方案：

ALTER TABLE users 
ADD COLUMN name_hash BINARY(16) 
GENERATED ALWAYS AS (UNHEX(MD5(name))) STORED;

CREATE INDEX idx_name_hash ON users(name_hash);

SELECT * FROM users
WHERE name_hash = UNHEX(MD5('张三'))
   AND name LIKE '%张三%';

优点：

  • 精确匹配快速定位
  • 二次验证保证准确性
  • 适合高基数列查询

3. 技术方案选型指南

决策矩阵参考：

场景特征 推荐方案 预期提升倍数
固定前缀查询 前缀索引 10-100x
中文全文检索 全文索引+分词 50-200x
高频复杂查询 Elasticsearch 100-1000x
数据实时性要求高 查询改写+缓存 5-10x
历史数据归档查询 分区表 3-5x

4. 避坑指南与最佳实践

  1. 索引长度陷阱：

    • VARCHAR(255)列创建前缀索引时，实际应测试不同长度选择率
    • 计算公式：SELECT COUNT(DISTINCT LEFT(column, N))/COUNT(*)

  2. 分词器选择：

    -- 查看当前分词配置
SHOW VARIABLES LIKE 'ngram_token_size';

-- 中文推荐配置
[mysqld]
ngram_token_size=2
ft_min_word_len=2

  3. 混合方案示例：

    -- 组合全文索引与普通索引
SELECT * FROM products 
WHERE MATCH(name) AGAINST('手机' IN BOOLEAN MODE)
  AND price BETWEEN 1000 AND 2000
ORDER BY sales_volume DESC
LIMIT 100;

5. 未来技术演进方向

  1. MySQL 8.0隐藏索引： 
    CREATE INDEX idx_name ON users(name) INVISIBLE;
ALTER TABLE users ALTER INDEX idx_name VISIBLE;
  2. 列式存储引擎： 
    ALTER TABLE log_data ENGINE=Columnstore;
  3. AI预测索引： 
    ANALYZE TABLE users PERSISTENT FOR ALL;