你所不知道的MySQL數據庫性能優化方案

關注公眾號，后臺回復“自動化”

一、MySQL數據庫引擎的選擇

MySQL數據庫中最重要的一個概念就是數據庫引擎，不同的數據庫引擎的工作原理存在很大差異最終造成MySQL數據庫服務的性能差異。

例如如果數據庫引擎需要支持事務，就必須滿足事務的基本特性——AICD特性（AICD：原子性、隔離性、一致性和永久性。屬于基礎知識所以不在這里贅述），那么自然就需要一定處理機制來實現這些特性。這樣做的現實效果就是導致寫入同樣數據量的情況下，支持事務的數據庫引擎比不支持事務的數據庫引擎耗費更多的時間。

這里我們首先為讀者列舉MySQL數據庫社區版中支持的數據庫引擎（部分）：

1. MEMORY：

MEMORY存儲引擎將表的數據完全存放在內存中。在MySQL數據庫的歷史版本中和該數據庫引擎類似的其它引擎是HEAP，后者曾是MySQL數據庫中訪問速度最快的數據庫引擎。

但由于這兩種數據庫引擎完全工作在內存中，所以如果MySQL或者服務器重新啟動，數據庫引擎中保存的數據將會丟失。

2. BLACKHOLE:

中文名“黑洞”，使用BLACKHOLE數據庫引擎的數據表不存儲任何數據，只根據數據庫操作過程記錄二進制日志。它的主要作用是作為MySQL主從復制的中繼器，并且可以在其上面添加業務過濾機制。

3. MyISAM:

MyISAM數據庫引擎是MySQL數據庫默認的數據庫引擎。MyISAM使用一種表格鎖定的機制，來優化多個并發的讀寫操作（實際上就是使用的一種避免數據臟讀的機制）。

但是這種機制對存儲空間的使用有一定的浪費。MyISAM還有一些有用的擴展，例如用來修復數據庫文件的MYISAMCHK工具和用來恢復浪費空間的MYISAMPACK工具。本文所介紹的MySQL數據庫相關技術將不涉及到這種數據庫引擎。

4. InnoDB:

InnoDB數據庫引擎是在各種版本的MySQL數據庫中使用最廣泛的一種數據庫引擎，本文后續的介紹中如果沒有特別說明都默認是在說InnoDB數據庫引擎。InnoDB數據庫引擎使用日志機制提供事務的支持

要了解MySQL數據庫中的性能問題，就首先要搞清楚在客戶端向MySQL數據庫提交一個事務操作時后者到底做了些什么事情，以及主要是怎么做的。本節所描述的工作過程主要圍繞InnoDB數據庫引擎進行：

上圖中筆者只畫出了InnoDB數據庫引擎在insert/update一個事務的過程中所涉及的重要工作區域，InnoDB的實際工作細節要比上圖所示的步驟復雜得多。

上文已經說到InnoDB數據庫引擎是一個支持事務的數據庫引擎，那么如何解決異常崩潰情況下的數據一致性問題就是它的設計中最重要的任務之一。

InnoDB數據庫引擎采用日志來解決這個問題，請注意這里說的InnoDB數據庫引擎日志，并不是MySQL數據庫全局的二進制日志。InnoDB數據庫引擎日志還有另外一個名字：重做日志（redo log），這是因為這部分日志主要的作用就是在數據庫異常崩潰并重啟后進行InnoDB引擎中數據的恢復。

為了提高MySQL數據庫的性能，InnoDB數據庫引擎的數據操作過程基本上都在內存中完成，然后通過一定的策略（后文會詳細介紹）將InnoDB Log Buffer內存區域中的日志數據同步到磁盤上的InnoDB File Log Group區域。

InnoDB File Log Group區域主要用于存儲InnoDB數據庫引擎的日志文件，它由多個大小相同的日志文件構成并且這些文件都采用順序讀寫。innodb_log_file_size參數將決定每個文件的大小，而innodb_log_files_in_group參數將決定整個日志組中有多少個日志文件。

