MySQL簡單了解“order by”是怎么工作的

更新時間：2019年05月21日 14:22:28 作者：piperck

在MySQl中ORDER BY 語句用于對結果集進行排序，那么它是怎么工作的，以及如何優(yōu)化，下面由小編跟大家講一講

針對排序來說，order by 是我們使用非常頻繁的關鍵字。結合之前我們對索引的了解再來看這篇文章會讓我們深刻理解在排序的時候，是如何利用索引來達到少掃描表或者使用外部排序的。

先定義一個表輔助我們后面理解：

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;

這時我們寫一條查詢語句

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

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

全字段排序

在 city 字段上面創(chuàng)建索引之后，我們使用執(zhí)行計劃來查看這個語句

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

我們在執(zhí)行上面 select 語句的時候通常經(jīng)歷了這樣一個過程

1. 初始化 sort_buffer, 確認放入 name, city, age 這三個字段。

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

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

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

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

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

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

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

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

Rowid 排序

如果單行很大，需要的字段全部放進 sort_buffer 效果就不會很好。

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

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

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

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

4. 取下個滿足條件的記錄重復 2 3 步驟。

5. 對 sort_buffer 中的 name 進行排序。

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