在開(kāi)發(fā)過(guò)程中，經(jīng)常會(huì)碰到一些業(yè)務(wù)場(chǎng)景，需要以完全模糊匹配的方式查找數(shù)據(jù)，就會(huì)想到用 like %xxx% 或者 like %xxx 的方式去實(shí)現(xiàn)，而且即使列上有選擇率很高的索引，也不會(huì)被使用。

在MySQL中可以通過(guò)ICP特性，全文索引，基于生成列索引解決這類(lèi)問(wèn)題，下面就從索引條件下推ICP，全文索引，基于生成列索引及如何利用它們解決模糊匹配的SQL性能問(wèn)題。

索引條件下推ICP

ICP介紹

MySQL 5.6開(kāi)始支持ICP(Index Condition Pushdown)，不支持ICP之前，當(dāng)進(jìn)行索引查詢時(shí)，首先根據(jù)索引來(lái)查找數(shù)據(jù)，然后再根據(jù)where條件來(lái)過(guò)濾，掃描了大量不必要的數(shù)據(jù)，增加了數(shù)據(jù)庫(kù)IO操作。

在支持ICP后，MySQL在取出索引數(shù)據(jù)的同時(shí)，判斷是否可以進(jìn)行where條件過(guò)濾，將where的部分過(guò)濾操作放在存儲(chǔ)引擎層提前過(guò)濾掉不必要的數(shù)據(jù)，減少了不必要數(shù)據(jù)被掃描帶來(lái)的IO開(kāi)銷(xiāo)。

在某些查詢下，可以減少Server層對(duì)存儲(chǔ)引擎層數(shù)據(jù)的讀取，從而提供數(shù)據(jù)庫(kù)的整體性能。

ICP具有以下特點(diǎn)

 ICP相關(guān)控制參數(shù)

index_condition_pushdown：索引條件下推默認(rèn)開(kāi)啟，設(shè)置為off關(guān)閉ICP特性。

mysql>show variables like 'optimizer_switch'; 
| optimizer_switch | index_condition_pushdown=on 
# 開(kāi)啟或者關(guān)閉ICP特性 
mysql>set optimizer_switch = 'index_condition_pushdown=on | off'; 

ICP處理過(guò)程

假設(shè)有用戶表users01(id, name, nickname, phone, create_time)，表中數(shù)據(jù)有11W。由于ICP只能用于二級(jí)索引，故在name,nickname列上創(chuàng)建復(fù)合索引idx_name_nickname(name,nickname)，分析SQL語(yǔ)句select * from users01 where name = 'Lyn' and nickname like '%SK%'在ICP關(guān)閉和開(kāi)啟下的執(zhí)行情況。

關(guān)閉ICP特性的SQL性能分析

 開(kāi)啟profiling進(jìn)行跟蹤SQL執(zhí)行期間每個(gè)階段的資源使用情況。

mysql>set profiling  = 1; 

關(guān)閉ICP特性分析SQL執(zhí)行情況

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執(zhí)行期間各階段的資源使用 
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 |#執(zhí)行階段耗時(shí)0.035773秒。 
| end                            | 0.000015 | 0.000006 |   0.000009 |            0 |             0 | 
#status狀態(tài)變量分析 
| Handler_read_next | 16384          |  ##請(qǐng)求讀的行數(shù) 
| Innodb_data_reads | 2989           |  #數(shù)據(jù)物理讀的總數(shù) 
| Innodb_pages_read | 2836           |  #邏輯讀的總數(shù) 
| Last_query_cost   | 8580.324460    |  #SQL語(yǔ)句的成本COST，主要包括IO_COST和CPU_COST。 

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

Handler_read_next=16384說(shuō)明掃描了16384行的數(shù)據(jù)，SQL實(shí)際返回只有12行數(shù)，耗時(shí)50ms。對(duì)于這種掃描大量數(shù)據(jù)行，只返回少量數(shù)據(jù)的SQL，可以從兩個(gè)方面去分析。

1.索引選擇率低：對(duì)于符合索引(name,nickname)，name作為前導(dǎo)列出現(xiàn) where 條件，CBO都會(huì)選擇走索引，因?yàn)閽呙杷饕热頀呙璧腃OST要小，但由于 name 列的基數(shù)不高，導(dǎo)致掃描了索引中大量的數(shù)據(jù)，導(dǎo)致SQL性能也不太高。

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

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

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

