MySQL中EXPLAIN的/基本使用及字段詳解
一、介紹
官網(wǎng)介紹:
https://dev.mysql.com/doc/refman/5.7/en/explain-output.html
https://dev.mysql.com/doc/refman/8.0/en/explain-output.html
explain(執(zhí)行計(jì)劃),使用explain關(guān)鍵字可以模擬優(yōu)化器執(zhí)行sql查詢語句,從而知道MySQL是如何處理sql語句。
explain主要用于分析查詢語句或表結(jié)構(gòu)的性能瓶頸。
通過explain命令可以得到:
- – 表的讀取順序
- – 數(shù)據(jù)讀取操作的操作類型
- – 哪些索引可以使用
- – 哪些索引被實(shí)際使用
- – 表之間的引用
- – 每張表有多少行被優(yōu)化器查詢
EXPLAIN 或者 DESC命令獲取 MySQL 如何執(zhí)行 SELECT 語句的信息,包括在 SELECT 語句執(zhí)行過程中表如何連接和連接的順序。
版本情況
- MySQL 5.6.3以前只能EXPLAIN SELECT ;MYSQL 5.6.3以后就可以EXPLAIN SELECT,UPDATE,DELETE
- 在5.7以前的版本中,想要顯示partitions 需要使用explain partitions 命令;想要顯示filtered 需要使用explain extended 命令。在5.7版本后,默認(rèn)explain直接顯示partitions和filtered中的信息。
基本語法
EXPLAIN 或 DESCRIBE語句的語法形式如下:
EXPLAIN SELECT select_options
或者
DESCRIBE SELECT select_options
環(huán)境準(zhǔn)備:
CREATE DATABASE testexplain CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
use testexplain;
CREATE TABLE L1(id INT PRIMARY KEY AUTO_INCREMENT,title VARCHAR(100) ); CREATE TABLE L2(id INT PRIMARY KEY AUTO_INCREMENT,title VARCHAR(100) ); CREATE TABLE L3(id INT PRIMARY KEY AUTO_INCREMENT,title VARCHAR(100) ); CREATE TABLE L4(id INT PRIMARY KEY AUTO_INCREMENT,title VARCHAR(100) );
INSERT INTO L1(title) VALUES('test001'),('test002'),('test003'); INSERT INTO L2(title) VALUES('test004'),('test005'),('test006'); INSERT INTO L3(title) VALUES('test007'),('test008'),('test009'); INSERT INTO L4(title) VALUES('test010'),('test011'),('test012');
二、基本的使用
explain使用:explain/desc+sql語句,通過執(zhí)行explain可以獲得sql語句執(zhí)行的相關(guān)信息。
EXPLAIN SELECT * FROM L1,L2,L3 WHERE L1.id=L2.id AND L2.id = L3.id;
DESC SELECT * FROM L1,L2,L3 WHERE L1.id=L2.id AND L2.id = L3.id;
序號 | 字段 | 含義 |
---|---|---|
1 | id | 查詢的序列號,是一組數(shù)字,表示查詢中執(zhí)行 SELECT 子句或操作表的順序。 |
2 | select_type | 表示 SELECT 的類型。常見取值有 SIMPLE (簡單查詢,不包含子查詢或聯(lián)合查詢)、PRIMARY (主查詢,即最外層的查詢)、UNION (聯(lián)合查詢中的第二個(gè)或后續(xù)查詢)、SUBQUERY (子查詢)等。 |
3 | table | 表示正在訪問的表。 |
4 | partitions | 顯示匹配的分區(qū)信息,如果是非分區(qū)表則為 NULL 。 |
5 | type | 表示表的訪問類型,性能由好到差的順序?yàn)?nbsp;system → const → eq_ref → ref → ref_or_null → index_merge → unique_subquery → index_subquery → range → index → ALL 。訪問類型越靠前,性能越好。 |
6 | possible_keys | 表示查詢時(shí)可能使用的索引。 |
7 | key | 實(shí)際使用的索引。如果沒有使用索引,則顯示為 NULL 。 |
8 | key_len | 表示使用的索引的字節(jié)數(shù)。這個(gè)值越大,表示查詢中使用的索引字段越多。 |
9 | ref | 顯示索引的哪一列被用到,并且如果可能的話,是哪些列或常量被用于查找索引列中的值。 |
10 | rows | 估計(jì)要讀取的行數(shù),這個(gè)數(shù)字是一個(gè)估計(jì)值,不一定是精確的。 |
11 | filtered | 表示服務(wù)器根據(jù)查詢條件過濾的行百分比。 |
12 | Extra | 包含執(zhí)行查詢的額外信息,比如是否使用臨時(shí)表、是否進(jìn)行文件排序等。常見值有 Using index (使用了覆蓋索引)、Using where (使用了 WHERE 過濾條件)、Using temporary (使用了臨時(shí)表)和 Using filesort (使用了文件排序)等。 |
三、字段詳解
3.1、id字段
select查詢的序列號,包含一組數(shù)字,表示查詢中執(zhí)行select子句或操作表的順序
- id相同,執(zhí)行順序由上至下
EXPLAIN SELECT * FROM L1,L2,L3 WHERE L1.id=L2.id AND L2.id = L3.id;
- id不同,如果是子查詢,id的序號會(huì)遞增,id值越大優(yōu)先級越高,越先被執(zhí)行
EXPLAIN SELECT * FROM L2 WHERE id = (SELECT id FROM L1 WHERE id = (SELECT L3.id FROM L3 WHERE L3.title = 'test009'));
3.2、select_type 與 table字段
查詢類型,主要用于區(qū)別普通查詢,聯(lián)合查詢,子查詢等的復(fù)雜查詢
- simple : 簡單的select查詢,查詢中不包含子查詢或者UNION
EXPLAIN SELECT * FROM L1;
- primary : 查詢中若包含任何復(fù)雜的子部分,最外層查詢被標(biāo)記
EXPLAIN SELECT * FROM L2 WHERE id = (SELECT id FROM L1 WHERE id = (SELECT L3.id FROM L3 WHERE L3.title = 'test003'));
- subquery : 在select或where列表中包含了子查詢
EXPLAIN SELECT * FROM L2 WHERE L2.id = (SELECT id FROM L3 WHERE L3.title = 'test03');
- derived : 在from列表中包含的子查詢被標(biāo)記為derived(衍生),MySQL會(huì)遞歸執(zhí)行這些子查詢,把結(jié)果放到臨時(shí)表中
- union : 如果第二個(gè)select出現(xiàn)在UNION之后,則被標(biāo)記為UNION,如果union包含在from子句的子查詢中,外層select被標(biāo)記為derived
- union result : UNION 的結(jié)果
EXPLAIN SELECT * FROM L2 UNION SELECT * FROM L3;
3.3、partitions
分區(qū)表是將一個(gè)表的數(shù)據(jù)根據(jù)某個(gè)字段的值分成多個(gè)分區(qū)來存儲的,這樣查詢時(shí)可以提高效率。
查詢時(shí)匹配到的分區(qū)信息,對于非分區(qū)表值為NULL ,當(dāng)查詢的是分區(qū)表時(shí), partitions 顯示分區(qū)表命中的分區(qū)情況。
對于非分區(qū)表(例如原始的 L1
表),partitions
字段會(huì)顯示 NULL
:
EXPLAIN SELECT * FROM L1 WHERE id = 1;
我們以 L1
表為例,將它根據(jù) id
字段進(jìn)行分區(qū):
CREATE TABLE L1_partitioned ( id INT PRIMARY KEY AUTO_INCREMENT, title VARCHAR(100) ) PARTITION BY RANGE (id) ( PARTITION p0 VALUES LESS THAN (2), PARTITION p1 VALUES LESS THAN (4), PARTITION p2 VALUES LESS THAN (6) );
INSERT INTO L1_partitioned(title) VALUES('test001'),('test002'),('test003'),('test004'),('test005');
這個(gè)表會(huì)根據(jù) id
的值分成 3 個(gè)分區(qū):
p0
分區(qū)存儲id
小于 2 的數(shù)據(jù)p1
分區(qū)存儲id
小于 4 的數(shù)據(jù)p2
分區(qū)存儲id
小于 6 的數(shù)據(jù)
使用 EXPLAIN
查看查詢的分區(qū)命中情況:
EXPLAIN SELECT * FROM L1_partitioned WHERE id = 1;
此查詢會(huì)顯示 partitions
字段的值為 p0
,因?yàn)?nbsp;id=1
的記錄被存儲在 p0
分區(qū)中。
EXPLAIN SELECT * FROM L1_partitioned WHERE id = 3;
此查詢會(huì)顯示 partitions
字段的值為 p1
,因?yàn)?nbsp;id=3
的記錄被存儲在 p1
分區(qū)中。
當(dāng)查詢條件跨越多個(gè)分區(qū)時(shí),EXPLAIN
會(huì)顯示命中的多個(gè)分區(qū):
EXPLAIN SELECT * FROM L1_partitioned WHERE id BETWEEN 1 AND 5;
3.4、type字段
type顯示的是連接類型,是較為重要的一個(gè)指標(biāo)。下面給出各種連接類型,按照從最佳類型到最壞類型進(jìn)行排序:
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge >unique_subquery > index_subquery > range > index > ALL
-- 簡化 system > const > eq_ref > ref > range > index > ALL
- system : 表僅有一行 (等于系統(tǒng)表)。這是const連接類型的一個(gè)特例,很少出現(xiàn)。
- const : 表示通過索引 一次就找到了, const用于比較 primary key 或者 unique 索引. 因?yàn)橹黄ヅ湟恍袛?shù)據(jù),所以如果將主鍵 放在 where條件中, MySQL就能將該查詢轉(zhuǎn)換為一個(gè)常量
EXPLAIN SELECT * FROM L1 WHERE L1.id = 1;
- eq_ref : 唯一性索引掃描,對于每個(gè)索引鍵,表中只有一條記錄與之匹配. 常見與主鍵或唯一索引掃描
EXPLAIN SELECT * FROM L1 ,L2 WHERE L1.id = L2.id ;
ref : 非唯一性索引掃描, 返回匹配某個(gè)單獨(dú)值的所有行, 本質(zhì)上也是一種索引訪問, 它返回所有匹配某個(gè)單獨(dú)值的行, 這是比較常見連接類型.
未加索引之前
EXPLAIN SELECT * FROM L1 ,L2 WHERE L1.title = L2.title ;
加索引之后
CREATE INDEX idx_title ON L2(title);
EXPLAIN SELECT * FROM L1 ,L2 WHERE L1.title = L2.title ;
range : 只檢索給定范圍的行,使用一個(gè)索引來選擇行。
EXPLAIN SELECT * FROM L1 WHERE L1.id > 10;
EXPLAIN SELECT * FROM L1 WHERE L1.id IN (1,2);
key顯示使用了哪個(gè)索引. where 子句后面 使用 between 、< 、> 、in 等查詢, 這種范圍查詢要比全表掃描好
index : 出現(xiàn)index 是 SQL 使用了索引, 但是沒有通過索引進(jìn)行過濾,一般是使用了索引進(jìn)行排序分組
EXPLAIN SELECT * FROM L1 ORDER BY id;
- ALL : 對于每個(gè)來自于先前的表的行組合,進(jìn)行完整的表掃描。
EXPLAIN SELECT * FROM L1;
一般來說,需要保證查詢至少達(dá)到 range級別,最好能到ref
3.5、possible_keys 與 key字段
- possible_keys
- 顯示可能應(yīng)用到這張表上的索引, 一個(gè)或者多個(gè). 查詢涉及到的字段上若存在索引, 則該索引將被列出, 但不一定被查詢實(shí)際使用.
- 實(shí)際使用的索引,若為null,則沒有使用到索引。(兩種可能,1.沒建立索引, 2.建立索引,但索引失效)。查詢中若使用了覆蓋索引,則該索引僅出現(xiàn)在key列表中。
- key
- 實(shí)際使用的索引,若為null,則沒有使用到索引。(兩種可能,1.沒建立索引, 2.建立索引,但索引失效)。查詢中若使用了覆蓋索引,則該索引僅出現(xiàn)在key列表中。
- 覆蓋索引:一個(gè)索引包含(或覆蓋)所有需要查詢的字段的值,通過查詢索引就可以獲取到字段值
- 理論上沒有使用索引,但實(shí)際上使用了
EXPLAIN SELECT L1.id FROM L1;
- 理論和實(shí)際上都沒有使用索引
EXPLAIN SELECT * FROM L1 WHERE title = 'test01';
- 理論和實(shí)際上都使用了索引
EXPLAIN SELECT * FROM L2 WHERE title = 'test02';
3.6、key_len字段
表示索引中使用的字節(jié)數(shù), 可以通過該列計(jì)算查詢中使用索引的長度.
key_len 字段能夠幫你檢查是否充分利用了索引 ken_len 越長, 說明索引使用的越充分
key_len表示使用的索引長度,key_len可以衡量索引的好壞,key_len越小 索引效果越好
上述的這兩句話是否存在矛盾呢,我們該怎么理解呢?
第一句:
key_len
越長,說明索引使用得越充分解釋:
key_len
表示在查詢中使用的索引字節(jié)數(shù)。它反映了查詢條件中實(shí)際使用了索引的多少。例如,假設(shè)有一個(gè)復(fù)合索引(例如
index_a_b_c
),它包含三個(gè)字段a, b, c
。如果你執(zhí)行的查詢只使用了a
字段進(jìn)行篩選,那么key_len
可能只包含字段a
的長度。如果查詢使用了a
和b
兩個(gè)字段進(jìn)行篩選,key_len
會(huì)增加,以反映更多的索引字段被使用。因此,當(dāng)
key_len
較長時(shí),意味著查詢充分利用了索引的多個(gè)部分,這通??梢蕴岣卟樵冃省?/p>第二句:
key_len
越小,索引效果越好解釋:
這句話強(qiáng)調(diào)了索引的選擇性和效率。
key_len
越小,表示查詢使用的索引部分越少,也可能意味著查詢的目標(biāo)更加精準(zhǔn),過濾的行數(shù)越少。如果一個(gè)查詢只需使用索引的前幾列(即
key_len
較?。?,并且可以快速過濾掉大部分不相關(guān)的行,那么該查詢的效率通常會(huì)更高。在某些情況下,使用較小的
key_len
可能會(huì)比使用較大的key_len
更有效,因?yàn)檫@減少了不必要的索引掃描(特別是當(dāng)大部分行都匹配前面的字段時(shí))。如何綜合理解這兩句話
這兩句話并不矛盾,而是從不同的角度解釋了
key_len
的作用:充分利用索引:當(dāng)你希望盡可能利用復(fù)合索引的多個(gè)字段時(shí),較大的
key_len
是有利的,因?yàn)樗砻鞑樵儣l件使用了索引的多個(gè)部分,從而可能減少全表掃描的需求。索引的效率:另一方面,較小的
key_len
可能意味著查詢條件已經(jīng)足夠過濾掉大多數(shù)不匹配的行,從而更快地找到所需的記錄。實(shí)際應(yīng)用中的考量
- 復(fù)合索引:如果你的查詢經(jīng)常使用復(fù)合索引的前幾個(gè)字段,而不使用全部字段,那么你可能希望
key_len
較小,這樣查詢效率可能更高,因?yàn)閿?shù)據(jù)庫引擎不需要掃描索引的所有部分。- 單字段索引:如果你有一個(gè)單字段索引,那么
key_len
的大小主要取決于這個(gè)字段的類型。對于簡單的查詢,key_len
較小可能是好事。總結(jié)來說,
key_len
并不是越大或越小越好,而是要根據(jù)查詢的具體情況來衡量。當(dāng)key_len
充分利用了索引的關(guān)鍵字段,并且有效過濾數(shù)據(jù)時(shí),這通常是一個(gè)高效的查詢設(shè)計(jì)。
SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; DROP TABLE IF EXISTS `user`; CREATE TABLE `user` ( `id` bigint NOT NULL AUTO_INCREMENT, `name` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `age` int NULL DEFAULT NULL, `sex` char(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `create_time` datetime NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE, INDEX `idx_name`(`name` ASC) USING BTREE, INDEX `idx_age`(`age` ASC) USING BTREE, INDEX `idx_sex`(`sex` ASC) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; INSERT INTO `user` VALUES (1, 'tom', 18, '男', '2024-08-17 10:09:00'); INSERT INTO `user` VALUES (2, 'zimu', 18, '男', '2024-08-07 10:09:30');
- 使用explain 進(jìn)行測試
列類型 | 是否為空 | 長度 | key_len | 備注 |
---|---|---|---|---|
tinyint | 允許Null | 1 | key_len = 1 + 1 | 允許NULL,key_len長度加1 |
tinyint not null | 不允許Null | 1 | key_len = 1 | 不允許NULL |
int | 允許Null | 4 | key_len = 4 + 1 | 允許NULL,key_len長度加1 |
int not null | 不允許Null | 4 | key_len = 4 | 不允許NULL |
bigint | 允許Null | 8 | key_len = 8 + 1 | 允許NULL,key_len長度加1 |
bigint not null | 不允許Null | 8 | key_len = 8 | 不允許NULL |
char(1) | 允許Null | utf8mb4=4, utf8=3, gbk=2 | key_len = 1*3 + 1 | 允許NULL,字符集utf8,key_len長度加1 |
char(1) not null | 不允許Null | utf8mb4=4, utf8=3, gbk=2 | key_len = 1*3 | 不允許NULL,字符集utf8 |
varchar(10) | 允許Null | utf8mb4=4, utf8=3, gbk=2 | key_len = 10*3 + 2 + 1 | 動(dòng)態(tài)列類型,key_len長度加2,允許NULL,key_len長度加1 |
varchar(10) not null | 不允許Null | utf8mb4=4, utf8=3, gbk=2 | key_len = 10*3 + 2 | 動(dòng)態(tài)列類型,key_len長度加2 |
id字段類型為bigint,長度為8,id為主鍵,不允許Null ,key_len = 8 。
EXPLAIN select * FROM user WHERE id = 1;
- name的字段類型是varchar(10),允許Null,字符編碼是utf8,一個(gè)字符占用3個(gè)字節(jié),varchar為動(dòng)態(tài)類型,key長度加2,key_len = 10 * 3 + 2 + 1 = 33 。
EXPLAIN select * FROM user WHERE name = 'tom';
聯(lián)合索引key_len計(jì)算
我們刪除user表其他輔助索引,建立一個(gè)聯(lián)合索引
ALTER TABLE user DROP INDEX `idx_name`, DROP INDEX `idx_age`, DROP INDEX `idx_sex`;
ALTER TABLE user ADD INDEX `idx_name_age`(`name`, `age`);
1、部分索引生效的情況
我們使用name進(jìn)行查詢
EXPLAIN select * FROM user WHERE name = 'tom';
由于聯(lián)合索引,根據(jù)最左匹配原則,使用到索引只有name這一列,name的字段類型是varchar(10),允許Null,字符編碼是utf8,一個(gè)字符占用3個(gè)字節(jié),varchar為動(dòng)態(tài)類型,key長度加2,key_len = 10 * 3+2 + 1 = 33 。
2、聯(lián)合索引完全使用索引的情況
EXPLAIN select * FROM user WHERE name = '張三' AND age = 19;
由于聯(lián)合索引,使用到(name,age)聯(lián)合索引,name的字段類型是varchar(10),允許Null,字符編碼是utf8,一個(gè)字符占用3個(gè)字節(jié),varchar為動(dòng)態(tài)類型,key長度加2,key_len = 10 * 3 + 2 + 1 = 33 ,age的字段類型是int,長度為4,允許Null ,key_len = 4 + 1 = 5 。聯(lián)合索引的key_len 為 key_len = 33+5 = 38。
3.7、ref 字段
顯示索引的哪一列被使用了,如果可能的話,是一個(gè)常數(shù)。哪些列或常量被用于查找索引列上的值
- L1.id=‘1’; 1是常量 , ref = const
EXPLAIN SELECT * FROM L1 WHERE L1.id='1';
- L2表被關(guān)聯(lián)查詢的時(shí)候,使用了主鍵索引, 而值使用的是驅(qū)動(dòng)表(執(zhí)行計(jì)劃中靠前的表是驅(qū)動(dòng)表)L1表的ID, 所以 ref = test_explain.L1.id
EXPLAIN SELECT * FROM L1 LEFT JOIN L2 ON L1.id = L2.id WHERE L1.title ='test01';
什么是驅(qū)動(dòng)表 ?
- 多表關(guān)聯(lián)查詢時(shí),第一個(gè)被處理的表就是驅(qū)動(dòng)表,使用驅(qū)動(dòng)表去關(guān)聯(lián)其他表.
- 驅(qū)動(dòng)表的確定非常的關(guān)鍵,會(huì)直接影響多表關(guān)聯(lián)的順序,也決定后續(xù)關(guān)聯(lián)查詢的性能
驅(qū)動(dòng)表的選擇要遵循一個(gè)規(guī)則:
在對最終的結(jié)果集沒有影響的前提下,優(yōu)先選擇結(jié)果集最小的那張表作為驅(qū)動(dòng)表
3.8、rows 字段
表示MySQL根據(jù)表統(tǒng)計(jì)信息及索引選用情況,估算的找到所需的記錄所需要讀取的行數(shù);越少越好
- 使用like 查詢,會(huì)產(chǎn)生全表掃描, L2中有3條記錄,就需要讀取3條記錄進(jìn)行查找
EXPLAIN SELECT * FROM L1,L2 WHERE L1.id = L2.id AND L2.title LIKE '%tes%';
- 如果使用等值查詢, 則可以直接找到要查詢的記錄,返回即可,所以只需要讀取一條
EXPLAIN SELECT * FROM L1,L2 WHERE L1.id = L2.id AND L2.title = 'test03';
總結(jié): 當(dāng)我們需要優(yōu)化一個(gè)SQL語句的時(shí)候,我們需要知道該SQL的執(zhí)行計(jì)劃,比如是全表掃描,還是索引掃描; 使用explain 關(guān)鍵字可以模擬優(yōu)化器執(zhí)行sql 語句,從而知道m(xù)ysql 是如何處理sql 語句的,方便我們開發(fā)人員有針對性的對SQL進(jìn)行優(yōu)化.
表的讀取順序。(對應(yīng)id)
數(shù)據(jù)讀取操作的操作類型。(對應(yīng)select_type)
哪些索引可以使用。(對應(yīng)possible_keys)
哪些索引被實(shí)際使用。(對應(yīng)key)
每張表有多少行被優(yōu)化器查詢。(對應(yīng)rows)
評估sql的質(zhì)量與效率 (對應(yīng)type)
3.9、filtered 字段
它指返回結(jié)果的行占需要讀到的行(rows列的值)的百分比
3.9、extra 字段
Extra 是 EXPLAIN 輸出中另外一個(gè)很重要的列,該列顯示MySQL在查詢過程中的一些詳細(xì)信息
CREATE TABLE users ( uid INT PRIMARY KEY AUTO_INCREMENT, uname VARCHAR(20), age INT(11) ); INSERT INTO users VALUES(NULL, 'lisa',10); INSERT INTO users VALUES(NULL, 'lisa',10); INSERT INTO users VALUES(NULL, 'rose',11); INSERT INTO users VALUES(NULL, 'jack', 12); INSERT INTO users VALUES(NULL, 'sam', 13);
- Using filesort
EXPLAIN SELECT * FROM users ORDER BY age;
執(zhí)行結(jié)果Extra為Using filesort ,這說明,得到所需結(jié)果集,需要對所有記錄進(jìn)行文件排序。這類SQL語句性能極差,需要進(jìn)行優(yōu)化。
典型的,在一個(gè)沒有建立索引的列上進(jìn)行了order by,就會(huì)觸發(fā)filesort,常見的優(yōu)化方案是,在order by的列上添加索引,避免每次查詢都全量排序。
filtered 它指返回結(jié)果的行占需要讀到的行(rows列的值)的百分比
- Using temporary
EXPLAIN SELECT COUNT(*),uname FROM users WHERE uid > 2 GROUP BY uname;
執(zhí)行結(jié)果Extra為Using temporary ,這說明需要建立臨時(shí)表 (temporary table) 來暫存中間結(jié)果。性能消耗大, 需要?jiǎng)?chuàng)建一張臨時(shí)表, 常見于group by語句中. 需配合SQL執(zhí)行過程來解釋, 如果group by和where索引條件不同, 那么group by中的字段需要?jiǎng)?chuàng)建臨時(shí)表分組后再回到原查詢表中.如果查詢條件where和group by是相同索引字段, 那么就不需要臨時(shí)表.
- Using where
EXPLAIN SELECT * FROM users WHERE age=10;
此語句的執(zhí)行結(jié)果Extra為Using where,表示使用了where條件過濾數(shù)據(jù)。需要注意的是:
- 返回所有記錄的SQL,不使用where條件過濾數(shù)據(jù),大概率不符合預(yù)期,對于這類SQL往往需要進(jìn)行優(yōu)化;
- 使用了where條件的SQL,并不代表不需要優(yōu)化,往往需要配合explain結(jié)果中的type(連接類型)來綜合判斷。例如本例查詢的 age 未設(shè)置索引,所以返回的type為ALL,仍有優(yōu)化空間,可以建立索引優(yōu)化查詢。
- Using index
表示直接訪問索引就能夠獲取到所需要的數(shù)據(jù)(覆蓋索引) , 不需要通過索引回表.
-- 為uname創(chuàng)建索引 alter table users add index idx_uname(uname);
EXPLAIN SELECT uid,uname FROM users WHERE uname='lisa';
此句執(zhí)行結(jié)果為Extra為Using index,說明sql所需要返回的所有列數(shù)據(jù)均在一棵索引樹上,而無需訪問實(shí)際的行記錄。
- Using join buffer (Block Nested Loop):
- 這個(gè)
Extra
字段的值表明 MySQL 在執(zhí)行嵌套循環(huán)連接時(shí)使用了連接緩沖區(qū)。這通常發(fā)生在沒有可用的合適索引時(shí),MySQL 會(huì)將一個(gè)表的數(shù)據(jù)加載到內(nèi)存中的緩沖區(qū),然后逐一掃描另一個(gè)表,以找到滿足連接條件的行。 - Block Nested Loop 是指 MySQL 會(huì)將外部表(在本例中是
u1
)的部分?jǐn)?shù)據(jù)塊加載到緩沖區(qū),然后與內(nèi)部表(在本例中是子查詢派生表u2
)進(jìn)行匹配。這樣可以減少對磁盤的訪問次數(shù),提高查詢效率。
- 這個(gè)
需要進(jìn)行嵌套循環(huán)計(jì)算.
ALTER TABLE users ADD COLUMN sex CHAR(1);
UPDATE users SET sex = '0' WHERE uname IN ('lisa', 'rose'); UPDATE users SET sex = '1' WHERE uname IN ('jack', 'sam');
EXPLAIN SELECT * FROM users u1 LEFT JOIN (SELECT * FROM users WHERE sex = '0') u2 ON u1.uname = u2.uname;
沒有顯示 Using join buffer
,可能是因?yàn)椴樵儍?yōu)化器在這個(gè)具體的場景下能夠有效地使用索引,因此不需要使用連接緩沖區(qū)。在這種情況下,MySQL 直接使用了 ref
類型的連接(通過索引進(jìn)行連接),而不是需要緩沖區(qū)的嵌套循環(huán)連接。
可以刪除或修改表上的索引,以便讓 MySQL 在執(zhí)行查詢時(shí)無法使用現(xiàn)有的索引,從而被迫使用連接緩沖區(qū)。
ALTER TABLE users DROP INDEX idx_uname;
EXPLAIN SELECT * FROM users u1 LEFT JOIN (SELECT * FROM users WHERE sex = '0') u2 ON u1.uname = u2.uname;
執(zhí)行結(jié)果Extra為Using join buffer (Block Nested Loop) 說明,需要進(jìn)行嵌套循環(huán)計(jì)算, 這里每個(gè)表都有五條記錄,內(nèi)外表查詢的type都為ALL。
問題在于 兩個(gè)關(guān)聯(lián)表join 使用 uname,關(guān)聯(lián)字段均未建立索引,就會(huì)出現(xiàn)這種情況。
常見的優(yōu)化方案是,在關(guān)聯(lián)字段上添加索引,避免每次嵌套循環(huán)計(jì)算。
- Using index condition
搜索條件中雖然出現(xiàn)了索引列,但是有部分條件無法使用索引,會(huì)根據(jù)能用索引的條件先搜索一遍再匹配無法使用索引的條件。
Using index condition 叫作 Index Condition Pushdown Optimization (索引下推優(yōu)化)。Index Condition Pushdown (ICP)是MySQL使用索引從表中檢索行的一種優(yōu)化。如果沒有ICP,存儲引擎將遍歷索引以定位表中的行,并將它們返回給MySQL服務(wù)器,服務(wù)器將判斷行的WHERE條件。在啟用ICP的情況下,如果可以只使用索引中的列來計(jì)算WHERE條件的一部分,MySQL服務(wù)器就會(huì)將WHERE條件的這一部分推到存儲引擎中。然后,存儲引擎通過使用索引條目來評估推入的索引條件,只有當(dāng)滿足該條件時(shí),才從表中讀取行。ICP可以減少存儲引擎必須訪問基表的次數(shù)和MySQL服務(wù)器必須訪問存儲引擎的次數(shù)。
CREATE TABLE employees ( id INT PRIMARY KEY AUTO_INCREMENT, first_name VARCHAR(50), last_name VARCHAR(50), age INT, department_id INT, salary DECIMAL(10, 2), hire_date DATE ); INSERT INTO employees (first_name, last_name, age, department_id, salary, hire_date) VALUES ('John', 'Doe', 30, 1, 60000.00, '2015-03-01'), ('Jane', 'Doe', 28, 2, 65000.00, '2016-07-15'), ('Mike', 'Smith', 45, 3, 75000.00, '2010-10-22'), ('Sara', 'Jones', 32, 1, 55000.00, '2018-01-12'), ('Tom', 'Brown', 29, 2, 58000.00, '2017-05-18');
接著,我們在 last_name
和 age
字段上創(chuàng)建復(fù)合索引:
CREATE INDEX idx_lastname_age ON employees(last_name, age);
編寫一個(gè)查詢,包含部分能利用索引的條件和部分不能利用索引的條件:
EXPLAIN SELECT * FROM employees WHERE last_name = 'Doe' AND age > 25 AND salary > 60000;
這一行表明 MySQL 在查詢中使用了 Index Condition Pushdown
優(yōu)化。
在這個(gè)例子中,last_name = 'Doe'
和 age > 25
可以利用復(fù)合索引 idx_lastname_age
,因此 MySQL 使用索引條件下推技術(shù),在存儲引擎層面盡量減少訪問行數(shù)據(jù)的次數(shù)。
salary > 60000
是不能利用索引的條件,但由于使用了 ICP,存儲引擎會(huì)先根據(jù) last_name
和 age
進(jìn)行初步過濾,然后再把符合條件的行返回給 MySQL 服務(wù)器,服務(wù)器進(jìn)一步應(yīng)用 salary > 60000
的過濾。
總結(jié):
Index Condition Pushdown (ICP) 是一種優(yōu)化技術(shù),允許 MySQL 在存儲引擎層面應(yīng)用部分 WHERE
條件,從而減少需要從表中讀取的行數(shù)。這可以提高查詢性能,尤其是在涉及復(fù)合索引時(shí)。
Using index condition
提示表示 MySQL 已經(jīng)應(yīng)用了 ICP 優(yōu)化。通過使用復(fù)合索引和帶有多條件的查詢,可以顯式地觀察到這個(gè)優(yōu)化技術(shù)的作用。
到此這篇關(guān)于MySQL中EXPLAIN的/基本使用及字段詳解的文章就介紹到這了,更多相關(guān)MySQL中EXPLAIN詳解內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
相關(guān)文章
PHP中實(shí)現(xiàn)MySQL嵌套事務(wù)的兩種解決方案
這篇文章主要介紹了PHP中實(shí)現(xiàn)MySQL嵌套事務(wù)的兩種解決方案,本文分析了doctrine和laravel的實(shí)現(xiàn)方式,并提取出來分析和總結(jié),需要的朋友可以參考下2015-02-02MySQL 利用frm文件和ibd文件恢復(fù)表數(shù)據(jù)
這篇文章主要介紹了MySQL 利用frm文件和ibd文件恢復(fù)表數(shù)據(jù),幫助大家更好的理解和學(xué)習(xí)使用MySQL,感興趣的朋友可以了解下2021-03-03MySQL Innodb表導(dǎo)致死鎖日志情況分析與歸納
發(fā)現(xiàn)當(dāng)備份表格的sql語句與刪除該表部分?jǐn)?shù)據(jù)的sql語句同時(shí)運(yùn)行時(shí),mysql會(huì)檢測出死鎖,并打印出日志2012-12-12Centos7使用yum安裝Mysql5.7.19的詳細(xì)步驟
本篇文章主要介紹了Centos7使用yum安裝Mysql5.7.19的詳細(xì)步驟,小編覺得挺不錯(cuò)的,現(xiàn)在分享給大家,也給大家做個(gè)參考。一起跟隨小編過來看看吧2017-09-09