面試被問:如何排查慢查詢(執行計劃)怎麼辦?愣著幹嘛?進來白嫖呀!

賜我白日夢 發表於 2021-07-22
面試

一、閱讀前

其實錄制了一個視訊專門講解這篇文章,視訊中講的比下文還全哦

面試被問:如何排查慢查詢(執行計劃)怎麼辦?愣著幹嘛?進來白嫖呀!

視訊連結:

https://mp.weixin.qq.com/s/wGcODm6gfy6JOvkcKp-8MA

https://mp.weixin.qq.com/s/wGcODm6gfy6JOvkcKp-8MA

Oow!

想白嫖explain實戰部分用到的建庫SQL、儲存過程等指令碼可以通過上面的視訊連結關注,後臺回覆:explain即可領取哦~

二、explain 實戰

2.1、初識執行計劃:

獲取sql執行計劃的語法:explain yoursql

explain select * from t1;

面試被問:如何排查慢查詢(執行計劃)怎麼辦?愣著幹嘛?進來白嫖呀!

欄位名 作用
id sql中的每一個select都有一個未對應的id,對子查詢來說,有多個select,就有多個id。
select_type sql執行計劃對應的查詢型別。如:
1.針對單表查詢或者是多表連線查詢的select type是simple。
2.union語句針對前半部分sql的select_type為primary,針對後半部分sql的select_type為union,做去重時的select_type是union result。3.sql中出現子查詢時,外層的select_type為primary,內層的select_type一般為subquery
table 你的sql要查詢哪個表
partitions 分割槽表
type 針對該表查詢時的查詢方式。如:
1.聚簇索引的const
2.二級索引的查詢ref(ref、eq_ref、ref_or_null)、range
3.對二級索引的全表掃描index
4.對聚簇索引的全表掃描的all
possible_keys 有哪些索引可以選擇
key 實際選擇的索引
key_len 索引的長度
ref 和上面選中key進行比較時,是等值匹配(const)還是其他的欄位(庫名.表名.列名)
rows 估算的可能會讀取的資料條數
filtered 過濾比例,真實資料*過濾比例為預計將讀取出來的資料
extra 額外的說明資料,如:
1.sql中有where條件時,Extra為:Using Where
2.sql中使用二級索引時,Extra為:Using Index
3.sql中有join語句多表關聯時:Extra為:Using join buffer(Block Nested Loop)
4.sql操作產生臨時表時,Extra為:Using temporary
5.sql需要根據某個欄位排序,且記憶體不夠時(不管是不是索引):Extra為:Using filesort

2.2、分析聯表SQL的執行計劃

SQL如下:

mysql> explain select * from t1 join t2;

面試被問:如何排查慢查詢(執行計劃)怎麼辦?愣著幹嘛?進來白嫖呀!

SQL執行時,會先將驅動表t1中的資料以全表掃描的方式檢索出來放在記憶體中,一共檢索4行。然後在將t2表中的資料檢索出來,和t1中的資料join在一起作為返回值。由於我們沒有加任何where條件,這裡還會存在一個笛卡爾積,也就是說結果中會有16條資料

執行計劃解析:

id:每一個select 關鍵字對應一個id,這條SQL中只有一個select,所以這兩行執行計劃的id都是1

select_type:均是simple 簡單的查詢方式。

table:查詢了哪張表

Partitions:分割槽

type:ALL表示全表掃描

possible_keys:可能使用到的索引,null表示,沒有任何索引 key:null表示實際上也沒有使用到索引

key_len:最長的索引的長度

ref:當你使用到索引時,索引列是等值匹配還是其他的連線方式,由於我們都沒有索引,所以直接為null

rows:估算的掃描行數

filterd:過濾的比例,實際數量*過濾比例 ≈ 本次查詢返回的行數

Extra:其他的資訊

2.3、分析子查詢SQL的執行計劃

SQL如下:

explain select * from t1 where x1 in (select x1 from t2) or x3 = 890;

面試被問:如何排查慢查詢(執行計劃)怎麼辦?愣著幹嘛?進來白嫖呀!

id:有兩個select 語言,所以執行計劃的id有兩個

table、partitions 不再贅述

