【MYSQL】 分割槽表

    對於MYSQL的態度一直都是會基本SQL和簡單命令就行，最近處理一個資料量很大的專案，為了提高效率，在資料庫方面的瓶頸上，選擇了使用分割槽表來提高查詢效率。至此和大家一起分享一下。

    1.引言

    本文初略的講述了mysql資料庫如何分割槽表。

    

    2.環境要求

    在5.1版本中不是預設就安裝了，而在之後版本中一般預設選擇了安裝分割槽表支援。可以透過如下方式檢視當前資料庫是否支援分割槽表操作：

wKioL1R6ym7A0CplAABfWiOHn-I850.jpg

    使用show variables like '%partition%';如果不支援分割槽，那麼value欄位值為No。

    3.重要概念描述

    3.1 分割槽欄位

    1）當僅存在單一主鍵時，不存在唯一鍵，那麼分割槽欄位必須是主鍵欄位；

    2）當存在複合主鍵時，不存在唯一鍵，那麼分割槽欄位必須是主鍵組合的一部分欄位，一個或多個。

    3）當主鍵和唯一鍵都存在時，那麼分割槽欄位必須同時包括主鍵欄位和唯一鍵欄位。

    4.分割槽表型別

    4.1 range分割槽

    1）語法展示：

# 語法

# 在建立表單的最後，新增partitions by range(分割槽欄位)(

#   partition 分割槽名 values less than(閥值1),

#   partition 分割槽名 values less than(閥值2),

#   ...

#   partition 分割槽名 values less than(閥值n),

# )

    示例展示：

create table test_range(

  id int auto_increment,

  description varchar(50),

  primary key(id)

) ENGINE=InnoDB auto_increment=1 default charset=utf8

partition by range(id)(

  partition p1 values less than(6), #id<6的存放在p1分割槽

  partition p2 values less than(11) #6 <= id < 11 存放在p2分割槽

);

    檢視分割槽情況：

   show create table test_range;

    wKioL1R6z4qC137DAADITZNS8H8149.jpg    

    注意到，在顯示的表結構新增了分割槽表的資訊。

    資料測試：

    insert into test_range values(null, "test1");    

    insert into test_range values(null, "test2");

    insert into test_range values(null, "test3");

    insert into test_range values(null, "test4");

    insert into test_range values(null, "test5");

    insert into test_range values(null, "test6");

    insert into test_range values(null, "test7");

    insert into test_range values(null, "test8");

    insert into test_range values(null, "test9");

    insert into test_range values(null, "test10");

    插入10條資料，此時我們來檢視其查詢執行過程：

wKioL1R60LDBET1bAADRpczcpSo931.jpg

    從結果可以發現，其只是在p1分割槽執行的查詢，那麼此時就減少了查詢掃描的資料量，從而提高了查詢效率。

    如果此時，我們插入第11條資料會發生什麼情況呢？

   insert into test_range values(null, "test11");

   會發錯：insert into test_range values(null, "test11")  Error Code: 1526. Table has no partition for value 11 0.015 sec

    原因很簡單，因為在我們建立表單時，僅僅指定了1 - 10的id數值分割槽，當插入id=11時的分割槽時，此時沒有分割槽提供，那麼就引發錯誤，那麼如果解決這樣的問題呢，採取如下方式，修改表的分割槽方式：

alter table test_range add partition(

  partition p3 values less than(MAXVALUE)

);

# 新增一個分割槽，也就是p3是id從11到maxValue的存放區域

    此時插入id=11的資料，並執行查詢解析：

wKiom1R60nnwSlUUAADL0geAo20618.jpg    發現，已經將其分配到p3分割槽中了。

    還需要特別注意的時，使用partition by range(分割槽欄位)，其中的分割槽欄位可以是分割槽欄位的表單式，但是必須是返回的整數，在5.5版本中，可以使用partition by range column/columns語法，指定某個欄位。這裡不做介紹。大家可以自己嘗試一下。

    4.2 list分割槽

    list分割槽可以理解為集合分割槽方式，意思就是指定某個集合來分割槽。

    語法展示：

   partition by list(分割槽欄位表示式)(

     partition 分割槽名 values in(value1, value2,...,valuen)#分割槽集合

   );

    示例展示：

create table test_list(

  id int auto_increment,

  description varchar(50),

  primary key(id)

)ENGINE=InnoDB auto_increment=1 default charset=utf8

partition by list(id)(

  partition p1 values in (1, 3, 5, 7, 9),#id=1,3,5,7,9分配至p1區

  partition p2 values in (2, 4, 6, 8, 10)#id=2,4,6,8,10分配至p2區

);

#可以如4.1中使用show create table test_list檢視錶建立結構。

    資料測試：使用4.1中資料測試sql，插入10條資料。

wKioL1R61Y-RjP7aAADWGviW2FI351.jpg    可以發現其查詢的僅僅是p1區。如果需要新增分割槽，可以使用4.1中使用的add partition來新增分割槽。

    4.3 hash分割槽

    使用hash函式得到取模，分配到不同的分割槽中。分割槽表示式必須返回整數。

    語法展示：

   partition by hash(分割槽表示式) partitions 表數量（模數）.

    示例展示：

create table test_hash(

  id int auto_increment,

  description varchar(50),

  primary key(id)

) ENGINE=InnoDB auto_increment=1 default charset=utf8

partition by hash(id) partitions 3; #以id分割槽，分配到3張表中

    資料測試：插入4.1類同10條資料

    wKioL1R615mA8FC8AADLcG2rGPs147.jpg

    你也可以嘗試修改id值，檢視其分配的分割槽。hash分割槽還有一種叫做linear hash線性分割槽，這裡不做介紹，

    

    4.4 key分割槽

    在本次開發中，我選擇的是key分割槽，因為其是針對一個或多個欄位作為分割槽欄位，不要求是正整數，其內部呼叫的是自己的hash函式，計算出hash整數值，然後取模分表。

    語法展示：

   partition by key(分割槽欄位組合) partitions 表數（模數）。

    操作和Hash分割槽一致，這裡就不做累贅的展示了。

    5.額外擴充套件

    5.1 在實際開發中，經常出現的情況是表已經上線使用，那麼必須動態新增分割槽型別。

   alter table 表名 partition by hash/key （分割槽欄位表示式） [partitions 表數]#如果不加partitions那麼預設為1.

   

   alter table 表名 partition by range/list(分割槽欄位表示式)（具體分割槽設定）。

    5.2 當發現之前的分割槽需要新增新的分割槽時，採取如下方式：

   list/range : alter table 表名 add partition (partition 分割槽名 [values in|values less than] [集合|數值])；

   hash/key : alter table 表名 add partition partitions 表數；

    例如：修改上述test_hash的分割槽數量

   alter table test_hash add partition partitions 6;

    5.3 刪除某個分割槽/刪除所有分割槽

   # 刪除某個分割槽

   list/range : alter table 表名 drop partition 分割槽名1, 分割槽名2，...；

   #例如：

   alter table test_list drop partition p1;

   hash/key : 上述語法不成立

   

   # 刪除整個分割槽

   alter table test_hash remove partitioning;

    還有諸如合併分割槽，以及5.5的一些新特性，list/range 增加column,columns支援。本文不做過多闡述。

©著作權歸作者所有：來自51CTO部落格作者程式冥的原創作品，如需轉載，請註明出處，否則將追究法律責任

mysql 分割槽表專題