Mysql優化基礎之Explain工具
欄位解釋
- id：代表sql中查詢語句的序列號，序列號越大則執行的優先順序越高，序號一樣誰在前誰先執行。id為null則最後執行
- select_type：查詢類型，表示當前被分析的sql語句的查詢的複雜度。這個欄位有多個值。
- SIMPLE：表示簡單查詢
- PRIMARY：表示複雜查詢中的最外層的select查詢語句
- SUBQUERY：表是子查詢語句 跟在select 關鍵字後面的select查詢語句；
- derived： 派生查詢，跟在一個select查詢語句的from關鍵字後面的select查詢語句
- table：表示當前訪問的表的名稱。
- partitions：返回的是數據分區的資訊（不常用）
- type：這個欄位決定mysql如何查找表中的數據，查找數據記錄的大概範圍。
- possible_keys：這個欄位顯示的是sql在查詢時可能使用到的索引，但是不一定真的使用，只是一種可能。
如果這一列有值，但是key列為null，那是因為mysql覺得可能會使用索引，但是又因為表中的數據很少，使用索引反而沒有全表掃描效率高，那麼mysql就不會使用索引查找
該列如果是NULL，則沒有相關的索引，考慮where條件語句的欄位加索引
- key：SQL執行中真正用到的索引欄位。
- key_len：用到的索引欄位的長度，通過這個欄位可以顯示具體使用到了索引欄位中的哪些列（主要針對組合索引）
- ref：表示那些列或常量被用於查找索引列上的值
- rows：表示在查詢過程中檢索了多少列 但是並不一定就是返回這麼多列數據。
- Extra：展示一些額外資訊，標識著表的索引使用情況。
TYPE詳解
有以下幾張表的情況：
-- 演員表
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','20 17‐12‐22 15:27:18'), (3,'c','2017‐12‐22 15:27:18');
-- 電影表
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');
-- 演員和電影中間表
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);
從好到差的順序為：system > const > eq_ref > ref > range > index > all; 一般來說我們優化到range就可以了 最好到ref
-
null：type欄位的值如果為null，那麼表示當前的查詢語句不需要訪問表，只需要從索引樹中就可以獲取我們需要的數據
explain select id from actor where id =1; -
system/const：用戶主鍵索引或者唯一索引查詢時，只能匹配1條數據 一般可以對sql查詢語句優化成一個常量，那麼type一般就是system或者const，system是const的一個特例。
explain select * from (select * from film where id = 1) tmp; -
eq_ref：在進行連接查詢時，例如left join 時，如果是使用主鍵索引或者唯一索引連接查詢 ，結果返回一條數據，則type的值為一般為eq_ref。
-
ref：相比較eq_ref,不使用主鍵索引或者唯一索引，使用的是普通索引或者唯一索引的部分前綴，索引與一個值進行比較後可能獲取到多個符合條件的行，不在是唯一的行了。
唯一索引的部分前綴：組合索引是唯一索引的一類，這裡指組合索引的前綴部分入組合索引a_b_c，那麼a和a_b是唯一索引a_b_c的前綴部分
-
range：通常使用範圍查找，例如between，in，<,>,>=等使用索引進行範圍檢索。
explain select * from film where id >2; -
index：掃描索引樹就能獲取到的數據，一般是掃描二級索引，並且不會從根節點掃描，一般直接掃描二級索引的葉子節點，速度比較慢。因為二級索引葉子節點不保存表中其他欄位數據 只保存主鍵，所以二級索引還是比較小的，掃描速度相比All還是很快的。這裡用到了覆蓋索引，什麼是覆蓋索引：可以直接遍歷索引樹就能獲取數據叫做覆蓋索引。這裡遍歷name索引樹就可以獲取到主鍵id的值就是覆蓋索引。
explain select id from film ; -
ALL:這是一種效率最低的type，需要掃描主鍵索引樹的葉子節點，獲取數據是表中其他列的數據，即全表掃描（掃描主索引樹的葉子節點）。
explain select remark from film ;
key_len計算
字元串
- char(n)：n位元組長度
- varchar(n)：如果是utf-8，則長度 3n + 2 位元組，加的2位元組用來存儲字元串長度
數值類型
- tinyint：1位元組
- smallint：2位元組
- int：4位元組
- bigint：8位元組
時間類型
- date：3位元組
- timestamp：4位元組
- datetime：8位元組
注意：如果欄位允許為 NULL，需要多1位元組記錄是否為 NULL
假如為某個表的一下三個欄位建立組合索引（name_age_phone）
namevarchar(50) DEFAULT NULL COMMENT ‘姓名’,
ageint(11) DEFAULT NULL COMMENT ‘年齡’,
phonevarchar(12) DEFAULT NULL,則完全使用索引的key_len計算為：key_len=name(50*3+2+1=153)+age(4+1)+phone(12*3+2+1=39)
