快捷導(dǎo)航

輕松上手MYSQL之SQL優(yōu)化之Explain詳解

更新時(shí)間：2024年06月25日 09:15:54 作者：danci_btq

Explain是SQL分析工具中非常重要的一個(gè)功能,它可以模擬優(yōu)化器執(zhí)行查詢語(yǔ)句,幫助我們理解查詢是如何執(zhí)行的,這篇文章主要給大家介紹了關(guān)于輕松上手MYSQL之SQL優(yōu)化之Explain詳解的相關(guān)資料,需要的朋友可以參考下

一、Explain

1.1 explain作用

在sql語(yǔ)句前添加explain，作用是查看mysql對(duì)這條sql的執(zhí)行計(jì)劃信息。

思考：MYSQL執(zhí)行SQL語(yǔ)句時(shí)一定按這個(gè)執(zhí)行計(jì)劃執(zhí)行么？

1.2 explain列說(shuō)明

在一條簡(jiǎn)單SQL前面添加explain查看有哪些列，如下：

在這里插入圖片描述

id

每個(gè)select對(duì)應(yīng)一個(gè)id值，其值是按 select 出現(xiàn)的順序增長(zhǎng)的。

注：id值越大執(zhí)行優(yōu)先級(jí)越高，id相同則從上往下執(zhí)行，id為NULL最后執(zhí)行

select_type

每個(gè)select對(duì)應(yīng)一個(gè)select_type，表示select的復(fù)雜度，有：

SIMPLE：簡(jiǎn)單查詢。查詢不包含子查詢和union，如上圖

PRIMARY：對(duì)于包含UNION、UNION ALL或者子查詢的大查詢來(lái)說(shuō)，它是由幾個(gè)小查詢組成的，其中最左邊的那個(gè)查詢的select_type值就是PRIMARY
SUBQUERY：包含在 select 中的子查詢(不在 from 子句中)
DERIVED：對(duì)于包含‘派生表’的查詢
UNION：在 union 中的第二個(gè)和隨后的 select

table

這一列表示 explain 的一行正在訪問(wèn)哪個(gè)表。
當(dāng) from 子句中有子查詢時(shí)，table列是 <derivenN> 格式，表示當(dāng)前查詢依賴 id=N 的查詢，于是先執(zhí)行 id=N 的查詢。
當(dāng)有 union 時(shí)，UNION RESULT 的 table 列的值為<union1,2>，1和2表示參與 union 的 select 行id。

partiitons

匹配的分區(qū)信息

type

這一列表示關(guān)聯(lián)類(lèi)型或訪問(wèn)類(lèi)型
效率從最優(yōu)到最差分別為:system > const > eq_ref > ref > range > index > ALL
SQL性能優(yōu)化的目標(biāo)：至少要達(dá)到range級(jí)別，要求是ref級(jí)別，最好是consts級(jí)別。
system：當(dāng)表中只有一條記錄并且該表使用的存儲(chǔ)引擎的統(tǒng)計(jì)數(shù)據(jù)都是精確地，表最多有一個(gè)匹配行，讀取1次，速度比較快。
const：system是 const的特例，表里只有一條元組匹配時(shí)為system
eq_ref：primary key 或 unique key 索引的所有部分被連接使用，最多只會(huì)返回一條符合條件的記錄。
ref：相比 eq_ref，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前綴，索引要和某個(gè)值相比較，可能會(huì) 找到多個(gè)符合條件的行。
range：用索引獲取某些范圍區(qū)間的記錄。

select_type

每個(gè)select對(duì)應(yīng)一個(gè)select_type，表示select的復(fù)雜度
SIMPLE：簡(jiǎn)單查詢。查詢不包含子查詢和union，如上圖
PRIMARY：對(duì)于包含UNION、UNION ALL或者子查詢的大查詢來(lái)說(shuō)，它是由幾個(gè)小查詢組成的，其中最左邊的那個(gè)查詢的select_type值就是PRIMARY
SUBQUERY：包含在 select 中的子查詢(不在 from 子句中)
DERIVED：對(duì)于包含‘派生表’的查詢
UNION：在 union 中的第二個(gè)和隨后的 select

possible_keys

標(biāo)識(shí)某個(gè)表查詢時(shí)可能使用哪些索引來(lái)查找。

key

實(shí)際使用哪個(gè)索引。
當(dāng)possible_keys有值，而key沒(méi)有值時(shí)，可能是因?yàn)楸頂?shù)據(jù)很少，mysql認(rèn)為沒(méi)有必要走索引，直接全表查詢了。
當(dāng)possible_keys為null時(shí)，可根據(jù)實(shí)際情況在where條件中添加索引來(lái)提升查詢效率。

key_len（key_len值計(jì)算）

實(shí)際使用到的索引的字節(jié)數(shù)，幫我們檢查是否充分利用上了索引，對(duì)于聯(lián)合索引有一定的參考意義。

比如有列n和address的聯(lián)合索引（表my_datas字段有id, n, address 和 time）

