譯者 | 崔皓
審校 | 孫淑娟
模式設計、索引、查詢、配置、I/O......還會出錯？遵循這10個提示，保證讓你的MySQL服務器流暢到飛起。
MySQL是世界上使用最廣泛的開源數(shù)據(jù)庫，它在業(yè)界的受歡迎程度讓其他數(shù)據(jù)庫望塵莫及。它是一個關系型數(shù)據(jù)庫管理系統(tǒng)，多年來都作為流行應用程序的核心。然而，在使用上可能會遇到挑戰(zhàn)，因此在性能提高上存在很多機會。
在過去的幾年里，MySQL也有一些重要的新發(fā)展。本文更新了Baron Schwartz??之前??提供的??一套MySQL性能調(diào)整技巧??。
下面是10個MySQL性能調(diào)優(yōu)技巧。
目錄
- MySQL性能提示1：模式設計與任何其他MySQL設置一樣重要
- MySQL性能提示2：輔助索引（Secondary Key）不是你的敵人
- MySQL性能提示3：行可以從索引中獲得服務
- MySQL性能提示4 :審查與回顧
- MySQL性能提示5：可見性很重要
- MySQL性能提示6：謹慎使用調(diào)優(yōu)工具
- MySQL性能提示7：I/O操作仍然昂貴
- MySQL性能提示8：利用通用表的表達式優(yōu)勢
- MySQL性能提示9：注意云計算
- MySQL性能提示10：保持Replication的最新狀態(tài)
MySQL性能提示1：模式設計與任何其他MySQL設置一樣重要
模式設計是在數(shù)據(jù)庫中最應該重視的事情之一。這一數(shù)據(jù)庫設計原則，早在20世紀70年代就被引入。自MySQL從5.6版本中轉(zhuǎn)移到InnoDB作為默認存儲引擎后，模式設計變得更加重要。
為什么會這樣呢？在InnoDB中，所有的東西都是主鍵!這與InnoDB組織數(shù)據(jù)的方式有關。在InnoDB中，主鍵（Primary Key）是集群的，每一個輔助索引（Secondary Key）都會為主鍵增加一個入口指針。如果你在設計模式時沒有考慮到這一點，那么性能將受到負面影響。
數(shù)據(jù)是使用B樹索引存儲的，因此以有序的方式插入數(shù)據(jù)（即使用準序列值）可以防止主鍵碎片化，從而減少尋找葉節(jié)點所需的I/O操作。
在一些用例中，順序主鍵不是正確的選擇--這里的一個很好的例子是通用唯一標識符或UUID。
MySQL性能提示2：輔助索引（Secondary Key）不是你的敵人
輔助索引（Secondary Key）是由一個后臺進程更新的。因此，對性能的影響并不像你所期望的那樣嚴重。相反，問題是圍繞著磁盤占用的，因為增加輔助索引（Secondary Key）會增加存儲需求。
對一個沒有索引的字段進行過濾，可能會導致每次查詢運行時都要進行全表掃描。當然，這可能會導致巨大的性能影響。因此，有一個輔助索引（Secondary Key）比沒有要好。
也就是說，不應該過度添加索引，因為增加過多的索引可能不會實現(xiàn)性能改進。同時，這些額外的索引可能會增加你的存儲成本，而且InnoDB必須執(zhí)行許多后臺操作來保持索引的更新。
MySQL性能提示3：行可以從索引中獲得服務
InnoDB可以直接從索引中找到并實際服務于行記錄，而輔助索引（Secondary Key）則指向主鍵，主鍵則包含行記錄本身。如果InnoDB緩沖池足夠大，它也可以在內(nèi)存中容納大多數(shù)數(shù)據(jù)。你甚至可以使用復合鍵，這對查詢來說通常比單獨的每列鍵更有效。MySQL可以在每個表的訪問中使用一個索引，所以如果你正在運行帶有WHERE x=1和y=2這樣的子句的查詢，那么在x,y上建立聯(lián)合索引比在每個列上有單獨的索引要好。
此外，對x,y的聯(lián)合索引也可以提高以下查詢的性能。
MySQL將使用覆蓋索引，并從內(nèi)存中的索引中提供Y。
在實踐中，當你有機會的時候，你可以通過使用聯(lián)合索引來提高性能。無論何時，當你設計聯(lián)合索引時，可以通過從左到右的方式讀取索引，所以給定一個這樣的查詢。
那么，一個關于a,b的聯(lián)合索引將有助于查詢。但是如果查詢是下面這個格式。
那么，這個a，b的聯(lián)合索引就是無效的，因為違反的最左原則，也就是從左往右讀取索引，因此會導致全表掃描。總是從左邊讀取索引的想法也適用于其他一些情況。例如，給定以下查詢。
那么在a,b,c上的聯(lián)合索引將只讀取第一列，因為沒有通過列b過濾的WHERE子句。所以在這種情況下，MySQL可以部分地讀取索引，這比全表掃描要好，但仍然不足以獲得查詢的最佳性能。
另一個與查詢設計有關的元素是最左邊的索引方法，因為這是MySQL中常用的優(yōu)化。例如，一個關于a,b,c的索引將不包括像select a,c where c=x這樣的查詢，因為該查詢不能跳過索引的第一部分，即a,b。然而，如果你有一個類似select c,count(c) where a=x and b=y group by c的查詢，它對a,b進行過濾并對c進行分組，那么a,b,c上的一個索引可以幫助進行過濾和分組。
MySQL性能提示4 :審查與回顧
僅僅擁有一輛一級方程式賽車并不能贏得比賽。如果你把一個沒有經(jīng)驗的司機放在方向盤后面，而他們在第一個彎道就撞車了，那就不會贏。同樣地，你可能有地球上調(diào)整得最好的MySQL服務器，但如果你有糟糕的查詢，你的數(shù)據(jù)庫就會比它應該的慢。
你應該隨著時間的推移定期審查你的查詢設計，因為你的應用程序會隨著新功能和錯誤的修復而改變。應用程序的數(shù)據(jù)集和使用模式也可能隨著時間的推移而改變，所有這些都會影響查詢的性能。
留出時間進行查詢審查和監(jiān)控查詢，執(zhí)行時間是非常重要的。你可以為此使用慢速查詢?nèi)罩净蛐阅苣Ｊ剑珜嵤┍O(jiān)控工具將幫助你獲得更好的數(shù)據(jù)。
請記住，并不總是最慢的查詢才是最需要解決的問題。例如，你可能有一個耗時30秒但每天運行兩次的查詢，與一個耗時1秒但每分鐘運行100次的查詢并存。為了取得大的勝利，你應該開始優(yōu)化第二個查詢，因為從長遠來看，改進這個查詢可以節(jié)省大量的時間和資源。
MySQL性能提示5：可見性很重要
監(jiān)測是性能調(diào)整的關鍵因素之一。如果不了解當前的工作負載和模式，就很難給出任何具體的建議。近年來，MySQL改進了對低級別的MySQL/InnoDB指標的暴露，這有助于了解工作負載。
例如，在早期版本中，性能模式是一個瓶頸，有相當大的影響，特別是如果你有許多表。在最近的MySQL版本中，就存在許多變化，如新的數(shù)據(jù)字典，已經(jīng)改善了性能，現(xiàn)在的版本可以有很多表，但并不會對性能造成大的影響。
大多數(shù)現(xiàn)代監(jiān)控工具都以某種方式使用性能模式，所以一個很好的建議是查看這些工具并選擇最適合你的工具。對性能數(shù)據(jù)的可見性可能是一筆巨大的財富。
MySQL性能提示6：謹慎使用調(diào)優(yōu)工具
調(diào)優(yōu)工具給出的建議在大多數(shù)情況下是有效的。然而，每個工作負載和每個模式有所不同。在某些情況下，調(diào)優(yōu)工具的建議并不奏效，在相信這些建議時，謹慎行事是明智的選擇。對于MySQL而言，可以對配置進行如下更改。
例如，將innodb_buffer_pool_size設置為總內(nèi)存的75%是好的經(jīng)驗法則。然而，現(xiàn)在在數(shù)百GB的內(nèi)存服務器的情況下，如果你有512GB的內(nèi)存，那就會留下128GB的自由空間，而不是專門用于緩沖池，這是一種很大的浪費。
innodb_log_file_size和innodb_log_files_in_group也是根據(jù)RAM的數(shù)量來定義。在內(nèi)存超過128GB的服務器上，這個設置沒有什么意義，因為它將創(chuàng)建64個重做日志文件（Redo log），每個2GB。這將導致128GB的重做日志（Redo log）存儲在磁盤上。在大多數(shù)情況下，不需要大的重做日志文件（Redo log），即使在最繁忙的環(huán)境中。因此，這并不是一個好的建議。
innodb_flushing_method是啟用自動配置時唯一正確配置的值。這個變量將flushing 方法設置為O_DIRECT_NO_FSYNC，這是使用Ext4或XFS文件系統(tǒng)時推薦的方法，因為它避免了數(shù)據(jù)的雙重緩沖。
一個好的建議是，在專用服務器上將innodb_buffer_pool_size設置為75%或80%。在擁有大量內(nèi)存的服務器上，即超過128GB的服務器，在對內(nèi)存消耗進行適當?shù)姆治龊螅瑢⑵湓黾拥?0%甚至更多。同樣，對于innodb_log_file_size和innodb_log_files_in_group 來說大多數(shù)情況下，從2GB的文件開始，監(jiān)測寫日志操作。通常情況下，在確定重做日志（Redo log）的大小時，建議覆蓋大約一個小時的寫入量。
關于innodb_flush_method，對于Ext4或XFS等現(xiàn)代Linux文件系統(tǒng)，這個選項應該被設置為O_DIRECT或O_DIRECT_NO_FSYNC。
MySQL性能提示7：I/O操作仍然昂貴
MySQL和InnoDB試圖最小化它們進行的I/O操作的數(shù)量，因為訪問存儲層在應用性能方面是昂貴的。有一些設置可以影響InnoDB執(zhí)行的I/O操作的數(shù)量。其中有兩個設置經(jīng)常被誤解，而改變它們往往會導致性能問題。
innodb_io_capacity和innodb_io_capacity_max是與后臺Flushing的I/O操作數(shù)量有關的變量。許多客戶增加這些設置的值，以利用現(xiàn)代固態(tài)硬盤的優(yōu)勢，它可以在相對較低的延遲下提供非常高的I/O容量。雖然這個想法看上去很合理，但增加I/O容量設置會導致一些問題。
第一個問題是通過使InnoDB過快地刷新臟頁而導致性能下降，從而減少了“被刷新前多次修改一個頁面的機會”。將臟頁保留在內(nèi)存中可以大大減少將數(shù)據(jù)寫入存儲的I/O操作。
其次，固態(tài)硬盤在出現(xiàn)性能下降之前有一個預期的寫入次數(shù)。因此，增加寫操作的數(shù)量會影響你的固態(tài)硬盤的壽命，即使你使用的是高端硬盤。
雖說云主機最近很流行，在云中運行MySQL服務實例也是可行的。然而，云中的服務器往往會有I/O限制，或者會對使用更多的I/O收取更多的費用。通過了解這些限制，你可以仔細配置這些參數(shù)，以確保不達到這些限制，并使I/O操作最小化。
提到innodb_lru_scan_depth也很重要，因為這個設置控制了緩沖池LRU頁面列表中，頁面清潔器線程在多遠的位置掃描臟頁。如果你有一個大的緩沖池和許多緩沖池實例的重寫工作負載，你可以通過減少這個變量來減少I/O的操作。
一個好的建議是保持默認值，除非你知道你需要改變它們。
還值得一提的是，最新的固態(tài)硬盤是專門為交易型數(shù)據(jù)庫而優(yōu)化的。西部數(shù)據(jù)就是一個例子，該公司尋求專家的幫助，以幫助他們滿足正在創(chuàng)建的新一輪應用的要求。
MySQL性能提示8：利用通用表的表達式優(yōu)勢
MySQL 8.0引入了通用表的表達式（CTE），這可以避免創(chuàng)建派生表的嵌套查詢。這個功能允許創(chuàng)建一個自定義查詢并引用結(jié)果，就好像是一個臨時表或一個視圖一樣。不同的是，CTEs可以在一個事務中被多次引用，而不需要明確地創(chuàng)建和刪除它們。
鑒于CTEs只被實例化一次，它們在運行多個查詢的復雜事務中往往更快。另外，支持CTE遞歸，可以在SQL語言中輕松創(chuàng)建復雜的結(jié)構(gòu)，如分層模型和系列。
MySQL性能提示9：注意云計算
對于MySQL部署，有許多不同的云選項值得考慮，從在虛擬機中實施MySQL服務器實例，到使用數(shù)據(jù)庫即服務（DBaaS）解決方案，選擇的范圍很廣。
許多這樣的服務承諾提供顯著的性能提升。在一些簡單的用例中，這種做法是可行的。然而，即使是在云端，也必須理解數(shù)據(jù)庫的基本原理，否則成本將大大增加。這種成本增加往往是通過增加更多的硬件來解決問題，而并沒有從設計上找問題。
MySQL性能提示10：保持Replication的最新狀態(tài)
近年來，圍繞著MySQL Replication進行了許多改進，在許多情況下，它無法及時同步主服務器寫入操作。在最新的MySQL主要版本中，Replication默認是并行的，這意味著多個Replication線程正在運行并試圖同時應用事務。
當然，執(zhí)行效率在很大程度上取決于應用程序?qū)懭氲墓ぷ髁浚诖蠖鄶?shù)情況下，并行復制可以幫助復制體跟上寫入操作。你可以用replica_parallel_type和replica_parallel_workers這兩個變量來控制。使用LOGICAL_CLOCK類型，事務被并行應用，并根據(jù)時間戳追蹤依賴關系。
總的來說，MySQL是數(shù)百萬開發(fā)者的領先開源數(shù)據(jù)庫，它將繼續(xù)成為世界范圍內(nèi)創(chuàng)建應用程序的首選平臺。通過研究圍繞模式設計、索引、調(diào)整和I/O的問題，可以極大地提高應用程序的性能。不要忘了，像轉(zhuǎn)移到云端這樣的部署方法也會有性能影響，因此要謹慎考慮。
譯者介紹
崔皓，51CTO社區(qū)編輯，資深架構(gòu)師，擁有18年的軟件開發(fā)和架構(gòu)經(jīng)驗，10年分布式架構(gòu)經(jīng)驗。
原文標題：??10 more essential MySQL performance tuning tips??，作者：Tibor K?r?cz
