在线观看免费av网,亚洲香蕉在线观看,在线看国产精品

分區是將一個表的數據按照某種方式，比如按照月、天或者其他方式，分成多個較小的、更容易管理的部分，也就是物理存儲根據一定規則放在不同文件中，但是邏輯上所有的數據仍在一個表中。如下圖所示：

MySQL實現分區表的方式是對底層表的封裝，意味著索引也是按照分區的子表定義的，沒有全局索引。這和Or acle不同，在Oracle中可以更加靈活的定義索引和表是否進行分區。

分區表的實現原理

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

那么在分區表上的操作是怎樣進行的呢？其實常規的CRUD操作以及返回結果和普通表沒有任何區別。具體分區層實現是先打開并鎖定所有底層表，優化器先判斷是否可以過濾部分分區，然后調用對應的存儲引擎接口訪問各個分區的數據進行相應的操作。

分區表的分區類型

分區表的類型主要包括RANGE、LIST、HASH、KEY四種，另外還有一種COLUMNS分區類型，因不經常用，本文不做介紹。

RANGE分區

給定一個連續的區間范圍（區間要求連續并且不能重疊），某個字段的值滿足這個范圍就會被分配到該分區。適用于字段的值是連續的字段，比如日期范圍, 連續的數字等。

示例代碼：

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT NOT NULL,
    store_id INT NOT NULL
)
PARTITION BY RANGE (YEAR(separated))
 (
    PARTITION p0 VALUES LESS THAN (1991),
    PARTITION p1 VALUES LESS THAN (1996),
    PARTITION p2 VALUES LESS THAN (2001),
    PARTITION p3 VALUES LESS THAN MAXVALUE
)

該示例中根據每個員工離開公司的年份進行劃分，對于1991年之前離職員工存儲在分區p0中，1991年至1995年離職的人存儲在分區p1，1996年至2000年離職的人存儲在分區p2中。

如果沒有創建LESS THAN MAXVALUE分區，那么可能存在插入超過2000年離職的人的數據丟失不存儲，因此RANGE分區為了防止丟數據會加入該分區。MAXVALUE表示一個始終大于最大可能整數值的整數值，因此2001年以后離開的所有人存儲在分區p3中。

若設置了 LESS THAN MAXVALUE分區，添加新分區時需要重新分區，此時存儲在LESS THAN MAXVALUE分區中的數據會根據重新分區規則進行數據重新分配，如下所示：

ALTER TABLE employees  BY RANGE (YEAR(separated))
(
  PARTITION p0 VALUES LESS THAN (1991),
  PARTITION p1 VALUES LESS THAN (1996),
  PARTITION p2 VALUES LESS THAN (2001),
  PARTITION p3 VALUES LESS THAN (2006),
  PARTITION p4 VALUES LESS THAN MAXVALUE
);

對于日期類型的字段，也可以使用UNIX_TIMESTAMP()函數，根據TIMESTAMP列的值按RANGE對表進行分區，如下例所示：

CREATE TABLE quarterly_report_status (
    report_id INT NOT NULL,
    report_status VARCHAR(20) NOT NULL,
    report_updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)
PARTITION BY RANGE ( UNIX_TIMESTAMP(report_updated) ) (
    PARTITION p0 VALUES LESS THAN ( UNIX_TIMESTAMP('2020-01-01 00:00:00') ),
    PARTITION p1 VALUES LESS THAN ( UNIX_TIMESTAMP('2020-04-01 00:00:00') ),
    PARTITION p2 VALUES LESS THAN ( UNIX_TIMESTAMP('2020-07-01 00:00:00') ),
    PARTITION p3 VALUES LESS THAN (MAXVALUE)
);

LIST分區

和RANGE分區方式相似， LIST分區是基于列值匹配一個離散值集合中的某個值來進行選擇分區。LIST分區沒有類似RANGE分區中“VALUES LESS THAN MAXVALUE”包含其他值在內的定義，其要匹配的任何值都必須在值列表中找到，如果不在列表中的數據插入表中操作會失敗。

示例代碼：

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT,
    store_id INT
 )
 PARTITION BY LIST(store_id) (
    PARTITION pNorth VALUES IN (3,5,6,9,17),
    PARTITION pEast VALUES IN (1,2,10,11,19,20),
    PARTITION pWest VALUES IN (4,12,13,14,18),
    PARTITION pCentral VALUES IN (7,8,15,16)
 );

