斑竹网络专注为中小企业客户提供以管理服务为核心的IT全方位服务 https://www.sysadm.cn
分类: Mysql/postgreSQL
2013-11-25 10:52:16
mysql数据库分区.
(平台: Redhat AS 4 Linux 2.6.9-5.ELsmp)
(时间:2012-09-07)
一、 关于MySQL分区
正常情况下,在mysql中每张表都以对应文件的形式存放在磁盘上的,虽然因存储引擎的不同,这些文件的名字不同,但是如果没有做分区的话,那么数据只会存储在固定的系统文件里。这样,如果一张表里的数据非常大,如达百万级或千万级,那么对应的文件就会变是非常大而影响数据库的读写性能。所以,为了提高数据库的读写性能,特别是磁盘的I/O情性,通常当一个表里的数据达到一定的量的时候,就会对数据库进行分区。也即将同一张表里的数据分别存储在多个不同的系统文件里。
要对数据库的表进行分区,需要数据库支持。要使用其支持可以在编译源码时加上--with-partition 选项。如果在编译时加了--with-plugins=max这个选项,那么上述--with-partition会自动添加上的。
要确认MySQL数据库是否支持分区,可以使用以下两种方法之一来确认:
1、SHOW VARIABLES LIKE '%partition%';
如果有以下输出则表示支持:
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| have_partitioning | YES |
+-------------------+-------+
2、SHOW PLUGINS; 如果输出包含以下内容则表示支持分区
partition | ACTIVE | STORAGE ENGINE | NULL | GPL |
从mysql官网上下载二进制包安装的数据库是支持分区功能的。
二、分区类型
Mysql支持以下分区类型
1、范围分区(RANGE partioning) :依照指定列数据大小的范围进
行分区;
2、列表分区(LIST partioning):与范围分区类似,只是分区的依
据不是以指定列数据值的连接范围,而是某种规则定义的一个列表进行的分区;
3、哈希分区(HASH portioning):HASH分区主要用来确保数据在
预先确定数目的分区中平均分布,你所要做的只是基于将要被哈希的列值指定一个列值或表达式,以及指定被分区的表将要被分割成的分区数量;
4、关键字分区(KEY portioning):按照KEY进行分区类似于按
照HASH分区,除了HASH分区使用的用户定义的表达式,而KEY分区的 哈希函数是由MySQL 服务器提供。
5、子分区(Subpartitioning):子分区是中每个分区的再
次分割,子分区既可以使用HASH希分区,也可以使用KEY分区。这 也被称为复合分区(composite partitioning)。
三、分区管理
1、添加分区
a. 对现存表添加分区
ALTER TABLE tr PARTITION BY RANGE( YEAR(purchased) ) (
PARTITION p0 VALUES LESS THAN (1990),
PARTITION p1 VALUES LESS THAN (1995),
PARTITION p2 VALUES LESS THAN (2000),
PARTITION p3 VALUES LESS THAN (2005)
) ;
其中purchased是表tr里的一个字段。
b. 在建表时添加分区:
CREATE TABLE tr (id INT, name VARCHAR(50), purchased DATE) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY RANGE( YEAR(purchased) ) (
PARTITION p0 VALUES LESS THAN (1990),
PARTITION p1 VALUES LESS THAN (1995),
PARTITION p2 VALUES LESS THAN (2000),
PARTITION p3 VALUES LESS THAN (2005)
) ;
2、删除一个表分区:
ALTER TABLE tr DROP PARTITION p2;
注意:
如果删除一个表分区,那么原先存放在该分区里的数据将会一同被删除,所以删除一个表分区时一定要注意。
3、增加一个表分区
ALTER TABLE tr ADD PARTITION (PARTITION p4 VALUES LESS THAN (2010));
注意:
a. 在现有的分区的基础上增加分区,其范围必须是增加的,否会出错,例如:
ALTER TABLE tr ADD PARTITION (PARTITION p4 VALUES LESS THAN (1980)); 出现如下错误
ERROR 1493 (HY000): VALUES LESS THAN value must be strictly increasing for each partition
b. 如果了分区并规定了范围,那么就要需要确保可能插到数据库里的数据都在已经存在的范围内,否则会出现报错。如上表中,如果在建立p4分区之前试图插入如下数据:
INSERT INTO tr VALUES (1, 'desk organiser', '2012-10-15'); 则会报如下错误:
ERROR 1526 (HY000): Table has no partition for value 2012
4、修改分区(对分区重新划分范围)
ALTER TABLE tr REORGANIZE PARTITION p0 INTO (
PARTITION s0 VALUES LESS THAN (1980),
PARTITION s1 VALUES LESS THAN (1990)
);
其它的更详细的分区操作参考mysql操作手册。
http://blog.51yip.com/mysql/949.html