優(yōu)化SQL，是DBA常見的工作之一。如何高效、快速地優(yōu)化一條語句，是每個(gè)DBA經(jīng)常要面對(duì)的一個(gè)問題。在日常的優(yōu)化工作中，我發(fā)現(xiàn)有很多操作是在優(yōu)化過程中必不可少的步驟。然而這些步驟重復(fù)性的執(zhí)行，又會(huì)耗費(fèi)DBA很多精力。

今天抽空總結(jié)下mysql數(shù)據(jù)庫(kù)常見的一些優(yōu)化步驟，僅供參考。

執(zhí)行計(jì)劃 — EXPLAIN命令

執(zhí)行計(jì)劃是語句優(yōu)化的主要切入點(diǎn)，通過執(zhí)行計(jì)劃的判讀了解語句的執(zhí)行過程。在執(zhí)行計(jì)劃生成方面，MySQL與Oracle明顯不同，它不會(huì)緩存執(zhí)行計(jì)劃，每次都執(zhí)行“硬解析”。

1. 基本用法

EXPLAIN QUERY 

當(dāng)在一個(gè)Select語句前使用關(guān)鍵字EXPLAIN時(shí)，MySQL會(huì)解釋了即將如何運(yùn)行該Select語句，它顯示了表如何連接、連接的順序等信息。

EXPLAIN EXTENDED QUERY 

當(dāng)使用EXTENDED關(guān)鍵字時(shí)，EXPLAIN產(chǎn)生附加信息，可以用SHOW WARNINGS瀏覽。該信息顯示優(yōu)化器限定SELECT語句中的表和列名，重寫并且執(zhí)行優(yōu)化規(guī)則后SELECT語句是什么樣子，并且還可能包括優(yōu)化過程的其它注解。

EXPLAIN PARTITIONS QUERY 

顯示的是查詢要訪問的數(shù)據(jù)分片——如果有分片的話。它只能在MySQL5.1及更新的版本里使用。

EXPLAIN FORMAT=JSON (5.6新特性) 

另一個(gè)格式顯示執(zhí)行計(jì)劃?？梢钥吹街T如表間關(guān)聯(lián)方式等信息。

2. 輸出字段

下面說明一下EXPLAIN輸出的字段含義:

(1) id

MySQL選定的執(zhí)行計(jì)劃中查詢的序列號(hào)。如果語句里沒有子查詢等情況，那么整個(gè)輸出里就只有一個(gè)SELECT，這樣一來每一行在這個(gè)列上都會(huì)顯示一個(gè)1。如果語句中使用了子查詢、集合操作、臨時(shí)表等情況，會(huì)給ID列帶來很大的復(fù)雜性。

(2) select_type

語句所使用的查詢類型。是簡(jiǎn)單SELECT還是復(fù)雜SELECT(如果是后者，顯示它屬于哪一種復(fù)雜類型)。常用有以下幾種標(biāo)記類型。

• DEPENDENT SUBQUERY：子查詢內(nèi)層的第一個(gè)SELECT，依賴于外部查詢的結(jié)果集。
• DEPENDENT UNION：子查詢中的UNION，且為UNION中從第二個(gè)SELECT開始的后面所有SELECT，同樣依賴于外部查詢的結(jié)果集。
• PRIMARY：子查詢中的最外層查詢，注意并不是主鍵查詢。
• SIMPLE：除子查詢或UNION之外的其他查詢。
• SUBQUERY：子查詢內(nèi)層查詢的第一個(gè)SELECT，結(jié)果不依賴于外部查詢結(jié)果集。
• UNCACHEABLE SUBQUERY：結(jié)果集無法緩存的子查詢。
• UNION：UNION語句中的第二個(gè)SELECT開始后面的所有SELECT，第一個(gè)SELECT為PRIMARY。
• UNION RESULT：UNION中的合并結(jié)果。從UNION臨時(shí)表獲取結(jié)果的SELECT。
• DERIVED：衍生表查詢(FROM子句中的子查詢)。MySQL會(huì)遞歸執(zhí)行這些子查詢，把結(jié)果放在臨時(shí)表里。在內(nèi)部，服務(wù)器就把當(dāng)做一個(gè)"衍生表"那樣來引用，因?yàn)榕R時(shí)表就是源自子查詢。

(3) table

這一步所訪問的數(shù)據(jù)庫(kù)中表的名稱或者SQL語句指定的一個(gè)別名表。這個(gè)值可能是表名、表的別名或者一個(gè)為查詢產(chǎn)生的臨時(shí)表的標(biāo)識(shí)符，如派生表、子查詢或集合。

