服務報價 | 域名主機 | 網絡營銷 | 軟件工具| [加入收藏]
?熱線電話: 130-5800-8007
當前位置: 主頁 > 開發教程 > mysql教程 >

如何理解MySQL的執行計劃

時間:2017-04-09 13:58來源:未知 作者:最模板 點擊:
MySQL的邏輯體系結構 最上層的服務并不是MySQL所獨有的,大多數基于網絡的客戶端/服務器的工具都有類似的架構。比如連接處理、授權認證、安全等等。 第二層架構是MySQL比較有意思的

MySQL的邏輯體系結構

如何理解MySQL的執行計劃

最上層的服務并不是MySQL所獨有的,大多數基于網絡的客戶端/服務器的工具都有類似的架構。比如連接處理、授權認證、安全等等。

第二層架構是MySQL比較有意思的部分。大多數MySQL的核心服務功能都在這一層,包括查詢解析、分析、優化、緩存以及所有內置函數(例如:日期、時間、數學和加密函數),所有跨存儲引擎的功能都在這一層實現:存儲過程,觸發器,視圖等。

第三層包含了存儲引擎。存儲引擎負責MySQL中數據的存儲和提取。和GNU/Linux下的各種文件系統一樣,每個存儲引擎都有它的優勢和劣勢。服務器通過API與存儲引擎進行通信。這些接口屏蔽了不同存儲引擎之間的差異,使得這些差異對上層的查詢過程透明。存儲引擎API包含十幾個底層函數,用于執行諸如“開始一個事物”或者“根據主鍵提取一行記錄”等操作。但存儲引擎不會去解析SQL(注:InnoDB是一個例外,它會解析外鍵定義,因為MySQL服務器本身沒有實現該功能),不同存儲引擎之間也不會互相通信,而只是簡單地響應上層服務器的請求。

連接管理與安全

每個客戶端連接都會在服務器進程中擁有一個線程,這個連接的查詢只會在這個單獨的線程中執行,該線程只能輪流在某個CPU核心或者CPU中運行。服務器會負責緩存線程,因此不需要為每一個新建的連接創建或者銷毀線程。(注:MySQL5.5或者更新的版本提供的一個API,支持線程池插件,可以使用池中少量的線程來服務大量的連接)。

當客戶端(應用)連接到MySQL服務器時,服務器需要對其進行認證。認證基于用戶名,原始主機信息和密碼。如果使用了安全套接字(SSL)的方式連接,還可以使用X.509證書認證。一旦客戶端連接成功,服務器會繼續驗證客戶端是否具有某個特定查詢的權限(例如,是否允許客戶端對world數據庫的Country表執行SELECT語句)。

優化與執行

MySQL會解析查詢,并創建內部數據結構(解析樹),然后對其進行各種優化,包括重寫查詢,決定表的讀取順序,以及選擇合適的索引等。用戶可以通過特殊的關鍵字提示(hint)優化器,影響它的決策過程。也可以請求優化器解釋(explain)優化過程的各個因素,使用戶可以知道服務器是如何進行優化決策的,并提供一個參考基準,便于用戶重構查詢和schema,修改相關配置,是應用盡可能高效運行。

優化器并不關心使用的是什么存儲引擎,但存儲引擎對于優化查詢是有影響的。優化器會請求存儲引擎提供容量或某個具體操作的開銷信息,以及表數據的統計信息等。例如,某些存儲引擎的某種索引,可能對一些特定的查詢有優化。

對于SELECT語句,在解析查詢之前,服務器會先檢查查詢緩存(Query Cache),如果能夠在其中找到對應的查詢,服務器就不必再執行查詢解析、優化和執行的整個過程,而是直接返回查詢緩存中的結果集。

查詢的過程以及開銷

查詢的過程:從客戶端到服務端,在服務器上進行解析,生成執行計劃,執行,并返回結果給客戶端,執行包括了大量為了檢索數據到存儲引擎的調用以及調用后的數據處理,包括排序,分組。

查詢的開銷:MySQL的解析,優化,鎖等待,以及數據處理等,存儲引用的API的調用。

SQL標準的執行流程(select)

