MySQL中Like模糊查詢速度太慢該怎么進行優化

# MySQL中Like模糊查詢速度太慢該怎么進行優化

## 前言

在數據庫查詢中，`LIKE`模糊查詢是最常用的操作之一。但當數據量達到百萬甚至千萬級別時，`LIKE`查詢的性能問題會變得非常突出。本文將從多個角度分析`LIKE`查詢慢的原因，并提供詳細的優化方案。

---

## 一、為什么LIKE查詢慢？

### 1. 全表掃描問題
`LIKE '%keyword%'`這種前后模糊匹配方式會導致：
- 無法使用B-Tree索引（最左前綴原則）
- 必須進行全表掃描（Full Table Scan）
- 數據量越大性能下降越明顯

### 2. 字符集和排序規則影響
- UTF8MB4等變長字符集比定長字符集更耗資源
- 復雜的排序規則（如`utf8mb4_unicode_ci`）比簡單規則（如`utf8mb4_general_ci`）計算成本更高

### 3. 通配符位置
性能排序：`LIKE 'keyword%'` > `LIKE '%keyword'` > `LIKE '%keyword%'`

---

## 二、核心優化方案

### 1. 使用索引優化前綴匹配
```sql
-- 只有這種形式能利用索引
ALTER TABLE products ADD INDEX idx_name(name(20));
SELECT * FROM products WHERE name LIKE 'apple%';

注意： - 需指定前綴長度（如name(20)） - 適用于LIKE 'prefix%'形式

2. 全文索引（FULLTEXT）

-- 創建全文索引
ALTER TABLE articles ADD FULLTEXT INDEX ft_index(content);

-- 使用全文檢索
SELECT * FROM articles 
WHERE MATCH(content) AGNST('+database -mysql' IN BOOLEAN MODE);

優勢： - 專為文本搜索設計 - 支持布爾搜索、相關性排序

限制： - 僅適用于MyISAM和InnoDB（MySQL 5.6+） - 默認最小詞長4字符（可通過ft_min_word_len調整）

3. 使用反向索引+倒排表（專業方案）

-- 創建關鍵詞表
CREATE TABLE keywords (
    id INT PRIMARY KEY,
    keyword VARCHAR(50),
    INDEX(keyword)
);

-- 創建關聯表
CREATE TABLE product_keywords (
    product_id INT,
    keyword_id INT,
    PRIMARY KEY(product_id, keyword_id)
);

-- 查詢示例
SELECT p.* FROM products p
JOIN product_keywords pk ON p.id = pk.product_id
JOIN keywords k ON pk.keyword_id = k.id
WHERE k.keyword = 'apple';

適用場景： - 電商平臺的商品搜索 - 內容管理系統

三、輔助優化技巧

1. 使用覆蓋索引

-- 創建包含常用查詢字段的復合索引
ALTER TABLE users ADD INDEX idx_cover(first_name, last_name, email);

-- 查詢時只返回索引列
SELECT first_name, last_name FROM users 
WHERE first_name LIKE 'John%';

2. 分頁緩存優化

-- 先獲取ID，再關聯查詢
SELECT * FROM products p
JOIN (
    SELECT id FROM products
    WHERE name LIKE '%phone%'
    LIMIT 10000, 20
) AS tmp ON p.id = tmp.id;

3. 使用內存表緩存熱點數據

-- 創建內存臨時表
CREATE TEMPORARY TABLE temp_results (
    id INT PRIMARY KEY,
    name VARCHAR(100)
) ENGINE=MEMORY;

-- 緩存結果
INSERT INTO temp_results
SELECT id, name FROM products WHERE name LIKE '%新品%';

4. 函數索引（MySQL 8.0+）

-- 創建函數索引
ALTER TABLE products ADD INDEX idx_reverse_name((REVERSE(name)));

-- 使用反向查詢優化后綴匹配
SELECT * FROM products 
WHERE REVERSE(name) LIKE REVERSE('%.com');

四、架構級解決方案

1. 讀寫分離

• 將搜索請求路由到只讀副本
• 減輕主庫壓力

2. 使用專業搜索引擎

3. 數據預處理

• 定期生成搜索詞摘要表
• 使用定時任務預計算熱門查詢

五、實戰案例

案例1：電商商品搜索優化

原始查詢：

SELECT * FROM products 
WHERE title LIKE '%智能手機%' 
AND status = 1
ORDER BY sales DESC
LIMIT 100;

優化方案： 1. 創建聯合索引：(status, sales) 2. 使用Elasticsearch建立商品搜索集群 3. 結果緩存到Redis，有效期5分鐘

案例2：日志內容檢索

需求：在TB級日志中查找錯誤信息

解決方案： 1. 使用LIKE 'ERROR%'替代LIKE '%ERROR%' 2. 按日期分表（如logs_202301） 3. 使用ClickHouse列式存儲

六、性能對比測試

測試環境：AWS RDS MySQL 8.0，1000萬條數據

結語

優化LIKE查詢需要根據具體場景選擇方案： 1. 簡單前綴匹配 → 普通索引 2. 復雜文本搜索 → 全文索引 3. 海量數據檢索 → 專業搜索引擎 4. 實時性要求高 → 內存緩存+預計算

最終建議：在MySQL 8.0+環境下，優先考慮函數索引和倒排索引方案，配合架構層面的讀寫分離和緩存策略，可以顯著提升模糊查詢性能。 “`

注：本文實際約1500字，可根據需要補充更多具體案例或配置細節。主要優化思路已完整呈現，包含代碼示例、方案對比和實戰建議。