從排序原理到MYSQL中的排序方式
本文參考MYSQL官方文件，演算法書籍，部分為自己觀點可能有誤,如果有誤請指出共同討論
轉載請說明出處，謝謝！
一、MYSQL排序可能用到的排序演算法
從MYSQL官方文件和原始碼的介面來看MYSQL使用BUFFER內部快速排序演算法，外部多路歸併排序演算法，相應的介面函式為
filesort()函式，注意filesort()是一個總的介面，內部排序實際呼叫save_index()下的std::stable_sort\std::sort、歸併排序
也包含在下面介面可能為merge_many_buff()，也就像執行計劃中using filesort的含義，他只能代表使用了排序，但是
是否使用到tempfile排序是看不出來的，但是這個可以再trace看到但是線上是不可以trace的研究是可以的，隨後我會演示。
還要注意using temporary只是說明使用了臨時表儲存中間結果，這裡先不討論，只看排序。
下面簡要介紹兩種演算法原理
1、buffer內部 快速排序演算法
快速排序是交換排序類演算法，是氣泡排序的升級版，其原理是採用分而治之的思想，在於每趟交換設定一個基準點，
將大於這個基準點的資料放到一邊，大於的放到另一邊，不斷的進行遞迴完成，對於大資料量的排序快速排序一般
效率優秀，在文章的最後是一個簡單的快速排序的實現，如果對演算法感興趣的可以參考一下。但是至少還能進
行3種優化
--小資料優化，因為快速排序對資料量小的時候並不是最優，可以使用其他排序演算法如插入排序。
--尾遞迴優化，減少棧的使用
--基準點優化，儘量取到資料中的中間值作為基準點，這樣能夠讓快速排序更加優化。
2、外部磁碟多路歸併排序
將內部快速排序後有序的資料檔案進行不斷的比較得到有序的檔案，每次歸併多少個檔案就是歸併的路數
圖中每一個R當然代表的一個有序的磁碟檔案
下圖2路歸併排序(擷取資料結構C語言版)
下圖5路歸併排序(擷取資料結構C語言版)
二、MYSQL相關引數
sort_buffer_size:
當然也就是每次排序的buffer，用作內部快速排序用，如果buffer越大當然產生的物理檔案也就越少，但是這個
引數是會話級別的，過分加大會造成記憶體不足，預設256K。注意：
On Linux, there are thresholds of 256KB and
2MB where larger values may significantly slow down memory allocation, so you should consider staying
below one of those values
read_rnd_buffer_size：
除了MRR用到，這裡也用到了用於 二次排序的時候對排序好的資料按照primary key(ROW_ID)按照分塊的方式再次排序，
意義同樣在回表取資料可以儘量順序化
max_length_for_sort_data：
單位為位元組(bytes)，如果排序返回行的欄位長度綜合大約這個值，使用二次排序而不是一次排序，預設1024，最小
值為4，如果加大這個值可能過多的使用一次排序造成高TEMPFILE空間使用而CPU不高,為什麼如此後面解釋。
max_sort_length：
單位為位元組(bytes)，如果排序欄位的長度超過這個值，只是用這個引數設定的長度，預設1024，比如text這種欄位
經常會大於這個值，如果加大這個值明顯會提高排序的準確性，但是也意味著更高的BUFFER使用和TEMPFILE使用
三、監控磁碟排序
Sort_merge_passes：磁碟排序歸併次數，減少sort_buffer_size大小會顯著減少Sort_merge_passes值，並且臨時檔案也
會變少，第五部分證明
sys.statements_with_sorting檢視
四、MYSQL二次訪問排序(original method)和一次訪問排序(modified method)
1、二次訪問排序
--讀取資料只包含排序鍵值和rowid(primary key)放到sort buffer
--在buffer中進行快速排序，如果buffer 滿則把記憶體中的排序資料寫入tempfile
--重複上面的過程直到內部快速排序完成，並且生成多個tmepfile檔案
--進行多路歸併排序原始碼介面在merge_many_buff()，其中定義了MERGEBUFF，MERGEBUFF2 2個巨集
這個在官方文件上有出現所以提出來說明一下
/* Defines used by filesort and uniques */
#define MERGEBUFF 7
#define MERGEBUFF2 15
如果有興趣的可以仔細看看原始碼..
--最後一次歸併的時候，只有rowid(priamry key)到最後的檔案中
--對最後的檔案根據rowid(primary key)訪問表資料，這樣就可以得到排序好的資料
這裡有一個類似MRR的優化，將資料進行分塊讀入read_rnd_buffer_size進行
按照rowid(primary key)排序在去訪問表的資料，目的在於減少隨機讀取造成的影響
但是這是分塊的，只能減少不能杜絕，特別是資料量特別大的情況下，因為
read_rnd_buffer_size只有預設256K.
問題在於對錶資料的二次訪問，一次在讀取資料的時候，後一次在通過排序好的
rowid(primary key)進行資料的訪問，並且會出現大量隨機訪問。
2、一次訪問排序
這個就簡單了，二次訪問排序是把排序鍵值和rowid(primary key)放到sort buffer，
這個就是關於需要的資料欄位全部放到sort buffer比如：
select id,name1,name2 from test order by id;
這裡id,name1,name2都會存放到sort buffer中。這樣排序好就好了，不需要回表取
資料了，當然這樣做的劣勢就是更大的sort buffer佔用，更大tempfile佔用。所以
mysql使用max_length_for_sort_data來限制預設1024，這是指id,name1,name2欄位
的bytes長度。
因為不需要回表，所以只要一次訪問資料
3、5.7.3後一次訪問排序演算法的優化
使用一個叫做pack優化的方法，目的在於壓縮NULL減少一次訪問排序演算法對sort buffer和tempfile的過多使用
原文：
without packing, a VARCHAR(255)
column value containing only 3 characters takes 255 characters in the sort buffer. With packing,
the value requires only 3 characters plus a two-byte length indicator. NULL values require only
a bitmask.
但是我在做MYSQL TRACE的時候發現這還有一個unpack的過程，並且每一行每一個欄位都需要pack unpack
隨後證明
關於使用了的那種排序方式在執行計劃中都體現為filesort不好弄清楚，但是我們可以通過trace的方式，
在官方文件也說了，但是我使用了對MYSQLD的trace方式來做，效果一致，詳細參考第五部分
五、證明觀點
1、首先需要證明是使用的是二次訪問排序還是一次訪問排序，是否啟用了pack
官方文件說明
"filesort_summary": {
"rows": 100,
"examined_rows": 100,
"number_of_tmp_files": 0,
"sort_buffer_size": 25192,
"sort_mode": ""
}
sort_mode:
: This indicates use of the original algorithm.
: This indicates use of the modified algorithm.
: This indicates use of the modified algorithm. Sort
buffer tuples contain the sort key value and packed columns referenced by the query.
也就是說
sort_key, rowid是二次訪問排序
sort_key, additional_fields是一次訪問排序
sort_key, packed_additional_fields是一次訪問排序+pack方式
好了我們來證明，使用測試表
mysql> show create table testmer \G;
*************************** 1. row ***************************
Table: testmer
Create Table: CREATE TABLE `testmer` (
`seq` int(11) NOT NULL,
`id1` int(11) DEFAULT NULL,
`id2` int(11) DEFAULT NULL,
`id3` int(11) DEFAULT NULL,
`id4` int(11) DEFAULT NULL,
PRIMARY KEY (`seq`),
KEY `id1` (`id1`,`id2`),
KEY `id3` (`id3`,`id4`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.01 sec)
mysql> select * from testmer ;
+-----+------+------+------+------+
| seq | id1 | id2 | id3 | id4 |
+-----+------+------+------+------+
| 1 | 1 | 2 | 4 | 4 |
| 2 | 1 | 3 | 4 | 5 |
| 3 | 1 | 2 | 4 | 4 |
| 4 | 2 | 4 | 5 | 6 |
| 5 | 2 | 6 | 5 | 8 |
| 6 | 2 | 10 | 5 | 3 |
| 7 | 4 | 5 | 8 | 10 |
| 8 | 0 | 1 | 3 | 4 |
+-----+------+------+------+------+
8 rows in set (0.01 sec)
分別在max_length_for_sort_data為1024和max_length_for_sort_data為4對
select * from testmer order by id1;
生成trace檔案
意義也就是使用一次訪問排序和二次訪問排序,因為資料量少也就在sort_buffer
排序就好了。
一次訪問排序：
opt: filesort_execution: ending struct
opt: filesort_summary: starting struct
opt: rows: 8
opt: examined_rows: 8
opt: number_of_tmp_files: 0
opt: sort_buffer_size: 34440
opt: sort_mode: ""
opt: filesort_summary: ending struct
為 一次訪問排序+pack方式
二次訪問排序：
opt: filesort_execution: ending struct
opt: filesort_summary: starting struct
opt: rows: 8
opt: examined_rows: 8
opt: number_of_tmp_files: 0
opt: sort_buffer_size: 18480
opt: sort_mode: ""
opt: filesort_summary: ending struct
為是二次訪問排序
可以看到不同，證明了max_length_for_sort_data的作用
其實這個是filesort()函式中的一個呼叫而已，其實gdb也可以打上斷點也能看到
Opt_trace_object(trace, "filesort_summary")
.add("rows", num_rows)
.add("examined_rows", param.examined_rows)
.add("number_of_tmp_files", num_chunks)
.add("sort_buffer_size", table_sort.sort_buffer_size())
.add_alnum("sort_mode",
param.using_packed_addons() ?
"" :
param.using_addon_fields() ?
"" : "");
2、證明減少sort_buffer_size大小會顯著減少Sort_merge_passes值，並且臨時檔案也
會變少
為了完成這個證明我建立了一個大表，降低先sort_buffer為使用如下的語句使用更多的
tempfile進行排序
mysql> select count(*) from testshared3;
+----------+
| count(*) |
+----------+
| 1048576 |
+----------+
1 row in set (28.31 sec)
mysql> set sort_buffer_size=50000;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like '%sort_buffer_size%';
+-------------------------+---------+
| Variable_name | Value |
+-------------------------+---------+
| sort_buffer_size | 50000 |
+-------------------------+---------+
mysql> show status like '%Sort_merge%';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Sort_merge_passes | 0 |
+-------------------+-------+
1 row in set (0.00 sec)
mysql> explain select * from testshared3 order by id limit 1048570,1;
+----+-------------+-------------+------------+------+---------------+------+---------+------+---------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------------+------------+------+---------------+------+---------+------+---------+----------+----------------+
| 1 | SIMPLE | testshared3 | NULL | ALL | NULL | NULL | NULL | NULL | 1023820 | 100.00 | Using filesort |
+----+-------------+-------------+------------+------+---------------+------+---------+------+---------+----------+----------------+
mysql> select * from testshared3 order by id limit 1048570,1;
+------+
| id |
+------+
| 1 |
+------+
1 row in set (5 min 4.76 sec)
完成後
mysql> show status like '%Sort_merge%';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Sort_merge_passes | 63 |
+-------------------+-------+
1 row in set (0.21 sec)
opt: number_of_tmp_files: 378
臨時檔案數量378
然後加大sort_buffer_size
mysql> show variables like '%sort_buffer_size%';
+-------------------------+---------+
| Variable_name | Value |
+-------------------------+---------+
| sort_buffer_size | 262144 |
+-------------------------+---------+
mysql> show status like '%Sort_merge%';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Sort_merge_passes | 0 |
+-------------------+-------+
1 row in set (0.04 sec)
還是同樣的語句
mysql> select * from testshared3 order by id limit 1048570,1;
+------+
| id |
+------+
| 1 |
+------+
1 row in set (5 min 4.76 sec)
mysql> show status like '%Sort_merge%';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Sort_merge_passes | 11 |
+-------------------+-------+
opt: number_of_tmp_files: 73
臨時檔案數量73
得到證明
3、證明有pack和unpack操作，並且每一行每一個欄位都需要pack unpack
這個直接檢視trace檔案是否有介面就好了
實際上可以看到8段如下的操作
>Field::unpack
Field::unpack
Field::unpack
Field::unpack
Field::unpack
Field::unpack
Field::unpack"|wc -l
40
[root@testmy tmp]# cat sortys2.trace|grep ">Field::pack"|wc -l
40
剛好是5(欄位)*8(行)
當然我隨後對一個大表只有一個欄位的表進行一樣的測試，既然是一個欄位使用
一次訪問排序的時候排序的全部欄位就是這個欄位而已，所以pack和unpack的次數應該
和行數差不多
mysql> select count(*) from testshared3;
+----------+
| count(*) |
+----------+
| 1048576 |
+----------+
1 row in set (28.31 sec)
mysql> desc testshared3;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
1 row in set (0.26 sec)
[root@testmy tmp]# cat mysqld11.trace|grep ">Field::unpack"|wc -l
1048571
也得到同樣基本相同的結構，證明了一次訪問排序中每一行每一個欄位都需
要pack、unpack操作，其實在整個trace中還能看到很多類容比如列取出了
一次訪問排序的全部欄位，這裡就不在詳解了
六、原始碼GDB發現內部排序呼叫STD容器的std::stable_sort std::sort 進行排序
(gdb) n
250 if (param->sort_length < 10)
(gdb) list
245 than quicksort seems to be somewhere around 10 to 40 records.
246 So we're a bit conservative, and stay with quicksort up to 100 records.
247 */
248 if (count <= 100)
249 {
250 if (param->sort_length < 10)
251 {
252 std::sort(m_sort_keys, m_sort_keys + count,
253 Mem_compare(param->sort_length));
254 return;
這部分mysql上的原始碼
最後附上快速排序的程式碼
帶排序資料是13,3,2,9,34,5,102,90,20,2
排序完成後如下：
gaopeng@bogon:~/datas$ ./a.out
sort result:2 2 3 5 9 13 20 34 90 102
轉載請說明出處，謝謝！
一、MYSQL排序可能用到的排序演算法
從MYSQL官方文件和原始碼的介面來看MYSQL使用BUFFER內部快速排序演算法，外部多路歸併排序演算法，相應的介面函式為
filesort()函式，注意filesort()是一個總的介面，內部排序實際呼叫save_index()下的std::stable_sort\std::sort、歸併排序
也包含在下面介面可能為merge_many_buff()，也就像執行計劃中using filesort的含義，他只能代表使用了排序，但是
是否使用到tempfile排序是看不出來的，但是這個可以再trace看到但是線上是不可以trace的研究是可以的，隨後我會演示。
還要注意using temporary只是說明使用了臨時表儲存中間結果，這裡先不討論，只看排序。
下面簡要介紹兩種演算法原理
1、buffer內部 快速排序演算法
快速排序是交換排序類演算法，是氣泡排序的升級版，其原理是採用分而治之的思想，在於每趟交換設定一個基準點，
將大於這個基準點的資料放到一邊，大於的放到另一邊，不斷的進行遞迴完成，對於大資料量的排序快速排序一般
效率優秀，在文章的最後是一個簡單的快速排序的實現，如果對演算法感興趣的可以參考一下。但是至少還能進
行3種優化
--小資料優化，因為快速排序對資料量小的時候並不是最優，可以使用其他排序演算法如插入排序。
--尾遞迴優化，減少棧的使用
--基準點優化，儘量取到資料中的中間值作為基準點，這樣能夠讓快速排序更加優化。
2、外部磁碟多路歸併排序
將內部快速排序後有序的資料檔案進行不斷的比較得到有序的檔案，每次歸併多少個檔案就是歸併的路數
圖中每一個R當然代表的一個有序的磁碟檔案
下圖2路歸併排序(擷取資料結構C語言版)
下圖5路歸併排序(擷取資料結構C語言版)
二、MYSQL相關引數
sort_buffer_size:
當然也就是每次排序的buffer，用作內部快速排序用，如果buffer越大當然產生的物理檔案也就越少，但是這個
引數是會話級別的，過分加大會造成記憶體不足，預設256K。注意：
On Linux, there are thresholds of 256KB and
2MB where larger values may significantly slow down memory allocation, so you should consider staying
below one of those values
read_rnd_buffer_size：
除了MRR用到，這裡也用到了用於 二次排序的時候對排序好的資料按照primary key(ROW_ID)按照分塊的方式再次排序，
意義同樣在回表取資料可以儘量順序化
max_length_for_sort_data：
單位為位元組(bytes)，如果排序返回行的欄位長度綜合大約這個值，使用二次排序而不是一次排序，預設1024，最小
值為4，如果加大這個值可能過多的使用一次排序造成高TEMPFILE空間使用而CPU不高,為什麼如此後面解釋。
max_sort_length：
單位為位元組(bytes)，如果排序欄位的長度超過這個值，只是用這個引數設定的長度，預設1024，比如text這種欄位
經常會大於這個值，如果加大這個值明顯會提高排序的準確性，但是也意味著更高的BUFFER使用和TEMPFILE使用
三、監控磁碟排序
Sort_merge_passes：磁碟排序歸併次數，減少sort_buffer_size大小會顯著減少Sort_merge_passes值，並且臨時檔案也
會變少，第五部分證明
sys.statements_with_sorting檢視
四、MYSQL二次訪問排序(original method)和一次訪問排序(modified method)
1、二次訪問排序
--讀取資料只包含排序鍵值和rowid(primary key)放到sort buffer
--在buffer中進行快速排序，如果buffer 滿則把記憶體中的排序資料寫入tempfile
--重複上面的過程直到內部快速排序完成，並且生成多個tmepfile檔案
--進行多路歸併排序原始碼介面在merge_many_buff()，其中定義了MERGEBUFF，MERGEBUFF2 2個巨集
這個在官方文件上有出現所以提出來說明一下
/* Defines used by filesort and uniques */
#define MERGEBUFF 7
#define MERGEBUFF2 15
如果有興趣的可以仔細看看原始碼..
--最後一次歸併的時候，只有rowid(priamry key)到最後的檔案中
--對最後的檔案根據rowid(primary key)訪問表資料，這樣就可以得到排序好的資料
這裡有一個類似MRR的優化，將資料進行分塊讀入read_rnd_buffer_size進行
按照rowid(primary key)排序在去訪問表的資料，目的在於減少隨機讀取造成的影響
但是這是分塊的，只能減少不能杜絕，特別是資料量特別大的情況下，因為
read_rnd_buffer_size只有預設256K.
問題在於對錶資料的二次訪問，一次在讀取資料的時候，後一次在通過排序好的
rowid(primary key)進行資料的訪問，並且會出現大量隨機訪問。
2、一次訪問排序
這個就簡單了，二次訪問排序是把排序鍵值和rowid(primary key)放到sort buffer，
這個就是關於需要的資料欄位全部放到sort buffer比如：
select id,name1,name2 from test order by id;
這裡id,name1,name2都會存放到sort buffer中。這樣排序好就好了，不需要回表取
資料了，當然這樣做的劣勢就是更大的sort buffer佔用，更大tempfile佔用。所以
mysql使用max_length_for_sort_data來限制預設1024，這是指id,name1,name2欄位
的bytes長度。
因為不需要回表，所以只要一次訪問資料
3、5.7.3後一次訪問排序演算法的優化
使用一個叫做pack優化的方法，目的在於壓縮NULL減少一次訪問排序演算法對sort buffer和tempfile的過多使用
原文：
without packing, a VARCHAR(255)
column value containing only 3 characters takes 255 characters in the sort buffer. With packing,
the value requires only 3 characters plus a two-byte length indicator. NULL values require only
a bitmask.
但是我在做MYSQL TRACE的時候發現這還有一個unpack的過程，並且每一行每一個欄位都需要pack unpack
隨後證明
關於使用了的那種排序方式在執行計劃中都體現為filesort不好弄清楚，但是我們可以通過trace的方式，
在官方文件也說了，但是我使用了對MYSQLD的trace方式來做，效果一致，詳細參考第五部分
五、證明觀點
1、首先需要證明是使用的是二次訪問排序還是一次訪問排序，是否啟用了pack
官方文件說明
"filesort_summary": {
"rows": 100,
"examined_rows": 100,
"number_of_tmp_files": 0,
"sort_buffer_size": 25192,
"sort_mode": ""
}
sort_mode:
: This indicates use of the original algorithm.
: This indicates use of the modified algorithm.
: This indicates use of the modified algorithm. Sort
buffer tuples contain the sort key value and packed columns referenced by the query.
也就是說
sort_key, rowid是二次訪問排序
sort_key, additional_fields是一次訪問排序
sort_key, packed_additional_fields是一次訪問排序+pack方式
好了我們來證明，使用測試表
mysql> show create table testmer \G;
*************************** 1. row ***************************
Table: testmer
Create Table: CREATE TABLE `testmer` (
`seq` int(11) NOT NULL,
`id1` int(11) DEFAULT NULL,
`id2` int(11) DEFAULT NULL,
`id3` int(11) DEFAULT NULL,
`id4` int(11) DEFAULT NULL,
PRIMARY KEY (`seq`),
KEY `id1` (`id1`,`id2`),
KEY `id3` (`id3`,`id4`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.01 sec)
mysql> select * from testmer ;
+-----+------+------+------+------+
| seq | id1 | id2 | id3 | id4 |
+-----+------+------+------+------+
| 1 | 1 | 2 | 4 | 4 |
| 2 | 1 | 3 | 4 | 5 |
| 3 | 1 | 2 | 4 | 4 |
| 4 | 2 | 4 | 5 | 6 |
| 5 | 2 | 6 | 5 | 8 |
| 6 | 2 | 10 | 5 | 3 |
| 7 | 4 | 5 | 8 | 10 |
| 8 | 0 | 1 | 3 | 4 |
+-----+------+------+------+------+
8 rows in set (0.01 sec)
分別在max_length_for_sort_data為1024和max_length_for_sort_data為4對
select * from testmer order by id1;
生成trace檔案
意義也就是使用一次訪問排序和二次訪問排序,因為資料量少也就在sort_buffer
排序就好了。
一次訪問排序：
opt: filesort_execution: ending struct
opt: filesort_summary: starting struct
opt: rows: 8
opt: examined_rows: 8
opt: number_of_tmp_files: 0
opt: sort_buffer_size: 34440
opt: sort_mode: ""
opt: filesort_summary: ending struct
為 一次訪問排序+pack方式
二次訪問排序：
opt: filesort_execution: ending struct
opt: filesort_summary: starting struct
opt: rows: 8
opt: examined_rows: 8
opt: number_of_tmp_files: 0
opt: sort_buffer_size: 18480
opt: sort_mode: ""
opt: filesort_summary: ending struct
為是二次訪問排序
可以看到不同，證明了max_length_for_sort_data的作用
其實這個是filesort()函式中的一個呼叫而已，其實gdb也可以打上斷點也能看到
Opt_trace_object(trace, "filesort_summary")
.add("rows", num_rows)
.add("examined_rows", param.examined_rows)
.add("number_of_tmp_files", num_chunks)
.add("sort_buffer_size", table_sort.sort_buffer_size())
.add_alnum("sort_mode",
param.using_packed_addons() ?
"" :
param.using_addon_fields() ?
"" : "");
2、證明減少sort_buffer_size大小會顯著減少Sort_merge_passes值，並且臨時檔案也
會變少
為了完成這個證明我建立了一個大表，降低先sort_buffer為使用如下的語句使用更多的
tempfile進行排序
mysql> select count(*) from testshared3;
+----------+
| count(*) |
+----------+
| 1048576 |
+----------+
1 row in set (28.31 sec)
mysql> set sort_buffer_size=50000;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like '%sort_buffer_size%';
+-------------------------+---------+
| Variable_name | Value |
+-------------------------+---------+
| sort_buffer_size | 50000 |
+-------------------------+---------+
mysql> show status like '%Sort_merge%';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Sort_merge_passes | 0 |
+-------------------+-------+
1 row in set (0.00 sec)
mysql> explain select * from testshared3 order by id limit 1048570,1;
+----+-------------+-------------+------------+------+---------------+------+---------+------+---------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------------+------------+------+---------------+------+---------+------+---------+----------+----------------+
| 1 | SIMPLE | testshared3 | NULL | ALL | NULL | NULL | NULL | NULL | 1023820 | 100.00 | Using filesort |
+----+-------------+-------------+------------+------+---------------+------+---------+------+---------+----------+----------------+
mysql> select * from testshared3 order by id limit 1048570,1;
+------+
| id |
+------+
| 1 |
+------+
1 row in set (5 min 4.76 sec)
完成後
mysql> show status like '%Sort_merge%';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Sort_merge_passes | 63 |
+-------------------+-------+
1 row in set (0.21 sec)
opt: number_of_tmp_files: 378
臨時檔案數量378
然後加大sort_buffer_size
mysql> show variables like '%sort_buffer_size%';
+-------------------------+---------+
| Variable_name | Value |
+-------------------------+---------+
| sort_buffer_size | 262144 |
+-------------------------+---------+
mysql> show status like '%Sort_merge%';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Sort_merge_passes | 0 |
+-------------------+-------+
1 row in set (0.04 sec)
還是同樣的語句
mysql> select * from testshared3 order by id limit 1048570,1;
+------+
| id |
+------+
| 1 |
+------+
1 row in set (5 min 4.76 sec)
mysql> show status like '%Sort_merge%';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Sort_merge_passes | 11 |
+-------------------+-------+
opt: number_of_tmp_files: 73
臨時檔案數量73
得到證明
3、證明有pack和unpack操作，並且每一行每一個欄位都需要pack unpack
這個直接檢視trace檔案是否有介面就好了
實際上可以看到8段如下的操作
>Field::unpack
Field::unpack
Field::unpack
Field::unpack
Field::unpack
Field::unpack
Field::unpack"|wc -l
40
[root@testmy tmp]# cat sortys2.trace|grep ">Field::pack"|wc -l
40
剛好是5(欄位)*8(行)
當然我隨後對一個大表只有一個欄位的表進行一樣的測試，既然是一個欄位使用
一次訪問排序的時候排序的全部欄位就是這個欄位而已，所以pack和unpack的次數應該
和行數差不多
mysql> select count(*) from testshared3;
+----------+
| count(*) |
+----------+
| 1048576 |
+----------+
1 row in set (28.31 sec)
mysql> desc testshared3;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
1 row in set (0.26 sec)
[root@testmy tmp]# cat mysqld11.trace|grep ">Field::unpack"|wc -l
1048571
也得到同樣基本相同的結構，證明了一次訪問排序中每一行每一個欄位都需
要pack、unpack操作，其實在整個trace中還能看到很多類容比如列取出了
一次訪問排序的全部欄位，這裡就不在詳解了
六、原始碼GDB發現內部排序呼叫STD容器的std::stable_sort std::sort 進行排序
(gdb) n
250 if (param->sort_length < 10)
(gdb) list
245 than quicksort seems to be somewhere around 10 to 40 records.
246 So we're a bit conservative, and stay with quicksort up to 100 records.
247 */
248 if (count <= 100)
249 {
250 if (param->sort_length < 10)
251 {
252 std::sort(m_sort_keys, m_sort_keys + count,
253 Mem_compare(param->sort_length));
254 return;
這部分mysql上的原始碼
點選(此處)摺疊或開啟
-
/*
-
std::stable_sort has some extra overhead in allocating the temp buffer,
-
which takes some time. The cutover point where it starts to get faster
-
than quicksort seems to be somewhere around 10 to 40 records.
-
So we're a bit conservative, and stay with quicksort up to 100 records.
-
*/
-
if (count <= 100)
-
{
-
if (param->sort_length < 10)
-
{
-
std::sort(m_sort_keys, m_sort_keys + count,
-
Mem_compare(param->sort_length));
-
return;
-
}
-
std::sort(m_sort_keys, m_sort_keys + count,
-
Mem_compare_longkey(param->sort_length));
-
return;
-
}
-
// Heuristics here: avoid function overhead call for short keys.
-
if (param->sort_length < 10)
-
{
-
std::stable_sort(m_sort_keys, m_sort_keys + count,
-
Mem_compare(param->sort_length));
-
return;
-
}
-
std::stable_sort(m_sort_keys, m_sort_keys + count,
- Mem_compare_longkey(param->sort_length));
最後附上快速排序的程式碼
帶排序資料是13,3,2,9,34,5,102,90,20,2
排序完成後如下：
gaopeng@bogon:~/datas$ ./a.out
sort result:2 2 3 5 9 13 20 34 90 102
點選(此處)摺疊或開啟
-
/*************************************************************************
-
> File Name: qsort.c
-
> Author: gaopeng QQ:22389860
-
> Mail: gaopp_200217@163.com
-
> Created Time: Fri 06 Jan 2017 03:04:08 AM CST
-
************************************************************************/
-
-
-
#include<stdio.h>
-
#include<stdlib.h>
-
-
-
int partition(int *k,int low,int high)
-
{
-
-
int point;
-
point = k[low]; //基準點,採用陣列的第一個值，這裡實際可以優化
-
while(low<high) //等待low=high一趟交換完成
-
{
-
-
while(low<high && k[high] >=point) //過濾掉尾部大於基準點的值,不需要交換
-
{
-
high--;
-
}
-
k[low] = k[high]; //基準點多次交換為無謂交換直接賦值即可
-
while(low<high && k[low] <=point) //過濾掉頭部小於基準點的值,不需要交換
-
{
-
-
low++;
-
}
-
k[high] = k[low]; //基準點多次交換為無謂交換直接賦值即可
-
}
-
k[low] = point;
-
return low;
-
}
-
-
int q_sort(int *k,int low,int high)
-
{
-
-
int point;
-
if(low<high)
-
{
-
-
point = partition(k,low,high);
-
q_sort(k,low,point-1); //實現遞迴前半部分
-
q_sort(k,point+1,high); //實現遞迴後半部分
-
}
-
return 0;
-
}
-
-
int main()
-
{
-
-
int i,a[10]={13,3,2,9,34,5,102,90,20,2}; //測試資料
-
q_sort(a,0,9); //陣列下標0 9
-
-
printf("sort result:");
-
for(i=0;i<10;i++)
-
{
-
printf("%d ",a[i]);
-
}
-
printf("\n");
- }
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/7728585/viewspace-2130743/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- MySQL中的排序MySql排序
- Mysql中的雙路排序和單路排序MySql排序
- MySQL中的排序規則MySql排序
- mysql分段排序MySql排序
- java排序方式對比Java排序
- 快速排序-遞迴方式排序遞迴
- 如何在mysql中實現自然排序MySql排序
- 【SQL 學習】UNION 的排序方式！SQL排序
- MySQL 對字串排序MySql字串排序
- MySQL入門——排序MySql排序
- MySQL自定義排序MySql排序
- Golang語言排序的幾種方式Golang排序
- MySQL 預設排序真的是按主鍵來排序的嗎MySql排序
- js中 的排序方法JS排序
- Java中List的排序Java排序
- java中hashMap的排序JavaHashMap排序
- Array中物件的排序物件排序
- Oracle排序的時候在PGA中的分割原則是 ？Oracle排序
- MySQL Order BY 排序過程MySql排序
- mysql相同數值排序MySql排序
- MySQL-排序資料MySql排序
- mysql中文欄位排序MySql排序
- MySQL 5.7 ORDER BY排序的優化MySql排序優化
- MySQL 按照指定的欄位排序MySql排序
- 最常用的 8 個排序演算法：從原理到改進，再到程式碼兌現透徹解析排序演算法
- O(lgn)的三種排序，快速排序、歸併排序、堆排序排序
- mysql中的（字元）轉換函式以及排序問題。MySql字元函式排序
- Laravel 幾種少用的排序方式程式碼Laravel排序
- 【SQL 學習】minus 和intsect 的排序方式！SQL排序
- Java中氣泡排序的原生實現方式(正序和逆序)Java排序
- 排序：氣泡排序&快速排序排序
- Java集合中的排序方法Java排序
- mysql首字母排序，拋棄傳統的php首字母排序MySql排序PHP
- 【排序】插入類排序—(折半)插入排序、希爾排序排序
- 計數排序的原址排序排序
- 從未排序的連結串列中刪除重複項排序
- 陣列排序（從小到大）陣列排序
- 排序：交換排序——氣泡排序法排序