SQL中如何對order by進行性能優化

一、MySQL中的兩種排序方式

• 索引排序: 通過有序索引順序掃描直接傳回有序資料。
• 額外排序: 對傳回的資料進行檔案排序。
• ORDER BY優化的核心原則: 盡量減少額外的排序，通過索引直接傳回有序資料。

二、索引排序

因為索引的結構是B+樹，索引中的資料是按照一定順序進行排列的，是以在排序查詢中如果能利用索引，就能避免額外的排序操作。EXPLAIN分析查詢時，Extra顯示為Using index。

比如查詢條件是 where age = 21 order by name ，那麼查詢過程就是會找到滿足 age = 21 的記錄，而符合這條的所有記錄一定是按照 name 排序的，是以也不需要額外進行排序。

三、額外排序

所有不是通過索引直接傳回排序結果的操作都是Filesort排序，也就是說進行了額外的排序操作。EXPLAIN分析查詢時，Extra顯示為Using fifilesort。

1、按執行位置劃分

Sort_Buffffer

MySQL 為每個線程各維護了一塊記憶體區域 sort_buffffer ，用于進行排序。sort_buffffer 的大小可以通過 sort_buffffer_size 來設定。

mysql> show variables like '%sort_buffer_size%';
+-------------------------+---------+
| Variable_name | Value |
+-------------------------+---------+
| sort_buffer_size | 262144 |
+-------------------------+---------+
mysql> select 262144 / 1024;
+---------------+
| 262144 / 1024 |
+---------------+
| 256.0000 |
+---------------+           

注: sort_Buffer_Size 并不是越大越好，由于是connection級的參數，過大的設定+高并發可能會耗盡系統記憶體資源。

Sort_Buffffer + 臨時檔案

如果加載的記錄字段總長度（可能是全字段也可能是 rowid排序的字段）小于 sort_buffffer_size 便使用 sort_buffffer 排序；如果超過則使用 sort_buffffer + 臨時檔案進行排序。

臨時檔案種類：

臨時表種類由參數 tmp_table_size 與臨時表大小決定，如果記憶體臨時表大小超過 tmp_table_size，那麼就會轉成磁盤臨時表。因為磁盤臨時表在磁盤上，是以使用記憶體臨時表的效率是大于磁盤臨時表的。

2、按執行方式劃分

執行方式是由 max_length_for_sort_data 參數與用于排序的單條記錄字段長度決定的，如果用于排序的單條記錄字段長度 <= max_length_for_sort_data ，就使用全字段排序；反之則使用 rowid 排序。

mysql> show variables like 'max_length_for_sort_data';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| max_length_for_sort_data | 1024 |
+--------------------------+-------+
           

2.1、全字段排序

全字段排序就是将查詢的所有字段全部加載進來進行排序。

優點：查詢快，執行過程簡單

缺點：需要的空間大。

select name,age,add from user where addr = '北京' order by name limit 1000; -- addr有索引           

上面查詢語句的執行流程:

1. 初始化 sort_buffffer，确定放入 name、age、addr 這3個字段。
2. 從索引 addr 中找到第一個滿足 addr=’北京’ 的主鍵ID（ID_x）。
3. 到主鍵索引中找到 ID_x，取出整行，取 name、addr、age 3個字段的值，存入 sort_buffffer。
4. 從索引 addr 取下一個記錄的主鍵ID。
5. 重複3、4，直到 addr 值不滿足條件。
6. 對 sort_buffffer 中的資料按照 name 做快速排序。
7. 把排序結果中的前1000行傳回給用戶端。

2.2、rowid排序

rowid 排序相對于全字段排序，不會把所有字段都放入sort_buffffer。是以在sort buffffer中進行排序之後還得回表查詢。

缺點：會産生更多次數的回表查詢，查詢可能會慢一些。

優點：所需的空間更小

select name,age,add from user where addr = '北京' order by name limit 1000; -- addr有索引           

假設 name、age、addr3個字段定義的總長度為36，而 max_length_for_sort_data = 16，就是單行的長度超了，MySQL認為單行太大，需要換一個算法。

