讀寫分離 & 分庫分表 & 深度分頁

balfish發表於2024-03-09

什麼是讀寫分離？

見名思意，根據讀寫分離的名字，我們就可以知道：讀寫分離主要是為了將對資料庫的讀寫操作分散到不同的資料庫節點上。 這樣的話，就能夠小幅提升寫效能，大幅提升讀效能。

一般情況下，我們都會選擇一主多從，也就是一臺主資料庫負責寫，其他的從資料庫負責讀。主庫和從庫之間會進行資料同步，以保證從庫中資料的準確性。這樣的架構實現起來比較簡單，並且也符合系統的寫少讀多的特點。

如何實現讀寫分離？

不論是使用哪一種讀寫分離具體的實現方案，想要實現讀寫分離一般包含如下幾步：

  1. 部署多臺資料庫，選擇其中的一臺作為主資料庫，其他的一臺或者多臺作為從資料庫。
  2. 保證主資料庫和從資料庫之間的資料是實時同步的，這個過程也就是我們常說的主從複製。
  3. 系統將寫請求交給主資料庫處理，讀請求交給從資料庫處理。

落實到專案本身的話，常用的方式有兩種：

1. 代理方式

我們可以在應用和資料中間加了一個代理層。應用程式所有的資料請求都交給代理層處理，代理層負責分離讀寫請求，將它們路由到對應的資料庫中。

提供類似功能的中介軟體有 MySQL Router（官方， MySQL Proxy 的替代方案）、Atlas（基於 MySQL Proxy）、MaxScale、MyCat。

關於 MySQL Router 多提一點：在 MySQL 8.2 的版本中，MySQL Router 能自動分辨對資料庫讀寫/操作並把這些操作路由到正確的例項上。這是一項有價值的功能，可以最佳化資料庫效能和可擴充套件性，而無需在應用程式中進行任何更改

2. 元件方式

在這種方式中，我們可以透過引入第三方元件來幫助我們讀寫請求。

這也是我比較推薦的一種方式。這種方式目前在各種網際網路公司中用的最多的，相關的實際的案例也非常多。如果你要採用這種方式的話，推薦使用 sharding-jdbc ，直接引入 jar 包即可使用，非常方便。同時，也節省了很多運維的成本。

主從複製原理是什麼？

MySQL binlog(binary log 即二進位制日誌檔案) 主要記錄了 MySQL 資料庫中資料的所有變化(資料庫執行的所有 DDL 和 DML 語句)。因此，我們根據主庫的 MySQL binlog 日誌就能夠將主庫的資料同步到從庫中。

  1. 主庫將資料庫中資料的變化寫入到 binlog
  2. 從庫連線主庫
  3. 從庫會建立一個 I/O 執行緒向主庫請求更新的 binlog
  4. 主庫會建立一個 binlog dump 執行緒來傳送 binlog ，從庫中的 I/O 執行緒負責接收
  5. 從庫的 I/O 執行緒將接收的 binlog 寫入到 relay log 中。
  6. 從庫的 SQL 執行緒讀取 relay log 同步資料本地（也就是再執行一遍 SQL ）。

怎麼樣？看了我對主從複製這個過程的講解，你應該搞明白了吧!

你一般看到 binlog 就要想到主從複製。當然，除了主從複製之外，binlog 還能幫助我們實現資料恢復。

如何避免主從延遲？

讀寫分離對於提升資料庫的併發非常有效，但是，同時也會引來一個問題：主庫和從庫的資料存在延遲，比如你寫完主庫之後，主庫的資料同步到從庫是需要時間的，這個時間差就導致了主庫和從庫的資料不一致性問題。這也就是我們經常說的 主從同步延遲 。

如果我們的業務場景無法容忍主從同步延遲的話，應該如何避免呢（注意：我這裡說的是避免而不是減少延遲）？

這裡提供兩種我知道的方案（能力有限，歡迎補充），你可以根據自己的業務場景參考一下。

強制將讀請求路由到主庫處理

既然你從庫的資料過期了，那我就直接從主庫讀取嘛！這種方案雖然會增加主庫的壓力，但是，實現起來比較簡單，也是我瞭解到的使用最多的一種方式。

比如 Sharding-JDBC 就是採用的這種方案。透過使用 Sharding-JDBC 的 HintManager 分片鍵值管理器，我們可以強制使用主庫。

HintManager hintManager = HintManager.getInstance();
hintManager.setMasterRouteOnly();
// 繼續JDBC操作

對於這種方案，你可以將那些必須獲取最新資料的讀請求都交給主庫處理。

延遲讀取

還有一些朋友肯定會想既然主從同步存在延遲，那我就在延遲之後讀取啊，比如主從同步延遲 0.5s,那我就 1s 之後再讀取資料。這樣多方便啊！方便是方便，但是也很扯淡。

不過，如果你是這樣設計業務流程就會好很多：對於一些對資料比較敏感的場景，你可以在完成寫請求之後，避免立即進行請求操作。比如你支付成功之後，跳轉到一個支付成功的頁面，當你點選返回之後才返回自己的賬戶。

