创建mysql表分区的方法

创建mysql表分区的方法我来给大家介绍一下mysql表分区创建与使用吧，希望对各位同学会有所帮助。表分区的测试使用，主要内容来自于其他博客文章以及mysql5.1的参考手册。表分区是最近才知道的哦，以前自己做都是分表来实现上亿级别的数据了，下面我来给大家介绍一下mysql表分区创建与使用吧，希望对各位同学会有所帮助。

表分区的测试使用，主要内容来自于其他博客文章以及mysql5.1的参考手册

mysql测试版本：mysql5.5.28

mysql物理存储文件（有mysql配置的datadir决定存储路径）格式简介

数据库engine为MYISAM

frm表结构文件，myd表数据文件，myi表索引文件。

INNODBengine对应的表物理存储文件

innodb的数据库的物理文件结构为：

.frm文件

.ibd文件和.ibdata文件：

这两种文件都是存放innodb数据的文件，之所以用两种文件来存放innodb的数据，是因为innodb的数据存储方式能够通过配置来决定是使用共享表空间存放存储数据，还是用独享表空间存放存储数据。

独享表空间存储方式使用.ibd文件，并且每个表一个ibd文件

共享表空间存储方式使用.ibdata文件，所有表共同使用一个ibdata文件

创建分区

分区的一些优点包括：

·与单个磁盘或文件系统分区相比，可以存储更多的数据。

·对于那些已经失去保存意义的数据，通常可以通过删除与那些数据有关的分区，很容易地删除那些数据。相反地，在某些情况下，添加新数据的过程又可以通过为那些新数据专门增加一个新的分区，来很方便地实现。

通常和分区有关的其他优点包括下面列出的这些。MySQL分区中的这些功能目前还没有实现，但是在我们的优先级列表中，具有高的优先级；我们希望在5.1的生产版本中，能包括这些功能。

·一些查询可以得到极大的优化，这主要是借助于满足一个给定WHERE语句的数据可以只保存在一个或多个分区内，这样在查找时就不用查找其他剩余的分区。因为分区可以在创建了分区表后进行修改，所以在第一次配置分区方案时还不曾这么做时，可以重新组织数据，来提高那些常用查询的效率。

·

涉及到例如SUM()和COUNT()这样聚合函数的查询，可以很容易地进行并行处理。这种查询的一个简单例子如“SELECTsalesperson_id,COUNT(orders)asorder_totalFROMsalesGROUPBYsalesperson_id；”。通过“并行”，这意味着该查询可以在每个分区上同时进行，最终结果只需通过总计所有分区得到的结果。

·

通过跨多个磁盘来分散数据查询，来获得更大的查询吞吐量。

简而言之就是数据管理优化，查询更快，数据查询并行

检测mysql是否支持分区复制代码代码如下:

mysql>showvariableslike

"%partition%";

+-------------------+-------+

|Variable_name

|Value|

+-------------------+-------+

|have_partitioning|YES

|

+-------------------+-------+

1rowinset

RANGE分区：基于属于一个给定连续区间的列值，把多行分配给分区。复制代码代码如下:

DROPTABLEIFEXISTS`p_range`;

CREATETABLE`p_range`(

`id`int(10)NOTNULLAUTO_INCREMENT,

`name`char(20)NOTNULL,

PRIMARYKEY(`id`)

)ENGINE=MyISAMAUTO_INCREMENT=9DEFAULTCHARSET=utf8

/*!50100PARTITIONBYRANGE(id)

(PARTITIONp0VALUESLESSTHAN(8)ENGINE=MyISAM)*/;

range分区就是partitionbyrange(id)表示按id1-7的数据存储在p0分区；如果id大于7了则数据不能写入了，因为没有对应的数据分区来存储；

所以这时在创建分区时需要使用maxvalues关键字了复制代码代码如下:

PARTITIONBYRANGE(id)

(

PARTITIONp0VALUESLESSTHAN(8),

PARTITIONp1VALUESLESSTHANMAXVALUE)

