分析查詢語句:EXPLAIN

来源:https://www.cnblogs.com/abinCode/archive/2023/04/25/17351692.html
-Advertisement-
Play Games

1.檢測虛擬機環境 1.1 bit檢測命令:getconf LONG_BIT(建議使用64bit做開發) 1.2 gcc環境檢測:gcc -v 如果不具備gcc環境,則使用yum -y install gcc- c++命令進行c++環境的安裝 2.開始安裝 2.1 下載redis: 進入官網找到下載 ...


一、概述

使用mysqldumpslow工具定位到慢查詢語句之後,可以使用explain或describe工具做針對性的分析查詢語句。

MySQL種有專門負責優化SELECT語句的優化器模塊:通過計算分析系統中收集到的統計信息,為客戶端請求的Query提供他認為最優的執行計劃。

這個執行計劃展示了接下來具體執行查詢的方式,比如多表連接的順序是什麼,對於每個表採用什麼訪問方法來具體執行查詢等等。MySQL提供了EXPLAIN語句來幫助我們查看某個查詢語句的具體執行計劃,看懂EXPLAIN語句的各個輸出項,可以有針對性的提升我們查詢語句的性能。

二、基本語法

explain  查詢語句;   select、insert、update、delete都可使用

例如:explain select * from user limit 10;

explain語句輸出的各列作用如下:

列名 描述
id 在一個大的查詢語句種,每一個select關鍵字都對應一個唯一的id
select_type select關鍵字對應的那個查詢的類型
table 表名
partitions 匹配的分區信息
type 針對單表的訪問方法
possible_keys 可能用到的索引
key 實際上用到的索引
key_len 實際用到的索引長度
ref 當使用索引列等值查詢時,與索引列進行等值匹配的對象信息
rows 預估的需要讀取的記錄數
filtered 某個表經過搜索條件過濾後剩餘記錄條數的百分比
exea 一些額外信息

 

 

 

 

 

 

 

 

 

 

 

 

 

三、數據準備

執行以下代碼,為分析explain做準備

#創建表
CREATE TABLE s1 (
    id INT AUTO_INCREMENT,
    key1 VARCHAR(100),
    key2 INT,
    key3 VARCHAR(100),
    key_part1 VARCHAR(100),
    key_part2 VARCHAR(100),
    key_part3 VARCHAR(100),
    common_field VARCHAR(100),
    PRIMARY KEY (id),
    INDEX idx_key1 (key1),
    UNIQUE INDEX idx_key2 (key2),
    INDEX idx_key3 (key3),
    INDEX idx_key_part(key_part1, key_part2, key_part3)
) ENGINE=INNODB CHARSET=utf8;


CREATE TABLE s2 (
    id INT AUTO_INCREMENT,
    key1 VARCHAR(100),
    key2 INT,
    key3 VARCHAR(100),
    key_part1 VARCHAR(100),
    key_part2 VARCHAR(100),
    key_part3 VARCHAR(100),
    common_field VARCHAR(100),
    PRIMARY KEY (id),
    INDEX idx_key1 (key1),
    UNIQUE INDEX idx_key2 (key2),
    INDEX idx_key3 (key3),
    INDEX idx_key_part(key_part1, key_part2, key_part3)
) ENGINE=INNODB CHARSET=utf8;

#創建存儲函數:
DELIMITER //
CREATE FUNCTION rand_string1(n INT) 
	RETURNS VARCHAR(255) #該函數會返回一個字元串
BEGIN 
	DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
	DECLARE return_str VARCHAR(255) DEFAULT '';
	DECLARE i INT DEFAULT 0;
	WHILE i < n DO
		SET return_str =CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));
		SET i = i + 1;
	END WHILE;
	RETURN return_str;
END //
DELIMITER ;

SET GLOBAL log_bin_trust_function_creators=1; 

#創建存儲過程:
DELIMITER //
CREATE PROCEDURE insert_s1 (IN min_num INT (10),IN max_num INT (10))
BEGIN
	DECLARE i INT DEFAULT 0;
	SET autocommit = 0;
	REPEAT
	SET i = i + 1;
	INSERT INTO s1 VALUES(
    (min_num + i),
    rand_string1(6),
    (min_num + 30 * i + 5),
    rand_string1(6),
    rand_string1(10),
    rand_string1(5),
    rand_string1(10),
    rand_string1(10));
	UNTIL i = max_num
	END REPEAT;
	COMMIT;
END //
DELIMITER ;


DELIMITER //
CREATE PROCEDURE insert_s2 (IN min_num INT (10),IN max_num INT (10))
BEGIN
	DECLARE i INT DEFAULT 0;
	SET autocommit = 0;
	REPEAT
	SET i = i + 1;
	INSERT INTO s2 VALUES(
        (min_num + i),
		rand_string1(6),
		(min_num + 30 * i + 5),
		rand_string1(6),
		rand_string1(10),
		rand_string1(5),
		rand_string1(10),
		rand_string1(10));
	UNTIL i = max_num
	END REPEAT;
	COMMIT;
