本文導(dǎo)讀

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

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

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

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

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

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

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

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

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

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

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

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

SELECT

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

INSERT

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

DELETE

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

UPDATE

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

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

分區(qū)表由多個(gè)相關(guān)的底層表實(shí)現(xiàn)，這些底層表也由處理程序?qū)ο蟊硎荆虼宋覀円部梢灾苯釉L問(wèn)每個(gè)分區(qū)。

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

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

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

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

我們?cè)诘谒墓?jié)詳細(xì)說(shuō)明，使用一些示例來(lái)了解在訪問(wèn)分區(qū)表時(shí)打開和鎖定所有基礎(chǔ)表的成本和后果。

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

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

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

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

CREATE TABLE `***` (
	`ID` BIGINT ( 20 ) NOT NULL AUTO_INCREMENT COMMENT '主鍵id',
	`LOG_ID` VARCHAR ( 32 ) NOT NULL COMMENT '交易流水號(hào)',
	`ODR_ID` VARCHAR ( 32 ) NOT NULL COMMENT '父單號(hào)',
	`SUB_ODR_ID` VARCHAR ( 32 ) NOT NULL COMMENT '子單號(hào)',
	`CREATE_TIME` datetime ( 0 ) NOT NULL COMMENT '創(chuàng)建時(shí)間',
	`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 '更新時(shí)間',
	`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á)式返回的值必須是一個(gè)確定的整數(shù)，而不是常數(shù)。這里我們使用函數(shù) YEAR() 或任何其他函數(shù)。

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

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

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

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

四、分區(qū)表可以用來(lái)避免一些特殊的瓶頸，例如獨(dú)占訪問(wèn)InnoDB的單個(gè)索引和ext3文件系統(tǒng)的索引節(jié)點(diǎn)鎖競(jìng)爭(zhēng)。

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

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

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

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

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

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

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

4.1 性能提升

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

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

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

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

4.2 null值會(huì)使分區(qū)過(guò)濾無(wú)效

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

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

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

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

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

這個(gè)問(wèn)題可以通過(guò)限制分區(qū)的數(shù)量來(lái)緩解。根據(jù)實(shí)際經(jīng)驗(yàn)，對(duì)于大多數(shù)系統(tǒng)，大約100個(gè)分區(qū)沒有問(wèn)題。

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

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

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

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

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

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

最新評(píng)論