(4) type

表的訪問方式。以下列出了各種不同類型的表連接，依次是從最好的到最差的。

• system：系統(tǒng)表，表只有一行記錄。這是const表連接類型的一個(gè)特例。
• const：讀常量，最多只有一行匹配的記錄。由于只有一行記錄，優(yōu)化程序里該行記錄的字段值可以被當(dāng)作是一個(gè)恒定值。const用于在和PRIMARY KEY或UNIQUE索引中有固定值比較的情形。
• eq_ref：最多只會(huì)有一條匹配結(jié)果，一般是通過主鍵或唯一鍵索引來訪問。從該表中會(huì)有一行記錄被讀取出來以和從前一個(gè)表中讀取出來的記錄做聯(lián)合。與const類型不同的是，這是最好的連接類型。它用在索引所有部分都用于做連接并且這個(gè)索引是一個(gè)PRIMARY KEY或UNIQUE類型。eq_ref可以用于在進(jìn)行"="做比較時(shí)檢索字段。比較的值可以是固定值或者是表達(dá)式，表達(dá)示中可以使用表里的字段，它們?cè)谧x表之前已經(jīng)準(zhǔn)備好了。
• ref：JOIN語句中驅(qū)動(dòng)表索引引用的查詢。該表中所有符合檢索值的記錄都會(huì)被取出來和從上一個(gè)表中取出來的記錄作聯(lián)合。ref用于連接程序使用鍵的最左前綴或者是該鍵不是PRIMARY KEY或UNIQUE索引(換句話說，就是連接程序無法根據(jù)鍵值只取得一條記錄)的情況。當(dāng)根據(jù)鍵值只查詢到少數(shù)幾條匹配的記錄時(shí)，這就是一個(gè)不錯(cuò)的連接類型。ref還可以用于檢索字段使用"="操作符來比較的時(shí)候。
• ref_or_null：與ref的唯一區(qū)別就是在使用索引引用的查詢之外再增加一個(gè)空值的查詢。這種連接類型類似ref，不同的是MySQL會(huì)在檢索的時(shí)候額外的搜索包含NULL值的記錄。這種連接類型的優(yōu)化是從MySQL 4.1.1開始的，它經(jīng)常用于子查詢。
• index_merge：查詢中同時(shí)使用兩個(gè)(或更多)索引，然后對(duì)索引結(jié)果進(jìn)行合并(merge)，再讀取表數(shù)據(jù)。這種連接類型意味著使用了Index Merge優(yōu)化方法。
• unique_subquery：子查詢中的返回結(jié)果字段組合是主鍵或唯一約束。
• index_subquery：子查詢中的返回結(jié)果字段組合是一個(gè)索引(或索引組合)，但不是一個(gè)主鍵或唯一索引。這種連接類型類似unique_subquery。它用子查詢來代替IN，不過它用于在子查詢中沒有唯一索引的情況下。
• range：索引范圍掃描。只有在給定范圍的記錄才會(huì)被取出來，利用索引來取得一條記錄。
• index：全索引掃描。連接類型跟ALL一樣，不同的是它只掃描索引樹。它通常會(huì)比ALL快點(diǎn)，因?yàn)樗饕募ǔ１葦?shù)據(jù)文件小。MySQL在查詢的字段知識(shí)單獨(dú)的索引的一部分的情況下使用這種連接類型。
• fulltext：全文索引掃描。
• all：全表掃描。

(5) possible_keys

該字段是指MySQL在搜索表記錄時(shí)可能使用哪個(gè)索引。如果沒有任何索引可以使用，就會(huì)顯示為null。

(6) key

查詢優(yōu)化器從possible_keys中所選擇使用的索引。key字段顯示了MySQL實(shí)際上要用的索引。當(dāng)沒有任何索引被用到的時(shí)候，這個(gè)字段的值就是NULL。

(7) key_len

被選中使用索引的索引鍵長(zhǎng)度。key_len字段顯示了MySQL使用索引的長(zhǎng)度。當(dāng)key字段的值為NULL時(shí)，索引的長(zhǎng)度就是NULL。

(8) ref

列出是通過常量，還是某個(gè)表的某個(gè)字段來過濾的。ref字段顯示了哪些字段或者常量被用來和key配合從表中查詢記錄出來。

(9) rows

該字段顯示了查詢優(yōu)化器通過系統(tǒng)收集的統(tǒng)計(jì)信息估算出來的結(jié)果集記錄條數(shù)。

(10) Extra

該字段顯示了查詢中MySQL的附加信息。

(11) filtered

