數(shù)據(jù)庫表分區(qū)會(huì)提升插入效率嗎

本篇文章給大家分享的是有關(guān)數(shù)據(jù)庫表分區(qū)會(huì)提升插入效率嗎，小編覺得挺實(shí)用的，因此分享給大家學(xué)習(xí)，希望大家閱讀完這篇文章后可以有所收獲，話不多說，跟著小編一起來看看吧。

數(shù)據(jù)庫表分區(qū)可提升插入效率；數(shù)據(jù)庫表分區(qū)可提高表增刪改查的效率。數(shù)據(jù)庫表分區(qū)進(jìn)行插入操作的原理：當(dāng)寫入一條記錄時(shí)，分區(qū)層打開并鎖住所有的底層表，然后確定哪個(gè)分區(qū)接受這條記錄，再將記錄寫入對應(yīng)的底層表。

什么是分區(qū)？

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

分區(qū)又可以分為兩種：

1、水平分區(qū)（Horizontal Partitioning）

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

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

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

2、垂直分區(qū)（Vertical Partitioning）

這種分區(qū)方式一般來說是通過對表的垂直劃分來減少目標(biāo)表的寬度，使某些特定的列被劃分到特定的分區(qū)，每個(gè)分區(qū)都包含了其中的列所對應(yīng)的行。

舉個(gè)簡單例子：一個(gè)包含了大text和BLOB列的表，這些text和BLOB列又不經(jīng)常被訪問，這時(shí)候就要把這些不經(jīng)常使用的text和BLOB了劃分到另一個(gè)分區(qū)，在保證它們數(shù)據(jù)相關(guān)性的同時(shí)還能提高訪問速度。

分區(qū)表的原理

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

在分區(qū)表上的操作按照下面的操作邏輯進(jìn)行：

select查詢：

當(dāng)查詢一個(gè)分區(qū)表的時(shí)候，分區(qū)層先打開并鎖住所有的底層表，優(yōu)化器判斷是否可以過濾部分分區(qū)，然后再調(diào)用對應(yīng)的存儲引擎接口訪問各個(gè)分區(qū)的數(shù)據(jù)

insert操作：

當(dāng)寫入一條記錄時(shí)，分區(qū)層打開并鎖住所有的底層表，然后確定哪個(gè)分區(qū)接受這條記錄，再將記錄寫入對應(yīng)的底層表

delete操作：

當(dāng)刪除一條記錄時(shí)，分區(qū)層先打開并鎖住所有的底層表，然后確定數(shù)據(jù)對應(yīng)的分區(qū)，最后對相應(yīng)底層表進(jìn)行刪除操作

update操作：

當(dāng)更新一條數(shù)據(jù)時(shí)，分區(qū)層先打開并鎖住所有的底層表，mysql先確定需要更新的記錄在哪個(gè)分區(qū)，然后取出數(shù)據(jù)并更新，再判斷更新后的數(shù)據(jù)應(yīng)該放在哪個(gè)分區(qū)，然后對底層表進(jìn)行寫入操作，并對原數(shù)據(jù)所在的底層表進(jìn)行刪除操作

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

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

A：表非常大以至于無法全部都放在內(nèi)存中，或者只在表的最后部分有熱點(diǎn)數(shù)據(jù)，其他都是歷史數(shù)據(jù)

B：分區(qū)表的數(shù)據(jù)更容易維護(hù)，如：想批量刪除大量數(shù)據(jù)可以使用清除整個(gè)分區(qū)的方式。另外，還可以對一個(gè)獨(dú)立分區(qū)進(jìn)行優(yōu)化、檢查、修復(fù)等操作

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

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

E：如果需要，還可以備份和恢復(fù)獨(dú)立的分區(qū)，這在非常大的數(shù)據(jù)集的場景下效果非常好

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

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

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

在list分區(qū)下要使用null，則必須顯式地定義在分區(qū)的散列值中，否則插入null時(shí)會(huì)報(bào)錯(cuò)。hash和key分區(qū)對于null的處理方式和range,list分區(qū)不一樣，任何分區(qū)函數(shù)都會(huì)將null返回為0.

分區(qū)

分區(qū)就是將數(shù)據(jù)庫或其構(gòu)成元素劃分為不同的獨(dú)立部分

－－是一種預(yù)先組織表存儲的方法

mysql支持水平分區(qū)

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

分區(qū)的分布是跨物理存儲進(jìn)行的

－－根據(jù)用戶在需要時(shí)設(shè)置的指定規(guī)則

－－每個(gè)分區(qū)存儲為其自己的單元

數(shù)據(jù)的劃分

－－根據(jù)分區(qū)功能將數(shù)據(jù)劃分為子集

－－分區(qū)類型和表達(dá)式是表定義的一部分

－－表達(dá)式可以是整數(shù)或返回整數(shù)值的函數(shù)。

－－此值根據(jù)定義確定將每條記錄存儲在哪個(gè)分區(qū)中

1.primary key和unique key必須包含在分區(qū)key的一部分，否則在創(chuàng)建primary key和unique index時(shí)會(huì)報(bào)”ERROR 1503 (HY000)“

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

3.所有分區(qū)的engine必須一樣

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

分區(qū)管理

新增分區(qū)

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

刪除分區(qū)

–當(dāng)刪除了一個(gè)分區(qū)，也同時(shí)刪除了該分區(qū)中所有的數(shù)據(jù)。

ALTER TABLE sale_data DROP PARTITION p201010;

分區(qū)的合并

下面的SQL，將p201001 – p201009 合并為3個(gè)分區(qū)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)
);

以上就是數(shù)據(jù)庫表分區(qū)會(huì)提升插入效率嗎，小編相信有部分知識點(diǎn)可能是我們?nèi)粘９ぷ鲿?huì)見到或用到的。希望你能通過這篇文章學(xué)到更多知識。更多詳情敬請關(guān)注億速云行業(yè)資訊頻道。