當MySQL數據庫完成初始化過程后這些日志文件將會按照參數的設置值，在磁盤上預占一個連續的磁盤空間。這樣做的現象就是雖然數據庫中還沒有任何數據，但是日志文件的總大小就已經是 innodb_log_file_size * innodb_log_files_in_group所得到的數值了：

InnoDB數據庫引擎 日志文件示例....total 1.0G-rw-rw---- 1 mysql mysql 500M May 4 06:09 ib_logfile0-rw-rw---- 1 mysql mysql 500M May 4 06:09 ib_logfile1....這樣做的目的是保證了后續同步日志數據的操作都是順序寫，而不是隨機寫。當日志數據寫到最后一個文件的末尾時，下一條日志數據又會重新從第一個日志文件的開始位置進行寫入。

InnoDB Log Buffer內存空間中的四個標識指針是InnoDB數據庫引擎日志處理部分最重要元素，它們分別是：Log sequence、Log flushed、Pages flushed和Last checkpoint，這四個標識涉及到InnoDB在崩潰重啟時不同的數據恢復策略，以及I/O性能優化中的關鍵原理。

這四個標識實際上是四個數值它們共享一個數值池（名叫LSN，日志序列號，其總長度是64位無符號整數），代表當前InnoDB對事務操作的處理狀態。并且它們數值有以下特點：Log sequence >= Log flushed >= Pages flushed >= Last checkpoint

1. I/O性能問題（1）

2. I/O性能問題（2）

1 innodb_flush_log_at_trx_commit = 0時，InnoDB將按照1秒鐘為單位向磁盤寫入這個階段所有已完成的事務日志信息。這里的寫入成功并不是說寫入到Linux操作系統的Page Cache中就算成功，而是需要等待操作系統真正寫到了物理磁盤上的通知（具體請參見之前講解文件系統的文章）。

這意味 著即使InnoDB Buffer Pool中的數據操作是成功的，但是一旦數據庫系統異常崩潰，那么業務系統將會丟失前1秒內寫入的數據：因為沒有磁盤介質上的日志就無法在異常重啟后恢復數據信息。

2 innodb_flush_log_at_trx_commit = 1時，InnoDB按照完成一個日志操作就向磁盤寫入事務日志信息的方式來工作（執行一個事務就寫入一個事務日志）。同樣，這里的寫入成功同樣是要等待操作系統返回真正寫入了物理磁盤的通知。

3 innodb_flush_log_at_trx_commit = 2時，InnoDB按照完成一個日志操作就向磁盤寫入日志信息的方式來工作。但是，這種工作模式下InnoDB不會等待操作系統返回物理磁盤上寫入成功的通知，就會繼續工作。

實際上這個時候，數據一般還存在于Linux操作系統的cache memory區塊中，所以這種模式下最好使用帶有日志功能的文件系統，并且確認開啟了文件系統的日志功能。

InnoDB數據庫引擎在這一步驟的最后一個動作是更改Log flushed標識指針值為當前最后完成刷新動作的事務日志LSN值。實際上執行完這個步驟，一個事務處理操作才算真正成功。

3. I/O性能問題（3）

4. I/O性能問題（4）

1 當代表事務的LSN數值在Log sequence——Log flushed范圍內時（不包括Log flushed），說明在數據庫崩潰時內存中的事務并沒有處理完，這部分事務操作將在恢復時被丟棄。

2 當代表事務的LSN數值在Log flushed——Pages flushed范圍內時（不包括Pages flushed），說明數據庫崩潰時磁盤上已經擁有這些事務完整的日志記錄。

InnoDB數據庫引擎將讀取這些日志數據，并繼續執行下去，直到代表這些事務的LSN值被標記為Checkpoint（或者小于Checkpoint標識的LSN值）。這里要注意，在數據庫崩潰時處于這個范圍內的某些事務可能已經完成了一部分的數據同步動作，但是肯定是不完整的。所以即使是這樣的事務也要重新進行磁盤同步，才能保證數據的一致性。

