MySQL高級特性——數(shù)據(jù)表分區(qū)的概念及機制詳解

MySQL 的分區(qū)的實現(xiàn)方式是對數(shù)據(jù)表進行一層包裝，這意味著索引實際是基于每個分區(qū)定義的，而不是整張表。這個特性和 Oracle 是不同的，在 Oracle 中的索引和數(shù)據(jù)表可以使用更靈活和更復(fù)雜的方式進行分區(qū)。​

MySQL 的分區(qū)通過定義 PATITION BY 子句的條件來決定數(shù)據(jù)行所屬分區(qū)的歸屬。在執(zhí)行查詢的時候，查詢優(yōu)化器會區(qū)分所在分區(qū)，這意味著查詢不會檢查全部分區(qū)，而僅僅是那些包含索要查詢數(shù)據(jù)所在的分區(qū)。​

分區(qū)的主要目的是對數(shù)據(jù)表進行大致形式的索引和聚集。這樣可以減少數(shù)據(jù)表的過大范圍的訪問，并可以將相關(guān)的數(shù)據(jù)行臨近存儲。分區(qū)的收益是顯著的，尤其是對于下面的場景：

• 當(dāng)數(shù)據(jù)表過大導(dǎo)致內(nèi)存空間難以承載時，或者一張數(shù)據(jù)表中有很多歷史數(shù)據(jù)以及熱區(qū)行。
• 分區(qū)數(shù)據(jù)相比為分區(qū)數(shù)據(jù)更容易維護。例如，通過刪除整個分區(qū)很容易將舊的數(shù)據(jù)清除，同時對于單個分區(qū)也可以很方便地進行優(yōu)化、檢查和修復(fù)操作。
• 分區(qū)數(shù)據(jù)在物理上可以分布存儲，這使得服務(wù)器可以更高效地使用多個硬盤驅(qū)動器。
• 可以使用分區(qū)避免某些工作負荷的瓶頸。
• 對于數(shù)據(jù)備份而言，可以單獨備份或恢復(fù)單個分區(qū)，這對于大的數(shù)據(jù)集來說十分有益。
  

MySQL 的分區(qū)實現(xiàn)細節(jié)十分復(fù)雜，弄清楚是很難的，我們只需要關(guān)注它的性能即可。如果想進一步了解，可以翻閱 MySQL 手冊中關(guān)于分區(qū)的部分。有了分區(qū)后，也帶來了其他問題以及限制：

• 創(chuàng)建表和更改表的命令更為復(fù)雜。
• 每張表最多只能有1024個分區(qū)。
• 在 MySQL 5.1版本中，分區(qū)表達式必須是整數(shù)或者返回一個整數(shù)；在 MySQL 5.5以后，在某些情況下可以使用列進行分區(qū)。
• 任主鍵或唯一索引都必須包含分區(qū)表達式中的全部列。
• 不可以使用外鍵約束。
  

分區(qū)機制

如前所述，分區(qū)表實際有多個隱藏的物理存儲表，這通過句柄對象呈現(xiàn)。我們不能直接訪問分區(qū)。通常，每個分區(qū)是通過存儲引擎管理的（因此要求所有分區(qū)的存儲引擎相同），而數(shù)據(jù)表中的索引實際上是隱藏的物理存儲表的索引。從存儲引擎的角度來看，分區(qū)也是數(shù)據(jù)表。存儲引擎實際并不知道數(shù)據(jù)表是獨立的還是一個大的數(shù)據(jù)表的一個分區(qū)。對于分區(qū)表的操作通過如下的邏輯操作實現(xiàn)：

SELECT 查詢

當(dāng)對分區(qū)表進行查詢時，分區(qū)層會打開和鎖定全部的隱藏分區(qū)，查詢優(yōu)化器會決定那些隱藏分區(qū)可以忽略，然后分區(qū)層通過句柄 API 調(diào)用管理分區(qū)的存儲引擎獲取查詢結(jié)果。

INSERT 操作

當(dāng)插入一行數(shù)據(jù)時，分區(qū)層會打開和鎖定全部分區(qū)，然后決定那個分區(qū)存儲當(dāng)前的數(shù)據(jù)行，并將該數(shù)據(jù)行存入對應(yīng)分區(qū)。

DELETE 操作