(8)  SELECT
(9)  DISTINCT
(11) <TOP_specification> <select_list>
(1)  FROM <left_table>
(3)  <join_type> JOIN <right_table>
(2)  ON <join_condition>
(4)  WHERE <where_condition>
(5)  GROUP BY <group_by_list>
(6)  WITH {CUBE ROLLUP}
(7)  HAVING <having_condition>
(10) ORDER BY <order_by_list>
  1. FROM:對FROM子句中的前兩個表執行笛卡爾積,生成虛擬表VT1
  2. ON:對VT1應用ON篩選器。只有那些使<join_condition>為真的行才被插入VT2
  3. OUTER(JOIN):如果指定了OUTER JOIN,保留表中未找到匹配的行將作為外部行添加到VT2,生成VT3。如果FROM子句包含兩個以上的表,則對上一個聯接生成的結果表和下一個表重復執行步驟1到步驟3,直到處理完所有的表為止
  4. 對VT3應用WHERE篩選器。只有使<where_condition>為TRUE的行才被插入VT4
  5. GROUP BY:按GROUP BY 子句中的列列表對VT4中的行分組,生成VT5
  6. CUBEROLLUP:把超組插入VT5,生成VT6。
  7. HAVING:對VT6應用HAVING篩選器。只有使<having_condition>為TRUE的組才會被插入VT7
  8. SELECT:處理SELECT列表,產生VT8。
  9. DISTINCT:將重復的行從VT8中移除,產生VT9
  10. ORDER BY:將VT9中的行按ORDER BY子句中的列列表排序,生成一個有表(VC10)
  11. TOP:從VC10的開始處選擇指定數量或比例的行,生成表VT11,并返回給調用者

SQL 中的 JOIN

如何理解MySQL的執行計劃

nested loop join 算法(對連接的優化)

在MySQL中,只有一種 Join 算法,就是大名鼎鼎的 Nested Loop Join,他沒有其他很多數據庫所提供的 Hash Join,也沒有 Sort Merge Join。顧名思義,Nested Loop Join 實際上就是通過驅動表的結果集作為循環基礎數據,然后一條一條的通過該結果集中的數據作為過濾條件到下一個表中查詢數據,然后合并結果。如果還有第三個參與 Join,則再通過前兩個表的 Join 結果集作為循環基礎數據,再一次通過循環查詢條件到第三個表中查詢數據,如此往復。

MySQL用一次掃描多次連接(single-sweep,multi-join)的方法來解決連接。這意味著MySQL從第一個表中讀取一條記錄,然后在第二個表中查找到對應的記錄,然后在第三個表 中查找,依次類推。當所有的表都掃描完了,它輸出選擇的字段并且回溯所有的表,直到找不到為止,因為有的表中可能有多條匹配的記錄下一條記錄將從該表讀取,再從下一個表開始繼續處理。

MySQL 執行計劃的理解

id:本次 select 的標識符。在查詢中每個 select 都有一個順序的數值。

select_type:

  • simple: 簡單的 select (沒有使用 union或子查詢)
  • primary: 最外層的 select。
  • union: 第二層,在select 之后使用了 union。
  • dependent union: union 語句中的第二個select,依賴于外部子查詢
  • subquery: 子查詢中的第一個 select
  • dependent subquery: 子查詢中的第一個 subquery依賴于外部的子查詢
  • derived: 派生表 select(from子句中的子查詢)

table:記錄查詢引用的表。

type:以下列出了各種不同類型的表連接,依次是從最好的到最差的:

SYSTEM > CONST > EQ_REF > REF > RANGE > INDEX > ALL(不僅僅是連接,單表查詢也會有)

  • system:表只有一行記錄(等于系統表)。這是 const 表連接類型的一個特例
  • const:表中最多只有一行匹配的記錄,它在查詢一開始的時候就會被讀取出來。由于只有一行記錄,在余下的優化程序里該行記錄的字段值可以被當作是一個恒定值。const表查詢起來非常快,因為只要讀取一次!const 用于在和 primary key 或unique 索引中有固定值比較的情形
  • wq_ref:從該表中會有一行記錄被讀取出來以和從前一個表中讀取出來的記錄做聯合。與const類型不同的是,這是最好的連接類型。它用在索引所有部 分都用于做連接并且這個索引是一個primary key 或 unique 類型。eq_ref可以用于在進行“=”做比較時檢索字段。比較的值可以是固定值或者是表達式,表達示中可以使用表里的字段,它們在讀表之前已經準備好了
  • ref: 該表中所有符合檢索值的記錄都會被取出來和從上一個表中取出來的記錄作聯合。ref用于連接程序使用鍵的最左前綴或者是該鍵不是 primary key 或 unique索引(換句話說,就是連接程序無法根據鍵值只取得一條記錄)的情況。當根據鍵值只查詢到少數幾條匹配的記錄時,這就是一個不錯的連接類型。 ref還可以用于檢索字段使用=操作符來比較的時候。以下的幾個例子中,MySQL將使用 ref 來處理ref_table,和eq_ref的區別是-用到的索引是否唯一性
  • range:只有在給定范圍的記錄才會被取出來,利用索引來取得一條記錄。range用于將某個字段和一個定值用以下任何操作符比較時:=、<>、>、>=、<、<=、is null、<=>、between、and 或 in,例子:
  • select * from tbl_name where key_column = 10;
  • select * from tbl_name where key_column between 10 and 20;
  • select * from tbl_name where key_column in (10,20,30);
  • select * from tbl_name where key_part1= 10 and key_part2 in (10,20,30);
  • index:連接類型跟 all 一樣,不同的是它只掃描索引樹。它通常會比 all快點,因為索引文件通常比數據文件小。MySQL在查詢的字段只是單獨的索引的一部分的情況下使用這種連接類型
  • all: 將對該表做全部掃描以和從前一個表中取得的記錄作聯合。這時候如果第一個表沒有被標識為const的話就不大好了,在其他情況下通常是非常糟糕的。正常地,可以通過增加索引使得能從表中更快的取得記錄以避免all