这样就表示，所有id大于7的数据记录存在在p1分区里。

RANGE分区在如下场合特别有用：

·

当需要删除“旧的”数据时。如果你使用上面最近的那个例子给出的分区方案，你只需简单地使用“ALTERTABLEemployeesDROPPARTITIONp0；”来删除所有在1991年前就已经停止工作的雇员相对应的所有行。对于有大量行的表，这比运行一个如“DELETEFROMemployeesWHEREYEAR(separated)<=1990；”这样的一个DELETE查询要有效得多。

·

想要使用一个包含有日期或时间值，或包含有从一些其他级数开始增长的值的列。

·

经常运行直接依赖于用于分割表的列的查询。例如，当执行一个如“SELECTCOUNT(*)FROMemployeesWHEREYEAR(separated)=2000GROUPBYstore_id；”这样的查询时，MySQL可以很迅速地确定只有分区p2需要扫描，这是因为余下的分区不可能包含有符合该WHERE子句的任何记录。

LIST分区：类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。

list分区可以理解为按一个键的id区间进行数据存储，比如类型表1,2,3,4的所有记录存储在p0里面，5,6,7,8存在在p1分区里面

这里与range分区一样，如果现在有条记录typeid是9，那么这条记录是不能存入的；

需要注意的是：LIST分区没有类似如“VALUESLESSTHANMAXVALUE”这样的包含其他值在内的定义。将要匹配的任何值都必须在值列表中找到。复制代码代码如下:

DROPTABLEIFEXISTS`p_list`;

CREATETABLE`p_list`(

`id`int(10)NOTNULLAUTO_INCREMENT,

`typeid`mediumint(10)NOTNULLDEFAULT'0',

`typename`char(20)DEFAULTNULL,

PRIMARYKEY(`id`,`typeid`)

)ENGINE=MyISAMAUTO_INCREMENT=9DEFAULTCHARSET=utf8

/*!50100PARTITIONBYLIST(typeid)

(PARTITIONp0VALUESIN(1,2,3,4)ENGINE=MyISAM,

PARTITIONp1VALUESIN(5,6,7,8)ENGINE=MyISAM)*/;

HASH分区：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL中有效的、产生非负整数值的任何表达式。

HASH分区主要用来确保数据在预先确定数目的分区中平均分布。在RANGE和LIST分区中，必须明确指定一个给定的列值或列值集合应该保存在哪个分区中；而在HASH分区中，MySQL自动完成这些工作，你所要做的只是基于将要被哈希的列值指定一个列值或表达式，以及指定被分区的表将要被分割成的分区数量。

要使用HASH分区来分割一个表，要在CREATETABLE语句上添加一个“PARTITIONBYHASH(expr)”子句，其中“expr”是一个返回一个整数的表达式。它可以仅仅是字段类型为MySQL整型的一列的名字。此外，你很可能需要在后面再添加一个“PARTITIONSnum”子句，其中num是一个非负的整数，它表示表将要被分割成分区的数量。如果没有包括一个PARTITIONS子句，那么分区的数量将默认为1。复制代码代码如下:

DROPTABLEIFEXISTS`p_hash`;

CREATETABLE`p_hash`(

`id`int(10)NOTNULLAUTO_INCREMENT,

`storeid`mediumint(10)NOTNULLDEFAULT'0',

`storename`char(255)DEFAULTNULL,

PRIMARYKEY(`id`,`storeid`)

)ENGINE=InnoDBAUTO_INCREMENT=11DEFAULTCHARSET=utf8

/*!50100PARTITIONBYHASH(storeid)

PARTITIONS4*/;

InnoDB引擎

简单点说就是数据的存入可以按partitionbyhash(expr);这里的expr可以是键名也可以是表达式比如YEAR(time)，如果是表达式的情况下