放入 sort_buffffer 的字段就會隻有要排序的字段 name，和主鍵 id，那麼排序的結果中就少了 addr 和age，就需要回表了。

上面查詢語句的執行流程:

1. 初始化 sort_buffffer，确定放入2個字段，name 和 id。
2. 從索引 addr 中找到第一個滿足addr=’北京’的主鍵ID（ID_x）。
3. 到主鍵索引中取出整行，把 name、id 這2個字段放入 sort_buffffer。
4. 從索引 addr 取下一個記錄的主鍵ID。
5. 重複3、4，直到city值不滿足條件。
6. 對 sort_buffffer 中的資料按照 name 做快速排序。
7. 取排序結果中的前1000行，并按照 id 的值到原表中取出 name、age、addr 3個字段的值傳回給用戶端。

總結

• 如果 MySQL 認為記憶體足夠大，會優先選擇全字段排序，把需要的字段都放到 sort_buffffer中， 這樣排序後就會直接從記憶體裡面傳回查詢結果了，不用再回到原表去取資料。

• MySQL 的一個設計思想：如果記憶體夠，就要多利用記憶體，盡量減少磁盤通路。 對于 InnoDB 表來說，rowid 排序會要求回表多造成磁盤讀，是以不會被優先選擇。

三、排序優化

添加索引

為 employee 表 建立索引

-- 聯合索引
ALTER TABLE employee ADD INDEX idx_name_age(NAME,age);
-- 為薪資字段添加索引
ALTER TABLE employee ADD INDEX idx_salary(salary);           

檢視 employee 表的索引情況

SHOW INDEX FROM employee;           

場景1: 隻查詢用于排序的 索引字段, 可以利用索引進行排序,最左原則

查詢 name, age 兩個字段, 并使用 name 與 age 行排序

EXPLAIN SELECT e.name, e.age FROM employee e ORDER BY e.name,e.age;           

場景2: 排序字段在多個索引中,無法使用索引排序

查詢 name , salary 字段, 并使用 name 與 salary 排序

EXPLAIN SELECT e.name, e.salary FROM employee e ORDER BY e.name,e.salary;           

場景3: 隻查詢用于排序的索引字段和主鍵, 可以利用索引進行排序

查詢 id , name , 使用 name 排序

EXPLAIN SELECT e.id, e.name FROM employee e ORDER BY e.name;           

場景4: 查詢主鍵之外的沒有添加索引的字段，不會利用索引排序

查詢 dep_id ,使用 name 進行排序

EXPLAIN SELECT e.dep_id FROM employee e ORDER BY e.name;
EXPLAIN SELECT id, e.dep_id FROM employee e ORDER BY e.name;
EXPLAIN SELECT * FROM employee e ORDER BY e.name;           

場景5: 排序字段順序與索引列順序不一緻,無法利用索引排序

使用聯合索引時, ORDER BY子句也要求, 排序字段順序和聯合索引列順序比對。

EXPLAIN SELECT e.name, e.age FROM employee e ORDER BY e.age,e.name;           

場景6: where 條件是 範圍查詢時, 會使order by 索引 失效

比如 添加一個條件 : age > 18 ,然後再根據 age 排序.

EXPLAIN SELECT e.name, e.age FROM employee e WHERE e.age > 10 ORDER BY e.age;           

注意: ORDERBY子句不要求必須索引中第一列,沒有仍然可以利用索引排序。但是有個前提條件，隻有在等值過濾時才可以，範圍查詢時不可以

EXPLAIN SELECT e.name, e.age FROM employee e WHERE e.age = 18 ORDER BY e.age;           

場景7: 升降序不一緻,無法利用索引排序

ORDER BY排序字段要麼全部正序排序，要麼全部倒序排序，否則無法利用索引排序。

-- 升序
EXPLAIN SELECT e.name, e.age FROM employee e ORDER BY e.name , e.age ;
-- 降序
EXPLAIN SELECT e.name, e.age FROM employee e ORDER BY e.name DESC, e.age DESC;           

name字段升序,age字段降序,索引失效

EXPLAIN SELECT e.name, e.age FROM employee e ORDER BY e.name, e.age DESC;