首頁 > 軟體

如何優化sql中的orderBy語句

2022-09-28 14:01:21

在使用資料庫進行資料查詢時,難免會遇到基於某些欄位對查詢的結果集進行排序的需求。在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。 


IT145.com E-mail:sddin#qq.com