輕松上手MYSQL之SQL優(yōu)化之Explain詳解
一、Explain
1.1 explain作用
在sql語句前添加explain，作用是查看mysql對這條sql的執(zhí)行計劃信息。
思考：MYSQL執(zhí)行SQL語句時一定按這個執(zhí)行計劃執(zhí)行么？
1.2 explain列說明
在一條簡單SQL前面添加explain查看有哪些列，如下：
id
每個select對應(yīng)一個id值，其值是按 select 出現(xiàn)的順序增長的。
注：id值越大執(zhí)行優(yōu)先級越高，id相同則從上往下執(zhí)行，id為NULL最后執(zhí)行
select_type
每個select對應(yīng)一個select_type，表示select的復(fù)雜度，有：
SIMPLE：簡單查詢。查詢不包含子查詢和union，如上圖
PRIMARY：對于包含UNION、UNION ALL或者子查詢的大查詢來說，它是由幾個小查詢組成的，其中最左邊的那個查詢的select_type值就是PRIMARY
SUBQUERY：包含在 select 中的子查詢(不在 from 子句中)
DERIVED：對于包含‘派生表’的查詢
UNION：在 union 中的第二個和隨后的 select
table
這一列表示 explain 的一行正在訪問哪個表。
當(dāng) from 子句中有子查詢時，table列是 <derivenN> 格式，表示當(dāng)前查詢依賴 id=N 的查詢，于是先執(zhí)行 id=N 的查 詢。
當(dāng)有 union 時，UNION RESULT 的 table 列的值為<union1,2>，1和2表示參與 union 的 select 行id。
partiitons
匹配的分區(qū)信息
type
這一列表示關(guān)聯(lián)類型或訪問類型
效率從最優(yōu)到最差分別為:system > const > eq_ref > ref > range > index > ALL
SQL性能優(yōu)化的目標(biāo)：至少要達(dá)到range級別，要求是ref級別，最好是consts級別。
system：當(dāng)表中只有一條記錄并且該表使用的存儲引擎的統(tǒng)計數(shù)據(jù)都是精確地，表最多有一個匹配行，讀取1次，速度比較快。
const：system是 const的特例，表里只有一條元組匹配時為system
eq_ref：primary key 或 unique key 索引的所有部分被連接使用 ，最多只會返回一條符合條件的記錄。
ref：相比 eq_ref，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前綴，索引要和某個值相比較，可能會 找到多個符合條件的行。
range：用索引獲取某些范圍區(qū)間的記錄。
select_type
每個select對應(yīng)一個select_type，表示select的復(fù)雜度
SIMPLE：簡單查詢。查詢不包含子查詢和union，如上圖
PRIMARY：對于包含UNION、UNION ALL或者子查詢的大查詢來說，它是由幾個小查詢組成的，其中最左邊的那個查詢的select_type值就是PRIMARY
SUBQUERY：包含在 select 中的子查詢(不在 from 子句中)
DERIVED：對于包含‘派生表’的查詢
UNION：在 union 中的第二個和隨后的 select
possible_keys
標(biāo)識某個表查詢時可能使用哪些索引來查找。
key
實(shí)際使用哪個索引。
當(dāng)possible_keys有值，而key沒有值時，可能是因?yàn)楸頂?shù)據(jù)很少，mysql認(rèn)為沒有必要走索引，直接全表查詢了。
當(dāng)possible_keys為null時，可根據(jù)實(shí)際情況在where條件中添加索引來提升查詢效率。
key_len（key_len值計算）
實(shí)際使用到的索引的字節(jié)數(shù)，幫我們檢查是否充分利用上了索引，對于聯(lián)合索引有一定的參考意義。
比如有列n和address的聯(lián)合索引（表my_datas字段有id, n, address 和 time）
key_len=5，通過計算索引占的字節(jié)數(shù)來判斷出查詢使用了聯(lián)合索引中的第一個列。
key_len的計算：（舉幾個類型）
測試表test1
字符串：char(n)和varchar(n)，5.0.3以后版本中，n均代表字符數(shù)，而不是字節(jié)數(shù)，如果是utf-8，一個數(shù)字或字母占1個字節(jié)，一個漢字占3個字節(jié)
char(n)：如果存漢字長度就是 4n 字節(jié)（若可為空 則+1）
col1_char是char(4)，那么len應(yīng)該是 4*4 + 可為空1 = 17 explain中key_len值為17
varchar(n)：如果存漢字則長度是 4n + 2 字節(jié)（若可為空 則+1），加的2字節(jié)用來存儲字符串長度，因?yàn)?varchar是變長字符串。
col2_varchar是varchar(32)，那么len應(yīng)該是 32 * 4 + 2 + 1 = 131 explain中key_len值為131
數(shù)值類型：
tinyint：1字節(jié)（若可為空 則+1）
col3_tinyint是tinyint，那么len應(yīng)該是 1+可為空1 = 2 explain中key_len值為2
smallint：2字節(jié)（若可為空 則+1）
col4_smallint是smallint，那么len應(yīng)該是 2+可為空1 = 3 explain中key_len值為3
int：4字節(jié)（若可為空 則+1）
col5_int是int，那么len應(yīng)該是 4+可為空1 = 5 explain中key_len值為5
bigint：8字節(jié) （若可為空 則+1）
col6_bigint是bigint，那么len應(yīng)該是 8+可為空1 = 9 explain中key_len值為9
時間類型：
date：3字節(jié)（若可為空 則+1）
col7_date是date，那么len應(yīng)該是 3 + 可為空1 = 4 explain中key_len值為4
timestamp：4字節(jié)（若可為空 則+1）
col8_timestamp是timestamp，那么len應(yīng)該是 4+可為空1 = 5 explain中key_len值為5 datetime：無小數(shù)秒位數(shù)，占5個字節(jié)。datetime(n) 其中n是保留的小數(shù)秒位數(shù)，額外占的存儲空間分別為 n=0時 額外空間0字節(jié) n=1(或2) 額外空間1字節(jié) n=3(或4) 額外空間2字節(jié) n=5(或6) 額外空間3字節(jié) col9_datetime是datetime，那么len應(yīng)該是 5+可為空1 = 6 explain中key_len值為6
注：
- myisam 表，單列索引，最大長度不能超過 1000 bytes，否則會報警，但是創(chuàng)建成功，最終創(chuàng)建的是前綴索引（取前333個字符）；
- myisam 表，組合索引，索引長度和不能超過 1000 bytes，否則會報錯，創(chuàng)建失敗；
- innodb 表，單列索引，超過 767 bytes的，給出warning，最終索引創(chuàng)建成功，取前綴索引（取前 255 字符）；
- innodb 表，組合索引，各列長度不超過 767 bytes ，如果有超過 767 bytes 的，則給出報警，索引最后創(chuàng)建成功， 但是對于超過 767 字節(jié)的列取前綴索引，與索引列順序無關(guān)，總和不得超過 3072 ，否則失敗，無法創(chuàng)建；
ref
這一列顯示了在key列記錄的索引中，表查找值所用到的列或常量，常見的有:const(常量)、字段名（庫名.表名.列名 如test.test.col1_char)
rows
這一列是mysql估計要讀取并檢測的行數(shù)，值越小越優(yōu)
注意這個不是結(jié)果集里的行數(shù)
filtered
通過索引掃描表估計要讀取并檢測的行數(shù)rows。
使用額外的查詢條件對rows行的數(shù)據(jù)進(jìn)行過濾行得有行數(shù)n占rows的比例，
即 n/rows * 100%
Extra
sql執(zhí)行計劃比較重要的參考信息，常見重要信息如下：
1 Using index：使用索引覆蓋
索引覆蓋：查詢的字段信息從這條sql使用的索引（輔助索引）樹中獲取。如：
查詢的字段是索引（col4_smallint）中的字段col4_smallint信息
也就是說，不需要通過輔助索引找到主鍵，再通過主鍵樹獲取想要的信息
- 2 Using where：使用where查詢數(shù)據(jù)，需要回表去獲取需要的數(shù)據(jù)
- 3 Using index condition：相當(dāng)于索引覆蓋后通過主鍵回表查詢，再通過where過濾
- 4 Using temporary：創(chuàng)建一張臨時表來處理查詢
- 5 Using filesort：顧名思義，使用文件（磁盤中）排序。mysql做了優(yōu)化，數(shù)據(jù)較少時排序是在內(nèi)在中進(jìn)行的，數(shù)據(jù)量較大時才會在磁盤中進(jìn)行排序。出現(xiàn)這種情況，就要考慮添加索引來優(yōu)化SQL了。
col1_char 未創(chuàng)建索引，mysql先預(yù)覽整個表對col1_char進(jìn)行排序和對應(yīng)的主鍵值序列，再通過主鍵值回主鍵索引樹查詢數(shù)據(jù)返回。
對 col1_char 添加索引之后，執(zhí)行計劃結(jié)果如下：
6 Select tables optimized away：使用函數(shù)來查詢某個索引信息時
總結(jié)
到此這篇關(guān)于輕松上手MYSQL之SQL優(yōu)化之Explain詳解的文章就介紹到這了,更多相關(guān)SQL優(yōu)化Explain內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
這篇文章主要介紹了刪除MySQL中所有表的外鍵的兩種方法,文中通過代碼示例講解的非常詳細(xì),對大家的學(xué)習(xí)或工作有一定的幫助,需要的朋友可以參考下2024-05-05
MySQL INNER JOIN 的底層實(shí)現(xiàn)原理分析
這篇文章主要介紹了MySQL INNER JOIN 的底層實(shí)現(xiàn)原理,INNER JOIN的工作分為篩選和連接兩個步驟，連接時可以使用多種算法，通過本文，我們深入了解了MySQL中INNER JOIN的底層實(shí)現(xiàn)原理，需要的朋友可以參考下2023-06-06
MySql三種避免重復(fù)插入數(shù)據(jù)的方法
這篇文章主要介紹了MySql三種避免重復(fù)插入數(shù)據(jù)的方法，幫助大家更好的理解和使用MySQL數(shù)據(jù)庫，感興趣的朋友可以了解下2020-09-09
多個字段拼接去重是指將多個字段的值按照一定的規(guī)則進(jìn)行拼接,并去除重復(fù)的拼接結(jié)果,本文主要介紹了MySQL中使用distinct單、多字段去重方法,感興趣的可以了解一下2024-05-05
MySQL數(shù)據(jù)據(jù)庫中我們經(jīng)常使用SQL SELECT語句來查詢數(shù)據(jù)，那么關(guān)于它的執(zhí)行順序，下面小編來帶大家簡單了解一下2019-05-05
mysql 8.0.15 版本安裝教程 連接Navicat.list
這篇文章主要為大家詳細(xì)介紹了mysql 8.0.15 版本安裝教程，連接Navicat.list，具有一定的參考價值，感興趣的小伙伴們可以參考一下2019-08-08
這篇文章主要介紹了SQL慢查詢優(yōu)化方案詳解,如果你的項目中出現(xiàn)了一些查詢超時情況，很可能是項目中有了一些慢查詢的情況產(chǎn)生，下面就慢查詢的排查和解決方案進(jìn)行一番分析,需要的朋友可以參考下2023-07-07
這篇文章主要為大家詳細(xì)介紹了win10下mysql 8.0.23 安裝配置方法圖文教程，文中安裝步驟介紹的非常詳細(xì)，具有一定的參考價值，感興趣的小伙伴們可以參考一下2021-01-01
正則表達(dá)式(REGEXP)與通配符(LIKE)的超詳細(xì)對比
正則表達(dá)式和通配符有許多相似的地方,但它們作用、用法、格式有許多差別,這篇文章主要介紹了正則表達(dá)式(REGEXP)與通配符(LIKE)的超詳細(xì)對比,文中通過代碼介紹的非常詳細(xì),需要的朋友可以參考下2025-07-07
mysql千萬級數(shù)據(jù)分頁查詢性能優(yōu)化
本文給大家分享的是作者在使用mysql進(jìn)行千萬級數(shù)據(jù)量分頁查詢的時候進(jìn)行性能優(yōu)化的方法，非常不錯的一篇文章，對我們學(xué)習(xí)mysql性能優(yōu)化非常有幫助2017-11-11