select_type:第一行查詢語句的查詢型別是Primary,主查詢。第二條查詢SQL的型別是Subquery,自查詢。

對於主查詢而言,where條件中有or x3 = 123 (x3我們建立了索引)說明他有可用的索引:t1_x3_index 。但是它最終並沒有使用這個索引,它使用的全表掃描ALL的查詢方式。所以對應的key_len(最長的索引長度為null)。預估全表掃描出9987條資料。

主查詢之所以有x3索引卻不用,是因為MYSQL認為,使用這個索引和不用索引的層本擦不多。

對於自查詢來說,它的查詢型別是自查詢。它選擇使用t2表的x1這個二級索引,最長的索引長度為515byte,預估掃描14948條資料。

2.4、分析union SQL的執行計劃

# 聯合t1、t2並對union的結果進行去重!
explain select * from t1 union select * from t2;

面試被問:如何排查慢查詢(執行計劃)怎麼辦?愣著幹嘛?進來白嫖呀!

同樣執行計劃有有3行

針對t1的查詢為主查詢,因為sql中沒有任何查詢條件,所以Extra、possible_key、key都為NULL,並且預計全表掃描9987行資料。

第二行不再贅述。

第三行的查詢型別為:Union result,針對表<union1,2>進行操作,Extra中的Using tmporary表明這是一個臨時表。也就是說,結果集放到臨時表中進行去重。

2.5、分析複雜SQL的執行計劃

# 查詢x1列重複次數超過1次以上的x1列以及它的重複次數。
explain select * from (select x1,count(*) as cnt from t1 group by x1) as _t1 where cnt > 1;

面試被問:如何排查慢查詢(執行計劃)怎麼辦?愣著幹嘛?進來白嫖呀!

先看id為2的執行計劃,它是針對t1表的查詢,並且最終選擇索引:t1_x1_index。注意它的select_type是Derived表示派生,意思是它的查詢結果是會被物化成一個臨時表給外層的sql使用。

再看外層的id為1的SQL,它是型別為primary的主查詢,查詢的自查詢生成的臨時表<drived2> 查詢的方式是全表掃描。

2.6、常見的執行計劃的type

2.6.1、const

查詢聚簇索引

explain select * from t1 where id = 5000;

面試被問:如何排查慢查詢(執行計劃)怎麼辦?愣著幹嘛?進來白嫖呀!

查詢唯一的二級索引,執行計劃的type同樣是const

# t3.x1 是unique key
explain select * from t3 where x1 = 'qweqwe';

面試被問:如何排查慢查詢(執行計劃)怎麼辦?愣著幹嘛?進來白嫖呀!

2.6.2、ref

ref 對非唯一的二級索引進行檢索

mysql> explain select * from t1 where x1 = 'ucshiuhdaiusd';

面試被問:如何排查慢查詢(執行計劃)怎麼辦?愣著幹嘛?進來白嫖呀!

多個二級索引的等值匹配的type也是ref

mysql> explain select * from t1 where x1 = 'ucshiuhdaiusd' and x3 = 'qdasdsadas';

面試被問:如何排查慢查詢(執行計劃)怎麼辦?愣著幹嘛?進來白嫖呀!

使用普通索引的做join操作,被join的表的查詢type也是ref

mysql> explain select * from t1 inner join t2 on t1.x1 = t2.x1;

面試被問:如何排查慢查詢(執行計劃)怎麼辦?愣著幹嘛?進來白嫖呀!

2.6.3、eq_ref

eq_ref 使用主鍵進行join,被join的表的查詢type為eq_ref

mysql> explain select * from t1 inner join t2 on t1.id = t2.id;

面試被問:如何排查慢查詢(執行計劃)怎麼辦?愣著幹嘛?進來白嫖呀!

2.6.4、eq_or_null

對普通二級索引進行檢索,並且二級索引允許存在null的情況,那麼查詢計劃的type為eq_or_null

mysql> explain select * from t1 where x1 = 'ucshiuhdaiusd' or x1 is null;

面試被問:如何排查慢查詢(執行計劃)怎麼辦?愣著幹嘛?進來白嫖呀!

2.6.5、range

基於二級索引進行對一個範圍進行檢索,查詢型別為:range

mysql> explain select * from t1 where id > 5000;

