MySQL 算法的藝術(shù)源于 order by 排序的巧用，什么樣的配置選擇什么樣的排序模式是有一定的規(guī)定和訣竅，這個(gè)就是mysql對于排序的底層原理的選擇。

 今天就給我大家介紹一下關(guān)于mysql底層的排序模式的選擇和使用。

一：排序模式

二：如何選擇排序模式

三：排序算法

1.1一般的排序模式分為以下幾種：

1. sort_key, rowid，表明排序緩沖區(qū)元組包含排序鍵值和原始表行的行id，排序后需要使用行id進(jìn)行回表，這種算法也稱為

2. original filesort algorithm，回表排序算法

3. sort_key, additional_fields，表明排序緩沖區(qū)元組包含排序鍵值和查詢所需要的列，排序后直接從緩沖區(qū)元組取數(shù)據(jù)，無需回表，這種算法也稱為modified filesort algorithm(不回表排序)；

4. sort_key, packed_additional_fields：類似上一種形式，但是附加的列(如varchar類型)緊密地打包在一起，而不是使用固定長度的編碼

2.1：排序模式的選擇需要考慮的因素就是：max_length_for_sort_data

一般查詢該屬性值的方法如下：

show variables like '%sort_buffer%'

這個(gè)屬性默認(rèn)值大小為1024字節(jié)

1. 如果查詢列和排序列占用的大小超過這個(gè)值，那么會(huì)轉(zhuǎn)而使用sort_key, rowid模式；

2. 如果不超過，那么所有列都會(huì)放入sort buffer中，使用sort_key, additional_fields或者sort_key, packed_additional_fields模式；

3. 如果查詢的記錄太多，那么會(huì)使用sort_key, packed_additional_fields對可變列進(jìn)行壓縮。

3.1:排序算法同樣的原理也是通過判斷數(shù)據(jù)量的大小來選擇不同的排序算法。

如果排序取的結(jié)果很小，小于內(nèi)存，那么會(huì)使用優(yōu)先級(jí)隊(duì)列進(jìn)行堆排序

如下:

SELECT * from users u  where u.city ='昆明市'  ORDER by name LIMIT 10

 這樣的sql一般的適用于排序量很小，并且小于內(nèi)存值選擇的排序方式就是優(yōu)先級(jí)隊(duì)列進(jìn)行數(shù)據(jù)堆的排序。

在使用limit加order by排序的時(shí)則有兩種情況下的排序選擇

SELECT * from users u  where u.city ='昆明市'  ORDER by name LIMIT 100,1

limit a ，b  

1. 當(dāng)a太大的時(shí)候，如：limit 900,10（表數(shù)據(jù)為1000條記錄）a相對于數(shù)據(jù)源來說太大了的時(shí)候，排序就會(huì)自動(dòng)選擇最后面的數(shù)據(jù)開始排序，那么選擇使用sort buffer進(jìn)行快速排序

2. 當(dāng)a排序不太大的時(shí)候，則排序就是自動(dòng)選擇優(yōu)先級(jí)隊(duì)列進(jìn)行排序。

如果參與排序的數(shù)據(jù)sort buffer裝不下了，那么我們會(huì)一批一批的給sort buffer進(jìn)行內(nèi)存快速排序，結(jié)果放入排序臨時(shí)文件，最終使對所有排好序的臨時(shí)文件進(jìn)行歸并排序。

order by  總結(jié)

1. order by字段盡量使用固定長度的字段類型，因?yàn)榕判蜃侄尾恢С謮嚎s；

2. order by字段如果需要用可變長度，應(yīng)盡量控制長度，道理同上；

3. 查詢中盡量不用用select *，避免查詢過多，導(dǎo)致order by的時(shí)候sort buffer內(nèi)存不夠?qū)е峦獠颗判?，或者行大小超過了max_length_for_sort_data導(dǎo)致走了sort_key, rowid排序模式，使得產(chǎn)生了更多的磁盤讀，影響性能；

4. 嘗試給排序字段和相關(guān)條件加上聯(lián)合索引，能夠用到覆蓋索引最佳。