END //
DELIMITER ;

#調用存儲過程
CALL insert_s1(10001,10000);

CALL insert_s2(10001,10000);

SELECT COUNT(*) FROM s1;

SELECT COUNT(*) FROM s2;

四、explain各列詳解

1、table:表名

查詢的每一個行記錄對應著一個單表

explain select * from s1;
explain select * from s1 inner join s2;

 2、id:在一個大的查詢語句中,每一個select關鍵字都對應一個唯一的id

explain select * from s1where key1 = 'a';

 總結:

id如果相同,可以認為是一組,從上往下執行

在所有組種,id越大,優先順序越高,越先執行

每一個id號碼表示一趟獨立的查詢,一個SQL查詢的趟數越少越好。

3、select_type:select關鍵字對應的那個查詢的類型,確定小查詢在大查詢種扮演什麼角色

①查詢語句中不包含UNION或者子查詢的查詢都算是SIMPLE類型

explain select * from s1;

 ②連接查詢也是SIMPLE

explain select * from s1 inner join s2;

 ③對於包含UNION、UNION ALL的大查詢來說,它是由幾個小查詢組成的,其中最左邊的查詢的select_type值就是PRIMARY,其餘的小查詢的select_type值為UNION

④MySQL選擇使用臨時表來完成UNION查詢的去重工作,針對該臨時表的查詢的select_type的值是UNION RESULT

 UNION 具有去重操作,UNION ALL沒有去重操作,所以UNION會有一個臨時表

 ⑤如果包含子查詢的查詢語句不能轉化為多表聯查的形式,並且該子查詢不是相關子查詢,該子查詢的第一個select關鍵字的查詢的select_type就是SUBQUERY

⑥如果包含子查詢的查詢語句不能轉化為多表聯查的形式,並且該子查詢是相關子查詢,該子查詢的第一個select關鍵字的查詢的select_type就是DEPENDENT SUBQUERY

⑦在包含`UNION`或者`UNION ALL`的大查詢中,如果各個小查詢都依賴於外層查詢的話,那除了最左邊的那個小查詢之外,其餘的小查詢的`select type `的值就是`DEPENDENT UNION

 ⑧對於包含派生表的查詢,該派生表對應的子查詢的select_type就是DERIVED

 ⑨當查詢優化器在執行包含子查詢的語句時,選擇將子查詢物化之後與外層查詢進行連接查詢時,該子查詢對應的`select type`屬性就是‘MATERIALIZED~

 4、type

執行計劃的一條記錄就代表著MySQL對某個表的執行查詢時的訪問方法,又稱°訪問類型”,其中的type列就表明瞭這個訪問方法是啥,是較為重要的一個指標。比如,看到type列的值是ref,表明MysQL即將使用ref訪問方法來執行對s1表的查詢。

完整的訪問方法如下: system,const,eq_ref,ref, fulltext,ref_or_null , index_merge ,unique_subquery , index_subquery , range , index,ALL。

①system

當表中只有一條記錄並且該表使用的存儲引擎統計的書數據是精確的,比如MyISAM、Memory,那麼對該表的訪問方法就是system

②const

當我們根據主鍵或唯一的二級索引列與常數進行等值匹配時,對單表的訪問方法就是const

explain select * from s1 where id = 10005;
explain select * from s1 where key2 = 10066;

 

 ③eq_ref

在連接查詢時,如果被驅動表是通過主鍵或者唯一二級索引列等值匹配的方式進行訪問的(如果該主鍵或者唯一二級索引是聯合索引的話,所有的索引列都必須進行等值比較),則對該被驅動表的訪問方法就是`eg ref`

explain select * from s1 inner join s2 on s1.id = s2.id;

 ④ref

當通過普通的二級索引列與常量進行等值匹配時來查詢某個表,那麼對該表的訪問方法就可能是`ref'

explain select * from s1 where key1 = 'a';

 ⑤unique_subquery

unique subquery`是針對在一些包含`IN`子查詢的查詢語句中,如果查詢優化器決定將`IN`子查詢
轉換為`ExISTS`子查詢,而且子查詢可以使用到主鍵進行等值匹配的話,那麼該子查詢執行計劃的'type '列的值就是`unique_ subquery

 ⑥如果使用索引獲取某些範圍區間的記錄,那麼就可能用到range訪問方法

 ⑦index

當我們可以使用索引覆蓋,但需要掃描全部的索引記錄時,該表的訪問方法就是index

 ⑧all:全表掃描

 5、possiable_keys和key

