---
title: 不懂SQL優化？那你就OUT了(六)
MySQL如何優化--ORDER BY
date: 2018-12-08
categories: 資料庫優化
---
在日常開發中，我們經常會使用 order by 子句對某些資料進行排序處理，那麼在mysql中使用order by子句時,我們應該怎樣優化order by 子句後的查詢字段來提高查詢效率和排序速度。
在mysql中使用 order by 進行排序有兩種方式：
1. 掃描有序索引進行排序(推薦)
2. 使用檔案進行排序（using filesort：記憶體/磁盤檔案排序擷取結果 ）
在InnoDB存儲引擎以B+樹作為索引的底層實作，B+樹的葉子節點存儲着所有資料頁而内部節點不存放資料資訊，并且所有葉子節點形成一個(雙向)連結清單。
你可以簡單的了解為：索引是一種特殊的檔案，當我們建立索引時，mysql會使用雙向連結清單的方式來事先給資料進行排序。
例如：
CREATE TABLE t_testOrderBy(
userid INT PRIMARY KEY AUTO_INCREMENT, -- 使用者編号
username VARCHAR(25), -- 使用者姓名
userAge INT, -- 使用者年齡
usergender CHAR(3), -- 使用者性别
provice VARCHAR(25), -- 所在省份
city VARCHAR(25), -- 所在城市
address VARCHAR(200) -- 詳細位址
);
測試資料：
INSERT INTO t_testOrderBy VALUES(NULL,'張三',18,'男','四川省','成都市','xxxx路222号');
INSERT INTO t_testOrderBy VALUES(NULL,'李四',20,'女','雲南省','昆明市','xxx北路12号');
INSERT INTO t_testOrderBy VALUES(NULL,'王五',24,'男','貴州省','遵義市','xxxxx路18号');
INSERT INTO t_testOrderBy VALUES(NULL,'趙六',19,'女','四川省','綿陽市','xx路234号');
INSERT INTO t_testOrderBy VALUES(NULL,'孫琦',28,'男','雲南省','玉溪市','xxxx路324号');
INSERT INTO t_testOrderBy VALUES(NULL,'王曉琪',21,'女','雲南省','玉溪市','xxxx路123号');
例如：
圖1：
從圖1可以看出：
使用userid進行排序時，userid上有主鍵索引，mysql會直接周遊userid索引的葉子節點連結清單，不需要進行額外的排序操作,這就是用索引掃描來排序。
使用 username 字段上沒有任何索引，此時B+樹結構不存在，mysql就隻能先掃表篩選出符合條件的資料，再将篩選結果根據username排序。這個排序過程就是filesort。
### 使用有序索引排序時
sql語句中，where子句和order by 子句都可以使用索引, where子句使用索引避免全表掃描,order by 子句使用索引盡量避開使用檔案排序（filesort)，以提高查詢效率。
雖然索引能提高查詢效率，但在一條sql裡，對于一張表的查詢 一次隻能使用一個索引（注：排除發生index merge的可能性），也就是說當where子句與order by 子句使用的索引不一緻時，MySQL隻能使用其中一個索引(B+樹)。
####order by 可以使用索引
1. 當select的字段包含在索引中時，能利用到索引排序功能，進行覆寫索引掃描.
例如： 為表中的username添加索引。
可以看出當 select 字段中包含了 userage(未建立索引)時，則不能使用索引。
2.當有聯合索引時，order by 子句使用索引必須遵循索引的最左字首原則。
例如: 為 省份，城市，詳細位址建立聯合索引
當不遵循最左字首原則時，則會使用filesort
3.聯合索引中的一部分做等值查詢 ，另一部分作為排序字段。（當然還是要遵循最左字首原則）
####order by 不使用索引排序
1.select字段在多個索引中，無法使用索引排序。
從上圖中可以看出，username的一個獨立的是以，二而provice是聯合索引，當select字段中有多個索引列時，無法使用索引排序
2.對不同的關鍵字使用ORDER BY：
3.當有聯合索引時，order by 子句使用索引 不 遵循索引的最左字首原則，無法使用索引排序
4. 升降序不一緻，無法使用索引排序。
5.order by 的字段使用函數
6.傳回資料量過大也會不使用索引。
## 使用檔案進行排序（後面在介紹）
對于不能利用索引避免排序的sql，資料庫不得不自己實作排序功能以滿足使用者需求，此時sql的執行計劃中會出現“Using filesort”，這裡需要注意的是filesort并不意味着就是檔案排序，其實也有可能是記憶體排序，這個主要由sort_buffer_size參數與結果集大小确定。
如果排序不可避免，可以用下面的辦法加速：
1. 避免使用 “select * ” 。
2. 增加sort_buffer_size變量的大小。
3. 增加read_rnd_buffer_size變量的大小。
4. 更改tmpdir指向具有大量空閑空間的專用檔案系統。
5. 使用合适的列大小存儲具體的内容，比如對于city字段 varchar(25)比varchar(200)能擷取更好的性能.
##order by 總結
轉載于:https://www.cnblogs.com/ysviewvicn/p/10288302.html