possible_keys:possible_keys字段是指 MySQL 在搜索表記錄時可能使用哪個索引。注意,這個字段完全獨立于 explain 顯示的表順序。這就意味著 possible_keys 里面所包含的索引可能在實際的使用中沒用到。如果這個字段的值是null,就表示沒有索引被用到。這種情況下,就可以檢查 where子句中哪些字段那些字段適合增加索引以提高查詢的性能。就這樣,創建一下索引,然后再用 explain 檢查一下。想看表都有什么索引,可以通過 show index from tbl_name 來看。

key:key字段顯示了 MySQL 實際上要用的索引。當沒有任何索引被用到的時候,這個字段的值就是null。想要讓 MySQL 強行使用或者忽略在 possible_keys 字段中的索引列表,可以在查詢語句中使用關鍵字 force index、 use index 或 ignore index。

key_len:key_len 字段顯示了MySQL使用索引的長度。當 key 字段的值為 null時,索引的長度就是 null。注意,key_len的值可以告訴你在聯合索引中 MySQL 會真正使用了哪些索引。

ref:ref 字段顯示了哪些字段或者常量被用來和 key 配合從表中查詢記錄出來。

rows:rows 字段顯示了 MySQL 認為在查詢中應該檢索的記錄數。MySQL 認為的得到結果需要讀取的記錄數,不是返回的記錄數。

extra:

  • not exists:mysql 在查詢時做一個 left join 優化時,當它在當前表中找到了和前一條記錄符合 left join 條件后,就不再搜索更多的記錄了。下面是一個這種類型的查詢例子:select * from t1 left join t2 on t1.id=t2.id where t2.id is null;假使 t2.id 定義為 not null。這種情況下,mysql將會掃描表 t1并且用 t1.id 的值在 t2 中查找記錄。當在 t2中找到一條匹配的記錄時,這就意味著 t2.id 肯定不會都是 null,就不會再在 t2 中查找相同 id 值的其他記錄了。也可以這么說,對于 t1 中的每個記錄,mysql 只需要在t2 中做一次查找,而不管在 t2 中實際有多少匹配的記錄。
  • range checked for each record (index map):mysql沒找到合適的可用的索引。取代的辦法是,對于前一個表的每一個行連接,它會做一個檢驗以決定該使用哪個索引(如果有的話),并且使用這個索引來從表里取得記錄。這個過程不會很快,但總比沒有任何索引時做表連接來得快。
  • using filesort:mysql 需要額外的做一遍排序從而以排好的順序取得記錄。排序程序根據連接的類型遍歷所有的記錄,并且將所有符合 where 條件的記錄的要排序的鍵和指向記錄的指針存儲起來。這些鍵已經排完序了,對應的記錄也會按照排好的順序取出來。
  • using index:字段的信息直接從索引樹中的信息取得,而不再去掃描實際的記錄。這種策略用于查詢時的字段是一個獨立索引的一部分。
  • using temporary:mysql需要創建臨時表存儲結果以完成查詢。這種情況通常發生在查詢時包含了 group by 和 order by 子句,它以不同的方式列出了各個字段。
  • using where:where 子句將用來限制哪些記錄匹配了下一個表或者發送給客戶端。除非你特別地想要取得或者檢查表種的所有記錄,否則的話當查詢的 extra 字段值不是 using where 并且表連接類型是 all 或 index 時可能表示有問題。如果你想要讓查詢盡可能的快,那么就應該注意 extra 字段的值為using filesort 和 using temporary 的情況。
(責任編輯:最模板)
頂一下
(0)
0%
踩一下
(0)
0%
------分隔線----------------------------
欄目列表
熱點內容
体彩22选5开奖结果