1、MySQL邏輯架構
日常在CURD的過程中，都避免不了跟數(shù)據(jù)庫打交道，大多數(shù)業(yè)務都離不開數(shù)據(jù)庫表的設計和SQL的編寫呼渣，那如何讓你編寫的SQL語句性能更優(yōu)呢？
先來整體看下MySQL邏輯架構圖：
MySQL整體邏輯架構圖可以分為Server和存儲引擎層力试。
Server層：
Server層涵蓋了MySQL的大多數(shù)核心服務功能徙邻，以及所有的內(nèi)置函數(shù)（如日期、時間畸裳、數(shù)學和加密函數(shù)等）缰犁，以及存儲過程、觸發(fā)器怖糊、視圖等跨存儲引擎的實現(xiàn)也在這一層來實現(xiàn)帅容。
- 連接器：負責跟客戶端建立連接、獲取權限伍伤、維持和管理連接并徘。
- 分析器：SQL詞法分析，SQL語法分析
- 優(yōu)化器：索引選擇扰魂，選擇一個執(zhí)行效率高的麦乞，生成執(zhí)行計劃
- 執(zhí)行器：操作引擎，返回執(zhí)行結果
- ...
- 查詢緩存：執(zhí)行SQL語句之前劝评，先查緩存姐直，緩存結果可能是以key-value對方式存儲的，key 是查詢的語句蒋畜，value 是查詢的結果声畏。
存儲引擎層：
負責數(shù)據(jù)的存儲和提取，是一種插件式的架構方式姻成。支持 InnoDB插龄、MyISAM、Memory 等多個存儲引擎科展。MySQL 5.5.5版本開始默認存儲引擎是 InnoDB均牢，也是目前常用的存儲引擎。
今天我們來看下詳細看下優(yōu)化器里的執(zhí)行計劃如何分析才睹，要分析一個 SQL 的執(zhí)行效率膨处，就要會看執(zhí)行計劃见秤，根據(jù)執(zhí)行計劃優(yōu)化 SQL，使其能達到高效查詢的目的真椿。
一條查詢語句需要經(jīng)過 MySQL 查詢優(yōu)化器的各種基于成本和規(guī)則鹃答，優(yōu)化后會生成一個所謂的執(zhí)行計劃。
那么這個執(zhí)行計劃主要展示具體執(zhí)行查詢的方式突硝，比如多表連接的順序是多少测摔，表里包含多個索引，每個表采用什么訪問方法來具體執(zhí)行查詢等解恰。
而設計 MySQL 的大佬是非常貼心的锋八，知道開發(fā)的朋友們都是親自寫 SQL 的，但是寫出 SQL 容易护盈，想寫出性能高的 SQL 可不簡單挟纱。
所以，大佬提供了 Explain 語句來幫我們查詢某個查詢語句的具體執(zhí)行計劃腐宋。
2紊服、SQL 執(zhí)行計劃解析
本文帶大家看懂 EXPLAIN 語句，必須要熟悉各項輸出是做什么的胸竞，從而有針對性的提升SQL 查詢語句的性能欺嗤。
列名 |
用途 |
|---|---|
| id | 每一個SELECT關鍵字查詢語句都對應一個唯一id |
| select_type | SELECT關鍵字對應的查詢類型 |
| table | 表名 |
| partitions | 匹配的分區(qū)信息 |
| types | 單表的訪問方法 |
| possible_keys | 可能用到的索引 |
| key | 實際使用到的索引 |
| key_len | 實際使用到的索引長度 |
| ref | 當使用索引列等值查詢時，與索引列進行等值匹配的對象信息 |
| rows | 預估需要讀取的記錄條數(shù) |
| filtered | 某個表經(jīng)過條件過濾后剩余的記錄條數(shù)百分比 |
| Extra | 額外的一些信息 |
為了方便解釋上面的執(zhí)行計劃各項輸出的含義卫枝，下面創(chuàng)建三張數(shù)據(jù)庫表煎饼。
數(shù)據(jù)庫創(chuàng)建三張表：
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');
EXPLAIN 執(zhí)行計劃參數(shù)詳解：
下載了最新的 MySQL8.0+ 版本，直接執(zhí)行 EXPLAIN 校赤，對比了 MySQL 5.0+ 版本執(zhí)行的 EXPLAIN EXTENDED 命令同樣都提供了一些查詢優(yōu)化的信息吆玖。除了執(zhí)行計劃各項輸出參數(shù)外，額外還有 filtered 列马篮，是一個百分比的值沾乘，rows * filtered/100 可以估算出將要和 EXPLAIN 中前一個表進行連接的行數(shù) 。
如下所示：
EXPLAIN 中的列
接下來我們將詳細說明下 EXPLAIN 執(zhí)行結果每一列的信息积蔚。
1、id 列
設計表時通常會設計 id烦周，一般會作為主鍵尽爆，執(zhí)行計劃的結果也不例外，也有 id 列读慎，id 列編號是 SELECT 的序列號漱贱，并且 id 的順序是按 SELECT 出現(xiàn)的順序增長的。id列越大執(zhí)行優(yōu)先級越高夭委，id 相同則從上往下執(zhí)行幅狮，id 為 NULL 最后執(zhí)行。
MySQL將 SELECT 查詢分為簡單查詢 SIMPLE 和復雜查詢 PRIMARY。
復雜查詢包括：簡單子查詢崇摄、派生表（ FROM 語句中的子查詢）擎值、UNION 和 UNION ALL 查詢。
簡單查詢：
復雜查詢：
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
這個查詢執(zhí)行時有個臨時表別名為 derived逐抑，外部 SELECT 查詢引用了這個臨時表
3）UNION 和 UNION ALL 查詢
EXPLAIN SELECT * FROM user UNION SELECT * FROM user;
UNION 結果總是放在一個匿名臨時表中鸠儿，臨時表不在 SQL 中出現(xiàn)，臨時表名為 <union1, 2>厕氨，因此它的 id 是 NULL进每，表明這個臨時表是為了合并兩個查詢結果集而創(chuàng)建的。
跟 UNION 對比命斧，UNION ALL 無需為最終結果而去重田晚，僅是單純的將多個查詢結果集中的記錄合并成一個并返回給用戶，所以不會使用到臨時表国葬，故沒有 id 為 NULL 記錄贤徒。如下所示：
EXPLAIN SELECT * FROM user UNION ALL SELECT * FROM user;
注意點：子查詢優(yōu)化為連接查詢
查詢優(yōu)化器可能對子查詢進行重寫，進而轉換為連接查詢胃惜，查詢計劃中的兩個id值是相同的泞莉，如下所示：
EXPLAIN SELECT * FROM user WHERE id IN (SELECT user_id FROM user_group);
2、select_type 列
MySQL中優(yōu)化器中的概念：
物化:
子查詢語句中的子查詢結果集中的記錄保存到臨時表的過程稱之為 物化（英文名：Materialize）船殉，簡單理解為存儲子查詢結果集的臨時表稱之為 物化表鲫趁。
也正因為物化表的記錄都建立了索引（基于內(nèi)存的物化表有哈希索引，基于磁盤的有B+樹索引）利虫，因此通過 IN 語句判斷某個操作數(shù)在不在子查詢的結果集中變得很快挨厚，從而提升語句的性能。
半連接 semi-join：
也是跟 IN 語句子查詢有關糠惫。
通用語句：
SELECT ... FROM outer_tables
WHERE expr IN (SELECT ... FROM inner_tables ...) AND ...
outer_tables 表對 inner_tables 半連接的意思：
對于outer_tables的某條記錄來說疫剃，我們僅關心在inner_tables表中是否存在匹配的記錄，而不用關心具體有多少條記錄與之匹配硼讽，最終結果只保留 outer_tables 表的記錄巢价。
每一個 SELECT 關鍵字的查詢都定義了一個 select_type 屬性，知道這個查詢屬性就能知道在整個查詢語句中所扮演的角色固阁。
1）SIMPLE：簡單查詢壤躲。查詢不包含子查詢 和 UNION。
2）PRIMARY：復雜查詢中最外層的SELECT备燃，可參照上面的 UNION 查詢語句碉克。
3）SUBQUERY：包含的子查詢語句無法轉換為 semi-join，并且為不相關子查詢并齐，查詢優(yōu)化器采用物化方案執(zhí)行該子查詢漏麦，該子查詢的第一個 SELECT 就會 SUBQUERY客税。該查詢由于被物化，只需要執(zhí)行一次撕贞。
4）DERIVED：對于采用物化形式執(zhí)行的包含派生表的查詢更耻，該派生表的對應的子查詢?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 查詢語句中的第二個和緊隨其后的 SELECT麻掸。
6）UNION RESULT：MySQL選擇使用臨時表完成 UNION 查詢的去重工作酥夭。
當 select_type 為這個值時，經(jīng)臣狗埽可以看到table的值是 <unionN,M>熬北，這說明匹配的 id 行 是這個集合的一部分。請看上面 UNION 查詢示例诚隙。
7）MATERIALIZED：當查詢優(yōu)化器執(zhí)行包含子查詢的語句時讶隐，選擇將子查詢物化之后與外層查詢進行連接查詢時，該子查詢類型為 MATERIALIZED久又。
8）DEPENDENT SUBQUERY：包含的子查詢語句無法轉換為 semi-join巫延，并且為相關子查詢，則該子查詢的第一個 SELECT 就會 DEPENDENT SUBQUERY地消。該查詢可能會被執(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');
上面這個子查詢語句中的 SELECT user_id FROM user_group WHERE name = 'a' 這個小查詢是第一個子查詢，所以它的 select_type 為 DEPENDENT SUBQUERY半夷，而 SELECT id FROM user WHERE name = 'b' 這個查詢在 UNION 后面婆廊，所以它的 select_type 為 DEPENDENT UNION。
最常見的值包括：SIMPLE 巫橄、PRIMARY淘邻、DERIVED、UNION湘换。
3宾舅、table 列
table 列表示 EXPLAIN 的單獨行的唯一標識符。這個值可能是表名彩倚、表的別名或者一個未查詢產(chǎn)生臨時表的標識符筹我，如派生表、子查詢或集合署恍。
當 FROM 子句中有子查詢時崎溃，如果優(yōu)化器采用的物化方式蜻直，table 列是 <derivenN> 格式盯质，表示當前查詢依賴 id=N 的查詢袁串，于是先執(zhí)行 id=N 的查詢。
當使用 UNION 查詢時呼巷，UNION RESULT 的 table 列的值為 <UNION1,2>囱修，1和2表示參與 UNION 的 SELECT 的行 id。
4王悍、type 列
這一列表示關聯(lián)類型或訪問類型破镰，即MySQL決定如何查找表中的行，查找數(shù)據(jù)行記錄的大概范圍压储。
依次從最優(yōu)到最差分別為：system > const > eq_ref > ref > range > index > ALL
一般來說鲜漩，得保證查詢達到range級別，最好達到ref
NULL：mysql能夠在優(yōu)化階段分解查詢語句集惋，在執(zhí)行階段用不著再訪問表或索引孕似。例如：在索引列中選取最小值，可以單獨查找索引來完成刮刑，不需要在執(zhí)行時訪問表喉祭。
1）system,const：MySQL 能對查詢的某部分進行優(yōu)化并將其轉化成一個常量。用于主鍵或唯一二級索引列與常數(shù)比較時雷绢，所以表最多有一個匹配行泛烙，讀取1次，速度比較快翘紊。system是 const 的特例蔽氨，表里只有一條記錄匹配時為 system。
EXPLAIN SELECT * FROM (SELECT * FROM user where id = 1) tmp;
2）eq_ref：在連接查詢時霞溪，如果被驅(qū)動表是通過主鍵或者唯一二級索引列等值匹配的方式進行訪問的孵滞，則對該被驅(qū)動表的訪問方法就是 eq_ref。這可能是在 const 之外最好的聯(lián)接類型了鸯匹。
EXPLAIN SELECT * FROM user_group INNER JOIN user ON user_group.user_id = user.id;
3）ref：相比 eq_ref坊饶，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前綴殴蓬，索引要和某個值相比較匿级，可能會找到多個符合條件的行。
a. 簡單 SELECT 查詢染厅，name 是普通索引（非唯一索引)洁灵。
EXPLAIN SELECT * FROM user where user.name = 'a';
b. 關聯(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：對普通二級索引進行等值查詢，該索引列也可以為NULL值時涩馆。
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 表里的兩個字段都有索引展运。
EXPLAIN SELECT * FROM group;
8）ALL：即全表掃描，MySQL 需要從頭到尾去查找表中所需要的行精刷。通常情況下這需要增加索引來進行優(yōu)化了拗胜。
EXPLAIN SELECT * FROM user;
5、possible_keys 列
possible_keys 列表示查詢可能使用哪些索引來查找怒允。
EXPLAIN 執(zhí)行計劃結果可能出現(xiàn) possible_keys 列挤土，而 key 顯示 NULL 的情況，這種情況是因為表中數(shù)據(jù)不多误算，MySQL 會認為索引對此查詢幫助不大仰美，選擇了全表查詢。
如果 possible_keys 列為 NULL儿礼，則沒有相關的索引咖杂。在這種情況下，可以通過檢查 WHERE 子句去分析下蚊夫，看看是否可以創(chuàng)造一個適當?shù)乃饕齺硖岣卟樵冃阅芩咦郑缓笥?EXPLAIN 查看效果。
另外注意：不是這一列的值越多越好知纷，使用索引過多壤圃，查詢優(yōu)化器計算時查詢成本高，所以如果可能的話琅轧，盡量刪除那些不用的索引伍绳。
6、key 列
key 列表示實際采用哪個索引來優(yōu)化對該表的訪問乍桂。
如果沒有使用索引冲杀，則該列是 NULL。如果想強制 MySQL使用或忽視 possible_keys 列中的索引睹酌，在查詢中使用 force index权谁、ignore index。
7憋沿、key_len 列
key_len 列表示當查詢優(yōu)化器決定使用某一個索引查詢時旺芽，該索引記錄的最大長度。
key_len 列計算規(guī)則如下：
- 字符串
char(n)：n字節(jié)長度
varchar(n)：2字節(jié)存儲字符串長度，如果是utf-8采章，則長度 3n + 2
注意：該索引列可以存儲NULL值字币，則key_len比不可以存儲NULL值時多1個字節(jié)。
比如：varchar(50)共缕，則實際占用的key_len長度是 3 * 50 + 2 = 152，如果該列允許存儲NULL士复，則key_len長度是153图谷。
- 數(shù)值類型
tinyint：1字節(jié)
smallint：2字節(jié)
int：4字節(jié)
bigint：8字節(jié)
- 時間類型
date：3字節(jié)
timestamp：4字節(jié)
datetime：8字節(jié)
索引最大長度是768字節(jié)，當字符串過長時阱洪，MySQL 會做一個類似左前綴索引的處理便贵，將前半部分的字符提取出來做索引。
舉例1：
user_group表中的聯(lián)合索引 idx_user_group_id 由 user_id 和 group_id 兩個int 列組成冗荸，并且每個 int 是 4 字節(jié)承璃。
EXPLAIN SELECT * FROM user_group WHERE user_id = 2;
通過結果中的 key_len=4可推斷出查詢使用了第一個列：user_id 列來執(zhí)行索引查找。
舉例2：
再看 user 表 name 字段是 varchar(45) 變長字符串類型蚌本，key_len為138 等于 45 * 3 + 2 (變長字節(jié)) + 1字節(jié)（允許存儲NULL值）
EXPLAIN SELECT * FROM user WHERE name = 'a';
所以盔粹，以后再看到 key_len 字段的值，不要在懵逼咯程癌，固定套路~
8舷嗡、ref 列
ref 列顯示了在 key 列記錄的索引中，表查找值所用到的列或常量嵌莉，常見的有：const（常量）进萄，字段名（例：user.id）。
9锐峭、rows 列
rows 列是查詢優(yōu)化器估計要讀取并檢測的行數(shù)中鼠，注意這個不是結果集里的行數(shù)。
如果查詢優(yōu)化器使用全表掃描查詢沿癞，rows 列代表預計的需要掃碼的行數(shù)援雇；
如果查詢優(yōu)化器使用索引執(zhí)行查詢，rows 列代表預計掃描的索引記錄行數(shù)椎扬。
10熊杨、filtered 列
對于單表來說意義不大，主要用于連接查詢中盗舰。
前文中也已提到 filtered 列晶府，是一個百分比的值，對于連接查詢來說钻趋，主要看驅(qū)動表的 filtered列的值 川陆，通過 rows * filtered/100 計算可以估算出被驅(qū)動表還需要執(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ū)動表user執(zhí)行的rows列為3行蛮位，filtered列為 33.33较沪，計算驅(qū)動表的扇出值為 3 * 33.33% 約等于1鳞绕，說明還需要對被驅(qū)動表執(zhí)行大約1次查詢。
11尸曼、Extra 列
Extra 列提供了一些額外信息们何。這一列在 MySQL中提供的信息有幾十個，這里僅列舉一些常見的重要值如下：
1）Using index：查詢的列被索引覆蓋控轿，并且 WHERE 篩選條件是索引的前導列冤竹，使用了索引性能高。一般是使用了覆蓋索引(查詢列都是索引列字段)茬射。對于 INNODB 存儲引擎來說鹦蠕，如果是輔助索引性能會有不少提高，并且也不需要回表查詢在抛。
2）Using where Using index：查詢的列被索引覆蓋钟病，并且 WHERE 篩選條件是索引列之一，但并不是索引的前導列刚梭，意味著無法直接通過索引查找來查詢到符合條件的數(shù)據(jù)肠阱。
3）NULL：查詢的列未被索引覆蓋，并且 WHERE 篩選條件是索引的前導列朴读，意味著用到了索引辖所，但是部分字段未被索引覆蓋，必須通過 回表 來查詢磨德，不是純粹地用到了索引缘回，也不是完全沒用到索引。
4）Using index condition：與Using where類似典挑，查詢的列不完全被索引覆蓋酥宴，WHERE 條件中是一個前導列的范圍。
5）Using temporary：MySQL 中需要創(chuàng)建一張內(nèi)部臨時表來處理查詢您觉，一般出現(xiàn)這種情況就需要考慮進行優(yōu)化了拙寡，首先是想到用索引來優(yōu)化。
通常在許多執(zhí)行包括DISTINCT琳水、GROUP BY肆糕、ORDER BY等子句查詢過程中，如果不能有效利用索引來完成查詢在孝，MySQL很有可能會尋求建立內(nèi)部臨時表來執(zhí)行查詢诚啃。
所以，執(zhí)行計劃中出現(xiàn)了 Using temporary 并不是個好兆頭私沮，因為建立與維護臨時表要付出很大的成本的始赎，要考慮使用索引來優(yōu)化改進。
6）Using filesort：MySQL 會對結果使用一個外部索引排序，而不是按索引次序從表里讀取行造垛。此時 MySQL 會根據(jù)聯(lián)接類型瀏覽所有符合條件的記錄魔招，并保存排序關鍵字和行指針，然后排序關鍵字并按順序檢索行信息五辽。這種情況下一般也是要考慮使用索引來優(yōu)化的办斑。
查詢中需要使用 filesort 的方式進行排序的記錄非常多，那么這個過長是很耗時的杆逗，想辦法將使用 文件排序 的執(zhí)行方式改進為使用索引進行排序乡翅。
7）Index merges：通常顯示為Using sort_union(...) 說明準備用 Sort-Union 索引合并方式來查詢；顯示為 Using union(...)髓迎，說明準備用Union索引合并方式查詢；顯示為Using intersect(...)建丧，說明準備使用Intersect索引合并方式查詢排龄。
8）LooseScan：在 IN 子查詢轉為 semi-join 時，如果采用的是 LooseScan 執(zhí)行策略翎朱，則會在Extra中提示橄维。
9）FirstMatch(tbl_name)：在 IN 子查詢轉為 semi-join 時，如果采用的是 FirstMatch 執(zhí)行策略拴曲，則會在Extra中提示争舞。
10）Using join buffer：強調(diào)了在獲取連接條件時沒有使用索引，并且需要連接緩沖區(qū)來存儲中間結果澈灼。出現(xiàn)該值竞川，應該注意，根據(jù)查詢的具體情況可能需要添加索引來改進性能叁熔。
我們所提到的回表操作 委乌，其實是一種隨機IO，比較耗時荣回，所以盡量避免上面提到的回表操作遭贸，當發(fā)現(xiàn)Extra提示為 Using filesort、Using temporary 時就需要格外注意了心软，考慮索引優(yōu)化壕吹。
3、最佳姿勢索引實踐
新建 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 '工號',
`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 '入職時間',
`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());
數(shù)據(jù)庫索引最佳實踐
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)化器會自動優(yōu)化效拭，將 name 排到最前面去，所以還是會正確使用聯(lián)合索引的。
聯(lián)合索引創(chuàng)建后缎患，你必須嚴格按照最左前綴的原理進行使用慕的，否則會無法使用到索引。盡量按照這個順序去寫挤渔，這樣避免 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、索引列上避免做計算操作
索引上盡量避免做函數(shù)計算等操作擂红，會導致索引失效而轉向全表掃描仪际。
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;
查詢的結果 type 列為 ALL，key 是空的昵骤，索引失效树碱，全表掃描。
計算邏輯盡量放到業(yè)務層去處理变秦，最大限度的命中索引成榜，同時還能節(jié)省數(shù)據(jù)庫資源開銷。
4蹦玫、存儲引擎無法使用索引中范圍條件右邊的列
EXPLAIN SELECT * FROM staff WHERE name= 'zhangsan' AND work_age > 2 AND position ='dev';
我們看到了執(zhí)行結果中 type 為 range 級別赎婚，使用了范圍查找，而 position 字段并沒有用到索引（沒有使用到BTree的索引去查詢）樱溉，只是從 name = 'zhangsan' AND work_age > 2 條件返回的結果集中惑淳，再過濾符合 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;
我們重點看下使用了 覆蓋索引 方式查詢绢馍，會在結果中 Extra 列顯示 Using index ，這說明在查詢列包含了索引列肠套，不需要再次回表查詢了舰涌。而如果使用 SELECT * 方式查詢，查詢列包含非索引的列你稚，Extra 顯示為 NULL瓷耙，所以還會進行回表查詢朱躺。
附一個曾經(jīng)線上SQL的優(yōu)化記錄：
artist 表有幾十萬條的數(shù)據(jù)量，第一條執(zhí)行的SQL沒有索引直接查詢搁痛，查詢耗時 0.557 毫秒长搀；第一次優(yōu)化新建 founded 字段作為普通索引，查詢耗時 0.0224 毫秒鸡典；第二次優(yōu)化再次重建聯(lián)合索引 founded_name源请，優(yōu)化后查詢耗時：0.0051 毫秒。因為使用了覆蓋索引查詢方式彻况，基于此優(yōu)化谁尸，SQL查詢效率提升非常明顯。
6纽甘、范圍條件查找能夠命中索引
范圍條件主要包括 <良蛮、<=、>悍赢、>=决瞳、between 等。
若條件中范圍列有普通索引和主鍵索引同時存在泽裳， 優(yōu)先使用主鍵索引:
EXPLAIN SELECT * FROM staff WHERE staff.s_no > 10 AND staff.id > 2;
范圍列可以用到索引瞒斩，注意聯(lián)合索引必須符合最左前綴法則破婆，如果查詢條件中有兩個范圍列則無法全用到索引涮总，優(yōu)化器會去選擇：
EXPLAIN SELECT * FROM staff WHERE staff.name != 'zl' AND staff.s_no > 1;
若條件中范圍查詢和等值查詢同時存在，優(yōu)先匹配等值查詢列的索引：
EXPLAIN SELECT * FROM staff WHERE staff.s_no > 10 AND staff.s_name = 'zl';
7祷舀、索引列不為 NULL瀑梗，IS NOT NULL無法使用索引
索引列建議都使用 NOT NULL 約束 及默認值，單列索引不存 NULL 值裳扯，聯(lián)合索引不存全部為 NULL 的值抛丽，如果列允許為 NULL，查詢結果可能不符合預期饰豺。
staff 表中為 remark 字段新建普通索引：
ALTER TABLE staff ADD INDEX idx_remark (remark);
IS NULL 查詢命中索引：
EXPLAIN SELECT * FROM staff WHERE staff.remark IS NULL;
IS NOT NULL 查詢不會命中索引：
EXPLAIN SELECT * FROM staff WHERE staff.name IS NOT NULL;
8亿鲜、模糊條件查詢以通配符開頭索引失效
like '%xx' 或 like '%xx%' 前導模糊查詢不能命中索引：
EXPLAIN SELECT * from staff where name like '%zhang%';
如何使用模擬查詢才能命中索引？
a）like 'xx%' 非前導模糊查詢可以命中索引：
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蒿柳、字符串類型不加單引號索引失效
字符串的數(shù)據(jù)類型一定要將常量值使用單引號，這個在日常開發(fā)中要特別注意的漩蟆，數(shù)據(jù)類型出現(xiàn)隱式轉換的時候不會命中索引垒探。
不加單引號索引失效
EXPLAIN SELECT * FROM staff WHERE name = 1;
name=1 類似于在該字段上做了一個函數(shù)運算，因此不會走索引的怠李。
加單引號會命中索引：
EXPLAIN SELECT * FROM staff WHERE name = 'zhangsan';
10圾叼、OR 使用多數(shù)情況下索引會失效
EXPLAIN SELECT * FROM staff WHERE name='zhangsan' OR work_age = 2;
盡管 name 和 work_age 是聯(lián)合索引蛤克，但是 work_age 列上并沒有建索引，所以使用了 OR 不會走索引夷蚊。
如果 OR 前后都是聯(lián)合索引帶頭大哥 name 字段构挤，那么就會用到索引，如下所示：
因 OR 后面的條件列中沒有索引撬码，會走全表掃描儿倒。存在全表掃描的情況下，就沒有必要多一次索引掃描增加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)合索引第一個字段，兩者使用 OR 查詢結果 Extra 顯示 Using sort_union(idx_name_age_position,idx_s_name); Using where 解釋一下叫胁。
如果執(zhí)行計劃 Extra 列出現(xiàn)了 Using sort_union(...) 的提示凰慈，說明準備使用 Sort-Union 索引合并的方式執(zhí)行查詢。如果出現(xiàn)了 Using intersect(...) 的提示驼鹅，說明準備使用 Intersect 索引合并方式執(zhí)行查詢微谓，如果出現(xiàn)了 Using union(...) 的提示 ，說明準備使用 Union 索引合并方式執(zhí)行查詢输钩。 括號中 ... 表示需要進行索引合并的索引名稱豺型。
使用UNION優(yōu)化改進：
EXPLAIN SELECT * FROM staff WHERE name='zhangsan' UNION SELECT * FROM staff WHERE s_name = 'zs';
使用 UNION 執(zhí)行計劃中出現(xiàn)了第三條記錄，Extra 中出現(xiàn) Using temporary买乃，說明 MySQL因為不能有效利用索引姻氨，建立了內(nèi)部臨時表來執(zhí)行查詢。當你在使用 DISTINCT 剪验、GROUP BY肴焊、UNION 等子句中的查詢過程中，都有可能會出現(xiàn)該擴展信息功戚。
使用UNION ALL進一步優(yōu)化：
EXPLAIN SELECT * FROM staff WHERE name='zhangsan' UNION ALL SELECT * FROM staff WHERE s_name = 'zs';
執(zhí)行結果中不再出現(xiàn)內(nèi)部臨時表娶眷，具體用的時候結合實際需求來定是否使用。
11啸臀、負向查詢條件不能使用索引届宠，可以優(yōu)化為 IN 查詢
負向查詢條件包括：!=、<>乘粒、NOT IN豌注、NOT EXISTS、NOT LIKE 等谓厘。
不會命中索引：
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 命中索引有個前提，是查詢條件字段數(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í)行結果 Extra 中出現(xiàn)了 Using filesort 的提示，一般看到這個就要想辦法優(yōu)化了纪他。
調(diào)整排序的兩個字段順序之后鄙煤，Extra 會提示為 Using index，使用了索引茶袒，避免了排序的資源開銷：
EXPLAIN SELECT name,work_age FROM staff ORDER BY name,work_age;
** 13梯刚、局部索引的使用**
局部索引虽填，區(qū)別于最左列索引（順序取索引中靠左的列的查詢）喳挑，它只取某列的一部分作為索引。
INNODB存儲引擎下怜跑，一般是字符串類型向叉，很長锥腻，全部作為索引大大增加存儲空間，索引也需要維護母谎，對于長字符串瘦黑，又想作為索引列，可取的辦法就是取前一部分（局部）销睁，代表一整列作為索引串供璧。
如何確保這個前綴能代表或大致代表這一列存崖？MySQL中有個概念是 索引選擇性冻记，是指索引中不重復的值的數(shù)目（也稱基數(shù)X）與整個表該列記錄總數(shù)（T）的比值±淳澹基數(shù)可以通過SHOW INDEX FROM 表名 查看冗栗。
比如一個列表 [1,2,2,3,5,6]，總數(shù)是 6供搀，不重復值數(shù)目為 5隅居，選擇性為 5/6，因此選擇性范圍是[X/T, 1]葛虐，這個值越大胎源，表示列中不重復值越多，越適合作為局部索引屿脐，而唯一索引（UNIQUE KEY）的選擇性是1涕蚤。
`SELECT COUNT(DISTINCT(CONCAT(LEFT(remark, N))/COUNT(*) FROM t; 測試出接近 1 的索引選擇性宪卿，其中N是索引的長度，窮舉法去找出N的值万栅，然后再建索引佑钾。
創(chuàng)建 局部索引 ，使用 remark 字段舉個例子
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%';
索引優(yōu)化總結
上面列了大部分場景索引最佳實戰(zhàn)烦粒，除此之外休溶，不宜建索引的幾點小總結：
1）更新非常頻繁字段不宜建索引
因為字段更新臺頻繁，會導致B+樹的頻繁的變更扰她，重建索引兽掰。所以這個過程是十分消耗數(shù)據(jù)庫性能的。
2）區(qū)分度不大的字段不宜建索引
比如類似性別這類的字段徒役，區(qū)分度不大禾进，建立索引的意義不大。因為不能有效過濾數(shù)據(jù)廉涕，性能和全表掃描相當泻云。另外注意一點，返回數(shù)據(jù)的比例在 30% 之外的狐蜕，優(yōu)化器不會選擇使用索引宠纯。
3）業(yè)務中有唯一特性的字段，建議建成唯一索引
業(yè)務中如果有唯一特性的字段层释，即使是多個字段的組合婆瓜，也盡量都建成唯一索引。盡管唯一索引會影響插入效率贡羔，但是對于查詢的速度提升是非常明顯的廉白。此外，還能夠提供校驗機制乖寒，如果沒有唯一索引猴蹂，高并發(fā)場景下，可能還會產(chǎn)生臟數(shù)據(jù)楣嘁。
4）多表關聯(lián)時磅轻，要確保關聯(lián)字段上必須有索引
5）創(chuàng)建索引時避免建立錯誤的認識
索引越多越好，認為一個查詢就需要建一個索引逐虚。
寧缺勿濫聋溜，認為索引會消耗空間、嚴重拖慢更新和新增速度叭爱。
抵制唯一索引撮躁，認為業(yè)務的唯一性一律需要在應用層通過“先查后插”方式解決。
過早優(yōu)化买雾，在不了解系統(tǒng)的情況下就開始優(yōu)化把曼。
6）最佳索引實踐口訣
如果你覺得上面哪些太啰嗦缨称，有朋友已總結為一套優(yōu)化口訣，優(yōu)化SQL時也能提個醒吧祝迂。
全值匹配我最愛睦尽，最左前綴要遵守；
帶頭大哥不能死型雳，中間兄弟不能斷当凡；
索引列上少計算，范圍之后全失效纠俭；
Like百分寫最右沿量，覆蓋索引不寫星；
不等空值還有or冤荆，索引失效要少用朴则；
VAR引號不可丟，SQL高級也不難钓简！
7）EXPLAIN 執(zhí)行計劃實踐總結
如果還是覺得 EXPLAIN 執(zhí)行計劃列太多了乌妒，也記不住呀，那么請重點關注以下幾列：
第1列：ID越大外邓，執(zhí)行的優(yōu)先級越高撤蚊；ID相等，從上往下優(yōu)先順序執(zhí)行损话。
第2列：select_type 查詢語句的類型侦啸，SIMPLE簡單查詢，PRIMARY復雜查詢丧枪，DERIVED衍生查詢(from子查詢的臨時表)光涂，派生表。
第4列：請重點掌握拧烦，type類型忘闻，查詢效率優(yōu)先級：system->const->eq_ref->ref->range->index->ALL
ALL 是最差的，system 是最好的屎篱，性能最佳服赎，阿里巴巴開發(fā)規(guī)約中要求最差也得到 range 級別葵蒂，而不能有 index交播、ALL。
最后践付，對于后端工程師而言秦士，盡力都能掌握 EXPLAIN 的使用，寫完SQL請習慣性的用它幫助你分析一下永高，做一個對SQL性能有追求的程序員隧土，因為SQL也是程序員必備技能提针，將慢查詢問題拍死在項目上線前夕。
如果覺得本文有所收獲曹傀，歡迎轉發(fā)分享辐脖。
參考資料：
MySQL官網(wǎng)
https://www.cnblogs.com/songwenjie/p/9402295.html
https://www.cnblogs.com/phpdragon/p/8231533.html
