數據庫服務器硬件優化

我們來看看對數據庫所在的服務器是如何進行優化的，服務器是數據庫的宿主，其性能直接影響了數據庫的性能，所以服務器的優化也是數據庫優化的第一步。

數據庫服務器通常是從 CPU、內存、磁盤三個角度進行硬件優化的，網絡方面取決于實際負載，建議使用萬兆網絡。

CPU

首先講下從 CPU 角度進行優化。

MySQL 對于 CPU 的運行需求各個版本均有不同，在 MySQL 5.1 版本以前對多核支持弱，近乎單核。MySQL 5.1 版本可以利用 4 核，MySQL 5.5 版本可以利用到 24 核，MySQL 5.6 版本可利用到 64 核，MySQL 5.6 版本后多核支持加強，可以支持到 128 核甚至更多。

對于 MySQL 而言，由于每個連接對應一個線程，每個并發 Query 只能使用一個核，即 MySQL 在執行單個 SQL 語句的時候底層只能用到一個 CPU 核，所以要想提升 SQL 執行效率，單個 SQL 執行盡可能快。在考慮 CPU 的時候，我們應該優先選擇高主頻 CPU 來加速單條 SQL 語句的執行效率。

由于 MySQL 每個并發 Query 只能使用一個核，MySQL 引入了 Thread Pool 功能來提升性能。Thread Pool 最開始是由 MariaDB 引入，Percona 在其基礎上引入了優先級隊列，官方后續引進。

通過參數 thread_handling 可以查看線程池模型，主要有 one-connection-per-thread 和 one-thread-per-connection 兩類，建議使用 one-thread-per-connection。

在 5.6 版本之前 MySQL 連接處理的方式是one-connection-per-thread，對于每一個數據庫連接 MySQL Server 都會創建一個獨立的線程服務（建立連接：CPU 劃分一定的 thread stack 后進行用戶身份認證，建立上下文信息，請求處理后關閉連接，釋放資源），處理請求后銷毀線程。

初期 Thread Pool 雖然可以事先創建一批線程并復用，但是無法解決高連接數的問題。one-connection-per-thread 方式隨著連接數暴增同樣會退化為單線程并創建更多的服務線程，高并發線程和高連接數意味著內存消耗加劇，進而出現由于上下文切換、資源爭用、CPU 和內存負載高而導致服務出現抖動。

而采用 one-thread-per-connection 的方式是一個線程對應一個連接，在其 Thread Pool 的實現方式中，線程處理的最小單位是 statement（SQL 語句），一個線程可以處理多個連接的請求。這樣，在保證充分利用硬件資源情況下（合理設置線程池大小），可以避免瞬間連接數暴增導致的服務器抖動。

如下圖，可以看到，Percona Thread Pool（one-thread-per-connection）工作原理是這樣的。

創建多個 Group（thread_pool_size參數指定，一般設置為 CPU core 數量），每個 Group 可有多個 worker；

線程根據 connection id 被分配到 Group（生命周期內不變），worker 以 SQL 為單位進行處理，保證每個連接都能及時得到響應；

每個 Group 有兩個任務隊列，優先隊列：存放已開啟事務的 SQL，保證事務優先被處理完（盡早釋放鎖）；優先隊列為空時才處理普通隊列；這樣相比 MariaDB Thread Pool 的實現，優先隊列可避免調度上的死鎖（A 和 B 被分到不同的 Group 中，A 事務已經開啟，并且獲得了鎖，可能無法立即得到調度執行，B 事務依賴 A 事務釋放鎖資源，但是先于 A 得到調度）；

額外創建一個 Timer 線程，定期檢查 Groups，若發現 woker 異常（堵塞/超時/worker 線程數目不夠）則及時喚醒；

若 Group 任務隊列為空（客戶端連接卻不為空），為空閑連接設置一個超時期限，之后將連接的 socket fd 綁定到 Group 中的 epollfd，線程則調用 epoll_wait() 批量取任務。