HASH分區

基于用戶定義表達式的返回值來進行分區，該表達式使用將要插入到表中的這些行的列值進行計算。

MySQL支持兩種HASH分區，常規HASH(HASH)分區和線性HASH(LINEARHASH) 分區。常規HASH分區使用取模算法，對應一個表達式expr可以計算出它被保存到哪個分區中，N = MOD(expr, num)。線性HASH分區使用一個線性的2的冪運算法則，V = POWER(2, CEILING(LOG(2,num)))。

常規HASH示例代碼：

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT,
    store_id INT
)
PARTITION BY HASH( YEAR(hired) )
PARTITIONS 4;

常規HASH分區非常簡便，使用HASH函數值的模數，可以讓數據平均的分布到每一個分區，但是由于分區在創建表的時候已經固定了，如果新增或者收縮分區的數據遷移比較大。

因此MySQL還支持線性HASH分區，線性HASH分區采用線性二乘冪算法，可以增加分區維護(增加、刪除、合并、拆分分區)時數據遷移和處理速度，但是也會存在各個分區之間數據的分布不太均衡的情況。

語法上線性HASH分區和HASH分區之間的唯一區別是在PARTITION BY子句中添加了LINEAR關鍵字。如下所示：

線性HASH示例代碼：

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT,
    store_id INT
)
PARTITION BY LINEAR HASH( YEAR(hired) )
PARTITIONS 6;

線性HASH分區的計算原理及步驟如下：

V = POWER(2,CEILING(LOG(2, num)))，其中：

num為分區的數量。
LOG(2, num) 代表計算num以2為底的對數。
CEILING() 代表向上取整。
POWER(2, x) 代表取2的x冪次。

如果num的值是2的冪數，那么這個表達式計算出來的結果不變。

假設num為13。則LOG（2,13）為3.7004397181411。CEILING（3.7004397181411）為4，而V = POWER（2,4）為16。

Set N =F(column_list) & (V - 1)。

N代表計算出來的數據所在分區編號。
F代表對分區鍵進行的操作，該操作返回一個整數值。
&代表位與運算。

當num是2的倍數時由于V計算出來的結果不變，這個時候線性HASH算法的計算結果F(column_list)&(V-1)=MOD(F(column_list)/num)和常規HASH取模算出的結果是一致的。

While N>= num:
```
Set V = V / 2

 Set N = N & (V - 1)
```

特別的，如果步驟2中計算出來的N比總分區數量num大，則需要執行本步驟操作，直到N

接下來用一個示例解釋下線性HASH算法找具體分區號的實現原理。創建一個有六個分區的線性HASH分區表，如下所示：

CREATE TABLE t1(
   Col1  INT ,
   Col2  CHAR(5) ,
   Col3  DATE 
)
PARTITION BY LINEAR HASH( YEAR(col3) )
PARTITIONS 6;

現在給該表中插入兩條記錄，Col3的值分別為'2003-04-14'和'1998-10-19'，其中上文中的F()在這里對應YEAR()，那么根據算法原理可以確定第一個分區號如下所示：

V = POWER(2, CEILING(LOG(2,6) )) = 8

N = YEAR('2003-04-14')& (8 - 1)

= 2003 & 7

= 3

判斷3>=6不成立，所以存儲在第3號分區，注意這里的3指的是P3,分區號是從P0開始。

當插入的值是'1998-10-19'計算所存儲的分區號如下所示：

V = 8

N = YEAR('1998-10-19')& (8 - 1)

= 1998 & 7

= 6

判斷6>=6成立，所以需要做下一步運算。

N = 6 & ((8 / 2) -1)

= 6 & 3

= 2

判斷2>=6不成立，所以存儲在第2號分區，同理這里的2指的是P2分區。

下面是用EXCEL做的一個簡單的測試，測算了一下MySQL使用線性HASH分區算法，將分區鍵值取（1-1048575），分區數量分別為4、5、6、7、8個時，各分區數據數量如圖所示：

從圖中我們可以看到官網說它是“線性”，但是又可能不太平均，是比較嚴謹的。從算法角度考慮，在分區鍵值平均分布的前提下，為了各分區數據量盡量平均，線性HASH推薦分區數量盡量為2的冪次，比如2，4，8，16。如果不能保證，則應盡量讓LOG(2, num)越接近于某個2的冪次。

