在《MySQL 常見(jiàn)語(yǔ)句加鎖分析》一文中，我們?cè)敿?xì)講解了 SQL 語(yǔ)句的加鎖原理并具體分析了大部分的簡(jiǎn)單 SQL 語(yǔ)句，但是實(shí)際業(yè)務(wù)場(chǎng)景中 SQL 語(yǔ)句往往及其復(fù)雜，包含多個(gè)條件，此時(shí)就需要具體分析SQL 使用到的索引，并了解 where 條件的判斷邏輯。
我們可以直接使用 explain 或者 optimizer_trace 來(lái)分析 SQL 語(yǔ)句執(zhí)行使用了哪些索引，具體使用可以看本系列文章的前兩篇文章。但是，今天我們講一下具體 Where 語(yǔ)句的條件的拆分和使用，即復(fù)雜 Where 條件是如何生效的。
用何登成大神的原話(huà)，就是
給定一條SQL，where條件中的每個(gè)子條件，在SQL執(zhí)行的過(guò)程中有分別起著什么樣的作用？
具體場(chǎng)景
我們使用下面這張 book 表作為實(shí)例，其中 id 為主鍵，ISBN（書(shū)號(hào)）為二級(jí)唯一索引，Author（作者）為二級(jí)非唯一索引，score（評(píng)分）無(wú)索引。
Index Key 和 Table Filter
基于上述表，我們具體分析一下如下?lián)碛袕?fù)雜 Where 條件的 SQL 語(yǔ)句。
mysql> UPDATE book SET score = 9.0 WHERE Author = 'Tom' AND ISBN > 'N0004' AND ISBN < 'N0007';
上述 SQL 語(yǔ)句的 Where 條件使用了兩個(gè)索引，分別是二級(jí)唯一索引 ISBN 和二級(jí)非唯一索引 Author。MySQL 會(huì)根據(jù)索引選擇性等指標(biāo)選擇其中一個(gè)索引來(lái)使用，而另外一個(gè)沒(méi)有被使用的 Where 條件就被當(dāng)做普通的過(guò)濾條件，一般稱(chēng)被用到的索引稱(chēng)為 Index Key，而作為普通過(guò)濾的條件則被稱(chēng)為 Table Filter。比如上面這條SQL 使用 ISBN索引來(lái)查詢(xún)，則 ISBN 就是 Index Key，而 Author = 'Tom' 這個(gè)條件就是 Table Filter。
所以，該 SQL 執(zhí)行的過(guò)程就是依次將 Index Key 范圍內(nèi)的索引記錄讀取，然后回表讀取完整數(shù)據(jù)記錄，然后返回給MySQL的服務(wù)層按照 Table Filter 進(jìn)行過(guò)濾。 至于加鎖，如下圖所示則需要將涉及的 Index Key 對(duì)應(yīng)的索引記錄都進(jìn)行加鎖。
但是當(dāng)使用的索引是復(fù)合索引時(shí)，則還可能出現(xiàn) Index Filter，利用它可以減少回表次數(shù)和返回給 MySQL 服務(wù)層的記錄的數(shù)量，降低存儲(chǔ)引擎和服務(wù)層的交互開(kāi)銷(xiāo)，提高 SQL 的執(zhí)行效率。
Index Filter
假設(shè)我們?cè)?book 表的 ISBN 和 Author 列上建立了聯(lián)合索引，并且上述 SQL 執(zhí)行時(shí)選擇了該復(fù)合索引。
對(duì)于這個(gè)場(chǎng)景，MySQL 依然使用 ISBN > 'N0004' AND ISBN < 'N0007' 條件來(lái)確定 SQL 查詢(xún)?cè)谒饕械倪B續(xù)位置，但是 Author = 'Tom' 可以用來(lái)直接過(guò)濾索引，即該條件可以使用復(fù)合索引來(lái)直接過(guò)濾條件，不需要讀取所有數(shù)據(jù)后由MySQL 服務(wù)層根據(jù) Table Filter 來(lái)過(guò)濾。這就是傳說(shuō)中的 ICP(Index Condition Pushdown，索引下推)技術(shù)，使用 Index Filter 過(guò)濾不滿(mǎn)足條件的記錄，無(wú)需加鎖。
根據(jù) Index Key 判斷查詢(xún)返回和根據(jù) Index Filter 進(jìn)行初步過(guò)濾后，存儲(chǔ)引擎將剩下的數(shù)據(jù)記錄返回給服務(wù)層，再由服務(wù)層根據(jù) Table Filter 進(jìn)行過(guò)濾。
ICP (索引下推)技術(shù)
MySQL 5.6 推出的 ICP 技術(shù)其實(shí)就是 Index Filter 技術(shù)，只不過(guò)是因?yàn)?MySQL 分為服務(wù)層和存儲(chǔ)引擎層，而 Index Filter 將原本服務(wù)層做的過(guò)濾操作“下推”到存儲(chǔ)引擎層處理。將原來(lái)的在服務(wù)層進(jìn)行的Table Filter中可以進(jìn)行Index Filter的部分，在引擎層面使用 Index Filter 進(jìn)行處理，不再需要回表進(jìn)行 Table Filter。
這樣做的好處就是減少了加鎖的記錄數(shù)，減少了回表查詢(xún)的數(shù)量，提高了 SQL 的執(zhí)行效率。
終于要到系列的最后一篇了，下一篇，我們將講解如何根據(jù) MySQL 信息判斷死鎖和解決死鎖。請(qǐng)大家關(guān)注，轉(zhuǎn)發(fā)和點(diǎn)贊三連走起。
