通常查詢慢查詢SQL
語句時會使用EXPLAIN
指令來查看SQL
語句的執行計劃,透過傳回的訊息,可以了解到Mysql
優化器是如何執行SQL
語句,透過分析可以幫助我們提供最佳化的思路。
explain 指令主要用於檢視SQL 語句的執行計劃,該指令可以模擬最佳化器執行SQL 查詢語句,可以幫助我們編寫和優化SQL。那麼 explain 具體可以提供哪些訊息,幫助我們如何去優化 SQl 的呢?
表格的讀取順序
資料讀取操作的運算類型
哪些索引可以使用
哪些索引被實際使用
#表之間的引用
每張表格有多少行被最佳化器查詢
使用方式: explain 待執行的sql
explain 會傳回一個待執行SQL 的執行計劃列表,列表包含了12 個字段,字段共同描述了SQL 在執行計劃中將會採取何種方式執行。以下清單詳細描述了執行計劃表的欄位意義:
欄位名稱 | 描述 |
---|---|
#id | 執行select 語句查詢的序號,決定表的讀取順序 |
select_type | 查詢的類型,也就是資料讀取操作的操作類型 |
table | 查詢的表名 |
表分區 | |
存取類型 | |
##可使用的索引。查詢所涉及的欄位上若存在索引,則該索引將被列出,但不一定被查詢實際使用到。如果這個字段為null 但是字段key 不為null,這種情況就是在查找時沒有可以使用的二級索引樹,但是二級索引中包含了需要查詢的字段,於是就不再查找聚簇索引(聚簇索引比較大),轉而掃描這個二級索引樹(二級索引樹比較小),並且此時一般訪問類型type 為index,及掃描整棵索引樹。 | |
實際掃描使用的索引。如果為null,則沒有使用索引;查詢中若使用了覆蓋索引,則該索引僅出現在key清單中; | |
#索引中使用的位元組數。可透過此列計算查詢中使用的索引的長度,在不損失精確性的情況下,長度越短越好;key_len 顯示的值為索引欄位的最大可能長度,並非實際使用長度,即key_len 是根據表定義計算而得,不是透過表格內檢索出的; | |
#顯示索引的哪一列被使用了。如果可能的話,是一個常數,哪些列或常數別用於查找索引列上的值; | |
根據表統計資訊及索引選用情況,大致估算出找到所需的記錄所需讀取的行數; | |
搜尋條件過濾後剩餘資料的百分比。 | |
包含不適合在其它列中顯示但十分重要的額外資訊 |
描述 | |
---|---|
執行順序由上到下 | |
如果是子查詢,id 的序號會遞增, id 值越大優先順序越高,越先被執行 | |
如果id 相同,可以認為是一組,從上往下順序執行,在所有群組中,id值越大,優先權越高,越先執行 |
類型名稱 | 描述 |
---|---|
simple | 簡單的select 查詢,查詢中不包含子查詢或union; |
primary | 查詢中若包含任何複雜的子查詢,最外層查詢則被標記; |
subquery | 在select 或where 清單中包含了子查詢; |
#dependent subquery | 子查詢中的第一個SELECT, 取決於外面的查詢。即子查詢依賴外層查詢的結果。 |
derived | 在from 清單中包含的子查詢被標記為DERIVED(衍生表),mysql 會遞歸執行這些子查詢,把結果放到臨時表中; |
union | 若第二個select 出現在union 之後,則被標記為union,若union 包含在from 子句的子查詢中,外層select 將被標記為DERIVED; |
union result | 從union 表(即union 合併的結果集)中取得select 查詢的結果; |
meterialized | 物化表,當子查詢關聯查詢時,子查詢結果儲存在物化暫存表,然後根據暫存表中的資料去主表匹配。 |
dependent union | UNION 中的第二個或後面的查詢語句,取決於外面的查詢 |
顯示的查詢表名,如果查詢使用了別名,那麼這裡顯示的是別名,如果不涉及對資料表的操作,那麼這顯示為null,也可以是以下之一:
#類型名稱 | #描述 |
---|---|
|
> 表示這個是臨時表,後邊的N就是執行計畫中的id,表示結果來自於這個查詢產生。
|
與 |
查詢將符合記錄的分割區。該值NULL
用於非分割表。
依序從好到差:
system
>const
>eq_ref
>ref
>ref_or_null
>range
>#index
>ALL
除了all
之外,其他的type
都可以使用到索引,除了index_merge
之外,其他的type
只可以用到一個索引。
我們自己建立一系列表格來實驗下:
#SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for goods -- ---------------------------- DROP TABLE IF EXISTS `goods`; CREATE TABLE `goods` ( `id` int(11) NOT NULL, `sn` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL, `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of goods -- ---------------------------- INSERT INTO `goods` VALUES (1, 'sn123456', '衣服'); -- ---------------------------- -- Table structure for sku -- ---------------------------- DROP TABLE IF EXISTS `sku`; CREATE TABLE `sku` ( `id` int(11) NOT NULL, `goods_id` int(11) NOT NULL, `status` int(11) NOT NULL, `deleted` int(11) NOT NULL, `barcode` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL, `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE, UNIQUE INDEX `index_2`(`name`) USING BTREE, INDEX `index_1`(`goods_id`, `status`, `deleted`, `barcode`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of sku -- ---------------------------- INSERT INTO `sku` VALUES (1, 1, 1, 0, 'kt123456', '黑色'); SET FOREIGN_KEY_CHECKS = 1;
EXPLAIN SELECT * FROM sku WHERE id=1;复制代码##eq_ref
EXPLAIN SELECT * FROM sku,goods WHERE sku.goods_id=goods.id;ref
EXPLAIN SELECT * FROM sku WHERE goods_id=1;#ref_or_null
EXPLAIN SELECT * FROM sku WHERE name='123456' or name IS NULL;range
EXPLAIN SELECT * FROM sku WHERE id BETWEEN 1 and 10;index
EXPLAIN SELECT barcode FROM sku WHERE deleted=0;all
EXPLAIN SELECT * FROM sku WHERE deleted=0;(6)possible_keys
為index_merge
時,這裡可能出現兩個以上的索引,其他的select_type
這裡只會出現一個。
,假設 age 是 int 類型且不可為 null;name 是 varchar(20) 類型且可以為 null,編碼為 utf8。若以這兩個欄位為索引查詢,那麼 key_len 的值為 4 3 * 20 2 1 = 67
。具體計算規則如下表所示:<table>
<thead><tr class="firstRow">
<th>值類型</th>
<th>值名稱</th>
<th>描述</th>
</tr></thead>
<tbody>
<tr>
<td>字串</td>
<td>CHAR(n)</td>
<td>n 位元組長度</td>
</tr>
<tr>
<td><br></td>
<td>VARCHAR(n)</td>
<td>如果是utf8 編碼,則是3 n 2位元組;;如果是utf8mb4 編碼,則是4 n 2 位元組。 </td>
</tr>
<tr>
<td>數值類型</td>
<td>TINYINT</td>
<td>1位元組</td>
</tr>
<tr>
<td><br></td>
<td></td>
<td></td>1位元組</tr>
<tr>
<td><br></td>
<td></td>SMALLINT<td></td>2位元組</tr>
<tr>
<td><br></td>
<td></td>#MEDIUMINT<td></td>3位元組</tr>
<tr>
<td><br></td>
<td>##INT</td>
<td>4位元組</td>
</tr>
<tr>
<td></td>
<td></td>BIGINT<td></td>8位元組</tr>
<tr>
<td>
<br>時間類型</td>
<td>DATE</td>
<td>#3位元組</td>
</tr>
<tr>
<td><br></td>
<td>TIMESTAMP</td>
<td>4位元組</td>
</tr>
<tr>
<td></td>
<td></td>#DATETIME<td>
<br>#8位元組</td>
</tr>
</tbody>
</table>欄位屬性######NULL 屬性佔用一個位元組。如果一個欄位是 NOT NULL 的, 則不佔用。 #####################<h3 data-id="heading-19"><strong>(9)ref</strong></h3>
<p>如果是使用的常數等值查詢,這裡會顯示<code>const
,如果是連接查詢,被驅動表的執行計劃這裡會顯示驅動表的關聯字段,如果是條件使用了表達式或函數,或者條件列發生了內部隱式轉換,這裡可能顯示為func
。
這裡是執行計畫中估算的掃描行數,不是精確值。
使用explain extended
時會出現這個列,5.7
之後的版本預設就有這個字段,不需要使用explain extended
了。這個欄位表示儲存引擎傳回的資料在server層過濾後,剩下多少滿足查詢的記錄數量的比例,注意是百分比,不是具體記錄數。
這個欄位可以顯示的資訊非常多,有數十種,常用的有:
1 、distinct:在select
部分使用了distinct
關鍵字
2、no tables used:不帶from
字句的查詢或From dual
查詢。使用not in()
形式子查詢或not exists()
運算子的連接查詢,這種叫做反連接。即,一般連接查詢是先查詢內表,再查詢外表,反連接就是先查詢外表,再查詢內表。
3、using filesort:說明mysql
會對資料使用一個外部的索引排序,而不是依照表內的索引順序讀取。 mysql
中無法利用索引完成的排序操作稱為「檔案排序」。排序時無法使用到索引時,就會出現這個。常見於order by
語句中,需要盡快優化
4、using index:查詢時不需要回表查詢,直接透過索引就可以取得查詢的數據。
5、using join buffer(block nested loop),using join buffer(batched key accss) :5.6.x
之後的版本最佳化關聯查詢的 BNL
,BKA
特性。主要是減少內表的循環數量以及比較順序地掃描查詢。
6、using sort_union,using_union,using intersect,using sort_intersection:
and
的各個索引的條件時,該資訊表示是從處理結果取得交集or
連接各個使用索引的條件時,該資訊表示從處理結果取得並集and
和or
查詢資訊量大時,先查詢主鍵,然後進行排序合併後,才能讀取記錄並回傳。 7、using temporary:表示使用了暫存表儲存中間結果。臨時表可以是記憶體臨時表和磁碟臨時表,執行計劃中看不出來,需要查看status
變量,used_tmp_table
,used_tmp_disk_table
才能看出來。常見於order by
和分組查詢group by
。 group by
一定要遵循所建索引的順序與個數。需要盡快最佳化
8、using where:表示儲存引擎傳回的記錄並不是所有的都符合查詢條件,需要在server
層進行篩選。查詢條件中分為限制條件和檢查條件,5.6
之前,儲存引擎只能根據限制條件掃描資料並返回,然後server
層根據檢查條件進行過濾再返回真正符合查詢的資料。 5.6.x
之後支援ICP
特性(index condition pushdown,索引下推),可以把檢查條件也下推到儲存引擎層,不符合檢查條件和限制條件的數據,直接不讀取,這樣就大大減少了儲存引擎掃描的記錄數量。 extra
列顯示using index condition
#9、firstmatch(tb_name) :5.6.x
#開始引入的最佳化子查詢的新特性之一,常見於where
字句含有in()
類型的子查詢。如果內表的資料量比較大,就可能出現這個
10、loosescan(m..n) :5.6.x
之後引進的最佳化子查詢的新特性之一,在in()
類型的子查詢中,當子查詢傳回的可能有重複記錄時,就可能出現這個
總的來說,我們只需要專注在結果中的幾列:
列名 | 備註 |
---|---|
type | 本次查詢表聯結類型,從這裡可以看到本次查詢大概的效率 |
key | 最終選擇的索引,如果沒有索引的話,本次查詢效率通常很差 |
key_len | 本次查詢用於結果篩選的索引實際長度 |
rows | 預計需要掃描的記錄數,預計需要掃描的記錄數越小越好 |
#Extra | 額外附加資訊,主要確認是否出現Using filesort 、Using temporary 這兩種情況 |
再來看下Extra
列中需要注意出現的幾種情況:
關鍵字 | 備註 |
---|---|
Using filesort | 將用外部排序而不是按照索引順序排列結果,資料較少時從記憶體排序,否則需要在磁碟完成排序,代價非常高,需要添加合適的索引 |
#Using temporary | 需要建立一個暫存表來儲存結果,這通常發生在對沒有索引的資料列進行GROUP BY 時,或 ORDER BY 裡的列不都在索引裡,需要加入適當的索引
|
Using index | 表示MySQL 使用覆蓋索引避免全表掃描,不需要再到表中進行二次查找數據,這是比較好的結果之一。注意不要和type 中的index 類型混淆 |
#Using where | 通常是進行了全表/全索引掃描後再用WHERE 子句完成結果過濾,需要加入適當的索引
|
Impossible WHERE | ##對Where子句判斷的結果總是false而不能選擇任何數據,例如 where 1=0,無須過度關注
|
使用某些聚合函數來存取存在索引的某個欄位時,最佳化器會透過索引直接一次定位到所需的資料行完成整個查詢,例如 | MIN()\MAX (),這種也是比較好的結果之一
|
以上是一文帶你快速看懂 MySQL 執行計劃的詳細內容。更多資訊請關注PHP中文網其他相關文章!