常規HASH和線性HASH增加和收縮分區原理是一樣的。增加和收縮分區后原來的數據會根據現有的分區數量重新分布。HASH分區不能刪除分區，所以不能使用DROP PARTITION操作進行分區刪除操作。可以通過ALTER TABLE ... COALESCE PARTITION num合并分區，這里的num是減去的分區數量。可以通過ALTERTABLE ... ADD PARTITION PARTITIONS num來增加分區，這里是num是增加的分區數量。

KEY分區

和HASH分區類似，但是KEY分區不允許使用自定義的表達式，需要使用MySQL Server提供的HASH函數。

MYSQL支持兩種KEY分區, 常規KEY(KEY)分區和線性KEY(LINEARKEY) 分區,其中計算區別和HASH分區一樣。

常規KEY示例代碼：

CREATE TABLE tm1 (
    s1 CHAR(32) PRIMARY KEY
)
PARTITION BY KEY(s1)
PARTITIONS 10;

線性KEY示例代碼：

CREATE TABLE tk (
    col1 INT NOT NULL,
    col2 CHAR(5),
    col3 DATE
)
PARTITION BY LINEAR KEY (col1)
PARTITIONS 3;

分區鍵的注意事項

無論采用哪種分區，要么分區表上沒有主鍵/唯一鍵，要么分區表的主鍵/唯一鍵都必須包含分區鍵，即不能使用主鍵/唯一鍵字段之外的其它字段分區。

針對只包含唯一鍵的表給出示例分析如下：

下面的三個例子是不正確的：

CREATE TABLE t1 (
    col1 INT NOT NULL,
    col2 DATE NOT NULL,
    col3 INT NOT NULL,
    col4 INT NOT NULL,
    UNIQUE KEY (col1, col2)
)
PARTITION BY HASH(col3)
PARTITIONS 4;


CREATE TABLE t2 (
    col1 INT NOT NULL,
    col2 DATE NOT NULL,
    col3 INT NOT NULL,
    col4 INT NOT NULL,
    UNIQUE KEY (col1),
    UNIQUE KEY (col3)
)
PARTITION BY HASH(col1 + col3)
PARTITIONS 4;


CREATE TABLE t3 (
         col1 INT NOT NULL,
         col2 DATE NOT NULL,
         col3 INT NOT NULL,
         col4 INT NOT NULL,
         UNIQUE KEY (col1, col2),
         UNIQUE KEY (col3)
 )
 PARTITION BY HASH(col1 + col3)
 PARTITIONS 4;

在以上三個示例中的表都有至少一個唯一鍵，但是在唯一鍵中不包括分區表達式中使用的所有列。因此在創建的時候就會報錯：ERROR 1491 (HY000): A PRIMARY KEY must include all columns in thetable's partitioning function。

以下三個示例是正確的：

CREATE TABLE t1 (
    col1 INT NOT NULL,
    col2 DATE NOT NULL,
    col3 INT NOT NULL,
    col4 INT NOT NULL,
    UNIQUE KEY (col1, col2, col3)
)
PARTITION BY HASH(col3)
PARTITIONS 4;


CREATE TABLE t2 (
    col1 INT NOT NULL,
    col2 DATE NOT NULL,
    col3 INT NOT NULL,
    col4 INT NOT NULL,
    UNIQUE KEY (col1, col3)
)
PARTITION BY HASH(col1 + col3)
PARTITIONS 4;


CREATE TABLE t3 (
      col1 INT NOT NULL,
      col2 DATE NOT NULL,
      col3 INT NOT NULL,
      col4 INT NOT NULL,
      UNIQUE KEY (col1, col2, col3),
      UNIQUE KEY (col3)
 )
PARTITION BY HASH(col3)
PARTITIONS 4;

在以上三個示例中表的分區鍵都是所屬表的所有唯一鍵包含的字段，因此可以成功創建。

CREATE TABLE t4 (
    col1 INT NOT NULL,
    col2 INT NOT NULL,
    col3 INT NOT NULL,
    col4 INT NOT NULL,
    UNIQUE KEY (col1, col3),
    UNIQUE KEY (col2, col4)
);

在上面這個示例表是沒有辦法分區的，因為無法在分區鍵中包含屬于兩個唯一鍵的任何列。