這個(gè)列式在MySQL5.1里新加進(jìn)去的，當(dāng)使用EXPLAIN EXTENDED時(shí)才會(huì)出現(xiàn)。它顯示的是針對(duì)表里符合某個(gè)條件(WHERE子句或聯(lián)接條件)的記錄數(shù)的百分比所作的一個(gè)悲觀估算。

3. SQL改寫

EXPLAIN除了可以顯示執(zhí)行計(jì)劃外，還可以顯示SQL改寫。所謂SQL改寫，是指MySQL在對(duì)SQL語句進(jìn)行優(yōu)化前，會(huì)基于一些原則進(jìn)行語句的改寫，以方便后面的優(yōu)化器進(jìn)行優(yōu)化生成更優(yōu)的執(zhí)行計(jì)劃。該功能是通過EXPLAIN EXTENDED+SHOW WARNINGS配合使用。

簡(jiǎn)單做個(gè)實(shí)驗(yàn)：

從上面示例中，可看到原有語句中的IN子查詢被改寫成為表間關(guān)聯(lián)的方式。

二、統(tǒng)計(jì)信息

查看統(tǒng)計(jì)信息也是優(yōu)化語句中必不可少的一步。通過統(tǒng)計(jì)信息可以快速了解對(duì)象的存儲(chǔ)特征如何。下面說明主要的兩類統(tǒng)計(jì)信息——表、索引。

1. 表統(tǒng)計(jì)信息 — SHOW TABLE STATUS

說明如下：

• Name：表名
• Engine：表的存儲(chǔ)引擎類型(ISAM、MyISAM或InnoDB)
• Row_format：行存儲(chǔ)格式(Fixed-固定的、Dynamic-動(dòng)態(tài)的或Compressed-壓縮的)
• Rows：行數(shù)量。在某些存儲(chǔ)引擎中，例如MyISAM和ISAM他們存儲(chǔ)了精確的記錄數(shù)。不過其他存儲(chǔ)引擎中，它可能只是近似值。
• Avg_row_length：平均行長(zhǎng)度。
• Data_length：數(shù)據(jù)文件的長(zhǎng)度。
• Max_data_length：數(shù)據(jù)文件的最大長(zhǎng)度。
• Index_length：索引文件的長(zhǎng)度。
• Data_free：已分配但未使用了字節(jié)數(shù)。
• Auto_increment：下一個(gè)autoincrement(自動(dòng)加1)值。
• Create_time：表被創(chuàng)造的時(shí)間。
• Update_time：數(shù)據(jù)文件最后更新的時(shí)間。
• Check_time：最后對(duì)表運(yùn)行一個(gè)檢查的時(shí)間。執(zhí)行mysqlcheck命令后更新，僅對(duì)MyISAM有效。
• Create_options：額外留給CREATE TABLE的選項(xiàng)。
• Comment：當(dāng)創(chuàng)造表時(shí)，使用的注釋(或?yàn)槭裁碝ySQL不能存取表信息的一些信息)。
• Version：數(shù)據(jù)表的'.frm'文件版本號(hào)。
• Collation：表的字符集和校正字符集。
• Checksum：實(shí)時(shí)的校驗(yàn)和值(如果有的話)。

三、索引統(tǒng)計(jì)信息 — SHOW INDEX

說明如下：

• Table：表名。
• Non_unique：0，如果索引不能包含重復(fù)。
• Key_name：索引名
• Seq_in_index：索引中的列順序號(hào)，從1開始。
• Column_name：列名。
• Collation：列怎樣在索引中被排序。在MySQL中，這可以有值A(chǔ)(升序)或NULL(不排序)。
• Cardinality：索引中唯一值的數(shù)量。
• Sub_part：如果列只是部分被索引，索引字符的數(shù)量。當(dāng)整個(gè)字段都做索引了，那么它的值是NULL。
• Packed：表示鍵值是如何壓縮的，NULL表示沒有壓縮。
• Null：當(dāng)字段包括NULL的記錄是YES，它的值為，反之則是''。
• Index_type：使用了哪種索引算法(有BTREE、FULLTEXT、HASH、RTREE)。
• Comment：備注。
• 系統(tǒng)參數(shù)：系統(tǒng)參數(shù)也會(huì)影響語句的執(zhí)行效率。查看系統(tǒng)參數(shù)，可使用SHOW VARIABLES命令。

四、優(yōu)化器開關(guān)

在MySQL中，還有一些參數(shù)是可以用來控制優(yōu)化器行為的。

1. 參數(shù)說明

