MySQL分庫分表與分區的入門指南

前言

關系型數據庫比較容易成為系統瓶頸,單機存儲容量、連接數、處理能力都有限,當數據量和並發量起來之後,就必須對數據庫進行切分瞭。

數據切分(sharding)的手段就是分庫分表。分庫分表有兩方面,可能是光分庫不分表,也可能是光分表不分庫。

數據庫分佈式的核心內容無非就是數據切分,以及切分後對數據的定位、整合。

為什麼要分庫分表

分表

單表數據量太大時,會嚴重影響sql執行的性能。一般單表到達幾百萬的時候,性能就會相對差一些瞭,這時就得分表瞭。

分表就是把一個表的數據放到多個表中,然後查詢的時候就查一個表。比如按照項目id來分表:將固定數量的項目數據放在一個表中,這樣就可以控制每個表的數據量在可控的范圍內。

分庫

根據經驗來講,一個庫最多支持到並發2000時就需要擴容瞭,而且一個健康的單庫並發值最好保持在1000左右。那麼你可以將一個庫的數據拆分到多個庫中,訪問的時候就訪問一個庫好瞭。

這就是所謂的分庫分表,為啥要分庫分表?

  • 提高並發支撐能力
  • 降低磁盤使用率
  • 提高SQL執行性能

如何分庫分表

直接看圖:

對於垂直拆分,建議最好在系統設計之初做好表設計,避免垂直分表。

水平拆分可以按照range來分,或是按照某個字段hash。按照range來分,好處在於擴容簡單,準備好新的表或庫就可以瞭。但是容易產生熱點問題,實際使用時要結合業務場景來看。按照hash來分,好處在於可以平均分配每個庫或表的請求壓力,缺點是擴容麻煩,之前的數據要rehash,存在一個數據遷移的過程。

分庫分表帶來的問題

分庫分表能有效地緩解單機和單庫帶來的網絡IO、硬件資源、連接數的壓力。但也帶來瞭一些問題。

  • 事務一致性問題
    通過分佈式事務或者保證最終一致性來解決。
  • 跨節點關聯查詢join問題
    全局表、字段冗餘、數據組裝、ER分片
  • 跨節點分頁、排序、聚集函數問題
    首先在不同分片節點進行查詢,最後要對結果進行匯總或歸並
  • 全局主鍵避重問題
    各種分佈式ID生成算法
  • 數據遷移、擴容問題
    如果是range分片,隻需要添加節點就可以進行擴容瞭。
    如果是hash,一般做法是先讀出歷史數據,然後按指定的分片規則再將數據寫入到各個分片節點中。

數據遷移

數據遷移介紹兩種方案。

一個最low的方案,就是系統停機一段時間,用實現寫好的導數據的工具跑一遍把單獨單表的數據獨出來,寫到分庫分表裡面去。

第二個方案聽起來就比較靠譜瞭,雙寫遷移方案。在線上系統裡,之前所有寫數據的地方,增刪改操作,除瞭對舊庫增刪改,再加上對新庫的增刪改,這就是所謂的雙寫。然後系統部署之後,把方案一裡的導數據工具跑起來,讀老庫寫新庫。寫的時候要根據gmt_modified這類字段判斷這條數據最後修改的時間,除非是讀出來新庫沒有,或是比新庫數據新才會寫。簡單來說就是不允許用老數據覆蓋新數據。

寫完一輪之後,有可能還是存在不一致,那麼就程序自動新一輪校驗,對比新老庫每個表的每條數據,接著如果有不一樣的,就針對那些不一樣的,從老庫讀數據再次寫。反復循環直到數據完全一致。

中間件

分庫分表的中間件比較常見的有:

  • Cobar:阿裡b2b團隊開發和開源的,屬於proxy層方案,介於應用服務器和數據庫服務器之間。應用程序通過JDBC驅動訪問Cobar集群,Cobar根據SQL和分庫規則對SQL做分解,然後分發到MySQL集群不同的數據庫實例上執行。不支持讀寫分離、存儲過程、跨庫join和分頁等操作。最近幾年都沒更新瞭,也沒啥人用瞭。
  • TDDL:淘寶團隊開發的,屬於client層方案。支持基本的crud語法和讀寫分離,但不支持join、多表查詢等語法。目前隻用也不多,因為還依賴淘寶的diamond配置管理系統。
  • Atlas:360開源的,屬於proxy層方案。也是好幾年沒維護,現在用的公司基本也很少瞭。
  • Sharding-jdbc:當當開源的,屬於client層方案,目前已更名為ShardingSphere。SQL語法支持的也比較多,沒有太多限制,支持分庫分表、讀寫分離、分佈式id生成、柔性事務(最大努力送達型事務、TCC事務)。而且使用的公司比較多,社區活躍。
  • Mycat:基於Cobar改造,屬於proxy層方案。支持的功能非常完善。相比Sharding-jdbc來說,年輕一些。

