mysql order by 優化

M莫發表於2018-07-14
MySql優化

version : 5.7, from 8.2.1.14 ORDER BY Optimization

本節描述MySQL何時可以使用索引來滿足ORDER BY子句，當不能使用索引時使用filesort，以及優化器中有關ORDER BY的執行計劃資訊。

一個order by語句對於有沒有使用limit可能存在執行差異。詳細內容檢視8.2.1.17 LIMIT Query Optimization。

使用索引實現order by

在某些情況下，MySQL可能會使用索引來滿足一個ORDER BY子句，並避免執行filesort 操作時涉及的額外排序。

雖然ORDER BY並不完全精確地匹配索引，但是索引還是會被使用，只要在WHERE子句中，所有未被使用的那部分索引（一個索引多個欄位-聯合索引的情況）以及所有ORDER BY欄位都是一個常量就沒問題，都會走到索引而不是filesort。

這裡我們有一張表tx_order,

CREATE TABLE `tx_order` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT ,
  `serial_number` varchar(255) NOT NULL ,
  `order_status` int unsigned DEFAULT 0 NOT NULL ,
  `market_id` varchar(10) DEFAULT NULL ,
  `market_name` varchar(255) DEFAULT NULL ,
  `shop_id` varchar(50) DEFAULT NULL ,
  `shop_name` varchar(100) DEFAULT NULL ,
  `mobile` varchar(64) DEFAULT NULL ,
  `create_date` datetime DEFAULT NULL ,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2333702 DEFAULT CHARSET=utf8;
複製程式碼

並且新增索引

alter table tx_order add index idx_market_date(market_id,create_date);
複製程式碼

在接下來的sql中分析order by對索引的使用情況。其中MySQL優化器實際執行sql是否使用索引還是表掃描取決於兩者的效率。

  • 下面這個sql中，優化器使用了idx_market_date索引避開了表掃描.
desc select market_id,create_date from tx_order.tx_order order by market_id,create_date;

1	SIMPLE	tx_order		index		idx_market_date	39		1671956	100	Using index

複製程式碼

然而這句sql中的查詢欄位都在索引中，如果查詢欄位不被包含在索引中，如「select market_id,create_date,market_name」。這種情況下，掃描整個索引並且查詢錶行以查不在索引中的列，這樣的操作的代價可能比表掃描更高，此時優化器可能不會使用索引。

desc select market_id,create_date,market_name from tx_order.tx_order order by market_id,create_date;

1	SIMPLE	tx_order		ALL					1671956	100	Using filesort

複製程式碼

在InnoDB中，我們知道主鍵（聚集索引）本身是索引的一部分，下面這個查詢中索引就會被使用。

desc select id,market_id,create_date from tx_order.tx_order order by market_id,create_date;

1	SIMPLE	tx_order		index		idx_market_date	39		1671956	100	Using index

複製程式碼
  • 下面這種情況，在where條件中索引中的一個欄位是一個常量，並且where子語句產生的範圍索引的效能比表掃描高的多，那麼這樣的查詢會選擇索引而不是表掃描。
desc select market_id,create_date from tx_order.tx_order where  market_id = '1009' order by create_date;

1	SIMPLE	tx_order		ref	idx_market_date	idx_market_date	33	const	170398	100	Using where; Using index

複製程式碼
  • 下面兩條sql比較特殊，也可以對比前面幾個order by ... asc的語句。看看下面的執行結果我們可以思考這是為什麼。首先新增索引的時候暫時是無法指定欄位排序的，alter table tx_order add index idx_market_date(market_id asc,create_date desc)，雖然這樣的寫法語法是支援的，但是當前版本的MySQL不做任邏輯何支援，都是統一安裝預設升序排列。在一個聯合索引中，查詢按照索引中的欄位排序，如果排序方式不一致，優化器還是會部分走表掃描的。
desc select market_id,create_date from tx_order.tx_order order by market_id desc ,create_date desc ;

1	SIMPLE	tx_order		index		idx_market_date	39		1671956	100	Using index

desc select market_id,create_date from tx_order.tx_order order by market_id asc ,create_date desc ;

1	SIMPLE	tx_order		index		idx_market_date	39		1671956	100	Using index; Using filesort

複製程式碼
  • 下面的查詢where子語句中的範圍索引優於表掃描，優化器會選擇索引解析order by。
desc select market_id,create_date from tx_order.tx_order where market_id > '1009' order by market_id asc;

1	SIMPLE	tx_order		range	idx_market_date	idx_market_date	33		835978	100	Using where; Using index

desc select market_id,create_date from tx_order.tx_order where market_id < '1009' order by market_id desc;

