?MySQL中like的模糊查詢如何優化

當然還可以ES等 這里只說mysql怎么搞

典型回答

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

• ●當like值前后都有匹配符時%abc%，無法使用索引

• ●當like值前有匹配符時%abc，無法使用索引

• ●當like值后有匹配符時'abc%'，可以使用索引

那么，like %abc真的無法優化了嗎？

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

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

接下來在查詢的時候，我們就可以使用v_name列進行模糊查詢了

當然這樣看起來有點麻煩，表中如果已經有了很多數據，還需要利用update語句反轉name到v_name中，如果數據量大了（幾百萬或上千萬條記錄）更新一下v_name耗時也比較長，同時也會增大表空間。

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

幸運的是在MySQL5.7.6之后，新增了虛擬列功能（如果不是>=5.7.6，只能用上面的土方法）為一個列建立一個虛擬列，并為虛擬列建立索引，在查詢時where中like條件改為虛擬列，就可以使用索引了。

我們再進行查詢，就會走索引了

當然如果你要查詢like 'abc%'和like '%abc'，你只需要使用一個union

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

虛擬列可以指定為VIRTUAL或STORED，VIRTUAL不會將虛擬列存儲到磁盤中，在使用時MySQL會現計算虛擬列的值，STORED會存儲到磁盤中，相當于我們手動創建的冗余列。所以：如果你的磁盤足夠大，可以使用STORED方式，這樣在查詢時速度會更快一些。

如果你的數據量級較大，不使用反向查詢的方式耗時會非常高。你可以使用如下sql測試虛擬列的效果：

/* 建表 */

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


/* 創建一個存儲過程，向test表中寫入2000000條數據，200條數據中abc字符前包含一些隨機字符（用于測試like '%abc'的情況），200條數據中abc字符后包含一些隨機字符（用于測試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 ;




/* 調用存儲過程，這里執行的會很慢 */

call InsertTestData();



/* 建立虛擬列 */
alter table test add column `v_name` varchar(50) generated always as (reverse(name));
/* 為虛擬列創建索引 */
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），這些列的值是通過表達式計算得出的，而不是直接存儲在表中的。虛擬列可以分為兩種類型：VIRTUAL 和 STORED。以下是虛擬列的好處和壞處：

好處

1. 簡化查詢：

   • 虛擬列可以簡化復雜的查詢，尤其是當查詢中需要頻繁使用某個表達式時。通過將表達式定義為虛擬列，可以直接查詢該列，而不需要在每次查詢時重復計算。

2. 數據一致性：

   • 虛擬列的值是根據其他列的值自動計算的，因此可以確保數據的一致性。如果基礎列的值發生變化，虛擬列的值會自動更新，避免了手動維護數據一致性的麻煩。

3. 減少冗余：

   • 使用虛擬列可以避免存儲冗余數據。例如，如果你需要根據某些列的值計算出一個結果，并且這個結果不需要頻繁更新，可以使用虛擬列來動態計算，而不需要將結果存儲在表中。

4. 索引支持：

   • 虛擬列可以被索引，這可以顯著提高查詢性能。特別是當虛擬列的計算結果經常用于查詢條件時，創建索引可以加速查詢。

5. 靈活性：

   • 虛擬列可以根據需要定義復雜的表達式，提供更高的靈活性。你可以根據業務需求動態生成數據，而不需要修改表結構或應用程序代碼。

壞處

1. 性能開銷：

   • VIRTUAL 虛擬列的值在每次查詢時動態計算，這可能會增加查詢的計算開銷，尤其是在表達式復雜或數據量大的情況下。雖然 STORED 虛擬列的值是預先計算并存儲的，但在插入或更新數據時會有額外的計算和存儲開銷。

2. 存儲空間：

   • STORED 虛擬列的值是實際存儲在表中的，因此會增加表的存儲空間。如果虛擬列的計算結果較大或表中有大量數據，這可能會導致存儲需求顯著增加。

3. 復雜性增加：

   • 虛擬列的定義可能會增加表結構的復雜性，尤其是在定義復雜的表達式時。這可能會使表的設計和維護變得更加困難。

4. 兼容性問題：

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

5. 索引限制：

   • 雖然虛擬列可以被索引，但并不是所有的表達式都支持索引。某些復雜的表達式可能無法創建索引，這可能會限制虛擬列在查詢優化中的應用。

總結

虛擬列在 MySQL 5.7 中提供了強大的功能，可以簡化查詢、提高數據一致性并減少冗余。然而，它們也可能帶來性能開銷、存儲空間增加和復雜性提升等問題。在使用虛擬列時，需要根據具體的業務需求和性能要求進行權衡，確保其帶來的好處大于潛在的缺點。