淺談MySQL排序道理與案例剖析。本站提示廣大學習愛好者：（淺談MySQL排序道理與案例剖析）文章只能為提供參考，不一定能成為您想要的結果。以下是淺談MySQL排序道理與案例剖析正文
媒介
排序是數據庫中的一個根本功效，MySQL也不破例。用戶經由過程Order by語句即能到達將指定的成果集排序的目標，其實不只僅是Order by語句，Group by語句，Distinct語句都邑隱含應用排序。本文起首會簡略引見SQL若何應用索引防止排序價值，然後會引見MySQL完成排序的外部道理，並引見與排序相干的參數，最初會給出幾個“奇異”排序例子，來談談排序分歧性成績，並解釋發生景象的實質緣由。
1.排序優化與索引應用
為了優化SQL語句的排序機能，最好的情形是防止排序，公道應用索引是一個不錯的辦法。由於索引自己也是有序的，假如在須要排序的字段下面樹立了適合的索引，那末便可以跳過排序的進程，進步SQL的查詢速度。上面我經由過程一些典范的SQL來講明哪些SQL可以應用索引削減排序，哪些SQL不克不及。假定t1表存在索引key1(key_part1,key_part2),key2(key2)
a.可以應用索引防止排序的SQL
SELECT * FROM t1 ORDER BY key_part1,key_part2; SELECT * FROM t1 WHERE key_part1 = constant ORDER BY key_part2; SELECT * FROM t1 WHERE key_part1 > constant ORDER BY key_part1 ASC; SELECT * FROM t1 WHERE key_part1 = constant1 AND key_part2 > constant2 ORDER BY key_part2;
b.不克不及應用索引防止排序的SQL
//排序字段在多個索引中，沒法應用索引排序 SELECT * FROM t1 ORDER BY key_part1,key_part2, key2; //排序鍵次序與索引中列次序紛歧致，沒法應用索引排序 SELECT * FROM t1 ORDER BY key_part2, key_part1; //起落序紛歧致，沒法應用索引排序 SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC; //key_part1是規模查詢，key_part2沒法應用索引排序 SELECT * FROM t1 WHERE key_part1> constant ORDER BY key_part2;
2.排序完成的算法
關於不克不及應用索引防止排序的SQL，數據庫不能不本身完成排序功效以知足用戶需求，此時SQL的履行籌劃中會湧現“Using filesort”，這裡須要留意的是filesort其實不意味著就是文件排序，其實也有能夠是內存排序，這個重要由sort_buffer_size參數與成果集年夜小肯定。MySQL外部完成排序重要有3種方法，慣例排序，優化排序和優先隊列排序，重要觸及3種排序算法：疾速排序、合並排序和堆排序。假定表構造和SQL語句以下：
CREATE TABLE t1(id int, col1 varchar(64), col2 varchar(64), col3 varchar(64), PRIMARY KEY(id),key(col1,col2)); SELECT col1,col2,col3 FROM t1 WHERE col1>100 ORDER BY col2;
a.慣例排序
(1).從表t1中獲得知足WHERE前提的記載
(2).關於每筆記錄，將記載的主鍵+排序鍵(id,col2)掏出放入sort buffer
(3).假如sort buffer可以寄存一切知足前提的(id,col2)對，則停止排序；不然sort buffer滿後，停止排序並固化莅臨時文件中。(排序算法采取的是疾速排序算法)
(4).若排序中發生了暫時文件，須要應用合並排序算法，包管暫時文件中記載是有序的
(5).輪回履行上述進程，直到一切知足前提的記載全體介入排序
(6).掃描排好序的(id,col2)對，並應用id去撈取SELECT須要前往的列(col1,col2,col3)
(7).將獲得的成果集前往給用戶。
從上述流程來看，能否應用文件排序重要看sort buffer能否能容下須要排序的(id,col2)對，這個buffer的年夜小由sort_buffer_size參數掌握。另外一次排序須要兩次IO，一次是撈(id,col2),第二次是撈(col1,col2,col3)，因為前往的成果集是按col2排序，是以id是亂序的，經由過程亂序的id去撈(col1,col2,col3)時會發生年夜量的隨機IO。關於第二次MySQL自己一個優化，即在撈之前起首將id排序，並放入緩沖區，這個緩存區年夜小由參數read_rnd_buffer_size掌握，然後有序去撈記載，將隨機IO轉為次序IO。
b.優化排序
慣例排序方法除排序自己，還須要額定兩次IO。優化的排序方法絕對於慣例排序，削減了第二次IO。重要差別在於，放入sort buffer不是(id,col2),而是(col1,col2,col3)。因為sort buffer中包括了查詢須要的一切字段，是以排序完成後可以直接前往，無需二次撈數據。這類方法的價值在於，異樣年夜小的sort buffer，能寄存的(col1,col2,col3)數量要小於(id,col2)，假如sort buffer不敷年夜，能夠招致須要寫暫時文件，形成額定的IO。固然MySQL供給了參數max_length_for_sort_data，只要當排序元組小於max_length_for_sort_data時，能力應用優化排序方法，不然只能用慣例排序方法。
c.優先隊列排序
為了獲得終究的排序成果，不管如何，我們都須要將一切知足前提的記載停止排序能力前往。那末絕對於優化排序方法，能否還有優化空間呢？5.6版本針對Order by limit M，N語句，在空間層面做了優化，參加了一種新的排序方法--優先隊列，這類方法采取堆排序完成。堆排序算法特點正好可以解limit M，N 這類排序的成績，固然依然須要一切元素介入排序，然則只須要M+N個元組的sort buffer空間便可，關於M，N很小的場景，根本不會由於sort buffer不敷而招致須要暫時文件停止合並排序的成績。關於升序，采取年夜頂堆，終究堆中的元素構成了最小的N個元素，關於降序，采取小頂堆，終究堆中的元素構成了最年夜的N的元素。
3.排序紛歧致成績
案例1
Mysql從5.5遷徙到5.6今後，發明分頁湧現了反復值。
測試表與數據：
create table t1(id int primary key, c1 int, c2 varchar(128)); insert into t1 values(1,1,'a'); insert into t1 values(2,2,'b'); insert into t1 values(3,2,'c'); insert into t1 values(4,2,'d'); insert into t1 values(5,3,'e'); insert into t1 values(6,4,'f'); insert into t1 values(7,5,'g');
假定每頁3筆記錄，第一頁limit 0,3和第二頁limit 3,3查詢成果以下：
我們可以看到 id為4的這筆記錄竟然同時湧現在兩次查詢中，這顯著是不相符預期的，並且在5.5版本中沒有這個成績。發生這個景象的緣由就是5.6針對limit M,N的語句采取了優先隊列，而優先隊列采取堆完成，好比上述的例子order by c1 asc limit 0，3 須要采取年夜小為3的年夜頂堆；limit 3，3須要采取年夜小為6的年夜頂堆。因為c1為2的記載有3條，而堆排序長短穩固的(關於雷同的key值，沒法包管排序後與排序前的地位分歧)，所以招致分頁反復的景象。為了不這個成績，我們可以在排序中加上獨一值，好比主鍵id，如許因為id是獨一的，確保介入排序的key值不雷同。將SQL寫成以下：
select * from t1 order by c1,id asc limit 0,3; select * from t1 order by c1,id asc limit 3,3;
案例2
兩個相似的查詢語句，除前往列分歧，其它都雷同，但排序的成果紛歧致。
測試表與數據：
create table t2(id int primary key, status int, c1 varchar(255),c2 varchar(255),c3 varchar(255),key(c1));
insert into t2 values(7,1,'a',repeat('a',255),repeat('a',255));
insert into t2 values(6,2,'b',repeat('a',255),repeat('a',255));
insert into t2 values(5,2,'c',repeat('a',255),repeat('a',255));
insert into t2 values(4,2,'a',repeat('a',255),repeat('a',255));
insert into t2 values(3,3,'b',repeat('a',255),repeat('a',255));
insert into t2 values(2,4,'c',repeat('a',255),repeat('a',255));
insert into t2 values(1,5,'a',repeat('a',255),repeat('a',255));
分離履行SQL語句：
select id,status,c1,c2 from t2 force index(c1) where c1>='b' order by status; select id,status from t2 force index(c1) where c1>='b' order by status;
履行成果以下：
看看二者的履行籌劃能否雷同
為了解釋成績，我在語句中加了force index的hint，確保能走上c1列索引。語句經由過程c1列索引撈取id，然後去表中撈取前往的列。依據c1列值的年夜小，記載在c1索引中的絕對地位以下：
(c1,id)===(b,6),(b,3),(5,c),(c,2)，對應的status值分離為2 3 2 4。從表中撈取數據並按status排序，則絕對地位變成(6,2,b),(5,2,c),(3,3,c),(2,4,c)，這就是第二條語句查詢前往的成果，那末為何第一條查詢語句(6,2,b),(5,2,c)是更換次序的呢？這裡要看我之條件到的a.慣例排序和b.優化排序中標紅的部門，便可以明確緣由了。因為第一條查詢前往的列的字節數跨越了max_length_for_sort_data，招致排序采取的是慣例排序，而在這類情形下MYSQL將rowid排序，將隨機IO轉為次序IO，所以前往的是5在前，6在後；而第二條查詢采取的是優化排序，沒有第二次撈取數據的進程，堅持了排序跋文錄的絕對地位。關於第一條語句，若想采取優化排序，我們將max_length_for_sort_data設置調年夜便可，好比2048。
上面是自己關於mysql 自界說排序（field，INSTR，locate）的一點心得，願望對年夜家有所贊助
起首解釋這裡有三個函數（order by field，ORDER BY INSTR，ORDER BY locate）
原表：
id user pass aaa aaa bbb bbb ccc ccc ddd ddd eee eee fff fff
上面是我履行後的成果：
SELECT * FROM `user` order by field(2,3,5,4,id) asc
id user pass aaa aaa ccc ccc ddd ddd eee eee fff fff bbb bbb
依據成果剖析：order by field(2,3,5,4,1,6) 成果顯示次序為：1 3 4 5 6 2
SELECT * FROM `user` order by field(2,3,5,4,id) desc
id user pass bbb bbb aaa aaa ccc ccc ddd ddd eee eee fff fff
依據成果剖析：order by field(2,3,5,4,1,6) 成果顯示次序為：2 1 3 4 5 6
SELECT * FROM `user` ORDER BY INSTR( '2,3,5,4', id ) ASC
id user pass aaa aaa fff fff bbb bbb ccc ccc eee eee ddd ddd
依據成果剖析：order by INSTR(2,3,5,4,1,6) 成果顯示次序為：1 6 2 3 5 4
SELECT * FROM `user` ORDER BY INSTR( '2,3,5,4', id ) DESC
id user pass ddd ddd eee eee ccc ccc bbb bbb aaa aaa fff fff
依據成果剖析：order by INSTR(2,3,5,4,1,6) 成果顯示次序為：4 5 3 2 1 6
SELECT * FROM `user` ORDER BY locate( id, '2,3,5,4' ) ASC
id user pass
aaa aaa fff fff bbb bbb ccc ccc eee eee ddd ddd
依據成果剖析：order by locate(2,3,5,4,1,6) 成果顯示次序為：1 6 2 3 5 4
SELECT * FROM `user` ORDER BY locate( id, '2,3,5,4' ) DESC
id user pass ddd ddd eee eee ccc ccc bbb bbb aaa aaa fff fff
依據成果剖析：order by locate(2,3,5,4,1,6) 成果顯示次序為：4 5 3 2 1 6
如我想要查找的數據庫中的ID次序起首是（2,3,5,4）然後在是其它的ID次序，你起首要把他降序排即（4 5 3 2），然後在 SELECT * FROM `user` ORDER BY INSTR( '4,5,3,2', id ) DESC limit 0,10 或用 SELECT * FROM `user` ORDER BY locate( id, '4,5,3,2' ) DESC 就獲得你想要的成果了。
id user pass bbb bbb ccc ccc eee eee ddd ddd aaa aaa fff fff
