MySQL: 喂，別走，聽我解釋一下好嗎?
MySQL：先別跑好嗎?
程序員：不跑你養我啊?
MySQL：你聽我給你「解釋」啊。
程序員：你先管好你自己吧。
現在的互聯網應用也好，傳統應用也罷，不管開發語言是什么，涉及到關系型數據庫，MySQL 基本都是不二之選。
在 Java 的開發框架里， MyBatis 可以自定義 SQL，ORM/JPA 會根據對象映射，自動生成一系列相關的 SQL。至于自動生成的SQL，甚至自定義的SQL 執行效率到底高不高，不用 select *, 增加 where 條件，避免查全量數據等等，我們開發人員可能基本都是根據現有的知識儲備，在大腦里過濾一遍，至于到底 SQL 是否高效執行，卻從來沒有去問過MySQL 一句。
而實際上，SQL語句寫完，這項工作完成了還不到一半，其余還需要評估我們SQL寫的質量如何，執行效率夠不夠高。MySQL 像一位智者一樣，一直待在那里，只要你問，知無不言。重點在于，你不要急著走，要聽他解釋一下。
Explain
在 MySQL 中，我們一般常用 desc tableName 來查看一張表的信息，各個列的定義等、通過 Explain SQL 來了解MySQL 是如何執行當前這條SQL的 。
實際上 desc、describe、explain 都可以用來查看MySQL 是如何執行當前這條SQL的，在 MySQL 8.0.19 之后，這三者的作用可以說是等價的，explain 也可以用來查看表信息。后面我們會直接以 explain 為例，來說明具體的作用。
官方文檔說的明白， explain 可以和SELECT、INSERT、UPDATE、DELETE 一起工作，顯示 MySQL 優化器的語句執行計劃，即用來告訴用戶 MySQL 會怎樣執行這條 SQL，以什么樣的順序，如果是多表的話是怎樣 Join的。
輸出字段官網文檔截圖如下：
上面返回看似不少，不過我們重點關注 type、key、rows 這三個。
我們常見應用的場景都是讀多寫少，而且對于 SQL 的執行的效率評估，一般也是說從已經存儲的十成、百萬甚至千萬條數據中查詢需要數據的效率。
后面以 SELECT 為例，來看看 explain 能帶給我們什么幫忙和建議。
假設有如下表定義及數據：
- CREATE TABLE `t3` (
- `id` int NOT NULL,
- `a` int DEFAULT NULL,
- `b` int DEFAULT NULL,
- PRIMARY KEY (`id`),
- KEY `a` (`a`)
- ) ENGINE=InnoDB;
- delimiter ;;
- create procedure idata()
- begin
- declare i int;
- set i=1;
- while(i<=100000) do
- insert into t3 values(i,i,i);
- set i=i+1;
- end while;
- end;;
- delimiter ;
- call idata();
執行完上述SQL，我們來試想一下，在當前十萬行數據的表中如果執行一條查詢SQL，那在少量數據中查找一定比全表查找要快很好。
比如我們最熟悉的通過主鍵查詢
- select a from t3 where id=100;
你會發現，explain 中 type 是 const， key 是 PRIMARY
再比如執行
- select * from t3 where b=100;
這個時候， explain 告訴我們，查詢類型是ALL，全表掃描：
如果我們是想要把這個表里全部數據顯示也就罷了，目前只查一條數據卻執行全表掃描，explain 告訴我們掃描行可能會到9萬多行，效率可想而知。
如果我們把SQL 改成這樣：
- select * from t3 where a=100;
此時 explain 變成了這樣：
你會發現，type 變成了 ref， key 變成了a, rows 是1， 區別只在于 a 列上建立了索引，此時掃描行數變成了一行，差別太明顯了。
如果我們要查找一個范圍內的數據，通過主鍵或者包含索引的列進行查詢時，
掃描的還是有限行，此時type是range，但如果還是通過 b 做為條件進行過濾，那還是全表掃描：
另外，為什么一般的SQL優化建議里都會說別用 select * ,指定具體用到的列呢?
肯定是用到什么列的數據查什么數據更節省內存，傳輸，不要等到查出結果再在內存里進行過濾，此外更重要的一點是，每個創建的索引，都有自己的索引樹，能夠在索引樹上完成查詢操作，就不需要再回表去查詢，效率當然會更高。
比如，我們把查詢換成了
- select a,id from t3 where a < 100;
此時，explain 會在Extra里告訴咱們，查詢的時候沒有回表，用到了index
如果把查詢列改成星，這個時候，就需要回表了，
咱們前面說重點關注 type， key， rows，可以再看下 Extra， type 里查詢效率從優到差，有
const
表中只有一行匹配，查詢一次即可滿足。常用來匹配主鍵或者唯一索引。
eq_ref
唯一索引
ref
非唯一索引
range
通過一個索引去查詢，只掃描指定范圍內的行。一般是在檢索列中包含 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, LIKE, or IN()
index
類似于全表掃描，區別在于只掃描索引樹
all
全表掃描，效率最低的。
在 MySQL 8.0.18 開始，還加入了一個 explain analyze，可以查看具體預計SQL執行耗時，比如我們通過它來查看上面的幾個命令，會有如下輸出，你會更直觀的感覺到加了索引帶來的效率提升。
是不是很明顯?
所有，你聽到MySQL 又在和你說了嗎?
『小伙子，你慢些走。有事沒事聽我給你先解釋解釋，說道說道啊』
小結一下，通過 explain，我們能在執行前就從 MySQL的優化器拿到對于當前 SQL 的執行計劃，了解執行中至少會掃描多少行，是否會使用到索引，大致會用多少時間等，這樣該加索引加索引，該改SQL就改SQL，這樣做到心中有數，應用的性能會更心中有數。
本文轉載自微信公眾號「Tomcat那些事兒」，可以通過以下二維碼關注。轉載本文請聯系Tomcat那些事兒公眾號。