MySQL 對于 CPU 的使用情況，在有些極端條件下，例如電子商務中的秒殺情形、關鍵節日集中慶祝的時刻及一些訪問密集型業務，并發執行的 SQL 的需求很大，要保證在短時間內有巨大的吞吐量，就需要選擇核心數多的 CPU。因此數據庫服務器優選主頻高、核數多的 CPU。

對于 MySQL 運行服務器 CPU 硬件優化總結如下：

系統配置選擇 Performance Per Watt Optimized（DAPC），發揮最大功耗性能，而不是節能模式（高運算節點禁用），節能模式在低高頻性能轉換時易出現Bug。

CPU優先選擇高主頻以提高運算能力；其次選擇核數多，可以多線程并發處理和多實例部署。

關閉 C1E（增強型空閑電源管理狀態轉換）和 C states，DB 服務器不需要節能和省電運行，默認是開啟狀態，DB 服務器建議關閉以提高 CPU 效率。

數據庫服務器選擇高主頻多核數 CPU 類型，同時開啟最大性能和關閉 CPU CIE 和 C States。 高頻加速 SQL 執行，多核解決并發。

內存

講完 CPU 再了解如何從內存方向進行優化。

MySQL 對內存的運行需求是足夠大的，單機多實例的情況內存建議按實際數據熱點數據量的 30% 進行規劃，單機單實例（即獨享實例）的情況建議是分配 50%～80%。

MySQL 雖然有 Query Cache 但是功能很雞肋，線上建議關閉 Query Cache，同時需要注意的是 MySQL 8.0 已移除 Query Cache。

MySQL 的內存分配原則取決于參數配置，詳情在接下來的 MySQL 參數優化中講解。

總結下 MySQL 對于內存的優化建議如下：

內存頻率選擇 Maximum Performance（最佳性能)。

大內存，renice mysql pid 避免 OOM 時 MySQL 被強殺。

內存設置菜單中，啟用 Node Interleaving 避免 NUMA 問題，同時建議修改系統配置關閉NUMA。

一句話描述，對于數據庫服務器而言優先選擇大內存，同時開啟最大性能并關閉NUMA。

磁盤

接下來，來了解磁盤優化。

對于磁盤的硬件需求，數據庫是 IO 型負載的軟件，優先使用 SSD、PCIe SSD，迫不得已使用高轉速機械硬盤，建議運行數據庫時放棄使用機械硬盤。

從 IO 類型而言，MySQL Undo Log 是順序寫，隨機讀， Redo Log 和 Binlog 是順序寫順序讀，OLTP 場景以隨機 IO 為主，OLAP 場景以順序 IO 為主，其存儲結構 MyISAM 是堆表、InnoDB 為索引組織表。

MySQL 對于磁盤的硬件優化原則。

RAID 策略選擇： Sata SSD、PCIe SSD 無需 RAID，機械硬盤優先選擇RAID10，其次是 RAID5。

RAID CACHE BBU 選擇：購置陣列卡同時配備 CACHE 及 BBU 模塊，可提升機械盤 IOPS，定期檢查或監控 CACHE 及 BBU 模塊的健康狀況，確保意外時不至于丟失數據。

磁盤類型選擇：優先選擇 SSD 或 PCIe SSD，機械盤使用高速硬盤。

讀寫策略選擇：有陣列卡時設置陣列寫策略為 WB 或 Force WB with no battery，嚴禁 WT。同時關閉陳列預讀策略，只用作寫緩存。

參數優化

講解完數據庫服務器運行硬件優化后，接下來你將學習 MySQL 參數優化，方便我們進行參數調優。

MySQL 參數優化可以分為系統全局內存參數（SGA）和線程全局內存（PGA）。首先先聊下 SGA 系統全局內存分配的相關參數：

