分類
發燒車訊

MySQL 執行計劃詳解

MySQL 原理篇

我們經常使用 MySQL 的執行計劃來查看 SQL 語句的執行效率,接下來分析執行計劃的各個显示內容。

EXPLAIN SELECT * FROM users 
WHERE id IN (SELECT userID FROMuser_address WHERE address = "湖南長沙麓谷") ;

執行計劃的 id

select 查詢的序列號,標識執行的順序

  • id 相同,執行順序由上至下
  • id 不同,如果是子查詢,id 的序號會遞增,id 值越大優先級越高,越先被執行

執行計劃的 select_type

查詢的類型,主要是用於區分普通查詢、聯合查詢、子查詢等。

  • SIMPLE:簡單的 select 查詢,查詢中不包含子查詢或者 union
  • PRIMARY:查詢中包含子部分,最外層查詢則被標記為 primary
  • SUBQUERY/MATERIALIZED:SUBQUERY 表示在 select 或 where 列表中包含了子查詢,MATERIALIZED表示 where 後面 in 條件的子查詢
  • UNION:表示 union 中的第二個或後面的 select 語句
  • UNION RESULT:union 的結果

對於 UNION 和 UNION RESULT 可以通過下面的例子展現:

EXPLAIN
SELECT * FROM users WHERE id IN(1, 2)
UNION
SELECT * FROM users WHERE id IN(3, 4);

執行計劃的 table

查詢涉及到的表。

  • 直接顯示錶名或者表的別名
  • <unionM,N> 由 ID 為 M,N 查詢 union 產生的結果
  • <subqueryN> 由 ID 為 N 查詢產生的結果

執行計劃的 type 

訪問類型,SQL 查詢優化中一個很重要的指標,結果值從好到壞依次是:system > const > eq_ref > ref > range > index > ALL。

  • system:系統表,少量數據,往往不需要進行磁盤IO
  • const:常量連接
  • eq_ref:主鍵索引(primary key)或者非空唯一索引(unique not null)等值掃描
  • ref:非主鍵非唯一索引等值掃描
  • range:範圍掃描
  • index:索引樹掃描
  • ALL:全表掃描(full table scan)

下面通過舉例說明。

system

explain select * from mysql.time_zone;

上例中,從系統庫 MySQL 的系統表 time_zone 里查詢數據,訪問類型為 system,這些數據已經加載到內存里,不需要進行磁盤 IO,這類掃描是速度最快的。

explain select * from (select * from user where id=1) tmp;

再舉一個例子,內層嵌套(const)返回了一個臨時表,外層嵌套從臨時表查詢,其掃描類型也是 system,也不需要走磁盤 IO,速度超快。

const 

數據準備:

