《MysqL入门MysqL数据表分区技术PARTITION浅析》要点：
本文介绍了MysqL入门MysqL数据表分区技术PARTITION浅析，希望对您有用。如果有疑问，可以联系我们。
MysqL必读在这一章节里,我们来了解下 MysqL 中的分区技术 (RANGE,LIST,HASH)
MysqL 的分区技术与水平分表有点类似,但是它是在逻辑层进行的水平分表,对于应用而言它还是一张表,换句话说: 分区不是实际真正的对一张表进行拆分,分区之后表还是一个表,它是把存储文件进行拆分.
MysqL必读在 MysqL 5.1(后) 有了几种分区类型:
RANGE分区: 基于属于一个给定连续区间的列值,把多行分配给分区
MysqL必读LIST分区: 类似于按 RANGE 分区,区别在于 LIST 分区是基于列值匹配一个离散值集合中的某个值来进行选择
MysqL必读HASH分区: 基于用户定义的表达式的返回值来进行选择分区,该表达式使用将要插入到表中的这些行的列值进行计算,这个函数可以包含 MysqL 中有效的、产生非负整数值的任何表达式
MysqL必读KEY分区: 累世于按 HASH 分区,区别在于 KEY 分区只支持计算一列或多列,且 MysqL 服务器提供其自身的哈希函数
分区应该注意的事项：
MysqL必读1、 做分区时,要么不定义主键,要么把分区字段加入到主键中
2、 分区字段不能为NULL,要不然怎么确定分区范围呢,所以尽量 NOT NULL
首先你可以查看下你的 MysqL 版本是否支持 PARTITION
代码如下:
或者:
代码如下:
MysqL> show variables like "%part%";
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| have_partitioning | YES |
+-------------------+-------+
RANGE 分区
假定你创建了一个如下的表,该表保存有20家音像店的职员记录,这20家音像店的编号从1到20. 如果你想将其分成4个小分区,那么你可以采用RANGE分区,创建的数据库表如下:
代码如下:
MysqL-> 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
-> ) ENGINE=Myisam DEFAULT CHARSET=utf8
-> PARTITION BY RANGE (store_id) (
-> PARTITION P0 VALUES LESS THAN (6),
-> PARTITION P1 VALUES LESS THAN (11),
-> PARTITION P2 VALUES LESS THAN (16),
-> PARTITION P3 VALUES LESS THAN (21)
-> );
如果你想把不同时期离职的员工进行分别存储,那么你可以将日期字段 separated (即离职时间) 作为一个 key,创建的 sql 语句如下:
代码如下:
MysqL-> CREATE TABLE employees (
-> id INT NOT NULL,
-> store_id INT NOT NULL
-> ) ENGINE=Myisam DEFAULT CHARSET=utf8
-> PARTITION BY RANGE (YEAR(separated)) (
-> PARTITION P0 VALUES LESS THAN (2001),
-> PARTITION P1 VALUES LESS THAN (2011),
-> PARTITION P2 VALUES LESS THAN (2021),
-> PARTITION P3 VALUES LESS THAN MAXVALUE
-> );
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-> CREATE TABLE employees (
-> id INT NOT NULL,
-> store_id INT NOT NULL
-> ) ENGINE=Myisam DEFAULT CHARSET=utf8
-> PARTITION BY LIST (store_id) (
-> PARTITION pNorth VALUES IN (3,17),
-> PARTITION pEast VALUES IN (1,20),
-> PARTITION pWest VALUES IN (4,18),
-> PARTITION pCentral VALUES IN (7,16)
-> );
MysqL必读
当你创建完之后,你可以进入 MysqL 数据储存文件,该文件夹位置定义在 MysqL 配置文件中
代码如下:
shawn@Shawn:~$ sudo vi /etc/MysqL/my.cnf;
[MysqLd]
datadir = /var/lib/MysqL
shawn@Shawn:~$ cd /var/lib/MysqL/dbName
shawn@Shawn:/var/lib/MysqL/dbName$ ll
显示如下:
8768 Jun 7 22:01 employees.frm
48 Jun 7 22:01 employees.par
0 Jun 7 22:01 employees#P#pCentral.MYD
1024 Jun 7 22:01 employees#P#pCentral.MYI
0 Jun 7 22:01 employees#P#pEast.MYD
1024 Jun 7 22:01 employees#P#pEast.MYI
0 Jun 7 22:01 employees#P#pNorth.MYD
1024 Jun 7 22:01 employees#P#pNorth.MYI
0 Jun 7 22:01 employees#P#pWest.MYD
1024 Jun 7 22:01 employees#P#pWest.MYI
从这里可以看出,它是把存储文件根据我们的定义进行了拆分
代码如下:
HASH 分区
HASH 分区主要用来确保数据在预先确定数目的分区中平均分布
如果你想把不同时期加入的员工进行分别存储,那么你可以将日期字段 hired 作为一个 key
代码如下:
MysqL-> CREATE TABLE employees (
-> id INT NOT NULL,
-> store_id INT NOT NULL
-> ) ENGINE=Myisam DEFAULT CHARSET=utf8
-> PARTITION BY HASH (YEAR(hired)) (
-> PARTITIONS 4
-> );
#这里注意的是 PARTITIONS,多了一个 s
这里要提一下的就是,如上的例子都是使用的是 Myisam 存储引擎,它默认使用独立表空间,所以你可以在上面的磁盘空间里看到不同的分区
而 InnoDB 引擎则默认使用共享表空间,此时就算你对 InnoDB 表进行分区,你查看下会发现,它并没有像 Myisam 那么样进行物理上的分区,所以你需要修改下 MysqL 配置文件:
代码如下:
shawn@Shawn:~$ sudo vi /etc/MysqL/my.cnf;
#添加:
innodb_file_per_table=1
#重启 MysqL
shawn@Shawn:~$ sudo /etc/init.d/MysqL restart
此时你再对 InooDB 进行分区,则会有如下效果:
代码如下:
8768 Jun 7 22:54 employees.frm
48 Jun 7 22:54 employees.par
98304 Jun 7 22:54 employees#P#pCentral.ibd
98304 Jun 7 22:54 employees#P#pEast.ibd
98304 Jun 7 22:54 employees#P#pNorth.ibd
98304 Jun 7 22:54 employees#P#pWest.ibd
分区管理
删除分区
代码如下:
MysqL> alter table employees drop partition pWest;
新增分区
代码如下:
@H_404_239@
#range添加新分区
MysqL> alter table employees add partition ( partition p4 values less than (26) );
#list添加新分区
MysqL> alter table employees add partition( partition pSouth values in (21,22,23) );
#hash重新分区
MysqL> alter table employees add partition partitions 5;