在使用MySQL進(jìn)行模糊查詢(xún)時(shí)，LIKE語(yǔ)句的性能可能會(huì)受到較大影響，尤其是在數(shù)據(jù)量較大的情況下。

但本質(zhì)上，用like進(jìn)行模糊查詢(xún)，只有以下三種情況：

• 前綴匹配：如果模糊查詢(xún)是前綴匹配（如 LIKE '%abc' ）,MySQL可以使用索引來(lái)加速查詢(xún)。確保在相關(guān)列上創(chuàng)建了索引
• 后綴匹配：對(duì)于后綴匹配（如LIKE 'abc%'），MySQL無(wú)法使用普通的B-tree索引。可以考慮使用反向索引（Reverse Index）或全文索引（Full-Text Index）
• 中間匹配：對(duì)于中間匹配（如LIKE '%abc%'）,MySQL也無(wú)法使用普通的B-tree索引。全文索引或搜索引擎（如Elasticsearcha）可能是更好的選擇。

一、前綴匹配優(yōu)化

前綴匹配（如LIKE 'abc%'）可以使用B-tree索引，因此性能較好。確保在相關(guān)列上創(chuàng)建索引

示例：

-- 創(chuàng)建表
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(255) NOT NULL
);
?
-- 插入數(shù)據(jù)
INSERT INTO users (username) VALUES ('john_doe'), ('jane_doe'), ('alice'), ('bob'), ('john_smith');
?
-- 創(chuàng)建索引
CREATE INDEX idx_username ON users(username);
?
-- 前綴匹配查詢(xún)
EXPLAIN SELECT * FROM users WHERE username LIKE 'john%';

• 執(zhí)行計(jì)劃分析
  • 如果使用了索引，EXPLAIN結(jié)果中的key列會(huì)顯示idx_username,表明查詢(xún)使用了索引
  • type會(huì)顯示range，表示使用了索引范圍掃描

二、后綴匹配優(yōu)化

后綴匹配（如LIKE '%abc'），無(wú)法直接使用B-tree索引，可以通過(guò)反轉(zhuǎn)字符串并創(chuàng)建索引來(lái)優(yōu)化

示例：

-- 添加反轉(zhuǎn)列
ALTER TABLE users ADD COLUMN reversed_username VARCHAR(255);
?
-- 更新反轉(zhuǎn)列數(shù)據(jù)
UPDATE users SET reversed_username = REVERSE(username);
-- REVERSE('hello') 的結(jié)果是 'olleh'
?
-- 創(chuàng)建反轉(zhuǎn)列索引
CREATE INDEX idx_reversed_username ON users(reversed_username);
?
-- 后綴匹配查詢(xún)（轉(zhuǎn)換為前綴匹配）
EXPLAIN SELECT * FROM users WHERE reversed_username LIKE REVERSE('doe') + '%';

• 執(zhí)行計(jì)劃分析
  • 查詢(xún)反轉(zhuǎn)后的列時(shí)，EXPLAIN結(jié)果中的key列會(huì)顯示idx_reversed_username，表明使用了索引
  • type列會(huì)顯示range，表示使用了索引范圍掃描

三、中間匹配優(yōu)化

中間匹配（如LIKE '%abc%'）無(wú)法使用B-tree索引。可以考慮使用全文索引或外部搜索引擎

示例（使用全文索引）

-- 創(chuàng)建全文索引
CREATE FULLTEXT INDEX idx_username_fulltext ON users(username);
?
-- 全文索引查詢(xún)
EXPLAIN SELECT * FROM users WHERE MATCH(username) AGAINST('doe');

• 執(zhí)行計(jì)劃分析：
  • EXPLAIN結(jié)果中的key列會(huì)顯示idx_username_fulltext，表明使用了全文索引
  • type列會(huì)顯示fulltext，表示使用了全文索引

四、覆蓋索引優(yōu)化

如果查詢(xún)只需要返回索引列，可以使用覆蓋索引（Covering index），避免回表操作

示例：

-- 創(chuàng)建覆蓋索引
CREATE INDEX idx_username_covering ON users(username, id);
?
-- 覆蓋索引查詢(xún)
EXPLAIN SELECT username FROM users WHERE username LIKE 'john%';

五、減少查詢(xún)范圍

通過(guò)其他條件縮小查詢(xún)范圍，減少模糊查詢(xún)的數(shù)據(jù)量

示例：

