性能剖析總結

• 1.定義性能最有效的方法是響應時間
• 2.如果無法測量就無法有效地優化，所以性能優化工作需要基于高質量、全方位及完整的響應時間測量
• 3.測量的最佳開始點是應用程序，而不是數據庫。即使問題出在底層的數據庫，借助良好的測量也可以很容易地發現問題
• 4.大多數系統無法完整地測量，測量有時候也會有錯誤的結果。但也可以想辦法繞過一些限制，并得到好的結果(但是要能意識到所使用的方法的缺陷和不確定性在哪里)
• 5.完整的測量會產生大量需要分析的數據，所以需要用到剖析器。這是最佳的工具，可以幫助將重要的問題冒泡到前面，這樣就可以決定從哪里開始分析會比較好
• 6.剖析報告是一種匯總信息，掩蓋和丟棄了太多細節。而且它不會告訴你缺少了什么，所以完全依賴剖析報告也是不明智的
• 7.有兩種消耗時間的操作:工作或者等待。大多數剖析器只能測量因為工作而消耗的時間，所以等待分析有時候是很有用的補充，尤其是當CPU利用率很低但工作卻一直無法完成的時候
• 8.優化和提升是兩回事。當繼續提升的成本超過收益的時候，應當停止優化
• 9.注意你的直覺，但應該只根據直覺來指導解決問題的思路，而不是用于確定系統的問題。決策應當盡量基于數據而不是感覺。

總的來說，解決性能問題的方法首先是要澄清問題，然后選擇合適的技術來解答這些問題。如果你想嘗試提升服務器的總體性能，那么一個比較好的七點是將所有查詢記錄到日志中，然后利用pt-query-digest工具生成系統級別的剖析報告。如果是要追查某些性能低下的查詢，記錄和剖析得方法也會有幫助。可以把精力放在尋找哪些消耗時間最多的、導致了糟糕的用戶體驗的，或者那些高度變化的，抑或有奇怪的響應時間直方圖的查詢。當找到了這些"壞"查詢時，要鉆取pt-query-digest報告中包含的該查詢的詳細信息，或者使用SHOW PROFILE及其他諸如EXPLAIN這樣的工具。

如果找不到這些查詢性能低下的原因，那么也可能時遇到了服務器級別的性能問題。這是，可以較高精度測量和回直服務器狀態計數器的細節信息。如果通過這樣的分析重現了問題，則應該通過同樣的數據制定一個可靠的觸發條件，來收集更多的診斷數據。多花費一點時間來確定可靠的觸發條件，盡量避免漏檢或者誤報。如果已經可以捕獲故障活動期間的數據，但還是無法找到其根本原因，則要么嘗試捕獲更多的數據，要么嘗試尋求幫助。
我們無法完整地測量工作系統，但說到底它們都是某種狀態機，所以只要足夠細心，邏輯清晰并且堅持下去，通常來說都能得到想要的結果。要注意的時不要把原因和結果搞混了，而且在確認問題之前也不要隨便針對系統做變動。
理論上純粹的自頂向下的方法分析和詳盡的測量只是理想的情況，而我們常常需要處理的是真實系統。真實系統是復雜且無法充分測量的，所以我們只能根據情況盡力而為。使用諸如pt-query-digest和MySQL企業監控器的查詢分析其這樣的工具并不完美，通常都不會給出問題根源的直接證據。但真的掌握了以后，已經足以完成大部分的優化診斷工作了。

Schema與數據類型優化

概述

良好的邏輯設計和物理設計是高性能的基石，應該根據系統將要執行的查詢語句來設計schema，這往往需要權衡各種因素。例如，反范式的設計可以加快某些類型的查詢，但同時可能使另一些類型的查詢變慢。比如添加計數器和匯總表是一種很好的優化查詢的方式，但這些表的維護成本可能會很高。MySQL獨有的特性和實現細節對性能的影響也很大。

選擇優化的數據類型

MySQL支持的數據類型非常多，選擇正確的數據類型對于獲取高性能至關重要。不管存儲哪種類型的數據，下面幾個簡單的原則都有助于做出更好的選擇。

• 1.更好的通常更好
  一般情況下，應該盡量使用可以正確存儲數據更小的數據類型(例如只需要存0~200,tinyint unsigned更好)。更小的數據類型通常更快，因為它們占用更少的磁盤、內存和CPU緩存，并且處理時需要的CPU周期也更少。
  但是要確保沒有嘀咕需要存儲的值范圍，因為在schema中的多個地方增加數據類型的范圍是一個非常耗時和痛苦的操作。如果無法確定哪個數據類型是最好的，就選擇你認為不會超過范圍的最小類型。(如果系統不是很忙或者存儲的數據量不多，或者是在可以輕易修改設計的早期階段，那之后修改數據類型也比較容易)
• 2.簡單就好
  簡單數據類型的操作通常需要更少的CPU周期。例如，整型比字符操作代價更低，因為字符集和校對規則(排序規則)使字符比較比整型比較更復雜。有兩個例子:一個是應該使用mySQL的內建類型(date,time,datetime)而不是字符串來存儲日期和時間，另外一個是應該使用整型存儲IP地址。
