看一遍就理解：Order By詳解
前言
日常開發(fā)中，我們經(jīng)常會(huì)使用到order by，親愛的小伙伴，你是否知道order by 的工作原理呢?order by的優(yōu)化思路是怎樣的呢?使用order by有哪些注意的問題呢?本文將跟大家一起來學(xué)習(xí)，攻克order by~
一個(gè)使用order by 的簡單例子
假設(shè)用一張員工表，表結(jié)構(gòu)如下：
- CREATE TABLE `staff` (
- `id` BIGINT ( 11 ) AUTO_INCREMENT COMMENT '主鍵id',
- `id_card` VARCHAR ( 20 ) NOT NULL COMMENT '身份證號(hào)碼',
- `name` VARCHAR ( 64 ) NOT NULL COMMENT '姓名',
- `age` INT ( 4 ) NOT NULL COMMENT '年齡',
- `city` VARCHAR ( 64 ) NOT NULL COMMENT '城市',
- PRIMARY KEY ( `id`),
- INDEX idx_city ( `city` )
- ) ENGINE = INNODB COMMENT '員工表';
表數(shù)據(jù)如下：
我們現(xiàn)在有這么一個(gè)需求：查詢前10個(gè)，來自深圳員工的姓名、年齡、城市，并且按照年齡小到大排序。對(duì)應(yīng)的 SQL 語句就可以這么寫：
- select name,age,city from staff where city = '深圳' order by age limit 10;
這條語句的邏輯很清楚，但是它的底層執(zhí)行流程是怎樣的呢?
order by 工作原理
explain 執(zhí)行計(jì)劃
我們先用Explain關(guān)鍵字查看一下執(zhí)行計(jì)劃
- 執(zhí)行計(jì)劃的key這個(gè)字段，表示使用到索引idx_city
- Extra 這個(gè)字段的 Using index condition 表示索引條件
- Extra 這個(gè)字段的 Using filesort表示用到排序
我們可以發(fā)現(xiàn)，這條SQL使用到了索引，并且也用到排序。那么它是怎么排序的呢?
全字段排序
MySQL 會(huì)給每個(gè)查詢線程分配一塊小內(nèi)存，用于排序的，稱為 sort_buffer。什么時(shí)候把字段放進(jìn)去排序呢，其實(shí)是通過idx_city索引找到對(duì)應(yīng)的數(shù)據(jù)，才把數(shù)據(jù)放進(jìn)去啦。
我們回顧下索引是怎么找到匹配的數(shù)據(jù)的，現(xiàn)在先把索引樹畫出來吧，idx_city索引樹如下：
idx_city索引樹，葉子節(jié)點(diǎn)存儲(chǔ)的是主鍵id。還有一棵id主鍵聚族索引樹，我們?cè)佼嫵鼍圩逅饕龢鋱D吧：
我們的查詢語句是怎么找到匹配數(shù)據(jù)的呢?先通過idx_city索引樹，找到對(duì)應(yīng)的主鍵id，然后再通過拿到的主鍵id，搜索id主鍵索引樹，找到對(duì)應(yīng)的行數(shù)據(jù)。
加上order by之后，整體的執(zhí)行流程就是：
- MySQL 為對(duì)應(yīng)的線程初始化sort_buffer，放入需要查詢的name、age、city字段;
- 從索引樹idx_city， 找到第一個(gè)滿足 city='深圳’條件的主鍵 id，也就是圖中的id=9;
- 到主鍵 id 索引樹拿到id=9的這一行數(shù)據(jù)， 取name、age、city三個(gè)字段的值，存到sort_buffer;
- 從索引樹idx_city 拿到下一個(gè)記錄的主鍵 id，即圖中的id=13;
- 重復(fù)步驟 3、4 直到city的值不等于深圳為止;
- 前面5步已經(jīng)查找到了所有city為深圳的數(shù)據(jù)，在 sort_buffer中，將所有數(shù)據(jù)根據(jù)age進(jìn)行排序;
- 按照排序結(jié)果取前10行返回給客戶端。
執(zhí)行示意圖如下：
將查詢所需的字段全部讀取到sort_buffer中，就是全字段排序。這里面，有些小伙伴可能會(huì)有個(gè)疑問,把查詢的所有字段都放到sort_buffer，而sort_buffer是一塊內(nèi)存來的，如果數(shù)據(jù)量太大，sort_buffer放不下怎么辦呢?
磁盤臨時(shí)文件輔助排序
實(shí)際上，sort_buffer的大小是由一個(gè)參數(shù)控制的：sort_buffer_size。如果要排序的數(shù)據(jù)小于sort_buffer_size，排序在sort_buffer 內(nèi)存中完成，如果要排序的數(shù)據(jù)大于sort_buffer_size，則借助磁盤文件來進(jìn)行排序
如何確定是否使用了磁盤文件來進(jìn)行排序呢?可以使用以下這幾個(gè)命令
- ## 打開optimizer_trace，開啟統(tǒng)計(jì)
- set optimizer_trace = "enabled=on";
- ## 執(zhí)行SQL語句
- select name,age,city from staff where city = '深圳' order by age limit 10;
- ## 查詢輸出的統(tǒng)計(jì)信息
- select * from information_schema.optimizer_trace
可以從 number_of_tmp_files 中看出，是否使用了臨時(shí)文件。
number_of_tmp_files 表示使用來排序的磁盤臨時(shí)文件數(shù)。如果number_of_tmp_files>0，則表示使用了磁盤文件來進(jìn)行排序。
使用了磁盤臨時(shí)文件，整個(gè)排序過程又是怎樣的呢?
- 從主鍵Id索引樹，拿到需要的數(shù)據(jù)，并放到sort_buffer內(nèi)存塊中。當(dāng)sort_buffer快要滿時(shí)，就對(duì)sort_buffer中的數(shù)據(jù)排序，排完后，把數(shù)據(jù)臨時(shí)放到磁盤一個(gè)小文件中。
- 繼續(xù)回到主鍵 id 索引樹取數(shù)據(jù)，繼續(xù)放到sort_buffer內(nèi)存中，排序后，也把這些數(shù)據(jù)寫入到磁盤臨時(shí)小文件中。
- 繼續(xù)循環(huán)，直到取出所有滿足條件的數(shù)據(jù)。最后把磁盤的臨時(shí)排好序的小文件，合并成一個(gè)有序的大文件。
TPS: 借助磁盤臨時(shí)小文件排序，實(shí)際上使用的是歸并排序算法。
小伙伴們可能會(huì)有個(gè)疑問，既然sort_buffer放不下，就需要用到臨時(shí)磁盤文件，這會(huì)影響排序效率。那為什么還要把排序不相關(guān)的字段(name，city)放到sort_buffer中呢?只放排序相關(guān)的age字段，它不香嗎?可以了解下rowid 排序。
rowid 排序
rowid 排序就是，只把查詢SQL需要用于排序的字段和主鍵id，放到sort_buffer中。那怎么確定走的是全字段排序還是rowid 排序排序呢?
實(shí)際上有個(gè)參數(shù)控制的。這個(gè)參數(shù)就是max_length_for_sort_data，它表示MySQL用于排序行數(shù)據(jù)的長度的一個(gè)參數(shù)，如果單行的長度超過這個(gè)值，MySQL 就認(rèn)為單行太大，就換rowid 排序。我們可以通過命令看下這個(gè)參數(shù)取值。
- show variables like 'max_length_for_sort_data';
max_length_for_sort_data 默認(rèn)值是1024。因?yàn)楸疚氖纠衝ame,age,city長度=64+4+64 =132 < 1024, 所以走的是全字段排序。我們來改下這個(gè)參數(shù)，改小一點(diǎn)，
- ## 修改排序數(shù)據(jù)最大單行長度為32
- set max_length_for_sort_data = 32;
- ## 執(zhí)行查詢SQL
- select name,age,city from staff where city = '深圳' order by age limit 10;
使用rowid 排序的話，整個(gè)SQL執(zhí)行流程又是怎樣的呢?
- MySQL 為對(duì)應(yīng)的線程初始化sort_buffer，放入需要排序的age字段，以及主鍵id;
- 從索引樹idx_city， 找到第一個(gè)滿足 city='深圳’條件的主鍵 id，也就是圖中的id=9;
- 到主鍵 id 索引樹拿到id=9的這一行數(shù)據(jù)， 取age和主鍵id的值，存到sort_buffer;
- 從索引樹idx_city 拿到下一個(gè)記錄的主鍵 id，即圖中的id=13;
- 重復(fù)步驟 3、4 直到city的值不等于深圳為止;
- 前面5步已經(jīng)查找到了所有city為深圳的數(shù)據(jù)，在 sort_buffer中，將所有數(shù)據(jù)根據(jù)age進(jìn)行排序;
- 遍歷排序結(jié)果，取前10行，并按照 id 的值回到原表中，取出city、name 和 age 三個(gè)字段返回給客戶端。
執(zhí)行示意圖如下：
對(duì)比一下全字段排序的流程，rowid 排序多了一次回表。
- 什么是回表?拿到主鍵再回到主鍵索引查詢的過程，就叫做回表”
我們通過optimizer_trace，可以看到是否使用了rowid排序的：
- ## 打開optimizer_trace，開啟統(tǒng)計(jì)
- set optimizer_trace = "enabled=on";
- ## 執(zhí)行SQL語句
- select name,age,city from staff where city = '深圳' order by age limit 10;
- ## 查詢輸出的統(tǒng)計(jì)信息
- select * from information_schema.optimizer_trace
全字段排序與rowid排序?qū)Ρ?/p>
- 全字段排序：sort_buffer內(nèi)存不夠的話，就需要用到磁盤臨時(shí)文件，造成磁盤訪問。
- rowid排序：sort_buffer可以放更多數(shù)據(jù)，但是需要再回到原表去取數(shù)據(jù)，比全字段排序多一次回表。
一般情況下，對(duì)于InnoDB存儲(chǔ)引擎，會(huì)優(yōu)先使用全字段排序。可以發(fā)現(xiàn) max_length_for_sort_data 參數(shù)設(shè)置為1024，這個(gè)數(shù)比較大的。一般情況下，排序字段不會(huì)超過這個(gè)值，也就是都會(huì)走全字段排序。
order by的一些優(yōu)化思路
我們?nèi)绾蝺?yōu)化order by語句呢?
- 因?yàn)閿?shù)據(jù)是無序的，所以就需要排序。如果數(shù)據(jù)本身是有序的，那就不用排了。而索引數(shù)據(jù)本身是有序的，我們通過建立聯(lián)合索引，優(yōu)化order by 語句。
- 我們還可以通過調(diào)整max_length_for_sort_data等參數(shù)優(yōu)化;
聯(lián)合索引優(yōu)化
再回顧下示例SQL的查詢計(jì)劃
- explain select name,age,city from staff where city = '深圳' order by age limit 10;
我們給查詢條件city和排序字段age，加個(gè)聯(lián)合索引idx_city_age。再去查看執(zhí)行計(jì)劃
- alter table staff add index idx_city_age(city,age);
- explain select name,age,city from staff where city = '深圳' order by age limit 10;
可以發(fā)現(xiàn)，加上idx_city_age聯(lián)合索引，就不需要Using filesort排序了。為什么呢?因?yàn)樗饕旧硎怯行虻模覀兛梢钥聪耰dx_city_age聯(lián)合索引示意圖，如下：
整個(gè)SQL執(zhí)行流程變成醬紫：
- 從索引idx_city_age找到滿足city='深圳’ 的主鍵 id
- 到主鍵 id索引取出整行，拿到 name、city、age 三個(gè)字段的值，作為結(jié)果集的一部分直接返回
- 從索引idx_city_age取下一個(gè)記錄主鍵id
- 重復(fù)步驟 2、3，直到查到第10條記錄，或者是不滿足city='深圳’ 條件時(shí)循環(huán)結(jié)束。
流程示意圖如下：
從示意圖看來，還是有一次回表操作。針對(duì)本次示例，有沒有更高效的方案呢?有的，可以使用覆蓋索引：
- 覆蓋索引：在查詢的數(shù)據(jù)列里面，不需要回表去查，直接從索引列就能取到想要的結(jié)果。換句話說，你SQL用到的索引列數(shù)據(jù)，覆蓋了查詢結(jié)果的列，就算上覆蓋索引了。”
我們給city，name，age 組成一個(gè)聯(lián)合索引，即可用到了覆蓋索引，這時(shí)候SQL執(zhí)行時(shí)，連回表操作都可以省去啦。
調(diào)整參數(shù)優(yōu)化
我們還可以通過調(diào)整參數(shù)，去優(yōu)化order by的執(zhí)行。比如可以調(diào)整sort_buffer_size的值。因?yàn)閟ort_buffer值太小，數(shù)據(jù)量大的話，會(huì)借助磁盤臨時(shí)文件排序。如果MySQL服務(wù)器配置高的話，可以使用稍微調(diào)整大點(diǎn)。
我們還可以調(diào)整max_length_for_sort_data的值，這個(gè)值太小的話，order by會(huì)走rowid排序，會(huì)回表，降低查詢性能。所以max_length_for_sort_data可以適當(dāng)大一點(diǎn)。
當(dāng)然，很多時(shí)候，這些MySQL參數(shù)值，我們直接采用默認(rèn)值就可以了。
使用order by 的一些注意點(diǎn)
沒有where條件，order by字段需要加索引嗎
日常開發(fā)過程中，我們可能會(huì)遇到?jīng)]有where條件的order by，那么，這時(shí)候order by后面的字段是否需要加索引呢。如有這么一個(gè)SQL，create_time是否需要加索引：
- select * from A order by create_time;
無條件查詢的話，即使create_time上有索引,也不會(huì)使用到。因?yàn)镸ySQL優(yōu)化器認(rèn)為走普通二級(jí)索引，再去回表成本比全表掃描排序更高。所以選擇走全表掃描,然后根據(jù)全字段排序或者rowid排序來進(jìn)行。
如果查詢SQL修改一下：
- select * from A order by create_time limit m;
無條件查詢,如果m值較小,是可以走索引的.因?yàn)镸ySQL優(yōu)化器認(rèn)為，根據(jù)索引有序性去回表查數(shù)據(jù),然后得到m條數(shù)據(jù),就可以終止循環(huán),那么成本比全表掃描小,則選擇走二級(jí)索引。
分頁limit過大時(shí)，會(huì)導(dǎo)致大量排序怎么辦?
假設(shè)SQL如下：
- select * from A order by a limit 100000,10
可以記錄上一頁最后的id，下一頁查詢時(shí)，查詢條件帶上id，如：where id > 上一頁最后id limit 10。
也可以在業(yè)務(wù)允許的情況下，限制頁數(shù)。
索引存儲(chǔ)順序與order by不一致，如何優(yōu)化?
假設(shè)有聯(lián)合索引 idx_age_name, 我們需求修改為這樣：查詢前10個(gè)員工的姓名、年齡，并且按照年齡小到大排序，如果年齡相同，則按姓名降序排。對(duì)應(yīng)的 SQL 語句就可以這么寫：
- select name,age from staff order by age ,name desc limit 10;
我們看下執(zhí)行計(jì)劃，發(fā)現(xiàn)使用到Using filesort。
這是因?yàn)椋琲dx_age_name索引樹中，age從小到大排序，如果age相同，再按name從小到大排序。而order by 中，是按age從小到大排序，如果age相同，再按name從大到小排序。也就是說，索引存儲(chǔ)順序與order by不一致。
我們?cè)趺磧?yōu)化呢?如果MySQL是8.0版本，支持Descending Indexes，可以這樣修改索引：
- CREATE TABLE `staff` (
- `id` bigint(11) NOT NULL AUTO_INCREMENT COMMENT '主鍵id',
- `id_card` varchar(20) NOT NULL COMMENT '身份證號(hào)碼',
- `name` varchar(64) NOT NULL COMMENT '姓名',
- `age` int(4) NOT NULL COMMENT '年齡',
- `city` varchar(64) NOT NULL COMMENT '城市',
- PRIMARY KEY (`id`),
- KEY `idx_age_name` (`age`,`name` desc) USING BTREE
- ) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8 COMMENT='員工表';
使用了in條件多個(gè)屬性時(shí)，SQL執(zhí)行是否有排序過程
如果我們有聯(lián)合索引idx_city_name，執(zhí)行這個(gè)SQL的話，是不會(huì)走排序過程的，如下：
- select * from staff where city in ('深圳') order by age limit 10;
但是，如果使用in條件，并且有多個(gè)條件時(shí)，就會(huì)有排序過程。
- explain select * from staff where city in ('深圳','上海') order by age limit 10;
這是因?yàn)?in有兩個(gè)條件，在滿足深圳時(shí)，age是排好序的，但是把滿足上海的age也加進(jìn)來，就不能保證滿足所有的age都是排好序的。因此需要Using filesort。
本文轉(zhuǎn)載自微信公眾號(hào)「撿田螺的小男孩」，可以通過以下二維碼關(guān)注。轉(zhuǎn)載本文請(qǐng)聯(lián)系撿田螺的小男孩公眾號(hào)。
