數據庫表分區會提升插入效率嗎

本篇文章給大家分享的是有關數據庫表分區會提升插入效率嗎，小編覺得挺實用的，因此分享給大家學習，希望大家閱讀完這篇文章后可以有所收獲，話不多說，跟著小編一起來看看吧。

數據庫表分區可提升插入效率；數據庫表分區可提高表增刪改查的效率。數據庫表分區進行插入操作的原理：當寫入一條記錄時，分區層打開并鎖住所有的底層表，然后確定哪個分區接受這條記錄，再將記錄寫入對應的底層表。

什么是分區？

分區是按照規則分解表，將數據分段劃分在多個位置存放，可以是同一塊磁盤也可以在不同的機器。分區后，表面上還是一張表，但數據散列到多個位置了。app讀寫的時候操作的還是大表名字，db自動去組織分區的數據。

分區又可以分為兩種：

1、水平分區（Horizontal Partitioning）

這種形式分區是對表的行進行分區，通過這樣的方式不同分組里面的物理列分割的數據集得以組合，從而進行個體分割（單分區）或集體分割（1個或多個分區）。所有在表中定義的列在每個數據集中都能找到，

所以表的特性依然得以保持。

舉個簡單例子：一個包含十年發票記錄的表可以被分區為十個不同的分區，每個分區包含的是其中一年的記錄。（注：這里具體使用的分區方式我們后面再說，可以先說一點，一定要通過某個屬性列來分割，譬如這里使用的列就是年份）

2、垂直分區（Vertical Partitioning）

這種分區方式一般來說是通過對表的垂直劃分來減少目標表的寬度，使某些特定的列被劃分到特定的分區，每個分區都包含了其中的列所對應的行。

舉個簡單例子：一個包含了大text和BLOB列的表，這些text和BLOB列又不經常被訪問，這時候就要把這些不經常使用的text和BLOB了劃分到另一個分區，在保證它們數據相關性的同時還能提高訪問速度。

分區表的原理

分區表是由多個相關的底層表實現，這些底層表也是由句柄對象表示，所以我們也可以直接訪問各個分區，存儲引擎管理分區的各個底層表和管理普通表一樣（所有的底層表都必須使用相同的存儲引擎），分區表的索引只是在各個底層表上各自加上一個相同的索引，從存儲引擎的角度來看，底層表和一個普通表沒有任何不同，存儲引擎也無須知道這是一個普通表還是一個分區表的一部分。

在分區表上的操作按照下面的操作邏輯進行：

select查詢：

當查詢一個分區表的時候，分區層先打開并鎖住所有的底層表，優化器判斷是否可以過濾部分分區，然后再調用對應的存儲引擎接口訪問各個分區的數據

insert操作：

當寫入一條記錄時，分區層打開并鎖住所有的底層表，然后確定哪個分區接受這條記錄，再將記錄寫入對應的底層表

delete操作：

當刪除一條記錄時，分區層先打開并鎖住所有的底層表，然后確定數據對應的分區，最后對相應底層表進行刪除操作

update操作：

當更新一條數據時，分區層先打開并鎖住所有的底層表，mysql先確定需要更新的記錄在哪個分區，然后取出數據并更新，再判斷更新后的數據應該放在哪個分區，然后對底層表進行寫入操作，并對原數據所在的底層表進行刪除操作

雖然每個操作都會打開并鎖住所有的底層表，但這并不是說分區表在處理過程中是鎖住全表的，如果存儲引擎能夠自己實現行級鎖，如：innodb，則會在分區層釋放對應的表鎖，這個加鎖和解鎖過程與普通Innodb上的查詢類似。

在下面的場景中，分區可以起到非常大的作用：

A：表非常大以至于無法全部都放在內存中，或者只在表的最后部分有熱點數據，其他都是歷史數據

B：分區表的數據更容易維護，如：想批量刪除大量數據可以使用清除整個分區的方式。另外，還可以對一個獨立分區進行優化、檢查、修復等操作

C：分區表的數據可以分布在不同的物理設備上，從而高效地利用多個硬件設備

D：可以使用分區表來避免某些特殊的瓶頸，如：innodb的單個索引的互斥訪問，ext3文件系統的inode鎖競爭等

E：如果需要，還可以備份和恢復獨立的分區，這在非常大的數據集的場景下效果非常好

F：優化查詢，在where字句中包含分區列時，可以只使用必要的分區來提高查詢效率，同時在涉及sum()和count()這類聚合函數的查詢時，可以在每個分區上面并行處理，最終只需要匯總所有分區得到的結果。

mysql數據庫的分區總是把null當作比任何非null更小的值，這和數據庫中處理null值的order by操作是一樣的，升序排序時null總是在最前面，因此對于不同的分區類型，mysql數據庫對于null的處理也各不相同。

對于range分區，如果向分區列插入了null，則mysql數據庫會將該值放入最左邊的分區，注意，如果刪除分區，分區下的所有內容都從磁盤中刪掉了，null所在分區被刪除，null值也就跟著被刪除了。

在list分區下要使用null，則必須顯式地定義在分區的散列值中，否則插入null時會報錯。hash和key分區對于null的處理方式和range,list分區不一樣，任何分區函數都會將null返回為0.

分區

分區就是將數據庫或其構成元素劃分為不同的獨立部分

－－是一種預先組織表存儲的方法

mysql支持水平分區

將特定表行分配為行的子集

分區的分布是跨物理存儲進行的

－－根據用戶在需要時設置的指定規則

－－每個分區存儲為其自己的單元

數據的劃分

－－根據分區功能將數據劃分為子集

－－分區類型和表達式是表定義的一部分

－－表達式可以是整數或返回整數值的函數。

－－此值根據定義確定將每條記錄存儲在哪個分區中

1.primary key和unique key必須包含在分區key的一部分，否則在創建primary key和unique index時會報”ERROR 1503 (HY000)“

2.范圍分區添加分區只能在最大值后面追加分區

3.所有分區的engine必須一樣

4.范圍分區分區字段：integer、數值表達式、日期列，日期函數表達式(如year(),to_days(),to_seconds(),unix_timestamp())

分區管理

新增分區

ALTER TABLE sale_data
ADD PARTITION (PARTITION p201010 VALUES LESS THAN (201011));

刪除分區

–當刪除了一個分區，也同時刪除了該分區中所有的數據。

ALTER TABLE sale_data DROP PARTITION p201010;

分區的合并

下面的SQL，將p201001 – p201009 合并為3個分區p2010Q1 – p2010Q3

ALTER TABLE sale_data
REORGANIZE PARTITION p201001,p201002,p201003,
p201004,p201005,p201006,
p201007,p201008,p201009 INTO
(
PARTITION p2010Q1 VALUES LESS THAN (201004),
PARTITION p2010Q2 VALUES LESS THAN (201007),
PARTITION p2010Q3 VALUES LESS THAN (201010)
);

以上就是數據庫表分區會提升插入效率嗎，小編相信有部分知識點可能是我們日常工作會見到或用到的。希望你能通過這篇文章學到更多知識。更多詳情敬請關注億速云行業資訊頻道。