innodb_buffer_pool_size ，用于緩存行數據、索引數據，以及事務鎖和自適應哈希等。 單機多實例的情況內存建議按實際數據熱點數據量的30%規劃，單機單實例（獨享實例）的情況建議是分配50%～80%。

innodb_buffer_pool_instances，用于提升性能。

innodb_additional_mem_pool_size，用于緩存所有數據字典。

innodb_log_buffer_size ，InnoDB Redo日志緩沖，提高Redo日志寫入效率。

key_buffer_size，MyISAM 表索引高速緩沖，提高 MyISAM 表索引讀寫效率。

query_cache_size，查詢緩存，緩存相同SQL查詢結果，提高查詢結果返回效率，建議禁用。

table_cache table_definiton_cache，表空間文件描述符緩存和表定義文件描述符緩存，提供數據表打開效率。

下圖所示是 PGA 系統全局內存分配，這個是每個連接使用時需要申請對應的內存。使用默認參數值相加統計出每個連接線程占用的最大內存大小為 25MB， 線程級參數不宜設置過大。

接下來我們從面試中經常關注的幾個參數進行講解。

Redo Log

第一個參數是控制 Redo Log 刷盤策略的 innodb_flush_log_at_trx_commit，它有三個取值策略，如下圖所示。

當取值為 0 ，表示事務提交時，MySQL 不會去處理日志緩存區（Log Buffer）的內容，也不會去處理日志文件的刷盤操作，由 MySQL 的后臺 Master 線程每隔 1s 將緩存區的文件刷新到日志文件中。

當取值為 1 ，表示事務提交時，會將日志緩沖區的日志寫入文件中，同時會刷新到磁盤中，保證數據庫事務完全不會丟失。這種設置影響數據庫性能。

當取值為 2，表示事務提交時，會將日志緩存區日志寫入到文件中，但是不會刷新到磁盤中。由 MySQL 的后臺 Master 線程每隔 1s 將系統緩存的日志文件刷新到磁盤中。

如下圖，可以看到其不同取值時對應與日志緩沖區、OS cache、日志文件（ib_logfile）之間的關系。

Binlog
第二個參數是控制 Binlog 刷盤策略的 sync_binlog，其取值分為 0、1、N（N1）三類，如下圖。

當取值為 0 時，事務提交時，MySQL 將 Binlog 信息寫入 Binlog 文件（OS Cache）中，但是 MySQL不控制 Binlog 的刷磁盤操作，由文件系統自己控制其緩存的刷新。這是最危險的，一旦操作系統宕機，在 Binlog cache 中的所有 Binlog 都會丟失。如果只是數據庫宕機，而操作系統未宕機，那么數據庫所生成的 Binlog 都不會丟失。

當取值為 1 時，每一個事務提交時，MySQL 都會把 Binlog 刷新到磁盤中。這樣，數據庫安全性最高，但是性能損耗也是最大的。如果這樣設置的話，在數據庫或操作系統宕機的情況下，二進制日志中缺少的任何事務也只能處于準備階段，那么導致服務器自動恢復時，會回滾這些事務，保證無數據丟失。雖然 Binlog 是順序 IO，但是多個事務同時提交，同樣會對 MySQL 和 IO 的性能帶來很大影響，不過 MySQL 可以通過 Group Commit 來緩解這種壓力。

當取值為 N 時，表示每 N 次事務提交，MySQL 調用文件系統的刷新操作將緩存刷新到磁盤中。如果數據庫或操作系統在這個時候宕機，數據庫可能會丟失一些事務。

Log

接下來是關于 Log 相關參數的介紹，對于寫入壓力大的 OLTP 場景，擴容 Redo Log 有助于提升寫入性能。主要關注的參數如下。

innodb_flush_log_at_timeout：每隔 N 秒寫入并刷新日志，默認為 1 即每秒 flush一次,可選 [1-2700]。該參數值允許增加 flush 之間的間隔以減少刷新，避免影響二進制日志組提交的性能。

