SQL 效能調校 - Explain

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

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

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

   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 時，就能避免效能問題。

-- 刪除已存在的表 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;