MySQL中l(wèi)ike的模糊查詢?nèi)绾蝺?yōu)化

當(dāng)然還可以ES等 這里只說mysql怎么搞

典型回答

在MySQL中，使用like進(jìn)行模糊查詢，在一定情況下是無法使用索引的。如下所示：

• ●當(dāng)like值前后都有匹配符時(shí)%abc%，無法使用索引

• ●當(dāng)like值前有匹配符時(shí)%abc，無法使用索引

• ●當(dāng)like值后有匹配符時(shí)'abc%'，可以使用索引

那么，like %abc真的無法優(yōu)化了嗎？

我們之所以會(huì)使用%abc來查詢說明表中的name可能包含以abc結(jié)尾的字符串，如果以abc%說明有以abc開頭的字符串。

假設(shè)我們要向表中的name寫入123abc，我們可以將這一列反轉(zhuǎn)過來，即cba321插入到一個(gè)冗余列v_name中，并為這一列建立索引：

接下來在查詢的時(shí)候，我們就可以使用v_name列進(jìn)行模糊查詢了

當(dāng)然這樣看起來有點(diǎn)麻煩，表中如果已經(jīng)有了很多數(shù)據(jù)，還需要利用update語句反轉(zhuǎn)name到v_name中，如果數(shù)據(jù)量大了（幾百萬或上千萬條記錄）更新一下v_name耗時(shí)也比較長(zhǎng)，同時(shí)也會(huì)增大表空間。

MySQL5.7.6之后，新增了虛擬列功能

幸運(yùn)的是在MySQL5.7.6之后，新增了虛擬列功能（如果不是>=5.7.6，只能用上面的土方法）為一個(gè)列建立一個(gè)虛擬列，并為虛擬列建立索引，在查詢時(shí)where中l(wèi)ike條件改為虛擬列，就可以使用索引了。

我們?cè)龠M(jìn)行查詢，就會(huì)走索引了

當(dāng)然如果你要查詢like 'abc%'和like '%abc'，你只需要使用一個(gè)union

可以看到，除了union result合并倆個(gè)語句，另外倆個(gè)查詢都已經(jīng)走索引了。如果你只想需要查詢name，甚至可以使用覆蓋索引進(jìn)一步提升性能

虛擬列可以指定為VIRTUAL或STORED，VIRTUAL不會(huì)將虛擬列存儲(chǔ)到磁盤中，在使用時(shí)MySQL會(huì)現(xiàn)計(jì)算虛擬列的值，STORED會(huì)存儲(chǔ)到磁盤中，相當(dāng)于我們手動(dòng)創(chuàng)建的冗余列。所以：如果你的磁盤足夠大，可以使用STORED方式，這樣在查詢時(shí)速度會(huì)更快一些。

如果你的數(shù)據(jù)量級(jí)較大，不使用反向查詢的方式耗時(shí)會(huì)非常高。你可以使用如下sql測(cè)試虛擬列的效果：

/* 建表 */

CREATE TABLE test (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50),
  INDEX idx_name (name)
) CHARACTER SET utf8;


/* 創(chuàng)建一個(gè)存儲(chǔ)過程，向test表中寫入2000000條數(shù)據(jù)，200條數(shù)據(jù)中abc字符前包含一些隨機(jī)字符（用于測(cè)試like '%abc'的情況），200條數(shù)據(jù)中abc字符后包含一些隨機(jī)字符（用于測(cè)試like 'abc%'的情況），其余行不包含abc字符 */

DELIMITER //

CREATE PROCEDURE InsertTestData()
BEGIN
  DECLARE i INT DEFAULT 1;
  
  WHILE i <= 2000000 DO
    IF i <= 200 THEN
      SET @randomPrefix1 = CONCAT(CHAR(FLOOR(RAND() * 26) + 65), CHAR(FLOOR(RAND() * 26) + 97), CHAR(FLOOR(RAND() * 26) + 48));
      SET @randomString1 = CONCAT(CHAR(FLOOR(RAND() * 26) + 65), CHAR(FLOOR(RAND() * 26) + 97), CHAR(FLOOR(RAND() * 26) + 48));
      SET @randomName1 = CONCAT(@randomPrefix1, @randomString1, 'abc');
      INSERT INTO test (name) VALUES (@randomName1);
    ELSEIF i <= 400 THEN
      SET @randomString2 = CONCAT(CHAR(FLOOR(RAND() * 26) + 65), CHAR(FLOOR(RAND() * 26) + 97), CHAR(FLOOR(RAND() * 26) + 48));
      SET @randomName2 = CONCAT('abc', @randomString2);
      INSERT INTO test (name) VALUES (@randomName2);
    ELSE
      SET @randomName3 = CONCAT(CHAR(FLOOR(RAND() * 26) + 65), CHAR(FLOOR(RAND() * 26) + 97), CHAR(FLOOR(RAND() * 26) + 48));
      INSERT INTO test (name) VALUES (@randomName3);
    END IF;
    
    SET i = i + 1;
  END WHILE;
END //

DELIMITER ;




/* 調(diào)用存儲(chǔ)過程，這里執(zhí)行的會(huì)很慢 */