innodb_log_file_size：日志文件大小，建議設置1～2GB。

innodb_log_files_in_group：日志文件組個數。

Replication

MySQL 高可用架構的基礎 Replication。

下圖是 MySQL 復制的基本原理圖，它描述了 Replication 的過程。

如圖，它整體分為三個步驟：

主庫 Master 將數據庫的變更操作記錄在二進制日志 Binary Log 中。

備庫 Slave 讀取主庫上的日志并寫入到本地中繼日志 Relay Log 中。

備庫讀取中繼日志 Relay Log 中的 Event 事件在備庫上進行重放 Replay。

整個過程涉及三個 Thread，分別是 Master 的 Binlog Dump Thred，和 Slave 的 I/O Thread 和 SQL Thread。

其具體參與主從復制的數據同步過程如下。

Master 服務器上對數據庫的變更操作記錄在 Binlog 中。

Master 的 Binlog Dump Thread 接到寫入請求后讀取 Binlog 推送給 Slave I/O Thread。

Slave I/O Thread 將讀取的 Binlog 寫入到本地 relay log 文件。

Slave SQL thread 檢測到 relay log 的變更請求，解析 relay log 并在從庫上進行應用。

以上整個復制過程都是異步操作，所以主從復制俗稱異步復制，存在數據延遲。

Master 數據變更后記錄 Binlog，只是通知 Binlog Dump Thread 有數據寫入后就告訴存儲引擎提交事務，并不會關注 Slave 是否接受并落地 Binlog Event。下圖是異步復制的時序圖。

考慮到一個場景，主庫正常寫入數據并提交事務 T1，但是 Slave1 和 Slave2 由于某種原因（例如網絡原因）一直無法接受到 Binlog Dump Thread Event 的推送請求，如果這時候 Master Crash，Slave 提升為 Master 后導致事務 T1 數據丟失。為了提升數據安全，MySQL 讓 Master 在某一個時間點等待 Slave 節點的 ACK 消息后才進行事務提交，這也是半同步復制的基礎。

MySQL 從 5.5 版本開始引入了半同步復制機制來降低數據丟失的概率，為什么是降低呢？原因除了 MySQL 半同步復制會退化成異步復制之外，還需要講下半同步復制after commit 和增強半同步 after-sync 的實現。

介紹半同步復制之前先快速過一下 MySQL 事務寫入碰到主從復制時的完整過程，主庫事務寫入分為 4 個步驟：

InnoDBRedoFile Write (Prepare Write)；

BinlogFile Flush Sync to Binlog File；

InnoDB Redo FileCommit（Commit Write）；

Send Binlog to Slave。

當 Master 不需要關注 Slave 是否接受到 Binlog Event 時，即為傳統的主從復制。

當 Master 需要在第三步等待 Slave 返回 ACK 時，即為 after-commit。

當 Master 需要在第二步等待 Slave 返回 ACK 時，即為 after-sync。

after_commit

首先介紹 after-commit。

MySQL Master 將事務寫入 Binlog（sync_binlog=1）并推送給 Slave 后主庫將事務提交到存儲引擎（此時未返回客戶端但是其他會話可以訪問到事務提交信息），Slave I/O Thread 將讀取的 Binlog 寫入到本地 relay log 文件（sync_relay=1）后向 Master 返回 ACK 消息，當主庫 Master 等到 Slave 返回的 ACK 消息后 Master 將事務提交成功的結果返回給客戶端。

對于當前會話的客戶端進行事務提交后，主庫等待 ACK 的過程中有兩種情況。

事務還沒發送到從庫，主庫 crash 并發起切換，從庫為新主庫。客戶端收到事務提交失敗的信息，需要重新提交該事務。

