30-27之資料庫層的擴展 - 分區表
it 鐵人賽 2019 mysql
Lastmod: 2019-12-15

正文開始

上一篇文章中,咱們有提到了兩種資料庫層的擴展方式 :

  • 分庫
  • 分表

其中分表是用來解決單表太大的問題,而接下來本章節要來介紹另一種處理單表太的工具 :

分區表

本篇文章分以下幾個章節 :

  • 分區概念
  • MySQL 分區的切分類型
  • 分區使用的注意事項

分區概念

分區表的核心概念為 :

將一張大表,根據『 規則 』拆分為『 隱藏 』的小表

觀念和分表事實上完全相同,就差在『 隱藏 』這個字詞上。它們的差異如下

  • 分表 : 分表後,應用層需要修改 sql 操作位置,指定到對應的分表上。
  • 分區 : 分區後,應用層『 不 』需要修改 sql 操作位置,資料庫層會自動幫你處理。

也就是說假設是使用 type 這個欄位來『 分表 』那你在查詢時可能需為變成如下指令 :

user 根據 type 拆分成三個表 :
1. user_type_A 表
2. user_type_B 表
3. user_type_C 長

SELECT * FROM user_type_A WHERE name = 'mark';

而如果是『 分區表 』的話為 :

SELECT * FROM user WHERE name = 'mark';

這裡問個問題 ~ 那實務上要使用分表還是分區呢 ?

首先這兩個沒有互相排斥,兩個可以一起使用,但如果是要先選一個來用的話,要選那個呢 ? 目前我是覺得『 分區 』比較簡單實現,但是它在使用上會比較需要『 使用者 』注意,一個用不好,性能反而會下降。

而『 分表 』某些方面來說彈性較大,可以做的業務較多,但相對的除非有一個很完美的中間件團隊,不然坑如果直接各別讓『 使用者 』直接用資料庫,會天下大亂的。

~ 小知識 ~

分區表這個東西在不少資料庫上都有提供像 mysql、ms-sql、postgresql 等都有支援。

分表區的優點

分區有以下幾個好處 :

  • 減少修改時對索引樹的維護 : 因為越大的樹越難維護,所以切小後,每顆樹就好維護多了。
  • 減少索引碎片處理 : 只要是有進行修改,就有可能會讓索引樹一個洞一個洞的,而通常會下達指令來整理這顆樹,但是這很耗效能,而且如果樹越大,資源吃越多。
  • 增加查詢效率 : 越大的樹查詢效率一定越小,而不過切小後,『 只對 』那顆小樹查那就快多了。
  • 增加表的維護性 : 假設咱們要針對大表中某一段時間砍資料,那這時如果每張表是以時間來分的話,那就只要直接砍掉小表,這樣也不會影響到其它小表的查詢。

上面看似美好。

但分區表不是銀彈

後面章節會說明它的坑處。

MySQL 分區的切分類型

mysql 中分區表提供了以下的類型,然後咱們指定分區的那個欄位都稱為 :

分區鍵

分區鍵的選擇要點提醒

那個欄位會變成每一次查詢時,都會要用的

類型 1 : RANGE 分區

假設咱們由一張儲員工的資料表,那咱們可以選擇以『 RANGE 』來分區,通常這種分區欄位比較常見的有日期或啥有順序的編號之類。

CREATE TABLE users (
    id INT NOT NULL,
    age INT NOT NULL,
)
PARTITION BY RANGE (age) (
    PARTITION p0 VALUES LESS THAN (20),
    PARTITION p1 VALUES LESS THAN (30),
    PARTITION p2 VALUES LESS THAN (40),
    PARTITION p3 VALUES LESS THAN (50)
);

假設咱們有以下的員工 :

A : age = 18
B : age = 25
C : age = 35
D : age = 45
E : age = 15

那這樣上述資料會分別放在不同的分區表中 :

p0 : A,E
p1 : B
p2 : C
p 3 : D

類型 2 : LIST 分區

list 這種分區通常是用在枚舉類型上,如下範例,咱們有一個 type 欄位,然後使用數字來代表類型,然後就使用這個類型來決定分區。

CREATE TABLE users (
    id INT NOT NULL,
    age INT NOT NULL,
    type INT NOT NULL
)
PARTITION BY list (type) (
    PARTITION p0 VALUES LESS THAN (0,1),
    PARTITION p1 VALUES LESS THAN (2),
);

假設咱們有以下的員工 :

A : age = 18, type : 0
B : age = 25, type : 1
C : age = 35, type : 2
D : age = 45, type : 0
E : age = 15, type : 1

那這樣上述資料會分別放在不同的分區表中 :

p0 : A,B,D,E
p1 : E

類型 3 : Hash 分區

它就是會直接將 id 進行 hash 後分成四個區。

