怎么優化模糊匹配Like %xxx%

本篇內容介紹了“怎么優化模糊匹配Like %xxx%”的有關知識，在實際案例的操作過程中，不少人都會遇到這樣的困境，接下來就讓小編帶領大家學習一下如何處理這些情況吧！希望大家仔細閱讀，能夠學有所成！

索引條件下推ICP

ICP介紹

MySQL 5.6開始支持ICP(Index Condition  Pushdown)，不支持ICP之前，當進行索引查詢時，首先根據索引來查找數據，然后再根據where條件來過濾，掃描了大量不必要的數據，增加了數據庫IO操作。

在支持ICP后，MySQL在取出索引數據的同時，判斷是否可以進行where條件過濾，將where的部分過濾操作放在存儲引擎層提前過濾掉不必要的數據，減少了不必要數據被掃描帶來的IO開銷。

在某些查詢下，可以減少Server層對存儲引擎層數據的讀取，從而提供數據庫的整體性能。

ICP具有以下特點

ICP相關控制參數

index_condition_pushdown：索引條件下推默認開啟，設置為off關閉ICP特性。

mysql>show variables like 'optimizer_switch'; | optimizer_switch | index_condition_pushdown=on # 開啟或者關閉ICP特性 mysql>set optimizer_switch = 'index_condition_pushdown=on | off';

ICP處理過程

假設有用戶表users01(id, name, nickname, phone,  create_time)，表中數據有11W。由于ICP只能用于二級索引，故在name,nickname列上創建復合索引idx_name_nickname(name,nickname)，分析SQL語句select  * from users01 where name = 'Lyn' and nickname like '%SK%'在ICP關閉和開啟下的執行情況。

關閉ICP特性的SQL性能分析

開啟profiling進行跟蹤SQL執行期間每個階段的資源使用情況。

mysql>set profiling  = 1;

關閉ICP特性分析SQL執行情況

mysql>set optimizer_switch = 'index_condition_pushdown=off'; 

mysql>explain select * from users01 where name = 'Lyn' and nickname like '%SK%'; |  1 | SIMPLE      | users01 | NULL       | ref  | idx_name_nickname | idx_name_nickname | 82      | const | 29016 |   100.00 | Using where | #查看SQL執行期間各階段的資源使用 mysql>show profile cpu,block io for query 2; | Status                         | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out | +--------------------------------+----------+----------+------------+--------------+---------------+ | starting                       | 0.000065 | 0.000057 |   0.000009 |            0 |             0 | .................. | executing                      | 0.035773 | 0.034644 |   0.000942 |            0 |             0 |#執行階段耗時0.035773秒。 | end                            | 0.000015 | 0.000006 |   0.000009 |            0 |             0 | #status狀態變量分析 | Handler_read_next | 16384          |  ##請求讀的行數 | Innodb_data_reads | 2989           |  #數據物理讀的總數 | Innodb_pages_read | 2836           |  #邏輯讀的總數 | Last_query_cost   | 8580.324460    |  #SQL語句的成本COST，主要包括IO_COST和CPU_COST。

通過explain分析執行計劃，SQL語句在關閉CP特性的情況下，走的是復合索引idx_name_nickname，Extra=Using  Where，首先通過復合索引 idx_name_nickname 前綴從存儲引擎中讀出 name = 'Lyn' 的所有記錄，然后在Server端用where  過濾 nickname like '%SK%' 情況。

Handler_read_next=16384說明掃描了16384行的數據，SQL實際返回只有12行數，耗時50ms。對于這種掃描大量數據行，只返回少量數據的SQL，可以從兩個方面去分析。

1.索引選擇率低：對于符合索引(name,nickname)，name作為前導列出現 where  條件，CBO都會選擇走索引，因為掃描索引比全表掃描的COST要小，但由于 name 列的基數不高，導致掃描了索引中大量的數據，導致SQL性能也不太高。

Column_name: name Cardinality: 6  可以看到users01表中name的不同的值只有6個，選擇率6/114688很低。

2.數據分布不均勻：對于where name =  ?來說，name數據分布不均勻時，SQL第一次傳入的值返回結果集很小，CBO就會選擇走索引，同時將SQL的執行計劃緩存起來，以后不管name傳入任何值都會走索引掃描，這其實是不對的，如果傳入name的值是Fly100返回表中80%的數據，這是走全表掃描更快。

| name      | count(*) | +---------------+----------+ | Grubby        |    12    | | Lyn           |    1000  | | Fly100        |    98100 |

在MySQL 8.0推出了列的直方圖統計信息特性，主要針對索引列數據分布不均勻的情況進行優化。

開啟ICP特性的性能分析

開啟ICP特性分析SQL執行情況