事務已經發送到從庫，主庫 crash 并發起切換，從庫為新主庫。從庫已經應用該事務并寫入數據，但客戶端連接重置同樣會收到事務提交失敗的信息，重新提交該事務時會報錯數據已存在（如訂單已提交成功）。

如下圖所示，after-commit 的情況在非當前客戶端訪問數據時會出現“數據幻讀”的情況，例如User1 想在 t1 表插入記錄 3 并在存儲引擎層提交事務，此時 User2 可以看到已經提交事務的數據記錄 3。當時 Master 在等待 Slave 返回 ACK 的過程中 Crash 了并且 Slave也沒有成功接受到 Binlog Event，此時 Slave 提升為 Master 時 User2 發現之前訪問到的數據記錄 3 又不見了。如果 Master Crash 后無法啟動，那么提交的事務記錄 3 在從庫上永遠找不到了，導致數據丟失。

after-sync

講完 after-commit，接下來我們講 after_sync。

為了提升數據的安全性，MySQL 5.7 引入了增強半同步 after_sync（無損復制），并將其設置為默認的半同步方式來解決數據丟失的問題。

如下圖，after-sync 是將 Master 等待 ACK 消息放到了 BinlogFile Flush Sync to Binlog File 之后，Engine Commit 之前，這樣就可以保證數據不會丟失，因為 Slave 接受到event 并寫入自身 relay log。

對于數據安全的場景，參數 innodb_flush_log_at_trx_commit 和 sync_log 配置為雙一配合 after-sync 半同步模式是一個好的選擇，這也是大部分金融場景的參數配置。

運維過程中有個關鍵點需要注意：當半同步等待 ACK 超時時，半同步復制會退化為異步復制，具體細節你在課后可以查看有關半同步相關參數的配置。

下圖是 MySQL 官方對于半同步復制的時序圖，主庫等待從庫寫入 relay log 并返回 ACK 后才進行 Engine Commit。

Galera Cluster

上面講的主從異步復制、半同步復制都屬于異步復制，接下來聊聊同步復制（準同步）Galera Cluster 和 MySQL Group Replication。

最開始 Galera Cluster 的實現有兩個，一個是 MariaDB 實現的 MariaDB Galera Cluster- MGC；一個是 Percona 實現的 Percona XtraDB Cluster-PXC。

通常由三個實例組成的一個集群，三個節點均可以提供讀寫，即常見的 Multi-Master多主架構。客戶端可以讀寫訪問集群任意一個節點，集群節點間組成了 Group communication，如下圖所示。這可以用來保證集群節點數據的強一致性，這種架構是 Share-Nothing，不共享數據、多副本的高冗余架構，擁有多點寫入、同步復制、無復制延遲、并法復制、隨意切換、節點自動配置、健康檢查等功能。

Group communication 的本質是 Galera Cluster，它來實現強一致性、支持多點寫入的同步復制集群架構，Galera Cluter 提供了一系列的 API，為上層 MySQL 提供豐富的狀態信息及回調函數，API 即 Write-Set Replication API，簡稱 wsrep API。通過這些API 來提供基于寫集驗證的樂觀的同步復制，當一個節點組裝完寫集后，每個節點在復制事務時都會在組內廣播寫集并進行寫集比對，如果沒有沖突的話，那么 Galera Cluster 層對該寫集對應的事務就可以繼續提交或 APPLY，當數據庫 MySQL 層得到Galera Cluster 層返回的回調狀態信息后繼續事務提交或回滾的操作。

Galera Cluster是一個強一致性集群，當集群節點有數據寫入時，Group communication 會向組內所有成員廣播寫集（初步可簡單理解為寫入的Binlog），所有節點驗證通過之后寫節點開始提交，其他節點執行寫集應用和提交，當出現數據沖突時則寫節點執行回滾，其他節點丟棄該寫集。

MySQL Group Replication

MySQL 在 5.7 版本參考 Galera Cluster 的技術實現推出了 MySQL Group Replication（簡稱 MGR）。