“但是应当记住，每当插入或更新（或者可能删除）一行，这个表达式都要计算一次；这意味着非常复杂的表达式可能会引起性能问题，尤其是在执行同时影响大量行的运算（例如批量插入）的时候。”

在执行删除、写入、更新时这个表达式都会计算一次。

数据的分布采用基于用户函数结果的模数来确定使用哪个编号的分区。换句话，对于一个表达式“expr”，将要保存记录的分区编号为N，其中“N=MOD(expr,num)”。

比如上面的storeid为10；那么N=MOD(10,4);N是等于2的，那么这条记录就存储在p2的分区里面。

如果插入一个表达式列值为'2005-09-15′的记录到表中，那么保存该条记录的分区确定如下：MOD(YEAR('2005-09-01′),4)

=

MOD(2005,4)

=

1;就存储在p1分区里面了。

“MySQL5.1还支持一个被称为“linearhashing（线性哈希功能）”的变量，它使用一个更加复杂的算法来确定新行插入到已经分区了的表中的位置。

线性哈希分区和常规哈希分区在语法上的唯一区别在于，在“PARTITIONBY”子句中添加“LINEAR”关键字；线性哈希功能使用的一个线性的2的幂（powers-of-two）运算法则

按照线性哈希分区的优点在于增加、删除、合并和拆分分区将变得更加快捷，有利于处理含有极其大量（1000GB）数据的表。

它的缺点在于，与使用常规HASH分区得到的数据分布相比，各个分区间数据的分布不大可能均衡。”

KEY分区：类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL服务器提供其自身的哈希函数。必须有一列或多列包含整数值。复制代码代码如下:

DROPTABLEIFEXISTS`p_key`;

CREATETABLE`p_key`(

`id`int(10)NOTNULLAUTO_INCREMENT,

`keyname`char(20)DEFAULTNULL,

`keyval`varchar(1000)DEFAULTNULL,

PRIMARYKEY(`id`)

)ENGINE=MyISAMAUTO_INCREMENT=12DEFAULTCHARSET=utf8

/*!50100PARTITIONBYKEY(id)

PARTITIONS4*/;

按照KEY进行分区类似于按照HASH分区，除了HASH分区使用的用户定义的表达式，而KEY分区的哈希函数是由MySQL服务器提供。MySQL簇（Cluster）使用函数MD5()来实现KEY分区；对于使用其他存储引擎的表，服务器使用其自己内部的哈希函数，这些函数是基于与PASSWORD()一样的运算法则。

“CREATETABLE…PARTITIONBYKEY”的语法规则类似于创建一个通过HASH分区的表的规则。它们唯一的区别在于使用的关键字是KEY而不是HASH，并且KEY分区只采用一个或多个列名的一个列表。

与hash的区别就是，hash使用用户定义的表达式如YEAR(time)；而key分区则是由mysql服务器提供的。同样KEY也是可以使用linear线性key的，与hashlinear是相同的算法。

子分区：是分区表中每个分区的再次分割。复制代码代码如下:

DROPTABLEIFEXISTS`p_subpartition`;

CREATETABLE`p_subpartition`(

`id`int(10)DEFAULTNULL,

`title`char(255)NOTNULL,

`createtime`dateNOTNULL

)ENGINE=MyISAMDEFAULTCHARSET=utf8

/*!50100

PARTITIONBYRANGE(YEAR(createtime))

SUBPARTITIONBYHASH(MONTH(createtime))

(PARTITIONp0VALUESLESSTHAN(2012)

(SUBPARTITIONs1ENGINE=MyISAM,

SUBPARTITIONs2ENGINE=MyISAM),

PARTITIONp1VALUESLESSTHAN(2013)

(SUBPARTITIONs3ENGINE=MyISAM,

SUBPARTITIONs4ENGINE=MyISAM),

PARTITIONp2VALUESLESSTHANMAXVALUE

(SUBPARTITIONs5ENGINE=MyISAM,

SUBPARTITIONs6ENGINE=MyISAM))*/;