綜上,現在可以考慮使用的就是Sharding-jdbc和Mycat。

Sharding-jdbc這種client層方案的有點在於不用部署,運維成本低,不需要代理層的二次轉發,性能高。缺點是有耦合性。
Mycat這種proxy層方案的缺點在於需要部署,自己運維一套中間件,運維成本高,但是好處在於對項目是透明的。

MySQL分區(不建議使用)

這裡介紹分區主要是防止和切分、分庫分表等概念混淆。
MySQL從5.1版本開始支持分區(partition)的功能。分區指根據一定的規則,數據庫把一個表分解成多個更小的、更容易管理的部分。就訪問數據庫的應用而言,邏輯上隻有一個表或一個索引,但是實際上這個表可能由多個物理分區組成,即對應用是透明的。
MySQL分區引入瞭分區鍵的概念,采取分治法,有利於管理非常大的表。分區鍵用於根據某個區間值、特定值列表或HASH函數執行數據的聚集,讓數據根據規則分佈在不同的分區中。MySQL 5.7中可用的分區類型主要有以下6種:

  • RANGE分區:基於一個給定連續區間范圍,把數據分配到不同的分區。
  • LIST分區:類似RANGE分區,區別在LIST分區是基於枚舉出的值列表分區,RANGE是基於給定的連續區間范圍分區。
  • COLUMNS分區:類似於RANGE和LIST,區別在於分區鍵既可以是多列,又可以是非整數。
  • HASH分區:基於給定的分區個數,把數據取模分配到不同的分區。
  • KEY分區:類似於HASH分區,但使用MySQL提供的哈希函數。
  • 子分區:也叫做復合分區或者組合分區,即在主分區下再做一層分區,將數據再次分割。

這裡舉一LIST分區的例子:

CREATE TABLE orders_list (
  id INT AUTO_INCREMENT,
  customer_surname VARCHAR(30),
  store_id INT,
  salesperson_id INT,
  order_date DATE,
  note VARCHAR(500),
  INDEX idx (id)
) ENGINE = INNODB
  PARTITION BY LIST(store_id) (
  PARTITION p1
  VALUES IN (1, 3, 4, 17)
  INDEX DIRECTORY = '/var/orders/district1'
  DATA DIRECTORY = '/var/orders/district1',
  PARTITION p2
  VALUES IN (2, 12, 14)
  INDEX DIRECTORY = '/var/orders/district2'
  DATA DIRECTORY = '/var/orders/district2',
  PARTITION p3
  VALUES IN (6, 8, 20)
  INDEX DIRECTORY = '/var/orders/district3'
  DATA DIRECTORY = '/var/orders/district3',
  PARTITION p4
  VALUES IN (5, 7, 9, 11, 16)
  INDEX DIRECTORY = '/var/orders/district4'
  DATA DIRECTORY = '/var/orders/district4',
  PARTITION p5
  VALUES IN (10, 13, 15, 18)
  INDEX DIRECTORY = '/var/orders/district5'
  DATA DIRECTORY = '/var/orders/district5'
);

分區的優點:

  • 擴大存儲容量。
  • 優化查詢。在WHERE子句中包含分區條件時可以隻掃描必要的分區來提高查詢效率;同事在涉及SUM()和COUNT()這類聚合函數的查詢時,可以在每個分區上並行處理。
  • 對於已經過期或不需要保存的數據分區,可以通過刪除分區來快速刪除數據。
  • 跨多磁盤來分散查詢數據,獲得更大的查詢吞吐量。

總結

到此這篇關於MySQL分庫分表與分區的文章就介紹到這瞭,更多相關MySQL分庫分表分區內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet!