MySQL中SQL執行計劃,你理解了嗎?

2020-12-17 程序猿小旺

首先我們來看一下什麼是執行計劃:

執行計劃就是解釋select語句如何在資料庫執行的、相關表是怎麼連接、連接的次序、有哪些索引和索引使用情況、每個表的掃描數據量等等。 簡單來說,就是通過數據來分析select語句的執行情況;

怎麼查看執行計劃:查看執行需要用到關鍵字EXPLAIN,將EXPLAIN放在SQL語句的前面( EXPLAIN [EXTENDED] SELECT select_options ),如下圖:

從上圖可以看出,通過執行計劃展示了表格數據,這個表格有id、select_type、table、partitions、type、possible_keys、key、key_len、ref、rows、filtered、extra等欄位的信息,這些欄位分別表示什麼意思呢?下面具體介紹下:

1、id

執行計劃的id 是select查詢的序列號,標識執行順序。注意有兩種情況,具體如下:

1)、當id相同時,執行順序從上到下執行。

左外連接,先執行左邊後執行右邊(例如:select * from user u left join custom_order co on u.id=co.creater 會先執行user表後執行custom_order),在執行計劃中,user表應該在上面,custom_order在下面,如下圖:

右外連接,先執行右邊後執行左邊(例如: select * from user u right join custom_order co on u.id=co.creater 會先執行custom_order表後執行user表),在執行計劃中,custom_order表應該在上面,user在下面,如下圖:

從上面的執行結果來看,和預計的一樣。

2)、當id不相同時,id越大越先執行。

例如:select * from user where id=(select creater from custom_order where id=1),這個語句中的子查詢會比主查詢會先執行。那在explain select * from user where id=(select creater from custom_order where id=1) 中 custom_order 的id應該是2,user的id應該是1,實際是不是這樣呢,如下圖:

從結果中可以知道,執行結果和預計的一樣;

2、select_type

執行計劃的select_type是select語句的類型,用於區分各種查詢;具體類型有以下幾種:

1)、SIMPLE

簡單的select語句,不使用union或子查詢。如下圖:

2)、PRIMARY

最外層的select語句。如下圖

3)、UNION

union中的第二個或後面的select語句。如下圖

4)、DEPENDENT UNION

union中的第二個或後面的select語句,取決於外面的查詢。如下圖

5)、UNION RESULT

union的結果。如下圖

6)、SUBQUERY

子查詢的第一個select語句。如下圖

7)、DEPENDENT SUBQUERY

子查詢中的第一個select語句,取決於外面的查詢。如下圖

8)、DERIVED

衍生表的select語句(在from列表中包含的子查詢)。如下圖

3、table

語句查詢中所涉及到的表。如下圖:

4、partitions

查詢涉及到的分區別。如下圖:

5、type

SQL語句的連接類型。具體個連接類型如下:(按照從效率高的類型到效率低的類型進行排序)

1)、system

表僅有一行(等於系統表)。這是const連接類型的一種特例。

2)、const

表最多有一個匹配行,它將在查詢開始時被讀取,常用於主鍵或唯一索引的定值查詢。因為匹配行只有一行,所以查詢速度很快。如下圖(id是主鍵):

3)、eq_ref

唯一性索引掃描,對於前表的每一行,表中只有一行與之相匹配或者沒有能匹配。常見於主鍵或唯一索引掃描。是除const類型和system類型外最快的類型。如下圖:

4)、ref

非唯一性索引掃描,對於前表的每一行,表中可存在多個與之相匹配的行。如下圖:

5)、ref_or_null

和ref類型一樣,但是添加了mysql可以專門搜索包含NULL值的行。如下圖:

6)、index_merge

標識使用了索引合併優化方法。如下圖:

7)、unique_subquery

一個索引查找函數,可以完全替換子查詢,效率更高。

8)、index_sugquery

類似於unique_subquery。可以替換IN子查詢,但只適合下列形式的子查詢中的非唯一索引。

9)、range

只檢索給定範圍的行,使用一個索引來選擇行。key列顯示使用了哪個索引。key_len包含所使用索引的最長關鍵元素。在該類型中ref列為NULL。當使用=、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN或者IN操作符,用常量比較關鍵字列時。如下圖:

10)、index

掃描索引樹。如果索引是複合索引,並且複合索引列滿足select所需的所有數據,則僅掃描索引樹,不需要回表。如下圖:

11)、ALL

掃描全表。這是最差的情況,沒有索引,進行完整的表掃描。如下圖:

6、possible_keys

語句中能使用索引有哪些。如下圖:

7、key

該語句執行的時候是使用的哪條索引。如下圖:

8、key_len該語句執行時使用的索引長度(字節數)。如下圖:

9、ref

該表的索引欄位關聯了哪張表的哪個欄位。如下圖:

10、rows

