本文導(dǎo)讀

本文詳細(xì)講解什么是分區(qū)表，分區(qū)表增刪改查的工作原理以及分區(qū)表的實戰(zhàn)，分區(qū)表的場景有哪些，哪些場景不建議用分區(qū)表，并列舉出六點使用分區(qū)表的誤區(qū)。

一、什么是分區(qū)表

分區(qū)表是一個獨立的邏輯表，底層由多個物理子表組成。

分區(qū)的代碼實際上是底層表的一組處理程序?qū)ο蟮姆庋b。分區(qū)表的請求將通過句柄對象（Handler Object）轉(zhuǎn)換為對存儲引擎的接口調(diào)用。

因此，分區(qū)完全封裝了SQL層的底層實現(xiàn)，對應(yīng)用程序是透明的，對SQL層是黑盒的。然而，從底層文件系統(tǒng)的角度來看，很容易發(fā)現(xiàn)每個分區(qū)表都有一個用#分隔的表文件。

MySQL實現(xiàn)分區(qū)表的方式——封裝底層表——意味著索引也是根據(jù)分區(qū)子表定義的，并且沒有全局索引。

MySQL在創(chuàng)建表時使用 PARTITION BY 子句定義存儲在每個分區(qū)中的數(shù)據(jù)（在第四節(jié)詳細(xì)說明）。

在執(zhí)行查詢時，優(yōu)化器將根據(jù)分區(qū)定義篩選沒有所需數(shù)據(jù)的分區(qū)。這樣，查詢不需要掃描所有分區(qū)——只需找到包含我們需要的數(shù)據(jù)的分區(qū)。

分區(qū)的主要目的之一是以更粗的粒度將數(shù)據(jù)劃分為不同的表。通過這種方式，可以將相關(guān)數(shù)據(jù)存儲在一起。此外，可以方便地批量刪除整個分區(qū)的數(shù)據(jù)。

二、分區(qū)表的工作原理

1、分區(qū)表增刪改查原理

分區(qū)表上的操作遵循以下操作邏輯：

SELECT

查詢分區(qū)表時，分區(qū)層首先打開并鎖定所有底層表。優(yōu)化器首先確定是否可以過濾部分分區(qū)，然后調(diào)用相應(yīng)的存儲引擎接口來訪問每個分區(qū)的數(shù)據(jù)。

INSERT

寫入記錄時，分區(qū)層首先打開并鎖定所有底層表，然后確定哪個分區(qū)接收記錄，然后將記錄寫入相應(yīng)的底層表。

DELETE

刪除記錄時，分區(qū)層首先打開并鎖定所有底層表，然后確定與數(shù)據(jù)對應(yīng)的分區(qū)，最后刪除相應(yīng)的底層表。

UPDATE

更新記錄時，分區(qū)層首先打開并鎖定所有底層表。MySQL首先確定要更新的記錄的分區(qū)，然后取出數(shù)據(jù)并對其進(jìn)行更新，然后確定更新后的數(shù)據(jù)應(yīng)該放在哪個分區(qū)中，最后寫入基礎(chǔ)表并刪除原始數(shù)據(jù)所在的基礎(chǔ)表。

2、分區(qū)表工作原理

分區(qū)表由多個相關(guān)的底層表實現(xiàn)，這些底層表也由處理程序?qū)ο蟊硎?，因此我們也可以直接訪問每個分區(qū)。

存儲引擎管理分區(qū)的所有基礎(chǔ)表，就像管理公共表一樣（所有基礎(chǔ)表必須使用相同的存儲引擎）。分區(qū)表的索引僅向每個基礎(chǔ)表添加相同的索引。

從存儲引擎的角度來看，基礎(chǔ)表與公共表沒有區(qū)別，存儲引擎不需要知道它是公共表還是分區(qū)表的一部分。