3 實際上在MySQL version 5.5的早期版本，InnoDB數據庫引擎中只有三個標識：Log sequence、Log flushed和Checkpoint。也就是說當臟頁成功同步到磁盤后，就會直接更新Checkpoint標識的LSN值。

后續版本的MySQL數據庫增加了Pages flushed標識點，這樣做的目的是保證Checkpoint和Pages flush的更新可以擁有獨立的周期，從而降低其帶來的性能消耗

1. Log flush和Pages flush

1 例如在讀取Page時，采用“預讀”思路將目標Page所臨近的Page一起讀取出來；在寫入Page時將目標Page所臨近的Page一起寫入（“臨近寫”）。“預讀”策略可以通過innodb_read_ahead_threshold參數進行設置，并通過read thread完成，另外 innodb_flush_neighors參數可以控制是否開啟“臨近寫”策略。

總的來說“預讀”/“臨近寫”在默認情況下都是開啟的，但“預讀”/“臨近寫”思路本身就需要一定的準確性，低命中率的“預讀”反而會降低InnoDB的I/O性能。

還有一種“隨機預讀”，它在MySQL version 5.6版本中默認就是關閉的，并且在隨后的版本中將會慢慢廢除，所以這里就不再介紹了。

2 例如將向磁盤提交Page的動作設計為周期性且批量進行，并且始終保持InnoDB Buffer Pool內存區域的臟頁（Dirty Page）在一定的比例，這些策略主要由innodb_io_capacity、innodb_max_dirty_pages_pct、innodb_io_capacity_max等參數控制。

3 例如通過調整Innodb_Buffer_Pool_size參數獲得更大的InnoDB Buffer Pool內存區域，存儲更多的Page。實際上Innodb Buffer Pool區域不僅包括我們已經介紹的Page Cache數據部分，還包括其它的數據區塊。

例如為了快速定位B+樹索引的Hash Index結構。調整Innodb_Buffer_Pool_size參數將會使這些數據區域都享受到內存容量帶來的優勢——至少不會頻繁地發生內容空間的強制清理。

2. 基礎硬件條件

為了解決上一節中提到的I/O性能問題，本文這里基于之前介紹的塊存儲方案的知識，列出這個問題的幾種解決方案。

除了根據I/O吞吐量要求對MySQL數據庫特別是InnoDB引擎的配置參數進行更改以外，本文提到的硬件層解決方法所需要花費的資金和能夠得到的I/O性能和擴展能力基本上成正比。

上一節我們已經對InnoDB數據庫引擎（以下簡稱InnoDB引擎）進行事務操作時的I/O過程進行了簡單說明，主要介紹了Log flush和Pages flush兩個過程。

如果我們需要挖掘正式生產環境上MySQL數據庫服務的性能潛力，那么對MySQL數據庫服務中的默認參數進行更改就是必須要做的事情。在進行配置修改之前我們先來看看如何查看當前MySQL數據庫特別是InnoDB引擎的工作狀態：

通過執行以下命令，我們可以查看當前InnoDB引擎的工作狀態show engine innodb status;

執行后可以得到類似如下的執行結果（已省去一部分與本文沒有涉及到的知識點所相關的狀態描述信息）：

參數設置規則介紹：

1. innodb_log_file_size

單個日志文件的大小不宜過小，例如設置為500MB。由于InnoBD引擎對日志文件采用順序寫的操作方式，所以不必擔心日志文件的操作消耗比數據文件操作更多的性能。

2. innodb_log_files_in_group

該參數控制了文件組中日志文件的總數。設置為2-5的范圍都不會有太大影響。更重要的是讀者應該清楚innodb_log_file_size * innodb_log_files_in_group就是InnoDB引擎在磁盤上可用日志空間的總大小

3. innodb_log_buffer_size

