Mysql数据表分区技术PARTITION浅析
在这一章节里，我们来了解下Mysql中的分区技术(RANGE,LIST,HASH)
Mysql的分区技术与水平分表有点类似，但是它是在逻辑层进行的水平分表，对于应用而言它还是一张表，换句话说:分区不是实际真正的对一张表进行拆分，分区之后表还是一个表，它是把存储文件进行拆分。
在Mysql5.1(后)有了几种分区类型:
RANGE分区:基于属于一个给定连续区间的列值，把多行分配给分区
LIST分区:类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择
HASH分区:基于用户定义的表达式的返回值来进行选择分区，该表达式使用将要插入到表中的这些行的列值进行计算，这个函数可以包含Mysql中有效的、产生非负整数值的任何表达式
KEY分区:累世于按HASH分区，区别在于KEY分区只支持计算一列或多列，且Mysql服务器提供其自身的哈希函数
分区应该注意的事项：
1、做分区时，要么不定义主键，要么把分区字段加入到主键中
2、分区字段不能为NULL，要不然怎么确定分区范围呢，所以尽量NOTNULL
首先你可以查看下你的Mysql版本是否支持PARTITION
mysql>showplugins; |partition |ACTIVE |STORAGEENGINE |NULL |GPL |
或者:
mysql>showvariableslike"%part%"; +-------------------+-------+ |Variable_name |Value| +-------------------+-------+ |have_partitioning|YES | +-------------------+-------+
RANGE分区
假定你创建了一个如下的表，该表保存有20家音像店的职员记录，这20家音像店的编号从1到20。如果你想将其分成4个小分区，那么你可以采用RANGE分区，创建的数据库表如下:
mysql->CREATETABLEemployees(
-> idINTNOTNULL,
-> fnameVARCHAR(30),
-> lnameVARCHAR(30),
-> hiredDATENOTNULLDEFAULT'1970-01-01',
-> separatedDATENOTNULLDEFAULT'9999-12-31',
-> job_codeINTNOTNULL,
-> store_idINTNOTNULL
->)ENGINE=MyisamDEFAULTCHARSET=utf8
->PARTITIONBYRANGE(store_id)(
-> PARTITIONP0VALUESLESSTHAN(6),
-> PARTITIONP1VALUESLESSTHAN(11),
-> PARTITIONP2VALUESLESSTHAN(16),
-> PARTITIONP3VALUESLESSTHAN(21)
->);如果你想把不同时期离职的员工进行分别存储，那么你可以将日期字段separated(即离职时间)作为一个key,创建的SQL语句如下:
mysql->CREATETABLEemployees(
-> idINTNOTNULL,
-> fnameVARCHAR(30),
-> lnameVARCHAR(30),
-> hiredDATENOTNULLDEFAULT'1970-01-01',
-> separatedDATENOTNULLDEFAULT'9999-12-31',
-> job_codeINTNOTNULL,
-> store_idINTNOTNULL
->)ENGINE=MyisamDEFAULTCHARSET=utf8
->PARTITIONBYRANGE(YEAR(separated))(
-> PARTITIONP0VALUESLESSTHAN(2001),
-> PARTITIONP1VALUESLESSTHAN(2011),
-> PARTITIONP2VALUESLESSTHAN(2021),
-> PARTITIONP3VALUESLESSTHANMAXVALUE
->);List分区
同样的例子，如果这20家影像店分布在4个有经销权的地区，
+------------------+--------------------------------------+
|地区 |音像店ID号 |
+------------------+--------------------------------------+
|北区 |3,5,6,9,17 |
|东区 |1,2,10,11,19,20 |
|西区 |4,12,13,14,18 |
|中心区 |7,8,15,16 |
+------------------+--------------------------------------+
mysql->CREATETABLEemployees(
-> idINTNOTNULL,
-> fnameVARCHAR(30),
-> lnameVARCHAR(30),
-> hiredDATENOTNULLDEFAULT'1970-01-01',
-> separatedDATENOTNULLDEFAULT'9999-12-31',
-> job_codeINTNOTNULL,
-> store_idINTNOTNULL
->)ENGINE=MyisamDEFAULTCHARSET=utf8
->PARTITIONBYLIST(store_id)(
-> PARTITIONpNorth VALUESIN(3,5,6,9,17),
-> PARTITIONpEast VALUESIN(1,2,10,11,19,20),
-> PARTITIONpWest VALUESIN(4,12,13,14,18),
-> PARTITIONpCentralVALUESIN(7,8,15,16)
->);
当你创建完之后，你可以进入Mysql数据储存文件，该文件夹位置定义在Mysql配置文件中
shawn@Shawn:~$sudovi/etc/mysql/my.cnf; [mysqld] datadir =/var/lib/mysql shawn@Shawn:~$cd/var/lib/mysql/dbName shawn@Shawn:/var/lib/mysql/dbName$ll 显示如下: 8768Jun 722:01employees.frm 48Jun 722:01employees.par 0Jun 722:01employees#P#pCentral.MYD 1024Jun 722:01employees#P#pCentral.MYI 0Jun 722:01employees#P#pEast.MYD 1024Jun 722:01employees#P#pEast.MYI 0Jun 722:01employees#P#pNorth.MYD 1024Jun 722:01employees#P#pNorth.MYI 0Jun 722:01employees#P#pWest.MYD 1024Jun 722:01employees#P#pWest.MYI
从这里可以看出，它是把存储文件根据我们的定义进行了拆分
employees.frm=表结构 employees.par=partition,申明是一个分区表 .MYD=数据文件 .MYI=索引文件
HASH分区
HASH分区主要用来确保数据在预先确定数目的分区中平均分布
如果你想把不同时期加入的员工进行分别存储，那么你可以将日期字段hired作为一个key
mysql->CREATETABLEemployees(
-> idINTNOTNULL,
-> fnameVARCHAR(30),
-> lnameVARCHAR(30),
-> hiredDATENOTNULLDEFAULT'1970-01-01',
-> separatedDATENOTNULLDEFAULT'9999-12-31',
-> job_codeINTNOTNULL,
-> store_idINTNOTNULL
->)ENGINE=MyisamDEFAULTCHARSET=utf8
->PARTITIONBYHASH(YEAR(hired))(
-> PARTITIONS4
->);
#这里注意的是PARTITIONS，多了一个s这里要提一下的就是，如上的例子都是使用的是Myisam存储引擎，它默认使用独立表空间，所以你可以在上面的磁盘空间里看到不同的分区
而InnoDB引擎则默认使用共享表空间,此时就算你对InnoDB表进行分区，你查看下会发现，它并没有像Myisam那么样进行物理上的分区，所以你需要修改下Mysql配置文件:
shawn@Shawn:~$sudovi/etc/mysql/my.cnf; #添加: innodb_file_per_table=1 #重启mysql shawn@Shawn:~$sudo/etc/init.d/mysqlrestart
此时你再对InooDB进行分区，则会有如下效果:
8768Jun 722:54employees.frm 48Jun 722:54employees.par 98304Jun 722:54employees#P#pCentral.ibd 98304Jun 722:54employees#P#pEast.ibd 98304Jun 722:54employees#P#pNorth.ibd 98304Jun 722:54employees#P#pWest.ibd
分区管理
删除分区
mysql>altertableemployeesdroppartitionpWest;
新增分区
#range添加新分区 mysql>altertableemployeesaddpartition(partitionp4valueslessthan(26)); #list添加新分区 mysql>altertableemployeesaddpartition(partitionpSouthvaluesin(21,22,23)); #hash重新分区 mysql>altertableemployeesaddpartitionpartitions5;