MySQL之——数据表分区技术PARTITION浅析

转载请注明出处:http://blog.csdn.net/l1028386804/article/details/50729565

这篇文章主要介绍了Mysql数据表分区技术PARTITION浅析,分别介绍了 Mysql 中的分区技术 RANGE、LIST、 HASH,需要的朋友可以参考下。

在这一章节里， 我们来了解下 Mysql 中的分区技术 (RANGE, LIST, HASH)
 
Mysql 的分区技术与水平分表有点类似， 但是它是在逻辑层进行的水平分表， 对于应用而言它还是一张表， 换句话说: 分区不是实际真正的对一张表进行拆分，分区之后表还是一个表，它是把存储文件进行拆分。

在 Mysql 5.1(后) 有了几种分区类型:

RANGE分区: 基于属于一个给定连续区间的列值， 把多行分配给分区

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

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

KEY分区: 累世于按 HASH 分区， 区别在于 KEY 分区只支持计算一列或多列， 且 Mysql 服务器提供其自身的哈希函数

分区应该注意的事项：

1、 做分区时，要么不定义主键，要么把分区字段加入到主键中
2、 分区字段不能为NULL，要不然怎么确定分区范围呢，所以尽量 NOT NULL
 
首先你可以查看下你的 Mysql 版本是否支持 PARTITION

[plain]  view plain  copy

1. mysql> show plugins;  
2.    
3. | partition    | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |  

或者:

[plain]  view plain  copy

1. mysql> show variables like "%part%";  
2.    
3. +-------------------+-------+  
4. | Variable_name     | Value |  
5. +-------------------+-------+  
6. | have_partitioning | YES   |  
7. +-------------------+-------+  

RANGE 分区

假定你创建了一个如下的表， 该表保存有20家音像店的职员记录， 这20家音像店的编号从1到20。 如果你想将其分成4个小分区， 那么你可以采用RANGE分区， 创建的数据库表如下:

[plain]  view plain  copy

1. mysql-> CREATE TABLE employees (  
2.      ->     id INT NOT NULL,  
3.      ->     fname VARCHAR(30),  
4.      ->     lname VARCHAR(30),  
5.      ->     hired DATE NOT NULL DEFAULT '1970-01-01',  
6.      ->     separated DATE NOT NULL DEFAULT '9999-12-31',  
7.      ->     job_code INT NOT NULL,  
8.      ->     store_id INT NOT NULL  
9.      -> ) ENGINE=Myisam DEFAULT CHARSET=utf8  
10.      -> PARTITION BY RANGE (store_id) (  
11.      ->     PARTITION P0 VALUES LESS THAN (6),  
12.      ->     PARTITION P1 VALUES LESS THAN (11),  
13.      ->     PARTITION P2 VALUES LESS THAN (16),  
14.      ->     PARTITION P3 VALUES LESS THAN (21)  
15.      -> );  

如果你想把不同时期离职的员工进行分别存储， 那么你可以将日期字段 separated (即离职时间) 作为一个 key, 创建的 SQL 语句如下:

[plain]  view plain  copy

1. mysql-> CREATE TABLE employees (  
2.      ->     id INT NOT NULL,  
3.      ->     fname VARCHAR(30),  
4.      ->     lname VARCHAR(30),  
5.      ->     hired DATE NOT NULL DEFAULT '1970-01-01',  
6.      ->     separated DATE NOT NULL DEFAULT '9999-12-31',  
7.      ->     job_code INT NOT NULL,  
8.      ->     store_id INT NOT NULL  
9.      -> ) ENGINE=Myisam DEFAULT CHARSET=utf8  
10.      -> PARTITION BY RANGE (YEAR(separated)) (  
11.      ->     PARTITION P0 VALUES LESS THAN (2001),  
12.      ->     PARTITION P1 VALUES LESS THAN (2011),  
13.      ->     PARTITION P2 VALUES LESS THAN (2021),  
14.      ->     PARTITION P3 VALUES LESS THAN MAXVALUE  
15.      -> );  

List 分区

同样的例子， 如果这20家影像店分布在4个有经销权的地区，

[plain]  view plain  copy

1. +------------------+--------------------------------------+  
2. | 地区             | 音像店 ID 号                         |  
3. +------------------+--------------------------------------+  
4. | 北区             | 3, 5, 6, 9, 17                       |  
5. | 东区             | 1, 2, 10, 11, 19, 20                 |  
6. | 西区             | 4, 12, 13, 14, 18                    |  
7. | 中心区           | 7, 8, 15, 16                         |  
8. +------------------+--------------------------------------+  
9.    
10. mysql-> CREATE TABLE employees (  
11.      ->     id INT NOT NULL,  
12.      ->     fname VARCHAR(30),  
13.      ->     lname VARCHAR(30),  
14.      ->     hired DATE NOT NULL DEFAULT '1970-01-01',  
15.      ->     separated DATE NOT NULL DEFAULT '9999-12-31',  
16.      ->     job_code INT NOT NULL,  
17.      ->     store_id INT NOT NULL  
18.      -> ) ENGINE=Myisam DEFAULT CHARSET=utf8  
19.      -> PARTITION BY LIST (store_id) (  
20.      ->     PARTITION pNorth   VALUES IN (3, 5, 6, 9, 17),  
21.      ->     PARTITION pEast    VALUES IN (1, 2, 10, 11, 19, 20),  
22.      ->     PARTITION pWest    VALUES IN (4, 12, 13, 14, 18),  
23.      ->     PARTITION pCentral VALUES IN (7, 8, 15, 16)  
24.      -> );  