1	SIMPLE	tx_order		range	idx_market_date	idx_market_date	33		230966	100	Using where; Using index
複製程式碼
  • 下面這個查詢中，order by的不再是market_id，但是所有查詢的行market_id都是一個常量，所以還是會走到索引的解析order by。
desc select market_id,create_date from tx_order.tx_order where market_id = '1009' and create_date>'2018-01-01' order by create_date desc;

1	SIMPLE	tx_order		range	idx_market_date	idx_market_date	39		94002	100	Using where; Using index
複製程式碼

在一些情況下，雖然MySQL對where條件處理的時候用會用到索引，但是不能夠用索引來解析order by, 看下面的例子。

  • order by使用到的索引非連續，MySQL解析order by還是會掃描表，我這裡有一個索引 idx_market_id(market_id,order_status,create_date)，看下面的sql執行結果。
desc select market_id,create_date from tx_order.tx_order where  market_id='1009' order by market_id ,create_date ;

1	SIMPLE	tx_order		ref	idx_market_id,idx_market_type_create_date	idx_market_id	33	const	138084	100	Using where; Using index; Using filesort
複製程式碼
  • 混合排序asc,desc
desc select market_id,create_date from tx_order.tx_order order by market_id asc ,create_date desc;

1	SIMPLE	tx_order		index		idx_market_date	39		1671956	100	Using index; Using filesort
複製程式碼
  • order by欄位使用函式，優化器解析order by放棄索引
desc select mobile from tx_order.tx_order order by  abs(mobile);

1	SIMPLE	tx_order		index		idx_mobile	768		1671956	100	Using index; Using filesort

複製程式碼
  • 在多表關聯查詢中，並且ORDER BY中的列並不是全部來自第1個用於搜尋行的非常量表。(這是EXPLAIN輸出中的沒有const聯接型別的第1個表）。
desc select a.market_id from tx_order.tx_order a ,tx_order_item b where a.id = b.order_id and a.market_id = '1009'  order by a.market_id,b.sku;

1	SIMPLE	b		ALL	idx_order_create				1	100	Using filesort
1	SIMPLE	a		eq_ref	PRIMARY,idx_market_date	PRIMARY	8	tx_order.b.order_id	1	10.19	Using where

複製程式碼
  • 有不同的ORDER BY和GROUP BY表示式。
desc select market_id,create_date from tx_order.tx_order   group by market_id,create_date order by create_date;

1	SIMPLE	tx_order		index	idx_market_date	idx_market_date	39		1671956	100	Using index; Using temporary; Using filesort

複製程式碼
  • 對於指定了排序索引長度的索引。在這種情況下，索引不能完全解析排序順序，需要使用filesort來排序。例如，建立索引alter table tx_order add index idx_mobile(mobile(5)); 然而mobile varchar(64).
desc select mobile from tx_order.tx_order order by mobile desc ;

1	SIMPLE	tx_order		ALL					1671956	100	Using filesort

複製程式碼

  • 有些情況，使用的表索引的型別不能按順序儲存行。例如，對於HEAP表的HASH索引情況即如此。

  • 排序索引的可用性可能受列別名的使用影響。

在下面的語句中，排序受到影響，不會使用索引.

desc select abs(market_id) as aa from tx_order.tx_order order by aa;

1	SIMPLE	tx_order		index		idx_market_date	39		1671956	100	Using index; Using filesort

複製程式碼

但是，下面的語句中，雖然查詢欄位有使用別名，但是真實的排序欄位還是索引中的欄位，那麼排序還是使用索引的。

desc select abs(market_id) as aa from tx_order.tx_order order by market_id;

1	SIMPLE	tx_order		index		idx_market_date	39		1671956	100	Using index

複製程式碼

在預設情況下，對於"group by col2,col2,..."這樣的語句，MySQL會同時會包含"order by col2,col2,..."等同於你顯示的加速"order by col2,col2,..."排序，這種情況下優化器的處理是沒有效能損失的。

對於這個預設情況，如果你想避開預設排序，可以使用 order by null 來避免，例如：

desc select market_id,count(market_id) from tx_order.tx_order group by market_id order by null ;
複製程式碼

優化器可能仍然選擇使用排序來實現分組操作。ORDER BY NULL 禁止對結果進行排序，而不是通過分組操作進行先前排序以確定結果。

注意

GROUP BY預設情況下隱式排序（即，在沒有列ASC或 列的DESC指示符的情況下GROUP BY）。但是，不推薦依賴於隱式 GROUP BY排序（即，在沒有ASC或 DESC指示符的情況下排序）或顯式排序GROUP BY（即，通過 對列使用顯式ASC或DESC指示符GROUP BY）。要生成給定的排序順序，有必要供一個 ORDER BY子句。

使用filesort實現排序

當無法使用索引排序的時候，MySQL使用filesort掃描表給結果集排序，相應的filesort在整個查詢過程中產生了額外的排序階段。

為了支援filesort，優化器實現會分配一定數量的記憶體sort_buffer_size區域，這塊記憶體區域是每個session獨佔的，並且可以更改這個變數值。

如果filesort資料集太大，在記憶體中無法實現排序，優化器會使用一塊磁碟作為臨時檔案來做排序。某些查詢特別適合記憶體排序完成filesort的操作，例如優化器可以有效的利用記憶體排序，而不需要臨時檔案實現。例如

desc select * from tx_order.tx_order order by market_name desc limit 10;

1	SIMPLE	tx_order		ALL					1671956	100	Using filesort

複製程式碼

Using temporary的例子

desc select market_name from tx_order.tx_order order by RAND() desc limit 10;

1	SIMPLE	tx_order		ALL					1671956	100	Using temporary; Using filesort

複製程式碼

影響order by優化

對於filesort的慢查詢，可以嘗試修改 max_length_for_sort_data 標量來達到效果，控制filesort選擇演算法的觸發點，可以嘗試調低 max_length_for_sort_data 值。（如果增大了max_length_for_sort_data的值，並且磁碟使用率上升，cpu使用率下降，）詳細資料請閱讀 Mysql 排序優化與索引使用（轉)。

