MySQL排序工作原理
在程序設(shè)計(jì)當(dāng)中，我們很多場(chǎng)景下都會(huì)用 group by 關(guān)鍵字。比如在分頁(yè)讀取數(shù)據(jù)時(shí)，為了避免重復(fù)掃描記錄，這就是必須要使用 group by 了。
比如我們使用如下 DDL 創(chuàng)建表：
- CREATE TABLE `user_info` (
- `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主鍵ID',
- `city` varchar(16) NOT NULL COMMENT '城市',
- `name` varchar(16) NOT NULL COMMENT '姓名',
- `age` int(11) NOT NULL COMMENT '年齡',
- `addr` varchar(128) DEFAULT NULL COMMENT '地址',
- PRIMARY KEY (`id`),
- KEY `city` (`city`)
- ) ENGINE=InnoDB DEFAULT CHARSET=utf8
并且我們會(huì)執(zhí)行如下查詢語(yǔ)句
- SELECT city,`name`,age FROM user_info WHERE city='上海' ORDER BY `name` LIMIT 1000;
全字段排序
因?yàn)樯厦娴慕ū碚Z(yǔ)句已經(jīng)在 city 字段上面創(chuàng)建索引了，當(dāng)我們使用 EXPLAIN 命令時(shí)，會(huì)有如下結(jié)果：
上面 Extra 字段中的 “Using filesort” 表示的就是需要排序，MySQL 會(huì)為每個(gè)線程分配一塊內(nèi)存用于排序，成為 sort_buffer。下面我們看一下 index(city) 的結(jié)構(gòu)示意圖。
執(zhí)行流程如下：
- 初始化 sort_buffer，確定放入 city name age 這 3 個(gè)字段；
- 從 city 索引中獲取到***個(gè) city='上海' 的記錄，也就是 id_x；
- 到主鍵索引中獲取對(duì)應(yīng)的記錄，并取出 name city age 的值放入 sort_buffer；
- 取下一條符合條件的記錄，重復(fù) 3 4 的操作，直至不符合條件為止；
- 對(duì) sort_buffer 中的數(shù)據(jù)按照 name 做快速排序；
- 取出前 1000 條數(shù)據(jù)并返回。
我們暫時(shí)叫這種排序過(guò)程為“全字段排序”，如下所示：
圖中的“按 name 排序” 可能在內(nèi)存中，也可能使用磁盤(pán)文件排序，這取決與排序所需要的內(nèi)存和 sort_buffer_size 。sort_buffer_size 就是 MySQL 為排序開(kāi)辟的內(nèi)存大小，當(dāng)所需內(nèi)存小于 sort_buffer_size 時(shí)，就直接在內(nèi)存中完成排序，如果所需要的內(nèi)存 大于 sort_buffer_size ，就需要額外的磁盤(pán)空間輔助排序。
rowid 排序
上面的算法在數(shù)據(jù)量比較大的時(shí)候，可能會(huì)出現(xiàn)一些問(wèn)題。因?yàn)樵谂判虻臅r(shí)候，存放了所有的返回字段，增加了 排序空間 （sort_buffer）的壓力。
- SET max_length_for_sort_data=16;
max_length_for_sort_data 是MySQL 限制排序行大小的參數(shù)。意思是，如果排序行大小超過(guò)了這個(gè)值，就會(huì)另選排序算法。上面 name city age 3 個(gè)字段的大小為 36，大于 16 ，在新的算法中將只有 name (排序字段) 和id 參與 sort_buffer 中的排序。過(guò)程如下
- 初始化 sort_buffer，確定放入 name id 這 2 個(gè)字段；
- 從 city 索引中獲取到***個(gè) city='上海' 的記錄，也就是 id_x；
- 到主鍵索引中獲取對(duì)應(yīng)的記錄，并取出 name id 的值放入 sort_buffer；
- 取下一條符合條件的記錄，重復(fù) 3 4 的操作，直至不符合條件為止；
- 對(duì) sort_buffer 中的數(shù)據(jù)按照 name 做快速排序；
- 取出前 1000 條數(shù)據(jù)，然后根據(jù) id 取出對(duì)應(yīng)記錄的 name city age 3 個(gè)字段并返回結(jié)果。
這種排序過(guò)程，我們稱為 rowid 排序，過(guò)程如下所示：
全字段排序 VS rowid 排序
從上面 2 個(gè)流程看來(lái)，如果內(nèi)存足夠時(shí)，MySQL 會(huì)讓返回值中所有字段存放在排序空間。當(dāng)MySQL 內(nèi)存過(guò)小時(shí)，才會(huì)考慮使用rowid 排序。但是從上面的流程看來(lái)，rowid 排序在返回結(jié)果前，還會(huì)再一次的回表。因此MySQL 認(rèn)為內(nèi)存充足的時(shí)候，會(huì)優(yōu)先采用 全字段排序。
上面的場(chǎng)景是：city 字段過(guò)濾后，name 字段不是有序的。其實(shí)我們可以通過(guò)聯(lián)合索引來(lái)規(guī)避掉 name 字段的排序。
- alter table user_info add index idx_city_user(city, name);
下面我們看一下聯(lián)合索引的示意圖：
從上面流程圖可以看出，當(dāng)我們?nèi)〕?city='上海' 的記錄時(shí)，name的字段也是有序的。過(guò)程如下
- 從 (city, name)索引中獲取到***個(gè) city='上海' 的記錄 id_x；
- 到主鍵索引中獲取對(duì)應(yīng)的記錄，并取出 name city age 的值作為結(jié)果集的一部分直接返回；
- 取下一條符合條件的記錄，重復(fù) 2 3 的操作，直至不符合條件或者達(dá)到 1000 條為止；
從聯(lián)合索引看來(lái)，我們是可以不用排序操作了，那么我們是否可以直接通過(guò) 索引就直接返回結(jié)果呢？也就是不要回表操作。答案是有的，那就是覆蓋索引。
- alter table user_info add index idx_city_user_age(city, name, age);
當(dāng)執(zhí)行查詢語(yǔ)句時(shí)，不僅 name 中的字段是有序的，并且 索引中已經(jīng)包含了結(jié)果集中的所有字段，過(guò)程如下：
- 從 (city, name,age)索引中獲取到***個(gè) city='上海' 的記錄，并取出 name city age 的值作為結(jié)果集的一部分直接返回；
- 取下一條符合條件的記錄，重復(fù) 1 2 的操作，直至不符合條件或者達(dá)到 1000 條為止；
