explain 讓你的 sql 寫的更踏實

本文主要通過一些實例介紹如何使用 mysql 中的 explain 關鍵字分析查詢語句,好讓我們的查詢語句寫的更踏實,也讓我們養成用 explain 分析的習慣,養成查詢語句的寫法習慣。

概念介紹

在 mysql 里, explain 是執行計劃的意思,即可以通過該命令看出 mysql 是如何執行該條 sql 的,可以通過分析索引,表結構等方面來優化你的慢查詢語句。

mysql 使用 explain + sql 語句 來查看執行計劃,執行結果有十個字段,具體描述如下:

字段 描述

id

id相同,執行順序由上至下;id不同,id的序號會遞增,id值越大優先級越高,越先被執行

select_type

主要是用于區別普通查詢、聯合查詢、子查詢等的復雜查詢

table

當前執行的表

type

訪問類型

possible_keys

可能使用的索引

key

實際使用的索引

key_len

使用的索引的長度

ref

顯示索引的哪一列被使用了

rows

查詢過程中可能掃描的行數

Extra

解析查詢的額外信息,通常會顯示是否使用了索引,是否需要排序,是否會用到臨時表等

其中 type 字段可選值如下,性能從低到高排列:

type 說明

ALL

全數據表掃描

index

全索引表掃描

RANGE

對索引列進行范圍查找

INDEX_MERGE

合并索引,使用多個單列索引搜索

REF

根據索引查找一個或多個值

EQ_REF

搜索時使用primary key 或 unique類型

CONST

常量,表最多有一個匹配行,因為僅有一行,在這行的列值可被優化器剩余部分認為是常數,const表很快,因為它們只讀取一次

SYSTEM

系統,表僅有一行(=系統表)。這是const聯接類型的一個特例

OK,概念匆匆介紹之后,結合自己的分析習慣,下面會通過實例聚焦 typekeyrowsExtra 這幾個字段,來介紹如何分析我們的查詢語句。

實例分析

數據初始化

新建測試表,插入 10 w 數據:

CREATE TABLE `test` (  
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `a` int(11) NOT NULL,
  `b` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

-- 批量插入 10w 數據
-- DROP PROCEDURE IF EXISTS batchInsert
DELIMITER $  
CREATE PROCEDURE batchInsert () BEGIN DECLARE i INT DEFAULT 1;  
START TRANSACTION; WHILE i<=100000  
DO  
INSERT INTO test (a,b) VALUES (i,i);  
SET i=i+1; END WHILE;  
COMMIT; END $  
CALL batchInsert ();

全表查詢

目前默認只有一個主鍵索引,我們分析下全表查詢:

mysql> explain select * from test;
id select_type table type possible_keys key key_len ref rows Extra

1

SIMPLE

test

ALL

NULL

NULL

NULL

NULL

100333

NULL

其中 type 值為 ALL,表示全表掃描了,我們看到 rows 這個字段顯示有 100332 條,實際上我們一共才 10w 條數據,說明這個字段只是 mysql 的一個預估,不總是準確的。這個 test 表一次真實的查詢時間為:2.708000s,可見這種全表掃描的效率非常低,是需要被優化的。

索引查詢

接下來我們分別給字段 a 和 b 添加普通索引。

mysql> alter table test add index idx_a(a);  
mysql> alter table test add index idx_b(b);

看下下面這條 sql:

mysql> explain select * from test where a > 10000;
id select_type table type possible_keys key key_len ref rows Extra

1

SIMPLE

test

ALL

idx_a

NULL

NULL

NULL

100333

Using where

我們發現 type 竟然不是 index, 剛剛不是給字段 a 添加索引了么?還有 possible_keys 也顯示了有 a_index,但是 key 顯示 null,表示實際上不會使用任何索引,這是為什么呢?

這是因為 select * 的話還需要回到主鍵索引上查找 b 字段,這個過程叫 回表

這條語句會從索引中查出 9w 條數據,也就是說這 9w 條數據都需要 回表 操作,全表掃描都才 10w 條數據,所以在 mysql 最后的決策是還不如直接全表掃描得了,至少還免去了回表過程了。

當然,最后決策是否用索引不是固定的,mysql 會比較各種查詢的代價,我們把上面的 sql 中 where 條件再稍微改造一下。

mysql> explain select * from test where a > 90000;
id select_type table type possible_keys key key_len ref rows Extra

1

SIMPLE

test

range

idx_a

idx_a

4

NULL

9999

Using index condition

再看這次 type 為 range 了, key 為 a_index,表示使用了 a 索引,如我們所愿了。這是因為滿足這次索引中查出只有 10000 條數據,mysql 認為 10000 條數據就算回表也要比全表掃描的代價低,因而決定查索引。

上面兩條查詢說明 mysql 會比較 索引 + 回表直接全表掃描 的查詢性能,選擇其中更好的作為最后的查詢方式,這就是 mysql 優化器的作用了。

還有一點就是這次 Extra 字段中值為 Using index condition,這是指條件過濾的時候用到了索引,但因為是 select * ,所以還是需要回表,再看下面這個語句。

mysql> explain select a from test where a > 90000;
id select_type table type possible_keys key key_len ref rows Extra

1

SIMPLE

test

range

idx_a

idx_a

4

NULL

9999

Using where; Using index

注意這次 Extra 的值為 Using where; Using index,表示查詢用到了索引,且要查詢的字段在索引中就能拿到,所以不需要回表,顯然這種效率比上面的要高,這也是日常開發中不建議寫 select * 的原因,盡量只查詢業務所需的字段。

排序查詢

再來看一個帶排序的查詢。

mysql> explain select a from test where a > 90000 order by b;
id select_type table type possible_keys key key_len ref rows Extra

1

SIMPLE

test

range

idx_a

idx_a

4

NULL

9999

Using index condition; Using filesort

我們知道索引本來就是有序帶,但這個 Extra 中返回了一個 Using filesort,說明無法利用索引完成排序,需要從內存或磁盤進行排序,具體哪種排序 explain 是沒有體現的。 總之,這種情況也是需要優化的,盡量能利用索引的有序性,比如下面:

mysql> explain select a from test where a > 90000 order by a;

id select_type table type possible_keys key key_len ref rows Extra

1

SIMPLE

test

range

idx_a

idx_a

4

NULL

9999

Using where; Using index

這次 Extra 值有 Using index 了,表示使用上了索引。

復合索引

我們再創建一個復合索引看看。

mysql> alter table test add index idx_a_b(a,b);

看下之前的查詢 mysql> explain select * from test where a > 10000;

id select_type table type possible_keys key key_len ref rows Extra

1

SIMPLE

t

range

idx a,idx a_b

idx a b

4

NULL

50166

Using where; Using index

這條 sql 剛剛在沒有創建復合索引的時候,是走的全表掃描,現在看 Extra 有 Using index,說明利用了覆蓋索引,同樣也免去了回表過程,即在 idx a b 索引上就能找出要查詢的字段。

總結

本文通過幾個實例介紹了如何使用 explain 來分析一條 sql 的查詢計劃,例子都很簡單,旨在能通俗易懂的說明白一些常見的查詢問題,也讓我們能養成良好的查詢習慣。

參考

https://dev.mysql.com/doc/refman/5.7/en/explain-output.html#explain_rows https://blog.csdn.net/poxiaonie/article/details/77757471 https://www.cnblogs.com/tufujie/p/9413852.html https://www.xttblog.com/?p=4225

我來評幾句
登錄后評論

已發表評論數()

相關站點

熱門文章
贵州11选5走势图软件