MySQL中（JOIN/ORDERBY）語句的查詢過程及優(yōu)化方法-創(chuàng)新互聯(lián)

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

網(wǎng)站建設哪家好，找成都創(chuàng)新互聯(lián)！專注于網(wǎng)頁設計、網(wǎng)站建設、微信開發(fā)、小程序制作、集團企業(yè)網(wǎng)站建設等服務項目。為回饋新老客戶創(chuàng)新互聯(lián)還提供了克什克騰免費建站歡迎大家使用！

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

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

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

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

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

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

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

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

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

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

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

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

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

第三種方法用于當ORDER BY的元素不屬于第一張表時，需要把關聯(lián)查詢的結(jié)果放入臨時表，最后對臨時表進行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í)行過程和方式，我們應該可以清楚的知道為什么Using filesort，Using temporary會嚴重的影響查詢性能，因為如果數(shù)據(jù)類型或者字段設計有問題，

在需要查詢的表以及結(jié)果中存在大數(shù)據(jù)的字段，而沒有合適的索引可用時，都可能會導致產(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操作負擔

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

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

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

以上就是本文的全部內(nèi)容，希望對大家的學習有所幫助，也希望大家多多支持創(chuàng)新互聯(lián)成都網(wǎng)站設計公司。

另外有需要云服務器可以了解下創(chuàng)新互聯(lián)scvps.cn，海內(nèi)外云服務器15元起步，三天無理由+7*72小時售后在線，公司持有idc許可證，提供“云服務器、裸金屬服務器、高防服務器、香港服務器、美國服務器、虛擬主機、免備案服務器”等云主機租用服務以及企業(yè)上云的綜合解決方案，具有“安全穩(wěn)定、簡單易用、服務可用性高、性價比高”等特點與優(yōu)勢，專為企業(yè)上云打造定制，能夠滿足用戶豐富、多元化的應用場景需求。

網(wǎng)頁標題：MySQL中（JOIN/ORDERBY）語句的查詢過程及優(yōu)化方法-創(chuàng)新互聯(lián)
文章URL：http://bm7419.com/article18/dioegp.html

成都網(wǎng)站建設公司_創(chuàng)新互聯(lián)，為您提供建站公司、虛擬主機、云服務器、標簽優(yōu)化、小程序開發(fā)、響應式網(wǎng)站

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)