面試被問:如何排查慢查詢(執行計劃)怎麼辦?愣著幹嘛?進來白嫖呀!

2.6.6、index

type 為 index 型別的執行計劃

# t3表中有3個索引,如下:
# id:聚簇索引
# x1:唯一的二級索引
# x1_x3_x2:聯合索引
explain select * from t3 where  x2 = 'fdc1a9f7d94ece2b68b7d3e3be1b0f3b';

可以看到,x2列沒有單獨的索引。但是sql的執行計劃選擇去聯合索引樹中掃全表,也不會去聚簇索引中全表掃描

面試被問:如何排查慢查詢(執行計劃)怎麼辦?愣著幹嘛?進來白嫖呀!

補充:索引的選擇邏輯

Case1: 比如現有索引:KEY(x1,x3)、KEY(x2,x4)

SQL如下:

select * from t where x1=xxx and x2>yyy;

那麼問題來了,x1、x2都有對應的索引,那MYSQL該如何選擇索引呢?

一般來說MYSQL會優先選擇一個掃描行數少的KEY,作為最終的索引,比如x1是等值條件,x2是range條件,所以最終大概率是通過x1索引查詢一次取id,再去回表找到x2列,使用x2>yyy的條件進行過濾。

Case2:在執行SQL語句時有可能會同時查詢多個索引。

比如:現有索引:KEY(x1)、KEY(x2)

SQL如下:

select * from table where x1=xxx and x2=yyy;

那它的執行計劃可能就是:先從x1索引數中取出x1=xxx的資料行,再從x2的索引樹中取出x2=yyy的資料行,這兩部分資料根據主鍵進行一次交集,再使用intersection交集後的結果去聚簇索引中回表。

之前的例子中說的是,在x1和x2這兩個索引中優先選一個掃描行數少的索引,先使用它查詢,在拿著查詢到的結果去回表。

那,之所以示例9中的情況會出現,是因為可能存在如下的情況:

(掃描x1的行數+掃描x2的行數)+ merge之後的行數 < 掃描x1或者x2的最少行數+回表的行數

此外:如果想讓一個SQL使用多個索引,也有硬性的條件:

  1. 如果使用聯合索引,那聯合索引中的每一個欄位都需要出現在sql中,且必須是等值匹配。
  2. 通過主鍵查詢+其他二級索引的等值匹配,也可能做一次多索引查詢做交集後再回表。

補充:如果你的SQL如下:

select * from table where x1=xxx or x2=yyy;

依然有可能使用多個索引,然後對多個索引的結果取union並集

Case3:如下SQL

select * from t1,t2 where t1.x1 = xxx and t1.x2 = t2.x2 and t2.x3 = yyy;

sql中的有t1、t2兩個表,前面的t1為驅動表,後面的t2為被驅動表。

sql中的t1.x2 = t2.x2為關聯條件。

SQL在執行時會先使用t1.x1=xxx為條件先撈出一部分資料,此次操作會根據x1的索引情況而不同,可能是const、ref、index或者全表掃描all。

假設第一次根據x1=xxx條件找到了2行資料(稱這兩行資料為A)。接下來會分別拿著這兩行資料中的x2為新的條件,去t2表中查詢,假設第一行資料的x2列值為123。他就會使用t2.x2=123 and t2.x3=yyy為新的條件在t2表找到匹配到的資料,和第一行資料關聯起來。

補充:

這種聯表查詢的方式也叫做內連線,比如t2中有3行符合x2=123 ,那麼它們都會被和A中的第一行拼接起來,作為最終的返回值。

如果t2表中沒有找到t2.x2=123的列,那麼A中的第一行資料也不會被保留作為最終的返回值。

那如果不想因為t2.x2中沒有符合條件的列也將A中的第一行保留下來,那麼可以使用外連線實現。如 left join on + 條件

接著處理A中的第二行資料。

2.7、詳解ref列

Case1 ref之: 庫名.表名.列名

explain select * from t1 inner join t2 on t1.id = t2.id;

面試被問:如何排查慢查詢(執行計劃)怎麼辦?愣著幹嘛?進來白嫖呀!

當執行第二個查詢計劃時,對主鍵ID進行等值匹配,而且是使用test.t1.id 來和ID進行等值匹配(而不是某個常量)