CREATE TABLE `user` (
  `id` int(11) NOT NULL,
  `NAME` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into user values(1,'shenjian');
insert into user values(2,'zhangsan');
insert into user values(3,'lisi');
explain select * from user where id=1;

const 掃描的條件為: 

  1. 命中主鍵(primary key)或者唯一(unique)索引
  2. 被連接的部分是一個常量(const)值 

如上例,id 是 主鍵索引,連接部分是常量1。

eq_ref

數據準備:

CREATE TABLE `user` (
  `id` int(11) NOT NULL,
  `NAME` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into user values(1,'shenjian');
insert into user values(2,'zhangsan');
insert into user values(3,'lisi');

CREATE TABLE `user_ex` (
  `id` int(11) NOT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into user_ex values(1,18);
insert into user_ex values(2,20);
insert into user_ex values(3,30);
insert into user_ex values(4,40);
insert into user_ex values(5,50);
EXPLAIN SELECT * FROM USER,user_ex WHERE user.id=user_ex.id;

eq_ref 掃描的條件為,對於前表的每一行(row),后表只有一行被掃描。 

再細化一點:  

  1. join 查詢
  2. 命中主鍵(primary key)或者非空唯一(unique not null)索引
  3. 等值連接;

如上例,id 是主鍵,該 join 查詢為 eq_ref 掃描。

ref

數據準備:

CREATE TABLE `user` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into user values(1,'shenjian');
insert into user values(2,'zhangsan');
insert into user values(3,'lisi');

CREATE TABLE `user_ex` (
  `id` int(11) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into user_ex values(1,18);
insert into user_ex values(2,20);
insert into user_ex values(3,30);
insert into user_ex values(4,40);
insert into user_ex values(5,50);
EXPLAIN SELECT * FROM USER,user_ex WHERE user.id=user_ex.id;

如果把上例 eq_ref 案例中的主鍵索引,改為普通非唯一(non unique)索引。就由 eq_ref 降級為了 ref,此時對於前表的每一行(row),后表可能有多於一行的數據被掃描。

select * from user where id=1;

當 id 改為普通非唯一索引后,常量的連接查詢,也由 const 降級為了 ref,因為也可能有多於一行的數據被掃描。

ref 掃描,可能出現在 join 里,也可能出現在單表普通索引里,每一次匹配可能有多行數據返回,雖然它比 eq_ref 要慢,但它仍然是一個很快的 join 類型。

range

數據準備:

CREATE TABLE `user` (
  `id` int(11) NOT NULL,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into user values(1,'shenjian');
insert into user values(2,'zhangsan');
insert into user values(3,'lisi');
insert into user values(4,'wangwu');
insert into user values(5,'zhaoliu');
explain select * from user where id between 1 and 4;
explain select * from user where id in(1,2,3);
explain select * from user where id > 3;

range 掃描就比較好理解了,它是索引上的範圍查詢,它會在索引上掃碼特定範圍內的值。

像上例中的 between,in,> 都是典型的範圍(range)查詢。

index

explain count (*) from user;

如上例,id 是主鍵,該 count 查詢需要通過掃描索引上的全部數據來計數,它僅比全表掃描快一點。

ALL 

數據準備:

CREATE TABLE `user` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into user values(1,'shenjian');
insert into user values(2,'zhangsan');
insert into user values(3,'lisi');

CREATE TABLE `user_ex` (
  `id` int(11) DEFAULT NULL,
  `age` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into user_ex values(1,18);
insert into user_ex values(2,20);
insert into user_ex values(3,30);
insert into user_ex values(4,40);
insert into user_ex values(5,50);
explain select * from user,user_ex where user.id=user_ex.id;

如果 id 上不建索引,對於前表的每一行(row),后表都要被全表掃描。

文章中,這個相同的 join 語句出現了三次:

  1. 掃描類型為 eq_ref,此時 id 為主鍵
  2. 掃描類型為 ref,此時 id 為非唯一普通索引
  3. 掃描類型為 ALL,全表掃描,此時id上無索引

有此可見,建立正確的索引,對數據庫性能的提升是多麼重要。

總結 

  1. explain 結果中的 type 字段,表示(廣義)連接類型,它描述了找到所需數據使用的掃描方式;
  2. 常見的掃描類型有:system>const>eq_ref>ref>range>index>ALL,其掃描速度由快到慢;
  3. 各類掃描類型的要點是:
    1. system 最快:不進行磁盤 IO
    2. const:PK 或者 unique 上的等值查詢
    3. eq_ref:PK 或者 unique 上的 join 查詢,等值匹配,對於前表的每一行,后表只有一行命中
    4. ref:非唯一索引,等值匹配,可能有多行命中
    5. range:索引上的範圍掃描,例如:between、in、>
    6. index:索引上的全集掃描,例如:InnoDB 的 count
    7. ALL 最慢:全表掃描
  1. 建立正確的索引,非常重要;
  2. 使用 explain 了解並優化執行計劃,非常重要;

執行計劃 possible_keys

查詢過程中有可能用到的索引。

執行計劃 key

實際使用的索引,如果為 NULL ,則沒有使用索引。

執行計劃 rows

根據表統計信息或者索引選用情況,大致估算出找到所需的記錄所需要讀取的行數。

執行計劃 filtered 

表示返回結果的行數占需讀取行數的百分比, filtered 的值越大越好。

執行計劃 Extra 

十分重要的額外信息。

  • Using filesort:MySQL 對數據使用一個外部的文件內容進行了排序,而不是按照表內的索引進行排序讀取。
  • Using temporary:使用臨時表保存中間結果,也就是說 MySQL 在對查詢結果排序時使用了臨時表,常見於order by 或 group by。
  • Using index:表示 SQL 操作中使用了覆蓋索引(Covering Index),避免了訪問表的數據行,效率高。
  • Using index condition:表示 SQL 操作命中了索引,但不是所有的列數據都在索引樹上,還需要訪問實際的行記錄。
  • Using where:表示 SQL 操作使用了 where 過濾條件。
  • Select tables optimized away:基於索引優化 MIN/MAX 操作或者 MyISAM 存儲引擎優化 COUNT(*) 操作,不必等到執行階段再進行計算,查詢執行計劃生成的階段即可完成優化。
  • Using join buffer (Block Nested Loop):表示 SQL 操作使用了關聯查詢或者子查詢,且需要進行嵌套循環計算。

下面通過舉例說明。

數據準備:

CREATE TABLE `user` (
  `id` int(11) NOT NULL,
  `name` varchar(20) DEFAULT NULL,
  `sex` varchar(5) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

insert into user values(1, 'shenjian','no');
insert into user values(2, 'zhangsan','no');
insert into user values(3, 'lisi', 'yes');
insert into user values(4, 'lisi', 'no');

數據說明:

用戶表:id 主鍵索引,name 普通索引(非唯一),sex 無索引。

四行記錄:其中 name 普通索引存在重複記錄 lisi。

Using filesort

explain select * from user order by sex;

 

Extra 為 Using filesort 說明,得到所需結果集,需要對所有記錄進行文件排序。

這類 SQL 語句性能極差,需要進行優化。

典型的,在一個沒有建立索引的列上進行了 order by,就會觸發 filesort,常見的優化方案是,在 order by 的列上添加索引,避免每次查詢都全量排序。

Using temporary

explain select * from user group by name order by sex;

Extra 為 Using temporary 說明,需要建立臨時表(temporary table)來暫存中間結果。

這類 SQL 語句性能較低,往往也需要進行優化。

典型的 group by 和 order by 同時存在,且作用於不同的字段時,就會建立臨時表,以便計算出最終的結果集。

臨時表存在兩種引擎,一種是 Memory 引擎,一種是 MyISAM 引擎,如果返回的數據在 16M 以內(默認),且沒有大字段的情況下,使用 Memory 引擎,否則使用 MyISAM 引擎。 

Using index

EXPLAIN SELECT id FROM USER;

Extra 為 Using index 說明,SQL 所需要返回的所有列數據均在一棵索引樹上,而無需訪問實際的行記錄。

這類 SQL 語句往往性能較好。

Using index condition

explain select id, name, sex from user where name='shenjian';

Extra 為 Using index condition 說明,確實命中了索引,但不是所有的列數據都在索引樹上,還需要訪問實際的行記錄。

這類 SQL 語句性能也較高,但不如 Using index。

Using where

explain select * from user where sex='no';

Extra 為 Using where 說明,查詢的結果集使用了 where 過濾條件,比如上面的 SQL 使用了 sex = 'no' 的過濾條件

Select tables optimized away

EXPLAIN SELECT MAX(id) FROM USER;

 

比如上面的語句查詢 id 的最大值,因為 id 是主鍵索引,根據 B+Tree 的結構,天然就是有序存放的,所以不需要等到執行階段再進行計算,查詢執行計劃生成的階段即可完成優化。

Using join buffer (Block Nested Loop)

explain select * from user where id in (select id from user where sex='no');

Extra 為 Using join buffer (Block Nested Loop) 說明,需要進行嵌套循環計算。內層和外層的 type 均為 ALL,rows 均為4,需要循環進行4*4次計算。

這類 SQL 語句性能往往也較低,需要進行優化。

典型的兩個關聯表 join,關聯字段均未建立索引,就會出現這種情況。常見的優化方案是,在關聯字段上添加索引,避免每次嵌套循環計算。

參考 

本站聲明:網站內容來源於博客園,如有侵權,請聯繫我們,我們將及時處理
【其他文章推薦】

※帶您來了解什麼是 USB CONNECTOR  ?

※自行創業 缺乏曝光? 下一步"網站設計"幫您第一時間規劃公司的門面形象

※如何讓商品強力曝光呢? 網頁設計公司幫您建置最吸引人的網站,提高曝光率!!

※綠能、環保無空污,成為電動車最新代名詞,目前市場使用率逐漸普及化

※廣告預算用在刀口上,網站設計公司幫您達到更多曝光效益