欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

MySQL索引的優(yōu)化之LIKE模糊查詢功能實(shí)現(xiàn)

 更新時間：2025年04月21日 14:38:40   作者：帥的飛起來  
這篇文章主要介紹了MySQL索引的優(yōu)化之LIKE模糊查詢功能實(shí)現(xiàn),本文通過示例代碼給大家介紹的非常詳細(xì),感興趣的朋友一起看看吧

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

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

  • 前綴匹配：如果模糊查詢是前綴匹配（如 LIKE '%abc' ）,MySQL可以使用索引來加速查詢。確保在相關(guān)列上創(chuàng)建了索引
  • 后綴匹配：對于后綴匹配（如LIKE 'abc%'），MySQL無法使用普通的B-tree索引?？梢钥紤]使用反向索引（Reverse Index）或全文索引（Full-Text Index）
  • 中間匹配：對于中間匹配（如LIKE '%abc%'）,MySQL也無法使用普通的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);
?
-- 前綴匹配查詢
EXPLAIN SELECT * FROM users WHERE username LIKE 'john%';
  • 執(zhí)行計(jì)劃分析
    • 如果使用了索引，EXPLAIN結(jié)果中的key列會顯示idx_username,表明查詢使用了索引
    • type會顯示range，表示使用了索引范圍掃描

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

后綴匹配（如LIKE '%abc'），無法直接使用B-tree索引，可以通過反轉(zhuǎn)字符串并創(chuàng)建索引來優(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);
?
-- 后綴匹配查詢（轉(zhuǎn)換為前綴匹配）
EXPLAIN SELECT * FROM users WHERE reversed_username LIKE REVERSE('doe') + '%';
  • 執(zhí)行計(jì)劃分析
    • 查詢反轉(zhuǎn)后的列時，EXPLAIN結(jié)果中的key列會顯示idx_reversed_username，表明使用了索引
    • type列會顯示range，表示使用了索引范圍掃描

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

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

示例（使用全文索引）

-- 創(chuàng)建全文索引
CREATE FULLTEXT INDEX idx_username_fulltext ON users(username);
?
-- 全文索引查詢
EXPLAIN SELECT * FROM users WHERE MATCH(username) AGAINST('doe');
  • 執(zhí)行計(jì)劃分析：
    • EXPLAIN結(jié)果中的key列會顯示idx_username_fulltext，表明使用了全文索引
    • type列會顯示fulltext，表示使用了全文索引

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

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

示例：

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

五、減少查詢范圍

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

示例：

-- 假設(shè)有一個注冊時間列
ALTER TABLE users ADD COLUMN registered_at DATETIME;
?
-- 插入數(shù)據(jù)
UPDATE users SET registered_at = NOW() - INTERVAL FLOOR(RAND() * 365) DAY;
?
-- 縮小查詢范圍
EXPLAIN SELECT * FROM users 
WHERE registered_at > '2023-01-01' 
AND username LIKE 'john%';
  • 執(zhí)行計(jì)劃分析
    • EXPLAIN結(jié)果中的key列會顯示idx_username,表明使用了索引
    • rows列的值會減少，表明查詢范圍縮小

六、避免通配符開頭

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

示例：

-- 不推薦的查詢
EXPLAIN SELECT * FROM users WHERE username LIKE '%doe';
?
-- 優(yōu)化后的查詢（使用全文索引）
EXPLAIN SELECT * FROM users WHERE MATCH(username) AGAINST('doe');
  • 執(zhí)行計(jì)劃分析：
    • 不推薦的查詢中，type列會顯示ALL，表示全表掃描。
    • 優(yōu)化后的查詢中，type列會顯示fulltext，表示使用了全文索引。

七、使用外部搜索引擎

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

示例

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

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

如果數(shù)據(jù)量非常大，可以使用分區(qū)表（Partitioning），來較少每次查詢需要掃描的數(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ū)表查詢
EXPLAIN SELECT * FROM users_partitioned 
WHERE registered_at > '2023-01-01' 
AND username LIKE 'john%';
  • 執(zhí)行計(jì)劃分析：
    • EXPLAIN結(jié)果中的partitions列會顯示查詢涉及的分區(qū)，表明查詢只掃描了部分?jǐn)?shù)據(jù)。

九、緩存結(jié)果

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

示例：

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

總結(jié)

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

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

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

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

相關(guān)文章

最新評論