MySQL中（JOIN/ORDER BY）語句的查詢過程及優化方法

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

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

在優化這個語句之前，我們先了解下SQL查詢的基本執行過程：

1.應用通過MySQL API把查詢命令發送給MySQL服務器，然后被解析

2.檢查權限、MySQL optimizer進行優化，經過解析和優化后的查詢命令被編譯為CPU可運行的二進制形式的查詢計劃（query plan），并可以被緩存

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

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

5.輸出查詢結果，并記錄binary logs

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

那么讓我們進一步了解下關聯查詢（JOIN）和ORDER BY是怎么工作的，MySQL有三種方式來處理關聯查詢和數據排序：

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

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

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

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

第三種方法用于當ORDER BY的元素不屬于第一張表時，需要把關聯查詢的結果放入臨時表，最后對臨時表進行filesort：

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

（1）使用了BLOB，TEXT類型的數據

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

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

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

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

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

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

3、去除TEXT類型的字段，TEXT可以替換為VARCHAR（65535）或對于中文而言VARCHAR（20000）

4、如果實在無法消除Using filesort，那么提高sort_buffer_size，以減少IO操作負擔

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

實施1、2、3的優化方法后，EXPLAIN結果如下：

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

以上就是本文的全部內容，希望對大家的學習有所幫助，也希望大家多多支持億速云。