在MySQL 8.0推出了列的直方圖統(tǒng)計(jì)信息特性，主要針對(duì)索引列數(shù)據(jù)分布不均勻的情況進(jìn)行優(yōu)化。

開(kāi)啟ICP特性的性能分析 

 開(kāi)啟ICP特性分析SQL執(zhí)行情況 

mysql>set optimizer_switch = 'index_condition_pushdown=on'; 

#執(zhí)行計(jì)劃 
|  1 | SIMPLE      | users01 | NULL       | ref  | idx_name_nickname | idx_name_nickname | 82      | const | 29016 |    11.11 | Using index condition | 
#status狀態(tài)變量分析 
| Handler_read_next | 12             | 
| Innodb_data_reads | 2989           | 
| Innodb_pages_read | 2836           | 
| Last_query_cost   | 8580.324460    | 

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

開(kāi)啟ICP特性后，由于 nickname 的 like 條件可以通過(guò)索引篩選，存儲(chǔ)引擎層通過(guò)索引與 where 條件的比較來(lái)去除不符合條件的記錄，這個(gè)過(guò)程不需要讀取記錄，同時(shí)只返回給Server層篩選后的記錄，減少不必要的IO開(kāi)銷(xiāo)。

Extra顯示的索引掃描方式

• using where：查詢使用索引的情況下，需要回表去查詢所需的數(shù)據(jù)。
• using index condition：查詢使用了索引，但是需要回表查詢數(shù)據(jù)。
• using index：查詢使用覆蓋索引的時(shí)候會(huì)出現(xiàn)。
• using index & using where：查詢使用了索引，但是需要的數(shù)據(jù)都在索引列中能找到，不需要回表查詢數(shù)據(jù)。

模糊匹配改寫(xiě)優(yōu)化

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

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

從執(zhí)行計(jì)劃看到 type=ALL，Extra=Using where 走的是全部掃描，沒(méi)有利用到ICP特性。

輔助索引idx_nickname(nickname)內(nèi)部是包含主鍵id的，等價(jià)于(id，nickname)的復(fù)合索引，嘗試?yán)酶采w索引特性將SQL改寫(xiě)為 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 | 

從執(zhí)行計(jì)劃看到，type=index，Extra=Using where; Using index，索引全掃描，但是需要的數(shù)據(jù)都在索引列中能找到，不需要回表。利用這個(gè)特點(diǎn)，將原始的SQL語(yǔ)句先獲取主鍵id，然后通過(guò)id跟原表進(jìn)行關(guān)聯(lián)，分析其執(zhí)行計(jì)劃。

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                     | 

從執(zhí)行計(jì)劃看，走了索引idx_nickname，不需要回表訪問(wèn)數(shù)據(jù)，執(zhí)行時(shí)間從60ms降低為40ms，type = index 說(shuō)明沒(méi)有用到ICP特性，但是可以利用 Using where; Using index 這種索引掃描不回表的方式減少資源開(kāi)銷(xiāo)來(lái)提升性能。

全文索引

MySQL 5.6開(kāi)始支持全文索引，可以在變長(zhǎng)的字符串類(lèi)型上創(chuàng)建全文索引，來(lái)加速模糊匹配業(yè)務(wù)場(chǎng)景的DML操作。它是一個(gè)inverted index(反向索引)，創(chuàng)建 fulltext index 時(shí)會(huì)自動(dòng)創(chuàng)建6個(gè) auxiliary index tables(輔助索引表)，同時(shí)支持索引并行創(chuàng)建，并行度可以通過(guò)參數(shù) innodb_ft_sort_pll_degree 設(shè)置，對(duì)于大表可以適當(dāng)增加該參數(shù)值。

刪除全文索引的表的數(shù)據(jù)時(shí)，會(huì)導(dǎo)致輔助索引表大量delete操作，InnoDB內(nèi)部采用標(biāo)記刪除，將已刪除的DOC_ID都記錄特殊的FTS_*_DELETED表中，但索引的大小不會(huì)減少，需要通過(guò)設(shè)置參數(shù)innodb_optimize_fulltext_only=ON 后，然后運(yùn)行OPTIMIZE TABLE來(lái)重建全文索引。