call InsertTestData();



/* 建立虛擬列 */
alter table test add column `v_name` varchar(50) generated always as (reverse(name));
/* 為虛擬列創(chuàng)建索引 */
alter table test add index `idx_name_virt`(v_name);


/* 使用虛擬列模糊查詢 */
select * from test where v_name like 'cba%'
union
select * from test where name like 'abc%'



/* 不使用虛擬列模糊查詢 */
select * from test where name like 'abc%'
union
select * from test where name like '%abc'

MySQL5.7.6 虛擬列功能

MySQL 5.7 引入了虛擬列（Generated Columns），這些列的值是通過表達(dá)式計(jì)算得出的，而不是直接存儲(chǔ)在表中的。虛擬列可以分為兩種類型：VIRTUAL 和 STORED。以下是虛擬列的好處和壞處：

好處

1. 簡(jiǎn)化查詢：

   • 虛擬列可以簡(jiǎn)化復(fù)雜的查詢，尤其是當(dāng)查詢中需要頻繁使用某個(gè)表達(dá)式時(shí)。通過將表達(dá)式定義為虛擬列，可以直接查詢?cè)摿?，而不需要在每次查詢時(shí)重復(fù)計(jì)算。

2. 數(shù)據(jù)一致性：

   • 虛擬列的值是根據(jù)其他列的值自動(dòng)計(jì)算的，因此可以確保數(shù)據(jù)的一致性。如果基礎(chǔ)列的值發(fā)生變化，虛擬列的值會(huì)自動(dòng)更新，避免了手動(dòng)維護(hù)數(shù)據(jù)一致性的麻煩。

3. 減少冗余：

   • 使用虛擬列可以避免存儲(chǔ)冗余數(shù)據(jù)。例如，如果你需要根據(jù)某些列的值計(jì)算出一個(gè)結(jié)果，并且這個(gè)結(jié)果不需要頻繁更新，可以使用虛擬列來動(dòng)態(tài)計(jì)算，而不需要將結(jié)果存儲(chǔ)在表中。

4. 索引支持：

   • 虛擬列可以被索引，這可以顯著提高查詢性能。特別是當(dāng)虛擬列的計(jì)算結(jié)果經(jīng)常用于查詢條件時(shí)，創(chuàng)建索引可以加速查詢。

5. 靈活性：

   • 虛擬列可以根據(jù)需要定義復(fù)雜的表達(dá)式，提供更高的靈活性。你可以根據(jù)業(yè)務(wù)需求動(dòng)態(tài)生成數(shù)據(jù)，而不需要修改表結(jié)構(gòu)或應(yīng)用程序代碼。

壞處

1. 性能開銷：

   • VIRTUAL 虛擬列的值在每次查詢時(shí)動(dòng)態(tài)計(jì)算，這可能會(huì)增加查詢的計(jì)算開銷，尤其是在表達(dá)式復(fù)雜或數(shù)據(jù)量大的情況下。雖然 STORED 虛擬列的值是預(yù)先計(jì)算并存儲(chǔ)的，但在插入或更新數(shù)據(jù)時(shí)會(huì)有額外的計(jì)算和存儲(chǔ)開銷。

2. 存儲(chǔ)空間：

   • STORED 虛擬列的值是實(shí)際存儲(chǔ)在表中的，因此會(huì)增加表的存儲(chǔ)空間。如果虛擬列的計(jì)算結(jié)果較大或表中有大量數(shù)據(jù)，這可能會(huì)導(dǎo)致存儲(chǔ)需求顯著增加。

3. 復(fù)雜性增加：

   • 虛擬列的定義可能會(huì)增加表結(jié)構(gòu)的復(fù)雜性，尤其是在定義復(fù)雜的表達(dá)式時(shí)。這可能會(huì)使表的設(shè)計(jì)和維護(hù)變得更加困難。

4. 兼容性問題：

   • 虛擬列是 MySQL 5.7 引入的特性，因此在較舊的 MySQL 版本中無法使用。如果你的應(yīng)用程序需要兼容舊版本的 MySQL，使用虛擬列可能會(huì)導(dǎo)致兼容性問題。

5. 索引限制：

   • 雖然虛擬列可以被索引，但并不是所有的表達(dá)式都支持索引。某些復(fù)雜的表達(dá)式可能無法創(chuàng)建索引，這可能會(huì)限制虛擬列在查詢優(yōu)化中的應(yīng)用。

總結(jié)

虛擬列在 MySQL 5.7 中提供了強(qiáng)大的功能，可以簡(jiǎn)化查詢、提高數(shù)據(jù)一致性并減少冗余。然而，它們也可能帶來性能開銷、存儲(chǔ)空間增加和復(fù)雜性提升等問題。在使用虛擬列時(shí)，需要根據(jù)具體的業(yè)務(wù)需求和性能要求進(jìn)行權(quán)衡，確保其帶來的好處大于潛在的缺點(diǎn)。

到此這篇關(guān)于MySQL中l(wèi)ike的模糊查詢優(yōu)化以及虛擬列功能詳解的文章就介紹到這了,更多相關(guān)MySQL like模糊查詢優(yōu)化內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論