# 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;
```


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://xu-min-chang.gitbook.io/caster-develop-note/java/mysql/sql-xiao-neng-diao-xiao-explain.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.