当你创建完之后， 你可以进入 Mysql 数据储存文件， 该文件夹位置定义在 Mysql 配置文件中

[plain]  view plain  copy

1. shawn@Shawn:~$ sudo vi /etc/mysql/my.cnf;  
2.    
3. [mysqld]  
4. datadir         = /var/lib/mysql  
5.    
6. shawn@Shawn:~$ cd /var/lib/mysql/dbName  
7. shawn@Shawn:/var/lib/mysql/dbName$ ll  
8.    
9. 显示如下:  
10. 8768 Jun  7 22:01 employees.frm  
11.   48 Jun  7 22:01 employees.par  
12.    0 Jun  7 22:01 employees#P#pCentral.MYD  
13. 1024 Jun  7 22:01 employees#P#pCentral.MYI  
14.    0 Jun  7 22:01 employees#P#pEast.MYD  
15. 1024 Jun  7 22:01 employees#P#pEast.MYI  
16.    0 Jun  7 22:01 employees#P#pNorth.MYD  
17. 1024 Jun  7 22:01 employees#P#pNorth.MYI  
18.    0 Jun  7 22:01 employees#P#pWest.MYD  
19. 1024 Jun  7 22:01 employees#P#pWest.MYI  

从这里可以看出， 它是把存储文件根据我们的定义进行了拆分

[plain]  view plain  copy

1. employees.frm = 表结构  
2. employees.par = partition, 申明是一个分区表  
3. .MYD = 数据文件  
4. .MYI = 索引文件  

HASH 分区

HASH 分区主要用来确保数据在预先确定数目的分区中平均分布
如果你想把不同时期加入的员工进行分别存储， 那么你可以将日期字段 hired 作为一个 key

[plain]  view plain  copy

1. mysql-> CREATE TABLE employees (  
2.      ->     id INT NOT NULL,  
3.      ->     fname VARCHAR(30),  
4.      ->     lname VARCHAR(30),  
5.      ->     hired DATE NOT NULL DEFAULT '1970-01-01',  
6.      ->     separated DATE NOT NULL DEFAULT '9999-12-31',  
7.      ->     job_code INT NOT NULL,  
8.      ->     store_id INT NOT NULL  
9.      -> ) ENGINE=Myisam DEFAULT CHARSET=utf8  
10.      -> PARTITION BY HASH (YEAR(hired)) (  
11.      ->     PARTITIONS 4  
12.      -> );  
13.        
14. #这里注意的是 PARTITIONS， 多了一个 s  

这里要提一下的就是， 如上的例子都是使用的是 Myisam 存储引擎，它默认使用独立表空间， 所以你可以在上面的磁盘空间里看到不同的分区
而 InnoDB 引擎则默认使用共享表空间, 此时就算你对 InnoDB 表进行分区， 你查看下会发现， 它并没有像 Myisam 那么样进行物理上的分区， 所以你需要修改下 Mysql 配置文件:

[plain]  view plain  copy

1. shawn@Shawn:~$ sudo vi /etc/mysql/my.cnf;  
2.    
3. #添加:  
4. innodb_file_per_table=1  
5.    
6. #重启 mysql  
7. shawn@Shawn:~$ sudo /etc/init.d/mysql restart  

此时你再对 InooDB 进行分区， 则会有如下效果:

[plain]  view plain  copy

1. 8768 Jun  7 22:54 employees.frm  
2.    48 Jun  7 22:54 employees.par  
3. 98304 Jun  7 22:54 employees#P#pCentral.ibd  
4. 98304 Jun  7 22:54 employees#P#pEast.ibd  
5. 98304 Jun  7 22:54 employees#P#pNorth.ibd  
6. 98304 Jun  7 22:54 employees#P#pWest.ibd  

分区管理

删除分区

[plain]  view plain  copy

1. mysql> alter table employees drop partition pWest;    

新增分区

[plain]  view plain  copy

1. #range添加新分区   
2. mysql> alter table employees add partition ( partition p4 values less than (26) );   
3.     
4. #list添加新分区   
5. mysql> alter table employees add partition( partition pSouth values in (21, 22, 23) );   
6.     
7. #hash重新分区   
8. mysql> alter table employees add partition partitions 5;