在使用explain分析查詢的時(shí)候涕滋，索引掃描排序顯示Using index/where。而文件排序顯示Using filesort疗垛。
全字段排序
MySQL會(huì)給每個(gè)線程分配一塊內(nèi)存用于排序症汹，稱為sort_buffer。
根據(jù)排序字段排序的操作可能在內(nèi)存中排序贷腕，也可能需要使用外部排序背镇，這取決于排序所
需的內(nèi)存和參數(shù)sort_buffer_size。
如果排序的數(shù)據(jù)量太大內(nèi)存放不下泽裳，就會(huì)使用磁盤臨時(shí)文件排序瞒斩。
可以通過(guò)OPTIMIZER_TRACE 的結(jié)果查看是否使用了磁盤臨時(shí)文件排序。
number_of_tmp_files表示的是涮总，排序過(guò)程中使用的臨時(shí)文件數(shù)胸囱，sort_buffer_size越小需要的文件數(shù)越多，如果sort_buffer_size超過(guò)了需要排序的數(shù)據(jù)的大小瀑梗，number_of_tmp_files就是0烹笔。
內(nèi)存放不下時(shí)，就需要使用外部排序抛丽，外部排序一般使用歸并排序算法谤职。可以這么簡(jiǎn)單理解铺纽，MySQL將需要排序的數(shù)據(jù)分成12份柬帕，每一份單獨(dú)排序后存在這些臨時(shí)文件中哟忍。然后把
這12個(gè)有序文件再合并成一個(gè)有序的大文件狡门。
- 表定義如下：
`CREATE TABLE `t` (
ìdìnt(11) NOT NULL,
`city` varchar(16) NOT NULL,
`name` varchar(16) NOT NULL,
àgeìnt(11) NOT NULL,
àddr` varchar(128) DEFAULT NULL,
PRIMARY KEY (ìd`),
KEY `city` (`city`)
) ENGINE=InnoDB;`
- 查詢語(yǔ)句如下：
select city,name,age from t where city='杭州' order by name limit 1000 - 執(zhí)行流程如下：
- 初始化sort_buffer，確定放入name锅很、city其馏、age這三個(gè)字段；
- 從索引city找到第一個(gè)滿足city='杭州’條件的主鍵id爆安，也就是圖中的ID_X叛复；
- 到主鍵id索引取出整行，取name扔仓、city褐奥、age三個(gè)字段的值，存入sort_buffer中翘簇；
- 從索引city取下一個(gè)記錄的主鍵id撬码；
- 重復(fù)步驟3、4直到city的值不滿足查詢條件為止版保，對(duì)應(yīng)的主鍵id也就是圖中的ID_Y呜笑；
- 對(duì)sort_buffer中的數(shù)據(jù)按照字段name做快速排序夫否；
- 按照排序結(jié)果取前1000行返回給客戶端。
rowId排序
如果查詢要返回的字段很多的話叫胁，那么sort_buffer里面要放的字段數(shù)太多凰慈，這樣內(nèi)存里能夠同時(shí)放下的行數(shù)很少，要分成很多個(gè)臨時(shí)文件驼鹅，排序的性能會(huì)很差微谓。
max_length_for_sort_data，是MySQL中專門控制用于排序的行數(shù)據(jù)的長(zhǎng)度的一個(gè)參數(shù)输钩。它的意思是堰酿，如果單行的長(zhǎng)度超過(guò)這個(gè)值，MySQL就認(rèn)為單行太大张足，要換一個(gè)算法.
新的算法放入sort_buffer的字段触创，只有要排序的列（即name字段）和主鍵id.
然后排序，排序完成后通過(guò)對(duì)應(yīng)的主鍵再回表查詢需要的字段为牍。相比之下哼绑，多了一次回表。
- 執(zhí)行流程如下：
- 初始化sort_buffer碉咆，確定放入兩個(gè)字段抖韩，即name和id；
- 從索引city找到第一個(gè)滿足city='杭州’條件的主鍵id疫铜，也就是圖中的ID_X茂浮；
- 到主鍵id索引取出整行，取name壳咕、id這兩個(gè)字段席揽，存入sort_buffer中；
- 從索引city取下一個(gè)記錄的主鍵id谓厘；
- 重復(fù)步驟3幌羞、4直到不滿足city='杭州’條件為止，也就是圖中的ID_Y竟稳；
- 對(duì)sort_buffer中的數(shù)據(jù)按照字段name進(jìn)行排序属桦；
- 遍歷排序結(jié)果，取前1000行他爸，并按照id的值回到原表中取出city聂宾、name和age三個(gè)字段返回給客戶端。
覆蓋索引優(yōu)化排序
不是所有的order by都需要排序诊笤，如果條件允許系谐，可以創(chuàng)建聯(lián)合索引，利用聯(lián)合索引查詢結(jié)果的天然有序盏混，避免排序蔚鸥，且聯(lián)合索引覆蓋了查詢尋的所有結(jié)果時(shí)惜论，可避免回表，性能大幅度提升止喷。但索引維護(hù)代價(jià)需要慎重考慮馆类。
關(guān)鍵點(diǎn)：聯(lián)合索引的最左原則和有序性。order by 最后面的字段放在索引的最后弹谁，如果是多字段排序乾巧，order by 后面的字段順序與索引中字段的相對(duì)順序保持一致。
查詢中存在范圍查找或者查詢條件不滿足最左原則時(shí)预愤，無(wú)法使用索引排序沟于。
