譯者 | 崔皓

審校 | 孫淑娟

模式設(shè)計(jì)、索引、查詢、配置、I/O......還會出錯？遵循這10個(gè)提示，保證讓你的MySQL服務(wù)器流暢到飛起。

MySQL是世界上使用最廣泛的開源數(shù)據(jù)庫，它在業(yè)界的受歡迎程度讓其他數(shù)據(jù)庫望塵莫及。它是一個(gè)關(guān)系型數(shù)據(jù)庫管理系統(tǒng)，多年來都作為流行應(yīng)用程序的核心。然而，在使用上可能會遇到挑戰(zhàn)，因此在性能提高上存在很多機(jī)會。

在過去的幾年里，MySQL也有一些重要的新發(fā)展。本文更新了Baron Schwartz??之前??提供的??一套MySQL性能調(diào)整技巧??。

下面是10個(gè)MySQL性能調(diào)優(yōu)技巧。

目錄

• MySQL性能提示1：模式設(shè)計(jì)與任何其他MySQL設(shè)置一樣重要
• MySQL性能提示2：輔助索引（Secondary Key）不是你的敵人
• MySQL性能提示3：行可以從索引中獲得服務(wù)
• MySQL性能提示4 :審查與回顧
• MySQL性能提示5：可見性很重要
• MySQL性能提示6：謹(jǐn)慎使用調(diào)優(yōu)工具
• MySQL性能提示7：I/O操作仍然昂貴
• MySQL性能提示8：利用通用表的表達(dá)式優(yōu)勢
• MySQL性能提示9：注意云計(jì)算
• MySQL性能提示10：保持Replication的最新狀態(tài)

MySQL性能提示1：模式設(shè)計(jì)與任何其他MySQL設(shè)置一樣重要

模式設(shè)計(jì)是在數(shù)據(jù)庫中最應(yīng)該重視的事情之一。這一數(shù)據(jù)庫設(shè)計(jì)原則，早在20世紀(jì)70年代就被引入。自MySQL從5.6版本中轉(zhuǎn)移到InnoDB作為默認(rèn)存儲引擎后，模式設(shè)計(jì)變得更加重要。

為什么會這樣呢？在InnoDB中，所有的東西都是主鍵!這與InnoDB組織數(shù)據(jù)的方式有關(guān)。在InnoDB中，主鍵（Primary Key）是集群的，每一個(gè)輔助索引（Secondary Key）都會為主鍵增加一個(gè)入口指針。如果你在設(shè)計(jì)模式時(shí)沒有考慮到這一點(diǎn)，那么性能將受到負(fù)面影響。

數(shù)據(jù)是使用B樹索引存儲的，因此以有序的方式插入數(shù)據(jù)（即使用準(zhǔn)序列值）可以防止主鍵碎片化，從而減少尋找葉節(jié)點(diǎn)所需的I/O操作。

在一些用例中，順序主鍵不是正確的選擇--這里的一個(gè)很好的例子是通用唯一標(biāo)識符或UUID。

MySQL性能提示2：輔助索引（Secondary Key）不是你的敵人

輔助索引（Secondary Key）是由一個(gè)后臺進(jìn)程更新的。因此，對性能的影響并不像你所期望的那樣嚴(yán)重。相反，問題是圍繞著磁盤占用的，因?yàn)樵黾虞o助索引（Secondary Key）會增加存儲需求。

對一個(gè)沒有索引的字段進(jìn)行過濾，可能會導(dǎo)致每次查詢運(yùn)行時(shí)都要進(jìn)行全表掃描。當(dāng)然，這可能會導(dǎo)致巨大的性能影響。因此，有一個(gè)輔助索引（Secondary Key）比沒有要好。

也就是說，不應(yīng)該過度添加索引，因?yàn)樵黾舆^多的索引可能不會實(shí)現(xiàn)性能改進(jìn)。同時(shí)，這些額外的索引可能會增加你的存儲成本，而且InnoDB必須執(zhí)行許多后臺操作來保持索引的更新。

MySQL性能提示3：行可以從索引中獲得服務(wù)

InnoDB可以直接從索引中找到并實(shí)際服務(wù)于行記錄，而輔助索引（Secondary Key）則指向主鍵，主鍵則包含行記錄本身。如果InnoDB緩沖池足夠大，它也可以在內(nèi)存中容納大多數(shù)數(shù)據(jù)。你甚至可以使用復(fù)合鍵，這對查詢來說通常比單獨(dú)的每列鍵更有效。MySQL可以在每個(gè)表的訪問中使用一個(gè)索引，所以如果你正在運(yùn)行帶有WHERE x=1和y=2這樣的子句的查詢，那么在x,y上建立聯(lián)合索引比在每個(gè)列上有單獨(dú)的索引要好。

