本節書摘來自異步社群出版社《mysql排錯指南》一書中的第1章，第1.6節，作者：【美】sveta smirnova（斯維特 斯米爾諾娃），更多章節内容可以通路雲栖社群“異步社群”公衆号檢視。
sql應用程式的一個常見問題就是性能退化。這一節将會介紹當你面對性能問題時的一些基本操作。不必擔心細節，僅僅關注本質的思想就好。随着知識的深入，你會發現你自己變得更加訓練有素，能夠更加高效地使用它們。
當我考慮本章應涵蓋哪些問題時，我曾猶豫是否應該涉及性能相關的問題。市面上有很多詳細介紹性能問題的資料，如mysql參考手冊中的“優化”章節以及o'reilly出版的相關書籍。我會在本書的最後簡單介紹一些有用的資料。你很容易就會把整個職業生涯都消耗在該問題上面，或者淹沒在可用資訊之中。
這裡将主要介紹select查詢。本節最後會簡單介紹一下如何處理修改資料的慢查詢問題。
處理慢查詢有3個主要技巧：調優查詢本身、調優表（包括增加索引）和調優伺服器。下面逐一詳細介紹。
最強大的查詢調優工具就是我們之前熟知的explain。這個工具為使用者提供了伺服器實際上如何執行查詢的詳細資訊。mysql 參考手冊已經詳細介紹了mysql的explain工具，是以這裡不再贅述。不過，我将會重點介紹輸出資訊中我認為是最重要、最有用的部分。
第一行中你要注意的是type，它展示了連接配接（join）的執行方式；還要注意rows，它展示了在查詢執行過程中檢查的行數的估計（例如，如果查詢必須掃描整個表，那麼rows展示的數值和該表中的行數相等）。多表連接配接需要檢查的行數是每個表中檢查行數的笛卡兒積。也就是說，如果請求在第一個表中檢查20行，另一個表中檢查30行，那麼連接配接一共執行了600次檢查。explain會包含join中每個表中的行。我們将通過下面的示例進行說明。
即使在操作單表時，explain也會報告連接配接。這可能聽起來有些奇怪，因為mysql的内部優化器把每個請求都當成一個連接配接，哪怕是單表上的連接配接。
我們來回顧一下前面介紹過的explain輸出：
檢查的行數是10乘以1，因為子查詢對于外部查詢的每一行執行一次。第一個查詢的類型是index，這意味着全部索引都将被讀取。第二個查詢的類型是index_subquery。這是一個索引查找函數，工作方式類似于ref類型。是以，在這個示例中，優化器将從itmes表中讀取全部索引記錄，并且對于從itmes表中查詢到10行記錄中的每一行，對應從items_links表中讀取一行記錄。
該如何确認這是一個對該查詢合理的執行方式呢？首先，重複查詢結果并檢查查詢的實際執行時間：
mysql伺服器檢查了10行然後傳回結果是4。這有多快？為了回答這個問題，統計每個表中的行數：
itmes表中有10行記錄，每行都有一個唯一的id。items_links表中有6行記錄，沒有唯一的id(iid)。對于目前的資料規模來說，這個設計看起來可以，不過與此同時，這也透露出一個潛在的問題。目前，links數小于items數，并且數目的差異不是很大，但是如果數目差距巨大，那就更加值得注意了。
為了驗證這個猜想，同時也為了舉例說明查詢調優的方法，我将向items表中插入一些資料。id字段定義為int not null auto_increment primary key，這樣可以確定對新插入的行不會存在關聯（link）。這樣，我就可以模仿現實中使用者想從一個大表中查詢少量link的場景（在該場景中是6個）。下面的語句展現了一個快速構造資料的小技巧，即重複地從表中選擇所有行，然後再插入更多行：
現在，看一下查詢的執行計劃是否有所變化：
查詢的執行計劃并沒有變化──這次為了6個link檢查了5136行！有沒有什麼方式可以重寫一下這個查詢，使其運作得更快一些呢？
子查詢的類型是index_subquery。這意味着優化器使用索引查詢函數完全替代了子查詢。show warnings的輸出展示了查詢是如何重寫的：
輸出資訊看起來令人生畏，不過至少在這裡可以看到一些連接配接。如果我們重寫查詢，讓在其上執行連接配接的列更加明顯，那又會怎麼樣？我們也将重寫子查詢，使之變成顯式join；在目前版本的mysql中，這個方法能夠顯著地提高性能：
結果看起來挺讓人振奮的，因為它沒有搜尋items表中的所有行。不過，這個查詢結果正确嗎？
我們得到了6行，而不是4行。這是因為我們要求傳回所有比對的行，這裡有相同的link被比對了兩次。可以通過添加distinct關鍵字來修複這個問題：
提示 提示
可以通過查詢重寫技巧來确認是否需要添加 distinct 關鍵字。隻要将count(*)替換成items.id，就可以看到重複的值。
加上distinct後，該查詢一樣高效嗎？我們再次通過explain來驗證一下：
它仍然檢查了6行記錄。是以，我們可以認為對于這個特定的資料集，該查詢得到了優化。本章後面将會解釋為何資料結構及其容量會有影響。
在該示例中，資料集是小規模的，是以即使在我的筆記本電腦上，我也無法讓其真正執行得特别緩慢。不過，原始的和優化過的查詢的執行時間的确有很大不同。下面是原始查詢的時間：
下面是優化過的查詢的時間：
對于如此小的資料集，查詢的時間仍然降低了一半！在測試中，雖然僅提高了0.11秒，不過如果對于上百萬行，那麼效率提升效果就會更好。
你剛剛學習了一個基本的使用explain指令的查詢調試技巧：閱讀目前查詢的資訊并與你預期的資訊進行比較。該過程可以用來調優從最簡單到最複雜的任何查詢。
上一節介紹了調優查詢的過程。在全部示例中，explain的輸出都包括索引資訊。那麼，如果表沒有索引會如何？或者如果沒有使用到索引呢？你該如何選擇在什麼時候、什麼地方、添加哪種索引呢？
當結果有限的時候，mysql伺服器會使用索引。是以，在與where、join和group by語句相關的列上添加索引可以加速查詢。在與order by語句相關的列上添加索引也會有效果，因為它将使伺服器更高效地排序。
在掌握這些規則的前提下，添加索引就成為了一個很簡單的工作。考察之前示例中的表，但沒有任何索引：
如你所見，沒有指定任何索引。我們在這些表上試驗一個沒有優化過的查詢，然後再優化它：
類型變成了 all，這是最耗時的類型，因為這表示會讀取所有行。該查詢這次檢查了6 * 5137 = 30 822行。這甚至比之前示例中我們認為的慢查詢還要糟。
來仔細檢查一下這個查詢：
這個查詢傳回結果集中唯一非空值的數目。應該在items.id列上添加索引，以使該查詢使用索引。
該查詢的另一部分是：
這裡有items表中id字段和items_links表中iid字段的連接配接。是以，應該在這兩列上添加索引。
現在看一下添加索引對查詢計劃的影響：
這看起來比之前好了很多，隻有一點不好： items表這次的類型是ref，比之前的eq_ref要差。這是因為我們添加的是一個簡單索引，而原始表在該列已經有唯一索引了。我們也可以簡單地修改該臨時表，因為id是唯一的并且也應該如此：
現在，當已經使用了執行更快的eq_ref類型的時候，可以删除items.id字段上多餘的索引。這在你關心資料修改的查詢速度的時候尤為重要，因為每次更新索引都會消耗時間。下一節會讨論何時該停止優化。
你剛剛學習了索引是如何影響查詢執行的以及何時應該添加索引。
選擇你自己的執行計劃
索引實際上也有減慢查詢的時候。在這種情況下，應該删除索引或者使用會忽略索引（ignore index）的語句（如果其他的查詢還需要用到該索引）。也可以使用強制索引（force index）使優化器知道你想要使用的索引。這些語句對于查詢調優也非常有用，比如當你想要了解特定索引對性能會有怎樣影響的時候。隻需要通過explain指令執行語句，然後分析輸出。
盡管使用ignore index和force index 可能聽起來不錯，但是你應該避免在生産環境中使用，除非你已經準備好在今後的每個更新版本中都逐一檢查使用了該語句查詢。
因為優化器總是試圖選擇最佳的執行計劃，随着版本的更新，可能對于同一個join會使用不同的執行計劃，是以這種檢查是必要的。當你沒有強制使用或者忽略索引的時候，優化器會按照它認為最佳的計劃執行。但是，如果你明确指定優化器在多表join的某個表中應該如何使用索引，那麼這個規則可能會造成其他影響，并且這個最終的執行計劃在新版本中可能會比之前要差。
在對單一表的查詢中使用ignore index和force index相對安全。對所有其他的情況，必須在更新後檢查確定查詢的執行計劃沒有改變。
在産品中使用ignore index和force index的另一個問題是對于指定表的最佳執行計劃依賴于其存儲的資料。一般的步驟是，優化器檢查表的統計資料然後依此調整計劃，當然在你使用了ignore index和force index的時候不會這樣做。如果你使用這些語句，你就必須定期檢查在應用程式的生命周期它們是否還有效。
前面讨論了簡單查詢。即使是簡單查詢，我們仍找到了優化的方向，有時經過一步一步的調優我們獲得了更好的結果。當你處理包含很多join條件，或者where子句和group by字句中包含很多字段的複雜查詢時，你就會擁有更多的選擇。可以認為你總會找到使性能更好的方法，并且這種優化永無止境。是以，現在問題是，什麼時候可以認為查詢優化合理并可以停止進一步優化。
深入了解性能優化的技術自然可以幫助你選擇合适的解決方案。不過，哪怕你自認不是專家，我們也有一些基本原則可以幫你決定停止優化。
首先，你應該了解查詢都做了什麼。例如，下面的查詢：
始終會傳回表中的所有列，該語句沒有什麼可優化的空間。
不過，即使你查詢所有列，添加join語句也會使情況改變：
這會産生優化效果，因為on條件限制了結果集。當然，同樣的分析也适用于where和group by條件。
其次，你應該通過explain輸出檢視連接配接類型。盡管你想要獲得可能的最佳的join類型，但是你應時刻意識到你的資料的限制。例如，非唯一的行永遠不會産生eq_ref或者更好的類型。
當你優化查詢的時候，你的資料是非常重要的。對于同樣的執行計劃，不同的資料可能會産生完全不同的結果。最簡單的示例就是比較表中隻有一行記錄和表中超過50%的行都有相同值的結果。在這樣情況下，使用索引會降低性能而不是提升性能。
另一個規則：不要隻依賴于explain的輸出，要衡量實際的查詢執行時間。
你需要牢記的另一件事情是索引在修改表時的影響。盡管索引通常會提高select查詢的性能，但是它會略微降低修改資料的查詢的性能，尤其是insert語句。是以，有些時候為了加快插入的速度，允許select查詢略慢是明智的。要時刻牢記考察你整個應用程式的性能，而不僅僅是某一個查詢的性能。
假如你已經對查詢進行了完全的優化，找不到進一步優化的方法，但是它仍然很慢，那麼還有沒有辦法可以提高它的性能呢？有的。有很多伺服器選項可以讓你調節對查詢有影響的因素，比如記憶體中臨時表的大小、排序緩沖區等。有些針對特定存儲引擎（如innodb）的選項，也會對查詢優化很有用。
第3章将詳細介紹這些選項。這裡僅對如何使用它們進行性能優化做一個概述。
調整伺服器的配置從某種程度來說是一個影響全局的行為，因為每個修改都可能對該伺服器上的每個查詢造成影響（對于指定引擎的選項，會影響每個使用該引擎的表上的查詢）。不過有些選項是針對特定類型的優化的，如果你的請求沒有滿足條件，它将沒有任何作用。
第一個需要檢查的選項是緩沖區大小（buffer size）。每個緩沖區都有其存在的特定原因。一般的規律是大緩沖區意味着高性能──不過僅當請求可以針對該緩沖區扮演的特定角色使用大容量緩存的時候。
當然，增加緩沖區大小是有代價的。下面是一些大緩沖區可能帶來的影響。我不是想要阻止你使用大緩沖區，因為在合理的環境下它是提高性能非常有效的手段。你僅需要牢記下面的要素然後合理地調整大小。
交換區（swapping）
大容量緩沖區可能會導緻使用到作業系統級别的交換區進而造成性能緩慢，這取決于系統記憶體大小。通常情況下，mysql伺服器在它所需的所有記憶體都來自實體記憶體的時候運作最快。當它使用到交換區的時候，性能顯著下降。
當為緩沖區配置設定的記憶體大小超過伺服器的實體記憶體大小的時候就會使用到交換區。請注意，有一些緩沖區是針對每個使用者線程的。要确定這些緩沖區究竟需要多少記憶體，可以用公式最大連接配接數 緩沖區大小（max_connections buffer_size）來計算。計算出所有緩沖區的記憶體和，并確定和小于mysqld伺服器可以使用的記憶體大小。這個計算的值不是決定性的，因為mysqld實際上可以配置設定多于你明确指定大小的記憶體。
啟動時間
mysqld需要配置設定的記憶體越多，其啟動時間就越長。
過期資料
我們還會有伸縮性問題，大部分時候是來自線程間的緩存共享。在這些場景中，擴充緩沖區做緩存會産生記憶體碎片。你通常會在伺服器運作數小時後發現記憶體碎片問題，該問題發生在舊的資料需要從緩沖區中移除以給新資料騰出空間的時候。這會導緻高速運轉的伺服器突然變慢。第3章會給出這樣的示例。
讨論完緩沖區，第3章讨論其他選項。屆時，我們不僅關注性能優化選項（如優化器選項），還會關注一些控制高可用性的選項。事務運作得越安全，就需要更多的檢查和更慢地執行性能。不過，要注意這些選項；隻有在你可以為了性能犧牲安全的時候才調優它們。
當你調優配置設定的時候，把性能作為整體來考慮尤為重要，因為每個選項都會影響整個伺服器。例如，如果你沒有使用特定引擎，針對該存儲引擎的選項調優不會有任何作用。這是顯而易見的，不過我确實見過很多安裝環境下有大量關于myisam引擎的選項，然而卻使用的innodb存儲引擎，或者相反的情況。如果你用一些通用配置作為模闆，這些注意點就尤為重要。
我們讨論了影響select性能的因素，在本節我們開始優化修改資料的查詢。update和delete查詢可以使用與select語句一樣的條件去限制受影響的行數。是以，可以使用相同的優化規則。
我們在1.3節中學習了如何把update和delete查詢轉換成select查詢，然後使用explain進行調試。可以在5.6.3以下版本的系統上使用該技巧解決性能問題，從5.6.3版本開始增加了explain對insert、update和delete查詢的支援，不過，請牢記update和delete查詢有時候與相應的select查詢的執行方式略有不同。
通過在查詢計劃前後查詢handler_%的狀态可以檢查是否使用了索引：
1 這裡使用了show status指令，這是show seesion status的同義指令，作用是檢視目前會話的變量狀态。
在測試前使用flush status查詢可以友善地重置這些變量。
我們将繼續介紹之前清單中的特定變量。你需要注意的是，因為這些是累積的值，是以它們會随着你的每次查詢增長。現在我們開始優化1.3節中的查詢示例，使其更新可以為空的列：
這條語句沒有修改任何行，因為資料在之前損壞了：現在每個字段中的值是0而不是null。但是這個請求執行非常緩慢。我們來看一下處理程式變量：
可以看到handler_read_rnd_next的值非常高，該值代表從datafile中讀取下一個值的頻繁程度。過高的值一般代表使用了全表掃描，這對性能是有影響的。handler_read_key也是一個相關的變量，表示讀取索引的請求數目。正常情況下該值相對于handler_read_rnd_next來說不應該這麼低，因為這意味着大部分行的讀取都沒有使用的索引。此外，handler_commit和handler_read_first的值也增長緩慢。它們分别代表事務送出的次數和讀索引中第一項的次數。最後，handler_read_first的值是1，表明我們請求伺服器讀取索引中第一條記錄，這可以當作全索引掃描的标志。
希望對這些handler_%狀态變量的簡介可以告訴你如何利用它們去檢查查詢是怎樣執行的。對于該查詢是否有提升空間這個問題将作為作業留給讀者自己去解答。
我僅将對insert查詢做些說明。它們沒有條件去限制受影響的行數，是以表中的索引隻會降低插入效率，因為每次插入都需要更新索引。插入的性能需要通過伺服器選項調優。特别地，影響innodb存儲引擎的選項會很有作用。
一種加速插入的方式是把多個插入合并成一條語句，這也叫做“批量插入”（bulk insert）：
不過，請注意插入會阻塞行甚至是整張表，是以其他查詢會在插入的過程中被拒絕通路。我将給出一個通用規則來結束本節内容：
在優化任何單個查詢的時候，請時刻注意整個應用程式的性能。
我們剛剛學習了如何優化伺服器選項才能顯著提升性能。在本章我們也學習了如何優化特定查詢以提升其運作速度。優化查詢和優化伺服器一般是解決性能問題的兩種選擇。那麼，有沒有通用的規則告訴我們該從哪個方向開始優化呢？
我認為恐怕沒有。優化伺服器選項看起來特别有效以至于許多人認為合理地的改變選項将會使mysqld運作得如火箭般高效。如果你也是那麼想的，我不得不讓你失望了：不好的查詢寫法仍會耗盡伺服器資源。并且你可能在重新開機伺服器後僅僅感受到幾個小時的伺服器高性能，然後它就又變慢了，因為每個查詢都需要消耗很多的資源并且你的緩存将會充滿。有時候，伺服器會被數以百萬計的查詢淹沒，需要更多的資源。
然後，優化每個查詢也不是一個好選擇。有些請求很少調用，是以沒有必要在這些查詢上浪費人力。還有的查詢可能查詢表中的所有行，這些查詢就沒有必要去嘗試優化了。
我一般推薦“混合”的優化模式。先優化伺服器選項，特别注意你使用的存儲引擎相關的選項，然後優化查詢。當優化完重要的查詢後，回頭再檢查伺服器選項，考慮是否有進一步的優化空間，然後再繼續優化剩下的查詢，反複如此，直到你對性能滿意。
你也可以從自己的應用程式中最慢的查詢開始或者找到那些可以通過簡單的優化獲得顯著提升的查詢，然後優化伺服器選項。參考之前展示的狀态變量，第6章将詳細介紹它們。
最後同樣重要的是：在性能優化中參考大量的資訊以形成你自己的政策。