天天看點

二、一文搞懂Mysql執行計劃

作者:Java架構之路

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;           
二、一文搞懂Mysql執行計劃

在查詢中的每個表會輸出一行,如果有兩個表通過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執行計劃
mysql> show warnings;           
二、一文搞懂Mysql執行計劃

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;           
二、一文搞懂Mysql執行計劃

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執行計劃
mysql> set session optimizer_switch='derived_merge=on'; #還原預設配置           

5) union:在union中的第二個和随後的select

mysql> explain select 1 union all select 1;           
二、一文搞懂Mysql執行計劃

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;           
二、一文搞懂Mysql執行計劃

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執行計劃
mysql> show warnings;           
二、一文搞懂Mysql執行計劃

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;           
二、一文搞懂Mysql執行計劃

ref:相比eq_ref,不使用唯一索引,而是使用普通索引或者唯一性索引的部分字首,索引要和某個值相比較,可能會 找到多個符合條件的行。

1. 簡單select查詢,name是普通索引 (非唯一索引)

mysql> explain select * from film where name = 'film1';           
二、一文搞懂Mysql執行計劃

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;           
二、一文搞懂Mysql執行計劃

range:範圍掃描通常出現在in(), between , > , <, > = 等操作中。使用一個索引來檢索給定範圍的行。

mysql> explain select * from actor where id > 1;           
二、一文搞懂Mysql執行計劃

index:掃描全索引就能拿到結果,一般是掃描某個二級索引,這種掃描不會從索引樹根節點開始快速查找,而是直接 對二級索引的葉子節點周遊和掃描,速度還是比較慢的,這種查詢一般為使用覆寫索引,二級索引一般比較小,是以這種通常比ALL快一些。

mysql> explain select * from film;           
二、一文搞懂Mysql執行計劃

ALL:即全表掃描,掃描你的聚簇索引的所有葉子節點。通常情況下這需要增加索引來進行優化了。

mysql> explain select * from actor;           
二、一文搞懂Mysql執行計劃

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;           
二、一文搞懂Mysql執行計劃

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;           
二、一文搞懂Mysql執行計劃

2) Using where:使用where語句來處理結果,并且查詢的列未被索引覆寫

mysql> explain select * from actor where name = 'a';           
二、一文搞懂Mysql執行計劃

3) Using index condition:查詢的列不完全被索引覆寫,where條件中是一個前導列的範圍;

mysql> explain select * from film_actor where film_id > 1;           
二、一文搞懂Mysql執行計劃

4) Using temporary:mysql需要建立一張臨時表來處理查詢。 出現這種情況一般是要進行優化的,首先是想到用索引來優化。

1. actor.name沒有索引,此時建立了張臨時表來distinct

mysql> explain select distinct name from actor;           
二、一文搞懂Mysql執行計劃

2. film.name建立了idx_name索引,此時查詢時extra是using index,沒有用臨時表

mysql> explain select distinct name from film;           
二、一文搞懂Mysql執行計劃

5) Using filesort:将用外部排序而不是索引排序,資料較小時從記憶體排序,否則需要在磁盤完成排序。這種情況下一 般也是要考慮使用索引來優化的。

1. actor.name未建立索引,會浏覽actor整個表,儲存排序關鍵字name和對應的id,然後排序name并檢索行記錄

mysql> explain select * from actor order by name;           
二、一文搞懂Mysql執行計劃

2. film.name建立了idx_name索引,此時查詢時extra是using index

mysql> explain select * from film order by name;           
二、一文搞懂Mysql執行計劃

6) Select tables optimized away:使用某些聚合函數(比如max、min)來通路存在索引的某個字段時

mysql> explain select min(id) from film;           
二、一文搞懂Mysql執行計劃

索引最佳實踐

示例表:
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';           
二、一文搞懂Mysql執行計劃
EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age = 22;           
二、一文搞懂Mysql執行計劃
XPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age = 22 AND position ='manager';           
二、一文搞懂Mysql執行計劃

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';           
二、一文搞懂Mysql執行計劃

3.不在索引列上做任何操作(計算、函數、(自動or手動)類型轉換),會導緻索引失效而轉向全表掃描

EXPLAIN SELECT * FROM employees WHERE name = 'LiLei';
EXPLAIN SELECT * FROM employees WHERE left(name,3) = 'LiLei';           
二、一文搞懂Mysql執行計劃

給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';           
二、一文搞懂Mysql執行計劃

轉化為日期範圍查詢,有可能會走索引:

EXPLAIN select * from employees where hire_time >='2018‐09‐30 00:00:00' and hire_time <='2018‐09‐30 23:59:59';           
二、一文搞懂Mysql執行計劃

還原最初索引狀态

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';           
二、一文搞懂Mysql執行計劃

5.盡量使用覆寫索引 (隻通路索引的查詢 (索引列包含查詢列) ) ,減少 select * 語句

XPLAIN SELECT name,age FROM employees WHERE name= 'LiLei' AND age = 23 AND position ='manager';           
二、一文搞懂Mysql執行計劃
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           
二、一文搞懂Mysql執行計劃

8.like以通配符開頭 ( '$abc...') mysql索引失效會變成全表掃描操作

EXPLAIN SELECT * FROM employees WHERE name like '%Lei'           
二、一文搞懂Mysql執行計劃
EXPLAIN SELECT * FROM employees WHERE name like 'Lei%'           
二、一文搞懂Mysql執行計劃

問題:解決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;           
二、一文搞懂Mysql執行計劃

10.少用or或in,用它查詢時,mysql不一定使用索引,mysql内部優化器會根據檢索比例、表大小等多個因素整體評估是否使用索引,詳見範圍查詢優化

EXPLAIN SELECT * FROM employees WHERE name = 'LiLei ' or name = 'HanMeimei ';           
二、一文搞懂Mysql執行計劃

11.範圍查詢優化

給年齡添加單值索引

ALTER TABLE `employees` ADD INDEX `idx_age` (`age`) USING BTREE ; 
explain select * from employees where age >=1 and age <=2000;           
二、一文搞懂Mysql執行計劃

沒走索引原因:mysql内部優化器會根據檢索比例、表大小等多個因素整體評估是否使用索引。比如這個例子,可能是 由于單次資料量查詢過大導緻優化器最終選擇不走索引

優化方法:可以将大的範圍拆分成多個小範圍

explain select * from employees where age >=1 and age <=1000; 
explain select * from employees where age >=1001 and age <=2000;           
二、一文搞懂Mysql執行計劃

還原最初索引狀态

ALTER TABLE `employees` DROP INDEX `idx_age`;           

索引使用總結:

二、一文搞懂Mysql執行計劃

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',''));           

繼續閱讀