該語句執行時需要讀取行數,數越小越好。如下圖:

11、filtered

該語句返回結果的行數佔讀取行數的百分比,值越大越好。如下圖:

12、extra

十分重要的額外信息。常見的如下:

1)、Distinct

MySQL發現第一個匹配的行後,停止為當前索引搜索更多的行。

2)、Not exists

MySQL能夠對查詢進行LEFT JOIN優化,發現1個匹配LEFT JOIN標準的行後,不再為前面的索引在該表內檢查更多的行。如下圖:

3)、Using filesort

MySQL 對數據使用一個外部的文件內容進行了排序,而不是按照表內的索引進行排序讀取。如下圖:

4)、Using index

僅使用索引樹中的信息從表中檢索列信息,而不必另外尋找讀取實際行,即不需要回表。如下圖:

5)、Using temporyary

在查詢時,MySQL需要創建一個臨時表來保存結果。常出現使用GROUP BY和ORDER BY子句時。如下圖:

6)、Using where

在查找使用索引的情況下,需要回表去查詢所需的數據,後再用WHERE子句完成結果過濾,通常出現這種情況的話就需要添加合適的索引來做優化了。如下圖:

7)、Using join buffer

連接中的表分成幾部分讀入連接緩衝區,然後從緩衝區中使用它們的行來與當前表執行連接。如下圖:

這次的內容就到這裡,歡迎大家在評論區評論!

