一、介紹

官網(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;

三、字段詳解

3.1、id字段

select查詢的序列號(hào)，包含一組數(shù)字，表示查詢中執(zhí)行select子句或操作表的順序

• id相同，執(zhí)行順序由上至下

EXPLAIN SELECT * FROM L1,L2,L3 WHERE L1.id=L2.id AND L2.id = L3.id;

• id不同，如果是子查詢，id的序號(hào)會(huì)遞增，id值越大優(yōu)先級(jí)越高，越先被執(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ū)來存儲(chǔ)的，這樣查詢時(shí)可以提高效率。

查詢時(shí)匹配到的分區(qū)信息，對(duì)于非分區(qū)表值為NULL ，當(dāng)查詢的是分區(qū)表時(shí)， partitions 顯示分區(qū)表命中的分區(qū)情況。

對(duì)于非分區(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ū)存儲(chǔ) id 小于 2 的數(shù)據(jù)
• p1 分區(qū)存儲(chǔ) id 小于 4 的數(shù)據(jù)
• p2 分區(qū)存儲(chǔ) id 小于 6 的數(shù)據(jù)

使用 EXPLAIN 查看查詢的分區(qū)命中情況：

EXPLAIN SELECT * FROM L1_partitioned WHERE id = 1;

此查詢會(huì)顯示 partitions 字段的值為 p0，因?yàn)?nbsp;id=1 的記錄被存儲(chǔ)在 p0 分區(qū)中。

EXPLAIN SELECT * FROM L1_partitioned WHERE id = 3;

此查詢會(huì)顯示 partitions 字段的值為 p1，因?yàn)?nbsp;id=3 的記錄被存儲(chǔ)在 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 : 唯一性索引掃描,對(duì)于每個(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 : 對(duì)于每個(gè)來自于先前的表的行組合,進(jìn)行完整的表掃描。

EXPLAIN SELECT * FROM L1;

一般來說,需要保證查詢至少達(dá)到 range級(jí)別,最好能到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越小 索引效果越好

上述的這兩句話是否存在矛盾呢，我們?cè)撛趺蠢斫饽兀?/p>

第一句：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è)部分，這通?？梢蕴岣卟樵冃?。

第二句：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è)字段的類型。對(duì)于簡單的查詢，key_len 較小可能是好事。

總結(jié)來說，key_len 并不是越大或越小越好，而是要根據(jù)查詢的具體情況來衡量。當(dāng) key_len 充分利用了索引的關(guān)鍵字段，并且有效過濾數(shù)據(jù)時(shí)，這通常是一個(gè)高效的查詢?cè)O(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)行測試

• 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ī)則:

在對(duì)最終的結(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ā)人員有針對(duì)性的對(duì)SQL進(jìn)行優(yōu)化.

• 表的讀取順序。（對(duì)應(yīng)id）

• 數(shù)據(jù)讀取操作的操作類型。（對(duì)應(yīng)select_type）

• 哪些索引可以使用。（對(duì)應(yīng)possible_keys）

• 哪些索引被實(shí)際使用。（對(duì)應(yīng)key）

• 每張表有多少行被優(yōu)化器查詢。（對(duì)應(yīng)rows）

• 評(píng)估sql的質(zhì)量與效率 (對(duì)應(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é)果集，需要對(duì)所有記錄進(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ù)期，對(duì)于這類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)行匹配。這樣可以減少對(duì)磁盤的訪問次數(shù)，提高查詢效率。

需要進(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，存儲(chǔ)引擎將遍歷索引以定位表中的行，并將它們返回給MySQL服務(wù)器，服務(wù)器將判斷行的WHERE條件。在啟用ICP的情況下，如果可以只使用索引中的列來計(jì)算WHERE條件的一部分，MySQL服務(wù)器就會(huì)將WHERE條件的這一部分推到存儲(chǔ)引擎中。然后，存儲(chǔ)引擎通過使用索引條目來評(píng)估推入的索引條件，只有當(dāng)滿足該條件時(shí)，才從表中讀取行。ICP可以減少存儲(chǔ)引擎必須訪問基表的次數(shù)和MySQL服務(wù)器必須訪問存儲(chǔ)引擎的次數(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');

接著，我們?cè)?nbsp;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ù)，在存儲(chǔ)引擎層面盡量減少訪問行數(shù)據(jù)的次數(shù)。

salary > 60000 是不能利用索引的條件，但由于使用了 ICP，存儲(chǔ)引擎會(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 在存儲(chǔ)引擎層面應(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)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論