此外，對x,y的聯(lián)合索引也可以提高以下查詢的性能。

SELECT y FROM table WHERE x=1

MySQL將使用覆蓋索引，并從內(nèi)存中的索引中提供Y。

在實(shí)踐中，當(dāng)你有機(jī)會的時(shí)候，你可以通過使用聯(lián)合索引來提高性能。無論何時(shí)，當(dāng)你設(shè)計(jì)聯(lián)合索引時(shí)，可以通過從左到右的方式讀取索引，所以給定一個(gè)這樣的查詢。

SELECT a,b,c FROM table WHERE a=1 and b=2

那么，一個(gè)關(guān)于a,b的聯(lián)合索引將有助于查詢。但是如果查詢是下面這個(gè)格式。

SELECT a,b,c FROM table WHERE b=2

那么，這個(gè)a，b的聯(lián)合索引就是無效的，因?yàn)檫`反的最左原則，也就是從左往右讀取索引，因此會導(dǎo)致全表掃描。總是從左邊讀取索引的想法也適用于其他一些情況。例如，給定以下查詢。

SELECT a,b,c FROM table WHERE a=1 and c=2

那么在a,b,c上的聯(lián)合索引將只讀取第一列，因?yàn)闆]有通過列b過濾的WHERE子句。所以在這種情況下，MySQL可以部分地讀取索引，這比全表掃描要好，但仍然不足以獲得查詢的最佳性能。

另一個(gè)與查詢設(shè)計(jì)有關(guān)的元素是最左邊的索引方法，因?yàn)檫@是MySQL中常用的優(yōu)化。例如，一個(gè)關(guān)于a,b,c的索引將不包括像select a,c where c=x這樣的查詢，因?yàn)樵摬樵儾荒芴^索引的第一部分，即a,b。然而，如果你有一個(gè)類似select c,count(c) where a=x and b=y group by c的查詢，它對a,b進(jìn)行過濾并對c進(jìn)行分組，那么a,b,c上的一個(gè)索引可以幫助進(jìn)行過濾和分組。

MySQL性能提示4 :審查與回顧

僅僅擁有一輛一級方程式賽車并不能贏得比賽。如果你把一個(gè)沒有經(jīng)驗(yàn)的司機(jī)放在方向盤后面，而他們在第一個(gè)彎道就撞車了，那就不會贏。同樣地，你可能有地球上調(diào)整得最好的MySQL服務(wù)器，但如果你有糟糕的查詢，你的數(shù)據(jù)庫就會比它應(yīng)該的慢。

你應(yīng)該隨著時(shí)間的推移定期審查你的查詢設(shè)計(jì)，因?yàn)槟愕膽?yīng)用程序會隨著新功能和錯誤的修復(fù)而改變。應(yīng)用程序的數(shù)據(jù)集和使用模式也可能隨著時(shí)間的推移而改變，所有這些都會影響查詢的性能。

留出時(shí)間進(jìn)行查詢審查和監(jiān)控查詢，執(zhí)行時(shí)間是非常重要的。你可以為此使用慢速查詢?nèi)罩净蛐阅苣Ｊ?，但?shí)施監(jiān)控工具將幫助你獲得更好的數(shù)據(jù)。

請記住，并不總是最慢的查詢才是最需要解決的問題。例如，你可能有一個(gè)耗時(shí)30秒但每天運(yùn)行兩次的查詢，與一個(gè)耗時(shí)1秒但每分鐘運(yùn)行100次的查詢并存。為了取得大的勝利，你應(yīng)該開始優(yōu)化第二個(gè)查詢，因?yàn)閺拈L遠(yuǎn)來看，改進(jìn)這個(gè)查詢可以節(jié)省大量的時(shí)間和資源。

MySQL性能提示5：可見性很重要

監(jiān)測是性能調(diào)整的關(guān)鍵因素之一。如果不了解當(dāng)前的工作負(fù)載和模式，就很難給出任何具體的建議。近年來，MySQL改進(jìn)了對低級別的MySQL/InnoDB指標(biāo)的暴露，這有助于了解工作負(fù)載。

例如，在早期版本中，性能模式是一個(gè)瓶頸，有相當(dāng)大的影響，特別是如果你有許多表。在最近的MySQL版本中，就存在許多變化，如新的數(shù)據(jù)字典，已經(jīng)改善了性能，現(xiàn)在的版本可以有很多表，但并不會對性能造成大的影響。

大多數(shù)現(xiàn)代監(jiān)控工具都以某種方式使用性能模式，所以一個(gè)很好的建議是查看這些工具并選擇最適合你的工具。對性能數(shù)據(jù)的可見性可能是一筆巨大的財(cái)富。

MySQL性能提示6：謹(jǐn)慎使用調(diào)優(yōu)工具

調(diào)優(yōu)工具給出的建議在大多數(shù)情況下是有效的。然而，每個(gè)工作負(fù)載和每個(gè)模式有所不同。在某些情況下，調(diào)優(yōu)工具的建議并不奏效，在相信這些建議時(shí)，謹(jǐn)慎行事是明智的選擇。對于MySQL而言，可以對配置進(jìn)行如下更改。

例如，將innodb_buffer_pool_size設(shè)置為總內(nèi)存的75%是好的經(jīng)驗(yàn)法則。然而，現(xiàn)在在數(shù)百GB的內(nèi)存服務(wù)器的情況下，如果你有512GB的內(nèi)存，那就會留下128GB的自由空間，而不是專門用于緩沖池，這是一種很大的浪費(fèi)。

innodb_log_file_size和innodb_log_files_in_group也是根據(jù)RAM的數(shù)量來定義。在內(nèi)存超過128GB的服務(wù)器上，這個(gè)設(shè)置沒有什么意義，因?yàn)樗鼘?chuàng)建64個(gè)重做日志文件（Redo log），每個(gè)2GB。這將導(dǎo)致128GB的重做日志（Redo log）存儲在磁盤上。在大多數(shù)情況下，不需要大的重做日志文件（Redo log），即使在最繁忙的環(huán)境中。因此，這并不是一個(gè)好的建議。

innodb_flushing_method是啟用自動配置時(shí)唯一正確配置的值。這個(gè)變量將flushing 方法設(shè)置為O_DIRECT_NO_FSYNC，這是使用Ext4或XFS文件系統(tǒng)時(shí)推薦的方法，因?yàn)樗苊饬藬?shù)據(jù)的雙重緩沖。

一個(gè)好的建議是，在專用服務(wù)器上將innodb_buffer_pool_size設(shè)置為75%或80%。在擁有大量內(nèi)存的服務(wù)器上，即超過128GB的服務(wù)器，在對內(nèi)存消耗進(jìn)行適當(dāng)?shù)姆治龊?，將其增加?0%甚至更多。同樣，對于innodb_log_file_size和innodb_log_files_in_group  來說大多數(shù)情況下，從2GB的文件開始，監(jiān)測寫日志操作。通常情況下，在確定重做日志（Redo log）的大小時(shí)，建議覆蓋大約一個(gè)小時(shí)的寫入量。

關(guān)于innodb_flush_method，對于Ext4或XFS等現(xiàn)代Linux文件系統(tǒng)，這個(gè)選項(xiàng)應(yīng)該被設(shè)置為O_DIRECT或O_DIRECT_NO_FSYNC。

MySQL性能提示7：I/O操作仍然昂貴

MySQL和InnoDB試圖最小化它們進(jìn)行的I/O操作的數(shù)量，因?yàn)樵L問存儲層在應(yīng)用性能方面是昂貴的。有一些設(shè)置可以影響InnoDB執(zhí)行的I/O操作的數(shù)量。其中有兩個(gè)設(shè)置經(jīng)常被誤解，而改變它們往往會導(dǎo)致性能問題。

innodb_io_capacity和innodb_io_capacity_max是與后臺Flushing的I/O操作數(shù)量有關(guān)的變量。許多客戶增加這些設(shè)置的值，以利用現(xiàn)代固態(tài)硬盤的優(yōu)勢，它可以在相對較低的延遲下提供非常高的I/O容量。雖然這個(gè)想法看上去很合理，但增加I/O容量設(shè)置會導(dǎo)致一些問題。

第一個(gè)問題是通過使InnoDB過快地刷新臟頁而導(dǎo)致性能下降，從而減少了“被刷新前多次修改一個(gè)頁面的機(jī)會”。將臟頁保留在內(nèi)存中可以大大減少將數(shù)據(jù)寫入存儲的I/O操作。

其次，固態(tài)硬盤在出現(xiàn)性能下降之前有一個(gè)預(yù)期的寫入次數(shù)。因此，增加寫操作的數(shù)量會影響你的固態(tài)硬盤的壽命，即使你使用的是高端硬盤。

雖說云主機(jī)最近很流行，在云中運(yùn)行MySQL服務(wù)實(shí)例也是可行的。然而，云中的服務(wù)器往往會有I/O限制，或者會對使用更多的I/O收取更多的費(fèi)用。通過了解這些限制，你可以仔細(xì)配置這些參數(shù)，以確保不達(dá)到這些限制，并使I/O操作最小化。

提到innodb_lru_scan_depth也很重要，因?yàn)檫@個(gè)設(shè)置控制了緩沖池LRU頁面列表中，頁面清潔器線程在多遠(yuǎn)的位置掃描臟頁。如果你有一個(gè)大的緩沖池和許多緩沖池實(shí)例的重寫工作負(fù)載，你可以通過減少這個(gè)變量來減少I/O的操作。

一個(gè)好的建議是保持默認(rèn)值，除非你知道你需要改變它們。

還值得一提的是，最新的固態(tài)硬盤是專門為交易型數(shù)據(jù)庫而優(yōu)化的。西部數(shù)據(jù)就是一個(gè)例子，該公司尋求專家的幫助，以幫助他們滿足正在創(chuàng)建的新一輪應(yīng)用的要求。

MySQL性能提示8：利用通用表的表達(dá)式優(yōu)勢

MySQL 8.0引入了通用表的表達(dá)式（CTE），這可以避免創(chuàng)建派生表的嵌套查詢。這個(gè)功能允許創(chuàng)建一個(gè)自定義查詢并引用結(jié)果，就好像是一個(gè)臨時(shí)表或一個(gè)視圖一樣。不同的是，CTEs可以在一個(gè)事務(wù)中被多次引用，而不需要明確地創(chuàng)建和刪除它們。

鑒于CTEs只被實(shí)例化一次，它們在運(yùn)行多個(gè)查詢的復(fù)雜事務(wù)中往往更快。另外，支持CTE遞歸，可以在SQL語言中輕松創(chuàng)建復(fù)雜的結(jié)構(gòu)，如分層模型和系列。

MySQL性能提示9：注意云計(jì)算

對于MySQL部署，有許多不同的云選項(xiàng)值得考慮，從在虛擬機(jī)中實(shí)施MySQL服務(wù)器實(shí)例，到使用數(shù)據(jù)庫即服務(wù)（DBaaS）解決方案，選擇的范圍很廣。

許多這樣的服務(wù)承諾提供顯著的性能提升。在一些簡單的用例中，這種做法是可行的。然而，即使是在云端，也必須理解數(shù)據(jù)庫的基本原理，否則成本將大大增加。這種成本增加往往是通過增加更多的硬件來解決問題，而并沒有從設(shè)計(jì)上找問題。

MySQL性能提示10：保持Replication的最新狀態(tài)

近年來，圍繞著MySQL Replication進(jìn)行了許多改進(jìn)，在許多情況下，它無法及時(shí)同步主服務(wù)器寫入操作。在最新的MySQL主要版本中，Replication默認(rèn)是并行的，這意味著多個(gè)Replication線程正在運(yùn)行并試圖同時(shí)應(yīng)用事務(wù)。

當(dāng)然，執(zhí)行效率在很大程度上取決于應(yīng)用程序?qū)懭氲墓ぷ髁浚诖蠖鄶?shù)情況下，并行復(fù)制可以幫助復(fù)制體跟上寫入操作。你可以用replica_parallel_type和replica_parallel_workers這兩個(gè)變量來控制。使用LOGICAL_CLOCK類型，事務(wù)被并行應(yīng)用，并根據(jù)時(shí)間戳追蹤依賴關(guān)系。

總的來說，MySQL是數(shù)百萬開發(fā)者的領(lǐng)先開源數(shù)據(jù)庫，它將繼續(xù)成為世界范圍內(nèi)創(chuàng)建應(yīng)用程序的首選平臺。通過研究圍繞模式設(shè)計(jì)、索引、調(diào)整和I/O的問題，可以極大地提高應(yīng)用程序的性能。不要忘了，像轉(zhuǎn)移到云端這樣的部署方法也會有性能影響，因此要謹(jǐn)慎考慮。

譯者介紹

崔皓，51CTO社區(qū)編輯，資深架構(gòu)師，擁有18年的軟件開發(fā)和架構(gòu)經(jīng)驗(yàn)，10年分布式架構(gòu)經(jīng)驗(yàn)。

原文標(biāo)題：??10 more essential MySQL performance tuning tips??，作者：Tibor K?r?cz