EXPLAIN sql優化方法（2） Using temporary ; Using filesort

優化group by語句

explain select id, sum(moneys) from sales2 group by id \g

explain select id, sum(moneys) from sales2 group by id order by null \g

你可以通過比較發現第一條語句會比第二句在extra:裡面多了using filesort.而恰恰filesort是最耗時的。

優化order by語句

在某些情況中，mysql可以使用一個索引來滿足order by子句，而不需要額外的排序。where 條件和 order by使用相同的索引，并且order by的順序和索引順序相同，并且order by的字段都是升序或者都是降序。

例如：

select * from t1 order by key_part1,key_part2,....:

select * from t1 where key_part1 = 1 order by key_part1 desc,key_part2 desc;

select * from t1 order by key_part1 desc, key_part2 desc;

但是以下的情況不使用索引：

select * from t1 order by key_part1 desc, key_part2 asc;

--order by的字段混合asc 和 desc

select * from t1 where key2=constant order by key1;

----用于查詢行的關鍵字與order by 中所使用的不相同

select * from t1 order by key1, key2;

----對不同的關鍵字使用order by

mysql > explain select a . id , a . title , b . title from jos_content a left join jos_categories b on a . catid = b . id left join jos_sections c on a . sectionid = c . id order by b . id ;

+----+-------------+-------+--------+---------------+---------+---------+-------------------------+-------+---------------------------------+

| 1 | simple | b | eq_ref | primary | primary | 4 | joomla_test . a . catid | 1 | |

3 rows in set ( 0.00 sec )

mysql > explain select a . id , a . title , b . title from jos_content a left join jos_categories b on a . catid = b . id left join jos_sections c on a . sectionid = c . id order by a . id ;

+----+-------------+-------+--------+---------------+---------+---------+-------------------------+-------+----------------+

| 1 | simple | b | eq_ref | primary | primary | 4 | joomla_test . a . catid | 1 | |

對于上面兩條語句，隻是修改了一下排序字段，而第一個使用了using temporary，而第二個卻沒有。在日常的網站維護中，如果有using temporary出現，說明需要做一些優化措施了。

而為什麼第一個用了臨時表，而第二個沒有用呢？

因為如果有order by子句和一個不同的group by子句，或者如果order by或group by中的字段都來自其他的表而非連接配接順序中的第一個表的話，就會建立一個臨時表了。

那麼，對于上面例子中的第一條語句，我們需要對jos_categories的id進行排序，可以将sql做如下改動：

mysql > explain select b . id , b . title , a . title from jos_categories a left join jos_content b on a . id = b . catid left join jos_sections c on b . sectionid = c . id order by a . id ;

+----+-------------+-------+--------+---------------+-----------+---------+-------------------------+------+----------------+

| 1 | simple | b | ref | idx_catid | idx_catid | 4 | joomla_test . a . id | 3328 | |

這樣我們發現，不會再有using temporary了，而且在查詢jos_content時，查詢的記錄明顯有了數量級的降低，這是因為jos_content的idx_catid起了作用。

是以結論是：

盡量對第一個表的索引鍵進行排序，這樣效率是高的。

我們還會發現，在排序的語句中都出現了using filesort，字面意思可能會被了解為：使用檔案進行排序或中檔案中進行排序。實際上這是不正确的，這是一個讓人産生誤解的詞語。

當我們試圖對一個沒有索引的字段進行排序時，就是filesoft。它跟檔案沒有任何關系，實際上是内部的一個快速排序。

然而，當我們回過頭來再看上面運作過的一個sql的時候會有以下發現：

mysql > explain select a . id , a . title , b . title from jos_content a , jos_categories b , jos_sections c where a . catid = b . id and a . sectionid = c . id order by c . id ;

+----+-------------+-------+--------+-----------------------+-------------+---------+---------------------+-------+-------------+

| 1 | simple | a | ref | idx_catid , idx_section | idx_section | 4 | joomla_test . c . id | 23293 | using where |

這是我們剛才運作過的一條語句，隻是加了一個排序，而這條語句中c表的主鍵對排序起了作用，我們會發現using filesort沒有了。

而盡管在上面的語句中也是對第一個表的主鍵進行排序，卻沒有得到想要的效果（第一個表的主鍵沒有用到），這是為什麼呢？實際上以上運作過的所有left join的語句中，第一個表的索引都沒有用到，盡管對第一個表的主鍵進行了排序也無濟于事。不免有些奇怪！

