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

更新時間：2021年05月17日 10:30:35 作者：島上碼農

當數據表過大時，通過普通的查詢優(yōu)化技巧已經無法大幅度提升性能，此時往往需要進行分區(qū)分表優(yōu)化。分區(qū)其實是將一張邏輯上統(tǒng)一的表在物理上劃分成了多張表。分區(qū)操作對使用者而言是一個黑盒操作，但是如果你從文件系統(tǒng)上看，就會看到分區(qū)數據表的實際存儲方式是分開的。

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

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

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

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

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

分區(qū)機制

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

SELECT 查詢

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

INSERT 操作

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

DELETE 操作

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

UPDATE 操作

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

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

分區(qū)的類型

MySQL 支持幾種類型的分區(qū)，最常用的類型是范圍分區(qū)——也就是針對某些列的的值或表達式按不同的范圍進行分區(qū)。例如，下面的語句就是根據年份將銷售數據分到不同的分區(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ū)子句中使用多種函數。最主要的要求是必須返回一個非常量的，確定的整數。在上面的例子中使用的是 YEAR 函數，也可以使用其他函數，例如 TO_DAYS()。使用時間間隔進行分區(qū)是基于日期數據的常用方式。

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

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

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

您可能感興趣的文章: