如何優化sql中的orderBy語句

在使用資料庫進行資料查詢時，難免會遇到基於某些欄位對查詢的結果集進行排序的需求。在sql中通常使用orderby語句來實現。將需要排序的欄位放到 該關鍵詞後，如果有多個欄位的話，就用","分割。

select * from table t order by t.column1,t.column2;

上面的sql表示查詢表table中資料，然後先按照column1排序，如果column1相同的話，在按照column2排序，排序的方式預設是降序。當然排序方式也是可以指定的。在被排序欄位後新增 DESC,ASE,分別表示降序和升序。

使用該orderby可以很方便的實現日常的排序操作。使用的多了，不知道你有沒有遇到過這種場景：有時候使用orderby後，sql執行效率非常慢，有時候卻比較快，由於整天被curd纏身，也沒有時間研究，反正就是覺得很神奇。趁這個週末比較閒，就來研究下，mysql中orderby是怎麼實現的。

為了方便描述，我們先建立一個資料表 t1，如下：

CREATE TABLE `t1` (
  `id` int(11) NOT NULL not null auto_increment,
  `a` int(11)  DEFAULT NULL,
  `b` int(11)  DEFAULT NULL,
  `c` int(11)  DEFAULT NULL,
  PRIMARY KEY (`id`) ,
  KEY `a` (`a`) USING BTREE
) ENGINE=InnoDB;

並插入資料：

insert into t1 (a,b,c) values (1,1,3);
insert into t1 (a,b,c) values (1,4,5);
insert into t1 (a,b,c) values (1,3,3);
insert into t1 (a,b,c) values (1,3,4);
insert into t1 (a,b,c) values (1,2,5);
insert into t1 (a,b,c) values (1,3,6);

為了使索引生效，插入10000行 7,7,7，無關資料，資料量少的情況下，會直接全表掃描

insert into t1 (a,b,c) values (7,7,7);

我們現在需要查詢 a=1的所有記錄，然後按照b欄位進行排序。

查詢sql為

select a,b,c from t1 where a = 1 order by b limit 2;

為了防止在查詢過程中全表掃描，我們在欄位a上新增了索引。

首先我們先通過語句

explain select a,b,c from t1 where a = 1 order by b lmit 2;

檢視sql的執行計劃，如下所示：

在extra中我們可以看到出現了Using filesort，這個表示 該sql執行過程中，執行了排序操作，排序操作在 sort_buffer中完成，sort_buffer是mysql分配給每個執行緒的一個記憶體緩衝區，該緩衝區專門用來完成排序，大小預設是1M，其大小由變數 sort_buffer_size 進行控制。

mysql在對orderby進行實現時，根據放入到sort_buffer中的欄位內容不同，進行了兩種不同實現方式：全欄位排序和rowid排序。

全欄位排序

首先我們先通過一張圖整體看一下sql執行過程：

mysql先根據查詢條件確定需要排序的資料集，也就是表中 a=1的資料集，即主鍵id從1到6的這些記錄。

整個sql的執行的過程如下：

1.建立並初始化sort_buffer,並確定需要放到該緩衝區中的欄位，也就是a,b,c這三個欄位。

2.從索引樹a中找到第一個滿足a=1的主鍵id，也就是id=1。

3.回表到id索引，取出整行資料，然後從整行資料中，取出a,b,c的值，放入到sort_buffer中。

4.從索引a中按照順序找到下一個a=1的主鍵id。

5.重複步驟3和步驟4，直到獲取到最後一個a=1的記錄，也就是主鍵id=5。

6.此時滿足條件a=1的所有記錄的 a,b,c欄位，全部讀放到了sort_buffer中，然後，對這些資料按照b的值進行進行排序，排序的方式是快速排序。就是那個面試經常面到的快速排序，時間複雜度為log2n的快速排序。

7.然後從排序後的結果集中取出前2行資料。

上面是就是msql中orderby的執行流程。因為放入到sort_buffer中的資料是需要輸出的全部欄位，所以這種排序被稱為全排序。

看到這裡不知道你是否會有疑問？如果需要排序的資料量很大的話，sort_buffer裝不下怎麼辦？

的確，如果a=1的資料行特別多，且需要存放到sort_buffer中的欄位比較多，可能不止a,b,c三個欄位，有些業務可能需要輸出更多欄位。那麼預設大小隻有1M的sort_buffer很可能容納不下。

當sort_buffer容納不下的時候，mysql會建立一批臨時的磁碟檔案來輔助排序。預設情況下會建立12個臨時檔案，將需要排序的資料分成12份，每一份單獨排序，形成12個內部資料有序的檔案，然後把這12個有序檔案在合併成一個有序的大檔案，最終完成資料的排序。

基於檔案的排序，相比基於記憶體的排序，排序效率要低很多，為了提高排序的效率，應該儘量避免基於檔案的排序，要想避免基於檔案排序，就需要讓sort_buffer可以容納需要排序的資料量。

所以對於sort_buffer容納不下的情況，mysql進行了優化。就是在排序時候，降低存放到sort_buffer中的欄位個數。

具體優化方式，就是下面的rowId排序

RowId 排序

在全欄位排序實現中，排序的過程中，要把需要輸出的欄位全部放到sort_buffer中，當輸出的欄位比較多的時候，可以放到sort_buffer中的資料行就會變少。也就增大了sort_buffer無法容納資料的風險，直至出現基於檔案的排序。

rowId排序對全欄位排序的優化手段，主要是減少了放到sort_buffer中欄位個數。

在rowId排序中，只會將需要排序的欄位和主鍵Id放到sort_buffer中。

select a,b,c from t1 where a = 1 order by b limit 2;

在rowId的排序中的執行流程如下：

1.初始化並建立sort_buffer，並確認要放入的的欄位，id和b。

2.從索引樹a中找到第一個滿足a=1的主鍵id，也就是id=1。

3.回表主鍵索引id，取出整行資料，從整行資料中取出id和b，存入sort_buffer中。

4.從索引a中取出下一條滿足a=1的 記錄的主鍵id。

5.重複步驟3和4，直到最後一個滿足a=1的主鍵id，也就是a=6。

6.對sort_buffer中的資料，按照欄位b排序。

7.從sort_buffer中的有序資料集中，取出前2個，因為此時取出的資料只有id和b，要想獲取a和c欄位，需要根據id欄位，回表到主鍵索引中取出整行資料，從整行資料中獲取需要的資料。

根據rowId排序的執行步驟，可以發現：相比全欄位排序，rowId排序的實現方式，減少了存放到sort_buffer中的資料量，降低了基於檔案的外部排序的可能性。

那rowid排序有不足的地方嗎？肯定有的，要不然全欄位排序就沒有存在的意義了。rowid排序不足之處在於，在最後的步驟7中，增加了回表的次數，不過這個回表的次數，取決於limit後的值，如果返回的結果集比較小的話，回表的次數還是比較小的。

mysql是如何在全欄位排序和rowId排序的呢？其實是根據存放的sort_buffer中每行欄位的長度決定的，如果mysql認為每次放到sort_buffer中的資料量很大的話，那麼就用rowId排序實現，否則使用全欄位排序。那麼多大算大呢？這個大小的閾值有一個變數的值來決定，這個變數就是 max_length_for_sort_data。如果每次放到sort_buffer中的資料大小大於該欄位值的話，就使用rowId排序，否則使用全欄位排序。

orderby的優化

上面講述了orderby的兩種排序的方式，以及一些優化策略，優化的目的主要就是避免基於磁碟檔案的外部排序。因為基於磁碟檔案的排序效率要遠低於基於sort_buffer的記憶體排序。

但是當資料量比較大的時候，即使sort_buffer比較大，所有資料全部放在記憶體中排序，sql的整體執行效率也不高，因為排序這個操作，本身就是比較消耗效能的。

試想，如果基於索引a獲取到所有a=1的資料，按照欄位b，天然就是有序的，那麼就不用執行排序操作，直接取出來的資料，就是符合結果的資料集，那麼sql的執行效率就會大幅度增長。

其實要實現整個sql執行過程中，避免排序操作也不難，只需要建立一個a和b的聯合索引即可。

alter table t1 add index a_b (a,b);

新增a和b的聯合索引後，sql執行流程就變成了：

1.從索引樹(a,b)中找到第一個滿足a=1的主鍵id，也就是id=1。

2.回表到主鍵索引樹，取出整行資料，並從中取出a，b，c，直接作為結果集的一部分返回。

3.從索引樹(a,b)上取出下一個滿足a=1的主鍵id。

4.重複步驟2和3，直到找到第二個滿足a=1的主鍵id,並回表獲取欄位a，b，c。

此時我們可以通過檢視sql的執行計劃，來判斷sql的執行過程中是否執行了排序操作。

explain select a,b from t1 where a = 1 order by b lmit 2;

通過檢視執行計劃，我們發現extra中已經沒有了using filesort了，也就是沒有執行排序操作了。

其實還可以通過覆蓋索引，對該sql進一步優化，通過在索引中覆蓋欄位c，來避免回表的操作。

alter table t1 add index a_b_c (a,b,c);

新增索引a_b_c後，sql的執行過程如下：

1.從索引樹(a,b,c)中找到第一個滿足a=1的索引，從中取出a，b，c。直接作為結果集的一部分直接返回。

2.從索引(a,b,c)中取出下一個，滿足a=1的記錄作為結果集的一部分。

3.重複執行步驟2，直到查到第二個a=1或者不滿足a=1的記錄。

此時通過檢視執行sql的的還行計劃可以發現 extra中只有 Using index。

explain select a,b from t1 where a = 1 order by b lmit 2;

總結

通過對該sql的多次優化，sql的最終執行效率和沒有排序的普通sql的查詢效率基本是一樣的。之所以可以避免orderby的排序操作，就是利用了索引天然有序的特點。

但是我們都知道，索引可以加快查詢的效率，但是索引的維護成本比較大，對資料表中資料的新增和修改都會涉及索引的變動，所以索引也不是越多越好，有時候，並不能因為一些不常用的查詢和排序，而增加了過多的索引，得不償失。

以上為個人經驗，希望能給大家一個參考，也希望大家多多支援it145.com。