• optimizer_search_depth：這個(gè)參數(shù)控制優(yōu)化器在窮舉執(zhí)行計(jì)劃時(shí)的限度。如果查詢長(zhǎng)時(shí)間處于"statistics"狀態(tài)，可以考慮調(diào)低此參數(shù)。
• optimizer_prune_level：默認(rèn)是打開的，這讓優(yōu)化器會(huì)根據(jù)需要掃描的行數(shù)來決定是否跳過某些執(zhí)行計(jì)劃。
• optimizer_switch：這個(gè)變量包含了一些開啟/關(guān)閉優(yōu)化器特性的標(biāo)志位。

2. 實(shí)驗(yàn)--干預(yù)優(yōu)化器行為(ICP特性)

默認(rèn)情況下，ICP特性是開啟的。查看一下優(yōu)化器行為。

基于二級(jí)索引的過濾查詢，使用了ICP特性，從Extra中的”Using index condition”可見。如果通過優(yōu)化器開關(guān)，干預(yù)優(yōu)化器行為，又會(huì)如何呢?

set optimizer_switch="index_condition_pushdown=off"; 

從Extra可見，ICP特性已經(jīng)禁用。

五、系統(tǒng)狀態(tài)(SHOW STATUS)

MySQL中也內(nèi)置了一些狀態(tài)，通過這些狀態(tài)變量也可反映出語句執(zhí)行的一些情況，方便定位問題。手工執(zhí)行的話，可以在執(zhí)行語句的前后分別執(zhí)行SHOW STATUS命令，查看狀態(tài)的變化。

狀態(tài)變量：

狀態(tài)變量很多，這里只介紹幾個(gè)常用的。

• Sort_merge_passes：排序算法已經(jīng)執(zhí)行的合并的數(shù)量。如果這個(gè)變量值較大，應(yīng)考慮增加sort_buffer_size系統(tǒng)變量的值。
• Sort_range：在范圍內(nèi)執(zhí)行的排序的數(shù)量。
• Sort_rows：已經(jīng)排序的行數(shù)。
• Sort_scan：通過掃描表完成的排序的數(shù)量。
• Handler_read_first：索引中第一條被讀的次數(shù)。讀取索引頭的次數(shù)，如果這個(gè)值很高，說明全索引掃描很多。
• Handler_read_key：根據(jù)鍵讀一行的請(qǐng)求數(shù)。如果較高，說明查詢和表的索引正確。
• Handler_read_next：按照鍵順序讀下一行的請(qǐng)求數(shù)。如果你用范圍約束或如果執(zhí)行索引掃描來查詢索引列，該值增加。
• Handler_read_prev：按照鍵順序讀前一行的請(qǐng)求數(shù)。
• Handler_read_rnd：根據(jù)固定位置讀一行的請(qǐng)求數(shù)。如果執(zhí)行大量查詢并需要對(duì)結(jié)果進(jìn)行排序該值較高。則可能使用了大量需要MySQL掃描整個(gè)表的查詢或連接沒有正確使用鍵。
• Handler_read_rnd_next：在數(shù)據(jù)文件中讀下一行的請(qǐng)求數(shù)。如果正進(jìn)行大量的表掃描，該值較高。通常說明表索引不正確或?qū)懭氲牟樵儧]有利用索引。

六、SQL性能分析器(Query Profiler)

MySQL的Query Profiler是一個(gè)使用非常方便的Query診斷分析工具，通過該工具可以獲取一條Query在整個(gè)執(zhí)行過程中多種資源的消耗情況，如CPU、IO、IPC、SWAP等，以及發(fā)生的PAGE FAULTS、CONTEXT SWITCHE等，同時(shí)還能得到該Query執(zhí)行過程中的MySQL所調(diào)用的各個(gè)函數(shù)在源文件中的位置。

1. 開啟

mysql> select @@profiling;  
mysql> set profiling=1; 

默認(rèn)情況下profiling的值為0表示MySQL SQL Profiler處于OFF狀態(tài)，開啟SQL性能分析器后profiling的值為1。

2. 執(zhí)行SQL語句

mysql> select count(*) from t1; 

3. 獲取概要信息

使用"show profile"命令獲取當(dāng)前系統(tǒng)中保存的多個(gè)Query的profile的概要信息。

mysql> show profiles; 

4. 針對(duì)單個(gè)Query獲取詳細(xì)的profile信息

在獲取概要信息之后，就可以根據(jù)概要信息的Query_ID來獲取某個(gè)Query的執(zhí)行過程中詳細(xì)的profile信息。

mysql> show profile for query 1;  
mysql> show profile cpu,block io for query 1;