總結

關於如何避免主從延遲，我們這裡介紹了兩種方案。實際上，延遲讀取這種方案沒辦法完全避免主從延遲，只能說可以減少出現延遲的機率而已，實際專案中一般不會使用。

總的來說，要想不出現延遲問題，一般還是要強制將那些必須獲取最新資料的讀請求都交給主庫處理。如果你的專案的大部分業務場景對資料準確性要求不是那麼高的話，這種方案還是可以選擇的。

什麼情況下會出現主從延遲？如何儘量減少延遲？

我們在上面的內容中也提到了主從延遲以及避免主從延遲的方法，這裡我們再來詳細分析一下主從延遲出現的原因以及應該如何儘量減少主從延遲。

要搞懂什麼情況下會出現主從延遲，我們需要先搞懂什麼是主從延遲。

MySQL 主從同步延時是指從庫的資料落後於主庫的資料，這種情況可能由以下兩個原因造成：

  1. 從庫 I/O 執行緒接收 binlog 的速度跟不上主庫寫入 binlog 的速度，導致從庫 relay log 的資料滯後於主庫 binlog 的資料；
  2. 從庫 SQL 執行緒執行 relay log 的速度跟不上從庫 I/O 執行緒接收 binlog 的速度，導致從庫的資料滯後於從庫 relay log 的資料。

與主從同步有關的時間點主要有 3 個：

  1. 主庫執行完一個事務，寫入 binlog，將這個時刻記為 T1；
  2. 從庫 I/O 執行緒接收到 binlog 並寫入 relay log 的時刻記為 T2；
  3. 從庫 SQL 執行緒讀取 relay log 同步資料本地的時刻記為 T3。

結合我們上面講到的主從複製原理，可以得出：

  • T2 和 T1 的差值反映了從庫 I/O 執行緒的效能和網路傳輸的效率，這個差值越小說明從庫 I/O 執行緒的效能和網路傳輸效率越高。
  • T3 和 T2 的差值反映了從庫 SQL 執行緒執行的速度，這個差值越小，說明從庫 SQL 執行緒執行速度越快。

結合我們上面講到的主從複製原理，可以得出：

  • T2 和 T1 的差值反映了從庫 I/O 執行緒的效能和網路傳輸的效率，這個差值越小說明從庫 I/O 執行緒的效能和網路傳輸效率越高。
  • T3 和 T2 的差值反映了從庫 SQL 執行緒執行的速度，這個差值越小，說明從庫 SQL 執行緒執行速度越快。

那什麼情況下會出現出從延遲呢？這裡列舉幾種常見的情況：

  1. 從庫機器效能比主庫差：從庫接收 binlog 並寫入 relay log 以及執行 SQL 語句的速度會比較慢（也就是 T2-T1 和 T3-T2 的值會較大），進而導致延遲。解決方法是選擇與主庫一樣規格或更高規格的機器作為從庫，或者對從庫進行效能最佳化，比如調整引數、增加快取、使用 SSD 等。
  2. 從庫處理的讀請求過多：從庫需要執行主庫的所有寫操作，同時還要響應讀請求，如果讀請求過多，會佔用從庫的 CPU、記憶體、網路等資源，影響從庫的複製效率（也就是 T2-T1 和 T3-T2 的值會較大，和前一種情況類似）。解決方法是引入快取（推薦）、使用一主多從的架構，將讀請求分散到不同的從庫，或者使用其他系統來提供查詢的能力，比如將 binlog 接入到 Hadoop、Elasticsearch 等系統中。
  3. 大事務：執行時間比較長，長時間未提交的事務就可以稱為大事務。由於大事務執行時間長，並且從庫上的大事務會比主庫上的大事務花費更多的時間和資源，因此非常容易造成主從延遲。解決辦法是避免大批次修改資料，儘量分批進行。類似的情況還有執行時間較長的慢 SQL ，實際專案遇到慢 SQL 應該進行最佳化。
  4. 從庫太多：主庫需要將 binlog 同步到所有的從庫，如果從庫數量太多，會增加同步的時間和開銷（也就是 T2-T1 的值會比較大，但這裡是因為主庫同步壓力大導致的）。解決方案是減少從庫的數量，或者將從庫分為不同的層級，讓上層的從庫再同步給下層的從庫，減少主庫的壓力。
  5. 網路延遲：如果主從之間的網路傳輸速度慢，或者出現丟包、抖動等問題，那麼就會影響 binlog 的傳輸效率，導致從庫延遲。解決方法是最佳化網路環境，比如提升頻寬、降低延遲、增加穩定性等。
  6. 單執行緒複製：MySQL5.5 及之前，只支援單執行緒複製。為了最佳化複製效能，MySQL 5.6 引入了 多執行緒複製，MySQL 5.7 還進一步完善了多執行緒複製。

分庫分表

分庫 就是將資料庫中的資料分散到不同的資料庫上，可以垂直分庫，也可以水平分庫。