• 3.盡量避免NULL
  很多表都包含可為NULL(空值)的列，即使應用程序并不需要保存NULL也是如此，這是因為可為NULL是列的默認屬性。通常情況下最好指定列為NOT NULL，除非真的需要存儲NULL值。如果查詢中包含可為NULL的列，對MySQL來說更難優化，因為可為NULL的列使得索引、索引統計和值比較都更復雜。可為NULL的列會使用更多的存儲空間，在MySQL里也需要特殊處理。當可為NULl的列被索引時，每個索引記錄需要一個額外的字節，在MyISAM里甚至還可能導致固定大小的索引(例如只有一個整數列的索引)變成可變大小的索引。
  通常把可為NULl的列改為NOT NULL帶來的性能提升比較小，所以(調優時)沒有必要首先在現有schema中查找并修改掉這種情況，除非確定這回導致問題。但是，如果計劃在列上鍵索引，就應該盡量避免設計成可為NULL的列。
  當然也有例外，例如值得一提的時，InnoDB使用單獨的位(bit)存儲NULL值，所以對于稀疏數據(很多值為NULL，只有少數行的列有非NULL值)有很好的空間效率。但這一點不適用于MyISAM

在為列選擇數據類型時，第一步需要確定合適的大類型:數字、字符串、時間等。這通常是很簡單的。但是我們會提到一些特殊的不是那么直觀的例子。
下一步是選擇具體類型。很多MySQL的數據類型可以存儲相同類型的數據，這是存儲的長度和范圍不一樣、允許的精度不同，或者需要的物理空間(磁盤和內存空間)不同，相同大類型的不同子類型數據有時也有一些特殊的行為和屬性。
然而TIMESTAMP只使用DATETIME一半的存儲空間，并且會根據時區變化，具有特殊的自動更新能力。另一方面，TIMESTAMP允許的時間范圍要小得多，有時候它的特殊能力會成為阻礙。

整數類型

有兩種類型的數字:整數(whole number)和實數(real number)。如果存儲整數，可以使用這幾種整數類型:TINYINT,SMALLINT, MEDIUMINT,INT,BIGINT.分別使用8，16，24，32，64存儲空間。它們可以存儲的值的范圍從-2^(N-1)到2(N-1)-1,其中N是存儲空間的位數。
整數類型有可選的UNSIGNED屬性，表示不允許負值，這大致可以使正數的上限提高已被。例如TINYINT UNSIGNED可以存儲的范圍是0_{255,而TINYINY的存儲范圍是-128}127.
有符號和無符號類型使用相同的存儲空間，并具有相同的性能，因此可以根據實際情況選擇合適的類型。你的選擇決定MySQL是怎么在內存和磁盤中保存數據的，然而，整數計算一半使用64位的BIGINT整數，即使在32位環境也是如此。(一些聚合函數是例外，它們使用DECIMAL或者DOUBLE進行計算)。
MySQL可以為整數類型指定寬度，例如INT(11)，對大多數應用這是沒有意義的，他不會限制值得合法范圍，只是規定了MySQL的一些交互工具(例如MySQL命令行客戶端)用來顯示字符的個數。對于存儲和計算來說INT(1)和INT(20)是相同的。

實數類型

實數是帶有小數部分的數字。然而，它們不只是為了存儲小數部分；也可以使用DECIMAL存儲比BIGINT還大的整數。MySQL既支持精確類型，也支持不精確類型。
FLOAT和DOUBLE類型支持使用標準的浮點運算進行近似計算。如果需要直到浮點運算時怎么計算的，則需要研究所使用的平臺的浮點數的具體實現。DECIMAL類型用于存儲精確的小數。在MySQL 5.0和更高版本，DECIMAL類型支持精確計算。MySQL4.1及更早版本則使用浮點運算來實現DECIMAL的計算，這樣做會因為精度損失導致一些奇怪的結果。在這些版本的MySQL中，DECIMAL只是一個"存儲類型"。
因為CPU不支持對DECIMAL的直接計算，所以在MySQL5.0以及更高版本中，MySQL服務器自身實現了DECIMAL的高精度計算。相對而言，CPU直接支持原生浮點計算，所以浮點運算明顯更快。
浮點和DECIMAL類型都可以指定精度。對DECIMAL列，可以指定小數點前后所允許的最大位數。這會影響列的空間消耗。MySQL5.0和更高版本將數字打包保存到一個二進制字符串中(每4個字節存9個數字)。例如DECIMAL(18,9)小數點兩邊將個存儲9個數字，一共使用9個字節:小數點前的數字用4個字節，小數點后的數字用4個字節，小數點本身占1個字節。
MySQL5.0和更高版本中的DECIMAL類型允許最多65個數字。而早期的MySQL版本中，這個限制時254個數字，并且保存為未壓縮的字符串(每個數字一個字節)。然而，這些(早期)版本實際上并不能在計算中使用這么大的數字，因為DECIMAL只是一種存儲格式，在計算中DECIMAL會轉換為DOUBLE類型。
有多種方法可以指定浮點列所需要的精度，這回使得MySQL悄悄選擇不同的數據類型，或者在存儲時對值進行取舍。這些精度定義時非標準的，所以建議只指定數據類型，不指定精度。
浮點類型在存儲同樣范圍的值時，通常比DECIMAL使用更少的空間。FLOAT使用4個字節存儲。DOUBLE只能用8個字節，相比FLOAT有更高的精度和更大的范圍。和整數類型一樣，能選擇的只是存儲類型;MySQL使用DOUBLE作為內部浮點計算的類型。
因為需要額外的空間和計算開銷，所以應該盡量只在對小數進行精確計算時才使用DECIMAL——例如存儲財務數據。但在數據量比較大的時候，可以高鋁使用BIGINT代替DECIMAL,將需要存儲的貨幣單位根據小數的位數乘以相應的倍數即可。假設要存儲財務數據精確到萬分之一，則可以把所有金額乘以一百萬，然后將結果存儲在BIGINT里，這樣就可以同時避免浮點存儲計算不精確和DECIMAL精確計算代價高的問題