MYSQL實現ORDER BY LIMIT的方法以及優先佇列(堆排序)

一、MYSQL中的LIMIT和ORACLE中的分頁
在MYSQL官方文件中描述limit是在結果集中返回你需要的資料，它可以儘快的返回需要的行而不用管剩下的行，
在ORACLE中也有相關的語法比如 12C以前的rownun<n，也是達到同樣的效果，同時limit也能做到分頁查詢如
limit n,m  則代表返回n開始的m行，ORACLE 12C以前也有分頁方式但是相對比較麻煩
那麼如果涉及到排序呢？我們需要返回按照欄位排序後的某幾行：
MYSQL:
select * from test order by id limit 51,100 
ORACLE 12C以前：
SELECT *
  FROM (SELECT tt.*, ROWNUM AS rowno
          FROM (SELECT t.*
                  FROM test t)
                 ORDER BY id desc) tt
         WHERE ROWNUM <= 100) table_alias
 WHERE table_alias.rowno > 50;


當然如上的語法如果id列有索引那麼就簡單了，索引本生就是排序好的，使用索引結構即可，但是如果id列沒有索引呢？
那該如何完成，難道把id列全部排序好在返回需要的行？顯然這樣代價過高，違背了limit中儘快返回需要的行的精神
這樣我們必須使用一種合適的演算法來完成，那這裡就引入的堆排序和優先佇列(Priority Queue 簡稱PQ)。
在MYSQL中執行計劃沒有完全的表現，執行計劃依然為filesort：
mysql> explain select * from testshared3 order by id limit 10,20;
+----+-------------+-------------+------------+------+---------------+------+---------+------+---------+----------+----------------+
| id | select_type | table       | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra          |
+----+-------------+-------------+------------+------+---------------+------+---------+------+---------+----------+----------------+
|  1 | SIMPLE      | testshared3 | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1023820 |   100.00 | Using filesort |
+----+-------------+-------------+------------+------+---------------+------+---------+------+---------+----------+----------------+
1 row in set, 1 warning (0.02 sec)


但是根據原始碼的提示
DBUG_PRINT("info", ("filesort PQ is applicable"));
DBUG_PRINT("info", ("filesort PQ is not applicable"));
注意這裡PQ可能棄用，什麼時候棄用看後面
可以看到是否啟用了PQ也就是優先佇列的簡寫 
可以再trace中找到相關說明:
[root@testmy tmp]# cat pq.trace |grep "filesort PQ is applicable"
T@2: | | | | | | | | | | info: filesort PQ is applicable


在ORACLE中使用執行計劃：
--------------------------------------------------------------------------------
Plan hash value: 1473139430
--------------------------------------------------------------------------------
| Id  | Operation                | Name   | Rows  | Bytes |TempSpc| Cost (%CPU)|
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |        |   100 | 77900 |       | 85431   (1)|
|*  1 |  VIEW                    |        |   100 | 77900 |       | 85431   (1)|
|*  2 |   COUNT STOPKEY          |        |       |       |       |            |
|   3 |    VIEW                  |        |   718K|   524M|       | 85431   (1)|
|*  4 |     SORT ORDER BY STOPKEY|        |   718K|   325M|   431M| 85431   (1)|
|   5 |      TABLE ACCESS FULL   | TEST10 |   718K|   325M|       | 13078   (1)|


這裡SORT ORDER BY STOPKEY就代表了排序停止，但是ORACLE沒有原始碼沒法確切的證據使用了
優先佇列和堆排序，只能猜測他使用了優先佇列和堆排序

二、堆排序和優先佇列

--大頂堆特性(小頂堆相似見程式碼)
1、必須滿足完全二叉樹
關於完全二叉樹參考
http://blog.itpub.net/7728585/viewspace-2125889/
2、很方便的根據父節點的位置計算出兩個葉子結點的位置
如果父節點的位置為i/2
左子節點為 i，右子節點為i+1
這是完全二叉樹的特性決定
3、所有子節點都可以看做一個子堆那麼所有結點都有
父節點>=左子節點 && 父節點>=右節點
4、很明顯的可以找到最大的元素，就是整個堆的根結點

--堆需要完成操作
堆排序方法也是最優佇列的實現方法，MYSQL原始碼中明顯的使用了優先佇列來優化order by limit n ，估計max也是用的這種演算法
當然前提是沒有使用到索引的情況下。
根據這些特性明顯又是一個遞迴的成堆的操作。
參考演算法導論第六章，裡面的插圖能夠加深理解，這裡擷取一張構建好的大頂堆