possiable_keys列表示在某個查詢語句中,對某個表執行單表查詢時可能用到的索引有哪些。

key表示實際用到的索引有哪些

6、key_len

實際使用到的索引的長度(位元組數),檢查是否充分利用了索引,值越大越好(和自己比),主要針對聯合索引。

 

先更新到這裡,會持續更新~~~~


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • dynamic簡介 dynamic是.NET Framework4.0的新特性。dynamic的出現讓C#具有了弱語言類型的特性。編譯器在編譯的時候不再對類型進行檢查,編譯時預設dynamic對象支持你想要的任何特性。 dynamic簡化反射實現 使用dynamic來簡化反射實現是一種比較常見的編程 ...
  • 隨著技術的發展,ASP.NET Core MVC也推出了好長時間,經過不斷的版本更新迭代,已經越來越完善,本系列文章主要講解ASP.NET Core MVC開發B/S系統過程中所涉及到的相關內容,適用於初學者,在校畢業生,或其他想從事ASP.NET Core MVC 系統開發的人員。 經過前幾篇文章... ...
  • 1、前言 學會使用 GNU Radio 中的 ZMQ,是從低階使用者向高階邁進的第一步! 因為學會了 ZMQ,就可以將 GNU Radio 中的實時數據流通過 socket 引到外面的 python/c 等大型應用程式中,做數據分析、展示、人工智慧等。 來自 ZeroMQ 官方介紹:ZeroMQ ( ...
  • @(文本處理三劍客) 劍仙鎮樓~ O(∩_∩)O 前言 上一篇中已經預告,我們這篇主要說Linux文本處理三劍客。他們分別是grep、sed、awk 。既然能被業界稱為三劍客,可見其在Linux命令中占有舉足輕重得地位。所以呢,這裡也專門寫一篇文章說一說其用法。 正則表達式 三劍客一般會配合著正則表 ...
  • 22條vscode常用快捷鍵技巧(Mac版) 1 摺疊代碼 option + command + [ 摺疊 option + command + ] 展開 2 一次性修改全文同一單詞 假設你寫錯了一個變數名,全文有10個,如何一次性修改? 單擊變數名,按住command + shift + L 即可 ...
  • 情況: 1.Windows功能列表中沒有媒體功能(Media Features),或該項下沒有Windows media player選項 2.普通在Windows-設置-功能-可選功能中單獨可以添加WMP,但實際並無效果,該組件需要相關功能包打開情況下才可以安裝成功,否則儘管顯示已安裝,但實際Pr ...
  • @(文件內容相關命令) 前言 上一篇中已經預告,我們這篇主要說一說Linux中文件內容查看處理相關的命令。一共16個命令,其中 標註的為重點命令,工作中用的也比較多,需要熟練掌握 cat 合併文件或查看文件內容 1、簡介 cat 是concatenate 單詞的縮寫,或者理解成貓,"喵"一下文件內容 ...
  • 功能03-優惠券秒殺02 4.功能03-優惠券秒殺 4.4一人一單 4.4.1需求分析 要求:修改秒殺業務,要求同一個優惠券,一個用戶只能下一單。 在之前的做法中,加入一個對用戶id和優惠券id的判斷,如果在優惠券下單表中已經存在,則表示該用戶對於這張優惠券已經下過單了,不允許重覆購買 4.4.2代 ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發(一):使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說,近來想嘗試開發一款安卓APP,考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP,畢竟是使用Visual Studio開發工具,使用起來也比較的順手,結合微軟官方的教程進行了安卓 ...
  • 前言 QuestPDF 是一個開源 .NET 庫,用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫,它徹底改變了我們生成 PDF 文檔的方 ...
  • 項目地址 項目後端地址: https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址: ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址: http://tree ...
  • 話不多說,直接開乾 一.下載 1.官方鏈接下載: https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe,運行開始下載SQL server; 二. ...
  • 前言 隨著物聯網(IoT)技術的迅猛發展,MQTT(消息隊列遙測傳輸)協議憑藉其輕量級和高效性,已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫,為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog支持多種接收器用於日誌存儲,增強器用於添加屬性,LogContext管理動態屬性,支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項,適用於不同需求。 ...
  • 目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據,爬取信息時需要模擬瀏覽器行為,否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下: 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • 1.前言 什麼是熱更新 游戲或者軟體更新時,無需重新下載客戶端進行安裝,而是在應用程式啟動的情況下,在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR,Xlua,ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles,Addressable, ...
  • 本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺,其實像阿裡雲,騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後,它會送 ...
  • 通過以下方式可以高效,並保證數據同步的可靠性 1.API設計 使用RESTful設計,確保API端點明確,並使用適當的HTTP方法(如POST用於創建,PUT用於更新)。 設計清晰的請求和響應模型,以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證,確保接收到的數據符合預期格 ...