order by 方式

1. 排序是存儲(chǔ)引擎層來(lái)做的。
2. 排序也是有多種策略可以供選擇和優(yōu)化的，與索引一樣，在某些情況下可能會(huì)使用錯(cuò)誤的策略，導(dǎo)致排序成本過(guò)高。這時(shí)也和索引一致，可以通過(guò)強(qiáng)制使用某種排序方式來(lái)降低排序速度。
3. 排序分為內(nèi)存排序和文件排序。explain file_sort并不代表使用了文件排序，只是說(shuō)明需要對(duì)接過(guò)數(shù)據(jù)進(jìn)行排序。

order by 存在兩種排序方式。
通過(guò)max_length_for_sort_data（行最大長(zhǎng)度配置）來(lái)決定是使用全字段排序，還是rowId排序。

對(duì)于這兩種排序優(yōu)先級(jí)來(lái)說(shuō)，全字段排序優(yōu)先級(jí)最高。通常InnoDB認(rèn)為，rowid排序會(huì)要求回表多造成磁盤(pán)讀，因此不會(huì)被優(yōu)先選擇（數(shù)據(jù)存儲(chǔ)在磁盤(pán)上，回表通常要去磁盤(pán)加載數(shù)據(jù)到Buffer Pool）。

除了兩種排序方式外，還有一種排序算法：優(yōu)先隊(duì)列排序算法。該算法的使用取決于返回結(jié)果的多少。通常我們?cè)诜猪?yè)場(chǎng)景中都會(huì)使用到該算法，因?yàn)榉祷財(cái)?shù)據(jù)較少，（limit 20）。

全字段排序

在max_length_for_sort_data（排序數(shù)據(jù)一行最大長(zhǎng)度）足夠時(shí)，會(huì)采用全字段排序的方式。

1. 初始化Sort Buffer 字段（查詢(xún)?nèi)孔侄危?/li>
2. 確認(rèn)使用的索引，并將通過(guò)索引獲取數(shù)據(jù)可能需要回表查詢(xún)?nèi)繑?shù)據(jù)。
3. 將全部數(shù)據(jù) 存入sort buffer
4. 基于排序字段排序
5. 返回排序結(jié)果

rowId 排序

在max_length_for_sort_data（排序數(shù)據(jù)一行最大長(zhǎng)度）不足時(shí)，會(huì)使用rowId排序方式。

1. 初始化sort buffer字段 （排序字段+id）
2. 確認(rèn)使用的索引，然后回表查詢(xún)排序字段，
3. 將字段放入 sort buffer中
4. 執(zhí)行排序
5. 回表查詢(xún)結(jié)果數(shù)據(jù)（返回全字段）
6. 返回結(jié)果

思考： 如果 索引已經(jīng)包含排序字段，那么還需要回表去查排序字段么？比如，A B聯(lián)合索引，排序結(jié)果是 B, A。

猜想： 如果是rowId排序，應(yīng)該是不需要回表查字段的，剩余排序方式一致。

優(yōu)先隊(duì)列排序算法

優(yōu)先隊(duì)列排序算法可以理解為，假如有10000條數(shù)據(jù)為只需要最大的10條。那么，此時(shí)我是否需要保證10000條數(shù)據(jù)都有序呢？

其實(shí)從效率上來(lái)講，肯定是沒(méi)有必要的。因?yàn)橹恍枰?0條，那么我就只用保證10條有序就OK。方式就是，創(chuàng)建一個(gè)隊(duì)列，將10條數(shù)據(jù)放進(jìn)隊(duì)列中，后續(xù)數(shù)據(jù)一次進(jìn)行比較，如果大于其中任何一條，替換該位置數(shù)據(jù)即可。這樣排序效率遠(yuǎn)大于歸并排序。

因此InnoDB在情況允許時(shí)會(huì)使用優(yōu)先隊(duì)列排序算法。

思考：如果此時(shí)需要返回1000條，那么還會(huì)用到有限隊(duì)列排序么？

解答：從邏輯上來(lái)講，優(yōu)先隊(duì)列排序效率也不是一定高于歸并排序的。比如一共100條數(shù)據(jù)，我要取最大99條，那么需要一個(gè) 99 條數(shù)據(jù)的隊(duì)列，然后 最后一條數(shù)據(jù)要依次比對(duì) 100 次， 然后 再對(duì) 隊(duì)列中 99 條數(shù)據(jù) 做排序。排序次數(shù)都一致。
而且，由于隊(duì)列原因，占用內(nèi)存也比較高。因此，除了數(shù)量外，還需要考慮內(nèi)存占用情況。如果超過(guò) sort buffer 大小那么也不會(huì)采用優(yōu)先隊(duì)列算法。

filesort 理解

如何判斷到底是內(nèi)存排序還是磁盤(pán)臨時(shí)文件排序

問(wèn)題思考

消除 filesort

select b from t where a = 1 order by b;

此時(shí) 如何創(chuàng)建索引？

select b from t where a in (1,2) order by b;

此時(shí)如何避免filesort

select b from t where a in (1,2) order by b limit 100000,100;