刪除一行數(shù)據(jù)時，分區(qū)層會打開和鎖定全部分區(qū)，檢查哪個分區(qū)包含該行數(shù)據(jù)，再將刪除請求發(fā)送到該分區(qū)。

UPDATE 操作

修改一行數(shù)據(jù)時，分區(qū)層打開和鎖定所有分區(qū)，檢查哪個分區(qū)包含該行數(shù)據(jù)，并獲取該行數(shù)據(jù)進行修改，然后再確定哪個分區(qū)應(yīng)當(dāng)包含新的數(shù)據(jù)行，并把插入請求發(fā)送到該分區(qū)，同時發(fā)送刪除請求到舊的分區(qū)。
上面的有些操作支持分區(qū)過濾（即忽略無關(guān)的分區(qū)）。例如，刪除一行時，服務(wù)器需要首先定位數(shù)據(jù)行位置。如果在 WHERE 條件中指定了匹配的分區(qū)表達式條件，服務(wù)器可以忽略掉不包含該行的分區(qū)。對于 UPDATE 操作也是類似，而 INSERT 操作本身就是如此，服務(wù)器會只查找需要插入的一個分區(qū)，而不是全部。

雖然分區(qū)層打開和鎖定了全部分區(qū)，但并不意味著分區(qū)會保持鎖定。像 InnoDB 的存儲引擎，可以支持行級別的鎖定，會只是分區(qū)層解除分區(qū)的鎖定。這個加鎖和解鎖的過程和普通的 InnoDB 數(shù)據(jù)表的鎖定過程類似。

分區(qū)的類型

MySQL 支持幾種類型的分區(qū)，最常用的類型是范圍分區(qū)——也就是針對某些列的的值或表達式按不同的范圍進行分區(qū)。例如，下面的語句就是根據(jù)年份將銷售數(shù)據(jù)分到不同的分區(qū)中：

CREATE TABLE sales (
  order_date DATETIME NOT NULL
  --其他列定義
) ENGINE=InnoDB PARTITION BY RANGE(YEAR(order_date)) (
  PARTITION p_2018 VALUES LESS THAN (2018),
  PARTITION p_2019 VALUES LESS THAN (2019),
  PARTITION p_2020 VALUES LESS THAN (2020),
  PARTITION p_other VALUES LESS THAN MAXVALUE);

可以在分區(qū)子句中使用多種函數(shù)。最主要的要求是必須返回一個非常量的，確定的整數(shù)。在上面的例子中使用的是 YEAR 函數(shù)，也可以使用其他函數(shù)，例如 TO_DAYS()。使用時間間隔進行分區(qū)是基于日期數(shù)據(jù)的常用方式。​

MySQL 也支持鍵，哈希以及列表的分區(qū)方法，有些還支持子分區(qū)（實際很少用）。在 MySQL 5.5以后，可u一使用 RANGE COLUMNS 的分區(qū)類型直接按基于日期的列進行分區(qū)，而不需要使用函數(shù)將日期轉(zhuǎn)換為整數(shù)。 其他常見的分區(qū)技巧包括：

• 使用鍵進行分區(qū)以減少 InnoDB 的互斥量的競爭；
• 可以使用取余計算的方法來循環(huán)構(gòu)建范圍的分區(qū)，例如如果只需要保持最近幾天的數(shù)據(jù)，可以通過對日期對7取余，或者使用所在的周天數(shù)進行分區(qū)。
• 假設(shè)數(shù)據(jù)表沒有自增的主鍵，但是也想對聚集在一起的熱區(qū)數(shù)據(jù)分區(qū)。由于時間戳不在主鍵里，也無法使用時間戳分區(qū)。這時候可以使用 HASH(id DIV 1000000)，這會在每1000000行數(shù)據(jù)進行分區(qū)。這使得無需更改主鍵也能完成我們要的效果。同時這樣還有附加的效果。那就是我們無需創(chuàng)建分區(qū)的常量去保留新的數(shù)據(jù)。
  

以上就是MySQL高級特性——數(shù)據(jù)表分區(qū)的概念及機制詳解的詳細內(nèi)容，更多關(guān)于MySQL高級特性 數(shù)據(jù)表分區(qū)的資料請關(guān)注腳本之家其它相關(guān)文章！