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

可以看到上述的查詢需要檢查1萬多記錄，并且使用了臨時表和filesort排序，這樣的查詢在用戶數(shù)快速增長后將成為噩夢。

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

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

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

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

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

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

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

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

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

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

第一種方法用于第一個非常量表中存在ORDER BY所依賴的列的索引，那就可直接使用已經(jīng)有序的索引來查找關(guān)聯(lián)表的數(shù)據(jù)，這種方式是性能最優(yōu)的，因為不需要額外的排序動作：

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

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

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

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

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

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

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

回到文章開頭所舉的查詢實例，它顯然是使用了效率最低的第三種方法，我們需要做和嘗試的優(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、如果實在無法消除Using filesort，那么提高sort_buffer_size，以減少IO操作負(fù)擔(dān)

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

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

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

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

最新評論