盡管每個操作都將”“首先打開并鎖定所有基礎(chǔ)表”，但這并不意味著分區(qū)表將在處理期間鎖定整個表。

如果存儲引擎可以自己實現(xiàn)行級鎖，例如InnoDB，它將在分區(qū)層釋放相應(yīng)的表鎖。這個鎖定和解鎖過程類似于普通InnoDB上的查詢。

我們在第四節(jié)詳細(xì)說明，使用一些示例來了解在訪問分區(qū)表時打開和鎖定所有基礎(chǔ)表的成本和后果。

三、分區(qū)表使用實戰(zhàn)

1、分區(qū)表企業(yè)級實戰(zhàn)

MySQL支持多個分區(qū)表。我們看到的最常見的分區(qū)是基于范圍的。每個分區(qū)存儲一個范圍內(nèi)的記錄。分區(qū)表達(dá)式可以是列或包含列的表達(dá)式。

例如，下表 按年創(chuàng)建分區(qū)表 # 存儲在不同的分區(qū)中：

CREATE TABLE `***` (
	`ID` BIGINT ( 20 ) NOT NULL AUTO_INCREMENT COMMENT '主鍵id',
	`LOG_ID` VARCHAR ( 32 ) NOT NULL COMMENT '交易流水號',
	`ODR_ID` VARCHAR ( 32 ) NOT NULL COMMENT '父單號',
	`SUB_ODR_ID` VARCHAR ( 32 ) NOT NULL COMMENT '子單號',
	`CREATE_TIME` datetime ( 0 ) NOT NULL COMMENT '創(chuàng)建時間',
	`CREATE_BY` VARCHAR ( 32 ) NOT NULL COMMENT ' 創(chuàng)建人',
	`UPDATE_TIME` datetime ( 0 ) NOT NULL DEFAULT CURRENT_TIMESTAMP ( 0 ) ON UPDATE CURRENT_TIMESTAMP ( 0 ) COMMENT '更新時間',
	`UPDATE_BY` VARCHAR ( 32 ) NOT NULL COMMENT '更新人',

	PRIMARY KEY ( `ID`, `CREATE_TIME` ) USING BTREE,
	UNIQUE INDEX `UNQ_LOG_SUBODR_ID` ( `LOG_ID`, `SUB_ODR_ID`, `CREATE_TIME` ) USING BTREE,

	INDEX `IDX_ODR_ID` ( `ODR_ID` ) USING BTREE,
	INDEX `IDX_SUB_ID` ( `SUB_ODR_ID` ) USING BTREE,
	INDEX `IDX_CREATE_TIME` ( `CREATE_TIME` ) USING BTREE,
	INDEX `IDX_UPDATE_TIME` ( `UPDATE_TIME` ) USING BTREE 
	) ENGINE = INNODB COMMENT = '***業(yè)務(wù)明細(xì)表' 

PARTITION BY RANGE ( YEAR ( `CREATE_TIME` ) ) (
	PARTITION p_2021 VALUES LESS THAN ( 2021 ),
	PARTITION p_2022 VALUES LESS THAN ( 2022 ),
	PARTITION p_2023 VALUES LESS THAN ( 2023 ),
	PARTITION p_2024 VALUES LESS THAN ( 2024 ),
	PARTITION p_catchall VALUES LESS THAN MAXVALUE )  ;

PARTITION分區(qū)子句中可以使用各種函數(shù)。但是，表達(dá)式返回的值必須是一個確定的整數(shù)，而不是常數(shù)。這里我們使用函數(shù) YEAR() 或任何其他函數(shù)。

2、分區(qū)表的使用場景

一、表太大，無法存儲在內(nèi)存中，或者只有表的最后一部分有熱數(shù)據(jù)，其余部分是歷史數(shù)據(jù)。

二、分區(qū)表數(shù)據(jù)更易于維護(hù)。例如，如果要批量刪除大量數(shù)據(jù)，可以使用清除整個分區(qū)的方法。此外，可以優(yōu)化、檢查和修復(fù)獨立的分區(qū)。

