如何查看哪一個SQL語法是最佳解：explain執行計畫

2024-02-22 258 words 2 minutes views

Contents

昨天與一位做自有產品的執行長官面談被問到如何確認SQL的效能，當下沒能立即回答上。

但今天在練習 LeetCode SQL 50 的時候，看到有神人對一道 570. Managers with at Least 5 Direct Reports 題目給出了五種 solutions，於是回想起之前聽過但不常用的 explain 語法，這樣慘痛的教訓肯定是要花時間瞭解一下的吧。

問題

題目只有一張 Table : Employee:

每一列包含了員工id、員工名、員工所屬部門以及他們的所屬主管id，如果managerId 為 null，就表示這員工沒有直屬主管。

沒有員工會自己當自己的主管，即 id 與 managerId 不能為相同的值。

1
2
3
4
5
6


create table employee (
  id int not null primary key auto_increment,
  name varchar(20),
  department varchar(2),
  managerId int
) auto_increment=101;

Table 欄位內容：

Employee table:
+-----+-------+------------+-----------+
| id  | name  | department | managerId |
+-----+-------+------------+-----------+
| 101 | John  | A          | null      |
| 102 | Dan   | A          | 101       |
| 103 | James | A          | 101       |
| 104 | Amy   | A          | 101       |
| 105 | Anne  | A          | 101       |
| 106 | Ron   | B          | 101       |
+-----+-------+------------+-----------+

預期結果：

+------+
| name |
+------+
| John |
+------+

解法:

A.

使用了一個子查詢來計算 directReports，並與外表做JOIN，可能會引起性能問題，因為每次執行都需要執行子查詢

B.

使用了 Inner join 和 Group by，沒有子查詢

C.

使用了子查詢和 IN 子句，雖然寫法簡單，但在查詢大數據量的資料表時，性能不如其他寫法

D.

使用了JOIN 和 GROUP BY，類似 (B) 第二個查詢

E.

使用了 LEFT JOIN 以及 GROUP BY，使用LEFT JOIN可能會返回更多行，再用 HAVING 子句過濾結果，性能不如 (B) 第二個查詢

如何看 explain 產出的 result grid ?

select_type 查詢類型策略：子查詢聯合查詢單表查詢
- simple ：簡單查詢，不包含子查詢，也不含union查詢
- primary：包含子查詢的主查詢（最外層）
- union：union之後的表
- dependent union：
- union result：
- subquery：包含子查詢的主查詢（非最外層）
- dependent subquery
- derived：衍生查詢（用到了臨時表）
- materialized
- uncacheable subquery
- uncacheable union
table 查詢的資料表名字，參考(E)就會是表的別名 (xxx as e1)

⭐️type連結使用類型

ranking	type	desc
最佳	`system`	是第二個`const`type的特殊情況，表只有一行
	`const`	使用PK主鍵或唯一的index索引
	`eq_ref`	在連接查詢時使用equivalent join等值連接，且連接條件使用PK 或 unique key
	`ref`	使用非唯一鍵或主鍵
	`fulltext`	使用全文索引
	`ref_or_null`	類似`ref`type，但MySQL會另外查詢哪些行包含NULL，常見於解析子查詢優化
	`index_merge`	在一個查詢裡使用到很多 index 索引
	`unique_subquery`	相較於`eq_ref`，使用了`in`子查詢，子查詢是主鍵或唯一索引
	`index_subquery`	類似 unique_subquery，但在子查詢中使用的是非唯一索引
	`range`	使用索引返回一個範圍的結果
	`index`	全表掃描，針對索引中的資料查詢。雖然避免了排序，開銷仍非常大
最差	`ALL`	最壞的情況，對每一筆紀錄完全掃描

possible_keys：在這table中能使用哪些index，以幫助查詢
key：代表你用什麼 index 去搜尋（實際使用的index）
ken_len：index長度，通常長度越短越好（保持precision的情況下）
ref：哪一個 index 被使用了
rows：返回結果的行數
Extra：其它說明

Contents

如何查看哪一個SQL語法是最佳解：explain執行計畫

問題

預期結果：

解法:

A.

B.

C.

D.

E.

如何看 explain 產出的 result grid ?

參考資料