1、MySQL邏輯架構(gòu)
日常在CURD的過程中，都避免不了跟數(shù)據(jù)庫打交道，大多數(shù)業(yè)務(wù)都離不開數(shù)據(jù)庫表的設(shè)計和SQL的編寫，那如何讓你編寫的SQL語句性能更優(yōu)呢？
先來整體看下MySQL邏輯架構(gòu)圖：
MySQL整體邏輯架構(gòu)圖可以分為Server和存儲引擎層。
Server層：
Server層涵蓋了MySQL的大多數(shù)核心服務(wù)功能，以及所有的內(nèi)置函數(shù)（如日期、時間、數(shù)學(xué)和加密函數(shù)等），以及存儲過程、觸發(fā)器、視圖等跨存儲引擎的實現(xiàn)也在這一層來實現(xiàn)。
- 連接器：負責(zé)跟客戶端建立連接、獲取權(quán)限、維持和管理連接。
- 分析器：SQL詞法分析，SQL語法分析
- 優(yōu)化器：索引選擇，選擇一個執(zhí)行效率高的，生成執(zhí)行計劃
- 執(zhí)行器：操作引擎，返回執(zhí)行結(jié)果
- ...
- 查詢緩存：執(zhí)行SQL語句之前，先查緩存，緩存結(jié)果可能是以key-value對方式存儲的，key 是查詢的語句，value 是查詢的結(jié)果。
存儲引擎層：
負責(zé)數(shù)據(jù)的存儲和提取，是一種插件式的架構(gòu)方式。支持 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í)行查詢等。
而設(shè)計 MySQL 的大佬是非常貼心的，知道開發(fā)的朋友們都是親自寫 SQL 的，但是寫出 SQL 容易，想寫出性能高的 SQL 可不簡單。
所以，大佬提供了 Explain 語句來幫我們查詢某個查詢語句的具體執(zhí)行計劃。
2、SQL 執(zhí)行計劃解析
本文帶大家看懂 EXPLAIN 語句，必須要熟悉各項輸出是做什么的，從而有針對性的提升SQL 查詢語句的性能。
列名 |
用途 |
|---|---|
| id | 每一個SELECT關(guān)鍵字查詢語句都對應(yīng)一個唯一id |
| select_type | SELECT關(guān)鍵字對應(yīng)的查詢類型 |
| table | 表名 |
| partitions | 匹配的分區(qū)信息 |
| types | 單表的訪問方法 |
| possible_keys | 可能用到的索引 |
| key | 實際使用到的索引 |
| key_len | 實際使用到的索引長度 |
| ref | 當使用索引列等值查詢時，與索引列進行等值匹配的對象信息 |
| rows | 預(yù)估需要讀取的記錄條數(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í)行結(jié)果每一列的信息。
1、id 列
設(shè)計表時通常會設(shè)計 id，一般會作為主鍵，執(zhí)行計劃的結(jié)果也不例外，也有 id 列，id 列編號是 SELECT 的序列號，并且 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
這個查詢執(zhí)行時有個臨時表別名為 derived，外部 SELECT 查詢引用了這個臨時表
3）UNION 和 UNION ALL 查詢
EXPLAIN SELECT * FROM user UNION SELECT * FROM user;
UNION 結(jié)果總是放在一個匿名臨時表中，臨時表不在 SQL 中出現(xiàn)，臨時表名為 <union1, 2>，因此它的 id 是 NULL，表明這個臨時表是為了合并兩個查詢結(jié)果集而創(chuàng)建的。
跟 UNION 對比，UNION ALL 無需為最終結(jié)果而去重，僅是單純的將多個查詢結(jié)果集中的記錄合并成一個并返回給用戶，所以不會使用到臨時表，故沒有 id 為 NULL 記錄。如下所示：
EXPLAIN SELECT * FROM user UNION ALL SELECT * FROM user;
注意點：子查詢優(yōu)化為連接查詢
查詢優(yōu)化器可能對子查詢進行重寫，進而轉(zhuǎn)換為連接查詢，查詢計劃中的兩個id值是相同的，如下所示：
EXPLAIN SELECT * FROM user WHERE id IN (SELECT user_id FROM user_group);
2、select_type 列
MySQL中優(yōu)化器中的概念：
物化:
子查詢語句中的子查詢結(jié)果集中的記錄保存到臨時表的過程稱之為 物化（英文名：Materialize），簡單理解為存儲子查詢結(jié)果集的臨時表稱之為 物化表。
也正因為物化表的記錄都建立了索引（基于內(nèi)存的物化表有哈希索引，基于磁盤的有B+樹索引），因此通過 IN 語句判斷某個操作數(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 表的記錄。
每一個 SELECT 關(guān)鍵字的查詢都定義了一個 select_type 屬性，知道這個查詢屬性就能知道在整個查詢語句中所扮演的角色。
1）SIMPLE：簡單查詢。查詢不包含子查詢 和 UNION。
2）PRIMARY：復(fù)雜查詢中最外層的SELECT，可參照上面的 UNION 查詢語句。
3）SUBQUERY：包含的子查詢語句無法轉(zhuǎn)換為 semi-join，并且為不相關(guān)子查詢，查詢優(yōu)化器采用物化方案執(zhí)行該子查詢，該子查詢的第一個 SELECT 就會 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 查詢語句中的第二個和緊隨其后的 SELECT。
6）UNION RESULT：MySQL選擇使用臨時表完成 UNION 查詢的去重工作。
當 select_type 為這個值時，經(jīng)常可以看到table的值是 <unionN,M>，這說明匹配的 id 行 是這個集合的一部分。請看上面 UNION 查詢示例。
7）MATERIALIZED：當查詢優(yōu)化器執(zhí)行包含子查詢的語句時，選擇將子查詢物化之后與外層查詢進行連接查詢時，該子查詢類型為 MATERIALIZED。
8）DEPENDENT SUBQUERY：包含的子查詢語句無法轉(zhuǎn)換為 semi-join，并且為相關(guān)子查詢，則該子查詢的第一個 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 列
這一列表示關(guān)聯(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)化并將其轉(zhuǎn)化成一個常量。用于主鍵或唯一二級索引列與常數(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. 關(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：對普通二級索引進行等值查詢，該索引列也可以為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 是從硬盤中讀?。?/p>
group 表里的兩個字段都有索引。
EXPLAIN SELECT * FROM group;
8）ALL：即全表掃描，MySQL 需要從頭到尾去查找表中所需要的行。通常情況下這需要增加索引來進行優(yōu)化了。
EXPLAIN SELECT * FROM user;
5、possible_keys 列
possible_keys 列表示查詢可能使用哪些索引來查找。
EXPLAIN 執(zhí)行計劃結(jié)果可能出現(xiàn) possible_keys 列，而 key 顯示 NULL 的情況，這種情況是因為表中數(shù)據(jù)不多，MySQL 會認為索引對此查詢幫助不大，選擇了全表查詢。
如果 possible_keys 列為 NULL，則沒有相關(guān)的索引。在這種情況下，可以通過檢查 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;
通過結(jié)果中的 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ù)，注意這個不是結(jié)果集里的行數(shù)。
如果查詢優(yōu)化器使用全表掃描查詢，rows 列代表預(yù)計的需要掃碼的行數(shù)；
如果查詢優(yōu)化器使用索引執(zhí)行查詢，rows 列代表預(yù)計掃描的索引記錄行數(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 篩選條件是索引的前導(dǎo)列，使用了索引性能高。一般是使用了覆蓋索引(查詢列都是索引列字段)。對于 INNODB 存儲引擎來說，如果是輔助索引性能會有不少提高，并且也不需要回表查詢。
2）Using where Using index：查詢的列被索引覆蓋，并且 WHERE 篩選條件是索引列之一，但并不是索引的前導(dǎo)列，意味著無法直接通過索引查找來查詢到符合條件的數(shù)據(jù)。
3）NULL：查詢的列未被索引覆蓋，并且 WHERE 篩選條件是索引的前導(dǎo)列，意味著用到了索引，但是部分字段未被索引覆蓋，必須通過 回表 來查詢，不是純粹地用到了索引，也不是完全沒用到索引。
4）Using index condition：與Using where類似，查詢的列不完全被索引覆蓋，WHERE 條件中是一個前導(dǎo)列的范圍。
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 會對結(jié)果使用一個外部索引排序，而不是按索引次序從表里讀取行。此時 MySQL 會根據(jù)聯(lián)接類型瀏覽所有符合條件的記錄，并保存排序關(guān)鍵字和行指針，然后排序關(guān)鍵字并按順序檢索行信息。這種情況下一般也是要考慮使用索引來優(yōu)化的。
查詢中需要使用 filesort 的方式進行排序的記錄非常多，那么這個過長是很耗時的，想辦法將使用 文件排序 的執(zhí)行方式改進為使用索引進行排序。
7）Index merges：通常顯示為Using sort_union(...) 說明準備用 Sort-Union 索引合并方式來查詢；顯示為 Using union(...)，說明準備用Union索引合并方式查詢；顯示為Using intersect(...)，說明準備使用Intersect索引合并方式查詢。
8）LooseScan：在 IN 子查詢轉(zhuǎn)為 semi-join 時，如果采用的是 LooseScan 執(zhí)行策略，則會在Extra中提示。
9）FirstMatch(tbl_name)：在 IN 子查詢轉(zhuǎn)為 semi-join 時，如果采用的是 FirstMatch 執(zhí)行策略，則會在Extra中提示。
10）Using join buffer：強調(diào)了在獲取連接條件時沒有使用索引，并且需要連接緩沖區(qū)來存儲中間結(jié)果。出現(xiàn)該值，應(yīng)該注意，根據(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ù)計算等操作，會導(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 是空的，索引失效，全表掃描。
計算邏輯盡量放到業(yè)務(wù)層去處理，最大限度的命中索引，同時還能節(jié)省數(shù)據(jù)庫資源開銷。
4、存儲引擎無法使用索引中范圍條件右邊的列
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;
我們重點看下使用了 覆蓋索引 方式查詢，會在結(jié)果中 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，查詢結(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 查詢不會命中索引：
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、字符串類型不加單引號索引失效
字符串的數(shù)據(jù)類型一定要將常量值使用單引號，這個在日常開發(fā)中要特別注意的，數(shù)據(jù)類型出現(xiàn)隱式轉(zhuǎ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 查詢結(jié)果 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í)行結(jié)果中不再出現(xiàn)內(nèi)部臨時表，具體用的時候結(jié)合實際需求來定是否使用。
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í)行結(jié)果 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中有個概念是 索引選擇性，是指索引中不重復(fù)的值的數(shù)目（也稱基數(shù)X）與整個表該列記錄總數(shù)（T）的比值?；鶖?shù)可以通過SHOW INDEX FROM 表名 查看。
比如一個列表 [1,2,2,3,5,6]，總數(shù)是 6，不重復(fù)值數(shù)目為 5，選擇性為 5/6，因此選擇性范圍是[X/T, 1]，這個值越大，表示列中不重復(fù)值越多，越適合作為局部索引，而唯一索引（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)化總結(jié)
上面列了大部分場景索引最佳實戰(zhàn)，除此之外，不宜建索引的幾點小總結(jié)：
1）更新非常頻繁字段不宜建索引
因為字段更新臺頻繁，會導(dǎo)致B+樹的頻繁的變更，重建索引。所以這個過程是十分消耗數(shù)據(jù)庫性能的。
2）區(qū)分度不大的字段不宜建索引
比如類似性別這類的字段，區(qū)分度不大，建立索引的意義不大。因為不能有效過濾數(shù)據(jù)，性能和全表掃描相當。另外注意一點，返回數(shù)據(jù)的比例在 30% 之外的，優(yōu)化器不會選擇使用索引。
3）業(yè)務(wù)中有唯一特性的字段，建議建成唯一索引
業(yè)務(wù)中如果有唯一特性的字段，即使是多個字段的組合，也盡量都建成唯一索引。盡管唯一索引會影響插入效率，但是對于查詢的速度提升是非常明顯的。此外，還能夠提供校驗機制，如果沒有唯一索引，高并發(fā)場景下，可能還會產(chǎn)生臟數(shù)據(jù)。
4）多表關(guān)聯(lián)時，要確保關(guān)聯(lián)字段上必須有索引
5）創(chuàng)建索引時避免建立錯誤的認識
索引越多越好，認為一個查詢就需要建一個索引。
寧缺勿濫，認為索引會消耗空間、嚴重拖慢更新和新增速度。
抵制唯一索引，認為業(yè)務(wù)的唯一性一律需要在應(yīng)用層通過“先查后插”方式解決。
過早優(yōu)化，在不了解系統(tǒng)的情況下就開始優(yōu)化。
6）最佳索引實踐口訣
如果你覺得上面哪些太啰嗦，有朋友已總結(jié)為一套優(yōu)化口訣，優(yōu)化SQL時也能提個醒吧。
全值匹配我最愛，最左前綴要遵守；
帶頭大哥不能死，中間兄弟不能斷；
索引列上少計算，范圍之后全失效；
Like百分寫最右，覆蓋索引不寫星；
不等空值還有or，索引失效要少用；
VAR引號不可丟，SQL高級也不難！
7）EXPLAIN 執(zhí)行計劃實踐總結(jié)
如果還是覺得 EXPLAIN 執(zhí)行計劃列太多了，也記不住呀，那么請重點關(guān)注以下幾列：
第1列：ID越大，執(zhí)行的優(yōu)先級越高；ID相等，從上往下優(yōu)先順序執(zhí)行。
第2列：select_type 查詢語句的類型，SIMPLE簡單查詢，PRIMARY復(fù)雜查詢，DERIVED衍生查詢(from子查詢的臨時表)，派生表。
第4列：請重點掌握，type類型，查詢效率優(yōu)先級：system->const->eq_ref->ref->range->index->ALL
ALL 是最差的，system 是最好的，性能最佳，阿里巴巴開發(fā)規(guī)約中要求最差也得到 range 級別，而不能有 index、ALL。
最后，對于后端工程師而言，盡力都能掌握 EXPLAIN 的使用，寫完SQL請習(xí)慣性的用它幫助你分析一下，做一個對SQL性能有追求的程序員，因為SQL也是程序員必備技能，將慢查詢問題拍死在項目上線前夕。
如果覺得本文有所收獲，歡迎轉(zhuǎn)發(fā)分享。
參考資料：
MySQL官網(wǎng)
https://www.cnblogs.com/songwenjie/p/9402295.html
https://www.cnblogs.com/phpdragon/p/8231533.html