相關焦點

  • MySQL-SQL語句執行流程
    全面地了解SQL語句執行的每個過程,才能更好的進行SQL的設計和優化。當希望MySQL能夠以更高的性能運行查詢時,最好的辦法就是弄清楚MySQL是如何優化和執行查詢的。一旦理解了這一點,很多查詢優化工作實際上就是遵循一些原則能夠按照預想的合理的方式運行。
  • 「MySQL系列」分析Sql執行時間及查詢執行計劃(附資料庫和一千萬數據)
    接下來這篇文章我們來看看如何分析我們的sql執行效率。首先找到執行慢的sql,然後對執行慢的SQL進行分析。在分析之前是不是有這樣的困惑,我的數據量這麼少。我如何分析SQL執行效率。不要慌,我們已準備了一千萬條數據。接下來看看如何將這一千萬條數據快速導入到資料庫中。
  • Mysql中一條SQL查詢語句是如何執行的?
    2.查詢流程解析select * from table1 where ID=10;這條語句相信大家再熟悉不過了,下面我們就看看這一條語句在mysql中是怎麼執行的。第一步:一條sql語句要經過連接器,客戶端要和mysql建立連接。
  • MySQL sql_mode 說明(及處理一起 sql_mode 引發的問題)
    (命令行執行完語句後,show warnings 可看見)那麼解決這類問題的終極(推薦)辦法其實是,考慮到數據的兼容性和準確性,MySQL就應該運行在嚴格模式下!無論開發環境還是生產環境,否則代碼移植到線上可能產生隱藏的問題。sql_mode 問題可以很簡單,也可以很複雜。
  • 一波騷操作,我把 SQL 執行效率提高了 10,000,000 倍!
    查看優化後的sql:SELECT`YSB`.`s`.,Mysql竟然不是先執行裡層的查詢,而是將sql優化成了exists子句,並出現了EPENDENT SUBQUERY,mysql是先執行外層查詢,再執行裡層的查詢,這樣就要循環70007*11=770077次。
  • mysql常用sql語句總結
    sql的內連接、外連接和交叉連接查詢等。FROM 子句中的表或視圖可通過內聯接或完整外部聯接按任意順序指定;但是,用左或右向外聯接指定表或視圖時,表或視圖的順序很重要。有關使用左或右向外聯接排列表的更多信息,請參見使用外聯接。聯合查詢union查詢語法:sql1 union all sql2;UNION 操作符用於合併兩個或多個 SELECT 語句的結果集。
  • MySQL怎麼刪除#sql開頭的臨時表
    /* 直接刪除,表名前加#mysql50*/root@testdb 01:42:57> DROP TABLE `#mysql50##sql-ib87-856498050`;註: #mysql50#前綴是MySQL 5. 1 中引入的文件名安全編碼。另外,表名因不符合命名規範,想要執行該腳本需要將表名用反引號括起來。
  • mysql中count(*)是最慢的嗎?
    count函數是什麼:這個問題,對於但凡接觸過資料庫的老鐵來說,實在太簡單了,但是對於筆者來說,學習任何一個事物,應該先對它的定義和概念一個清晰的認識,這樣才能更好的理解和學習它。首先 count函數是sql(Structured Query Language)中的一個聚合函數,用來統計滿足「條件」數據的行數。滿足的條件具體指什麼呢?
  • 利用圖形界面從SQL導入導出到MySQL
    從sql導出到mysql的方法很多,現介紹一種無需編程,直接利用sql和mysql裡的圖形界面進行導入導出的簡單方法。前提是已經安裝了sqlserver客戶端和mysql的圖形界面管理工具 phpmyadmin。
  • 一條查詢SQL在MySQL中是怎麼執行的
    ,在這個過程中逐步的揭開MySQL的面紗,對MySQL有個深入的理解。如果查詢語句在緩存中可以查到這個key,就直接把結果返回給客戶端。如果語句不在緩存中,就會繼續執行後邊的階段。執行完成後,將執行結果存入緩存中。但是,大部分情況下都不建議使用查詢緩存,這是因為查詢緩存往往弊大於利。
  • mysql┃多個角度說明sql優化,讓你吊打面試官!
    的優化是我們經常都會提到的一個話題,也是重中之重,在很多大廠中會有專門的DBA來做這件事情,甚至更過分的是連應屆生的招聘崗位要求上都寫了需要懂一點sql優化,最近moon一直在寫關於mysql的文章,包括之前寫的索引相關,其實也都是為了這篇文章做個鋪墊,所以你懂了嗎,今天我將從表結構、索引、查詢語句、分庫分表這四個維度來和大家聊聊,在工作中,怎麼進行sql優化?
  • |sql|mysql|數據源|java|...
    但是如果你在初級或者中級的階段,就知道了答案,豈不是爽歪歪麼?  估計大部分人都不知道這個問題該怎麼回答,稍微發散一下思維,倒是可以猜測一下,今天我們就來搞清楚JDBC底層到底是如何連接資料庫的。往後別再猜了。  反過來,如果面試官問你JDBC的時候,你能知道底層是怎麼連接資料庫的,估計,很多相對較水的面試官也會一臉懵逼。
  • mysql條件查詢,最簡單的一種,你知道嗎?
    在上一篇文章中我們學習了在php中怎樣查詢資料庫中的數據,哪些數據都是直接獲取一個表中的所有數據,全部拿過來,沒有經過過濾,很多數據對於我們來說暫時是用不上的,這就相當於浪費了帶寬,不利於提高數據處理的效率,所以,我們非常有必要在抓取數據的時候進行過濾,今天就介紹mysql的條件查詢,最簡單的一種
  • mysql中變相解決排名問題,有點厲害
    為大家熟知的關係型資料庫有mysql、SQLServer、oracle、DB2等。它們的sql語句其實是有細微的差別的,不要想著有些語句在SQLServer上可以用就可以在mysql上使用。今天老韓就來講一下其中的一個知識點,mysql中如何排名。
  • 生產MySQL資料庫執行一次analyze採集信息,應用炸了
    故障簡介     之前,有開發找到我,說應用的某個功能查詢比以前慢了很多,讓開發提供了慢的SQL語句,去對應的MySQL資料庫看了一下執行計劃
  • 面對MySQL 查詢索引失效,程式設計師的六大優化技巧!
    第三步分析器:mysql在執行之前,首先會對sql語句做詞法解析和語法解析,以確定你要做什麼,並會識別語句中的關鍵詞,比如select,order by等,以及解析sql語法是否正確等。第四步優化器:優化器是資料庫的一個核心子系統,你也可以把他理解為 MySQL 資料庫中的一個核心模塊或者一個核心功能模塊。優化器的目的是按照一定原則來得到它認為的目標SQL在當前情形下最有效的執行路徑,優化器的目的是為了得到目標SQL的執行計劃。經過分析器,mysql就知道你要做什麼了。
  • MySQL教程之MySQL定時備份資料庫
    >1.在系統命令行中,輸入如下實現還原:mysql -uroot -p123456 < /data/mysqlDump/mydb.sql2.在登錄進入mysql系統中,通過source指令找到對應系統中的文件進行還原:mysql> source /data/mysqlDump
  • MySQL如何完成一次查詢?
    mysql完成一次查詢過程是比較複雜的,在說明查詢過程前先介紹一下它的基礎概念和結構原理來幫助理解。下面從四個方面介紹,分別是mysql語句,mysql結構原理,mysql查詢過程,最後設置幾個有趣問題。
  • Select * from user的千層套路——一個sql是如何執行的
    Select * from user的千層套路    作為一個程式設計師,可以說是無時無刻不與sql語句進行打交道,可是你真的了解MySQl的基本框架嗎?以及你所寫的每一條SQL是如何運行的嗎?就比如下面這條平平無奇的SQL語句:select * from user where id='1';    對於這麼一條簡單的不能再簡單的sql語句來說mysql伺服器做了哪些處理呢?
  • MySQL優化:學會使用show profile和trace分析慢查詢
    使用如下命令開啟:mysql> set profiling=1;1.3 執行要分析的sql語句mysql> select * from test_table where d=90000;執行完成SQL後再執行