MySQL 檔案排序 &索引排序，mysql檔案排序

MySQL 檔案排序 &索引排序，mysql檔案排序

排序操作是非常消耗cpu的操作，當系統設定不當或query取出的欄位過多時，還可以造成MySQL不得不放棄最佳化後的排序演算法，而使用較為古老的需要兩次IO的讀取表資料的排序演算法，使排序效率非常低下。

利用索引進行排序操作，主要是利用了索引的有序性。在通過索引進行檢索的過程中，就已經得到了有序的資料訪問順序，依次讀取結果資料後就不需要進行排序操作，進而避免了此操作，提高了排序結果集的query效能。

很簡單，盡量使用索引排序，這就對了。

做個實驗，表結構如下

mysql> show create table artist \G*************************** 1. row ***************************       Table: artistCreate Table: CREATE TABLE `artist` (  `artist_id` int(10) unsigned NOT NULL,  `type` enum('Band','Person','Unknown','Combination') NOT NULL,  `name` varchar(255) NOT NULL,  `gender` enum('Male','Female') DEFAULT NULL,  `founded` year(4) DEFAULT NULL,  `country_id` smallint(5) unsigned DEFAULT NULL,  PRIMARY KEY (`artist_id`),  UNIQUE KEY `name` (`name`)) ENGINE=InnoDB DEFAULT CHARSET=latin11 row in set (0.00 sec)

使用檔案排序：

mysql> Explain select name, founded from artist where name like 'AUSTRALIA%' order by founded \G*************************** 1. row ***************************           id: 1  select_type: SIMPLE        table: artist         type: rangepossible_keys: name          key: name      key_len: 257          ref: NULL         rows: 22        Extra: Using index condition; Using filesort1 row in set (0.00 sec)

查看結果：

mysql> show session status like '%sort%';+-------------------+-------+| Variable_name     | Value |+-------------------+-------+| Sort_merge_passes | 0     || Sort_range        | 0     || Sort_rows         | 0     || Sort_scan         | 0     |+-------------------+-------+4 rows in set (0.00 sec)mysql> select name, founded from artist where name like 'AUSTRALIA%' order by founded \G......22 rows in set (0.00 sec)mysql> show session status like '%sort%';+-------------------+-------+| Variable_name     | Value |+-------------------+-------+| Sort_merge_passes | 0     || Sort_range        | 1     || Sort_rows         | 22    || Sort_scan         | 0     |+-------------------+-------+4 rows in set (0.00 sec)

幾個參數

sort_merge_passes  由於sort buffer不夠大，不得不將需要排序的資料進行分段，然後再通過sort merge的演算法完成整個過程的merge總次數，一般整個參數用來參考sort buffer size 是否足夠。

sort range session/global層級（單位：次） 通過range scan完成的排序總次數。

sort rows   session/global 層級（單位：row) 排序的總行數。

sort scan   通過掃描表完成的排序總次數。

索引排序，其中extra中的filesort不見了

mysql> Explain select name, founded from artist where name like 'AUSTRALIA%' order by name \G*************************** 1. row ***************************           id: 1  select_type: SIMPLE        table: artist         type: rangepossible_keys: name          key: name      key_len: 257          ref: NULL         rows: 22        Extra: Using index condition1 row in set (0.00 sec)

結果：

mysql> show session status like '%sort%';+-------------------+-------+| Variable_name     | Value |+-------------------+-------+| Sort_merge_passes | 0     || Sort_range        | 0     || Sort_rows         | 0     || Sort_scan         | 0     |+-------------------+-------+4 rows in set (0.00 sec)mysql> select name, founded from artist where name like 'AUSTRALIA%' order by name \G......22 rows in set (0.00 sec)mysql> show session status like '%sort%';+-------------------+-------+| Variable_name     | Value |+-------------------+-------+| Sort_merge_passes | 0     || Sort_range        | 0     || Sort_rows         | 0     || Sort_scan         | 0     |+-------------------+-------+4 rows in set (0.00 sec)

這次所有的參數都沒有改變，這就是索引排序的力量，呵呵。