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

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

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

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

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

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

分區(qū)機(jī)制

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

SELECT 查詢

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

INSERT 操作

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

DELETE 操作

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

UPDATE 操作

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

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

分區(qū)的類型

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

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

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

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

最新評(píng)論