這個參數決定了InnoDB引擎可使用的日志內存空間。只要沒有類似插入blob類型數據的操作（也不建議有這樣的操作），這個內存空間都不需要設置得太大。5MB-10MB是一個推薦的設置值，不過這個參數還是要和innodb_flush_log_at_trx_commit參數配合使用。

4. innodb_flush_log_at_trx_commit

該參數可以說是InnoDB引擎日志操作策略部分最重要的設置參數之一。如果您將innodb_flush_log_at_trx_commit設置為0，代表著InnoDB引擎將會按照1秒鐘的周期進行日志從內存到磁盤的同步。

這時innodb_log_buffer_size的值就不能過小，因為在一個同步周期內如果待刷新的日志超過了innodb_log_buffer_size設置的大小，InnoDB就會強制執行同步操作。

如果您的Linux操作系統使用的是帶有日志功能的文件系統并且日志功能是開啟的，那么還是建議將該參數設置為2。

5. innodb_buffer_pool_size

這個參數調整分配給InnoDB引擎使用的可用數據內存區域的大小。實際上這個數據區域不止包括了本文中一直強調的Page Cache區域，它還有很多數據區域。

例如InnoDB中用來進行查詢排序的Sort Buffer區域。建議的設置大小是MySQL數據庫服務所在物理服務器上總內存的60%——80%（文件系統的Cache Memory/Buffer Memory等其它程序還要使用）。

8GB的物理服務器可設置6GB的InnoDB Buffer Pool可用內存區域。注意，當MySQL數據庫啟動時并不是立刻就會占據所有數據區域。

6. innodb_buffer_pool_instances

本小節和本文中多處位置都提及到innodb_buffer_pool_size參數以及它的含義。這個參數值在生產環境下一般設置得都比較大（例如4GB、8GB、12GB、24GB等等）。

但是由于臟數據刷盤的周期性，在I/O性能強勁的物理機器上可能就會存在I/O間歇性低谷。為了將I/O操作一直保持在一定的工作效能上，也為了發揮CPU的計算性能，InnoDB引擎允許將innodb_buffer_pool劃分為多個獨立的運行實例，當InnoDB需要讀取新的Page時，它們會按照一定的算法被分配到某個獨立運行的buffer pool instance中。

這些buffer pool instance有各自獨立的LRU算法隊列、獨立計算臟頁比例，并且獨立進行臟頁刷新。innodb_buffer_pool_instances參數在具有較高I/O性能并且具有較大innodb_buffer_pool_size設定值的物理設備上能夠對I/O性能產生非常明顯的影響。

如果您采用的是固態磁盤或者磁盤陣列作為MySQL服務器的硬件層存儲介質，那么建議1-2GB的innodb_buffer_pool就分配一個獨立的運行實例（這樣算下來12GB的buffer pool可以設置6-12個運行實例，注意進行生產環境下的實測調整哦-）。

但如果您只是使用的機械磁盤又或者innodb_buffer_pool_size的值并不大，那么將innodb_buffer_pool_instances參數設置為1就可以了。

7. innodb_io_capacity

該參數控制著InnoDB Buffer Pool數據內存區域進行磁盤同步時每次可以同步的臟頁數量。在磁盤I/O性能不足時，如果innodb_io_capacity參數值過大就會造成I/O阻塞，并且造成InnoDB引擎性能較大的降低。但如果您使用的是固態硬盤或者RAID磁盤陣列,就可以將innodb_io_capacity參數默認的200設置大一些，例如設置成500——800）。

8. innodb_adaptive_flushing

該參數一定要打開，保證臟頁的同步周期由InnoDB引擎根據實時I/O性能情況自行控制同步頻率（實際上只有兩種頻率：1秒或者10秒）。

9. innodb_max_dirty_pages_pct

該參數默認為75，一般情況下無需更改。另外innodb_io_capacity_max參數表示當臟頁數量在InnoDB Buffer Pool內存中的比例超過了innodb_max_dirty_pages_pct參數設置的上限后，就按照innodb_io_capacity_max設置的臟頁數量強制進行臟頁的刷新（建議采用默認值即可）。

