線上的某個SQL語句的執行計劃分析
- 2019 年 11 月 22 日
- 筆記
前兩天遇到一個問題，是關於一個SQL的執行計劃的，大概是這麼個SQL:
select C from table where A=1 and B>date_a and B<date_b group by C
對應的表結構是：
create table tbl_name (
A XXX,
B XXX,
C XXX,
index(A,B,C)
);
從表結構中可以看出來，這個表是使用了ABC為順序的聯合索引，然後查看這個表的執行計劃，如下（為了方便說明，我們設計了一個簡易的表來替代）：
mysql 22:28:02>>create table tbl_name(a int,b int,c int,index `idx_abc`(a,b,c)); Query OK, 0 rows affected (0.07 sec) mysql 22:28:52>>insert into tbl_name values (1,1,1),(2,2,2),(3,3,3); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql 22:29:18>>insert into tbl_name values (1,1,6),(2,2,5),(3,3,4); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql 22:29:40>>insert into tbl_name select * from tbl_name; Query OK, 6 rows affected (0.00 sec) Records: 6 Duplicates: 0 Warnings: 0 mysql 22:29:55>>insert into tbl_name select * from tbl_name; Query OK, 12 rows affected (0.01 sec) Records: 12 Duplicates: 0 Warnings: 0 mysql 22:29:57>>explain select c from tbl_name where a=1 and b>=2 and b<=3 group by c; +----+-------------+----------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------------------------------------------+ | 1 | SIMPLE | tbl_name | NULL | range | idx_abc | idx_abc | 10 | NULL | 1 | 100.00 | Using where; Using index; Using temporary; Using filesort | +----+-------------+----------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------------------------------------------+ 1 row in set, 1 warning (0.00 sec)
從表中不難看出來執行計劃中反應出Using temporary和Using filesort這兩個額外的資訊，這裡還是有一些疑問的，第一，命名沒有進行order by 的操作，為什麼額外的資訊中會有filesort？其次，這個臨時表又是幹什麼用的？
要回答這個問題，我們先從官方文檔中查看一下資訊：
In some cases, the server creates internal temporary tables while processing statements. Users have no direct control over when this occurs.
- Evaluation of statements that contain an
ORDER BYclause and a differentGROUP BYclause, or for which theORDER BYorGROUP BYcontains columns from tables other than the first table in the join queue. - Evaluation of
DISTINCTcombined withORDER BYmay require a temporary table.
這裡的意思是說，在某些情況下，伺服器會創建臨時表來處理語句，這個處理過程用戶沒有辦法干預。然後列舉了幾種情況，其中的兩條是：如果語句包含order by 或者包含group by語句，則可能用到臨時表，或者order by和group by語句在關聯查詢的後面的表裡出現，則可能會用到臨時表；如果distinct和order by同時出現的時候，可能會用到臨時表。
通俗的來講，在許多查詢的執行過程中，MySQL可能會藉助臨時表來完成一些功能，比如去重、排序之類的，比如我們在執行許多包含DISTINCT、GROUP BY、UNION等子句的查詢過程中，如果不能有效利用索引來完成查詢，MySQL很有可能尋求通過建立內部的臨時表來執行查詢。如果查詢中使用到了內部的臨時表，在執行計劃的Extra列將會顯示Using temporary提示。
到這裡就能解釋為什麼會用到臨時表了。這裡我們注意到，查詢計劃中還有一個using filesort的關鍵字，我們的SQL看起來並沒有執行order by的語句，為什麼會出現filesort的語句呢？其實這個問題的本質還是由於mysql幫我們做了優化，默認按照分組的欄位進行排序，如果我們不想要這個排序的功能，可以使用null來顯示的禁止掉這個功能，如下：
mysql 22:29:57>>explain select c from tbl_name where a=1 and b>=2 and b<=3 group by c order by null; +----+-------------+----------+------------+-------+---------------+---------+---------+------+------+----------+-------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------+------------+-------+---------------+---------+---------+------+------+----------+-------------------------------------------+ | 1 | SIMPLE | tbl_name | NULL | range | idx_abc | idx_abc | 10 | NULL | 1 | 100.00 | Using where; Using index; Using temporary | +----+-------------+----------+------------+-------+---------------+---------+---------+------+------+----------+-------------------------------------------+ 1 row in set, 1 warning (0.00 sec)
如果我們使用了order by null的關鍵字，那麼explain的結果中就沒有using filesort了，就是說不主動進行文件排序了，如果你訪問的結果集比較大，這其實是一種優化的方式。