MGR 同樣是一個支持多點寫入的多主復制架構，它基于原生 MySQL 主從復制的基礎上構建組通信層，由 Group Replication 提供一組原子消息并且按照全局順序進行消息傳遞，集群任何節點均可寫入，但所有寫入事務只有在獲得復制組認證通過（多數派協議 Paxos）后才能進行提交。例如由若干個節點共同組成一個復制組，一個事務的提交必須經過組內大多數節點（N / 2 + 1）決議并通過，才能得以提交。

如下是 MySQL Group Replication /Galera Cluster 的時序圖：由 3 個節點組成一個復制組，Consensus 層為一致性協議層，在事務提交過程中，發生組間通信，由 2 個節點決議（certify）通過這個事務，事務才能夠最終得以提交并響應。

經典架構和適用場景

接下來聊一下經典架構和適用場景。

主從復制

首先是基于主從構建的一主一從架構，應用程序讀寫直接訪問 Master 或者配置 Master 和 Slave 的數據源進行人為的讀寫分離，當 Master 出現故障時需要人工維護介入。通常適合于輕量級程序、高可用要求不高的業務場景。如下圖所示，這類架構中應用程序直連訪問 Master 和 Slave 進行讀寫分離，當 Master 出現故障時由于無法自動切換導致服務受損。因此通常會基于此架構加上 VIP/DNS + Keepalived 及雙主復制來做一個簡單的高可用切換。

雙主復制

隨著業務的發展，架構由常見的主從演變為雙主架構并引入高可用組件。

常見的使用方式是構建雙主復制，其中一個 Master 提供線上服務，另一個 Master 作為 Standby 供高可用切換，Master 下游掛載 Slave 承擔讀請求。 高可用架構通常是配置 VIP/DNS+Keepalived 或使用業內早期的 MMM 架構。

MMM 架構提供了單點判斷的 Monitor，由它來判斷 Master 的存活并進行 VIP 的漂移，MMM 的優點是基于 MySQL 原生復制，其工具集功能強大，提供了一套 HA、Failover 的 tools 來幫助運維。

MMM 缺點是架構比較落伍且長期不更新（導致很多 MySQL 的新特性無法支持，例如 GTID），同時由于 MMM 是單點判斷并沒有 watch dog 守護進程，對于網絡分區或網絡抖動的場景會出現集群腦裂，當出現業務在兩邊數據同時寫入時會出現寫入沖突甚至數據錯亂的問題。最大的問題是 MMM 備選主延遲過大會導致無法切換，不提供 binlog 補償的功能。

該架構不適用于對數據一致性要求高的業務場景，適用于能夠容忍網絡抖動導致數據沖突和不可用、容忍數據丟失的應用場景，其架構擴展和讀寫分離需要應用程序聯調配合。

樹形復制

通用的架構還有樹形復制，也叫級聯復制。這類架構通常適用于數據訪問策略分層，例如 MySQL Master 和 MySQL Slave 參與線上業務訪問及高可用切換，MySQL Statistic 節點提供離線查詢、報表慢查和非線上業務訪問，如下圖。

這類架構是業內常用的復制拓撲圖，開啟半同步復制及配置一套高可用切換工具即可應對大部分應用場景的訪問需求。

環形復制

最后介紹下環形復制，基于原生 MySQL Replication 構建的環形復制由于構建費力不討好、運維復雜不友好等原因不在介紹范圍內，環形復制重點在于 MySQL Group Replication 和 Galera Cluster。

這兩種架構均可滿足保證集群節點數據強一致性、多點寫入的需求，適用于金融場景及對數據一致性要求高的業務場景，根據成熟度和線上集群使用規模考慮，目前 MGR 線上運行較少，可以技術儲備并關注官網信息，當前推進成熟的 Galera Cluster，按需選擇 MariaDB Galera Cluster-MGC 或 Percona XtraDB Cluster-PXC 即可。