程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> 關於MYSQL數據庫 >> mysql中explain出現using filesort的優化方法

mysql中explain出現using filesort的優化方法

編輯：關於MYSQL數據庫

explain是mysql解釋select查詢語句的一個關鍵字,它可以很方便的對MySQL語句進行調試,看索引是否使用. EXPLAIN tbl_name EXPLAIN SELECT select_options
EXPLAIN 語句可以被當作 DESCRIBE 的同義詞來用，也可以用來獲取一個MySQL要執行的 SELECT 語句的相關信息。
EXPLAIN tbl_name 語法和 DESCRIBE tbl_name 或 SHOW COLUMNS FROM tbl_name 一樣。
當在一個 SELECT 語句前使用關鍵字 EXPLAIN 時，MySQL會解釋了即將如何運行該 SELECT 語句，它顯示了表如何連接、連接的順序等信息。
在explain我們所使用的sql的時候，經常會遇到using filesort這種情況，原以為是由於有相同列值的原因引起，結果昨天看到公司的一個sql，跟同事討論了下加上自己又做了一些測試，突然發現自己原來的想法是錯誤的。
首先，只有在order by 數據列的時候才可能會出現using filesort，而且如果你不對進行order by的這一列設置索引的話，無論列值是否有相同的都會出現using filesort。因此，只要用到order by 的這一列都應該為其建立一個索引。 SELECT * FROM DB.TB WHERE ID=2224 AND FID IN (11,8,13,123,382,40) ORDER BY INVERSE_DATE LIMIT 0, 4
裡面建立的索引為一個三列的多列索引：IDX（ID,FID ,INVERSE_DATE）。INVERSE_DATE這個是時間的反向索引。
對於這個sql我當時最開始認為應該是個優化好的狀態，應該沒有什麼纰漏了，結果一explain才發現竟然出現了：Using where; Using filesort。
為什麼呢，後來經過分析才得知，原來在多列索引在建立的時候是以B-樹結構建立的，因此建立索引的時候是先建立ID的按順序排的索引，在相同ID的情況下建立FID按順序排的索引，最後在FID 相同的情況下建立按INVERSE_DATE順序排的索引，如果列數更多以此類推。有了這個理論依據我們可以看出在這個sql使用這個IDX索引的時候只是用在了order by之前，order by INVERSE_DATE 實際上是using filesort出來的。。汗死了。。因此如果我們要在優化一下這個sql就應該為它建立另一個索引IDX（ID,INVERSE_DATE），這樣就消除了using filesort速度也會快很多。

**************************************************************************************

用Explain分析SQL語句的時候，經常發現有的語句在Extra列會出現Using filesort，根據MySQL官方文檔對他的描述：

Quotation MySQL must do an extra pass to find out how to retrIEve the rows in sorted order. The sort is done by going through all rows according to the join type and storing the sort key and pointer to the row for all rows that match the WHERE clause.

中文手冊上翻譯的很別扭：

Quotation “MySQL需要額外的一次傳遞，以找出如何按排序順序檢索行，通過根據聯接類型浏覽所有行並為所有匹配where子句的行保存排序關鍵字和行的指針來完成排序，然後關鍵字被排序，並按排序順序檢索行。”

總的來說，Using filesort 是MySQL裡一種速度比較慢的外部排序，如果能避免是最好的了，很多時候，我們可以通過優化索引來盡量避免出現Using filesort，從而提高速度。這裡舉個簡單的例子：

CREATE TABLE `testing` (
   `id` int(10) unsigned NOT NULL auto_increment,
   `room_number` int(10) unsigned NOT NULL default '0',
   PRIMARY KEY   (`id`),
   KEY `room_number` (`room_number`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

寫個存儲過程askwan，插入10萬條測試數據

MySQL> DELIMITER $$ DROP PROCEDURE IF EXISTS `askwan`.`askwan` $$
CREATE PROCEDURE `askwan`.`askwan` ()
BEGIN
     DECLARE v INT DEFAULT 1;
             WHILE v<100000;
                   DO
                   INSERT INTO testing VALUES(v,v);
                   SET v=v+1;
             END WHILE; END $$ mysql> DELIMITER ; MySQL> CALL askwan();
Query OK, 1 row affected (13.21 sec)

OK,數據准備好了，開始試驗。由上面例子中建立的表信息，我已經建立了兩個索引，一個主鍵id，一個room_number列索引
那現在來看一條SQL,

SELECT id FROM testing WHERE room_number=1000 ORDER BY id ;

分析一下

MySQL> EXPLAIN SELECT id FROM testing WHERE room_number=1000 ORDER BY id ;
+----+-------------+---------+------+---------------+-------------+---------+-------+------+-----------------------------+
| id | select_type | table    | type | possible_keys | key          | key_len | ref    | rows | Extra                        |
+----+-------------+---------+------+---------------+-------------+---------+-------+------+-----------------------------+
|   1 | SIMPLE       | testing | ref   | room_number    | room_number | 4        | const |     1 | Using where; Using filesort | 
+----+-------------+---------+------+---------------+-------------+---------+-------+------+-----------------------------+
1 row in set (0.00 sec)

出現了Using filesort，並且用到了room_number這列索引，但是，在這裡用到的索引是針對WHERE後面的room_number條件的，而最後面的排序是根據id來的，這就是手冊中說的，“額外的一次排序”！，於是就會出現Using filesort，根據我以前寫過的一文章，我再建立一個聯合索引 room_number_id

alter table testing add index room_number_id(room_number,id);

在來分析一下

MySQL> EXPLAIN SELECT id FROM testing WHERE room_number=1000 ORDER BY id ;
+----+-------------+---------+------+----------------------------+----------------+---------+-------+------+--------------------------+
| id | select_type | table    | type | possible_keys               | key             | key_len | ref    | rows | Extra                     |
+----+-------------+---------+------+----------------------------+----------------+---------+-------+------+--------------------------+
|   1 | SIMPLE       | testing | ref   | room_number,room_number_id | room_number_id | 4        | const |     1 | Using where;   | 
+----+-------------+---------+------+----------------------------+----------------+---------+-------+------+--------------------------+
1 row in set (0.00 sec)

現在Using filesort不見了。總結一下：
   1.一般有order by語句，在索引加得不當的情況下，都有可能出現Using filesort，這時候就要對SQL語句和索引進行優化了，但是，並不是說出現Using filesort就是個嚴重的問題，不是這樣的，此次舉的例子比較極端，幾乎不太可能出現這麼傻瓜的查詢，優化和不優化，要看它是不是影響了業務性能。
   2. 從上面可以看到聯合索引，也可以叫多列索引，形如 key ('A1','A2','A3' ,'A4')等的，排序的思路一般是，先按照A1來排序，A1相同，然後按照A2排序，以此類推，這樣對於（A1）,(A1,A2),(A1,A2,A3)的索引都是有效的，但是對於（A2,A3）這樣的索引就無效了。