可以看到p_subpartition有三个分区p0,p1,p2；而这三个分区每一个又进一步分为2个分区。那么整个表都就分为6个小分区；

可以看到代表p_sobpartitionp0.myd的文件消失了，取代的是p_subpartition#p#p0#sp#s1.myd

在MySQL5.1中，对于已经通过RANGE或LIST分区了的表再进行子分区是可能的。

子分区是分区表中每个分区的再次分割，子分区既可以使用HASH希分区，也可以使用KEY分区。这也被称为复合分区（compositepartitioning）。

1，如果一个分区中创建了子分区，其他分区也要有子分区

2，如果创建了了分区，每个分区中的子分区数必有相同

3，同一分区内的子分区，名字不相同，不同分区内的子分区名子可以相同（5.1.50不适用）

分区注意点

1、重新分区时，如果原分区里面存在maxvalue则新的分区里面也必须包含maxvalue否则就错误。

altertablep_range2x

reorganizepartitionp1,p2

into(partitionp0valueslessthan(5),partitionp1valueslessthanmaxvalue);

[Err]1520–Reorganizeofrangepartitionscannotchangetotalrangesexceptforlastpartitionwhereitcanextendtherange

2、分区删除时，数据也同样会被删除

altertablep_rangedroppartitionp0;

3、如果range分区列表里面没有maxvalue则如有新数据大于现在分区range数据值那么这个数据是无法写入到数据库表的。

4、修改表名不需要删除分区后在进行更改，修改表名后分区存储mydmyi对应也会自动更改。

如果希望从所有分区删除所有的数据，但是又保留表的定义和表的分区模式，使用TRUNCATETABLE命令。（请参见13.2.9节，“TRUNCATE语法”）。

如果希望改变表的分区而又不丢失数据，使用“ALTERTABLE…REORGANIZEPARTITION”语句。参见下面的内容，或者在13.1.2节，“ALTERTABLE语法”中参考关于REORGANIZEPARTITION的信息。

5、对表进行分区时，不论采用哪种分区方式如果表中存在主键那么主键必须在分区列中。表分区的局限性。

6、list方式分区没有类似于range那种lessthanmaxvalue的写法，也就是说list分区表的所有数据都必须在分区字段的值列表集合中。

7、在MySQL5.1版中，同一个分区表的所有分区必须使用同一个存储引擎；例如，不能对一个分区使用MyISAM，而对另一个使用InnoDB。

8、分区的名字是不区分大小写的，myp1与MYp1是相同的。

分区的管理

range与list分区的改变动作不能适用于hash与key方式的分区。删除与添加动作是都能使用的。

以下面的例子复制代码代码如下:

DROPTABLEIFEXISTS`p_list`;

CREATETABLE`p_list`(

`id`int(10)NOTNULLAUTO_INCREMENT,

`typeid`mediumint(10)NOTNULLDEFAULT'0',

`typename`char(20)DEFAULTNULL,

PRIMARYKEY(`id`,`typeid`)

)ENGINE=MyISAMAUTO_INCREMENT=9DEFAULTCHARSET=utf8

/*!50100PARTITIONBYLIST(typeid)

(PARTITIONp0VALUESIN(1,2,3,4)ENGINE=MyISAM,

PARTITIONp1VALUESIN(5,6,7,8)ENGINE=MyISAM)*/;

range与list分区的管理

删除分区

ALTERTABLEtrDROPPARTITIONp1;

需要注意的是删除分区后，该分区的所有数据都没有了。同时删除后存在一个重大影响也就是typeid为5,6,7,8的记录是不能写入到该表了的！

清空数据

如果想要保留表结构与分区结构可以使用TRUNCATETABLE清空表

更改分区保留数据

ALTERTABLEtbl_nameREORGANIZEPARTITIONpartition_listINTO(partition_definitions)；如果想保留数据进行分区的更改

ALTERTABLEp_listREORGANIZEPARTITIONp0INTO(

PARTITIONs0VALUESIN(1,2),

PARTITIONs1VALUESIN(3,4),

);这样就能进行分区的合并了，那怎么进行拆分呢

