MySQL慢查詢優(yōu)化 | 聯(lián)結原理
點擊上方藍色字體，選擇“設為星標”
前段時間筆者開發(fā)某個項目遇到了MySQL性能問題，每張表的數(shù)據(jù)量都在五千萬以上，個別表數(shù)據(jù)量甚至在一個億以上，在開發(fā)的過程中遇到了非常多的數(shù)據(jù)庫性能優(yōu)化難點，筆者在開發(fā)過程中查詢了很多資料，很多查詢語句也在優(yōu)化過程中取得了比較好的效果。筆者也將開發(fā)過程中遇到的sql優(yōu)化問題總結為文章，以便日后回顧。這篇文章主要講解mysql執(zhí)行聯(lián)結運算的原理。為了避免泄露公司業(yè)務及數(shù)據(jù)，在文章中涉及的sql語句都和公司業(yè)務無關。
1. Simple Nested Loop Join
在聯(lián)接計算時候，Mysql會以某張表作為驅動表，利用驅動表中的每一條數(shù)據(jù)到關聯(lián)表中根據(jù)聯(lián)接條件查詢數(shù)據(jù)，如下圖r表聯(lián)結s表，mysql會以r表中每一條數(shù)據(jù)關聯(lián)計算s表。這種聯(lián)結查詢開銷很大，如果r表有M條數(shù)據(jù)，s表有N條數(shù)據(jù)，那么匹配總數(shù)是MN次。這種關聯(lián)方式性能最差，尤其是深分頁聯(lián)結計算。
2. Index Nested Loop Join
在A表關聯(lián)B表的時候，如果B表的關聯(lián)字段上存在索引，mysql就會在索引上判斷聯(lián)接條件，如果聯(lián)接條件滿足，那么就從索引列拿到rowid，然后回表查找想要的列，回表數(shù)據(jù)查找是隨機IO的過程，并且每次只能對一個rowid進行回表查找數(shù)據(jù)。在談Index Nested Loop Join回表查詢優(yōu)化之前，筆者先了解了下MRR機制，看如下sql語句，其中price列存在索引
select * from tb_book_base where price > 15 limit 1000,20;
Mysql在執(zhí)行上述語句的時候，首先會根據(jù)索引列獲取rowid，再根據(jù)rowid回表查詢基礎信息，這樣查詢和Index Nested Loop Join一樣存在回表隨機IO的問題，mysql的MRR機制可以優(yōu)化性能，原理如下：
Mysql根據(jù)price列條件從索引列拿到rowid后不立即回表查找數(shù)據(jù)行，而是緩存在一個buffer，當緩存的buffer rowid達到一定數(shù)量的時候，再進行回表，回表之前我們將buffer中rowid先進行排序，如此一來我們就能將完全的隨機IO優(yōu)化為順序跳躍IO，因為順序跳躍IO仍然有機會能利用操作系統(tǒng)預讀的磁盤塊，所以性能要優(yōu)于隨機IO。MySQL中這種優(yōu)化方式稱為MRR，其中的rowid buffer稱為read_rnd_buffer。如果我們需要使用mrr機制，需要將mrr_cost_based參數(shù)設置為false
如果mysql使用了MRR機制會在Extra列中顯示該信息
但是遺憾的是MRR機制根據(jù)索引列查詢必須是范圍查詢<，>，between，因為只有范圍查詢才能獲取一批rowid，筆者也想到如果獲取價格為15元的圖書信息(=查詢)，因為價格為15元的圖書不止一本，也能獲取一批rowid，mysql是否還能使用MRR機制呢，筆者進行了實驗，發(fā)現(xiàn)mysql并沒有使用MRR機制
只能對索引進行范圍查找是MRR的缺陷，于是又有了BKA優(yōu)化，batch key access join是mysql 5.6提出優(yōu)化方案，它在關聯(lián)查詢中獲取一批rowid，然后將這批rowid進行排序，再回表查找，它將隨機IO轉換成了順序跳躍IO。舉一個具體的例子，如果R表關聯(lián)S表， 并且mysql選擇了R表作為驅動表，如果想利用BKA優(yōu)化，關鍵在于選取一批rowid，mysql會選取R表中一批數(shù)據(jù)行存放在join_buffer，然后利用索引查到S表rowid關聯(lián)S表，獲取到一批S表rowid，將rowid排序再回S表查詢。BKA算法默認關閉，需要通過如下參數(shù)打開
比如我們執(zhí)行如下sql，查詢某本書的相關的作者，翻譯，出版社信息，如果使用BKA算法，那么在Extra列中會顯示
3. Blocked Nested Loop Join
存在索引的時候，mysql會使用Index Nested Loop Join，但是有時候關聯(lián)表的關聯(lián)列可能不存在索引，此種情況下，如果Mysql使用Simple Nested Loop Join每次都用一條驅動表數(shù)據(jù)與關聯(lián)表比對計算，那么性能會非常差。Mysql針對不存在索引的情況進行了一些優(yōu)化。優(yōu)化算法如下：mysql將驅動表的關聯(lián)字段列緩存起來(避免回表查找)，放在join buffer當中，然后批量與關聯(lián)表每條數(shù)據(jù)比較，需要注意的是mysql在執(zhí)行Blocked Buffer Join的時候，mysql不僅僅將驅動表的關聯(lián)列放到join buffer中，同時也會將select列放到join buffer中，目的是避免回表查找以提高性能。下圖概述了上述Blocked Nested Loop Join原理
如果mysql使用了join buffer，explain命令的Extra列會顯示該信息
Join Buffer 大小也可以通過如下命令查看
SHOW VARIABLES LIKE '%join_buffer_size%';3.1 缺陷
磁盤IO是性能殺手，一次磁盤IO需要接近10ms(5ms尋道時間，4ms旋轉時間)。為了加快查詢速度，避免磁盤IO，MySQL中有緩存熱數(shù)據(jù)頁的Buffer Poll。內(nèi)存命中率是評估buffer pool的重要指標。有資料顯示一個穩(wěn)定服務的線上系統(tǒng)，要保證響應時間符合要求的話，內(nèi)存命中率需要達到99%。我們可以通過如下命令查看內(nèi)存命中率，其中buffer pool hit 表示內(nèi)存命中率。
Show engine innodb status
BNL確實能夠優(yōu)化聯(lián)結查詢，但是BNL算法會掃描非常多關聯(lián)表的行數(shù)，如果BNL算法多次掃描一個大的關聯(lián)冷表，冷數(shù)據(jù)頁會不斷的加載進入Join Buffer，因為Join Buffer大小也是有限制的，那么熱數(shù)據(jù)頁就會被替換出Join Buffer，所以大冷表BNL 聯(lián)結不僅僅會加大磁盤IO壓力，同時BNL會對Buffer Pool造成持續(xù)性影響，要想提高內(nèi)存命中率只能依靠后續(xù)的請求慢慢恢復。
4. MySQL不支持的Join
除了上述的三種join 方式，還有Hash Join和Merge Join兩種常用的Join方式，但是MySQL并不支持這兩種Join方式，筆者學習之后，覺得也有必要將資料進行整理，以便日后學習回顧。
4.1 Hash Join
看到Hash Join筆者想起了HashSet，HashSet可以在O(1)時間內(nèi)判斷某個值是否存在于集合中，原理是利用hashCode定位到指定的哈希桶，發(fā)生碰撞時，再取出鏈表逐一比對(碰撞時時間復雜度降為O(N))。Hash Join利用的也是相似的原理，比如R表聯(lián)結S表，mysql會選取較小的表的關聯(lián)鍵join key在內(nèi)存中建立散列表，然后利用大表中的每一條記錄探測散列表。但是內(nèi)存可能會不足，不能完全放下小表散列鍵，數(shù)據(jù)庫會利用一個hash函數(shù)將R表和S表分割成不同的分區(qū)，比如R表被切分為R1，R2，R3，S表被切分為S1，S2，S3，然后R1和S1進行Hash Join，R2和S2進行Hash Join。需要注意的是Hash Join只能用于等值聯(lián)結，而且hash join會涉及到散列函數(shù)會CPU的消耗會比較高，內(nèi)存占用也會比較高，并且hash是無序的，所以輸出結果也是無序的。
4.2 Merge Join
Merge Join只能對已經(jīng)排過序的Join Key操作(索引)，如果未排序，那么會先排序再操作，未排序時，開銷會很大。舉個例子如果R表的Join Key為[a，b，c，d，e]，S表的Join Key為[a，c，d，e，f]，數(shù)據(jù)庫執(zhí)行Merge Join時會隨機選擇一張表作為驅動表，如果選擇了R表作為驅動表，當掃描到S表join key 為a的時候則匹配，然后當掃描S表join key = c的時候，再以S表=c處作為驅動表掃描R表，此時會從R表的值為b的位置開始掃描，Merge Join整體時間復雜度是O(max(n1，n2))。注n1,n2分別為R表S表的長度
5. 驅動表優(yōu)化
文末筆者還想談一談表聯(lián)結中驅動表。Mysql聯(lián)接優(yōu)化的目標是盡可能減少nested loop join 總數(shù)，關聯(lián)查詢時候，必須以某張表作為驅動表。我們可以使用explain命令查看mysql到底使用了哪張表作為驅動表？Explain第一行顯示的表就是驅動表。
mysql是如何選擇驅動表的呢？mysql查詢優(yōu)化器優(yōu)先選擇小表作為驅動表，小表不僅僅指表的真實行數(shù)或者磁盤空間大小，也包括了兩個表按照各自條件過濾后的表。比如上圖，mysql使用tb_book_main作為驅動表，tb_book_main有4000萬行數(shù)據(jù)，tb_book_base只有1000萬行數(shù)據(jù)，但是mysql使用了tb_book_main作為驅動表，原因就在于mysql是用過濾條件后的結果集判斷表大小，經(jīng)過`code`= '0413558924'條件比對后的tb_book_main表小于tb_book_base。
但是mysql查詢優(yōu)化器有時候會判斷出錯，選擇了大表作為驅動表?；蛘咭驗榱硗庖环N情況，MySQL使用了小表作為驅動表，但是索引位于大表上，這可能會造成using temporary，file sort的情況，看如下這個實例
SELECT
t1.`isbn`,
`title`,
`sub_title`
FROM
`tb_book_base` t1
INNER join
`tb_book_main` t2 ON t1.`isbn` = t2.`isbn`
WHERE
t2.`code` = ( SELECT `code` FROM tb_book_press WHERE `name` = '6215981117' )
AND t1.publish_time > 20150101
ORDER BY
t1.`publish_time`;上述這個例子，mysql判斷經(jīng)過條件篩選之后的t2表要比t1表小，所以mysql選擇t2表作為驅動表，因為mysql只會使用驅動表上的索引，索引mysql不會使用t1表的publish_time索引，這會造成磁盤排序，sql語句的性能會很差，我們可以通過Explain證實上述的判斷
如果我們需要使用大表上的索引幫助排序，所以我們需要手動指定驅動表。我們可以通STRAIGHT_JOIN語法
SELECT
t1.`isbn`,
`title`,
`sub_title`
FROM
`tb_book_base` t1 FORCE INDEX (`publish_time_idx`)
STRAIGHT_JOIN `tb_book_main` t2 ON t1.`isbn` = t2.`isbn`
WHERE
t2.`code` = ( SELECT `code` FROM tb_book_press WHERE `name` = '6215981117' )
AND t1.publish_time > 20150101
ORDER BY
t1.`publish_time`;
版權聲明：
文章不錯？點個【在看】吧！??