MySQL高級特性——數據表分區的概念及機制詳解

Posted on by

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

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

分區的主要目的是對數據表進行大致形式的索引和聚集。這樣可以減少數據表的過大范圍的訪問，並可以將相關的數據行臨近存儲。分區的收益是顯著的，尤其是對於下面的場景：

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

MySQL 的分區實現細節十分復雜，弄清楚是很難的，我們隻需要關註它的性能即可。如果想進一步瞭解，可以翻閱 MySQL 手冊中關於分區的部分。有瞭分區後，也帶來瞭其他問題以及限制：

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

分區機制

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

SELECT 查詢

當對分區表進行查詢時，分區層會打開和鎖定全部的隱藏分區，查詢優化器會決定那些隱藏分區可以忽略，然後分區層通過句柄 API 調用管理分區的存儲引擎獲取查詢結果。

INSERT 操作

當插入一行數據時，分區層會打開和鎖定全部分區，然後決定那個分區存儲當前的數據行，並將該數據行存入對應分區。

DELETE 操作

刪除一行數據時，分區層會打開和鎖定全部分區，檢查哪個分區包含該行數據，再將刪除請求發送到該分區。

UPDATE 操作

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

雖然分區層打開和鎖定瞭全部分區，但並不意味著分區會保持鎖定。像 InnoDB 的存儲引擎，可以支持行級別的鎖定，會隻是分區層解除分區的鎖定。這個加鎖和解鎖的過程和普通的 InnoDB 數據表的鎖定過程類似。

分區的類型

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

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);

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

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

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

以上就是MySQL高級特性——數據表分區的概念及機制詳解的詳細內容，更多關於MySQL高級特性 數據表分區的資料請關註WalkonNet其它相關文章！

推薦閱讀：