前言

本章詳細(xì)講下排序，排序在我們業(yè)務(wù)開發(fā)非常常見，有對(duì)時(shí)間進(jìn)行排序，又對(duì)城市進(jìn)行排序的。

不合適的排序，將對(duì)系統(tǒng)是災(zāi)難性的，這個(gè)不是危言聳聽。

可能有些人會(huì)想，對(duì)于排序mysql 是怎么實(shí)現(xiàn)的，它的底層原理是怎么樣的，如果我加上分頁(yè)，排序是不是就會(huì)快一些。

關(guān)于這些問題，本章詳細(xì)講解。

有人經(jīng)常問我，mysql 優(yōu)化的規(guī)則，總是不假思索的說(shuō)ESR，E 是 equal ，S是sort 。

可見排序有多么重要，為了講解方便，我先畫個(gè)思維導(dǎo)圖。

上圖標(biāo)的1，2 是mysql 配置文件可以配置的。

可以通過 show variables like 'max_length_for_sort_data'; 可以具體的配置。

從圖上我們可以看到mysql 排序分為全字段排序，和 rowid 。

這是兩大類，里面又分為內(nèi)存排序，文件排序，我將從這2大類4小類講解。

全字段排序

由上圖可以看出 Extra = Using filesort 就表示了排序，但此時(shí)還不能判斷是文件排序還是內(nèi)存排序

可以根據(jù)下面介紹的方法，來(lái)確定一個(gè)排序語(yǔ)句是否使用了臨時(shí)文件

/* 打開optimizer_trace，只對(duì)本線程有效 */
SET optimizer_trace='enabled=on'; 
?
/* @a保存Innodb_rows_read的初始值 */
select VARIABLE_VALUE into @a from  performance_schema.session_status where variable_name = 'Innodb_rows_read';
?
/* 執(zhí)行語(yǔ)句 */
select city, name,age from t where city='杭州' order by name limit 1000; 
?
/* 查看 OPTIMIZER_TRACE 輸出 */
SELECT * FROM `information_schema`.`OPTIMIZER_TRACE`\G
?
/* @b保存Innodb_rows_read的當(dāng)前值 */
select VARIABLE_VALUE into @b from performance_schema.session_status where variable_name = 'Innodb_rows_read';
?
/* 計(jì)算Innodb_rows_read差值 */
select @b-@a;

Number_of_tmp_files>0 就表示文件排序，沒有就表示是內(nèi)存排序。

sort_buffer_size 越小，那么 Number_of_tmp_files 就會(huì)越大，文件排序用的是歸并排序，也就是把數(shù)據(jù)分給多個(gè)文件，每個(gè)文件排序后，最終合并一個(gè)文件。

上面sort_mode 可以看到，這是一個(gè)全字段排序，什么是全字段排序，就拿上面這個(gè)sql 語(yǔ)句來(lái)說(shuō)，city ,name,age 都在文件里，對(duì)name 進(jìn)行排序

這個(gè)排序的內(nèi)部是這么實(shí)現(xiàn)的：

• 初始化 sort_buffer，確定放入 name、city、age 這三個(gè)字段；
• 從索引 city 找到第一個(gè)滿足 city='杭州’ 條件的主鍵 id
• 到主鍵 id 索引取出整行，取 name、city、age 三個(gè)字段的值，存入 sort_buffer 中；
• 從索引 city 取下一個(gè)滿足 city='杭州’ 的主鍵 id；
• 重復(fù)步驟 3、4 直到 city 的值不滿足查詢條件為止
• 對(duì) sort_buffer 中的數(shù)據(jù)按照字段 name 做快速排序；
• 按照排序結(jié)果取前 1000 行返回給客戶端。

由此我們發(fā)現(xiàn)，排序會(huì)對(duì)表的所有的記錄進(jìn)行排序，然后在取出1000條

rowid

如果 排序數(shù)據(jù)的長(zhǎng)度超過了 max_length_for_sort_data 就是 rowid排序。

排序數(shù)據(jù)的長(zhǎng)度就是指拿上面這個(gè)例子說(shuō) name、city、age 這三個(gè)字段大于 max_length_for_sort_data 就是rowid 排序。

為什么會(huì)這樣的呢，mysql 會(huì)盡量用內(nèi)存排序，字段越長(zhǎng)，占用空間越大，未了提高排序效率，就會(huì)用rowid 排序。

rowid排序的步驟是這樣的：

• 初始化 sort_buffer，確定放入兩個(gè)字段，即 name 和 id；
• 從索引 city 找到第一個(gè)滿足 city='杭州’條件的主鍵 id
• 到主鍵 id 索引取出整行，取 name、id 這兩個(gè)字段，存入 sort_buffer 中；
• 從索引 city 取下一個(gè)記錄的主鍵 id；
• 重復(fù)步驟 3、4 直到不滿足 city='杭州’條件為止，
• 對(duì) sort_buffer 中的數(shù)據(jù)按照字段 name 進(jìn)行排序；
• 遍歷排序結(jié)果，取前 1000 行，并按照 id 的值回到原表中取出 city、name 和 age 三個(gè)字段返回給客戶端。

我們可以看到 rowid 會(huì)多訪問一次表，在mysql 看來(lái)，排序的復(fù)雜度高于回表的復(fù)雜度，這也是一種取舍。

綜上可以看出不管是內(nèi)存排序還是文件排序，都是很繁瑣的，那么有沒有對(duì)于這個(gè)問題有沒有優(yōu)化點(diǎn)了，在前面我們已經(jīng)講過了，索引一定是有序的，如果我們對(duì)city，name 建一個(gè)聯(lián)合索引，就不用mysql 重新排序，因?yàn)樗饕旧砭褪怯行虻摹?/p>

就是如下所示：

alter table t add index city_user(city, name);

但是上面雖然不用mysql 用文件排序，但是還是要回表的，那還有沒有進(jìn)一步的優(yōu)化呢，我們可以考慮用覆蓋索引

如下所示：

alter table t add index city_user_age(city, name, age);

這樣就不用回表了，用explain 來(lái)看 Extra using index

大家要綜合考慮吧，索引越多，索引越大，會(huì)影響插入的速度的。

總結(jié)

以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。

最新評(píng)論