mysql優化索引——Using filesort 用Explain分析SQL語句的時候，經常發現有的語句在Extra列會出現Using filesort，根據mysql官方文檔對他的描述： www.2cto.com 引用 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. 中文手冊上翻譯的很別扭： 引用 “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=latin1 www.2cto.com 寫個存儲過程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) www.2cto.com 出現了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不見了。 www.2cto.com 總結一下： 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）這樣的索引就無效了。
