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