SQL 效能調校 - Explain
EXPLAIN
是 MySQL 中的一個關鍵字,用於分析查詢的執行計畫(execution plan)。通過執行 EXPLAIN
查詢,你可以獲得有關 MySQL 數據庫如何執行特定 SQL 查詢的詳細信息。這些信息包括查詢的執行順序、使用的索引、掃描的記錄數等。 EXPLAIN
用於優化 SQL 查詢,以確保它們以最有效的方式運行。
以下是使用 EXPLAIN
的基本方法以及你可以從結果中獲得的一些重要信息:
基本的
EXPLAIN
查詢:要使用EXPLAIN
,只需在你的 SQL 查詢之前加上EXPLAIN
關鍵字,然後執行該查詢。例如:EXPLAIN SELECT * FROM your_table WHERE column_name = 'some_value';
查詢的 ID:在
EXPLAIN
結果中,你會看到查詢的 ID(id
),這是查詢的唯一標識符,其數值越大優先值越高。選擇類型:
select_type
表示查詢的類型,可以是SIMPLE
(簡單查詢)、PRIMARY
(最外層查詢)、SUBQUERY
(子查詢)等。不同的查詢類型可能會影響性能。表格:
table
列顯示用於查詢的表格名稱。類型:
type
表示用於查詢的連接類型,這可以告訴你是否在查詢中使用了索引。常見的類型包括ALL
(全表掃描)、index
(使用索引掃描)、range
(範圍查詢)等。性能從好到壞依次是:system > const > eq_ref > ref > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL可能的鍵:
possible_keys
顯示了可能用於查詢的索引列表。使用的鍵:
key
顯示實際用於查詢的索引。這可以告訴你是否使用了索引以及使用了哪個索引。鍵長度:
key_len
顯示了使用的索引的長度。掃描的記錄數:
rows
顯示了查詢時掃描的記錄數。這可以幫助你評估查詢的效能。額外信息:
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;
Last updated