key_len=5，通過(guò)計(jì)算索引占的字節(jié)數(shù)來(lái)判斷出查詢使用了聯(lián)合索引中的第一個(gè)列。

key_len的計(jì)算：（舉幾個(gè)類(lèi)型）

測(cè)試表test1

在這里插入圖片描述

字符串：char(n)和varchar(n)，5.0.3以后版本中，n均代表字符數(shù)，而不是字節(jié)數(shù)，如果是utf-8，一個(gè)數(shù)字或字母占1個(gè)字節(jié)，一個(gè)漢字占3個(gè)字節(jié)

char(n)：如果存漢字長(zhǎng)度就是 4n 字節(jié)（若可為空則+1）

   col1_char是char(4)，那么len應(yīng)該是 4*4 + 可為空1 = 17
   explain中key_len值為17

varchar(n)：如果存漢字則長(zhǎng)度是 4n + 2 字節(jié)（若可為空則+1），加的2字節(jié)用來(lái)存儲(chǔ)字符串長(zhǎng)度，因?yàn)?varchar是變長(zhǎng)字符串。
```
   col2_varchar是varchar(32)，那么len應(yīng)該是 32 * 4 + 2 + 1 = 131
   explain中key_len值為131
```

數(shù)值類(lèi)型：

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

時(shí)間類(lèi)型：

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：無(wú)小數(shù)秒位數(shù)，占5個(gè)字節(jié)。datetime(n) 其中n是保留的小數(shù)秒位數(shù)，額外占的存儲(chǔ)空間分別為
   n=0時(shí)      額外空間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 表，單列索引，最大長(zhǎng)度不能超過(guò) 1000 bytes，否則會(huì)報(bào)警，但是創(chuàng)建成功，最終創(chuàng)建的是前綴索引（取前333個(gè)字符）；
     - myisam 表，組合索引，索引長(zhǎng)度和不能超過(guò) 1000 bytes，否則會(huì)報(bào)錯(cuò)，創(chuàng)建失敗；
     - innodb 表，單列索引，超過(guò) 767 bytes的，給出warning，最終索引創(chuàng)建成功，取前綴索引（取前 255 字符）；
     - innodb 表，組合索引，各列長(zhǎng)度不超過(guò) 767 bytes ，如果有超過(guò) 767 bytes 的，則給出報(bào)警，索引最后創(chuàng)建成功， 但是對(duì)于超過(guò) 767 字節(jié)的列取前綴索引，與索引列順序無(wú)關(guān)，總和不得超過(guò) 3072 ，否則失敗，無(wú)法創(chuàng)建；

ref

    這一列顯示了在key列記錄的索引中，表查找值所用到的列或常量，常見(jiàn)的有:const(常量)、字段名（庫(kù)名.表名.列名 如test.test.col1_char)

rows

這一列是mysql估計(jì)要讀取并檢測(cè)的行數(shù)，值越小越優(yōu)
注意這個(gè)不是結(jié)果集里的行數(shù)

filtered

    通過(guò)索引掃描表估計(jì)要讀取并檢測(cè)的行數(shù)rows。
    使用額外的查詢條件對(duì)rows行的數(shù)據(jù)進(jìn)行過(guò)濾行得有行數(shù)n占rows的比例，
    即 n/rows * 100%

Extra

sql執(zhí)行計(jì)劃比較重要的參考信息，常見(jiàn)重要信息如下：

1 Using index：使用索引覆蓋

索引覆蓋：查詢的字段信息從這條sql使用的索引（輔助索引）樹(shù)中獲取。如：

查詢的字段是索引（col4_smallint）中的字段col4_smallint信息
也就是說(shuō)，不需要通過(guò)輔助索引找到主鍵，再通過(guò)主鍵樹(shù)獲取想要的信息

2 Using where：使用where查詢數(shù)據(jù)，需要回表去獲取需要的數(shù)據(jù)
3 Using index condition：相當(dāng)于索引覆蓋后通過(guò)主鍵回表查詢，再通過(guò)where過(guò)濾
4 Using temporary：創(chuàng)建一張臨時(shí)表來(lái)處理查詢
5 Using filesort：顧名思義，使用文件（磁盤(pán)中）排序。mysql做了優(yōu)化，數(shù)據(jù)較少時(shí)排序是在內(nèi)在中進(jìn)行的，數(shù)據(jù)量較大時(shí)才會(huì)在磁盤(pán)中進(jìn)行排序。出現(xiàn)這種情況，就要考慮添加索引來(lái)優(yōu)化SQL了。
col1_char 未創(chuàng)建索引，mysql先預(yù)覽整個(gè)表對(duì)col1_char進(jìn)行排序和對(duì)應(yīng)的主鍵值序列，再通過(guò)主鍵值回主鍵索引樹(shù)查詢數(shù)據(jù)返回。
對(duì) col1_char 添加索引之后，執(zhí)行計(jì)劃結(jié)果如下：
6 Select tables optimized away：使用函數(shù)來(lái)查詢某個(gè)索引信息時(shí)