日常在CURD的過程中，都避免不了跟數(shù)據(jù)庫打交道，大多數(shù)業(yè)務(wù)都離不開數(shù)據(jù)庫表的設(shè)計(jì)和SQL的編寫，那如何讓你編寫的SQL語句性能更優(yōu)呢？
先來整體看下MySQL邏輯架構(gòu)圖：
MySQL整體邏輯架構(gòu)圖可以分為Server和存儲(chǔ)引擎層。
Server層：
Server層涵蓋了MySQL的大多數(shù)核心服務(wù)功能，以及所有的內(nèi)置函數(shù)（如日期、時(shí)間、數(shù)學(xué)和加密函數(shù)等），以及存儲(chǔ)過程、觸發(fā)器、視圖等跨存儲(chǔ)引擎的實(shí)現(xiàn)也在這一層來實(shí)現(xiàn)。
存儲(chǔ)引擎層：
負(fù)責(zé)數(shù)據(jù)的存儲(chǔ)和提取，是一種插件式的架構(gòu)方式。支持 InnoDB、MyISAM、Memory 等多個(gè)存儲(chǔ)引擎。MySQL 5.5.5版本開始默認(rèn)存儲(chǔ)引擎是 InnoDB，也是目前常用的存儲(chǔ)引擎。
今天我們來看下詳細(xì)看下優(yōu)化器里的執(zhí)行計(jì)劃如何分析，要分析一個(gè) SQL 的執(zhí)行效率，就要會(huì)看執(zhí)行計(jì)劃，根據(jù)執(zhí)行計(jì)劃優(yōu)化 SQL，使其能達(dá)到高效查詢的目的。
一條查詢語句需要經(jīng)過 MySQL 查詢優(yōu)化器的各種基于成本和規(guī)則，優(yōu)化后會(huì)生成一個(gè)所謂的執(zhí)行計(jì)劃。
那么這個(gè)執(zhí)行計(jì)劃主要展示具體執(zhí)行查詢的方式，比如多表連接的順序是多少，表里包含多個(gè)索引，每個(gè)表采用什么訪問方法來具體執(zhí)行查詢等。
而設(shè)計(jì) MySQL 的大佬是非常貼心的，知道開發(fā)的朋友們都是親自寫 SQL 的，但是寫出 SQL 容易，想寫出性能高的 SQL 可不簡單。
所以，大佬提供了 Explain 語句來幫我們查詢某個(gè)查詢語句的具體執(zhí)行計(jì)劃。
本文帶大家看懂 EXPLAIN 語句，必須要熟悉各項(xiàng)輸出是做什么的，從而有針對性的提升SQL 查詢語句的性能。
列名 |
用途 |
|---|---|
| id | 每一個(gè)SELECT關(guān)鍵字查詢語句都對應(yīng)一個(gè)唯一id |
| select_type | SELECT關(guān)鍵字對應(yīng)的查詢類型 |
| table | 表名 |
| partitions | 匹配的分區(qū)信息 |
| types | 單表的訪問方法 |
| possible_keys | 可能用到的索引 |
| key | 實(shí)際使用到的索引 |
| key_len | 實(shí)際使用到的索引長度 |
| ref | 當(dāng)使用索引列等值查詢時(shí)，與索引列進(jìn)行等值匹配的對象信息 |
| rows | 預(yù)估需要讀取的記錄條數(shù) |
| filtered | 某個(gè)表經(jīng)過條件過濾后剩余的記錄條數(shù)百分比 |
| Extra | 額外的一些信息 |
為了方便解釋上面的執(zhí)行計(jì)劃各項(xiàng)輸出的含義，下面創(chuàng)建三張數(shù)據(jù)庫表。
DROP TABLE IF EXISTS user;
CREATE TABLE `user` (
`id` int(11) NOT NULL,
`name` varchar(45) DEFAULT NULL,
`update_time` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO user (`id`, `name`, `update_time`)
VALUES (1,'a','2017-12-22 15:27:18'), (2,'b','2017-12-22 15:27:18'), (3,'c','2017-12-22 15:27:18');
DROP TABLE IF EXISTS `group`;
CREATE TABLE `group` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(10) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `group` (`id`, `name`) VALUES (1,'group1'),(2,'group2'),(3,'group3');
DROP TABLE IF EXISTS user_group;
CREATE TABLE `user_group` (
`id` int(11) NOT NULL,
`user_id` int(11) NOT NULL,
`group_id` int(11) NOT NULL,
`remark` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_user_id` (`user_id`),
KEY `idx_group_id` (`group_id`),
KEY `idx_user_group_id` (`user_id`,`group_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO user_group (`id`, `user_id`, `group_id`, `remark`)
VALUES (1,1,1,'bak1'), (2,2,2,'bak2'), (3,3,3,'bak3');
下載了最新的 MySQL8.0+ 版本，直接執(zhí)行 EXPLAIN ，對比了 MySQL 5.0+ 版本執(zhí)行的 EXPLAIN EXTENDED 命令同樣都提供了一些查詢優(yōu)化的信息。除了執(zhí)行計(jì)劃各項(xiàng)輸出參數(shù)外，額外還有 filtered 列，是一個(gè)百分比的值，rows * filtered/100 可以估算出將要和 EXPLAIN 中前一個(gè)表進(jìn)行連接的行數(shù) 。
如下所示：
EXPLAIN 中的列
接下來我們將詳細(xì)說明下 EXPLAIN 執(zhí)行結(jié)果每一列的信息。
1、id 列
設(shè)計(jì)表時(shí)通常會(huì)設(shè)計(jì) id，一般會(huì)作為主鍵，執(zhí)行計(jì)劃的結(jié)果也不例外，也有 id 列，id 列編號(hào)是 SELECT 的序列號(hào)，并且 id 的順序是按 SELECT 出現(xiàn)的順序增長的。id列越大執(zhí)行優(yōu)先級越高，id 相同則從上往下執(zhí)行，id 為 NULL 最后執(zhí)行。
MySQL將 SELECT 查詢分為簡單查詢 SIMPLE 和復(fù)雜查詢 PRIMARY。
復(fù)雜查詢包括：簡單子查詢、派生表（ FROM 語句中的子查詢）、UNION 和 UNION ALL 查詢。
簡單查詢：
復(fù)雜查詢：
1）簡單子查詢
EXPLAIN SELECT (SELECT 1 from user LIMIT 1) from user;
2）FROM 子句中的子查詢
EXPLAIN SELECT * FROM (SELECT id, count(*) as c from group GROUP BY name) as derived
這個(gè)查詢執(zhí)行時(shí)有個(gè)臨時(shí)表別名為 derived，外部 SELECT 查詢引用了這個(gè)臨時(shí)表
3）UNION 和 UNION ALL 查詢
EXPLAIN SELECT * FROM user UNION SELECT * FROM user;
UNION 結(jié)果總是放在一個(gè)匿名臨時(shí)表中，臨時(shí)表不在 SQL 中出現(xiàn)，臨時(shí)表名為 <union1, 2>，因此它的 id 是 NULL，表明這個(gè)臨時(shí)表是為了合并兩個(gè)查詢結(jié)果集而創(chuàng)建的。
跟 UNION 對比，UNION ALL 無需為最終結(jié)果而去重，僅是單純的將多個(gè)查詢結(jié)果集中的記錄合并成一個(gè)并返回給用戶，所以不會(huì)使用到臨時(shí)表，故沒有 id 為 NULL 記錄。如下所示：
EXPLAIN SELECT * FROM user UNION ALL SELECT * FROM user;
注意點(diǎn)：子查詢優(yōu)化為連接查詢
查詢優(yōu)化器可能對子查詢進(jìn)行重寫，進(jìn)而轉(zhuǎn)換為連接查詢，查詢計(jì)劃中的兩個(gè)id值是相同的，如下所示：
EXPLAIN SELECT * FROM user WHERE id IN (SELECT user_id FROM user_group);
2、select_type 列
MySQL中優(yōu)化器中的概念：
物化:
子查詢語句中的子查詢結(jié)果集中的記錄保存到臨時(shí)表的過程稱之為 物化（英文名：Materialize），簡單理解為存儲(chǔ)子查詢結(jié)果集的臨時(shí)表稱之為 物化表。
也正因?yàn)槲锘淼挠涗浂冀⒘怂饕ɑ趦?nèi)存的物化表有哈希索引，基于磁盤的有B+樹索引），因此通過 IN 語句判斷某個(gè)操作數(shù)在不在子查詢的結(jié)果集中變得很快，從而提升語句的性能。
半連接 semi-join：
也是跟 IN 語句子查詢有關(guān)。
通用語句：
SELECT ... FROM outer_tables
WHERE expr IN (SELECT ... FROM inner_tables ...) AND ...
outer_tables 表對 inner_tables 半連接的意思：
對于 outer_tables的某條記錄來說，我們僅關(guān)心在inner_tables 表中是否存在匹配的記錄，而不用關(guān)心具體有多少條記錄與之匹配，最終結(jié)果只保留 outer_tables 表的記錄。
每一個(gè) SELECT 關(guān)鍵字的查詢都定義了一個(gè) select_type 屬性，知道這個(gè)查詢屬性就能知道在整個(gè)查詢語句中所扮演的角色。
1）SIMPLE：簡單查詢。查詢不包含子查詢 和 UNION。
2）PRIMARY：復(fù)雜查詢中最外層的SELECT，可參照上面的 UNION 查詢語句。
3）SUBQUERY：包含的子查詢語句無法轉(zhuǎn)換為 semi-join，并且為不相關(guān)子查詢，查詢優(yōu)化器采用物化方案執(zhí)行該子查詢，該子查詢的第一個(gè) SELECT 就會(huì) SUBQUERY。該查詢由于被物化，只需要執(zhí)行一次。
4）DERIVED：對于采用物化形式執(zhí)行的包含派生表的查詢，該派生表的對應(yīng)的子查詢?yōu)?DERIVED。
查詢語句如下所示：
EXPLAIN SELECT * FROM (SELECT id, count(*) as c FROM user GROUP BY id) AS derived_u where c>1;
5）UNION：在 UNION 查詢語句中的第二個(gè)和緊隨其后的 SELECT。
6）UNION RESULT：MySQL選擇使用臨時(shí)表完成 UNION 查詢的去重工作。
當(dāng) select_type 為這個(gè)值時(shí)，經(jīng)?？梢钥吹絫able的值是 <unionN,M>，這說明匹配的 id 行 是這個(gè)集合的一部分。請看上面 UNION 查詢示例。
7）MATERIALIZED：當(dāng)查詢優(yōu)化器執(zhí)行包含子查詢的語句時(shí)，選擇將子查詢物化之后與外層查詢進(jìn)行連接查詢時(shí)，該子查詢類型為 MATERIALIZED。
8）DEPENDENT SUBQUERY：包含的子查詢語句無法轉(zhuǎn)換為 semi-join，并且為相關(guān)子查詢，則該子查詢的第一個(gè) SELECT 就會(huì) DEPENDENT SUBQUERY。該查詢可能會(huì)被執(zhí)行多次。
8）DEPENDENT UNION：包含的子查詢語句中包含了 UNION 或者 UNION ALL 的大查詢，這些查詢都依賴外層查詢，這些子查詢語句類型為 DEPENDENT UNION。
EXPLAIN SELECT * FROM user WHERE id IN (SELECT user_id FROM user_group WHERE name = 'a' UNION SELECT id FROM user WHERE name = 'b');
上面這個(gè)子查詢語句中的 SELECT user_id FROM user_group WHERE name = 'a' 這個(gè)小查詢是第一個(gè)子查詢，所以它的 select_type 為 DEPENDENT SUBQUERY，而 SELECT id FROM user WHERE name = 'b' 這個(gè)查詢在 UNION 后面，所以它的 select_type 為 DEPENDENT UNION。
最常見的值包括：SIMPLE 、PRIMARY、DERIVED、UNION。
3、table 列
table 列表示 EXPLAIN 的單獨(dú)行的唯一標(biāo)識(shí)符。這個(gè)值可能是表名、表的別名或者一個(gè)未查詢產(chǎn)生臨時(shí)表的標(biāo)識(shí)符，如派生表、子查詢或集合。
當(dāng) FROM 子句中有子查詢時(shí)，如果優(yōu)化器采用的物化方式，table 列是 <derivenN> 格式，表示當(dāng)前查詢依賴 id=N 的查詢，于是先執(zhí)行 id=N 的查詢。
當(dāng)使用 UNION 查詢時(shí)，UNION RESULT 的 table 列的值為 <UNION1,2>，1和2表示參與 UNION 的 SELECT 的行 id。
4、type 列
這一列表示關(guān)聯(lián)類型或訪問類型，即MySQL決定如何查找表中的行，查找數(shù)據(jù)行記錄的大概范圍。
依次從最優(yōu)到最差分別為：system > const > eq_ref > ref > range > index > ALL
一般來說，得保證查詢達(dá)到range級別，最好達(dá)到ref
NULL：mysql能夠在優(yōu)化階段分解查詢語句，在執(zhí)行階段用不著再訪問表或索引。例如：在索引列中選取最小值，可以單獨(dú)查找索引來完成，不需要在執(zhí)行時(shí)訪問表。
1）system,const：MySQL 能對查詢的某部分進(jìn)行優(yōu)化并將其轉(zhuǎn)化成一個(gè)常量。用于主鍵或唯一二級索引列與常數(shù)比較時(shí)，所以表最多有一個(gè)匹配行，讀取1次，速度比較快。system是 const 的特例，表里只有一條記錄匹配時(shí)為 system。
EXPLAIN SELECT * FROM (SELECT * FROM user where id = 1) tmp;
2）eq_ref：在連接查詢時(shí)，如果被驅(qū)動(dòng)表是通過主鍵或者唯一二級索引列等值匹配的方式進(jìn)行訪問的，則對該被驅(qū)動(dòng)表的訪問方法就是 eq_ref。這可能是在 const 之外最好的聯(lián)接類型了。
EXPLAIN SELECT * FROM user_group INNER JOIN user ON user_group.user_id = user.id;
3）ref：相比 eq_ref，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前綴，索引要和某個(gè)值相比較，可能會(huì)找到多個(gè)符合條件的行。
a. 簡單 SELECT 查詢，name 是普通索引（非唯一索引)。
EXPLAIN SELECT * FROM user where user.name = 'a';
b. 關(guān)聯(lián)表查詢，idx_user_group_id (user_id,group_id) 為聯(lián)合索引，這里使用到了user_group聯(lián)合索引最左邊前綴 user_id。
EXPLAIN SELECT user_id FROM user LEFT JOIN user_group ON user.id = user_group.user_id;
4）ref_or_null：對普通二級索引進(jìn)行等值查詢，該索引列也可以為NULL值時(shí)。
EXPLAIN SELECT * FROM user where user.name = 'a' OR name IS NULL;
5）index_merge：MySQL使用索引合并的方式執(zhí)行的。
EXPLAIN SELECT * FROM user WHERE user.name = 'a' OR user.id = 1;
6）range：使用索引獲取范圍區(qū)間的記錄，通常出現(xiàn)在 in, between ,> ,<, >= 等操作中。
EXPLAIN SELECT * FROM user WHERE user.id > 1;
7）index：掃描全表索引，這通常比ALL快一些。（index是從索引中讀取的，而 ALL 是從硬盤中讀取）
group 表里的兩個(gè)字段都有索引。
EXPLAIN SELECT * FROM group;
8）ALL：即全表掃描，MySQL 需要從頭到尾去查找表中所需要的行。通常情況下這需要增加索引來進(jìn)行優(yōu)化了。
EXPLAIN SELECT * FROM user;
5、possible_keys 列
possible_keys 列表示查詢可能使用哪些索引來查找。
EXPLAIN 執(zhí)行計(jì)劃結(jié)果可能出現(xiàn) possible_keys 列，而 key 顯示 NULL 的情況，這種情況是因?yàn)楸碇袛?shù)據(jù)不多，MySQL 會(huì)認(rèn)為索引對此查詢幫助不大，選擇了全表查詢。
如果 possible_keys 列為 NULL，則沒有相關(guān)的索引。在這種情況下，可以通過檢查 WHERE 子句去分析下，看看是否可以創(chuàng)造一個(gè)適當(dāng)?shù)乃饕齺硖岣卟樵冃阅?，然后?EXPLAIN 查看效果。
另外注意：不是這一列的值越多越好，使用索引過多，查詢優(yōu)化器計(jì)算時(shí)查詢成本高，所以如果可能的話，盡量刪除那些不用的索引。
6、key 列
key 列表示實(shí)際采用哪個(gè)索引來優(yōu)化對該表的訪問。
如果沒有使用索引，則該列是 NULL。如果想強(qiáng)制 MySQL使用或忽視 possible_keys 列中的索引，在查詢中使用 force index、ignore index。
7、key_len 列
key_len 列表示當(dāng)查詢優(yōu)化器決定使用某一個(gè)索引查詢時(shí)，該索引記錄的最大長度。
key_len 列計(jì)算規(guī)則如下：
char(n)：n字節(jié)長度
varchar(n)：2字節(jié)存儲(chǔ)字符串長度，如果是utf-8，則長度 3n + 2
注意：該索引列可以存儲(chǔ)NULL值，則key_len比不可以存儲(chǔ)NULL值時(shí)多1個(gè)字節(jié)。
比如：varchar(50)，則實(shí)際占用的key_len長度是 3 * 50 + 2 = 152，如果該列允許存儲(chǔ)NULL，則key_len長度是153。
tinyint：1字節(jié)
smallint：2字節(jié)
int：4字節(jié)
bigint：8字節(jié)
date：3字節(jié)
timestamp：4字節(jié)
datetime：8字節(jié)
索引最大長度是768字節(jié)，當(dāng)字符串過長時(shí)，MySQL 會(huì)做一個(gè)類似左前綴索引的處理，將前半部分的字符提取出來做索引。
舉例1：
user_group表中的聯(lián)合索引 idx_user_group_id 由 user_id 和 group_id 兩個(gè)int 列組成，并且每個(gè) int 是 4 字節(jié)。
EXPLAIN SELECT * FROM user_group WHERE user_id = 2;
通過結(jié)果中的 key_len=4可推斷出查詢使用了第一個(gè)列：user_id 列來執(zhí)行索引查找。
舉例2：
再看 user 表 name 字段是 varchar(45) 變長字符串類型，key_len為138 等于 45 * 3 + 2 (變長字節(jié)) + 1字節(jié)（允許存儲(chǔ)NULL值）
EXPLAIN SELECT * FROM user WHERE name = 'a';
所以，以后再看到 key_len 字段的值，不要在懵逼咯，固定套路~
8、ref 列
ref 列顯示了在 key 列記錄的索引中，表查找值所用到的列或常量，常見的有：const（常量），字段名（例：user.id）。
9、rows 列
rows 列是查詢優(yōu)化器估計(jì)要讀取并檢測的行數(shù)，注意這個(gè)不是結(jié)果集里的行數(shù)。
如果查詢優(yōu)化器使用全表掃描查詢，rows 列代表預(yù)計(jì)的需要掃碼的行數(shù)；
如果查詢優(yōu)化器使用索引執(zhí)行查詢，rows 列代表預(yù)計(jì)掃描的索引記錄行數(shù)。
10、filtered 列
對于單表來說意義不大，主要用于連接查詢中。
前文中也已提到 filtered 列，是一個(gè)百分比的值，對于連接查詢來說，主要看驅(qū)動(dòng)表的 filtered列的值 ，通過 rows * filtered/100 計(jì)算可以估算出被驅(qū)動(dòng)表還需要執(zhí)行的查詢次數(shù)。
EXPLAIN SELECT * FROM user INNER JOIN user_group ON user.id = user_group.user_id WHERE user.update_time = '2019-01-01';
可以看到驅(qū)動(dòng)表user執(zhí)行的rows列為3行，filtered列為 33.33，計(jì)算驅(qū)動(dòng)表的扇出值為 3 * 33.33% 約等于1，說明還需要對被驅(qū)動(dòng)表執(zhí)行大約1次查詢。
11、Extra 列
Extra 列提供了一些額外信息。這一列在 MySQL中提供的信息有幾十個(gè)，這里僅列舉一些常見的重要值如下：
1）Using index：查詢的列被索引覆蓋，并且 WHERE 篩選條件是索引的前導(dǎo)列，使用了索引性能高。一般是使用了覆蓋索引(查詢列都是索引列字段)。對于 INNODB 存儲(chǔ)引擎來說，如果是輔助索引性能會(huì)有不少提高，并且也不需要回表查詢。
2）Using where Using index：查詢的列被索引覆蓋，并且 WHERE 篩選條件是索引列之一，但并不是索引的前導(dǎo)列，意味著無法直接通過索引查找來查詢到符合條件的數(shù)據(jù)。
3）NULL：查詢的列未被索引覆蓋，并且 WHERE 篩選條件是索引的前導(dǎo)列，意味著用到了索引，但是部分字段未被索引覆蓋，必須通過 回表 來查詢，不是純粹地用到了索引，也不是完全沒用到索引。
4）Using index condition：與Using where類似，查詢的列不完全被索引覆蓋，WHERE 條件中是一個(gè)前導(dǎo)列的范圍。
5）Using temporary：MySQL 中需要?jiǎng)?chuàng)建一張內(nèi)部臨時(shí)表來處理查詢，一般出現(xiàn)這種情況就需要考慮進(jìn)行優(yōu)化了，首先是想到用索引來優(yōu)化。
通常在許多執(zhí)行包括DISTINCT、GROUP BY、ORDER BY等子句查詢過程中，如果不能有效利用索引來完成查詢，MySQL很有可能會(huì)尋求建立內(nèi)部臨時(shí)表來執(zhí)行查詢。
所以，執(zhí)行計(jì)劃中出現(xiàn)了 Using temporary 并不是個(gè)好兆頭，因?yàn)榻⑴c維護(hù)臨時(shí)表要付出很大的成本的，要考慮使用索引來優(yōu)化改進(jìn)。
6）Using filesort：MySQL 會(huì)對結(jié)果使用一個(gè)外部索引排序，而不是按索引次序從表里讀取行。此時(shí) MySQL 會(huì)根據(jù)聯(lián)接類型瀏覽所有符合條件的記錄，并保存排序關(guān)鍵字和行指針，然后排序關(guān)鍵字并按順序檢索行信息。這種情況下一般也是要考慮使用索引來優(yōu)化的。
查詢中需要使用 filesort 的方式進(jìn)行排序的記錄非常多，那么這個(gè)過長是很耗時(shí)的，想辦法將使用 文件排序 的執(zhí)行方式改進(jìn)為使用索引進(jìn)行排序。
7）Index merges：通常顯示為Using sort_union(...) 說明準(zhǔn)備用 Sort-Union 索引合并方式來查詢；顯示為 Using union(...)，說明準(zhǔn)備用Union索引合并方式查詢；顯示為Using intersect(...)，說明準(zhǔn)備使用Intersect索引合并方式查詢。
8）LooseScan：在 IN 子查詢轉(zhuǎn)為 semi-join 時(shí)，如果采用的是 LooseScan 執(zhí)行策略，則會(huì)在Extra中提示。
9）FirstMatch(tbl_name)：在 IN 子查詢轉(zhuǎn)為 semi-join 時(shí)，如果采用的是 FirstMatch 執(zhí)行策略，則會(huì)在Extra中提示。
10）Using join buffer：強(qiáng)調(diào)了在獲取連接條件時(shí)沒有使用索引，并且需要連接緩沖區(qū)來存儲(chǔ)中間結(jié)果。出現(xiàn)該值，應(yīng)該注意，根據(jù)查詢的具體情況可能需要添加索引來改進(jìn)性能。
我們所提到的回表操作 ，其實(shí)是一種隨機(jī)IO，比較耗時(shí)，所以盡量避免上面提到的回表操作，當(dāng)發(fā)現(xiàn)Extra提示為 Using filesort、Using temporary 時(shí)就需要格外注意了，考慮索引優(yōu)化。
staff 表表演使用：# 重建 `staff` 表
DROP TABLE `staff`;
CREATE TABLE `staff` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名',
`s_name` VARCHAR(24) NOT NULL DEFAULT '' COMMENT '花名',
`s_no` INT(4) NOT NULL DEFAULT 0 COMMENT '工號(hào)',
`work_age` int(11) NOT NULL DEFAULT '0' COMMENT '工齡',
`position` varchar(20) NOT NULL DEFAULT '' COMMENT '職位',
`arrival_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入職時(shí)間',
`remark` VARCHAR(500) DEFAULT NULL COMMENT '備注', # 允許 NULL
PRIMARY KEY (`id`), # 主鍵
UNIQUE KEY idx_s_name (s_name), # 唯一索引
KEY idx_s_no (s_no), # 普通索引
KEY `idx_name_age_position` (`name`,`work_age`,`position`) USING BTREE # 聯(lián)合索引
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COMMENT='員工記錄表';
# 初始化 `staff` 表數(shù)據(jù)
INSERT INTO staff(name,s_name,s_no,work_age,position,arrival_time) VALUES('zhangsan','zs',10,2,'manager',NOW());
INSERT INTO staff(name,s_name,s_no,work_age,position,arrival_time) VALUES('lisi','ls',11,3,'dev',NOW());
INSERT INTO staff(name,s_name,s_no,work_age,position,arrival_time) VALUES('wangwu','ww',12,8,'dev',NOW());
INSERT INTO staff(name,s_name,s_no,work_age,position,arrival_time) VALUES('zhangliu','zl',110,5,'dev',NOW());
INSERT INTO staff(name,s_name,s_no,work_age,position,arrival_time) VALUES('xiaosun','xs',111,5,'dev',NOW());
INSERT INTO staff(name,s_name,s_no,work_age,position,arrival_time) VALUES('donggua','dg',200,3,'dev',NOW());
1、全值匹配：
EXPLAIN SELECT * FROM staff WHERE name= 'zhangsan';
EXPLAIN SELECT * FROM staff WHERE name= 'zhangsan' AND work_age = 2;
EXPLAIN SELECT * FROM staff where name = 'zhangsan' AND work_age = 2 AND position = 'dev';
EXPLAIN SELECT * FROM staff where position = 'dev' AND name = 'zhangsan' AND work_age = 2;
最后一條，我們將 position 放到了 WHERE 條件后面，盡管沒有按照聯(lián)合索引的順序編寫條件，MySQL 優(yōu)化器會(huì)自動(dòng)優(yōu)化，將 name 排到最前面去，所以還是會(huì)正確使用聯(lián)合索引的。
聯(lián)合索引創(chuàng)建后，你必須嚴(yán)格按照最左前綴的原理進(jìn)行使用，否則會(huì)無法使用到索引。盡量按照這個(gè)順序去寫，這樣避免 MySQL 優(yōu)化器再次優(yōu)化了。
2、最佳左前綴法則：
如果索引了多列，要遵守最左前綴法則。指的是查詢從索引的最左前列開始并且不跳過索引中的列。
以下 SQL 符合最左前綴匹配法則：
EXPLAIN SELECT * FROM staff WHERE name = 'zhangsan' AND work_age = 3 AND position = 'manager';
EXPLAIN SELECT * FROM staff WHERE name = 'zhangsan' AND position = 'manager';
以下執(zhí)行都是全表掃描，type 為 ALL，都不符合最左前綴法則：
EXPLAIN SELECT * FROM staff WHERE work_age = 2 AND position ='dev';
EXPLAIN SELECT * FROM staff WHERE position = 'dev';
3、索引列上避免做計(jì)算操作
索引上盡量避免做函數(shù)計(jì)算等操作，會(huì)導(dǎo)致索引失效而轉(zhuǎn)向全表掃描。
WHERE 條件后面索引列使用函數(shù):
EXPLAIN SELECT * FROM staff WHERE LEFT(name, 5) = 'zhang';
EXPLAIN SELECT * FROM staff WHERE LOWER(name) = 'zhangsan';
EXPLAIN SELECT * FROM staff WHERE staff.s_no * 2 > 3;
查詢的結(jié)果 type 列為 ALL，key 是空的，索引失效，全表掃描。
計(jì)算邏輯盡量放到業(yè)務(wù)層去處理，最大限度的命中索引，同時(shí)還能節(jié)省數(shù)據(jù)庫資源開銷。
4、存儲(chǔ)引擎無法使用索引中范圍條件右邊的列
EXPLAIN SELECT * FROM staff WHERE name= 'zhangsan' AND work_age > 2 AND position ='dev';
我們看到了執(zhí)行結(jié)果中 type 為 range 級別，使用了范圍查找，而 position 字段并沒有用到索引（沒有使用到BTree的索引去查詢），只是從 name = 'zhangsan' AND work_age > 2 條件返回的結(jié)果集中，再過濾符合 position 字段條件的數(shù)據(jù)。
5、盡量使用覆蓋索引
覆蓋索引：簡單理解，只訪問建了索引的列。減少使用 SELECT * 語句查詢列。
使用了覆蓋索引：
EXPLAIN SELECT name,work_age FROM staff WHERE name= 'zhangsan' AND work_age = 3;
SELECT * 查詢：
EXPLAIN SELECT * FROM staff WHERE name= 'zhangsan' AND work_age = 3;
我們重點(diǎn)看下使用了 覆蓋索引 方式查詢，會(huì)在結(jié)果中 Extra 列顯示 Using index ，這說明在查詢列包含了索引列，不需要再次回表查詢了。而如果使用 SELECT * 方式查詢，查詢列包含非索引的列，Extra 顯示為 NULL，所以還會(huì)進(jìn)行回表查詢。
附一個(gè)曾經(jīng)線上SQL的優(yōu)化記錄：
artist 表有幾十萬條的數(shù)據(jù)量，第一條執(zhí)行的SQL沒有索引直接查詢，查詢耗時(shí) 0.557 毫秒；第一次優(yōu)化新建 founded 字段作為普通索引，查詢耗時(shí) 0.0224 毫秒；第二次優(yōu)化再次重建聯(lián)合索引 founded_name，優(yōu)化后查詢耗時(shí)：0.0051 毫秒。因?yàn)槭褂昧烁采w索引查詢方式，基于此優(yōu)化，SQL查詢效率提升非常明顯。
6、范圍條件查找能夠命中索引
范圍條件主要包括 <、<=、>、>=、between 等。
若條件中范圍列有普通索引和主鍵索引同時(shí)存在， 優(yōu)先使用主鍵索引:
EXPLAIN SELECT * FROM staff WHERE staff.s_no > 10 AND staff.id > 2;
范圍列可以用到索引，注意聯(lián)合索引必須符合最左前綴法則，如果查詢條件中有兩個(gè)范圍列則無法全用到索引，優(yōu)化器會(huì)去選擇：
EXPLAIN SELECT * FROM staff WHERE staff.name != 'zl' AND staff.s_no > 1;
若條件中范圍查詢和等值查詢同時(shí)存在，優(yōu)先匹配等值查詢列的索引：
EXPLAIN SELECT * FROM staff WHERE staff.s_no > 10 AND staff.s_name = 'zl';
7、索引列不為 NULL，IS NOT NULL無法使用索引
索引列建議都使用 NOT NULL 約束 及默認(rèn)值，單列索引不存 NULL 值，聯(lián)合索引不存全部為 NULL 的值，如果列允許為 NULL，查詢結(jié)果可能不符合預(yù)期。
staff 表中為 remark 字段新建普通索引：
ALTER TABLE staff ADD INDEX idx_remark (remark);
IS NULL 查詢命中索引：
EXPLAIN SELECT * FROM staff WHERE staff.remark IS NULL;
IS NOT NULL 查詢不會(huì)命中索引：
EXPLAIN SELECT * FROM staff WHERE staff.name IS NOT NULL;
8、模糊條件查詢以通配符開頭索引失效
like '%xx' 或 like '%xx%' 前導(dǎo)模糊查詢不能命中索引：
EXPLAIN SELECT * from staff where name like '%zhang%';
如何使用模擬查詢才能命中索引？
a）like 'xx%' 非前導(dǎo)模糊查詢可以命中索引：
EXPLAIN SELECT * FROM staff WHERE name LIKE 'zhang%';
b）使用覆蓋索引，查詢字段必須要建立覆蓋索引字段
EXPLAIN SELECT name,work_age FROM staff WHERE name LIKE '%zhang%';
聯(lián)合索引是 idx_name_work_age_position
9、字符串類型不加單引號(hào)索引失效
字符串的數(shù)據(jù)類型一定要將常量值使用單引號(hào)，這個(gè)在日常開發(fā)中要特別注意的，數(shù)據(jù)類型出現(xiàn)隱式轉(zhuǎn)換的時(shí)候不會(huì)命中索引。
不加單引號(hào)索引失效
EXPLAIN SELECT * FROM staff WHERE name = 1;
name=1 類似于在該字段上做了一個(gè)函數(shù)運(yùn)算，因此不會(huì)走索引的。
加單引號(hào)會(huì)命中索引：
EXPLAIN SELECT * FROM staff WHERE name = 'zhangsan';
10、OR 使用多數(shù)情況下索引會(huì)失效
EXPLAIN SELECT * FROM staff WHERE name='zhangsan' OR work_age = 2;
盡管 name 和 work_age 是聯(lián)合索引，但是 work_age 列上并沒有建索引，所以使用了 OR 不會(huì)走索引。
如果 OR 前后都是聯(lián)合索引帶頭大哥 name 字段，那么就會(huì)用到索引，如下所示：
因 OR 后面的條件列中沒有索引，會(huì)走全表掃描。存在全表掃描的情況下，就沒有必要多一次索引掃描增加IO訪問。
可使用覆蓋索引查詢：
EXPLAIN SELECT name,work_age FROM staff WHERE name='zhangsan' OR work_age = 2;
** OR 后面也使用索引列：**
EXPLAIN SELECT * FROM staff WHERE name='zhangsan' OR s_name='wangwu';
s_name 是唯一索引，name是聯(lián)合索引第一個(gè)字段，兩者使用 OR 查詢結(jié)果 Extra 顯示 Using sort_union(idx_name_age_position,idx_s_name); Using where 解釋一下。
如果執(zhí)行計(jì)劃 Extra 列出現(xiàn)了 Using sort_union(...) 的提示，說明準(zhǔn)備使用 Sort-Union 索引合并的方式執(zhí)行查詢。如果出現(xiàn)了 Using intersect(...) 的提示，說明準(zhǔn)備使用 Intersect 索引合并方式執(zhí)行查詢，如果出現(xiàn)了 Using union(...) 的提示 ，說明準(zhǔn)備使用 Union 索引合并方式執(zhí)行查詢。 括號(hào)中 ... 表示需要進(jìn)行索引合并的索引名稱。
使用UNION優(yōu)化改進(jìn)：
EXPLAIN SELECT * FROM staff WHERE name='zhangsan' UNION SELECT * FROM staff WHERE s_name = 'zs';
使用 UNION 執(zhí)行計(jì)劃中出現(xiàn)了第三條記錄，Extra 中出現(xiàn) Using temporary，說明 MySQL因?yàn)椴荒苡行Ю盟饕?，建立了?nèi)部臨時(shí)表來執(zhí)行查詢。當(dāng)你在使用 DISTINCT 、GROUP BY、UNION 等子句中的查詢過程中，都有可能會(huì)出現(xiàn)該擴(kuò)展信息。
使用UNION ALL進(jìn)一步優(yōu)化：
EXPLAIN SELECT * FROM staff WHERE name='zhangsan' UNION ALL SELECT * FROM staff WHERE s_name = 'zs';
執(zhí)行結(jié)果中不再出現(xiàn)內(nèi)部臨時(shí)表，具體用的時(shí)候結(jié)合實(shí)際需求來定是否使用。
11、負(fù)向查詢條件不能使用索引，可以優(yōu)化為 IN 查詢
負(fù)向查詢條件包括：!=、<>、NOT IN、NOT EXISTS、NOT LIKE 等。
不會(huì)命中索引：
EXPLAIN SELECT * FROM staff WHERE s_no !=1 AND s_no != 2;
EXPLAIN SELECT * FROM staff WHERE s_no NOT IN (1,2);
使用IN優(yōu)化，命中索引：
EXPLAIN SELECT * FROM staff WHERE s_no IN (11,12);
但是使用 IN 命中索引有個(gè)前提，是查詢條件字段數(shù)據(jù)區(qū)分度要高，通常如：狀態(tài)、類型、性別之類的字段。
** 12、排序?qū)λ饕挠绊?*
ORDER BY是經(jīng)常用的語句，排序也遵循最左前綴列的原則。
查詢所有列未命中索引：
EXPLAIN SELECT * FROM staff ORDER BY name,work_age;
覆蓋索引查詢可命中索引：
覆蓋索引能夠利用聯(lián)合索引查詢，但是 ORDER BY 后的條件查詢不符合最左前綴原則，執(zhí)行結(jié)果 Extra 中出現(xiàn)了 Using filesort 的提示，一般看到這個(gè)就要想辦法優(yōu)化了。
調(diào)整排序的兩個(gè)字段順序之后，Extra 會(huì)提示為 Using index，使用了索引，避免了排序的資源開銷：
EXPLAIN SELECT name,work_age FROM staff ORDER BY name,work_age;
** 13、局部索引的使用**
局部索引，區(qū)別于最左列索引（順序取索引中靠左的列的查詢），它只取某列的一部分作為索引。
INNODB存儲(chǔ)引擎下，一般是字符串類型，很長，全部作為索引大大增加存儲(chǔ)空間，索引也需要維護(hù)，對于長字符串，又想作為索引列，可取的辦法就是取前一部分（局部），代表一整列作為索引串。
如何確保這個(gè)前綴能代表或大致代表這一列？MySQL中有個(gè)概念是 索引選擇性，是指索引中不重復(fù)的值的數(shù)目（也稱基數(shù)X）與整個(gè)表該列記錄總數(shù)（T）的比值。基數(shù)可以通過SHOW INDEX FROM 表名 查看。
比如一個(gè)列表 [1,2,2,3,5,6]，總數(shù)是 6，不重復(fù)值數(shù)目為 5，選擇性為 5/6，因此選擇性范圍是[X/T, 1]，這個(gè)值越大，表示列中不重復(fù)值越多，越適合作為局部索引，而唯一索引（UNIQUE KEY）的選擇性是1。
`SELECT COUNT(DISTINCT(CONCAT(LEFT(remark, N))/COUNT(*) FROM t; 測試出接近 1 的索引選擇性，其中N是索引的長度，窮舉法去找出N的值，然后再建索引。
創(chuàng)建 局部索引 ，使用 remark 字段舉個(gè)例子
EXPLAIN SELECT * FROM staff where remark LIKE 'xxx%';
對 remark 字段重建局部索引：
ALTER TABLE staff DROP INDEX idx_remark_part, ADD INDEX idx_remark_part(remark(5));
再次執(zhí)行查詢：
EXPLAIN SELECT * FROM staff where remark LIKE 'xxx%';
上面列了大部分場景索引最佳實(shí)戰(zhàn)，除此之外，不宜建索引的幾點(diǎn)小總結(jié)：
1）更新非常頻繁字段不宜建索引
因?yàn)樽侄胃屡_(tái)頻繁，會(huì)導(dǎo)致B+樹的頻繁的變更，重建索引。所以這個(gè)過程是十分消耗數(shù)據(jù)庫性能的。
2）區(qū)分度不大的字段不宜建索引
比如類似性別這類的字段，區(qū)分度不大，建立索引的意義不大。因?yàn)椴荒苡行н^濾數(shù)據(jù)，性能和全表掃描相當(dāng)。另外注意一點(diǎn)，返回?cái)?shù)據(jù)的比例在 30% 之外的，優(yōu)化器不會(huì)選擇使用索引。
3）業(yè)務(wù)中有唯一特性的字段，建議建成唯一索引
業(yè)務(wù)中如果有唯一特性的字段，即使是多個(gè)字段的組合，也盡量都建成唯一索引。盡管唯一索引會(huì)影響插入效率，但是對于查詢的速度提升是非常明顯的。此外，還能夠提供校驗(yàn)機(jī)制，如果沒有唯一索引，高并發(fā)場景下，可能還會(huì)產(chǎn)生臟數(shù)據(jù)。
4）多表關(guān)聯(lián)時(shí)，要確保關(guān)聯(lián)字段上必須有索引
5）創(chuàng)建索引時(shí)避免建立錯(cuò)誤的認(rèn)識(shí)
索引越多越好，認(rèn)為一個(gè)查詢就需要建一個(gè)索引。
寧缺勿濫，認(rèn)為索引會(huì)消耗空間、嚴(yán)重拖慢更新和新增速度。
抵制唯一索引，認(rèn)為業(yè)務(wù)的唯一性一律需要在應(yīng)用層通過“先查后插”方式解決。
過早優(yōu)化，在不了解系統(tǒng)的情況下就開始優(yōu)化。
6）最佳索引實(shí)踐口訣
如果你覺得上面哪些太啰嗦，有朋友已總結(jié)為一套優(yōu)化口訣，優(yōu)化SQL時(shí)也能提個(gè)醒吧。
全值匹配我最愛，最左前綴要遵守；
帶頭大哥不能死，中間兄弟不能斷；
索引列上少計(jì)算，范圍之后全失效；
Like百分寫最右，覆蓋索引不寫星；
不等空值還有or，索引失效要少用；
VAR引號(hào)不可丟，SQL高級也不難！
7）EXPLAIN 執(zhí)行計(jì)劃實(shí)踐總結(jié)
如果還是覺得 EXPLAIN 執(zhí)行計(jì)劃列太多了，也記不住呀，那么請重點(diǎn)關(guān)注以下幾列：
第1列：ID越大，執(zhí)行的優(yōu)先級越高；ID相等，從上往下優(yōu)先順序執(zhí)行。
第2列：select_type 查詢語句的類型，SIMPLE簡單查詢，PRIMARY復(fù)雜查詢，DERIVED衍生查詢(from子查詢的臨時(shí)表)，派生表。
第4列：請重點(diǎn)掌握，type類型，查詢效率優(yōu)先級：system->const->eq_ref->ref->range->index->ALL
ALL 是最差的，system 是最好的，性能最佳，阿里巴巴開發(fā)規(guī)約中要求最差也得到 range 級別，而不能有 index、ALL。
最后，對于后端工程師而言，盡力都能掌握 EXPLAIN 的使用，寫完SQL請習(xí)慣性的用它幫助你分析一下，做一個(gè)對SQL性能有追求的程序員，因?yàn)镾QL也是程序員必備技能，將慢查詢問題拍死在項(xiàng)目上線前夕。
如果覺得本文有所收獲，歡迎轉(zhuǎn)發(fā)分享。
參考資料：
MySQL官網(wǎng)
https://www.cnblogs.com/songwenjie/p/9402295.html
https://www.cnblogs.com/phpdragon/p/8231533.html
聯(lián)系客服
