using filesort

一般人的回答是： “當(dāng)行數(shù)據(jù)太大，導(dǎo)致內(nèi)存無(wú)法容下這些數(shù)據(jù)產(chǎn)生的臨時(shí)表時(shí)，他們就會(huì)被放入磁盤中排序。” ?很不幸，這個(gè)答案是錯(cuò)的 ，臨時(shí)表在太大的時(shí)候確實(shí)會(huì)到磁盤離去，但是EXPLAIN不會(huì)顯示這些。

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.

那么事實(shí)是， filesort 這個(gè)名字取得太搓逼了。 filesort的意思是只要一個(gè)排序無(wú)法使用索引來(lái)排序，就叫filesort。他和file沒(méi)半毛錢關(guān)系。filesort應(yīng)該叫做sort。(筆者補(bǔ)充一下：意思是說(shuō)如果無(wú)法用已有index來(lái)排序，那么就需要數(shù)據(jù)庫(kù)服務(wù)器額外的進(jìn)行數(shù)據(jù)排序，這樣其實(shí)是會(huì)增加性能開(kāi)銷的。)

另外不光order by會(huì)出現(xiàn)filesort ?group by沒(méi)有使用索引一樣會(huì)出現(xiàn)

檢查sort狀態(tài)

show?session?status?like?'%sort%';

sort_merge_passes ?由于sort buffer不夠大，不得不將需要排序的數(shù)據(jù)進(jìn)行分段，然后再通過(guò)sort merge的算法完成整個(gè)過(guò)程的merge總次數(shù)，一般整個(gè)參數(shù)用來(lái)參考sort buffer size 是否足夠。

sort range session/global級(jí)別(單位：次) 通過(guò)range scan完成的排序總次數(shù)。

sort rows ? session/global 級(jí)別(單位：row) 排序的總行數(shù)。

sort scan ? 通過(guò)掃描表完成的排序總次數(shù)。

當(dāng)無(wú)法避免排序操作時(shí)，又該如何來(lái)優(yōu)化呢？很顯然，應(yīng)該盡可能讓 MySQL 選擇使用第二種單路算法來(lái)進(jìn)行排序。這樣可以減少大量的隨機(jī)IO操作，很大幅度地提高排序工作的效率。

1. 合理設(shè)置索引 讓排序字段使用上索引排序

2. 加大 max_length_for_sort_data 參數(shù)的設(shè)置

3. 去掉不必要的返回字段

當(dāng)內(nèi)存不是很充裕時(shí)，不能簡(jiǎn)單地通過(guò)強(qiáng)行加大上面的參數(shù)來(lái)強(qiáng)迫 MySQL 去使用改進(jìn)版的排序算法，否則可能會(huì)造成 MySQL 不得不將數(shù)據(jù)分成很多段，然后進(jìn)行排序，這樣可能會(huì)得不償失。此時(shí)就須要去掉不必要的返回字段，讓返回結(jié)果長(zhǎng)度適應(yīng) max_length_for_sort_data 參數(shù)的限制。

4. 增大 sort_buffer_size 參數(shù)設(shè)置

增大 sort_buffer_size 并不是為了讓 MySQL選擇改進(jìn)版的排序算法，而是為了讓MySQL盡量減少在排序過(guò)程中對(duì)須要排序的數(shù)據(jù)進(jìn)行分段，因?yàn)榉侄螘?huì)造成 MySQL 不得不使用臨時(shí)表來(lái)進(jìn)行交換排序。

Using temporary

內(nèi)部臨時(shí)表產(chǎn)生的時(shí)機(jī)有以下幾種：

使用 ORDER BY 子句和一個(gè)不一樣的 GROUP BY 子句(經(jīng)過(guò)筆者實(shí)驗(yàn)，應(yīng)該是GROUP BY一個(gè)無(wú)索引列,就會(huì)產(chǎn)生臨時(shí)表)，或者 ORDER BY 或 GROUP BY 的列不是來(lái)自JOIN語(yǔ)句序列的第一個(gè)表，就會(huì)產(chǎn)生臨時(shí)表(經(jīng)筆者實(shí)驗(yàn)，應(yīng)該是使用JOIN時(shí)， GROUP BY 任何列都會(huì)產(chǎn)生臨時(shí)表)

DISTINCT 和 ORDER BY 一起使用時(shí)可能需要臨時(shí)表(筆者實(shí)驗(yàn)是只要用了DISTINCT(非索引列)，都會(huì)產(chǎn)生臨時(shí)表)

用了 SQL_SMALL_RESULT, mysql就會(huì)用內(nèi)存臨時(shí)表。定義：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

有些情況服務(wù)器會(huì)直接使用磁盤臨時(shí)表

表里存在BLOB或者TEXT的時(shí)候(這是因?yàn)镸EMORY引擎不支持這兩種數(shù)據(jù)類型，這里筆者補(bǔ)充一下，并非只要查詢里含有BLOB和TEXT類型的列就會(huì)產(chǎn)生磁盤臨時(shí)表，按照高性能MYSQL里的話，應(yīng)該這么說(shuō)：“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.”也就是說(shuō)如果我們的查詢中包含了BLOB和TEXT的列，而且又需要臨時(shí)表，這時(shí)候臨時(shí)表就被強(qiáng)制轉(zhuǎn)成使用磁盤臨時(shí)表，所以此書(shū)一直在提醒我們，如果要對(duì)BLOB和TEXT排序，應(yīng)該使用SUBSTRING(column， length)將這些列截?cái)嘧兂勺址?#xff0c;這樣就可以使用in-memory臨時(shí)表了

GROUP BY 或者 DISTINCT 子句大小超過(guò) 512 Bytes

使用了UNION 或 UNION ALL 并且 SELECT 的列里有超過(guò)512 Bytes的列

如果內(nèi)置內(nèi)存臨時(shí)表創(chuàng)建后變得太大，MySQL會(huì)自動(dòng)將它轉(zhuǎn)換成磁盤臨時(shí)表。內(nèi)存臨時(shí)表的大小取決與 tmp_table_size參數(shù)和max_heap_table_size參數(shù)的值。用 CREATE TABLE 產(chǎn)生的內(nèi)存臨時(shí)表的大小取決與 max_heap_table_size來(lái)決定是否要將其轉(zhuǎn)換成磁盤臨時(shí)表

當(dāng)服務(wù)器生成一個(gè)內(nèi)存臨時(shí)表，Created_tmp_tables狀態(tài)變量值會(huì)增加，當(dāng)服務(wù)器創(chuàng)建了一個(gè)磁盤臨時(shí)表時(shí)，Created_tmp_disk_tables狀態(tài)變量值會(huì)增加。(這幾個(gè)變量可以通過(guò) show status命令查看得到)

Tips: internal temporaray table 的大小受限制的是tmp_table_size和max_heap_table_size的最小值；而 user-created temporary table的大小只受限與max_heap_table_size,而與tmp_table_size無(wú)關(guān)

總結(jié)

以上是生活随笔為你收集整理的mysql using filesort_mysql using filesort Using temporary的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。