但是設想一下這個問題：什么情況下最可能使臟頁在內存中的占比超過上限呢？當然是InnoDB引擎的事務不斷快速執行，并且I/O性能又不足以快速完成同步。這時InnoDB引擎將停止事務的執行，并且進行強制刷新。所以，當問題真正發生時innodb_io_capacity_max參數設置得再大也不可能解決I/O擁堵的問題，反而可能使問題更嚴重。

10. innodb_page_size（截止其他參數）

該參數決定了InnoDB引擎中每一頁的大小。每一個page包含多條row數據，更大的page size意味著內存中存儲的每頁信息有更多的數據條數。由于文件系統和底層硬件設置的結構，所以該值都為4KB的整數倍（默認值為16KB，可選值為4KB、8KB、16KB）。

注意如果您需要更改這個參數值，那么就必須在MySQL數據庫初始化啟動時，就加入到my.cnf配置文件中。否則一旦創建了用戶數據表，再對這個參數進行修改，MySQL數據庫就會報錯。

11. innodb_read_io_threads

該參數設置InnoDB數據庫中的負責從磁盤上讀取數據的線程數量，另外這些線程還負責在預讀選項開啟時承擔起預讀的工作任務。innodb_read_io_threads的建議值為CPU的內核數量.

12. innodb_write_io_threads

該參數設置InnoDB數據庫中負責將臟頁同步到磁盤上的線程數量。innodb_write_io_threads的建議值為CPU的內核數量。

13. innodb_read_ahead_threshold

該參數表示InnoDB引擎中的順序預讀閥值。在buffer pool中的page也有一個組織結構：64個page組成一個extent結構。當InnoDB發現在一個extent結構中已經連續讀取N個page，那么InnoDB會接著將另外64 - N個后續的page讀入到buffer pool中。

順序預讀在“連續讀”性能較高的硬件設備上，對性能的影響非常小。所以如果讀者使用了I/O性能比較強勁的固態磁盤環境或者磁盤陣列環境，則建議直接關閉該功能（設置為0即可）。

14. innodb_random_read_ahead

該參數表示是否開啟隨機預讀，默認是關閉的。

15. innodb_flush_neighbors

既然InnoDB引擎提供Page的預讀功能，當然就提供預寫功能。該參數表示當Buffer Pool中的臟頁被同步到磁盤時，是否一起刷新和這個臟頁臨近的頁信息。

該參數在I/O性能比較強勁的固態磁盤環境或者磁盤陣列環境下，對性能提升并不明顯。所以建議在這樣的情況下直接關閉這個功能（設置為0即可）。

16. sort_buffer_size

后文介紹數據庫查詢優化時會討論到這個參數。該參數對數據庫引擎的查詢性能，特別是有對結果進行排序要求的查詢性能影響非常大。

17. join_buffer_size

后文介紹數據庫查詢優化時會討論到這個參數。該參數對數據庫引擎的查詢性能，特別是有各種join連接要求的查詢性能影響非常大。

18. binlog_cache_size

在MySQL數據庫中處理InnoDB層存在“重做日志”以外，在數據庫管理層還有一個獨立工作的二進制日志模塊。這個日志模塊的工作方式和“重做日志”的工作方式相似，它們采用的辦法都是在內存中進行日志數據變更，然后按照一定的策略周期性/直接同步到磁盤上，binlog_cache_size參數設置的就是可供二進制日志在內存中進行暫存的空間大小。

需要注意的是：binlog_cache_size和innodb_buffer_pool_size不同的是，前者的大小以MySQL數據庫的客戶端連接為單位。也就是說MySQL數據庫會為兩個獨立的數據庫客戶端連接分別分配獨立運行的binlog cache空間。

