大家好，我是聰，一個樂于分享的小小程序員。在不久之前我寫了一個慢 SQL 分析工具，可以用來分析 Java Mybatis 項目的 SQL 執行情況，其中剛好涉及到了 explain 的使用。感興趣的可以了解一下。
Github 地址?：https://github.com/lhccong/sql-slow-mirror
那么開始我們今天的主題吧，今天的聰碰見了一個當面試官的朋友問我，用過 explain 嗎？說說怎么分析的？
聰：一臉正經的回答道💡
聰：你好面試官，我當然用過 explain，我平時都會那它去查看 SQL 語句是否還能優化。接下來我從主要屬性跟實際例子來講解：
主要的屬性
1） 🌱 id
查詢中每個 SELECT 子句的標識符。簡單查詢的 id 通常為 1，復雜查詢（如包含子查詢或 UNION）的 id 會有多個。
2） 🌱 select_type
描述查詢的類型。比如：簡單查詢顯示為 SIMPLE，子查詢顯示為 SUBQUERY，UNION 中的第二個和后續查詢顯示為 UNION。
3）🌱 table
表名稱這個就不用再詳細解釋了吧哈哈。
4） 🌱 partitions
表示查詢涉及到的分區。如果你有使用分區表的話才需要關注此字段。
5） 🌱 type（重點記憶?）
表示訪問的類型，這里也可以看出你的 SQL 的性能。可能的值從最好到最差包括：system、const、eq_ref、ref、range、index、ALL。其中 ALL 表示全表掃描，效率最低。
-
system：
表示查詢的表只有一行（系統表）。這是一個特殊的情況，不常見。
-
const：
表示查詢的表最多只有一行匹配結果。這通常發生在查詢條件是主鍵或唯一索引，并且是常量比較，以下是一個使用主鍵查找的例子：
EXPLAIN SELECT * FROM employees WHERE employee_id = 12345; -
eq_ref：
表示對于每個來自前一張表的行，MySQL 僅訪問一次這個表。這通常發生在連接查詢中使用主鍵或唯一索引的情況下，例子如下：
EXPLAIN SELECT * FROM employees e JOIN departments d ON e.department_id = d.department_id; -
ref：
MySQL 使用非唯一索引掃描來查找行。查詢條件使用的索引是非唯一的（如普通索引），例子如下使用了非唯一索引進行查找：
EXPLAIN SELECT * FROM employees WHERE department_id = 5; -
range：表示 MySQL 會掃描表的一部分，而不是全部行。范圍掃描通常出現在使用索引的范圍查詢中（如
BETWEEN、>,<,>=,<=）。下面是范圍查詢：EXPLAIN SELECT * FROM employees WHERE salary BETWEEN 50000 AND 100000; -
index：表示 MySQL 掃描索引中的所有行，而不是表中的所有行。即使索引列的值覆蓋查詢，也需要掃描整個索引。以下是使用索引掃描例子：
EXPLAIN SELECT name FROM employees; -
all（性能最差）：表示 MySQL 需要掃描表中的所有行，即全表掃描。這通常出現在沒有索引的查詢條件中。以下是全表掃描例子：
EXPLAIN SELECT * FROM employees;
6） 🌱 possible_keys
表示查詢可能使用的索引列表。
7） 🌱 key
實際使用索引的長度。如果沒有使用索引，該字段顯示為 NULL。
8） 🌱 key_len
這個字段表示使用的索引的長度。該值是根據索引的定義和查詢條件計算的。
9） 🌱 rows
MySQL 會估計為了找到所需的行，需要讀取的行數。該值是一個估計值，不是精確值。
10）🌱 filtered
顯示查詢條件過濾掉的行的百分比。一個高百分比表示查詢條件的選擇性好。
11）🌱 Extra
額外信息，如 Using index（表示使用覆蓋索引）、Using where（表示使用 WHERE 條件進行過濾）、Using temporary（表示使用臨時表）、Using filesort（表示需要額外的排序步驟）。
看完這個是不是一目了然了捏，那么接下來跟著我看看實際的分析例子吧！！
實際例子🌰
1.創建 employees 表
CREATE TABLE employees (employee_id INT PRIMARY KEY,first_name VARCHAR(50),last_name VARCHAR(50),department_id INT,salary DECIMAL(10, 2),hire_date DATE,INDEX (department_id)
);
我們要執行以下查詢來查找部門 ID 為 5 且薪水在 50000 到 100000 之間的員工，并按薪水降序排序：
SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE department_id = 5 AND salary BETWEEN 50000 AND 100000
ORDER BY salary DESC;
2.我們先使用 explain 分析計劃進行分析：
EXPLAIN SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE department_id = 5 AND salary BETWEEN 50000 AND 100000
ORDER BY salary DESC;
輸出結果如下：
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | employees | NULL | ref | department_id | department_id | 4 | const | 5000 | 20.00 | Using where; Using filesort |
3.分析執行計劃
從執行計劃中看出，type 為 ref，表示使用了 department_id 索引，這是個非唯一索引。key 為 department_id 這個索引，而且 rows 為 5000，表示掃描了 5000 行匹配的 department_id = 5 的條件。從 Extra 看出在應用 WHERE 條件后，還需要進行文件排序來滿足 ORDER BY 子句。
4.找出問題
盡管查詢使用了索引，但由于索引不完全覆蓋查詢的條件和排序，查詢需要進行額外的文件排序。這可能會導致性能瓶頸，特別是在結果集較大時。
5.優化解決它！
創建復合索引
創建一個包含 department_id 和 salary 的復合索引，這樣可以覆蓋查詢的 WHERE 和 ORDER BY 條件：
CREATE INDEX idx_department_salary ON employees (department_id, salary);
復合索引可以使查詢在掃描 department_id 列時，同時按 salary 列排序，避免額外的文件排序。
再次執行計劃分析
優化后的 EXPLAIN 輸出如下：
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | employees | NULL | range | idx_department_salary | idx_department_salary | 5 | NULL | 500 | 100.00 | Using where |
6.分析優化后的結果
從新的 EXPLAIN 輸出中可以看出：
- type:
range，表示使用范圍掃描，這是個相對高效的訪問類型。 - key:
idx_department_salary，表示實際使用了復合索引。 - rows: 500，估計讀取的行數減少了，因為索引更精確地覆蓋了查詢條件。
- Extra: 僅顯示
Using where，不再需要文件排序，因為索引已經覆蓋了排序需求。
是不是分析起來很簡單咧，完結撒花！！！！，除了新增聯合索引的方式，你們還知道什么優化策略嗎？
