ORDER BY調(diào)優(yōu)的核心原理，原則是利用索引的有序性跳過(guò)排序環(huán)節(jié)

關(guān)于ORDER BY語(yǔ)句的一些嘗試

我們使用employees表進(jìn)行嘗試，索引情況如下

在執(zhí)行計(jì)劃的結(jié)果中，Extra里如果存在，Using filesort則表示，排序沒(méi)有使用到索引。

explain
select *
from employees
order by first_name,last_name;

結(jié)果

并沒(méi)有用到索引，發(fā)生了全表掃描

explain
select *
from employees
order by first_name,last_name
limit 10;

結(jié)果

這次的查詢(xún)就用到了索引。為什么一次是ALL，一次是index呢？
因?yàn)榈谝淮蜗喈?dāng)于對(duì)整張表進(jìn)行排序的，排序是基于成本計(jì)算的，在優(yōu)化器發(fā)現(xiàn)全表掃描開(kāi)銷(xiāo)更低時(shí)，會(huì)直接使用全表掃描。而第二次是僅僅對(duì)前10條數(shù)據(jù)進(jìn)行排序，掃描索引的成本要小于掃面全表，所以用到了索引。
----------------------------------------------------------------------------------------------------------------->

explain
select *
from employees
where first_name = 'Bader'
order by last_name;

結(jié)果

這句SQL是用到了索引排序的，當(dāng)執(zhí)行查詢(xún)時(shí)，查找出來(lái)的數(shù)據(jù)為[‘Bader’，last_name[i]，emp_no]，因?yàn)樗饕怯行虻?#xff0c;'Bader’是確定的，那么數(shù)據(jù)已經(jīng)按照l(shuí)ast_name排好序了，就跳過(guò)了排序的環(huán)節(jié)。
----------------------------------------------------------------------------------------------------------------->

explain
select *
from employees
where first_name < 'Bader'
order by first_name;

結(jié)果

根據(jù)執(zhí)行結(jié)果是使用了索引的，因?yàn)樵趫?zhí)行查詢(xún)語(yǔ)句時(shí)，查找出來(lái)的數(shù)據(jù)為[first_name，last_name，emp_no]，這一部分?jǐn)?shù)據(jù)已經(jīng)是按照f(shuō)irst_name排好序的，所以不需要再次進(jìn)行排序了。
----------------------------------------------------------------------------------------------------------------->

explain
select *
from employees
where first_name = 'Bader'and last_name > 'Peng'
order by last_name;

結(jié)果

跟上面的同理，因?yàn)樵趫?zhí)行查詢(xún)語(yǔ)句時(shí)，查找出來(lái)的數(shù)據(jù)為[Bader，last_name[i]，emp_no]，這一部分?jǐn)?shù)據(jù)已經(jīng)是按照l(shuí)ast_name排好序的，所以不需要再次進(jìn)行排序了。
----------------------------------------------------------------------------------------------------------------->

explain
select *
from employees
order by first_name,emp_no limit 10;

結(jié)果

根據(jù)執(zhí)行結(jié)果 ，該語(yǔ)句沒(méi)有用到索引，因?yàn)閮蓚€(gè)排序字段存在于不同的兩個(gè)索引中，會(huì)先按first_name進(jìn)行排序，再將相同first_name的數(shù)據(jù)按照emp_no進(jìn)行排序。
----------------------------------------------------------------------------------------------------------------->

explain
select *
from employees
order by first_name desc ,last_name asc limit 10;

結(jié)果

因?yàn)樗饕械膬蓚€(gè)字段，在進(jìn)行排序中的升降序不一致，所以無(wú)法使用索引。
----------------------------------------------------------------------------------------------------------------->

explain
select *
from employees
where first_name < 'Bader'
order by last_name limit 10;

結(jié)果

根據(jù)結(jié)果得知，在進(jìn)行查詢(xún)時(shí)使用了索引，但在排序時(shí)使用的是Using filesort。說(shuō)明排序時(shí)沒(méi)有用到索引。組合索引中part1范圍查詢(xún)，使用part2進(jìn)行排序是無(wú)法使用索引排序的。

排序模式

Using filesort排序原理，目前MySQL使用了三種排序模式

模式一：rowid排序（常規(guī)排序）

排序過(guò)程

1. 從表中獲取滿足where條件的數(shù)據(jù)。
2. 對(duì)于每條記錄，將記錄的主鍵及排序字段（id,order_column）取出放入sort buffer（由sort_buffer_size控制大小）。
3. 如果sort buffer能存放所有滿足條件的（id,order_column），則進(jìn)行排序；否則，當(dāng)sort buffer存滿后，會(huì)將sort buffer中的數(shù)據(jù)排序并存放到臨時(shí)文件中。
   • 在沒(méi)有產(chǎn)生臨時(shí)文件時(shí)，在內(nèi)存中使用快速排序算法
   • 如果產(chǎn)生了臨時(shí)文件，則需要利用歸并排序算法，從而保證記錄有序
4. 掃描排序好的（id,order_column）數(shù)據(jù)，并利用id去取select需要返回的其他字段。
5. 返回結(jié)果集。

排序特點(diǎn)

