MySQL中（JOIN/ORDER BY）語(yǔ)句的查詢過(guò)程及優(yōu)化方法

在MySQL查詢語(yǔ)句過(guò)程和EXPLAIN語(yǔ)句基本概念及其優(yōu)化中介紹了EXPLAIN語(yǔ)句，并舉了一個(gè)慢查詢例子：

可以看到上述的查詢需要檢查1萬(wàn)多記錄，并且使用了臨時(shí)表和filesort排序，這樣的查詢?cè)谟脩魯?shù)快速增長(zhǎng)后將成為噩夢(mèng)。

在優(yōu)化這個(gè)語(yǔ)句之前，我們先了解下SQL查詢的基本執(zhí)行過(guò)程：

1.應(yīng)用通過(guò)MySQL API把查詢命令發(fā)送給MySQL服務(wù)器，然后被解析

2.檢查權(quán)限、MySQL optimizer進(jìn)行優(yōu)化，經(jīng)過(guò)解析和優(yōu)化后的查詢命令被編譯為CPU可運(yùn)行的二進(jìn)制形式的查詢計(jì)劃（query plan），并可以被緩存

3.如果存在索引，那么先掃描索引，如果數(shù)據(jù)被索引覆蓋，那么不需要額外的查找，如果不是，根據(jù)索引查找和讀取對(duì)應(yīng)的記錄

4.如果有關(guān)聯(lián)查詢，查詢次序是掃描第一張表找到滿足條件的記錄，按照第一張表和第二張表的關(guān)聯(lián)鍵值，掃描第二張表查找滿足條件的記錄，按此順序循環(huán)

5.輸出查詢結(jié)果，并記錄binary logs

顯然合適的索引將大大簡(jiǎn)化和加速查找。再看一下上面那條查詢語(yǔ)句，除了條件查詢外，還有關(guān)聯(lián)查詢以及ORDER BY即排序操作，

那么讓我們進(jìn)一步了解下關(guān)聯(lián)查詢（JOIN）和ORDER BY是怎么工作的，MySQL有三種方式來(lái)處理關(guān)聯(lián)查詢和數(shù)據(jù)排序：

第一種方法是基于索引，第二種是對(duì)第一個(gè)非常量表進(jìn)行filesort（quicksort），還有一種是把聯(lián)合查詢的結(jié)果放入臨時(shí)表，然后進(jìn)行filesort。

注1：關(guān)于什么是非常量表，請(qǐng)參考閱讀MySQL開發(fā)手冊(cè)：Consts and Constant Tables，
注2：什么是filesort呢，這不是字面意思的文件排序，filesort有兩種模式：
1、模式1：排序后的元素涵蓋了要輸出的數(shù)據(jù)。排序結(jié)果是一串有序序列元素組，不再需要額外的記錄讀取；
2、模式2：排序結(jié)果是sort_key,row_id>鍵值對(duì)序列，通過(guò)這些row_ids再去讀取記錄（隨機(jī)讀取，效率低下）；
注3：關(guān)于什么是臨時(shí)表，請(qǐng)參考閱讀MySQL開發(fā)手冊(cè)：How MySQL Uses Internal Temporary Tables

第一種方法用于第一個(gè)非常量表中存在ORDER BY所依賴的列的索引，那就可直接使用已經(jīng)有序的索引來(lái)查找關(guān)聯(lián)表的數(shù)據(jù)，這種方式是性能最優(yōu)的，因?yàn)椴恍枰~外的排序動(dòng)作：

第二種方式用于ORDER BY所依賴的列全部屬于第一張查詢表且沒(méi)有索引，那么我們可以先對(duì)第一張表的記錄進(jìn)行filesort（模式可能是模式1也可能是模式2），得到有序行索引，然后再做關(guān)聯(lián)查詢，filesort的結(jié)果可能是在內(nèi)存中，也可能在硬盤上，這取決于系統(tǒng)變量sort_buffer_size（一般為2M左右）：

第三種方法用于當(dāng)ORDER BY的元素不屬于第一張表時(shí)，需要把關(guān)聯(lián)查詢的結(jié)果放入臨時(shí)表，最后對(duì)臨時(shí)表進(jìn)行filesort：

第三種方法中的臨時(shí)表，可能是在內(nèi)存中(in-memory table)，也可能是在硬盤上，一般是下面兩種情況會(huì)使用硬盤（on-disk table）：

（1）使用了BLOB，TEXT類型的數(shù)據(jù)

（2）內(nèi)存表占用超過(guò)了系統(tǒng)變量tmp_table_size/max_heap_table_size的限定（一般為16M左右），只能放在硬盤上

從上面的查詢執(zhí)行過(guò)程和方式，我們應(yīng)該可以清楚的知道為什么Using filesort，Using temporary會(huì)嚴(yán)重的影響查詢性能，因?yàn)槿绻麛?shù)據(jù)類型或者字段設(shè)計(jì)有問(wèn)題，

在需要查詢的表以及結(jié)果中存在大數(shù)據(jù)的字段，而沒(méi)有合適的索引可用時(shí)，都可能會(huì)導(dǎo)致產(chǎn)生大量的IO操作，這就是查詢性能緩慢的根源所在。

回到文章開頭所舉的查詢實(shí)例，它顯然是使用了效率最低的第三種方法，我們需要做和嘗試的優(yōu)化手段有：

1、為users.fl_no添加索引，為select和where所使用的字段建立索引

2、把users.fl_no轉(zhuǎn)移到或者作為冗余字段添加到表user_profile中

3、去除TEXT類型的字段，TEXT可以替換為VARCHAR（65535）或?qū)τ谥形亩訴ARCHAR（20000）

4、如果實(shí)在無(wú)法消除Using filesort，那么提高sort_buffer_size，以減少IO操作負(fù)擔(dān)

5、盡量使用第一張表所覆蓋的索引進(jìn)行排序，實(shí)在不行，可以把排序邏輯從MySQL中移到PHP/Java程序中執(zhí)行

實(shí)施1、2、3的優(yōu)化方法后，EXPLAIN結(jié)果如下：

備注：編寫簡(jiǎn)單的PHP應(yīng)用，用siege測(cè)試，查詢效率提高>3倍。

以上就是本文的全部?jī)?nèi)容，希望對(duì)大家的學(xué)習(xí)有所幫助，也希望大家多多支持腳本之家。