深入瞭解MySQL中分割區表的原理與企業級實戰

2022-11-13 14:01:24

本文導讀

本文詳細講解什麼是分割區表，分割區表增刪改查的工作原理以及分割區表的實戰，分割區表的場景有哪些，哪些場景不建議用分割區表，並列舉出六點使用分割區表的誤區。

一、什麼是分割區表

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

分割區的程式碼實際上是底層表的一組處理程式物件的封裝。分割區表的請求將通過控制程式碼物件（Handler Object）轉換為對儲存引擎的介面呼叫。

因此，分割區完全封裝了SQL層的底層實現，對應用程式是透明的，對SQL層是黑盒的。然而，從底層檔案系統的角度來看，很容易發現每個分割區表都有一個用#分隔的表檔案。

MySQL實現分割區表的方式——封裝底層表——意味著索引也是根據分割區子表定義的，並且沒有全域性索引。

MySQL在建立表時使用 PARTITION BY 子句定義儲存在每個分割區中的資料（在第四節詳細說明）。

在執行查詢時，優化器將根據分割區定義篩選沒有所需資料的分割區。這樣，查詢不需要掃描所有分割區——只需找到包含我們需要的資料的分割區。

分割區的主要目的之一是以更粗的粒度將資料劃分為不同的表。通過這種方式，可以將相關資料儲存在一起。此外，可以方便地批次刪除整個分割區的資料。

二、分割區表的工作原理

1、分割區表增刪改查原理

分割區表上的操作遵循以下操作邏輯：

SELECT

查詢分割區表時，分割區層首先開啟並鎖定所有底層表。優化器首先確定是否可以過濾部分分割區，然後呼叫相應的儲存引擎介面來存取每個分割區的資料。

INSERT

寫入記錄時，分割區層首先開啟並鎖定所有底層表，然後確定哪個分割區接收記錄，然後將記錄寫入相應的底層表。

DELETE

刪除記錄時，分割區層首先開啟並鎖定所有底層表，然後確定與資料對應的分割區，最後刪除相應的底層表。

UPDATE

更新記錄時，分割區層首先開啟並鎖定所有底層表。MySQL首先確定要更新的記錄的分割區，然後取出資料並對其進行更新，然後確定更新後的資料應該放在哪個分割區中，最後寫入基礎表並刪除原始資料所在的基礎表。

2、分割區表工作原理

分割區表由多個相關的底層表實現，這些底層表也由處理程式物件表示，因此我們也可以直接存取每個分割區。

儲存引擎管理分割區的所有基礎表，就像管理公共表一樣（所有基礎表必須使用相同的儲存引擎）。分割區表的索引僅向每個基礎表新增相同的索引。

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

儘管每個操作都將”“首先開啟並鎖定所有基礎表”，但這並不意味著分割區表將在處理期間鎖定整個表。

如果儲存引擎可以自己實現行級鎖，例如InnoDB，它將在分割區層釋放相應的表鎖。這個鎖定和解鎖過程類似於普通InnoDB上的查詢。

我們在第四節詳細說明，使用一些範例來了解在存取分割區表時開啟和鎖定所有基礎表的成本和後果。

三、分割區表使用實戰

1、分割區表企業級實戰

MySQL支援多個分割區表。我們看到的最常見的分割區是基於範圍的。每個分割區儲存一個範圍內的記錄。分割區表示式可以是列或包含列的表示式。

例如，下表按年建立分割區表 # 儲存在不同的分割區中：

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 '建立時間',
	`CREATE_BY` VARCHAR ( 32 ) NOT NULL COMMENT ' 建立人',
	`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 = '***業務明細表' 

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分割區子句中可以使用各種函數。但是，表示式返回的值必須是一個確定的整數，而不是常數。這裡我們使用函數 YEAR() 或任何其他函數。

2、分割區表的使用場景

一、表太大，無法儲存在記憶體中，或者只有表的最後一部分有熱資料，其餘部分是歷史資料。

二、分割區表資料更易於維護。例如，如果要批次刪除大量資料，可以使用清除整個分割區的方法。此外，可以優化、檢查和修復獨立的分割區。

三、分割區表的資料可以分佈在不同的物理裝置上，從而可以有效地使用多個硬體裝置。

四、分割區表可以用來避免一些特殊的瓶頸，例如獨佔存取InnoDB的單個索引和ext3檔案系統的索引節點鎖競爭。

五、備份和恢復獨立的分割區，大的資料集場景。

3、分割區表自身限制

一、一個表最多隻能有1024個分割區。

二、在MySQL 5.1中，分割區表示式必須是整數或返回整數的表示式。在MySOL 5.5中在某些情況下，列可以直接用於分割區。

三、如果分割區欄位中有主鍵列或唯一索引列，則必須包括所有主鍵列和唯一索引列。

四、分割區表中不能使用外來鍵約

4、分割區表的誤區

4.1 效能提升

許多人會認為分割區表將一個大表劃分為多個小表，因此MySQL資料庫的效能將大大提高。

這是錯誤的理解！分割區表技術並不是用來提高MySQL資料庫的效能，而是為了方便資料管理。

分割區表的建立需要主鍵包含分割區列；在分割區表中唯一索引僅在當前分割區檔案唯一，而不是全域性唯一；分割區表唯一索引推薦使用類似 UUID 的全域性唯一實現；

分割區表不解決效能問題，如果使用非分割區列查詢，效能反而會更差；推薦分割區表用於資料管理、速度快、紀錄檔小。

4.2 null值會使分割區過濾無效

檢查第一個分割區，因為 YEAR() 函數在接收非法值時可能返回NULL值，因此該範圍的值可能返回NULL並儲存在第一個分割區中。如果第一個分割區非常大，特別是當使用“完全掃描資料，無索引”策略時，成本將非常高。

4.3 分割區列和索引列不匹配

如果定義的索引列和分割區列不匹配，查詢將無法執行分割區篩選。

4.4 選擇分割區的成本可能更高

不同型別的分割區以不同的方式實現，因此它們的效能不同。當我們在行中寫入大量資料時。每次將一行資料寫入範圍分割區表時，都需要掃描分割區定義列表以找到合適的目標分割區。

這個問題可以通過限制分割區的數量來緩解。根據實際經驗，對於大多數系統，大約100個分割區沒有問題。

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

當查詢和存取分割區表時，MySQL需要開啟並鎖定所有底層表，這是分割區表的另一個成本。

單個操作，例如使用批插入或LOAD DATA INFILE一次刪除多行資料。

4.6 維護分割區的成本可能更高

分割區重組的原理類似於ALTER，首先，建立一個臨時分割區，然後將資料複製到其中，最後刪除原始分割區。這樣會使維護分割區的成本可能更高

到此這篇關於深入瞭解MySQL中分割區表的原理與企業級實戰的文章就介紹到這了,更多相關MySQL分割區表內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！