這篇文章主要介紹“如何優(yōu)化sql中order By語句”的相關(guān)知識，小編通過實(shí)際案例向大家展示操作過程，操作方法簡單快捷，實(shí)用性強(qiáng)，希望這篇“如何優(yōu)化sql中order By語句”文章能幫助大家解決問題。

會澤網(wǎng)站建設(shè)公司創(chuàng)新互聯(lián),會澤網(wǎng)站設(shè)計(jì)制作，有大型網(wǎng)站制作公司豐富經(jīng)驗(yàn)。已為會澤近1000家提供企業(yè)網(wǎng)站建設(shè)服務(wù)。企業(yè)網(wǎng)站搭建\外貿(mào)網(wǎng)站建設(shè)要多少錢，請找那個售后服務(wù)好的會澤做網(wǎng)站的公司定做！

在使用數(shù)據(jù)庫進(jìn)行數(shù)據(jù)查詢時，難免會遇到基于某些字段對查詢的結(jié)果集進(jìn)行排序的需求。在sql中通常使用orderby語句來實(shí)現(xiàn)。將需要排序的字段放到 該關(guān)鍵詞后，如果有多個字段的話，就用","分割。

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

上面的sql表示查詢表table中數(shù)據(jù)，然后先按照column1排序，如果column1相同的話，在按照column2排序，排序的方式默認(rèn)是降序。當(dāng)然排序方式也是可以指定的。在被排序字段后添加 DESC,ASE,分別表示降序和升序。

使用該orderby可以很方便的實(shí)現(xiàn)日常的排序操作。使用的多了，不知道你有沒有遇到過這種場景：有時候使用orderby后，sql執(zhí)行效率非常慢，有時候卻比較快，由于整天被curd纏身，也沒有時間研究，反正就是覺得很神奇。趁這個周末比較閑，就來研究下，MySQL中orderby是怎么實(shí)現(xiàn)的。

為了方便描述，我們先建立一個數(shù)據(jù)表 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;

并插入數(shù)據(jù)：

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，無關(guān)數(shù)據(jù)，數(shù)據(jù)量少的情況下，會直接全表掃描

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

我們現(xiàn)在需要查找 a=1的所有記錄，然后按照b字段進(jìn)行排序。

查詢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的執(zhí)行計(jì)劃，如下所示：

在extra中我們可以看到出現(xiàn)了Using filesort，這個表示 該sql執(zhí)行過程中，執(zhí)行了排序操作，排序操作在 sort_buffer中完成，sort_buffer是mysql分配給每個線程的一個內(nèi)存緩沖區(qū)，該緩沖區(qū)專門用來完成排序，大小默認(rèn)是1M，其大小由變量 sort_buffer_size 進(jìn)行控制。

mysql在對orderby進(jìn)行實(shí)現(xiàn)時，根據(jù)放入到sort_buffer中的字段內(nèi)容不同，進(jìn)行了兩種不同實(shí)現(xiàn)方式：全字段排序和rowid排序。

全字段排序

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

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

整個sql的執(zhí)行的過程如下：

1.創(chuàng)建并初始化sort_buffer,并確定需要放到該緩沖區(qū)中的字段，也就是a,b,c這三個字段。

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

3.回表到id索引，取出整行數(shù)據(jù)，然后從整行數(shù)據(jù)中，取出a,b,c的值，放入到sort_buffer中。

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

5.重復(fù)步驟3和步驟4，直到獲取到最后一個a=1的記錄，也就是主鍵id=5。

6.此時滿足條件a=1的所有記錄的 a,b,c字段，全部讀放到了sort_buffer中，然后，對這些數(shù)據(jù)按照b的值進(jìn)行進(jìn)行排序，排序的方式是快速排序。就是那個面試經(jīng)常面到的快速排序，時間復(fù)雜度為log2n的快速排序。

7.然后從排序后的結(jié)果集中取出前2行數(shù)據(jù)。

上面是就是msql中orderby的執(zhí)行流程。因?yàn)榉湃氲絪ort_buffer中的數(shù)據(jù)是需要輸出的全部字段，所以這種排序被稱為全排序。

看到這里不知道你是否會有疑問？如果需要排序的數(shù)據(jù)量很大的話，sort_buffer裝不下怎么辦？

的確，如果a=1的數(shù)據(jù)行特別多，且需要存放到sort_buffer中的字段比較多，可能不止a,b,c三個字段，有些業(yè)務(wù)可能需要輸出更多字段。那么默認(rèn)大小只有1M的sort_buffer很可能容納不下。

當(dāng)sort_buffer容納不下的時候，mysql會創(chuàng)建一批臨時的磁盤文件來輔助排序。默認(rèn)情況下會創(chuàng)建12個臨時文件，將需要排序的數(shù)據(jù)分成12份，每一份單獨(dú)排序，形成12個內(nèi)部數(shù)據(jù)有序的文件，然后把這12個有序文件在合并成一個有序的大文件，最終完成數(shù)據(jù)的排序。

基于文件的排序，相比基于內(nèi)存的排序，排序效率要低很多，為了提高排序的效率，應(yīng)該盡量避免基于文件的排序，要想避免基于文件排序，就需要讓sort_buffer可以容納需要排序的數(shù)據(jù)量。

所以對于sort_buffer容納不下的情況，mysql進(jìn)行了優(yōu)化。就是在排序時候，降低存放到sort_buffer中的字段個數(shù)。