關于主鍵和唯一鍵是一樣的，這里不重復做示例分析，秉承一個原則：主鍵/唯一鍵必須包含分區鍵的所有列。

分區表優缺點

大多數互聯網公司都不建議用MySQL分區表，雖然它有不少好處，但是不足之處也同樣很多。接下來講解下對應的優缺點。

優點：

分區表對業務透明，只需要維護一個表的數據結構，DML操作和普通表沒有區別。
DML操作加鎖僅影響操作的分區，不會影響未訪問分區。
通過TRUNCATE操作可以快速清理特定分區數據，通過DROP操作也可以直接刪除掉不需要的分區和對應的數據。
通過大數據量分區能有效降低索引層數，提高查詢性能。

缺點：

DDL操作需要鎖定所有分區，導致所有分區上操作都被阻塞。
當表數據量較小時，分區表和非分區表性能相近，分區表效果有限。
當表數據量較大時，對分區表進行DDL或其他運維操作難度大、風險高。
分區表在行業內使用較少，社區資料有限，存在未知風險多。
當單臺服務器性能無法滿足時，對分區表進行分拆的成本較高。
當分區表操作不當導致訪問所有分區時，會導致嚴重的性能問題。
使用分庫分表可以有效降低運維操作影響，對1億數據量表做DDL操作需要謹慎評估，而對10萬數據量表做DDL操作可以默認其很快完成。
使用分庫分表可以有效減小宕機或其他故障影響，將數據分庫分表到10套群集上，一套群集發生故障僅影響一部分的業務。
MySQL不支持自動分區擴展，需要手動新增分區并進行數據再均衡。

總結：

對于上億行或者更大數量的普通表清理，只能采用DELETE的方式，該方式不但性能差，而且無法真正回收空間。分區表可以通過刪除分區等方式，對歷史數據進行清理的同時數據文件也做了回收，真正釋放了空間、而且效率很高。

很多互聯網公司不建議用MySQL分區表，那么我個人的見解是：

對于特定場景是可以考慮采用分區表，如歷史數據有明確的分區范圍，訪問不垮分區，極少的變化操作，查詢語句邏輯簡單，無性能瓶頸。

對于Oracle這些商業數據庫，由于商業授權導致橫向擴展成本較高，且分區表功能穩定，因此通過硬件擴展和分區來承擔大數據量帶來的負載。

對于MySQL開源數據庫，企業有資源有能力將很多需求遷移到數據庫外通過代碼邏輯或者其它替代方式實現，因此更追求MySQL使用過程中的簡單、穩定、可靠，且通過增加服務器以及分庫分表更能有效處理數據量爆炸式增長帶來的性能問題。

因此個人不建議大量使用MySQL分區表，尤其是在重要的業務上。

聲明：本文內容及配圖由入駐作者撰寫或者入駐合作網站授權轉載。文章觀點僅代表作者本人，不代表電子發燒友網立場。文章及其配圖僅供工程師學習之用，如有內容侵權或者其他違規問題，請聯系本站處理。舉報投訴

存儲

存儲

+關注

關注
13

文章
4296

瀏覽量
85801
分區表

分區表

+關注

關注
0

文章
3

瀏覽量
6423
MySQL

MySQL

+關注

關注
1

文章
804

瀏覽量
26531

色哟哟视频在线观看-色哟哟视频在线-色哟哟欧美15最新在线-色哟哟免费在线观看-国产l精品国产亚洲区在线观看-国产l精品国产亚洲区久久

搜索歷史

MySQL分區表的類型及原理

評論

《DNESP32S3使用指南-IDF版_V1.6》第七章分區表

硬盤分區表錯誤與解決辦法

關于Linux nandflash分區表的疑問?

uboot源碼怎么查看spiflash分區表

MySQL表分區類型及介紹

詳解Hive分區表

關于MySQL分區和分表的詳細介紹

怎樣去修改分區表參數文件呢

Rockchip使用GPT作為其主要分區表資料推薦

瑞芯微在開源支持中使用GPT作為其主要分區表

求助，默認分區表中關于app分區大小的理解

默認分區表中關于app分區大小的問題求解

零磁道和分區表修復是什么意思

硬盤分區表破壞，所有盤符或部分盤符丟失故障

mysql中表分區的備份與恢復