192
[MySQL 優化] --order by 原理
一、簡介：
大部分查詢中都要使用到order by。那麼處理排序的方法有兩種：一種是使用索引，另外一種則是查詢時候使用Filesort處理。
****1. 利用索引進行排： ****
利用有序索引進行排序，當 Query ORDER BY 條件和 Query 的執行計劃中所利用的 Index 的索引鍵完全一致，且索引訪問方式為 rang、 ref 或者 index 的時候，MySQL 可以利用索引順序而直接取得已經排好序的數據。因為 MySQL 不需要進行實際的排序操作
2. 利用內存或者磁盤排序算法：
1. single pass
1. two pass
優缺點對比
| 優點 | 缺點 |
|---|---|
| 使用索引 | 執行insert，update，delete查詢時，索引已經排序好，隻需要依次讀取即可，處理速度快 |
| 使用Filesort | 1.因不必創建索引，故無使用索引時那麼多的缺點 2.若要排序的記錄不多，在內存中進行Filesort處理，速度也非常快 |
不能使用索引進行排序的場景：###
- 排序的基準太多，無法依據某個基準創建索引
- 要對group by 的結果或者DISTINCT的結果進行排序時
- 對臨時表的結果(union union all[5.7進行優化沒有臨時表的出現])重新排序時
二、 舉例：
表結構：
5.7@3306>[employees]>show create table employees\G
*************************** 1. row ***************************
Table: employees
Create Table: CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` enum('M','F') NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1.使用索引進行排序：
5.7@3306>[employees]>desc select emp_no,first_name, last_name from employees order by emp_no desc limit 0,10;
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-------+
| 1 | SIMPLE | employees | NULL | index | NULL | PRIMARY | 4 | NULL | 10 | 100.00 | NULL |
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-------+
2.使用single pass:
5.7@3306>[employees]>show 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)
5.7@3306>[employees]>select emp_no,first_name, last_name from employees order by first_name limit 100;
分析：將select的列，order 列放入到排序緩衝，進行排序處理，排序完成後，直接將排序緩衝中的內容返回。
5.7@3306>[employees]>show status like '%sort%';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Sort_merge_passes | 0 |
| Sort_range | 0 |
| Sort_rows | 100 |
| Sort_scan | 1 |
+-------------------+-------+
4 rows in set (0.00 sec)
5.7@3306>[employees]>desc select emp_no,first_name, last_name from employees order by first_name limit 100;
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+----------------+
| 1 | SIMPLE | employees | NULL | ALL | NULL | NULL | NULL | NULL | 299423 | 100.00 | Using filesort |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+----------------+
1 row in set, 1 warning (0.00 sec)
那麼這樣會造成什麼問題：IO，網絡等
3.Two pass
5.7@3306>[employees]>show variables like "%max_length%";
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| max_length_for_sort_data | 1024 |
+--------------------------+-------+
1 row in set (0.00 sec)
排序的值需要大於上麵的值
5.7@3306>[employees]>show 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)
5.7@3306>[employees]> select count(1) from ( select * from employees order by first_name desc limit 100000000 ) a;
+----------+
| count(1) |
+----------+
| 300024 |
+----------+
1 row in set (0.87 sec)
5.7@3306>[employees]>show status like '%sort%';
+-------------------+--------+
| Variable_name | Value |
+-------------------+--------+
| Sort_merge_passes | 25 |
| Sort_range | 0 |
| Sort_rows | 300024 |
| Sort_scan | 1 |
+-------------------+--------+
4 rows in set (0.00 sec)
解釋：Sort_merge_passes 超出sort buffer的值，將數據寫入到了tmp file中
三、 優化filesort：
優先選擇第一種using index 的排序方式，在第一種方式無法滿足的情況下，盡可能讓 MySQL 選擇使用第二種單路算法來進行排序。這樣可以減少大量的隨機IO操作,很大幅度地提高排序工作的效率。
1、去掉不必要的字段
2、加大max_length_for_sort_data 參數的設置
3、增大sort buffer的設置
最後更新：2017-06-19 22:32:07