-- 假設(shè)有一個(gè)注冊(cè)時(shí)間列
ALTER TABLE users ADD COLUMN registered_at DATETIME;
?
-- 插入數(shù)據(jù)
UPDATE users SET registered_at = NOW() - INTERVAL FLOOR(RAND() * 365) DAY;
?
-- 縮小查詢(xún)范圍
EXPLAIN SELECT * FROM users 
WHERE registered_at > '2023-01-01' 
AND username LIKE 'john%';

• 執(zhí)行計(jì)劃分析
  • EXPLAIN結(jié)果中的key列會(huì)顯示idx_username,表明使用了索引
  • rows列的值會(huì)減少，表明查詢(xún)范圍縮小

六、避免通配符開(kāi)頭

盡量避免在LIKE語(yǔ)句中使用通配符開(kāi)頭（如%abc），因?yàn)檫@種查詢(xún)無(wú)法使用索引

示例：

-- 不推薦的查詢(xún)
EXPLAIN SELECT * FROM users WHERE username LIKE '%doe';
?
-- 優(yōu)化后的查詢(xún)（使用全文索引）
EXPLAIN SELECT * FROM users WHERE MATCH(username) AGAINST('doe');

• 執(zhí)行計(jì)劃分析：
  • 不推薦的查詢(xún)中，type列會(huì)顯示ALL，表示全表掃描。
  • 優(yōu)化后的查詢(xún)中，type列會(huì)顯示fulltext，表示使用了全文索引。

七、使用外部搜索引擎

對(duì)于復(fù)雜的模糊查詢(xún)需求，尤其是大數(shù)據(jù)量場(chǎng)景，可以使用外部搜索引擎（如Elatsticsearch）

示例

• 將數(shù)據(jù)同步到Elasticsearch。
• 使用Elasticsearch進(jìn)行模糊查詢(xún)。

八、分區(qū)表優(yōu)化

如果數(shù)據(jù)量非常大，可以使用分區(qū)表（Partitioning），來(lái)較少每次查詢(xún)需要掃描的數(shù)據(jù)量

示例：

-- 創(chuàng)建分區(qū)表
CREATE TABLE users_partitioned (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(255) NOT NULL,
    registered_at DATETIME
) PARTITION BY RANGE (YEAR(registered_at)) (
    PARTITION p0 VALUES LESS THAN (2020),
    PARTITION p1 VALUES LESS THAN (2021),
    PARTITION p2 VALUES LESS THAN (2022),
    PARTITION p3 VALUES LESS THAN (2023),
    PARTITION p4 VALUES LESS THAN MAXVALUE
);
?
-- 插入數(shù)據(jù)
INSERT INTO users_partitioned (username, registered_at) 
SELECT username, registered_at FROM users;
?
-- 分區(qū)表查詢(xún)
EXPLAIN SELECT * FROM users_partitioned 
WHERE registered_at > '2023-01-01' 
AND username LIKE 'john%';

• 執(zhí)行計(jì)劃分析：
  • EXPLAIN結(jié)果中的partitions列會(huì)顯示查詢(xún)涉及的分區(qū)，表明查詢(xún)只掃描了部分?jǐn)?shù)據(jù)。

九、緩存結(jié)果

如果模糊查詢(xún)的結(jié)果不經(jīng)常變化，可以將查詢(xún)結(jié)果緩存起來(lái)，減少數(shù)據(jù)庫(kù)的查詢(xún)壓力

示例：

• 使用redis緩存查詢(xún)結(jié)果
• 設(shè)置緩存的過(guò)期時(shí)間，確保數(shù)據(jù)的時(shí)效性

總結(jié)

通過(guò)以上方法，可以顯著優(yōu)化MySQL中LIKE模糊查詢(xún)的性能。根據(jù)具體的業(yè)務(wù)需求和數(shù)據(jù)特點(diǎn)，選擇合適的優(yōu)化策略：

• 前綴匹配：使用普通索引。
• 后綴匹配：使用反轉(zhuǎn)索引。
• 中間匹配：使用全文索引或外部搜索引擎。
• 大數(shù)據(jù)量：使用分區(qū)表或外部搜索引擎。
• 高頻查詢(xún)：使用緩存。

注：了解MySQL-MATCH ... AGAINST工具參考MySQL-MATCH ... AGAINST工具

到此這篇關(guān)于MySQL--索引的優(yōu)化--LIKE模糊查詢(xún)的文章就介紹到這了,更多相關(guān)mysql like模糊查詢(xún)內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論