構建方法：自下而上的構建自左向右構建堆，其實就是不斷呼叫維護方法的過程
維護方法：使用遞迴的逐級下降的方法進行維護，是整個演算法的核心內容，搞清楚了維護方法其他任何操作都來自於它。
排序方法：最大元素放到最後,然後逐層下降的方法進行調整。
資料庫中的應用：
order by asc/desc limit n：簡化的排序而已，只是排序前面n就可以了，不用全部排序完成，效能優越，資料庫分頁查詢大量使用這個演算法。參考程式碼
max/min ：a[1]就是最大值,只能保證a[1]>=a[2]&&a[1]>=a[3]  不能保證a[3]>=a[4]，堆建立完成後就可以找到MAX值，但是MYSQL max並沒有使用這個演算法

我在程式碼中完成了這些操作，程式碼中有比較詳細的註釋，這裡就不詳細說明了。
我使用了2個陣列用於作為測試資料
        int i,a[11]={0,999,3,2,9,34,5,102,90,2222,1}; //測試資料 a[0]不使用
        int b[11]={0,999,3,2,9,999,888888,102,90,2222,111};//測試資料 b[0]不使用

分別求a素組的最大值和最小前3位數字，求b陣列的MAX/MIN值，結果如下：
gaopeng@bogon:~/datas$ ./a.out 
大頂堆:
order by desc a array limit 3 result:2222 999 102 
max values b array reulst:888888 
小頂堆:
order by asc a array limit 3 result:1 2 3 
min values b array reulst:2 
可以看到沒問題。


--優先佇列：優先佇列不同於普通佇列先進先出的規則，而定義為以某種規定先出，比如最大先出或者最小先出，這個沒什麼難度了，不就和資料庫的order
            by limit是一回事嗎？當然是用大頂堆或者小頂堆完成
 
三、MYSQL中優先佇列的介面

MYSQL中的優先佇列類在
priority_queue.h中的class Priority_queue : public Less
他實現了很多功能，不過其他功能都很簡單主要是堆的維護
下面是MYSQL原始碼中的堆的維護程式碼
  void heapify(size_type i, size_type last)
  {
    DBUG_ASSERT(i < size());
    size_type largest = i;

    do
    {
      i = largest;
      size_type l = left(i);
      size_type r = right(i);


      if (l < last && Base::operator()(m_container[i], m_container[l]))
      {
        largest = l;
      }


      if (r < last && Base::operator()(m_container[largest], m_container[r]))
      {
        largest = r;
      }


      if (largest != i)
      {
        std::swap(m_container[i], m_container[largest]);
      }
    } while (largest != i);
  }
可以看見實際和我寫的差不多。


四、MYSQL如何判斷是否啟用PQ
一般來說快速排序的效率高於堆排序，但是堆排序有著天生的特點可以實現優先佇列，來實現
order by limit 

(關於快速排序參考：http://blog.itpub.net/7728585/viewspace-2130743/)

那麼這裡就涉及一個問題，那就是快速排序和最優的佇列的臨界切換，比如
表A 100W行記錄 id列沒有索引
select * from a order by id limit 10;
和
select * from a order by id limit 900000,10;

肯定前者應該使用最優佇列，而後者實際上要排序好至少900010行資料才能返回。
那麼這個時候應該使用快速排序,那麼trace資訊應該為
filesort PQ is not applicable
[root@testmy tmp]# cat pqdis.trace |grep "filesort PQ "
T@2: | | | | | | | | | | info: filesort PQ is not applicable

那麼MYSQL值確定是否使用PQ，其判定介面為check_if_pq_applicable函式，
簡單的說MYSQL認為堆排序比快速排序慢3倍如下：
  /*
    How much Priority Queue sort is slower than qsort.
    Measurements (see unit test) indicate that PQ is roughly 3 times slower.
  */
  const double PQ_slowness= 3.0;


所以就要進行演算法的切換，但是具體演算法沒有仔細研究可以自行參考check_if_pq_applicable函式
至少和下面有關
1、是否能夠在記憶體中完成
2、排序行數
3、欄位數


最後需要說明一點PQ排序關閉了一次訪問排序的pack功能如下：
    /*
      For PQ queries (with limit) we know exactly how many pointers/records
      we have in the buffer, so to simplify things, we initialize
      all pointers here. (We cannot pack fields anyways, so there is no
      point in doing lazy initialization).
     */

五、實現程式碼，維護方法列出了2種實現，方法2是演算法導論上的更容易理解



</n，也是達到同樣的效果，同時limit也能做到分頁查詢如

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/7728585/viewspace-2130920/，如需轉載，請註明出處，否則將追究法律責任。