欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

MySQL中EXPLAIN的/基本使用及字段詳解

 更新時(shí)間:2024年09月11日 09:47:20   作者:土法打碼  
EXPLAIN命令是MySQL中用于解析SQL查詢語句的工具,幫助開發(fā)者理解MySQL如何執(zhí)行SQL語句,通過EXPLAIN可以查看SQL執(zhí)行的詳細(xì)信息,如表的讀取順序、使用的索引、數(shù)據(jù)讀取操作的類型等,這篇文章主要介紹了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;

序號字段含義
1id查詢的序列號,是一組數(shù)字,表示查詢中執(zhí)行 SELECT 子句或操作表的順序。
2select_type表示 SELECT 的類型。常見取值有 SIMPLE(簡單查詢,不包含子查詢或聯(lián)合查詢)、PRIMARY(主查詢,即最外層的查詢)、UNION(聯(lián)合查詢中的第二個(gè)或后續(xù)查詢)、SUBQUERY(子查詢)等。
3table表示正在訪問的表。
4partitions顯示匹配的分區(qū)信息,如果是非分區(qū)表則為 NULL
5type表示表的訪問類型,性能由好到差的順序?yàn)?nbsp;system → const → eq_ref → ref → ref_or_null → index_merge → unique_subquery → index_subquery → range → index → ALL。訪問類型越靠前,性能越好。
6possible_keys表示查詢時(shí)可能使用的索引。
7key實(shí)際使用的索引。如果沒有使用索引,則顯示為 NULL。
8key_len表示使用的索引的字節(jié)數(shù)。這個(gè)值越大,表示查詢中使用的索引字段越多。
9ref顯示索引的哪一列被用到,并且如果可能的話,是哪些列或常量被用于查找索引列中的值。
10rows估計(jì)要讀取的行數(shù),這個(gè)數(shù)字是一個(gè)估計(jì)值,不一定是精確的。
11filtered表示服務(wù)器根據(jù)查詢條件過濾的行百分比。
12Extra包含執(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允許Null1key_len = 1 + 1允許NULL,key_len長度加1
tinyint not null不允許Null1key_len = 1不允許NULL
int允許Null4key_len = 4 + 1允許NULL,key_len長度加1
int not null不允許Null4key_len = 4不允許NULL
bigint允許Null8key_len = 8 + 1允許NULL,key_len長度加1
bigint not null不允許Null8key_len = 8不允許NULL
char(1)允許Nullutf8mb4=4, utf8=3, gbk=2key_len = 1*3 + 1允許NULL,字符集utf8,key_len長度加1
char(1) not null不允許Nullutf8mb4=4, utf8=3, gbk=2key_len = 1*3不允許NULL,字符集utf8
varchar(10)允許Nullutf8mb4=4, utf8=3, gbk=2key_len = 10*3 + 2 + 1動(dòng)態(tài)列類型,key_len長度加2,允許NULL,key_len長度加1
varchar(10) not null不允許Nullutf8mb4=4, utf8=3, gbk=2key_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ù),提高查詢效率。

需要進(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)文章

  • mysql中迅速插入百萬條測試數(shù)據(jù)的方法

    mysql中迅速插入百萬條測試數(shù)據(jù)的方法

    最近想到創(chuàng)建一個(gè)大量數(shù)據(jù)的測試環(huán)境,于是找了一下怎么插入100W條數(shù)據(jù),我用的是20個(gè)字段
    2012-04-04
  • 修改MySQL的默認(rèn)密碼的四種小方法

    修改MySQL的默認(rèn)密碼的四種小方法

    對于windows平臺來說安裝完MySQL后,系統(tǒng)就已經(jīng)默認(rèn)生成了許可表和賬戶,下文中就教給大家如何修改MySQ的默認(rèn)密碼。
    2015-09-09
  • PHP中實(shí)現(xiàn)MySQL嵌套事務(wù)的兩種解決方案

    PHP中實(shí)現(xiàn)MySQL嵌套事務(wù)的兩種解決方案

    這篇文章主要介紹了PHP中實(shí)現(xiàn)MySQL嵌套事務(wù)的兩種解決方案,本文分析了doctrine和laravel的實(shí)現(xiàn)方式,并提取出來分析和總結(jié),需要的朋友可以參考下
    2015-02-02
  • MySQL 空間碎片的查看與回收

    MySQL 空間碎片的查看與回收

    ySQL數(shù)據(jù)庫在運(yùn)行過程中可能會(huì)出現(xiàn)空間碎片的問題,本文就來介紹一下MySQL 空間碎片的查看與回收 ,具有一定的參考價(jià)值,感興趣的可以了解一下
    2025-02-02
  • MySQL 利用frm文件和ibd文件恢復(fù)表數(shù)據(jù)

    MySQL 利用frm文件和ibd文件恢復(fù)表數(shù)據(jù)

    這篇文章主要介紹了MySQL 利用frm文件和ibd文件恢復(fù)表數(shù)據(jù),幫助大家更好的理解和學(xué)習(xí)使用MySQL,感興趣的朋友可以了解下
    2021-03-03
  • mysql 添加索引的實(shí)現(xiàn)步驟

    mysql 添加索引的實(shí)現(xiàn)步驟

    索引是一種用于提高查詢效率的數(shù)據(jù)結(jié)構(gòu),通過添加索引,可以加快數(shù)據(jù)的查找速度,本文主要介紹了mysql 添加索引的實(shí)現(xiàn)步驟,具有一定的參考價(jià)值,感興趣的可以了解一下
    2024-07-07
  • MySQL Innodb表導(dǎo)致死鎖日志情況分析與歸納

    MySQL Innodb表導(dǎo)致死鎖日志情況分析與歸納

    發(fā)現(xiàn)當(dāng)備份表格的sql語句與刪除該表部分?jǐn)?shù)據(jù)的sql語句同時(shí)運(yùn)行時(shí),mysql會(huì)檢測出死鎖,并打印出日志
    2012-12-12
  • 詳解如何使用DockerHub官方的MySQL鏡像

    詳解如何使用DockerHub官方的MySQL鏡像

    MySQL是一個(gè)廣泛使用的開源關(guān)系型數(shù)據(jù)庫,那如何獲取Mysql Docker鏡像?下面通過這篇文章來一起看看如何使用DockerHub官方的MySQL鏡像,有需要的朋友們可以參考借鑒。
    2016-12-12
  • 一看就懂的MySQL的聚簇索引及聚簇索引是如何長高的

    一看就懂的MySQL的聚簇索引及聚簇索引是如何長高的

    聚簇索引不是一種單獨(dú)的索引類型,而是一種數(shù)據(jù)存儲方式。innodb的聚簇索引實(shí)際上在同一個(gè)結(jié)構(gòu)中保存了B-tree索引和數(shù)據(jù)行。通過本文學(xué)習(xí)MySQL的聚簇索引及聚簇索引是如何長高的,感興趣的朋友一起學(xué)習(xí)下吧
    2021-05-05
  • Centos7使用yum安裝Mysql5.7.19的詳細(xì)步驟

    Centos7使用yum安裝Mysql5.7.19的詳細(xì)步驟

    本篇文章主要介紹了Centos7使用yum安裝Mysql5.7.19的詳細(xì)步驟,小編覺得挺不錯(cuò)的,現(xiàn)在分享給大家,也給大家做個(gè)參考。一起跟隨小編過來看看吧
    2017-09-09

最新評論