MySQL SQL最佳化

標籤：style   http   color   使用   strong   檔案   

--MySQL SQL最佳化

-------------------2014/07/23

 幾種order by的情況
    乍一看這個問題好像有點複雜，我們從最簡單的case開始看起。
    用這個表來說明：(10w行資料)

 

1、  最簡單的order ―― order by索引欄位

 從explain的結果來看（Extra列），這個語句並不作排序。因為欄位a已經是有順序的。就是按照索引a的順序依次讀pk的值(在這裡是隱藏的系統列)，一個個從聚簇索引的data中讀入。

 

 2、  複雜一點 ―― order by 停用字詞段

    這裡Extra列顯示一個Using filesort。這裡的filesort並不是指字面上的“檔案排序”，說的就是與上面一種情況相比，在Server層作了排序。至於是否使用檔案，取決於排序過程中的記憶體是否足夠，不夠則需要臨時檔案。

 

    並不到此為止，我們細細想一下，server層要怎麼作排序呢？

    一個簡單的想法是把表資料都讀到記憶體，然後排序。讀到記憶體當然可以想怎麼整就怎麼整。但是這個做法很耗費記憶體。需要佔用與表一樣大小的記憶體。

 

    另外一個做法，唯讀入欄位b和其對應的主鍵id。可以想象為這兩個欄位構成的結構體，按照b的值作排序。排序完成後，按欄位b的順序依次取主鍵id，取得結果返回。

 

    實際上第二種作法就是這個例子中的實際執行過程。存放用於排序的欄位值的結構我們稱為sort_keys.

至於order by b,c這樣的語句，效果與order by b相同，可以簡單理解為上面結構體多了一個欄位。

 

 3、  欄位函數排序

     有了上面的流程，這裡就簡單了，還是按順序讀入所有的欄位b，只是sort_keys中存的是b的長度而已。

 

4、Order by rand()

    按照自然想法， order by rand() 也可以仿照上面描述的做法，對於每一行，將產生的rand()的值放入sort_kyes裡即可。但實際上上效果如下：

    Extra欄位裡面有一個Using temporary， 也就是說用到了暫存資料表。那麼Using temporary的時候操作流程是怎樣的呢？
    a)   建立一個heap引擎的暫存資料表，欄位名為 ”” a b c d, 第一個欄位為匿名;
    b)   將表tb中的資料按行讀入到暫存資料表中，同時給第一欄位填入一個隨機實數(0,1);
    c)   按照第一個欄位排序，返回
    d)   查詢完成刪除暫存資料表

    分析一下這個過程,由於把資料從InnoDB表裡面讀入暫存資料表，則InnoDB表實際上也已經讀入記憶體，在這個過程中，若不考慮記憶體不夠時的寫檔案策略， 則記憶體中有兩份表的全拷貝；另外多了從記憶體中將資料一一拷貝到暫存資料表的過程。

 

--order by rank 最佳化

基本都是利用查詢max(id) * rand()來隨機擷取資料來解決。

例如：

SELECT *
FROM `table`
WHERE id >= (SELECT FLOOR( MAX(id) * RAND()) FROM `table` )
ORDER BY id LIMIT 1;

可以最佳化加上MIN(id)的判斷，因為沒有加上MIN(id)的判斷，結果有一些時間總是查詢到表中的min前的一些行。

SELECT * FROM `table`
WHERE id >= (SELECT floor( RAND() * ((SELECT MAX(id) FROM `table`)-(SELECT MIN(id) FROM `table`)) + (SELECT MIN(id) FROM `table`)))
ORDER BY id LIMIT 1;

--最佳化弊端：如果資料值分布非常不均勻，那個得到的結果隨機性有問題。例如：

資料分布  1-----3----------------------40---------------------------2000000

那麼按照這個方式，應該有百分之九十幾的幾率都得到2000000的行。