針對(duì)排序來(lái)說(shuō)，order by 是我們使用非常頻繁的關(guān)鍵字。結(jié)合之前我們對(duì)索引的了解再來(lái)看這篇文章會(huì)讓我們深刻理解在排序的時(shí)候，是如何利用索引來(lái)達(dá)到少掃描表或者使用外部排序的。

先定義一個(gè)表輔助我們后面理解：

CREATE TABLE `t` (
 `id` int(11) NOT NULL,
 `city` varchar(16) NOT NULL,
 `name` varchar(16) NOT NULL,
 `age` int(11) NOT NULL,
 `addr` varchar(128) DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `city` (`city`)
) ENGINE=InnoDB;

這時(shí)我們寫(xiě)一條查詢語(yǔ)句

select city,name,age from t where city='杭州' order by name limit 1000 ;

根據(jù)上面的表定義來(lái)看，city=xxx 可以使用到我們定義的一個(gè)索引。但是 order by name 明顯我們沒(méi)有索引，所以肯定需要先用索引查詢到 city=xxx 然后再進(jìn)行回表查詢，最后再排序。

全字段排序

在 city 字段上面創(chuàng)建索引之后，我們使用執(zhí)行計(jì)劃來(lái)查看這個(gè)語(yǔ)句

可以看到有索引的情況下 我們這里還是使用了 "Using filesort" 表示需要排序，MySQL 會(huì)給每個(gè)線程分配一塊內(nèi)存用于排序 稱為 sort_buffer。

我們?cè)趫?zhí)行上面 select 語(yǔ)句的時(shí)候通常經(jīng)歷了這樣一個(gè)過(guò)程

1. 初始化 sort_buffer, 確認(rèn)放入 name, city, age 這三個(gè)字段。

2. 從索引 city 找到第一個(gè)滿足 city='杭州'條件的主鍵 id。

3. 回表取到 name, city, age 三個(gè)字段值，存入 sort_buffer 中。

4. 從索引 city 取下一個(gè)主鍵 id 記錄。

5. 重復(fù) 3-4 步驟，直到 city 不滿足條件。

6. 對(duì) sort_buffer 中的數(shù)據(jù)按照字段 name 做快速排序。

7. 排序結(jié)果取前 1000 行返回給客戶端。

這被我們稱為全字段排序。

按照 name 排序這個(gè)動(dòng)作即可能在內(nèi)存中完成，也可以能使用外部文件排序。這取決于 sort_buffer_size 。sort_buffer_size 的默認(rèn)值是1048576 byte 也就是 1M,如果要排序的數(shù)據(jù)量小于 1m 排序就在內(nèi)存中完成，如果排序數(shù)據(jù)量大，內(nèi)存放不下，則使用磁盤臨時(shí)文件輔助排序。

Rowid 排序

如果單行很大，需要的字段全部放進(jìn) sort_buffer 效果就不會(huì)很好。

MySQL 中專門用于控制排序的行數(shù)據(jù)長(zhǎng)度有個(gè)參數(shù) max_length_for_sort_data 默認(rèn)是1024，如果超過(guò)了這個(gè)值就會(huì)使用 rowid 排序。那么執(zhí)行上面語(yǔ)句的流程就變成了

1. 初始化 sort_buffe 確定放入兩個(gè)字段即 name 和 id 。

2. 從索引 city 找到第一個(gè)滿足 city = '杭州'條件的主鍵 id。

3. 回表取 name 和 id 兩個(gè)字段 存入 sort_buffer 中。

4. 取下個(gè)滿足條件的記錄 重復(fù) 2 3 步驟。

5. 對(duì) sort_buffer 中的 name 進(jìn)行排序。

6.遍歷結(jié)果取前 1000 行。然后按照 id 再回一次表取的結(jié)果字段返回給客戶端。

其實(shí)并不是所有 oder by 語(yǔ)句都需要進(jìn)行上面的二次排序操作。從上面分析的執(zhí)行過(guò)程，我們可以注意到。MySQL 之所以需要生成臨時(shí)表，是因?yàn)橐谂R時(shí)表上做排序，是因?yàn)橹拔覀內(nèi)〉玫氖菙?shù)據(jù)是無(wú)序的。

如果我們對(duì)剛才的索引修改一下，使得他是一個(gè)聯(lián)合索引，那么第二個(gè)字段我們拿到的值其實(shí)就是有序的了。

聯(lián)合索引滿足這么一個(gè)條件，當(dāng)我們的第一個(gè)索引字段是相等的情況下，第二個(gè)字段是有序的。

這能保證如果我們建立 (city,name) 索引的話，當(dāng)我們?cè)谒阉?city='杭州'的情況的是時(shí)候找到的目標(biāo)第二個(gè)字段 name 其實(shí)是有序的。所以查詢過(guò)程可以簡(jiǎn)化成。

1. 從索引 (city, name) 找到第一個(gè)滿足 city = '杭州'條件的主鍵 id 。

2. 回表取到 name city age 三個(gè)值返回。

3. 取下一個(gè) id 。

4. 重復(fù)2 3 兩個(gè)步驟直到 1000 條記錄，或者是不滿足 city = '杭州'條件結(jié)束。

也因?yàn)椴樵冞^(guò)程都可以使用到索引的有序性，所以不再需要排序也不需要時(shí)使用 sort buffer 了。

更近一步的優(yōu)化就是之前說(shuō)過(guò)的索引覆蓋，將需要查詢的字段也覆蓋進(jìn)索引中，再省掉回表的步驟，可以讓整個(gè)查詢的速度更快。

以上就是本文的全部?jī)?nèi)容，希望對(duì)大家的學(xué)習(xí)有所幫助，也希望大家多多支持腳本之家。