MySQL中order by的實現 和 by rand() 和最佳化

標籤：style   http   color   使用   strong   檔案   資料   ar   

“MySQL 裡面的order by rand()”是怎麼實現的。我們今天來簡單說說MySQL裡的order by。
    幾種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表實際上也已經讀入記憶體，在這個過程中，若不考慮記憶體不夠時的寫檔案策略， 則記憶體中有兩份表的全拷貝；另外多了從記憶體中將資料一一拷貝到暫存資料表的過程。

     這個查詢在我的測試環境中耗時2.41s（多次次執行，不計第一次載入資料的時間）

order by rand()的改進
    我們前面說過，實際上對於這種簡單的order by rand() 的情況，也可以等同於按照停用字詞段來處理。在sort_array 中存入隨機值即可。
     按照這個思路的patch在這裡，效果上


    執行時間減少為1.89s，效能提升21%, 這個例子單行1k，單行越大提升效果越好。