mysql using filesort Using temporary

標籤：存在   返回   name   zed   like   包含   改進   distinct   演算法   

using filesort 

一般人的回答是： “當行資料太大，導致記憶體無法容下這些資料產生的暫存資料表時，他們就會被放入磁碟中排序。”  很不幸，這個答案是錯的 ，暫存資料表在太大的時候確實會到磁碟離去，但是EXPLAIN不會顯示這些。

The truth is, filesort is badly named. Anytime a sort can’t be performed from an index, it’s a filesort. It has nothing to do with files. Filesort should be called “sort.” It is quicksort at heart.

那麼事實是， filesort 這個名字取得太搓逼了。 filesort的意思是只要一個排序無法使用索引來排序，就叫filesort。他和file沒半毛錢關係。filesort應該叫做sort。（筆者補充一下：意思是說如果無法用已有index來排序，那麼就需要資料庫伺服器額外的進行資料排序，這樣其實是會增加效能開銷的。）

另外不光order by會出現filesort  group by沒有使用索引一樣會出現

檢查sort狀態

 show session status like ‘%sort%‘;  

 

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

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

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

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

 

 

當無法避免排序操作時，又該如何來最佳化呢？很顯然，應該儘可能讓 MySQL 選擇使用第二種單路演算法來進行排序。這樣可以減少大量的隨機IO操作，很大幅度地提高排序工作的效率。

1. 合理設定索引 讓排序欄位使用上索引排序

2. 加大 max_length_for_sort_data 參數的設定

3. 去掉不必要的返回欄位

當記憶體不是很充裕時，不能簡單地通過強行加大上面的參數來強迫 MySQL 去使用改進版的排序演算法，否則可能會造成 MySQL 不得不將資料分成很多段，然後進行排序，這樣可能會得不償失。此時就須要去掉不必要的返回欄位，讓返回結果長度適應 max_length_for_sort_data 參數的限制。

4. 增大 sort_buffer_size 參數設定

增大 sort_buffer_size 並不是為了讓 MySQL選擇改進版的排序演算法，而是為了讓MySQL盡量減少在排序過程中對須要排序的資料進行分段，因為分段會造成 MySQL 不得不使用暫存資料表來進行交換排序。

 

Using temporary

內部暫存資料表產生的時機有以下幾種：

• 使用 ORDER BY 子句和一個不一樣的 GROUP BY 子句（經過筆者實驗，應該是GROUP BY一個無索引列,就會產生暫存資料表），或者 ORDER BY 或 GROUP BY 的列不是來自JOIN語句序列的第一個表，就會產生暫存資料表(經筆者實驗，應該是使用JOIN時， GROUP BY 任何列都會產生暫存資料表)

• DISTINCT 和 ORDER BY 一起使用時可能需要暫存資料表（筆者實驗是只要用了DISTINCT（非索引列），都會產生暫存資料表）

• 用了 SQL_SMALL_RESULT, mysql就會用記憶體暫存資料表。定義：SQL_BIG_RESULT or SQL_SMALL_RESULT can be used with GROUP BY or DISTINCT to tell the optimizer that the result set has many rows or is small, respectively. For SQL_BIG_RESULT, MySQL

有些情況伺服器會直接使用磁碟暫存資料表

• 表裡存在BLOB或者TEXT的時候(這是因為MEMORY引擎不支援這兩種資料類型，這裡筆者補充一下，並非只要查詢裡含有BLOB和TEXT類型的列就會產生磁碟暫存資料表，按照高效能MYSQL裡的話，應該這麼說：“Because the Memory storage engine doesn‘t support the BLOB and TEXT types, queries that use BLOB or TEXT columns and need an implicit temporary table will have to use on-disk MyISAM temporry tables, even for only a few rows.”也就是說如果我們的查詢中包含了BLOB和TEXT的列，而且又需要暫存資料表，這時候暫存資料表就被強制轉成使用磁碟暫存資料表，所以此書一直在提醒我們，如果要對BLOB和TEXT排序，應該使用SUBSTRING（column， length）將這些列截斷變成字串，這樣就可以使用in-memory暫存資料表了
• GROUP BY 或者 DISTINCT 子句大小超過 512 Bytes
• 使用了UNION 或 UNION ALL 並且 SELECT 的列裡有超過512 Bytes的列

 

如果內建記憶體暫存資料表建立後變得太大，MySQL會自動將它轉換成磁碟暫存資料表。記憶體暫存資料表的大小取決與 tmp_table_size參數和max_heap_table_size參數的值。用 CREATE TABLE 產生的記憶體暫存資料表的大小取決與 max_heap_table_size來決定是否要將其轉換成磁碟暫存資料表

當伺服器產生一個記憶體暫存資料表，Created_tmp_tables狀態變數值會增加，當伺服器建立了一個磁碟暫存資料表時，Created_tmp_disk_tables狀態變數值會增加。(這幾個變數可以通過 show status命令查看得到)

Tips: internal temporaray table 的大小受限制的是tmp_table_size和max_heap_table_size的最小值；而 user-created temporary table的大小隻受限與max_heap_table_size,而與tmp_table_size無關

mysql using filesort Using temporary