mysql 8.0.28 查詢語句執行順序實測結果

語言: CN / TW / HK

TL;NRs

根據實測結果,MySQL8.0.28 中 SQL 語句的執行順序為:

(8)     SELECT 
(5)     DISTINCT <select_list>
(1)     FROM <left_table>
(3)     <join_type> JOIN <right_table>
(4)     ON <join_condition>
(2)     WHERE <where_condition>
(6)     GROUP BY <group_by_list>
(7)     HAVING <having_condition>
(9)     ORDER BY <order_by_condition>
(10)    LIMIT <limit_number>

引言

關於 SQL 語句的執行順序,常見的是以下版本。然而該版本卻與實測結果不符。

(7)     SELECT 
(8)     DISTINCT <select_list>
(1)     FROM <left_table>
(3)     <join_type> JOIN <right_table>
(2)     ON <join_condition>
(4)     WHERE <where_condition>
(5)     GROUP BY <group_by_list>
(6)     HAVING <having_condition>
(9)     ORDER BY <order_by_condition>
(10)    LIMIT <limit_number>

MySQL 可以通過 EXPLAIN ANALYZE sql_statement 顯示真實的執行過程。那麼可以通過一個複雜的語句完成測試。

準備資料

準備三個表 t1, t2, t3, 其中資料分別為:

測試

執行以下語句

EXPLAIN ANALYZE
SELECT
DISTINCT COUNT(p.id) AS cnt, COUNT(e.id) AS nn
FROM t1 p
LEFT JOIN t2 q ON p.id > q.id
INNER JOIN t2 w ON q.id < w.id
RIGHT JOIN t3 e ON w.id = e.id
WHERE p.id < 10
GROUP BY p.id
HAVING cnt > 3
ORDER BY cnt DESC, nn DESC
LIMIT 1;

結果為:

-> Limit: 10 row(s)  (actual time=0.394..0.395 rows=1 loops=1)
    -> Sort with duplicate removal: cnt DESC, nn DESC  (actual time=0.393..0.394 rows=1 loops=1)
        -> Filter: (cnt > 3)  (actual time=0.372..0.374 rows=5 loops=1)
            -> Table scan on <temporary>  (actual time=0.001..0.001 rows=6 loops=1)
                -> Aggregate using temporary table  (actual time=0.370..0.372 rows=6 loops=1)
                    -> Inner hash join (e.id = w.id)  (cost=4.73 rows=3) (actual time=0.314..0.324 rows=32 loops=1)
                        -> Table scan on e  (cost=0.13 rows=5) (actual time=0.008..0.016 rows=5 loops=1)
                        -> Hash
                            -> Filter: (q.id < w.id)  (cost=3.15 rows=3) (actual time=0.265..0.282 rows=32 loops=1)
                                -> Inner hash join (no condition)  (cost=3.15 rows=3) (actual time=0.259..0.271 rows=72 loops=1)
                                    -> Covering index scan on w using PRIMARY  (cost=0.13 rows=3) (actual time=0.007..0.010 rows=4 loops=1)
                                    -> Hash
                                        -> Nested loop inner join  (cost=2.10 rows=3) (actual time=0.084..0.232 rows=18 loops=1)
                                            -> Filter: (p.id < 10)  (cost=1.05 rows=3) (actual time=0.036..0.051 rows=7 loops=1)
                                                -> Table scan on p  (cost=1.05 rows=8) (actual time=0.034..0.046 rows=8 loops=1)
                                            -> Filter: (p.id > q.id)  (cost=0.13 rows=1) (actual time=0.021..0.025 rows=3 loops=7)
                                                -> Covering index range scan on q (re-planned for each iteration)  (cost=0.13 rows=3) (actual time=0.021..0.024 rows=3 loops=7)

結果分析

這是一個呼叫棧,還原其執行過程為:

篩選 LIMIT 10 {
    排序 ORDER BY cnt DESC, nn DESC {
        呼叫 HAVING cnt > 3 過濾器 {
            讀取臨時聚合表 {
                聚合 {
                    第三次聯結 RIGHT JOIN t3 e ON w.id = e.id {
                        掃描表 e ;
                        第二次聯結 INNER JOIN t2 w ON q.id < w.id {
                            掃描表 w {
                                使用主鍵掃描
                                得到 4 行
                            }
                            第一次聯結 t1 p LEFT JOIN t2 q ON p.id > q.id {
                                掃描表 p {
                                    使用 WHERE p.id < 10 過濾器
                                    共 8 行,返回 7 行
                                }
                                迴圈掃描表 q {
                                    7 次迴圈 {
                                        使用過濾器 ON  p.id > q.id 
                                    }
                                }
                                執行雜湊,共 21 行,返回 18 行
                            }
                            執行全連線,獲得 4 * 18 = 72 行
                            執行 ON q.id < w.id 過濾器,剩餘 32 行
                        }
                        執行相等聯結 e.id = w.id, 返回 32 行
                    }
                    完成所有的聯結,獲得 32 行
                    進行聚合 GROUP BY p.id 獲得 6 行
                }
                讀取臨時聚合表,獲得 6 行 
            }
            執行過濾,剩餘 5 行 
        }
        去重,剩餘 2 行 
        排序
		返回 1 行
    }
    輸出前 1 項
}

可以看到:

  • 首先進行表的掃描,也就是所謂的 FROM 第一
    • 有主鍵的表會使用主鍵索引
    • 有索引的表會使用索引
    • 有多個表需要掃描時,根據 SQL 語句進行倒序執行
  • WHERE 會在表的掃描過程中執行,也就是 WHERE 第二
  • 讀取到表後,會執行連線
    • 有多個聯結時,同樣是倒序執行
    • 首先執行全連線,也就是 JOIN 第三
    • 全連線完成後會馬上執行 ON 的過濾,也就是 ON 第四
  • 完成連線後,會執行去重,也就是 DISTINCT 第五
  • 完成去重後,會進行上一層的連線
  • 所有連線都完成後,會執行聚合,也就是 GROUP BY 第六
  • 聚合完成後,會執行一次掃描,也就是 SELECT 第七
  • 掃描結束後,會執行 HAVING 過濾,也就是 HAVING 第八
  • 完成過濾後,會進行排序,也就是 ORDER BY 第九
  • 最後進行 LIMIT 的限制,也就是 LIMIT 第十
    • 需要注意的是,LIMIT 的引數在 sort 函式的返回結果中就已經起作用,合理推測是使用的堆排序

結論

根據實測結果,MySQL8.0.28 中 SQL 語句的執行順序為:

(8)     SELECT 
(5)     DISTINCT <select_list>
(1)     FROM <left_table>
(3)     <join_type> JOIN <right_table>
(4)     ON <join_condition>
(2)     WHERE <where_condition>
(6)     GROUP BY <group_by_list>
(7)     HAVING <having_condition>
(9)     ORDER BY <order_by_condition>
(10)    LIMIT <limit_number>