三、分區(qū)表的數(shù)據(jù)可以分布在不同的物理設(shè)備上，從而可以有效地使用多個硬件設(shè)備。

四、分區(qū)表可以用來避免一些特殊的瓶頸，例如獨占訪問InnoDB的單個索引和ext3文件系統(tǒng)的索引節(jié)點鎖競爭。

五、備份和恢復(fù)獨立的分區(qū)，大的數(shù)據(jù)集場景。

3、分區(qū)表自身限制

一、一個表最多只能有1024個分區(qū)。

二、在MySQL 5.1中，分區(qū)表達(dá)式必須是整數(shù)或返回整數(shù)的表達(dá)式。在MySOL 5.5中在某些情況下，列可以直接用于分區(qū)。

三、如果分區(qū)字段中有主鍵列或唯一索引列，則必須包括所有主鍵列和唯一索引列。

四、分區(qū)表中不能使用外鍵約

4、分區(qū)表的誤區(qū)

4.1 性能提升

許多人會認(rèn)為分區(qū)表將一個大表劃分為多個小表，因此MySQL數(shù)據(jù)庫的性能將大大提高。

這是錯誤的理解！分區(qū)表技術(shù)并不是用來提高M(jìn)ySQL數(shù)據(jù)庫的性能，而是為了方便數(shù)據(jù)管理。

分區(qū)表的創(chuàng)建需要主鍵包含分區(qū)列；在分區(qū)表中唯一索引僅在當(dāng)前分區(qū)文件唯一，而不是全局唯一；分區(qū)表唯一索引推薦使用類似 UUID 的全局唯一實現(xiàn)；

分區(qū)表不解決性能問題，如果使用非分區(qū)列查詢，性能反而會更差；推薦分區(qū)表用于數(shù)據(jù)管理、速度快、日志小。

4.2 null值會使分區(qū)過濾無效

檢查第一個分區(qū)，因為 YEAR() 函數(shù)在接收非法值時可能返回NULL值，因此該范圍的值可能返回NULL并存儲在第一個分區(qū)中。如果第一個分區(qū)非常大，特別是當(dāng)使用“完全掃描數(shù)據(jù)，無索引”策略時，成本將非常高。 

4.3 分區(qū)列和索引列不匹配

如果定義的索引列和分區(qū)列不匹配，查詢將無法執(zhí)行分區(qū)篩選。 

4.4 選擇分區(qū)的成本可能更高

不同類型的分區(qū)以不同的方式實現(xiàn)，因此它們的性能不同。當(dāng)我們在行中寫入大量數(shù)據(jù)時。每次將一行數(shù)據(jù)寫入范圍分區(qū)表時，都需要掃描分區(qū)定義列表以找到合適的目標(biāo)分區(qū)。

這個問題可以通過限制分區(qū)的數(shù)量來緩解。根據(jù)實際經(jīng)驗，對于大多數(shù)系統(tǒng)，大約100個分區(qū)沒有問題。

4.5 鎖住所有表的成本可能更高

當(dāng)查詢和訪問分區(qū)表時，MySQL需要打開并鎖定所有底層表，這是分區(qū)表的另一個成本。

單個操作，例如使用批插入或LOAD DATA INFILE一次刪除多行數(shù)據(jù)。

4.6 維護(hù)分區(qū)的成本可能更高

分區(qū)重組的原理類似于ALTER，首先，創(chuàng)建一個臨時分區(qū)，然后將數(shù)據(jù)復(fù)制到其中，最后刪除原始分區(qū)。這樣會使維護(hù)分區(qū)的成本可能更高

到此這篇關(guān)于深入了解MySQL中分區(qū)表的原理與企業(yè)級實戰(zhàn)的文章就介紹到這了,更多相關(guān)MySQL分區(qū)表內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論