• 看sort buffer是否能存放查詢(xún)出來(lái)的所有的結(jié)果集，如果不滿足，就會(huì)差生臨時(shí)文件
• 一次排序需要兩次IO
  • 第一次，把查詢(xún)出來(lái)的（id,order_column）結(jié)果集放入sort buffer中；第二次，通過(guò)id去獲取需要返回的其他字段。由于返回結(jié)果是按照order_column進(jìn)行排序的，所以主鍵id是亂序的，會(huì)存在隨機(jī)IO的問(wèn)題。之前文中提到，在用主鍵id取值前，會(huì)按照主鍵id進(jìn)行排序，并放入一個(gè)緩存中，該緩存大小是由read_rnd_buffer_size控制，接著再去取記錄，從而把隨機(jī)IO轉(zhuǎn)換成順序IO。

模式二：全字段排序（優(yōu)化排序）

排序過(guò)程

跟第一種模式相比，有幾點(diǎn)不同

• 直接取出表中需要的所有字段，放到sort buffer種
• 由于sort buffer已經(jīng)包含了查詢(xún)需要的所有的字段，因此sort buffer種排序完成后直接返回結(jié)果集

全字段排序 vs rowid排序

• 優(yōu)點(diǎn)：性能的提升，無(wú)需兩次IO，因?yàn)槿侄闻判蛞呀?jīng)將需要的所有字段存儲(chǔ)到了sort buffer種，無(wú)需再次用主鍵id去表中獲取
• 缺點(diǎn)：由于全字段排序會(huì)將需要的所有的字段放入sort buffer中，所以占用空間比較大，如果sort buffer不夠大，那么很容易產(chǎn)生臨時(shí)文件

排序算法的選擇

• max_length_for_sort_data：當(dāng)OEDER BY中出現(xiàn)的字段的總長(zhǎng)度小于該值，使用全字段排序，反之則使用rowid排序。

模式三：打包字段排序

• MySQL5.7引入
• 與模式二工作原理一致，不同點(diǎn)在于會(huì)將字段緊密的排列在一起，而不是固定長(zhǎng)度的空間。
  • 例如：一個(gè)字段定義為VARCHAR(32)，值為’yes’；在不打包的情況下占用32字節(jié)，打包的情況下2+3字節(jié)。

參數(shù)匯總

變量	作用
sort_buffer_size	指定sort buffer的大小
max_length_for_fort_data	當(dāng)ORDER BY中出現(xiàn)字段的總長(zhǎng)度小于該值時(shí)使用全字段排序，反之使用rowid排序
read_rnd_buffer_size	按照主鍵排序后存放的緩存區(qū)大小

使用optimizer_trace分析排序過(guò)程

explain展示的排序方式很有限，僅僅是Using filesort，如果我們想了解更多的細(xì)節(jié)就需要使用optimizer_trace進(jìn)行分析了。
以下面語(yǔ)句為例：

select *
from employees
where first_name < 'Bader'
order by last_name;

執(zhí)行

SET OPTIMIZER_TRACE="enabled=on",END_MARKERS_IN_JSON=on;
SET optimizer_trace_offset=-30,optimizer_trace_limit=30;

開(kāi)啟OPTIMIZER_TRACE，執(zhí)行示例SQL語(yǔ)句，再次執(zhí)行

select * from information_schema.OPTIMIZER_TRACE where QUERY like '%Bader%';

獲取分析結(jié)果，將trace字段的內(nèi)容復(fù)制出來(lái)進(jìn)行分析；
我們主要關(guān)注的是filesort_summary，

“filesort_summary”: {
“memory_available”: 262144,
“key_size”: 265,
“row_size”: 399,
“max_rows_per_buffer”: 502,
“num_rows_estimate”: 927744,
“num_rows_found”: 22287,
“num_initial_chunks_spilled_to_disk”: 0,
“peak_memory_used”: 204314,
“sort_algorithm”: “std::sort”,
“unpacked_addon_fields”: “using_priority_queue”,
“sort_mode”: “<varlen_sort_key, additional_fields>”
}

其相關(guān)字段解讀如下：

• memory_available：可用內(nèi)存，其實(shí)就是fort_buffer_size設(shè)置的值
• num_rows_found：有多少條數(shù)據(jù)參與排序，越小越好
• num_initial_chunks_spilled_to_disk：產(chǎn)生了幾個(gè)臨時(shí)文件，0表示完全基于內(nèi)存排序
• sort_mode
  • <varlen_sort_key,rowid>：使用了rowid排序模式
  • <varlen_sort_key, additional_fields>：使用了全字段排序模式
  • <varlen_sort_key, packed_additional_fields>：使用了打包字段排序模式

如何調(diào)優(yōu)ORDER BY

• 利用索引，防止filesort發(fā)生
• 如果發(fā)生了filesort，且無(wú)法避免，就要對(duì)filesort進(jìn)行優(yōu)化

如何調(diào)優(yōu)filesort

• 調(diào)大sort_buffer_size，減少/避免臨時(shí)文件的產(chǎn)生，從而進(jìn)行的歸并操作
  • 當(dāng)optimizer_trace的結(jié)果中 num_initial_chunks_spilled_to_disk的值較大時(shí)，需要調(diào)整
  • show status like ‘%sort_merge_passes%’;查看發(fā)生歸并操作的次數(shù)
• 調(diào)大read_rnd_buffer_size，讓一次順序IO返回更多的結(jié)果
• 設(shè)置合理的max_length_for_sort_data的值