引言
最佳化SQL，是DBA常見的工作之一。如何高效、快速地最佳化一條語句，是每個DBA經常要面對的一個問題。在日常的最佳化工作中，我發現有很多操作是在最佳化過程中必不可少的步驟。然而這些步驟重複性的執行，又會耗費DBA很多精力。於是萌發了自己編寫小工具，提高最佳化效率的想法。
那選擇何種語言來開發工具呢？
對於一名DBA來說，掌握一門語言配合自己的工作是非常必要的。相對於shell的簡單、perl的飄逸，Python是一種嚴謹的進階語言。其具備上手快、文法簡單、擴充豐富、跨平台等多種優點。很多人把它稱為一種“膠水”語言，通過大量豐富的類庫、模組，可以快速搭建出自己需要的工具。
於是乎，這個小工具就成了我學習Python的第一個作業，我把它稱之為“MySQL語句最佳化協助工具輔助”。而且從此以後，我深深愛上了Python，並開發了很多資料庫相關的小工具，以後有機會介紹給大家。
一、最佳化手段、步驟
下面在介紹工具使用之前，首先說明下MySQL中語句最佳化常用的手段、方法及需要注意的問題。這也是大家在日常手工最佳化中，需要瞭解掌握的。
1、執行計畫 — EXPLAIN命令
執行計畫是語句最佳化的主要切入點，通過執行計畫的判讀瞭解語句的執行過程。在執行計畫產生方面，MySQL與Oracle明顯不同，它不會緩衝執行計畫，每次都執行“硬解析”。查看執行計畫的方法，就是使用EXPLAIN命令。
基本用法
當在一個Select語句前使用關鍵字EXPLAIN時，MySQL會解釋了即將如何運行該Select語句，它顯示了表如何串連、串連的順序等資訊。
當使用EXTENDED關鍵字時，EXPLAIN產生附加資訊，可以用SHOW WARNINGS瀏覽。該資訊顯示最佳化器限定SELECT語句中的表和列名，重寫並且執行最佳化規則後SELECT語句是什麼樣子，並且還可能包括最佳化過程的其它註解。在MySQL5.0及更新的版本裡都可以使用，在MySQL5.1裡它有額外增加了一個過濾列(filtered)。
顯示的是查詢要訪問的資料分區——如果有分區的話。它只能在MySQL5.1及更新的版本裡使用。
另一個格式顯示執行計畫。可以看到諸如表間關聯方式等資訊。
輸出欄位
下面說明一下EXPLAIN輸出的欄位含義，並由此學習如何判斷一個執行計畫。
MySQL選定的執行計畫中查詢的序號。如果語句裡沒有子查詢等情況，那麼整個輸出裡就只有一個SELECT，這樣一來每一行在這個列上都會顯示一個1。如果語句中使用了子查詢、集合操作、暫存資料表等情況，會給ID列帶來很大的複雜性。如上例中，WHERE部分使用了子查詢，其id=2的行表示一個關聯子查詢。
語句所使用的查詢類型。是簡單SELECT還是複雜SELECT(如果是後者，顯示它屬於哪一種複雜類型)。常用有以下幾種標記類型。
-
DEPENDENT SUBQUERY
子查詢內層的第一個SELECT，依賴於外部查詢的結果集。
-
DEPENDENT UNION
子查詢中的UNION，且為UNION中從第二個SELECT開始的後面所有SELECT，同樣依賴於外部查詢的結果集。
-
PRIMARY
子查詢中的最外層查詢，注意並不是主鍵查詢。
-
SIMPLE
除子查詢或UNION之外的其他查詢。
-
SUBQUERY
子查詢內層查詢的第一個SELECT，結果不依賴於外部查詢結果集。
-
UNCACHEABLE SUBQUERY
結果集無法緩衝的子查詢。
-
UNION
UNION語句中的第二個SELECT開始後面的所有SELECT，第一個SELECT為PRIMARY。
-
UNION RESULT
UNION中的合并結果。從UNION暫存資料表擷取結果的SELECT。
-
DERIVED
衍生表查詢(FROM子句中的子查詢)。MySQL會遞迴執行這些子查詢，把結果放在暫存資料表裡。在內部，伺服器就把當做一個”衍生表”那樣來引用，因為暫存資料表就是源自子查詢。
這一步所訪問的資料庫中表的名稱或者SQL語句指定的一個別名表。這個值可能是表名、表的別名或者一個為查詢產生的暫存資料表的標識符，如派生表、子查詢或集合。
表的訪問方式。以下列出了各種不同類型的表串連，依次是從最好的到最差的。
-
system
系統資料表，表只有一行記錄。這是const表連線類型的一個特例。
-
const
讀常量，最多隻有一行匹配的記錄。由於只有一行記錄，最佳化程式裡該行記錄的欄位值可以被當作是一個恒定值。const用於在和PRIMARY KEY或UNIQUE索引中有固定值比較的情形。
-
eq_ref
最多隻會有一條匹配結果，一般是通過主鍵或唯一鍵索引來訪問。從該表中會有一行記錄被讀取出來以和從前一個表中讀取出來的記錄做聯合。與const類型不同的是，這是最好的連線類型。它用在索引所有部分都用於做串連並且這個索引是一個PRIMARY KEY或UNIQUE類型。eq_ref可以用於在進行”=”做比較時檢索欄位。比較的值可以是固定值或者是運算式，表達示中可以使用表裡的欄位，它們在讀表之前已經準備好了。
-
ref
JOIN語句中驅動表索引引用的查詢。該表中所有符合檢索值的記錄都會被取出來和從上一個表中取出來的記錄作聯合。ref用於串連程式使用鍵的最左首碼或者是該鍵不是PRIMARY KEY或UNIQUE索引(換句話說，就是串連程式無法根據索引值只取得一條記錄)的情況。當根據索引值只查詢到少數幾條匹配的記錄時，這就是一個不錯的連線類型。ref還可以用於檢索欄位使用”=”操作符來比較的時候。
-
ref_or_null
與ref的唯一區別就是在使用索引引用的查詢之外再增加一個空值的查詢。這種連線類型類似ref，不同的是MySQL會在檢索的時候額外的搜尋包含NULL值的記錄。這種連線類型的最佳化是從MySQL 4.1.1開始的，它經常用於子查詢。
-
index_merge
查詢中同時使用兩個(或更多)索引，然後對索引結果進行合并(merge)，再讀取表資料。這種連線類型意味著使用了Index Merge最佳化方法。
-
unique_subquery
子查詢中的返回結果欄位組合是主鍵或唯一約束。
-
index_subquery
子查詢中的返回結果欄位組合是一個索引(或索引組合)，但不是一個主鍵或唯一索引。這種連線類型類似unique_subquery。它用子查詢來代替IN，不過它用於在子查詢中沒有唯一索引的情況下。
-
range
索引範圍掃描。只有在給定範圍的記錄才會被取出來，利用索引來取得一條記錄。
-
index
全索引掃描。連線類型跟ALL一樣，不同的是它只掃描索引樹。它通常會比ALL快點，因為索引檔案通常比資料檔案小。MySQL在查詢的欄位知識單獨的索引的一部分的情況下使用這種連線類型。
-
fulltext
全文索引掃描。
-
all
全表掃描。
該欄位是指MySQL在搜尋表記錄時可能使用哪個索引。如果沒有任何索引可以使用，就會顯示為null。
查詢最佳化工具從possible_keys中所選擇使用的索引。key欄位顯示了MySQL實際上要用的索引。當沒有任何索引被用到的時候，這個欄位的值就是NULL。
被選中使用索引的索引鍵長度。key_len欄位顯示了MySQL使用索引的長度。當key欄位的值為NULL時，索引的長度就是NULL。
列出是通過常量，還是某個表的某個欄位來過濾的。ref欄位顯示了哪些欄位或者常量被用來和key配合從表中查詢記錄出來。
該欄位顯示了查詢最佳化工具通過系統收集的統計資訊估算出來的結果集記錄條數。
該欄位顯示了查詢中MySQL的附加資訊。
這個列式在MySQL5.1裡新加進去的，當使用EXPLAIN EXTENDED時才會出現。它顯示的是針對錶裡符合某個條件(WHERE子句或聯結條件)的記錄數的百分比所作的一個悲觀估算。
SQL改寫
EXPLAIN除了可以顯示執行計畫外，還可以顯示SQL改寫。所謂SQL改寫，是指MySQL在對SQL語句進行最佳化前，會基於一些原則進行語句的改寫，以方便後面的最佳化器進行最佳化產生更優的執行計畫。該功能是通過EXPLAIN EXTENDED+SHOW WARNINGS配合使用。下面通過樣本說明一下。
從上面樣本中，可看到原有語句中的IN子查詢被改寫成為表間關聯的方式。
2、統計資訊
查看統計資訊也是最佳化語句中必不可少的一步。通過統計資訊可以快速瞭解對象的儲存特徵如何。下面說明主要的兩類統計資訊——表、索引。
表統計資訊 — SHOW TABLE STATUS
-
Name： 表名
-
Engine： 表的儲存引擎類型(ISAM、MyISAM或InnoDB)
-
Row_format： 行儲存格式(Fixed-固定的、Dynamic-動態或Compressed-壓縮的)
-
Rows： 行數量。在某些儲存引擎中，例如MyISAM和ISAM他們儲存了精確的記錄數。不過其他儲存引擎中，它可能只是近似值。
-
Avg_row_length： 平均行長度。
-
Data_length： 資料檔案的長度。
-
Max_data_length： 資料檔案的最大長度。
-
Index_length： 索引檔案的長度。
-
Data_free： 已指派但未使用了位元組數。
-
Auto_increment： 下一個autoincrement(自動加1)值。
-
Create_time： 表被創造的時間。
-
Update_time： 資料檔案最後更新的時間。
-
Check_time： 最後對錶運行一個檢查的時間。執行mysqlcheck命令後更新，僅對MyISAM有效。
-
Create_options： 額外留給CREATE TABLE的選項。
-
Comment： 當創造表時，使用的注釋(或為什麼MySQL不能存取表資訊的一些資訊)。
-
Version： 資料表的’.frm’檔案版本號碼。
-
Collation： 表的字元集和校正字元集。
-
Checksum： 即時的校正和值(如果有的話)。
3、索引統計資訊 — SHOW INDEX
-
Table： 表名。
-
Non_unique： 0，如果索引不能包含重複。
-
Key_name： 索引名
-
Seq_in_index： 索引中的列順序號，從1開始。
-
Column_name： 列名。
-
Collation： 列怎樣在索引中被排序。在MySQL中，這可以有值A(升序)或NULL(不排序)。
-
Cardinality： 索引中唯一值的數量。
-
Sub_part： 如果列只是部分被索引，索引字元的數量。當整個欄位都做索引了，那麼它的值是NULL。
-
Packed： 表示索引值是如何壓縮的，NULL表示沒有壓縮。
-
Null： 當欄位包括NULL的記錄是YES，它的值為，反之則是”。
-
Index_type： 使用了哪種索引演算法(有BTREE、FULLTEXT、HASH、RTREE)。
-
Comment： 備忘。
-
系統參數： 系統參數也會影響語句的執行效率。查看系統參數，可使用SHOW VARIABLES命令。
參數說明
系統參數很多，下面介紹幾個。
排序區大小。其大小直接影響排序使用的演算法。如果系統中排序都比較大、記憶體充足且並發量不是很大的情況，可以適當增加此參數。這個參數是針對單個Thead的。
Join操作使用記憶體地區大小。只有當Join是ALL、index、range或index_merge時使用到Join Buffer。如果join語句較多，可以適當增大join_buffer_size。需要注意到是，這個值針對單個Thread。每個Thread都會自己建立獨立的Buffer，而不是整個系統共用的Buffer，不要設定過大而造成系統記憶體不足。
如果記憶體內的暫存資料表超過該值，MySQL自動將它轉換為硬碟上的MyISAM表。如果執行許多進階GROUP BY查詢並且有大量記憶體，則可以增加tmp_table_size的值。
讀查詢操作所能使用的緩衝區大小。這個參數是針對單個Thead的。
4、最佳化器開關
在MySQL中，還有一些參數是可以用來控制最佳化器行為的。
參數說明
這個參數控制最佳化器在窮舉執行計畫時的限度。如果查詢長時間處於”statistics”狀態，可以考慮調低此參數。
預設是開啟的，這讓最佳化器會根據需要掃描的行數來決定是否跳過某些執行計畫。
這個變數包含了一些開啟/關閉最佳化器特性的標誌位。
樣本 — 幹預最佳化器行為（ICP特性）
預設情況下，ICP特性是開啟的。查看一下最佳化器行為。
基於二級索引的過濾查詢，使用了ICP特性，從Extra中的”Using index condition”可見。如果通過最佳化器開關，幹預最佳化器行為，又會如何呢？
從Extra可見，ICP特性已經禁用。
5、系統狀態（SHOW STATUS）
MySQL中也內建了一些狀態，通過這些狀態變數也可反映出語句執行的一些情況，方便定位問題。手工執行的話，可以在執行語句的前後分別執行SHOW STATUS命令，查看狀態的變化。當然，因狀態變數很多，對比起來不太方便，後面我介紹的小工具，可以解決這個問題。
狀態變數
狀態變數很多，這裡介紹幾個。
排序演算法已經執行的合并的數量。如果這個變數值較大，應考慮增加sort_buffer_size系統變數的值。
在範圍內執行的排序的數量。
已經排序的行數。
通過掃描表完成的排序的數量。
索引中第一條被讀的次數。讀取索引頭的次數，如果這個值很高，說明全索引掃描很多。
根據鍵讀一行的請求數。如果較高，說明查詢和表的索引正確。
按照鍵順序讀下一行的請求數。如果你用範圍約束或如果執行索引掃描來查詢索引列，該值增加。
按照鍵順序讀前一行的請求數。
根據固定位置讀一行的請求數。如果執行大量查詢並需要對結果進行排序該值較高。則可能使用了大量需要MySQL掃描整個表的查詢或串連沒有正確使用鍵。
在資料檔案中讀下一行的請求數。如果正進行大量的表掃描，該值較高。通常說明表索引不正確或寫入的查詢沒有利用索引。
6、SQL效能分析器（Query Profiler）
MySQL的Query Profiler是一個使用非常方便的Query診斷分析工具，通過該工具可以擷取一條Query在整個執行過程中多種資源的消耗情況，如CPU、IO、IPC、SWAP等，以及發生的PAGE FAULTS、CONTEXT SWITCHE等，同時還能得到該Query執行過程中的MySQL所調用的各個函數在源檔案中的位置。
使用方法
mysql> select @@profiling;
mysql> set profiling=1;
預設情況下profiling的值為0表示MySQL SQL Profiler處於OFF狀態，開啟SQL效能分析器後profiling的值為1。
mysql> select count(*) from t1;
使用”show profile”命令擷取當前系統中儲存的多個Query的profile的概要資訊。
mysql> show profiles;
+———-+————+———————–+
| Query_ID | Duration | Query |
+———-+————+———————–+
| 1 | 0.00039300 | select count(*) from t1 |
+———-+————+———————–+
在擷取概要資訊之後，就可以根據概要資訊的Query_ID來擷取某個Query的執行過程中詳細的profile資訊。
mysql> show profile for query 1;
mysql> show profile cpu,block io for query 1;
二、工具說明
前面談到了多種手段，對於SQL語句的調優都有所協助。通過下面這個小工具，可以自動調用命令將上面這些內容一次性推給DBA，大大加速最佳化的過程。
1、準備條件
2、調用方法
python mysql_tuning.py -p tuning_sql.ini -s ‘select xxx’
參數說明
-p 指定設定檔名稱
-s 指定SQL語句
3、設定檔
共分兩節資訊，分別是[database]描述資料庫連接資訊，[option]回合組態資訊。
[database]
server_ip = 127.0.0.1
db_user = testuser
db_pwd = testpwd
db_name = test
[option]
sys_parm = ON //是否顯示系統參數
sql_plan = ON //是否顯示執行計畫
obj_stat = ON //是否顯示相關對象(表、索引)統計資訊
ses_status = ON //是否顯示運行前後狀態資訊(啟用後會真實執行SQL)
sql_profile = ON //是否顯示PROFILE跟蹤資訊(啟用後會真實執行SQL)
4、輸出說明
標題部分
包含運行資料庫的地址資訊及資料版本資訊。
原始SQL
使用者執行輸入的SQL，這部分主要是為了後續對比SQL改寫時使用。語句顯示時使用了格式化。
系統級參數
指令碼選擇顯示了部分與SQL效能相關的參數。這部分是寫死在代碼中的，如需擴充需要修改指令碼。
最佳化器開關
下面是和最佳化器相關的一些參數，通過調整這些參數可以人為幹預最佳化器行為。
執行計畫
就是調用explain extended的輸出結果。如果結果過長，可能出現顯示串列的問題(暫時未解決)。
最佳化器改寫後的SQL
通過這裡可判斷最佳化器是否對SQL進行了某種最佳化(例如子查詢的處理)。
統計資訊
在SQL語句中所有涉及到的表及其索引的統計資訊都會在這裡顯示出來。
運行狀態資訊
在會話層級對比了執行前後的狀態(SHOW STATUS)，並將出現變化的部分顯示出來。需要注意的是，因為收集狀態資料是採用SELECT方式，會造成個別指標的誤差(例如Com_select)。
PROFILE詳細資料
調用SHOW PROFILE得到的詳細資料。
PROFILE匯總資訊
根據PROFILE的資源消耗情況，顯示不同階段消耗對比情況(TOP N)，直觀顯示”瓶頸”所在。