于是我們繼續測試了下一條sql：

mysql > explain select a . id , a . title , b . title from jos_content a left join jos_categories b on a . catid = b . id left join jos_sections c on a . sectionid = c . id where a . id < 100 ;

+----+-------------+-------+--------+----------------+---------+---------+-------------------------+------+-------------+

| 1 | simple | b | eq_ref | primary | primary | 4 | joomla_test . a . catid | 1 | |

3 rows in set ( 0.05 sec )

然後，當再次進行排序操作的時候，using filesoft也沒有再出現

mysql > explain select a . id , a . title , b . title from jos_content a left join jos_categories b on a . catid = b . id left join jos_sections c on a . sectionid = c . id where a . id < 100 order by a . id ;

+----+-------------+-------+--------+---------------+---------+---------+-------------------------+------+-------------+

| 1 | simple | b | eq_ref | primary | primary | 4 | joomla_test . a . catid | 1 | |

這個結果表明：對where條件裡涉及到的字段，mysql會使用索引進行搜尋，而這個索引的使用也對排序的效率有很好的提升。

寫了段程式測試了一下，分别讓以下兩個sql語句執行200次：

select a . id , a . title , b . title from jos_content a left join jos_categories b on a . catid = b . id left join jos_sections c on a . sectionid = c . id

select a . id , a . title , b . title from jos_content a , jos_categories b , jos_sections c where a . catid = b . id and a . sectionid = c . id

select a . id , a . title , b . title from jos_content a left join jos_categories b on a . catid = b . id left join jos_sections c on a . sectionid = c . id 　 order by rand () limit 10

select a . id from jos_content a left join jos_categories b on b . id = a . catid left join jos_sections c on a . sectionid = c . id order by a . id

結果是第(1)條平均用時20s ，第(2)條平均用時44s ，第(3)條平均用時70s ，第(4)條平均用時2s 。而且假如我們用explain觀察第(3)條語句的執行情況，會發現它建立了temporary表來進行排序。

綜上所述，可以得出如下結論：

1. 對需要查詢和排序的字段要加索引。

2. 在一定環境下，left join還是比普通連接配接查詢效率要高，但是要盡量少地連接配接表，并且在做連接配接查詢時注意觀察索引是否起了作用。

3. 排序盡量對第一個表的索引字段進行，可以避免mysql建立臨時表，這是非常耗資源的。

4. 對where條件裡涉及到的字段，應适當地添加索引，這樣會對排序操作有優化的作用。

6. 從第4點可以看出，如果說在分頁時我們能先得到主鍵，再根據主鍵查詢相關内容，也能得到查詢的優化效果。通過國外《high performance mysql》專家組的測試可以看出，根據主鍵進行查詢的類似“select ... from... where id = ...”的sql語句（其中id為primarykey），每秒鐘能夠處理10000次以上的查詢，而普通的select查詢每秒隻能處理幾十次到幾百次。涉及到分頁的查詢效率問題，網上的可用資源越來越多，查詢功能也展現出了它的重要性。也便是sphinx、lucene這些第三方搜尋引擎的用武之地了。

7. 在平時的作業中，可以打開mysql的slow queries功能，經常檢查一下是哪些語句降低的mysql的執行效率，并進行定期優化。

EXPLAIN sql優化方法（2） Using temporary ; Using filesort

繼續閱讀

neo4j之cypher使用文檔

GitHub連夜封殺！這份阿裡 10W 字内部 Java 字面試手冊到底有多強？

spark/scala關于【資源檔案】加載方法概述外部檔案加載方案測試資源檔案打包入jar包中小結

NOSQL安全攻擊

mybatis_入門程式Mybatis入門

mysql使用source指令導入.sql檔案

AOP程式設計_Android優雅權限架構(1)概念基礎，2021金三銀四前言正文大綱正文

登入plsql 報錯 the account is locked --使用者被鎖

sqlServer根據經緯查距離

Effective Java 8:通用程式設計

SequoiaDB巨杉資料庫C++驅動概述

OOM三種類型

工廠模式-三種類型

【遞歸】高效率求2的n次幂

win10本地scala和spark安裝安裝scala安裝spark

scala (3) Function 和 Method