1、MySQL概述

從本文開始我們將討論建立在塊存儲方案之上的關系型數據庫的性能優化方案和集群方案。關系型數據庫的選型將以創業公司、互聯網行業使用最廣泛的MySQL數據為目標，但是MySQL的安裝過程和基本使用方法等知識并不在我們討論的范圍內。后續幾篇文章我們首先討論影響單個MySQL節點性能的主要因素，然后介紹MySQL讀寫分離、數據表橫縱拆分的原理和技術方案。

MySQL數據庫目前已被Oracle收購，并發展處多個版本。目前使用最廣泛且免費的MySQL版本是MySQL Community(社區版)，另外還有三個付費的MySQL版本MySQL Standard(MySQL標準版)、MySQL Enterprise(MySQL企業版)、MySQL Cluster(MySQL集群版)，這三個版本是按照CPU內核進行費用計算，并且價格由低到高。最后Oracle還提供了兩個微型的MySQL版本：MySQL Classic(經典版)，這個版本的MySQL只提供了MyISAM存儲引擎但是安裝快速，占用空間較少；MySQL Embedded(嵌入式版本)，這個版本的競爭軟件是SQLite。雖然社區版本是免費的并且這個版本提供的功能也沒有企業級版本豐富，同樣的硬件條件下單節點性能也沒有企業基本版優秀。但是我們可以借助社區版本自身提供的功能和一些第三方軟件配合使用，搭建起相對廉價且性能不俗的MySQL數據庫集群。

2、數據庫引擎的選擇

MySQL數據庫中最重要的一個概念就是數據庫引擎，不同的數據庫引擎的工作原理存在很大差異最終造成MySQL數據庫服務的性能差異。例如如果數據庫引擎需要支持事務，就必須滿足事務的基本特性——AICD特性(AICD：原子性、隔離性、一致性和永久性。屬于基礎知識所以不在這里贅述)，那么自然就需要一定處理機制來實現這些特性。這樣做的現實效果就是導致寫入同樣數據量的情況下，支持事務的數據庫引擎比不支持事務的數據庫引擎耗費更多的時間。這里我們首先為讀者列舉MySQL數據庫社區版中支持的數據庫引擎(部分)：

MEMORY：MEMORY存儲引擎將表的數據完全存放在內存中。在MySQL數據庫的歷史版本中和該數據庫引擎類似的其它引擎是HEAP，后者曾是MySQL數據庫中訪問速度最快的數據庫引擎。但由于這兩種數據庫引擎完全工作在內存中，所以如果MySQL或者服務器重新啟動，數據庫引擎中保存的數據將會丟失。
BLACKHOLE：中文名“黑洞”，使用BLACKHOLE數據庫引擎的數據表不存儲任何數據，只根據數據庫操作過程記錄二進制日志。它的主要作用是作為MySQL主從復制的中繼器，并且可以在其上面添加業務過濾機制。
MyISAM：MyISAM數據庫引擎是MySQL數據庫默認的數據庫引擎。MyISAM使用一種表格鎖定的機制，來優化多個并發的讀寫操作(實際上就是使用的一種避免數據臟讀的機制)。但是這種機制對存儲空間的使用有一定的浪費。MyISAM還有一些有用的擴展，例如用來修復數據庫文件的MYISAMCHK工具和用來恢復浪費空間的MYISAMPACK工具。本文所介紹的MySQL數據庫相關技術將不涉及到這種數據庫引擎。
InnoDB：InnoDB數據庫引擎是在各種版本的MySQL數據庫中使用最廣泛的一種數據庫引擎，本文后續的介紹中如果沒有特別說明都默認是在說InnoDB數據庫引擎。InnoDB數據庫引擎使用日志機制提供事務的支持。

3、基本I/O性能

要了解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
....

這樣做的目的是保證了后續同步日志數據的操作都是順序寫，而不是隨機寫。當日志數據寫到最后一個文件的末尾時，下一條日志數據又會重新從第一個日志文件的開始位置進行寫入。

