分庫分表 Sharding & Partition - 2

12th鐵人賽 mysql

雷N

2020-10-08 00:30:30

2316 瀏覽

分享至

昨天講了垂直分庫, 來分享垂直分表跟其它?

垂直分表 Vertical Partition

把一個表的欄位, 可能有常用的欄位跟很不常用的欄位給分開成表來存放.

如果一個部落格平台, 文章的表欄位有

CREATE TABLE `posts` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(100) NOT NULL,
  `author_id` bigint(20) unsigned NOT NULL,
  `body` blob,
  `tag_id` smallint(5) unsigned DEFAULT NULL,
  `created_date` date DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_posts_author_id` (`author_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

這些欄位, 這裡分兩個情境;
一個是文章列表, 一個是特定文章的全部內容詳情.
通常在文章列表, 查詢條件是tag、author或是時間範圍.

特定文章的全部內容詳情則是有給定某一個post的id.
可是內容詳情其實往往內容很多, 且可能帶有圖片或是影片等二進制非結構化的檔案.

如果該表是這樣設計, MySQL通常會將這Body(blob)欄位放在extrenal pages裡面

參考externally-stored-fields-in-innodb
這樣其實每次查找都會去page撈內容, 會有IO爭搶問題, 且未必情境是需要的.

所以垂直分表就是把欄位做分表, 這裡能拆成

CREATE TABLE `posts` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(100) NOT NULL,
  `author_id` bigint(20) unsigned NOT NULL,
  `tag_id` smallint(5) unsigned DEFAULT NULL,
  `created_date` date DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_posts_author_id` (`author_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `post_body` (
  `post_id` bigint(20) unsigned NOT NULL,
  `body` blob NOT NULL,
  PRIMARY KEY (`post_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

平時API根據條件撈出列表頁需要的內容, 當選好文章時, 把id往回帶到post_body做查詢.

通常按照這些原則做垂直拆分

把Blob、Text這些大的欄位類型拆分到另一個表
欄位非常多, 但有些很少用到的欄位也能考慮拆分(冷熱分離)

把熱點欄位的數據放在一起, 減少隨機IO的機率(循序IO跟隨機IO的成本差異非常大),
甚至也能做成快取.

水平分表 Horizontal Partitioning

水平分表就是把原本在單個庫單個業務表拆分成n個邏輯相關的業務子表(ex: tab_0000, tab_0001...), 不同的業務子表各自儲存不同區間的資料, 對外則是形成一個整體.

這個策略其實比較少用, 因為單機併發量TPS還是上不來, 只是優化了單一表資料量過大的問題, 以及減少單表的IO爭搶(因為能不同表放不同顆硬碟).

水平切分的策略

Time Range: 一定時間區間的資料放到一張表內; 按天分表, 按月分表; 用時間跟規則就能計算在哪個分表或是分庫上
ID Range: 以一個量為分界線, 對線性遞增的ID做切割; 假設10萬為分界, 每10萬就分到下一張表, 子表ID == ID / 10萬 % 100 , 比如id=999 會對到表的tb_00, id=19萬則是對到表的tb_01
Mod ID: ID % 分表/分片數量; 分4表, 則id=3, 會落在tb_01上; 但一旦分表數量變更了會需要做數據搬遷.
Hash % Mod : 把欄位透過Hash再Mod取模, 算出在哪裡; 假設Hash fun(x) = x+1, 分4表, 則id=3, 會落在tb_01; 但是跟第二個方法有一樣的問題
Mapping Table : 直接把該id落在哪個分表上, 給紀錄在另一個表或是NoSQL上; 基本上不太推薦, 因為就是想要一次查詢就命中, 這樣反而會多一次的查詢, 違背原本的初衷.

水平分庫 Horizontal Sharding

如果Master的TPS過高, 還是能依照水平分表的思路. 把單一業務庫進行水平化.
把業務子表按照特定演算法或規則分散到n個邏輯相關的業務子庫中(ex: db_0000, db_0001...).
通常都需要專門的Sharding中間件來負責數據的路由工作.
通常還是會有一組1對1的從庫, 但懶得畫了.