# SQL 效能調校 - Explain

`EXPLAIN` 是 MySQL 中的一個關鍵字，用於分析查詢的執行計畫（execution plan）。通過執行 `EXPLAIN` 查詢，你可以獲得有關 MySQL 數據庫如何執行特定 SQL 查詢的詳細信息。這些信息包括查詢的執行順序、使用的索引、掃描的記錄數等。 `EXPLAIN` 用於優化 SQL 查詢，以確保它們以最有效的方式運行。

以下是使用 `EXPLAIN` 的基本方法以及你可以從結果中獲得的一些重要信息：

1. **基本的 `EXPLAIN` 查詢**：要使用 `EXPLAIN`，只需在你的 SQL 查詢之前加上 `EXPLAIN` 關鍵字，然後執行該查詢。例如：

   ```sql
   EXPLAIN SELECT * FROM your_table WHERE column_name = 'some_value';
   ```
2. **查詢的 ID**：在 `EXPLAIN` 結果中，你會看到查詢的 ID（`id`），這是查詢的唯一標識符，其數值越大優先值越高。
3. **選擇類型**：`select_type` 表示查詢的類型，可以是 `SIMPLE`（簡單查詢）、`PRIMARY`（最外層查詢）、`SUBQUERY`（子查詢）等。不同的查詢類型可能會影響性能。
4. **表格**：`table` 列顯示用於查詢的表格名稱。
5. **類型**：`type` 表示用於查詢的連接類型，這可以告訴你是否在查詢中使用了索引。常見的類型包括 `ALL`（全表掃描）、`index`（使用索引掃描）、`range`（範圍查詢）等。性能從好到壞依次是：system > const > eq\_ref > ref > ref\_or\_null > index\_merge > unique\_subquery > index\_subquery > range > index > ALL
6. **可能的鍵**：`possible_keys` 顯示了可能用於查詢的索引列表。
7. **使用的鍵**：`key` 顯示實際用於查詢的索引。這可以告訴你是否使用了索引以及使用了哪個索引。
8. **鍵長度**：`key_len` 顯示了使用的索引的長度。
9. **掃描的記錄數**：`rows` 顯示了查詢時掃描的記錄數。這可以幫助你評估查詢的效能。
10. **額外信息**：`Extra` 列提供了關於查詢執行的其他信息，例如 `Using where`（表示使用了 `WHERE` 子句）或 `Using temporary`（表示使用了臨時表）。

通過分析 `EXPLAIN` 的結果，你可以了解 MySQL 是如何執行你的查詢的，並優化查詢以提高性能。你可以關注以下方面進行優化：

* 確保查詢使用了適當的索引。
* 減少掃描的記錄數，以降低查詢的複雜度。
* 注意選擇類型，選擇最合適的查詢策略。
* 識別並消除不必要的 `Using temporary` 或 `Using filesort`。

總之，`EXPLAIN` 是一個有用的工具，可以幫助你優化 SQL 查詢，以確保數據庫系統以最佳性能運行。

#### 首先我們就先建立三張表，來進行測試

從我給的SELECT 範例，可以查看到各種狀況的EXPLAIN 結果，進而提升自我再編寫SQL 時，就能避免效能問題。