3-1、I/O 性能問題的產生

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【數據恢復檢查點】

每當InnoDB接收到一個完整數據庫insert/update請求事務后，就會創建一個新的LSN。新的LSN = 舊的LSN + 本次寫入的日志大小。這條最新的日志將會使用Log sequence進行標記，并且如果出現接收到多個事務請求的情況下，InnoDB也會按照一個既定的順序對這些日志進行排序，然后依次生成新的LSN。這一步驟是完全在內存中進行的，所以不存在I/O性能問題。
接下來Mysql就會開始執行這個事務中的各種細節操作。InnoDB數據庫引擎專門有一個InnoDB Buffer Pool內存空間用來進行數據更改或數據新增。其大小由innodb_buffer_pool_size參數控制，其數據來源于innoDB data file并且以Page的形式存在于InnoDB Buffer Pool中。當日志中有insert操作時則生成新的Page；當日志中有update操作時，InnoDB會檢查該數據是否已經存在于Page Cache中，如果存在(命中)就直接更新這個Page Cache中的內容，如果不存在(未命中)就會繼續從InnoDB data file中讀取原始數據到InnoDB Buffer Pool中然后再更新。這里要注意幾個問題：

還記得我們在討論磁盤設備時提到的“預讀”技術嗎？這個技術的思路是，如果某個區域的數據被讀取和使用那么在不久的將來與其相鄰的區域也將會被讀取和使用。所以為了提高讀取效率，磁盤控制芯片會將磁盤上目標塊和其相鄰的若干塊一起讀取出來。InnoDB數據庫引擎同樣使用了這個思路，即讀取某個Page時將會同時讀取臨近的Page，但是是否能起到提到I/O性能的目的還是要分不同的運行環境(后文進行說明)。

當InnoDB完成InnoDB Buffer Pool中的數據操作后，更改后數據所涉及到的Page將和此時存儲在磁盤上的數據不一樣，這樣的Page稱為臟頁。如何控制臟頁將是保持數據一致性的關鍵，InnoDB數據庫引擎的做法是首先向InnoDB File Log Group日志文件中寫入這個事務的日志信息。這里的寫入策略由三種，通過innodb_flush_log_at_trx_commit參數可以進行控制：

innodb_flush_log_at_trx_commit = 0時，InnoDB將按照1秒鐘為單位向磁盤寫入這個階段所有已完成的事務日志信息。這里的寫入成功并不是說寫入到Linux操作系統的Page Cache中就算成功，而是需要等待操作系統真正寫到了物理磁盤上的通知(具體請參見之前講解文件系統的文章)。這意味著即使InnoDB Buffer Pool中的數據操作是成功的，但是一旦數據庫系統異常崩潰，那么業務系統將會丟失前1秒內寫入的數據：因為沒有磁盤介質上的日志就無法在異常重啟后恢復數據信息。【定時持久化日志】
innodb_flush_log_at_trx_commit = 1時，InnoDB按照完成一個日志操作就向磁盤寫入事務日志信息的方式來工作(執行一個事務就寫入一個事務日志)。同樣，這里的寫入成功同樣是要等待操作系統返回真正寫入了物理磁盤的通知。【1事務1日志，等操作系統寫】
innodb_flush_log_at_trx_commit = 2時，InnoDB按照完成一個日志操作就向磁盤寫入日志信息的方式來工作。但是，這種工作模式下InnoDB不會等待操作系統返回物理磁盤上寫入成功的通知，就會繼續工作。實際上這個時候，數據一般還存在于Linux操作系統的cache memory區塊中，所以這種模式下最好使用帶有日志功能的文件系統，并且確認開啟了文件系統的日志功能。【1事務1日志，不等操作系統寫】

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