ALTERTABLEp_listREORGANIZEPARTITIONs0,s1INTO(

PARTITIONp0VALUESIN(1,2,3,4),

);使用REORGANIZEPARTITION进行数据的合并与拆分，数据是没有丢失的。

在使用REORGANIZE进行重新分区时，需要注意几点：

1、用来确定新分区模式的PARTITION子句使用与用在CREATETABLE中确定分区模式的PARTITION子句相同的规则。(partition分区子句必须与创建原分区时的规则相同)

2、partition_definitions列表中分区的合集应该与在partition_list中命名分区的合集占有相同的区间或值集合。(不管是合并还是拆分，s0,s1到p0；p0到s0,s1里面的区间或者值都必须相同)

3、对于按照RANGE分区的表，只能重新组织相邻的分区；不能跳过RANGE分区。(比如按range年份p01990,p12000,p22013三个分区；在合并时partitionp0,p2into()

这样是不行的，因为这两个分区不是相邻的分区；)

4、不能使用REORGANIZEPARTITION来改变表的分区类型；也就是说，例如，不能把RANGE分区变为HASH分区，反之亦然。也不能使用该命令来改变分区表达式或列。

增加分区

ALTERTABLEp_listADDPARTITION(PARTITIONp2VALUESIN(9,10,11))；

但是不能使用

ALTERTABLEp_listADDPARTITION(PARTITIONp2VALUESIN(9,14))；

这样mysql会产生错误1465(HY000):在LIST分区中，同一个常数的多次定义

hash与key分区的管理在改变分区设置方面，按照HASH分区或KEY分区的表彼此非常相似，但是它们又与按照RANGE或LIST分区的表在很多方面有差别。

关于添加和删除按照RANGE或LIST进行分区的表的分区

不能使用与从按照RANGE或LIST分区的表中删除分区相同的方式，来从HASH或KEY分区的表中删除分区。但是，可以使用“ALTERTABLE...COALESCEPARTITION”命令来合并HASH或KEY分区。

查看源代码打印帮助1DROPTABLEIFEXISTS`p_hash`;

2

3CREATETABLE`p_hash`(

4`id`int(10)NOTNULLAUTO_INCREMENT,

5`storeid`mediumint(10)NOTNULLDEFAULT'0',

6`storename`char(255)DEFAULTNULL,

7PRIMARYKEY(`id`,`storeid`)

8)ENGINE=InnoDBAUTO_INCREMENT=11DEFAULTCHARSET=utf8

9/*!50100PARTITIONBYHASH(storeid)

10PARTITIONS4*/;

如p_hash的分区数为4个；

要减少分区数为2个

ALTERTABLEp_hashCOALESCEPARTITION2；

对于按照HASH，KEY，LINEARHASH，或LINEARKEY分区的表，COALESCE能起到同样的作用。COALESCE不能用来增加分区的数量，如果你尝试这么做，结果会出现类似于下面的错误：

mysql>ALTERTABLEclientsCOALESCEPARTITION18;

错误1478(HY000):不能移动所有分区，使用DROPTABLE代替要增加顾客表的分区数量从12到18，使用“ALTERTABLE…ADDPARTITION”,具体如下：

ALTERTABLEclientsADDPARTITIONPARTITIONS18；注释：“ALTERTABLE…REORGANIZEPARTITION”不能用于按照HASH或HASH分区的表。

分区维护

重建分区

这和先删除保存在分区中的所有记录，然后重新插入它们，具有同样的效果。它可用于整理分区碎片。

ALTERTABLEt1REBUILDPARTITION(p0,p1)；

优化分区如果从分区中删除了大量的行，或者对一个带有可变长度的行（也就是说，有VARCHAR，BLOB，或TEXT类型的列）作了许多修改，

可以使用“ALTERTABLE…OPTIMIZEPARTITI