```sql
-- 刪除已存在的表 one, two, three，如果存在的話
DROP TABLE IF EXISTS one;
DROP TABLE IF EXISTS two;
DROP TABLE IF EXISTS three;

-- 創建表 one，包含 one_id（主鍵），two_id 和 one_name 列
create table one (
    one_id int unsigned primary key not null auto_increment,
    two_id int unsigned not null,
    one_name varchar(10) null
);

-- 創建表 two，包含 two_id（主鍵），three_id 和 two_name 列
create table two (
    two_id int unsigned primary key not null auto_increment,
    three_id int unsigned not null,
    two_name varchar(10) not null
);

-- 創建表 three，包含 three_id 和 three_name 列
create table three (
    three_id int unsigned,
    three_name varchar(10) not null
);

-- 向表 one 插入數據
insert into one(two_id, one_name) VALUES (1,'id_1') ,(2,'id_2'),(3,'id_3'),(4,'id_1');

-- 向表 two 插入數據
insert into two(three_id, two_name) VALUES (1,'id_1') ,(2,'id_2'),(3,'id_3'),(4,'id_1');

-- 向表 three 插入數據
insert into three(three_name) VALUES ('id_1') ,('id_2'),('id_3'),('id_1');


# 以下可以自行加上 EXPLAIN 去查看解釋內容
-- 查詢表 one（o）、two（t）、three（r）的所有列，通過條件關聯它們
-- 具體條件：表 one 中的 two_id 等於表 two 中的 two_id，表 two 中的 three_id 等於表 three 中的 three_id
SELECT * FROM one o, two t, three r WHERE o.two_id = t.two_id AND t.three_id = r.three_id;

-- 查詢表 one（o）中的所有列，其中條件為 o.two_id 等於子查詢的結果
-- 子查詢：從表 two（t）中選擇 t.two_id，其中 t.three_id 等於子查詢的結果
-- 子查詢：從表 three（r）中選擇 r.three_id，其中 r.three_name 等於 '我是第三表2'
SELECT * FROM one o WHERE o.two_id = (SELECT t.two_id FROM two t WHERE t.three_id = (SELECT r.three_id FROM three r WHERE r.three_name='我是第三表2'));

-- 查詢表 one（o）中的所有列，其中條件為 o.two_id 等於子查詢的結果，並且 o.one_id 在表 one 中有與 o.one_name='我是第一表2' 相關聯的行
SELECT * FROM one o WHERE o.two_id = (SELECT t.two_id FROM two t WHERE t.three_id = (SELECT r.three_id FROM three r WHERE r.three_name='我是第三表2')) AND o.one_id IN (SELECT one_id FROM one WHERE o.one_name='我是第一表2');

-- 查詢表 two（t）中的 two_name 列和一個子查詢的結果（one.one_id），該子查詢返回表 one（one）中的 one_id 列
-- 使用 UNION 運算符將兩個查詢的結果合併
-- 第一個查詢選擇表 two 中 two_id 和 two_name 列，其中 two_name 為空字符串('')
-- 第二個查詢選擇表 three（r）中的 three_name 列和 three_id 列
SELECT t.two_name, (SELECT one.one_id FROM one) o FROM (SELECT two_id, two_name FROM two WHERE two_name ='') t UNION (SELECT r.three_name, r.three_id FROM three r);

-- 查詢表 one 中 one_id=1 的所有列
SELECT * FROM one WHERE one_id=1;

-- 查詢表 one（o）中的 one_name 列，同時關聯表 two（t）以匹配 o.one_id = t.two_id
SELECT o.one_name FROM one o, two t WHERE o.one_id = t.two_id;

-- 查詢表 one（o）中的 one_id 列，其中 o.one_name = 'id_1'
SELECT o.one_id FROM one o WHERE o.one_name = 'id_1';

-- 查詢表 one（o）中的 one_id 列，其中 o.one_name = 'id_1' 或 o.one_name 為空（NULL）
-- 無法獲得 type = ref_or_null 的結果
SELECT o.one_id FROM one o WHERE o.one_name = 'id_1' OR o.one_name IS NULL;

-- 查詢表 one（o）中的所有列，其中 o.one_id > 1 且 o.one_name = 'xin'
SELECT * FROM one o WHERE o.one_id > 1 AND o.one_name ='xin';

-- 查詢表 three 中 three_id 介於 2 和 3 之間的所有列
SELECT * FROM three WHERE three_id BETWEEN 2 AND 3;

-- 查詢表 two 中的 two_id 列
SELECT two_id FROM two;

-- 查詢表 two 中的所有列
SELECT * FROM two;
```