mysql>set optimizer_switch = 'index_condition_pushdown=on'; 

#執行計劃 |  1 | SIMPLE      | users01 | NULL       | ref  | idx_name_nickname | idx_name_nickname | 82      | const | 29016 |    11.11 | Using index condition | #status狀態變量分析 | Handler_read_next | 12             | | Innodb_data_reads | 2989           | | Innodb_pages_read | 2836           | | Last_query_cost   | 8580.324460    |

從執行計劃可以看出，走了復合索引 idx_name_nickname，Extra=Using index  condition，且只掃描了12行數據，說明使用了索引條件下推ICP特性，SQL總共耗時10ms，跟關閉ICP特性下相比，SQL性能提升了5倍。

開啟ICP特性后，由于 nickname 的 like 條件可以通過索引篩選，存儲引擎層通過索引與 where  條件的比較來去除不符合條件的記錄，這個過程不需要讀取記錄，同時只返回給Server層篩選后的記錄，減少不必要的IO開銷。

Extra顯示的索引掃描方式

• using where：查詢使用索引的情況下，需要回表去查詢所需的數據。

• using index condition：查詢使用了索引，但是需要回表查詢數據。

• using index：查詢使用覆蓋索引的時候會出現。

• using index & using where：查詢使用了索引，但是需要的數據都在索引列中能找到，不需要回表查詢數據。

模糊匹配改寫優化

在開啟ICP特性后，對于條件where name = 'Lyn' and nickname like '%SK%' 可以利用復合索引  (name,nickname) 減少不必要的數據掃描，提升SQL性能。但對于 where nickname like  '%SK%'完全模糊匹配查詢能否利用ICP特性提升性能?首先創建nickname上單列索引 idx_nickname。

mysql>alter table users01 add index idx_nickname(nickname); #SQL執行計劃 |  1 | SIMPLE      | users01 | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 114543 |    11.11 | Using where |

從執行計劃看到 type=ALL，Extra=Using where 走的是全部掃描，沒有利用到ICP特性。

輔助索引idx_nickname(nickname)內部是包含主鍵id的，等價于(id，nickname)的復合索引，嘗試利用覆蓋索引特性將SQL改寫為  select Id from users01 where nickname like '%SK%' 。

|  1 | SIMPLE      | users01 | NULL       | index | NULL          | idx_nickname | 83      | NULL | 114543 |    11.11 | Using where; Using index |

從執行計劃看到，type=index，Extra=Using where; Using  index，索引全掃描，但是需要的數據都在索引列中能找到，不需要回表。利用這個特點，將原始的SQL語句先獲取主鍵id，然后通過id跟原表進行關聯，分析其執行計劃。

select  * from users01 a , (select id from users01 where nickname like '%SK%') b where a.id = b.id; 

|  1 | SIMPLE      | users01 | NULL       | index  | PRIMARY       | idx_nickname | 83      | NULL            | 114543 |    11.11 | Using where; Using index | |  1 | SIMPLE      | a       | NULL       | eq_ref | PRIMARY       | PRIMARY      | 4       | test.users01.id |      1 |   100.00 | NULL                     |

從執行計劃看，走了索引idx_nickname，不需要回表訪問數據，執行時間從60ms降低為40ms，type = index  說明沒有用到ICP特性，但是可以利用 Using where; Using index 這種索引掃描不回表的方式減少資源開銷來提升性能。

全文索引

MySQL 5.6開始支持全文索引，可以在變長的字符串類型上創建全文索引，來加速模糊匹配業務場景的DML操作。它是一個inverted  index(反向索引)，創建 fulltext index 時會自動創建6個 auxiliary index  tables(輔助索引表)，同時支持索引并行創建，并行度可以通過參數 innodb_ft_sort_pll_degree  設置，對于大表可以適當增加該參數值。

刪除全文索引的表的數據時，會導致輔助索引表大量delete操作，InnoDB內部采用標記刪除，將已刪除的DOC_ID都記錄特殊的FTS_*_DELETED表中，但索引的大小不會減少，需要通過設置參數innodb_optimize_fulltext_only=ON  后，然后運行OPTIMIZE TABLE來重建全文索引。

全文索引特征

兩種檢索模式

• IN NATURAL LANGUAGE MODE：默認模式，以自然語言的方式搜索，AGAINST('看風' IN NATURAL LANGUAGE  MODE ) 等價于AGAINST('看風')。

• IN BOOLEAN MODE：布爾模式，表是字符串前后的字符有特殊含義，如查找包含SK，但不包含Lyn的記錄，可以用+，-符號。

AGAINST('+SK -Lyn' in BOOLEAN MODE);

這時查找 nickname like '%Lyn%' ，通過反向索引關聯數組可以知道，單詞Lyn存儲于文檔4中，然后定位到具體的輔助索引表中。

