当前位置： 移动技术网 > IT编程>数据库>Mysql > Mysql数据表分区技术PARTITION浅析

Mysql数据表分区技术PARTITION浅析

2017年12月12日  | 移动技术网IT编程  | 我要评论

哈尔贾,方太厨具价格,宫间葵

在这一章节里， 我们来了解下 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

复制代码 代码如下:

mysql> show plugins;
 
| partition    | active   | storage engine     | null    | gpl     |

或者:
复制代码 代码如下:

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,
     ->     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 (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,
     ->     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 list (store_id) (
     ->     partition pnorth   values in (3, 5, 6, 9, 17),
     ->     partition peast    values in (1, 2, 10, 11, 19, 20),
     ->     partition pwest    values in (4, 12, 13, 14, 18),
     ->     partition pcentral values in (7, 8, 15, 16)
     -> );


当你创建完之后， 你可以进入 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

从这里可以看出， 它是把存储文件根据我们的定义进行了拆分
复制代码 代码如下:

employees.frm = 表结构
employees.par = partition, 申明是一个分区表
.myd = 数据文件
.myi = 索引文件
 

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

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 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; 

新增分区
复制代码 代码如下:

#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; 

您可能感兴趣的文章:

如对本文有疑问，请在下面进行留言讨论，广大热心网友会与你互动！！ 点击进行留言回复

相关文章:

验证码：
最近更新的文章
大家感兴趣的文章
移动技术网