CREATE TABLE users (
    id INT NOT NULL,
    age INT NOT NULL,
    type INT NOT NULL
)
PARTITION BY HASH(id)
PARTITIONS 4;

類型 4 : KEY 分區

它就是會直接根據指定的 pk 來分區。

CREATE TABLE users (
    id INT NOT NULL PRIMARY KEY, ,
    age INT NOT NULL,
    type INT NOT NULL
)
PARTITION BY KEY()
PARTITIONS 4;

分區使用的注意事項


注意 1 : 查詢時一定有用到分區鍵,不然還是會每個分區找

假設咱們有以下的分區表。

CREATE TABLE users (
    id INT NOT NULL PRIMARY KEY, ,
    age INT NOT NULL,
    type INT NOT NULL
)
PARTITION BY KEY()
PARTITIONS 4;

然後咱們下達以下的指令進行 query :

EXPLAIN SELECT * FROM users WHERE age = 10;

那你會看到以下的結果如下 explain 的結果,你會發現它去每一個分區都找過。

partitions 
p0,p1,p2,p2

而如果改成 :

EXPLAIN SELECT * FROM users WHERE id = 1;

結果如下,就有使用到分區表。

partitions 
p0

注意 2 : 沒有使用到分區表查詢會慢

分區別是為了解決單表太大,導致操作太慢所進行的一種解法,以 b+ 樹的概念來看,就是將一顆大樹分成幾個小樹。

但是這有一個問題,如果你的查詢沒有用到分區,那也就代表你需要去每個小樹找,那這樣整體而言 i/o 的次樹會大於一顆大樹。

mysql innodb 是使用 b+ 樹來建立資料,在資料庫中基本上樹的高度大約為 2 至 3,不論資料的多與少,這也代表你分區以後,那幾顆分區後出來的 b+ 樹高度也是會差不多的。

所以有可能大表查詢需要 3 次 i/o,但變到小表可能就只需要 2 次 i/o,但是如果沒有指定分區查詢的話,假設有 4 個小表,那就代表要 2 * 4 = 8 次 i/o 查詢,這樣反而變慢了。

所以這裡記好 :

查詢時一定要包含分區鍵來查詢,沒用到會很慢

注意 3 : NULL 的問題

如果你的分區鍵中,所對應的表格是 null 的話,那他會如何處理呢 ?

它會都儲在第一個分區

所以有時後不知覺第一個分區查詢會非常慢就是這個原因。

注意 4 : 索引的使用問題

分區鍵與索引鍵不相同時

假設咱們有一張表,它們的分區與索引鍵如下 :

欄位 a 與 b

分區鍵 : a
索引鍵 : b

那你想想,如果這個時後下以下的 sql 會如何呢 ? 它基本上會去每一個分區表中的每個顆 b+ 樹去找一次,就算有使用索引 b 仍然會非常的慢,尤其如果是沒有用到『 覆蓋索引 』。

SELECT * FROM table WHERE b = 1;

所以這裡建議,任何查詢一定要配分區鍵

SELECT * FROM table WHERE b = 1 AND a = x

~ 小備註 ~

上面有提到覆蓋索引這東西,如果還不知道的友人們,可以參考看看筆者這篇文章。

30-12 之資料庫層的核心 - MySQL 的索引實現

注意 5 : 在 Range 類型 INSERT 時的問題

在範例類型的分區表的操作時,insert 實際上是進行以下事情 :

分區層鎖住所有分區表,然後決定它是在那,再將它丟入那個分區

事實上這個操作非常的消耗性能,所以基本會建議 range 不要設太多分區。

注意 6 : 記得常用 Explain 來看看有沒有分區

注意 explain 中的 partiation 欄位,如果是全部的分區都有掃到,那就要考慮改一下你的語法。

id: 1
select_type: simple
table: user
partiation: p1

最後的注意 : 分區鍵的選擇非常重要

你只要記好,你只有一次機會選擇它,上線已後要改,會出人命的。

結論與心得

本篇文章中,咱們介紹了淺談了資料庫分區表的概念,它的概念事實上和咱們上一章節介紹的分表非常的相似,但是還是有點不同。

  • 分表 : 分表後,應用層需要修改 sql 操作位置,指定到對應的分表上。
  • 分區 : 分區後,應用層『 不 』需要修改 sql 操作位置,資料庫層會自動幫你處理。

然後這裡說一下,並不是所有表太大,都需要使用分表或分區這兩個擴展,很多情況下,事實上不需要所有的資料都需要存放在資料庫中,這些資料事實上可以考慮轉移至 s3 或啥的,唯有當資料一定要存在資料庫中,且都需要查詢的情況,才可考慮。

不然分表與分區後,應用端操作是個雷,資料庫端也是個雷。

最後在提醒一下分區與分表是用在 :

單表太大的情境

而不是資料庫太大的情境,我真看過有人不管表大不大,一律都分區這樣玩。

參考資料

comments powered by Disqus