但是涉及數據變動的臟頁還沒有更新到磁盤上，為什么事物的處理就可以算作成功了呢？這是因為即使這個時候數據庫異常崩潰了，就憑存儲在磁盤上的完整日志我們也可以重做數據。好吧，最好還是要同步臟頁是吧。在第三個步驟InnoDB數據庫引擎將會把最近Log flush時所涉及到的臟頁(最舊臟頁)更新到磁盤上。當完成臟頁向磁盤的同步操作后，InnoDB數據庫引擎將會更新Pages flushed標識點的LSN值，表示這個LSN值所代表的事務(以及之前的事務)都已經完成了內存和磁盤上的數據同步動作。當InnoDB數據庫引擎進行臟頁更新時，將會按照一定的周期策略批量提交臟頁到Linux操作系統的cache memory區塊中。每一次批量提交的臟頁數量由innodb_io_capacity參數決定。

不同版本InnoDB數據庫引擎支持的pages flush策略是不一樣的，但最基本的規則沒有變化，就是周期性刷新。從Mysql version 5.6開始InnoDB數據庫引擎向管理者提供了一個innodb_adaptive_flushing參數，當這個參數設置為“no”時InnoDB數據庫引擎將檢測臟頁在InnoDB Buffer Pool中的比例，以及即時I/O狀態等情況來決定pages flush的周期。如果臟頁在InnoDB Buffer Pool中的比例達到了由innodb_max_dirty_pages_pct(默認為75)參數設置的百分比閥值，這時InnoDB數據庫引擎將按照innodb_io_capacity_max(默認值2000)參數設置的數量將這寫臟頁一起同步到磁盤。

當磁盤I/O性能不足且innodb_io_capacity設置過大時，會導致產生較長的I/O隊列造成I/O請求阻塞，一旦累積到innodb_max_dirty_pages_pct閥值，又會產生更長的I/O阻塞隊列；反之則會造成物理服務器的I/O性能沒有被去完全使用。所以innodb_io_capacity的設置非常重要，特別是當讀者在硬件層采用SSD固態硬盤和高速磁盤陣列時。

Checkpoint是InnoDB數據庫引擎中最后一個標識點。這個標識點代表著當數據庫異常崩潰重啟后，小于或者等于這個標識點LSN值的所有日志信息、數據信息都無需進行重做檢查。而LSN值大于Checkpoint的所有事務都需要重做，只是重做策略將視LSN值所在標識區域的不同而不同：

當代表事務的LSN數值在Log sequence——Log flushed范圍內時(不包括Log flushed)，說明在數據庫崩潰時內存中的事務并沒有處理完，這部分事務操作將在恢復時被丟棄。
當代表事務的LSN數值在Log flushed——Pages flushed范圍內時(不包括Pages flushed)，說明數據庫崩潰時磁盤上已經擁有這些事務完整的日志記錄。InnoDB數據庫引擎將讀取這些日志數據，并繼續執行下去，直到代表這些事務的LSN值被標記為Checkpoint(或者小于Checkpoint標識的LSN值)。這里要注意，在數據庫崩潰時處于這個范圍內的某些事務可能已經完成了一部分的數據同步動作，但是肯定是不完整的。所以即使是這樣的事務也要重新進行磁盤同步，才能保證數據的一致性。
實際上在MySQL version 5.5的早期版本，InnoDB數據庫引擎中只有三個標識：Log sequence、Log flushed和Checkpoint。也就是說當臟頁成功同步到磁盤后，就會直接更新Checkpoint標識的LSN值。后續版本的MySQL數據庫增加了Pages flushed標識點，這樣做的目的是保證Checkpoint和Pages flush的更新可以擁有獨立的周期，從而降低其帶來的性能消耗。

3-2、I/O 性能問題要點

Log flush和Pages flush

從上一小節的描述中，我們大致知道了在InnoDB數據庫引擎中一個事務的處理過程中有兩個步驟存在I/O操作：Log flush和Pages flush。

