大家好，我是鋒哥。最近不少粉絲問鋒哥MySQL中like模糊查詢如何優化?今天鋒哥來總結下，大家可以參考。

最近鋒哥也開始收一些Java學員，有意向可以找鋒哥。

在數據庫應用中，LIKE 是一種常用的模糊查詢方法，它允許我們在查詢中使用通配符進行部分匹配。雖然 LIKE 查詢在許多情況下非常方便，但當數據量增大時，使用 LIKE 查詢會導致性能問題，尤其是當查詢中使用了前置通配符（%abc）時，MySQL無法利用索引，從而導致全表掃描。為了提高性能，我們需要對 LIKE 查詢進行優化。

1. LIKE查詢的工作原理

MySQL的 LIKE 查詢通過對字段內容進行匹配來查找符合條件的記錄。常用的通配符有：

• %

• ：匹配零個或多個字符。

• _

• ：匹配一個字符。

例如，查詢字段 name 中包含“張”字的所有記錄：

SELECT*FROM users WHERE name LIKE'%張%';

如果查詢條件是 %張%，MySQL將無法使用索引，因為查詢必須掃描每一行來查找匹配。這個查詢將變得非常慢，尤其是在表的記錄非常多時。

2. LIKE模糊查詢的性能瓶頸

全表掃描

當使用 LIKE 查詢時，如果在搜索模式中使用了前置通配符（如 %abc），MySQL無法利用索引。這意味著它必須對表中的每一行數據進行掃描，從而顯著影響查詢速度。

無法利用索引

如果查詢模式是前置通配符的形式（如 %abc），MySQL就無法利用索引進行加速，性能會變得非常差。即使字段上有索引，MySQL也無法使用它，因為索引只能用來提高基于前綴的匹配（比如 abc%）。

3. 優化LIKE查詢的方法

1. 使用前綴匹配（避免前置通配符）

最有效的優化方法之一是避免使用前置通配符。例如：

SELECT*FROM users WHERE name LIKE'張%';

這種情況下，MySQL可以利用索引進行快速查找，避免全表掃描。如果可以確定查詢條件的模式盡量避免前綴是 % 的模糊查詢。

2. 使用全文索引（FULLTEXT）

對于大量文本數據的模糊匹配查詢，可以考慮使用MySQL的全文索引。全文索引允許對文本字段進行更高效的搜索，適合大數據量的模糊查詢。

步驟：

1. 在表中創建全文索引：

2. ALTERTABLE users ADD FULLTEXT(name);

3. 使用 MATCH ... AGAINST 來替代 LIKE：

4. SELECT*FROM users WHEREMATCH(name) AGAINST ('張'INBOOLEAN MODE);

5. 這樣可以顯著提高查詢效率，尤其是對于包含大量文本的字段。

3. 使用正則表達式（REGEXP）

在一些特定情況下，可以考慮使用正則表達式來優化查詢。正則表達式在某些情況下比 LIKE 更高效，尤其是對于復雜模式匹配。MySQL支持 REGEXP 運算符，它允許使用正則表達式來進行模式匹配。

例如：

SELECT*FROM users WHERE name REGEXP '^張';

然而，REGEXP 在一些查詢中仍然可能不如 LIKE 快，因此在使用時需要謹慎評估性能。

4. 使用 IN 和 OR 來減少查詢范圍

當查詢條件可以分解成多個離散的值時，可以使用 IN 或 OR 進行優化，避免模糊匹配。例如，如果我們知道名字中有特定的幾個值，可以使用 IN：

SELECT*FROM users WHERE name IN ('張三', '張偉', '張杰');

這能顯著減少掃描的記錄數量，從而提高查詢效率。

5. 使用索引優化查詢

為了優化基于 LIKE 的查詢，可以確保查詢列有索引，特別是對于查詢條件的字段。通過建立索引，可以加速基于前綴匹配的查詢。例如：

CREATE INDEX idx_name ON users(name);

對于前綴查詢，創建索引后可以顯著提高查詢性能。但是，記住只有像 name LIKE '張%' 這樣的前綴查詢才能有效使用索引。

6. 分表或分庫

對于數據量極大的表，可以通過分表或分庫的方式將數據劃分為多個子集，避免單一表的性能瓶頸。例如，可以根據 name 字段的首字母進行分表，或者基于其他邏輯進行拆分。這樣可以減少每次查詢掃描的表的大小，從而提高查詢效率。

4. 示例：優化前置通配符的LIKE查詢

假設我們有一個包含用戶信息的表 users，并且我們需要查詢名字中包含“張”字的所有用戶。原始查詢：

SELECT*FROM users WHERE name LIKE'%張%';

優化方案1：避免使用前置通配符

我們將查詢修改為避免前置通配符的形式：

SELECT*FROM users WHERE name LIKE'張%';

優化方案2：使用全文索引

我們為 name 字段添加全文索引，并使用 MATCH ... AGAINST 進行查詢：

ALTERTABLE users ADD FULLTEXT(name);

查詢語句：

SELECT*FROM users WHEREMATCH(name) AGAINST ('張'INBOOLEAN MODE);

優化方案3：使用正則表達式

如果需要更靈活的模式匹配，可以使用正則表達式：

SELECT*FROM users WHERE name REGEXP '張';

5. 總結

• 避免前置通配符

• ：盡量使用 LIKE '張%' 這樣的前綴匹配，避免 %張% 這樣的查詢。

• 使用全文索引

• ：對于大量文本數據，可以通過創建全文索引并使用 MATCH 來加速查詢。

• 使用正則表達式

• ：對于復雜的模式匹配，考慮使用 REGEXP，但要注意其性能影響。

• 索引優化

• ：確保查詢列有合適的索引，尤其是對于前綴匹配的情況。

• 分表或分庫

• ：對于大數據量的情況，可以通過分表或分庫來優化性能。

通過上述方法的優化，能夠顯著提高 MySQL 中 LIKE 模糊查詢的性能，尤其在大數據量的應用場景下，能夠提升查詢效率，降低查詢時間。

原文來源：https://mp.weixin.qq.com/s/hyg6rYsUXPYiHjvzw9sO9g