相關文章
資料庫系列：MySQL慢查詢分析和效能最佳化
資料庫系列：MySQL索引最佳化總結（綜合版）
資料庫系列：高併發下的資料欄位變更
資料庫系列：覆蓋索引和規避回表
資料庫系列：資料庫高可用及無損擴容
資料庫系列：使用高區分度索引列提升效能
資料庫系列：字首索引和索引長度的取捨
資料庫系列：MySQL引擎MyISAM和InnoDB的比較
資料庫系列：InnoDB下實現高併發控制
資料庫系列：事務的4種隔離級別
資料庫系列：RR和RC下，快照讀的區別
資料庫系列：MySQL InnoDB鎖機制介紹
資料庫系列：MySQL不同操作分別用什麼鎖？
資料庫系列：業內主流MySQL資料中介軟體梳理
資料庫系列：巨量資料表的分頁效能問題
資料庫系列: 主流分庫分表中介軟體介紹(圖文總結)
資料裂變，資料庫高可用架構設計實踐
1 本文索引
1 啥是MySQL主從複製?
主從複製，是指建立一個和主資料庫完全一樣的資料庫環境（稱為從資料庫），並將主庫的操作行為進行復制的過程，那如何保證從庫跟主庫的資料一致性？ 即將主資料庫的DDL和DML的操作日誌同步到從資料庫上，然後在從資料庫上對這些日誌進行重新執行，來保證從資料庫和主資料庫的資料的一致性。
1.1 為什麼要做主從複製?
1、在複雜的業務操作中，經常會有操作導致鎖行甚至鎖表的情況，如果讀寫不解耦，會很影響執行中的業務，使用主從複製，讓主庫負責寫，從庫負責讀，職責更單一，效能更優良。
即使主庫出現了鎖表的情景，透過讀從庫也可以保證業務的正常執行。
2、保證資料的熱備份，主庫當機後能夠及時替換主庫，保障業務可用性。
3、架構的演進：業務量擴大，I/O訪問頻率增高，單機無法滿足，主從複製可以做多庫方案，降低磁碟I/O訪問的頻率，提高單機的I/O效能。
4、本質上也是分治理念，主從複製、讀寫分離即是壓力分拆的過程。
5、讀寫比也影響整個拆分方式，讀寫比越高，主從庫比例應越高，才能保證讀寫的均衡，才能保證較好的執行效能。讀寫比下的主從分配策略如下：
| 讀寫比（大約） | 主庫 | 從庫 |
|---|---|---|
| 50：50 | 1 | 1 |
| 66.6：33.3 | 1 2 | |
| 80：20 | 1 | 4 |
| -- -- | -- -- | -- -- |
1.2 為什麼會出現主從延遲?
當在從庫上啟動複製時，首先建立I/O執行緒連線主庫，主庫隨後建立Binlog Dump執行緒讀取資料庫事件併傳送給I/O執行緒，I/O執行緒獲取到事件資料後更新到從庫的中繼日誌Relay Log中去，之後從庫上的SQL執行緒讀取中繼日誌Relay Log中更新的資料庫事件並應用，如下圖所示：
細化一下有如下幾個步驟：
1、MySQL主庫在事務提交時把資料變更（insert、delet、update）作為事件日誌記錄在二進位制日誌表（binlog）裡面。
2、主庫上有一個工作執行緒 binlog dump thread，把binlog的內容傳送到從庫的中繼日誌relay log中。
3、從庫根據中繼日誌relay log重做資料變更操作，透過邏輯複製來達到主庫和從庫的資料一致性。
4、MySQL透過三個執行緒來完成主從庫間的資料複製，其中binlog dump執行緒跑在主庫上，I/O執行緒和SQL執行緒跑在從庫上。擁有多個從庫的主庫會為每一個連線到主庫的從庫建立一個binlog dump執行緒。
2 主從延遲的原因分析
先理解以下概念，說說啥是主從延時？
主從延時，通常指的是在資料庫的主從複製架構中，從伺服器（Slave）在接收並應用主伺服器（Master）上的資料變更時所經歷的時間延遲。具體來說，當主伺服器上的資料發生變化後，這些變更需要透過複製機制同步到從伺服器，而從伺服器處理這些變更並完成資料同步所需的時間就構成了所謂的延時。
直接的後果就是從庫中查詢到的資料結果和主庫中是不一致的，我們經常在查詢資料的過程中會查詢到舊資料，可能就是這個原因導致的！
MySQL主從複製，讀寫分離是我們常用的資料庫架構，但是在併發量較大、資料變化大的場景下，主從延時會比較嚴重。
延遲的本質原因是：系統TPS併發較高時，主庫產生的DML（也包含一部分DDL）數量超過Slave一個Sql執行緒所能承受的範圍，效率就降低了。
我們看到這個sql thread 是單個執行緒，所以他在重做RelayLog的時候，能力也是有限的。
還有一些其他原因，比如：
- 主伺服器負載過高：當主伺服器需要處理大量的資料變更時，可能會產生較高的負載，導致資料變更的生成和傳輸速度變慢，從而增加從伺服器的同步延遲。
- 從伺服器負載過高：從伺服器在接收並應用主伺服器上的資料變更時，如果自身的負載過高，可能會導致處理速度變慢，從而產生同步延遲。
- 網路延遲：主從伺服器之間的網路連線不穩定或頻寬不足，也可能導致資料變更的傳輸速度變慢，從而增加同步延遲。
- 機器效能差異：主從伺服器的硬體配置不同，例如CPU、記憶體、磁碟等效能差異，也可能導致同步延遲的產生。
- MySQL配置不合理：例如，如果主伺服器的二進位制日誌（binlog）設定過大，或者從伺服器的中繼日誌（relay log）配置不合理，都可能導致處理速度變慢，從而產生同步延遲。
- 從庫大量的大型的 query 語句產生了鎖等待
3 主從延時最佳化方案
3.1 最優的系統配置
最佳化系統配置（系統級、連結層、儲存引擎層），讓資料庫處在最優狀態：最大連線數、允許錯誤數、允許超時時間、pool_size、log_size等，保證記憶體、CPU、儲存空間的擴容（硬體部分）。
倒金字塔法則告訴我們，這一塊往往是被忽略的，但是又是必不可少的。
如果MySQL部署在linux系統上，可以適當調整作業系統的引數來最佳化MySQL效能，下面是對Linux核心引數進行適當調整。
1 # TIME_WAIT超時時間，預設是60s
2 net.ipv4.tcp_fin_timeout = 30
3 # 增加tcp支援的佇列數，加大佇列長度可容納更多的等待連線
4 net.ipv4.tcp_max_syn_backlog = 65535
5 # 減少斷開連線時 ，資源回收
6 net.ipv4.tcp_max_tw_buckets = 8000
7 net.ipv4.tcp_tw_reuse = 1
8 net.ipv4.tcp_tw_recycle = 1
9 net.ipv4.tcp_fin_timeout = 10
10 # 開啟檔案的限制
11 *soft nofile 65535
12 *hard nofile 65535
MySQL5.5+版本之後，預設儲存引擎為InnoDB，我們這邊列出部分可能影響資料庫效能的引數。
公共引數預設值：
1 max_connections = 151
2 # 同時處理最大連線數，建議設定最大連線數是上限連線數的80%左右,一般預設值為151，可以做適當調整。
3 sort_buffer_size = 2M
4 # 查詢排序時緩衝區大小，只對order by和group by起作用，建議增大為16M
5 open_files_limit = 1024
6 # 開啟檔案數限制，如果show global status like 'open_files'檢視的值等於或者大於open_files_limit值時，程式會無法連線資料庫或卡死
InnoDB引數預設值：
1 innodb_buffer_pool_size = 128M
2 # 索引和資料緩衝區大小，建議設定實體記憶體的70%左右（這個前提是這個伺服器只用做Mysql資料庫伺服器）
3 innodb_buffer_pool_instances = 1
4 # 緩衝池例項個數，推薦設定4個或8個
5 innodb_flush_log_at_trx_commit = 1
6 # 關鍵引數，0代表大約每秒寫入到日誌並同步到磁碟，資料庫故障會丟失1秒左右事務資料。1為每執行一條SQL後寫入到日誌並同步到磁碟，I/O開銷大，執行完SQL要等待日誌讀寫，效率低。2代表只把日誌寫入到系統快取區，再每秒同步到磁碟，效率很高，如果伺服器故障，才會丟失事務資料。對資料安全性要求不是很高的推薦設定2，效能高，修改後效果明顯。
7 sync_binlog=1
8
9 innodb_file_per_table = ON
10 # 是否共享表空間，5.7+版本預設ON，共享表空間idbdata檔案不斷增大，影響一定的I/O效能。建議開啟獨立表空間模式，每個表的索引和資料都存在自己獨立的表空間中，可以實現單表在不同資料庫中移動。
11 innodb_log_buffer_size = 8M
12 # 日誌緩衝區大小，由於日誌最長每秒鐘重新整理一次，所以一般不用超過16M
3.2 資料庫層做合理分治
資料庫分割槽是永恆的話題，主從延遲一定程度上是單臺資料庫主服務操作過於頻繁，使得單執行緒的SQL thread 疲於應付。可以適當的從功能上對資料庫進行拆分，分擔壓力。
資料庫拆分可以參考我的這篇文章《分庫分表》，這邊就不贅述。
3.3 從庫同步完成後響應
假如你的業務時間允許，你可以在寫入主庫的時候，確保資料都同步到從庫了之後才返回這條資料寫入成功，當然如果有多個從庫，你也必須確保每個從庫都寫入成功。當然，這個方案對效能和時間的消耗是極大的，會直接降低你的系統吞吐量，不推薦。
3.4 適當引入快取
可以引入redis或者其他nosql資料庫來儲存我們經常會產生主從延遲的業務資料。當我在寫入資料庫的同時，我們再寫入一份到redis中。
讀取資料的時候，我們可以先去檢視redis中是否有這個資料，如果有我們就可以直接從redis中讀取這個資料。當資料真正同步到資料庫中的時候，再從redis中把資料刪除。如下圖：
這邊還需注意兩點，很重要喲，面試必問：
1、雖然一定程度上緩解延遲的問題，但如果遇到高併發的情況，對Redis的頻繁刪除也不合理，所以需要結合場景綜合考慮，比如定期刪除快取。
2、高併發情況下可能存在slave還沒同步，又有新的值寫進來了，這時候Master --> Slave 還在排隊中，但是Cache已經被更新了。所以如果對Redis進行刪除，可能會誤刪除最新的快取值，導致讀取到的資料是舊的。
所以這個地方也需要注意，可以同時將唯一鍵（比如主鍵）也做儲存，刪除之前做一個判斷，避免誤刪。或者乾脆不實時刪除快取，低峰值期再來處理。
3.5 多執行緒重放RelayLog
MySQL使用單執行緒重放RelayLog，那能不能在這上面做解法呢，比如使用多執行緒並行重放RelayLog，就可以縮短時間。但是這個對資料一致性是個考驗。
需要考慮如何分割RelayLog，才能夠讓多個資料庫例項，多個執行緒並行重放RelayLog，不會出現不一致。比如RelayLog包含這三條語句給學生授予學分的記錄，你就不知道結果會變成什麼。可能是806甚至是721。
1 update t_score set score = 721 where stu_code=374532;
2 update t_score set score = 806 where stu_code=374532;
3 update t_score set score = 899 where stu_code=374532;
解法就是：
相同庫表上的寫操作，用相同的執行緒來重放RelayLog；不同庫表上的寫操作，可以併發用多個執行緒併發來重放RelayLog。
設計一個雜湊演算法，hash(db-name) % thread-num，表名稱hash之後再模上執行緒數，就能很輕易做到，同一個庫表上的寫操作，被同一個重放執行緒序列執行，做到提效的目的。
這其實也是一種分治的思維，類似上面直接對資料庫進行拆分。
3.6 少量讀業務直連主庫
業務量不多的情況下，不做主從分離.既然主從延遲是由於從庫同步寫庫不及時引起的，那我們也可以在有主從延遲的地方改變讀庫方式，由原來的讀從庫改為讀主庫。當然這也會增加程式碼的一些邏輯複雜性。
這邊需要注意的是，直接讀主庫的業務量不宜多，而且是讀實時一致性有剛性需求的業務才這麼做。否則背離讀寫分離的目的。
3.7 適當的限流、降級
任何的伺服器都是有吞吐量的限制的，沒有任何一個方案可以無限制的承載使用者的大量流量。所以我們必須估算好我們的伺服器能夠承載的流量上限是多少。
達到這個上限之後，就要採取快取，限流，降級的這三大殺招來應對我們的流量。這也是應對主從延遲的根本處理辦法。
3.8 高版本MySQL支援多執行緒複製
MySQL 5.6版本開始支援多執行緒複製（也稱為並行複製），MySQL5.7之後，提供了基於GTID並行複製的能力。在5.6這個版本中，預設是單執行緒複製，但是可以透過配置引數slave_parallel_workers來啟用多執行緒複製。
要啟用多執行緒複製，請按照以下步驟操作：
1. 確保你的MySQL版本是5.6+
2. 修改多執行緒配置
修改MySQL的my.cnf（或my.ini）配置檔案，在從伺服器上設定slave_parallel_workers引數為期望的工作執行緒數，例如：
[mysqld]
slave_parallel_workers = 8
3. 重啟MySQL服務以使配置生效。
4. 確認多執行緒複製是否已經啟用：
SHOW VARIABLES LIKE 'slave_parallel_workers';
如果返回值大於0，則表示多執行緒複製已經開啟，並且可以使用指定數量的執行緒來應用日誌事件。
4 總結
上面提到了多種方案都是可以討論，每個方法都有弊端和優勢，根據實際情況進行選型。