引言
優化SQL,是DBA常見的工作之一。如何高效、快速地優化一條語句,是每個DBA經常要面對的一個問題。在日常的優化工作中,我發現有很多操作是在優化過程中必不可少的步驟。然而這些步驟重複性的執行,又會耗費DBA很多精力。於是萌發了自己編寫小工具,提高優化效率的想法。
那選擇何種語言來開發工具呢?
對於一名DBA來說,掌握一門語言配合自己的工作是非常必要的。相對於shell的簡單、perl的飄逸,Python是一種嚴謹的高級語言。其具備上手快、語法簡單、擴展豐富、跨平臺等多種優點。很多人把它稱為一種「膠水」語言,通過大量豐富的類庫、模塊,可以快速搭建出自己需要的工具。
於是乎,這個小工具就成了我學習Python的第一個作業,我把它稱之為「MySQL語句優化輔助工具」。而且從此以後,我深深愛上了Python,並開發了很多資料庫相關的小工具,以後有機會介紹給大家。
一、優化手段、步驟
下面在介紹工具使用之前,首先說明下MySQL中語句優化常用的手段、方法及需要注意的問題。這也是大家在日常手工優化中,需要了解掌握的。
1、執行計劃 — EXPLAIN命令
執行計劃是語句優化的主要切入點,通過執行計劃的判讀瞭解語句的執行過程。在執行計劃生成方面,MySQL與Oracle明顯不同,它不會緩存執行計劃,每次都執行「硬解析」。查看執行計劃的方法,就是使用EXPLAIN命令。
1)基本用法
EXPLAIN QUERY
當在一個Select語句前使用關鍵字EXPLAIN時,MySQL會解釋了即將如何運行該Select語句,它顯示了表如何連接、連接的順序等信息。
EXPLAIN EXTENDED QUERY
當使用EXTENDED關鍵字時,EXPLAIN產生附加信息,可以用SHOW WARNINGS瀏覽。該信息顯示優化器限定SELECT語句中的表和列名,重寫並且執行優化規則後SELECT語句是什麼樣子,並且還可能包括優化過程的其它註解。在MySQL5.0及更新的版本里都可以使用,在MySQL5.1裏它有額外增加了一個過濾列(filtered)。
EXPLAIN PARTITIONS QUERY
顯示的是查詢要訪問的數據分片——如果有分片的話。它只能在MySQL5.1及更新的版本里使用。
EXPLAIN FORMAT=JSON (5.6新特性)
另一個格式顯示執行計劃。可以看到諸如表間關聯方式等信息。
2)輸出欄位
下面說明一下EXPLAIN輸出的欄位含義,並由此學習如何判斷一個執行計劃。
id
MySQL選定的執行計劃中查詢的序列號。如果語句裏沒有子查詢等情況,那麼整個輸出裏就只有一個SELECT,這樣一來每一行在這個列上都會顯示一個1。如果語句中使用了子查詢、集合操作、臨時表等情況,會給ID列帶來很大的複雜性。如上例中,WHERE部分使用了子查詢,其id=2的行表示一個關聯子查詢。
select_type
語句所使用的查詢類型。是簡單SELECT還是複雜SELECT(如果是後者,顯示它屬於哪一種複雜類型)。常用有以下幾種標記類型。
子查詢內層的第一個SELECT,依賴於外部查詢的結果集。
子查詢中的UNION,且為UNION中從第二個SELECT開始的後面所有SELECT,同樣依賴於外部查詢的結果集。
子查詢中的最外層查詢,注意並不是主鍵查詢。
除子查詢或UNION之外的其他查詢。
子查詢內層查詢的第一個SELECT,結果不依賴於外部查詢結果集。
結果集無法緩存的子查詢。
UNION語句中的第二個SELECT開始後面的所有SELECT,第一個SELECT為PRIMARY。
UNION中的合併結果。從UNION臨時表獲取結果的SELECT。
衍生表查詢(FROM子句中的子查詢)。MySQL會遞歸執行這些子查詢,把結果放在臨時表裡。在內部,伺服器就把當做一個"衍生表"那樣來引用,因為臨時表就是源自子查詢。
table
這一步所訪問的資料庫中表的名稱或者SQL語句指定的一個別名錶。這個值可能是表名、表的別名或者一個為查詢產生的臨時表的標識符,如派生表、子查詢或集合。
type
表的訪問方式。以下列出了各種不同類型的表連接,依次是從最好的到最差的。
系統表,表只有一行記錄。這是const表連接類型的一個特例。
讀常量,最多隻有一行匹配的記錄。由於只有一行記錄,優化程序裏該行記錄的欄位值可以被當作是一個恆定值。const用於在和PRIMARY KEY或UNIQUE索引中有固定值比較的情形。
最多隻會有一條匹配結果,一般是通過主鍵或唯一鍵索引來訪問。從該表中會有一行記錄被讀取出來以和從前一個表中讀取出來的記錄做聯合。與const類型不同的是,這是最好的連接類型。它用在索引所有部分都用於做連接並且這個索引是一個PRIMARY KEY或UNIQUE類型。eq_ref可以用於在進行"="做比較時檢索欄位。比較的值可以是固定值或者是表達式,表達示中可以使用表裡的欄位,它們在讀表之前已經準備好了。
JOIN語句中驅動表索引引用的查詢。該表中所有符合檢索值的記錄都會被取出來和從上一個表中取出來的記錄作聯合。ref用於連接程序使用鍵的最左前綴或者是該鍵不是PRIMARY KEY或UNIQUE索引(換句話說,就是連接程序無法根據鍵值只取得一條記錄)的情況。當根據鍵值只查詢到少數幾條匹配的記錄時,這就是一個不錯的連接類型。ref還可以用於檢索欄位使用"="操作符來比較的時候。
與ref的唯一區別就是在使用索引引用的查詢之外再增加一個空值的查詢。這種連接類型類似ref,不同的是MySQL會在檢索的時候額外的搜索包含NULL值的記錄。這種連接類型的優化是從MySQL 4.1.1開始的,它經常用於子查詢。
查詢中同時使用兩個(或更多)索引,然後對索引結果進行合併(merge),再讀取表數據。這種連接類型意味著使用了Index Merge優化方法。
子查詢中的返回結果欄位組合是主鍵或唯一約束。
子查詢中的返回結果欄位組合是一個索引(或索引組合),但不是一個主鍵或唯一索引。這種連接類型類似unique_subquery。它用子查詢來代替IN,不過它用於在子查詢中沒有唯一索引的情況下。
索引範圍掃描。只有在給定範圍的記錄才會被取出來,利用索引來取得一條記錄。
全索引掃描。連接類型跟ALL一樣,不同的是它只掃描索引樹。它通常會比ALL快點,因為索引文件通常比數據文件小。MySQL在查詢的欄位知識單獨的索引的一部分的情況下使用這種連接類型。
全文索引掃描。
全表掃描。
possible_keys
該欄位是指MySQL在搜索表記錄時可能使用哪個索引。如果沒有任何索引可以使用,就會顯示為null。
key
查詢優化器從possible_keys中所選擇使用的索引。key欄位顯示了MySQL實際上要用的索引。當沒有任何索引被用到的時候,這個欄位的值就是NULL。
key_len
被選中使用索引的索引鍵長度。key_len欄位顯示了MySQL使用索引的長度。當key欄位的值為NULL時,索引的長度就是NULL。
ref
列出是通過常量,還是某個表的某個欄位來過濾的。ref欄位顯示了哪些欄位或者常量被用來和key配合從表中查詢記錄出來。
rows
該欄位顯示了查詢優化器通過系統收集的統計信息估算出來的結果集記錄條數。
Extra
該欄位顯示了查詢中MySQL的附加信息。
filtered
這個列式在MySQL5.1裏新加進去的,當使用EXPLAIN EXTENDED時才會出現。它顯示的是針對錶裏符合某個條件(WHERE子句或聯接條件)的記錄數的百分比所作的一個悲觀估算。
3) SQL改寫
EXPLAIN除了可以顯示執行計劃外,還可以顯示SQL改寫。所謂SQL改寫,是指MySQL在對SQL語句進行優化前,會基於一些原則進行語句的改寫,以方便後面的優化器進行優化生成更優的執行計劃。該功能是通過EXPLAIN EXTENDED+SHOW WARNINGS配合使用。下面通過示例說明一下。