Log flush的過程是將完成的事務日志寫入到日志文件中，由于InnoDB數據庫引擎中日志文件的組織方式，所以Log flush中對磁盤的操作是順序寫。并且技術團隊還可以通過innodb_flush_log_at_trx_commit參數來調整InnoDB Log Buffer到InnoDB File Log Group的同步策略，這有助于進一步提高Log flush性能。這也就是為什么實際環境中往往將MySQL數據庫(大部分關系型數據庫都適用)直接建立在塊存儲方案上，而不是建立在文件存儲方案或者對象存儲方案上的原因。

Pages flush的過程就沒有那么幸運了，InnoDB數據庫引擎不可能事先知道數據庫會存放哪些數據，也不可能知道下次的update操作和select操作的目標數據存放在哪個區域。所以InnoDB數據庫引擎針對Page的讀取和更新都只能基于隨機讀寫。那么Pages flush過程就需要在如何保持I/O性能這問題上想更多的解決辦法。

例如在讀取Page時，采用“預讀”思路將目標Page所臨近的Page一起讀取出來；在寫入Page時將目標Page所臨近的Page一起寫入(“臨近寫”)。“預讀”策略可以通過innodb_read_ahead_threshold參數進行設置，并通過read thread完成，另外 innodb_flush_neighors參數可以控制是否開啟“臨近寫”策略。總的來說“預讀”/“臨近寫”在默認情況下都是開啟的，但“預讀”/“臨近寫”思路本身就需要一定的準確性，低命中率的“預讀”反而會降低InnoDB的I/O性能。還有一種“隨機預讀”，它在MySQL version 5.6版本中默認就是關閉的，并且在隨后的版本中將會慢慢廢除，所以這里就不再介紹了。
例如將向磁盤提交Page的動作設計為周期性且批量進行，并且始終保持InnoDB Buffer Pool內存區域的臟頁(Dirty Page)在一定的比例，這些策略主要由innodb_io_capacity、innodb_max_dirty_pages_pct、innodb_io_capacity_max等參數控制。
例如通過調整Innodb_Buffer_Pool_size參數獲得更大的InnoDB Buffer Pool內存區域，存儲更多的Page。實際上Innodb Buffer Pool區域不僅包括我們已經介紹的Page Cache數據部分，還包括其它的數據區塊。例如為了快速定位B+樹索引的Hash Index結構。調整Innodb_Buffer_Pool_size參數將會使這些數據區域都享受到內存容量帶來的優勢——至少不會頻繁地發生內容空間的強制清理。

基礎硬件條件

按照本專題之前文章介紹的塊存儲方案來看(《架構設計(1)——塊存儲方案(1)》、《架構設計(2)——塊存儲方案(2)》)，如果存儲MySQL數據的底層硬件介質就只是一塊機械磁盤，那么無論怎樣優化MySQL的其它各參數，MySQL實際對磁盤的順序I/O速度理論上也只有100MB/S左右。這還是不計算硬件層校驗、不計算不同文件系統處理耗時等等時間，所以實際I/O速度只會更慢。另外如果采用單塊機械磁盤存儲MySQL的數據，那么磁盤空間的擴容也是一個問題。目前市場上能買到的容量最大的單塊機械磁盤，它的存儲空間也只有10TB。當這部分容量使用完后想要進行擴容就基本上是就一個不可能完成的任務了。最后這種存儲方式還有安全性的問題，單塊機械磁盤在持續的高I/O環境下是很容易損壞的，只要是有一定資金支持的公司，機械磁盤本身就看做耗材。

所以即使是初創型公司的線上生產環境，本文也不推薦使用單塊機械磁盤存儲任何需要持久保存的業務數據。如果是因為資金問題，則推薦使用一些云服務商提供的現成PaaS環境，原因是這些PaaS環境本身就支持數據恢復功能，且利用云服務商已經建設好的價格不菲的硬件/軟件環境，MySQL數據庫的I/O性能和計算性能暫時還不會成為業務系統的瓶頸。