Mysql安裝文檔參考：
Explain工具介紹
使用EXPLAIN關鍵字可以模拟優化器執行SQL語句，分析你的查詢語句或是結構的性能瓶頸，在select語句之前增加 explain 關鍵字，MySQL 會在查詢上設定一個标記，執行查詢會傳回執行計劃的資訊，而不是執行這條SQL
注意：如果from中包含子查詢，仍會執行該子查詢，将結果放入臨時表中
Explain分析示例
參考官方文檔：
示例表：
DROP TABLE IF EXISTS `actor`;
CREATE TABLE `actor` (
`id` int(11) NOT NULL,
`name` varchar(45) DEFAULT NULL,
`update_time` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `actor` (`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 `film`;
CREATE TABLE `film` (
`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 `film` (`id`, `name`) VALUES (3,'film0'),(1,'film1'),(2,'film2');
DROP TABLE IF EXISTS `film_actor`;
CREATE TABLE `film_actor` (
`id` int(11) NOT NULL,
`film_id` int(11) NOT NULL,
`actor_id` int(11) NOT NULL,
`remark` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_film_actor_id` (`film_id`,`actor_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `film_actor` (`id`, `film_id`, `actor_id`) VALUES (1,1,1),(2,1,2),(3,2,1); mysql> explain select * from actor; 
在查詢中的每個表會輸出一行，如果有兩個表通過join連接配接查詢，那麼會輸出兩行
explain 兩個變種
1) explain extended：會在explain的基礎上額外提供一些查詢優化的資訊。緊随其後通過show warnings指令可以得到優化後的查詢語句，進而看出優化器優化了什麼。額外還有 filtered 列，是一個半分比的值，rows * filtered/100可以估算出将要和explain中前一個表進行連接配接的行數 (前一個表指explain中的id值比目前表id值小的表) 。
mysql> explain extended select * from film where id = 1; 
mysql> show warnings; 2) explain partitions：相比explain多了個partitions字段，如果查詢是基于分區表的話，會顯示查詢将通路的分區。
explain中的列
接下來我們将展示explain中每個列的資訊。
1. id列
id列的編号是select的序列号，有幾個select就有幾個id，并且id的順序是按select出現的順序增長的。 id列越大執行優先級越高，id相同則從上往下執行，id為NULL最後執行。
2. select_type列
select_type 表示對應行是簡單還是複雜的查詢。
1) simple：簡單查詢。查詢不包含子查詢和union
mysql> explain select * from film where id = 2; 2) primary：複雜查詢中最外層的select
3) subquery：包含在 select 中的子查詢(不在from子句中)
4) derived：包含在 from子句中的子查詢。MySQL會将結果存放在一個臨時表中，也稱為派生表(derived的英文含義)
用這個例子來了解 primary、subquery和derived 類型
mysql> set session optimizer_switch= 'derived_merge=off '; #關閉mysql5.7新特性對衍生表的合 并優化
mysql> explain select (select 1 from actor where id = 1) from (select * from film where id = 1) der; mysql> set session optimizer_switch='derived_merge=on'; #還原預設配置 5) union：在union中的第二個和随後的select
mysql> explain select 1 union all select 1; 3. table列
這一清單示explain的一行正在通路哪個表。
當 from 子句中有子查詢時，table列是<derivenN>格式，表示目前查詢依賴 id=N的查詢，于是先執行id=N的查詢。
當有union時， UNION RESULT的table列的值為<union1,2>，1和2表示參與union的select 行id。
4. type列
這一清單示關聯類型或通路類型，即MySQL決定如何查找表中的行，查找資料行記錄的大概範圍。 依次從最優到最差分别為：system > const > eq_ref > ref > range > index > ALL
一般來說，得保證查詢達到range級别，最好達到ref
NULL：mysql能夠在優化階段分解查詢語句，在執行階段用不着再通路表或索引。例如：在索引列中選取最小值，可以單獨查找索引來完成，不需要在執行時通路表
mysql> explain select min(id) from film; const, system：mysql能對查詢的某部分進行優化并将其轉化成一個常量(可以看show warnings 的結果)。用于primary key或unique key的所有列與常數比較時，是以表最多有一個比對行，讀取1次，速度比較快。system是 const的特例，表裡隻有一條元組比對時為system
mysql> explain extended select * from (select * from film where id = 1) tmp; mysql> show warnings; eq_ref：primary key或unique key索引的所有部分被連接配接使用，最多隻會傳回一條符合條件的記錄。這可能是在 const 之外最好的聯接類型了，簡單的select查詢不會出現這種type。
mysql> explain select * from film_actor left join film on film_actor.film_id = film.id; ref：相比eq_ref，不使用唯一索引，而是使用普通索引或者唯一性索引的部分字首，索引要和某個值相比較，可能會 找到多個符合條件的行。
1. 簡單select查詢，name是普通索引 (非唯一索引)
mysql> explain select * from film where name = 'film1'; 2.關聯表查詢，idx_film_actor_id是film_id和actor_id的聯合索引，這裡使用到了film_actor的左邊字首film_id部分。
mysql> explain select film_id from film left join film_actor on film.id = film_actor.fi lm_id; range：範圍掃描通常出現在in(), between , > , <, > = 等操作中。使用一個索引來檢索給定範圍的行。
mysql> explain select * from actor where id > 1; index：掃描全索引就能拿到結果，一般是掃描某個二級索引，這種掃描不會從索引樹根節點開始快速查找，而是直接 對二級索引的葉子節點周遊和掃描，速度還是比較慢的，這種查詢一般為使用覆寫索引，二級索引一般比較小，是以這種通常比ALL快一些。
mysql> explain select * from film; ALL：即全表掃描，掃描你的聚簇索引的所有葉子節點。通常情況下這需要增加索引來進行優化了。
mysql> explain select * from actor; 5. possible_keys列
這一列顯示查詢可能使用哪些索引來查找。
explain時可能出現possible_keys有列 ，而key顯示NULL的情況，這種情況是因為表中資料不多，mysql認為索引 對此查詢幫助不大，選擇了全表查詢。
如果該列是NULL，則沒有相關的索引。在這種情況下，可以通過檢查where子句看是否可以創造一個适當的索引來提 高查詢性能，然後用explain檢視效果。
6. key列
這一列顯示mysql實際采用哪個索引來優化對該表的通路。
如果沒有使用索引，則該列是NULL。如果想強制mysql使用或忽視possible_keys列中的索引，在查詢中使用force index、ignore index。
7. key_len列
這一列顯示了mysql在索引裡使用的位元組數，通過這個值可以算出具體使用了索引中的哪些列。
舉例來說，film_actor的聯合索引idx_film_actor_id由film_id和actor_id兩個int列組成，并且每個int是4位元組。通過結果中的key_len=4可推斷出查詢使用了第一個列：film_id列來執行索引查找。
mysql> explain select * from film_actor where film_id = 2; key_len計算規則如下：
- 字元串，char(n)和varchar(n)，5.0.3以後版本中，n均代表字元數，而不是位元組數，如果是utf-8，一個數字或字母占1個位元組，一個漢字占3個位元組
- char(n)：如果存漢字長度就是3n位元組
- varchar(n)：如果存漢字則長度是 3n + 2 位元組，加的2位元組用來存儲字元串長度， 因為varchar是變長字元串
- 數值類型
- tinyint： 1位元組
- smallint：2位元組
- int：4位元組
- bigint：8位元組
- 時間類型
- date：3位元組
- timestamp：4位元組
- datetime：8位元組
- 如果字段允許為NULL，需要1位元組記錄是否為 NULL
索引最大長度是768位元組，當字元串過長時，mysql會做一個類似左字首索引的處理，将前半部分的字元提取出來做索引。
8. ref列
這一列顯示了在key列記錄的索引中，表查找值所用到的列或常量，常見的有：const(常量)，字段名(例：film.id)
9. rows列
這一列是mysql估計要讀取并檢測的行數，注意這個不是結果集裡的行數。
10. Extra列
這一列展示的是額外資訊。常見的重要值如下：
1) Using index：使用覆寫索引
覆寫索引定義：mysql執行計劃explain結果裡的key有使用索引，如果select後面查詢的字段都可以從這個索引的樹中擷取，這種情況一般可以說是用到了覆寫索引，extra裡一般都有using index；覆寫索引一般針對的是輔助索引，整個查詢結果隻通過輔助索引就能拿到結果，不需要通過輔助索引樹找到主鍵，再通過主鍵去主鍵索引樹裡擷取其它字段值
mysql> explain select film_id from film_actor where film_id = 1; 2) Using where：使用where語句來處理結果，并且查詢的列未被索引覆寫
mysql> explain select * from actor where name = 'a'; 3) Using index condition：查詢的列不完全被索引覆寫，where條件中是一個前導列的範圍；
mysql> explain select * from film_actor where film_id > 1; 4) Using temporary：mysql需要建立一張臨時表來處理查詢。 出現這種情況一般是要進行優化的，首先是想到用索引來優化。
1. actor.name沒有索引，此時建立了張臨時表來distinct
mysql> explain select distinct name from actor; 2. film.name建立了idx_name索引，此時查詢時extra是using index,沒有用臨時表
mysql> explain select distinct name from film; 5) Using filesort：将用外部排序而不是索引排序，資料較小時從記憶體排序，否則需要在磁盤完成排序。這種情況下一 般也是要考慮使用索引來優化的。
1. actor.name未建立索引，會浏覽actor整個表，儲存排序關鍵字name和對應的id，然後排序name并檢索行記錄
mysql> explain select * from actor order by name; 2. film.name建立了idx_name索引,此時查詢時extra是using index
mysql> explain select * from film order by name; 6) Select tables optimized away：使用某些聚合函數(比如max、min)來通路存在索引的某個字段時
mysql> explain select min(id) from film; 索引最佳實踐
示例表：
CREATE TABLE `employees` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名',
`age` int(11) NOT NULL DEFAULT '0' COMMENT '年齡',
`position` varchar(20) NOT NULL DEFAULT '' COMMENT '職位',
`hire_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入職時間',
PRIMARY KEY (`id`),
KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COMMENT='員工記錄表';
INSERT INTO employees(name,age,position,hire_time) VALUES('LiLei',22,'manager',NOW());
INSERT INTO employees(name,age,position,hire_time) VALUES('HanMeimei',23,'dev',NOW());
INSERT INTO employees(name,age,position,hire_time) VALUES('Lucy',23,'dev',NOW()); 1.全值比對
EXPLAIN SELECT * FROM employees WHERE name= 'LiLei'; EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age = 22; XPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age = 22 AND position ='manager'; 2.最左字首法則
如果索引了多列，要遵守最左字首法則。指的是查詢從索引的最左前列開始并且不跳過索引中的列。
EXPLAIN SELECT * FROM employees WHERE name = 'Bill' and age = 31;
EXPLAIN SELECT * FROM employees WHERE age = 30 AND position = 'dev';
EXPLAIN SELECT * FROM employees WHERE position = 'manager'; 3.不在索引列上做任何操作(計算、函數、(自動or手動)類型轉換)，會導緻索引失效而轉向全表掃描
EXPLAIN SELECT * FROM employees WHERE name = 'LiLei';
EXPLAIN SELECT * FROM employees WHERE left(name,3) = 'LiLei'; 給hire_time增加一個普通索引：
ALTER TABLE `employees` ADD INDEX `idx_hire_time` (`hire_time`) USING BTREE ;
EXPLAIN select * from employees where date(hire_time) ='2018‐09‐30'; 轉化為日期範圍查詢，有可能會走索引：
EXPLAIN select * from employees where hire_time >='2018‐09‐30 00:00:00' and hire_time <='2018‐09‐30 23:59:59'; 還原最初索引狀态
ALTER TABLE `employees` DROP INDEX `idx_hire_time`; 4.存儲引擎不能使用索引中範圍條件右邊的列
EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age = 22 AND position ='manager';
EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age > 22 AND position ='manager'; 5.盡量使用覆寫索引 (隻通路索引的查詢 (索引列包含查詢列) ) ，減少 select * 語句
XPLAIN SELECT name,age FROM employees WHERE name= 'LiLei' AND age = 23 AND position ='manager'; EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age = 23 AND position ='manager'; 6.mysql在使用不等于(!=或者<>)，not in ，not exists的時候無法使用索引會導緻全表掃描
< 小于、 > 大于、 <=、 >= 這些 ，mysql内部優化器會根據檢索比例、表大小等多個因素整體評估是否使用索引
EXPLAIN SELECT * FROM employees WHERE name != 'LiLei'; 7.is null,is not null一般情況下也無法使用索引
EXPLAIN SELECT * FROM employees WHERE name is null 8.like以通配符開頭 ( '$abc...') mysql索引失效會變成全表掃描操作
EXPLAIN SELECT * FROM employees WHERE name like '%Lei' EXPLAIN SELECT * FROM employees WHERE name like 'Lei%' 問題：解決like'%字元串%'索引不被使用的方法？
a) 使用覆寫索引，查詢字段必須是建立覆寫索引字段
EXPLAIN SELECT name,age,position FROM employees WHERE name like '%Lei% '; b) 如果不能使用覆寫索引則可能需要借助搜尋引擎
9.字元串不加單引号索引失效
EXPLAIN SELECT * FROM employees WHERE name = '1000';
EXPLAIN SELECT * FROM employees WHERE name = 1000; 10.少用or或in，用它查詢時，mysql不一定使用索引，mysql内部優化器會根據檢索比例、表大小等多個因素整體評估是否使用索引，詳見範圍查詢優化
EXPLAIN SELECT * FROM employees WHERE name = 'LiLei ' or name = 'HanMeimei '; 11.範圍查詢優化
給年齡添加單值索引
ALTER TABLE `employees` ADD INDEX `idx_age` (`age`) USING BTREE ;
explain select * from employees where age >=1 and age <=2000; 沒走索引原因：mysql内部優化器會根據檢索比例、表大小等多個因素整體評估是否使用索引。比如這個例子，可能是 由于單次資料量查詢過大導緻優化器最終選擇不走索引
優化方法：可以将大的範圍拆分成多個小範圍
explain select * from employees where age >=1 and age <=1000;
explain select * from employees where age >=1001 and age <=2000; 還原最初索引狀态
ALTER TABLE `employees` DROP INDEX `idx_age`; 索引使用總結：
like KK%相當于=常量 ，%KK和%KK% 相當于範圍
‐‐ mysql5.7關閉ONLY_FULL_GROUP_BY報錯
2 select version(), @@sql_mode;SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));