全文索引特征

兩種檢索模式

• IN NATURAL LANGUAGE MODE：默認(rèn)模式，以自然語(yǔ)言的方式搜索，AGAINST('看風(fēng)' IN NATURAL LANGUAGE MODE ) 等價(jià)于AGAINST('看風(fēng)')。
• IN BOOLEAN MODE：布爾模式，表是字符串前后的字符有特殊含義，如查找包含SK，但不包含Lyn的記錄，可以用+，-符號(hào)。

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

這時(shí)查找 nickname like '%Lyn%' ，通過(guò)反向索引關(guān)聯(lián)數(shù)組可以知道，單詞Lyn存儲(chǔ)于文檔4中，然后定位到具體的輔助索引表中。

全文索引分析

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

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

查看數(shù)據(jù)分布

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

全文索引相關(guān)對(duì)象分析

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

模糊匹配優(yōu)化

對(duì)于SQL語(yǔ)句后面的條件 nickname like '%看風(fēng)%' 默認(rèn)情況下，CBO是不會(huì)選擇走nickname索引的，該寫(xiě)SQL為全文索引匹配的方式：match(nickname) against('看風(fēng)')。

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

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

生成列

MySQL 5.7開(kāi)始支持生成列，生成列是由表達(dá)式的值計(jì)算而來(lái)，有兩種模式：VIRTUAL和STORED，如果不指定默認(rèn)是VIRTUAL，創(chuàng)建語(yǔ)法如下：

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

生成列特征

• VIRTUAL生成列用于復(fù)雜的條件定義，能夠簡(jiǎn)化和統(tǒng)一查詢，不占用空間，訪問(wèn)列是會(huì)做計(jì)算。
• STORED生成列用作物化緩存，對(duì)于復(fù)雜的條件，可以降低計(jì)算成本，占用磁盤(pán)空間。
• 支持輔助索引的創(chuàng)建，分區(qū)以及生成列可以模擬函數(shù)索引。
• 不支持存儲(chǔ)過(guò)程，用戶自定義函數(shù)的表達(dá)式，NONDETERMINISTIC的內(nèi)置函數(shù)，如NOW()， RAND()以及不支持子查詢

生成列使用

#添加基于函數(shù)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 | #虛擬生成列 

模糊匹配優(yōu)化

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

1. 利用內(nèi)置reverse函數(shù)將like '%風(fēng)云'反轉(zhuǎn)為like '云風(fēng)%'，基于此函數(shù)添加虛擬生成列。
2. 在虛擬生成列上創(chuàng)建索引。
3. 將SQL改寫(xiě)成通過(guò)生成列l(wèi)ike reverse('%風(fēng)云')去過(guò)濾，走生成列上的索引。

添加虛擬生成列并創(chuàng)建索引。

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執(zhí)行計(jì)劃 
|  1 | SIMPLE      | users01 | NULL       | range | idx_reverse_nickname | idx_reverse_nickname | 803     | NULL |    1 |   100.00 | Using where | 

可以看到對(duì)于 like '%xxx' 無(wú)法使用索引的場(chǎng)景，可以通過(guò)基于生成列的索引方式解決。

總結(jié)

介紹了索引條件下推ICP特性，全文索引以以及生成列特性，利用這些特性可以對(duì)模糊匹配 like %xxx% 或 like %xxx 的業(yè)務(wù)SQL進(jìn)行優(yōu)化，可以有效降低不必要的數(shù)據(jù)讀取，減少I(mǎi)O掃描以及CPU開(kāi)銷(xiāo)，提高服務(wù)的穩(wěn)定性。

對(duì)于MySQL每個(gè)版本發(fā)布的新特性，尤其是跟優(yōu)化器和SQL相關(guān)的，應(yīng)該去關(guān)注和了解，可能會(huì)發(fā)現(xiàn)適合自己業(yè)務(wù)場(chǎng)景的特性。

我是敖丙，你知道的越多，你不知道的越多，我們下期見(jiàn)。