正式環境的數據庫中為每一個數據庫連接設置的binlog cache空間不需要太大，當然這還要考慮實際的客戶端請求頻度和數據類型，還要考慮下面將介紹的sync_binlog參數設定。該參數建議的幾個設置值為：32768（32KB為默認值，沒有特別的要求可以保留該設置）、65536（64KB）、131072（128KB）、262144（KB）、524288（512KB）、1048576（1MB）以內。

19. sync_binlog

在MySQL數據庫中除了InnoDB的“重做日志”需要同步以外，二進制日志也需要進行同步。這個參數是指MySQL數據庫在內存中進行X次二進制日志操作后，就將內存中的二進制日志同步到磁盤中

1. 概述如下：

以下是一組可以在配置有固態硬盤和磁盤陣列的正式MySQL數據庫環境下使用的配置項參考，主要是為讀者總結InnoDB引擎中和I/O性能相關的重要參數（只和I/O性能有關，因為后續的文章中還會介紹其他參數）。

讀者在進行參數配置是還是需要按照自己團隊的生產環境情況，對配置項進行調整（這些參數信息都在MySQL數據庫的my.cnf主配置文件中進行設置）：

2. 配置完成后可以通過以下命令查看當前MySQL數據庫和InnoDB引擎中相關的配置參數（為節約篇幅，已省去一部分查詢結果）：

三、提供更優的硬件方案

這是最基本的硬件層改造方式，目前大多數廠商提供的PC Server基本上都集成了RAID控制器。所以這樣做一般不需要額外增加購買硬件設備的費用。

在MySQL官網上并沒有明確推薦使用哪一種磁盤陣列模式，但是從搭建磁盤陣列支持MySQL的實際引用情況來看，更多是使用RAID 10陣列模式（另外磁盤陣列的整體性能和陣列控制芯片有很大關系）。

RAID 10陣列模式可以在提升了整個系統I/O性能的基礎上兼顧了存儲的安全性。為了使用RAID 10磁盤陣列模式，讀者至少需要為準備4塊磁盤。其中2/4的磁盤容量用來存儲數據冗余，另外2/4的磁盤容量用來分散存儲數據。對于RAID 10磁盤陣列模式的詳細工作方式介紹，讀者可以參看另一篇文章（《架構設計：系統存儲（2）——塊存儲方案（2）》）

以上解決方案中，每一個機械磁盤的I/O性能將會成為整個RAID 10磁盤陣列的性能瓶頸（不考慮陣列控制芯片的處理性能）。所以如果技術團隊還有多余的資金支持那么下一步要做的就是將構成RAID 10磁盤陣列多個機械磁盤全部替換成固態磁盤。如下圖所示：

USB3.0接口的理論帶寬只有600MB/S，而且PC Server內置的磁盤陣列控制器由于服務器內部空間的限制，也存在磁盤數量擴展困難的問題。如果讀者確認生產環境的某個物理服務器將以I/O讀寫操作為主，且I/O性能將成為其上工作的應用軟件的瓶頸。

那么這時最好的硬件方案就是直接采用外置企業級磁盤陣列柜 + 光纖接口的方式搭建硬件層支持。

目前主流的光纖線路帶寬為16Gb/s，這遠遠高于USB3.0 6Gb/s的理論帶寬、高于SAS 12Gb/s的理論帶寬。另外單個企業級磁盤陣列柜可容納的磁盤數量就已經很高了（例如IBM Storwize V5000 單柜提供24個磁盤位，單柜支持最大72TB存儲容量），并且這些企業級盤柜一般支持擴展成多柜。

這兩種特性有效解決了硬件層面磁盤I/O速度和容量的問題，但代價就是這些IT基礎折本的價格一般比較昂貴，技術團隊所在企業需要有比較寬裕的項目/產品建設預算。

Bye~

轉載自：http://6tt.co/nKdc

-- RECOMMEND --01｜測開之Java全棧自動化

02｜接口測試——官方requsets庫

03｜接口測試數據庫斷言的實現與設計

南京兆柏數據恢復中心