垂直分庫 就是把單一資料庫按照業務進行劃分，不同的業務使用不同的資料庫，進而將一個資料庫的壓力分擔到多個資料庫。

水平分庫 是把同一個表按一定規則拆分到不同的資料庫中，每個庫可以位於不同的伺服器上，這樣就實現了水平擴充套件，解決了單表的儲存和效能瓶頸的問題。
分表同。

常見的分片演算法有哪些？

分片演算法主要解決了資料被水平分片之後，資料究竟該存放在哪個表的問題。

  • 雜湊分片：求指定 key（比如 id） 的雜湊，然後根據雜湊值確定資料應被放置在哪個表中。雜湊分片比較適合隨機讀寫的場景，不太適合經常需要範圍查詢的場景。
  • 範圍分片：按照特性的範圍區間（比如時間區間、ID 區間）來分配資料，比如 將 id 為 1~299999 的記錄分到第一個庫， 300000~599999 的分到第二個庫。範圍分片適合需要經常進行範圍查詢的場景，不太適合隨機讀寫的場景（資料未被分散，容易出現熱點資料的問題）。
  • 地理位置分片：很多 NewSQL 資料庫都支援地理位置分片演算法，也就是根據地理位置（如城市、地域）來分配資料。
  • 融合演算法：靈活組合多種分片演算法，比如將雜湊分片和範圍分片組合

深度分頁介紹

查詢偏移量過大的場景我們稱為深度分頁，這會導致查詢效能較低，例如：

# MySQL 在無法利用索引的情況下跳過1000000條記錄後，再獲取10條記錄
SELECT * FROM t_order ORDER BY id LIMIT 1000000, 10

範圍查詢

當可以保證 ID 的連續性時，根據 ID 範圍進行分頁是比較好的解決方案： 

# 查詢指定 ID 範圍的資料
SELECT * FROM t_order WHERE id > 100000 AND id <= 100010 ORDER BY id
# 也可以透過記錄上次查詢結果的最後一條記錄的ID進行下一頁的查詢：
SELECT * FROM t_order WHERE id > 100000 LIMIT 10

這種最佳化方式限制比較大，且一般專案的 ID 也沒辦法保證完全連續。

子查詢

我們先查詢出 limit 第一個引數對應的主鍵值，再根據這個主鍵值再去過濾並 limit，這樣效率會更快一些。

# 透過子查詢來獲取 id 的起始值，把 limit 1000000 的條件轉移到子查詢
SELECT * FROM t_order WHERE id >= (SELECT id FROM t_order limit 1000000, 1) LIMIT 10;

不過，子查詢的結果會產生一張新表，會影響效能，應該儘量避免大量使用子查詢。並且，這種方法只適用於 ID 是正序的。在複雜分頁場景，往往需要透過過濾條件，篩選到符合條件的 ID，此時的 ID 是離散且不連續的。

當然，我們也可以利用子查詢先去獲取目標分頁的 ID 集合，然後再根據 ID 集合獲取內容，但這種寫法非常繁瑣，不如使用 INNER JOIN 延遲關聯。

延遲關聯

延遲關聯的最佳化思路，跟子查詢的最佳化思路其實是一樣的：都是把條件轉移到主鍵索引樹，減少回表的次數。不同點是，延遲關聯使用了 INNER JOIN（內連線） 包含子查詢。

SELECT t1.* FROM t_order t1
INNER JOIN (SELECT id FROM t_order limit 1000000, 10) t2
ON t1.id = t2.id
LIMIT 10;

覆蓋索引

索引中已經包含了所有需要獲取的欄位的查詢方式稱為覆蓋索引。

覆蓋索引的好處：

  • 避免 InnoDB 表進行索引的二次查詢，也就是回表操作: InnoDB 是以聚集索引的順序來儲存的，對於 InnoDB 來說，二級索引在葉子節點中所儲存的是行的主鍵資訊，如果是用二級索引查詢資料的話，在查詢到相應的鍵值後，還要透過主鍵進行二次查詢才能獲取我們真實所需要的資料。而在覆蓋索引中，二級索引的鍵值中可以獲取所有的資料，避免了對主鍵的二次查詢（回表），減少了 IO 操作，提升了查詢效率。
  • 可以把隨機 IO 變成順序 IO 加快查詢效率: 由於覆蓋索引是按鍵值的順序儲存的，對於 IO 密集型的範圍查詢來說，對比隨機從磁碟讀取每一行的資料 IO 要少的多，因此利用覆蓋索引在訪問時也可以把磁碟的隨機讀取的 IO 轉變成索引查詢的順序 IO。
# 如果只需要查詢 id, code, type 這三列，可建立 code 和 type 的覆蓋索引
SELECT id, code, type FROM t_order
ORDER BY code
LIMIT 1000000, 10;

不過，當查詢的結果集佔表的總行數的很大一部分時，可能就不會走索引了，自動轉換為全表掃描。當然了，也可以透過 FORCE INDEX 來強制查詢最佳化器走索引，但這種提升效果一般不明顯。

相關文章