Case2 ref之:const

explain select * from t1 where x1 = 'qwdasdas';

面試被問:如何排查慢查詢(執行計劃)怎麼辦?愣著幹嘛?進來白嫖呀!

2.8、Extra 列

關於這一列上面其實提到過了

1、Using where

explain select * from t1 where x1 = 'ucshiuhdaiusd' and x3 = 'qdasdsadas';

2、Using Index

explain select  x1  from t1;

3、Using filesort ,其中的x1是二級索引,x2是普通索引。

面試被問:如何排查慢查詢(執行計劃)怎麼辦?愣著幹嘛?進來白嫖呀!

4、Using temporary,表示:使用了中間表

Notice,當你的sql中針對非索引的列進行 group by、distinct、union操作時,它都會通過一張中間表去完成指定操作。而如果對索引列進行group by、distinct、union時,會直接使用索引完成我們的操作。

Case1:

# x1是二級索引
mysql> explain select distinct x1  from t1;
# x2是普通列
mysql> explain select distinct x2  from t1;

面試被問:如何排查慢查詢(執行計劃)怎麼辦?愣著幹嘛?進來白嫖呀!

Case2:

# x1是索引列
mysql> explain select x1,count(*)  from t1 group by x1;
# x2是普通列

explain select x2,count(*)  from t1 group by x2;

面試被問:如何排查慢查詢(執行計劃)怎麼辦?愣著幹嘛?進來白嫖呀!

三、成本計算

SQL的執行是有成本的,比如讀取磁碟的操作遠比記憶體中的操作高的多。而執行計劃的作用就是選擇一個低成本執行方式去執行我們的SQL。

下面看一種簡單的計算SQL執行成本的方式:

tablestatus

通過如上面的命令可以得到mysql替我們維護的表的統計資料。

其中的rows並不準確,為估算值。data_length/1024/16 為資料頁的數量。

全表掃描的成本計算方法如下:

SQL的執行成本 約為 資料頁的數量*1.0 + 資料行數*0.2

四、MySQL會改寫你的SQL

如果MYSQL認為你的SQL寫的不是很好,它會改寫優化你的SQL,目的是為了更好的分析得到最優執行計劃。

Case1: mysql會去除sql中多餘的括號

Case2: 常量替換,如果 `where i=5 and jCase3: 對於如下SQL:

select * from t1 join t2 where t1.x1 = t2.x2 and t1.id = 1

比如t1表中有id、x1、x2、x3四列

MySQL的可能按照如下的順序執行

1.反正t1表中就有1行資料符合預期,那先執行如下SQL,將資料查詢出來
select * from t1 where id = 1;

比如查詢結果如下
id	x1	x2	x3
1		2		3		4

2.使用第一步中查詢出來的值,當做常量替換、改寫原始sql
select 1,2,3,4,t2.* from t1 join t2 where t2.x2 = 2;

Case4: 如下SQL:

select * from t1 where x1 = (select x1 from t2 where id = xxx);

MySQL執行該SQL時,首先會執行子查詢語句,先根據id找到一條資料,取出其x1的值,作為外層sql的常量。然後再執行外層SQL

Case5: ```bash select * from t1 where x1 = (select x1 from t2 where t2.x2 = t1.x2); ```

子查詢中的條件又依賴t1表的x2列的值,所以想執行該SQL,就得先遍歷t1表,將每一行t1中的x2的值放入到子查詢中作為條件,得到子查詢的x1後再當做常量作為外層查詢的條件。

Case6: 對in語句的優化,如下SQL:

select * from t1 where x1 in (select x1 from t1 where x2 = xxx);

假設t1、t2中各有10萬條資料。

MySQL可能會將SQL的執行流程優化成下面這樣:

Step1:先執行子查詢語句,假設我們從10w條資料中過濾出500條。

Step2:基於memory儲存引擎,再記憶體中,將過濾出的500條資料寫入臨時表,也叫做物化表,併為他建立索引。如果資料很多記憶體不夠用,也可能以普通B+Tree的方式將其放在磁碟中。

Step3:遍歷這個臨時表,並將遍歷出的x1的值作為條件放在外層sql中,這樣就避免了遍歷外層sql時需要的10萬次遍歷了!