要提高ORDER BY速度，請檢查是否可以讓MySQL使用索引而不是額外的排序階段。如果無法做到這一點，請嘗試以下策略：

  • 增加 sort_buffer_size 變數值。理想情況下，該值應足夠大，以使整個結果集適合排序緩衝區（以避免寫入磁碟和合並傳遞），但至少該值必須足夠大以容納15個元組。（最多可以合併15個臨時磁碟檔案，每個檔案至少有一個元組在記憶體中必須有空間。）

    請考慮儲存在排序緩衝區中的列值的大小受 max_sort_length系統變數值的影響。例如，如果元組儲存長字串列的值並且您增加了值 max_sort_length，則排序緩衝區元組的大小也會增加，並且可能需要您增加 sort_buffer_size。對於作為字串表示式（例如呼叫字串值函式的那些）計算的列值，filesort演算法無法分辨表示式值的最大長度，因此必須分配 max_sort_length 每個元組的位元組數。

    要監視合併傳遞的數量（合併臨時檔案），請檢查 Sort_merge_passes 狀態變數。

  • 增加 read_rnd_buffer_size 變數值，以便一次讀取更多行。

  • 將tmpdir 系統變數更改為指向具有大量可用空間的專用檔案系統。變數值可以列出以迴圈方式使用的幾個路徑; 您可以使用此功能將負載分散到多個目錄中。:在Unix上用冒號字元（）分隔路徑，;在Windows上用分號字元（）分隔路徑。路徑應命名位於不同物理磁碟上的檔案系統中的目錄 ，而不是同一磁碟上的不同分割槽。

通過執行計劃檢視sql解析

使用 EXPLAIN （參見8.8.1 Optimizing Queries with EXPLAIN），可以檢查MySQL是否可以使用索引來解析ORDER BY子句.

  • 如果輸出Extra列 EXPLAIN不包含Using filesort，則使用索引並且filesort不執行。
  • 如果輸出Extra列 EXPLAIN包含 Using filesort，則不使用索引並filesort執行。

另外，filesort執行的時候優化器的trace可以輸出filesort_summary資訊快。例如：

"filesort_summary": {
  "rows": 100,
  "examined_rows": 100,
  "number_of_tmp_files": 0,
  "sort_buffer_size": 25192,
  "sort_mode": "<sort_key, packed_additional_fields>"
}
複製程式碼

對於MySQL的trace，詳細請參考Chapter 8 Tracing the Optimizer.

總結

想要寫出高效可靠的排序查詢，你需要搞明白order by大概的執行過程，這裡可以參考How MySQL executes ORDER BY,Mysql 排序優化與索引使用（轉)這兩篇文章。

我們在寫sql語句並且使用order by的時候，首先考慮滿足索引條件，如果不滿足那麼滿足記憶體中filesort，最壞的情況就是臨時檔案出現了，當然這種情況是我們最不想看到的。

同時這裡要說一下我的個人經驗：

  1. 聯合索引是個好東西，能夠應用到專案中的很多使用場景，詳細優化可以參照8.3 Optimization and Indexes。
  2. sql改寫，複雜的單條sql可以改寫成兩條或者三條，使用上索引。
  3. 建立好的表結構，為欄位分配最合身的型別和長度。

開放過程中多去琢磨sql，多看執行計劃，有效的避免慢查詢，提高服務的效能。

參考

相關文章