具體優(yōu)化方式，就是下面的rowId排序

RowId 排序

在全字段排序?qū)崿F(xiàn)中，排序的過程中，要把需要輸出的字段全部放到sort_buffer中，當(dāng)輸出的字段比較多的時候，可以放到sort_buffer中的數(shù)據(jù)行就會變少。也就增大了sort_buffer無法容納數(shù)據(jù)的風(fēng)險，直至出現(xiàn)基于文件的排序。

rowId排序?qū)θ侄闻判虻膬?yōu)化手段，主要是減少了放到sort_buffer中字段個數(shù)。

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

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

在rowId的排序中的執(zhí)行流程如下：

1.初始化并創(chuàng)建sort_buffer，并確認(rèn)要放入的的字段，id和b。

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

3.回表主鍵索引id，取出整行數(shù)據(jù)，從整行數(shù)據(jù)中取出id和b，存入sort_buffer中。

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

5.重復(fù)步驟3和4，直到最后一個滿足a=1的主鍵id，也就是a=6。

6.對sort_buffer中的數(shù)據(jù)，按照字段b排序。

7.從sort_buffer中的有序數(shù)據(jù)集中，取出前2個，因?yàn)榇藭r取出的數(shù)據(jù)只有id和b，要想獲取a和c字段，需要根據(jù)id字段，回表到主鍵索引中取出整行數(shù)據(jù)，從整行數(shù)據(jù)中獲取需要的數(shù)據(jù)。

根據(jù)rowId排序的執(zhí)行步驟，可以發(fā)現(xiàn)：相比全字段排序，rowId排序的實(shí)現(xiàn)方式，減少了存放到sort_buffer中的數(shù)據(jù)量，降低了基于文件的外部排序的可能性。

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

mysql是如何在全字段排序和rowId排序的呢？其實(shí)是根據(jù)存放的sort_buffer中每行字段的長度決定的，如果mysql認(rèn)為每次放到sort_buffer中的數(shù)據(jù)量很大的話，那么就用rowId排序?qū)崿F(xiàn)，否則使用全字段排序。那么多大算大呢？這個大小的閾值有一個變量的值來決定，這個變量就是 max_length_for_sort_data。如果每次放到sort_buffer中的數(shù)據(jù)大小大于該字段值的話，就使用rowId排序，否則使用全字段排序。

orderby的優(yōu)化

上面講述了orderby的兩種排序的方式，以及一些優(yōu)化策略，優(yōu)化的目的主要就是避免基于磁盤文件的外部排序。因?yàn)榛诖疟P文件的排序效率要遠(yuǎn)低于基于sort_buffer的內(nèi)存排序。

但是當(dāng)數(shù)據(jù)量比較大的時候，即使sort_buffer比較大，所有數(shù)據(jù)全部放在內(nèi)存中排序，sql的整體執(zhí)行效率也不高，因?yàn)榕判蜻@個操作，本身就是比較消耗性能的。

試想，如果基于索引a獲取到所有a=1的數(shù)據(jù)，按照字段b，天然就是有序的，那么就不用執(zhí)行排序操作，直接取出來的數(shù)據(jù)，就是符合結(jié)果的數(shù)據(jù)集，那么sql的執(zhí)行效率就會大幅度增長。

其實(shí)要實(shí)現(xiàn)整個sql執(zhí)行過程中，避免排序操作也不難，只需要創(chuàng)建一個a和b的聯(lián)合索引即可。

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

添加a和b的聯(lián)合索引后，sql執(zhí)行流程就變成了：

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

2.回表到主鍵索引樹，取出整行數(shù)據(jù)，并從中取出a，b，c，直接作為結(jié)果集的一部分返回。

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

4.重復(fù)步驟2和3，直到找到第二個滿足a=1的主鍵id,并回表獲取字段a，b，c。

此時我們可以通過查看sql的執(zhí)行計(jì)劃，來判斷sql的執(zhí)行過程中是否執(zhí)行了排序操作。

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

通過查看執(zhí)行計(jì)劃，我們發(fā)現(xiàn)extra中已經(jīng)沒有了using filesort了，也就是沒有執(zhí)行排序操作了。

其實(shí)還可以通過覆蓋索引，對該sql進(jìn)一步優(yōu)化，通過在索引中覆蓋字段c，來避免回表的操作。

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

添加索引a_b_c后，sql的執(zhí)行過程如下：

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

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

3.重復(fù)執(zhí)行步驟2，直到查到第二個a=1或者不滿足a=1的記錄。

此時通過查看執(zhí)行sql的的還行計(jì)劃可以發(fā)現(xiàn) extra中只有 Using index。

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

關(guān)于“如何優(yōu)化sql中order By語句”的內(nèi)容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業(yè)相關(guān)的知識，可以關(guān)注創(chuàng)新互聯(lián)行業(yè)資訊頻道，小編每天都會為大家更新不同的知識點(diǎn)。

文章名稱：如何優(yōu)化sql中orderBy語句
標(biāo)題路徑：http://chinadenli.net/article10/pighgo.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供網(wǎng)頁設(shè)計(jì)公司、靜態(tài)網(wǎng)站、域名注冊、網(wǎng)站設(shè)計(jì)、網(wǎng)站改版、標(biāo)簽優(yōu)化

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)