【MYSQL】 分區表

標籤：mysql 分區表

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

    1.引言

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

    

    2.環境要求

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

650) this.width=650;" src="http://s3.51cto.com/wyfs02/M02/54/2A/wKioL1R6ym7A0CplAABfWiOHn-I850.jpg" title="partitions.png" alt="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=utf8partition 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;

    650) this.width=650;" src="http://s3.51cto.com/wyfs02/M00/54/2A/wKioL1R6z4qC137DAADITZNS8H8149.jpg" title="p1png.png" alt="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條資料，此時我們來查看其查詢執行過程：

650) this.width=650;" src="http://s3.51cto.com/wyfs02/M02/54/2A/wKioL1R60LDBET1bAADRpczcpSo931.jpg" title="p2.png" alt="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 110.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的資料，並執行查詢解析：

650) this.width=650;" src="http://s3.51cto.com/wyfs02/M00/54/2B/wKiom1R60nnwSlUUAADL0geAo20618.jpg" title="p3.png" alt="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=utf8partition 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條資料。

650) this.width=650;" src="http://s3.51cto.com/wyfs02/M02/54/2A/wKioL1R61Y-RjP7aAADWGviW2FI351.jpg" title="p4.png" alt="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=utf8partition by hash(id) partitions 3; #以id分區，分配到3張表中

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

    650) this.width=650;" src="http://s3.51cto.com/wyfs02/M00/54/2A/wKioL1R615mA8FC8AADLcG2rGPs147.jpg" title="p5.png" alt="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支援。本文不做過多闡述。

本文出自 “java程式冥” 部落格，請務必保留此出處http://793404905.blog.51cto.com/6179428/1584785

【MYSQL】 分區表