全文索引分析

對表users01的nickname添加支持中文分詞的全文索引

mysql>alter table users01 add fulltext index idx_full_nickname(nickname) with parser ngram;

查看數據分布

#設置當前的全文索引表 mysql>set global innodb_ft_aux_table = 'test/users01'; #查看數據文件 mysql>select * from information_schema.innodb_ft_index_cache; +--------+--------------+-------------+-----------+--------+----------+ | WORD   | FIRST_DOC_ID | LAST_DOC_ID | DOC_COUNT | DOC_ID | POSITION | +--------+--------------+-------------+-----------+--------+----------+ ............. | 看風   |            7 |           7 |         1 |      7 |        3 | | 笑看   |            7 |           7 |         1 |      7 |        0 |

全文索引相關對象分析

#全文索引對象分析 mysql>SELECT table_id, name, space from INFORMATION_SCHEMA.INNODB_TABLES where name like 'test/%'; |     1198 | test/users01                                       |   139 | #存儲被標記刪除同時從索引中清理的文檔ID,其中_being_deleted_cache是_being_deleted表的內存版本。 |     1199 | test/fts_00000000000004ae_being_deleted            |   140 | |     1200 | test/fts_00000000000004ae_being_deleted_cache      |   141 | #存儲索引內部狀態信息及FTS_SYNCED_DOC_ID |     1201 | test/fts_00000000000004ae_config                   |   142 |  #存儲被標記刪除但沒有從索引中清理的文檔ID,其中_deleted_cache是_deleted表的內存版本。 |     1202 | test/fts_00000000000004ae_deleted                  |   143 | |     1203 | test/fts_00000000000004ae_deleted_cache            |   144 |

模糊匹配優化

對于SQL語句后面的條件 nickname like '%看風%'  默認情況下，CBO是不會選擇走nickname索引的，該寫SQL為全文索引匹配的方式：match(nickname) against('看風')。

mysql>explain select * from users01 where match(nickname) against('看風'); |  1 | SIMPLE      | users01 | NULL       | fulltext | idx_full_nickname | idx_full_nickname | 0       | const |    1 |   100.00 | Using where; Ft_hints: sorted |

使用了全文索引的方式查詢，type=fulltext，同時命中全文索引  idx_full_nickname，從上面的分析可知，在MySQL中，對于完全模糊匹配%%查詢的SQL可以通過全文索引提高效率。

生成列

MySQL  5.7開始支持生成列，生成列是由表達式的值計算而來，有兩種模式：VIRTUAL和STORED，如果不指定默認是VIRTUAL，創建語法如下：

col_name data_type [GENERATED ALWAYS] AS (expr)  [**VIRTUAL** | **STORED**] [NOT NULL | NULL]

生成列特征

• VIRTUAL生成列用于復雜的條件定義，能夠簡化和統一查詢，不占用空間，訪問列是會做計算。

• STORED生成列用作物化緩存，對于復雜的條件，可以降低計算成本，占用磁盤空間。

• 支持輔助索引的創建，分區以及生成列可以模擬函數索引。

• 不支持存儲過程，用戶自定義函數的表達式，NONDETERMINISTIC的內置函數，如NOW()， RAND()以及不支持子查詢

生成列使用

#添加基于函數reverse的生成列reverse_nickname mysql>alter table users01 add reverse_nickname varchar(200) generated always as (reverse(nickname)); #查看生成列信息 mysql>show columns from users01; | reverse_nickname | varchar(200) | YES  |     | NULL              | VIRTUAL GENERATED | #虛擬生成列

模糊匹配優化

對于where條件后的 like '%xxx' 是無法利用索引掃描，可以利用MySQL 5.7的生成列模擬函數索引的方式解決，具體步驟如下：

1. 利用內置reverse函數將like '%風云'反轉為like '云風%'，基于此函數添加虛擬生成列。

2. 在虛擬生成列上創建索引。

3. 將SQL改寫成通過生成列like reverse('%風云')去過濾，走生成列上的索引。

添加虛擬生成列并創建索引。

mysql>alter table users01 add reverse_nickname varchar(200) generated always as (reverse(nickname)); mysql>alter table users01 add index idx_reverse_nickname(reverse_nickname); #SQL執行計劃 |  1 | SIMPLE      | users01 | NULL       | range | idx_reverse_nickname | idx_reverse_nickname | 803     | NULL |    1 |   100.00 | Using where |

可以看到對于 like '%xxx' 無法使用索引的場景，可以通過基于生成列的索引方式解決。

“怎么優化模糊匹配Like %xxx%”的內容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業相關的知識可以關注億速云網站，小編將為大家輸出更多高質量的實用文章！