首页 > 代码库 > MySQL分区

MySQL分区

一、MySQL分区简介

    当mysql一张数据表中的数据达到一定的量时,在其中查询某一个数据,需要花费大量的时间。为了避免这种查询的等待,可以对一张大的数据表做拆分。将其拆分成多张小的数据表。可以基于物理的拆分,将一张表拆分成多张小表,分别存放于不同的服务器上,以分散对mysql服务器的写的压力。也可以基于逻辑的拆分,将一张表存放到不同的区块或磁盘上,以提高mysql的读写性能。

    mysql数据拆分基于拆分方式的不同,又分为水平拆分和垂直拆分,水平拆分也叫基于行的拆分,它不改变表结构,只是把多行数据分成多个表来进行存放,每个表只存放其中一部分行的数据。垂直拆分也叫基于列的拆分,它是将一张表中的多个列分开,拆分后的每张表只存放一部分列。

    mysql分区是一种基于逻辑的水平拆分的方式。


二、分区优点

1、分区可以分在多个磁盘,存储更大一点

2、根据查找条件,也就是where后面的条件,查找只查找相应的分区不用全部查找了

3、进行大数据搜索时可以进行并行处理。

4、跨多个磁盘来分散数据查询,来获得更大的查询吞吐量


三、mysql分区的模式

1、RANGE

将数据按照某一列或者某几列的范围的不同进行划分,例如可以将一个表通过年份划分成三个分区,90年代的数据,2000到2010年之前的数据,2010年至今的数据。

1)创建一个range分区表:

mysql> CREATE TABLE IF NOT EXISTS `user` (

 -> `id` int(11) NOT NULL AUTO_INCREMENT COMMENT ‘用户ID‘,

 -> `name` varchar(50) NOT NULL DEFAULT ‘‘ COMMENT ‘名称‘,

 -> `sex` int(1) NOT NULL DEFAULT ‘0‘ COMMENT ‘0为男,1为女‘,

 -> PRIMARY KEY (`id`)

 -> ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1

 -> PARTITION BY RANGE (id) (

 -> PARTITION p0 VALUES LESS THAN (3),

 -> PARTITION p1 VALUES LESS THAN (6),

 -> PARTITION p2 VALUES LESS THAN (9),

 -> PARTITION p3 VALUES LESS THAN (12),

 -> PARTITION p4 VALUES LESS THAN MAXVALUE

 -> );


2)对现有表进行分区,并按照规则自动将表中现有数据分配到对应的分区中:

mysql> alter table aa partition by RANGE(id)

 -> (PARTITION p1 VALUES less than (1),

 -> PARTITION p2 VALUES less than (5),

 -> PARTITION p3 VALUES less than MAXVALUE);


2、 LIST

系统通过预定义的列的值对所对应的行数据进行分区

1)创建一个list分区表:

mysql> CREATE TABLE IF NOT EXISTS `list_part` (

 -> `id` int(11)  PRIMARY KEY AUTO_INCREMENT NOT UNSIGNED NOT NULL COMMENT ‘用户ID‘,

 -> `province_id` int(2) NOT NULL DEFAULT 0 COMMENT ‘省‘,

 -> `name` varchar(50) NOT NULL DEFAULT ‘‘ COMMENT ‘名称‘,

 -> `sex` int(1) NOT NULL DEFAULT ‘0‘ COMMENT ‘0为男,1为女‘

 -> ) ENGINE=INNODB DEFAULT CHARSET=utf8

 -> PARTITION BY LIST (province_id) (

 -> PARTITION p0 VALUES IN (1,2,3,4,5,6,7,8),

 -> PARTITION p1 VALUES IN (9,10,11,12,16,21),

 -> PARTITION p2 VALUES IN (13,14,15,19),

 -> PARTITION p3 VALUES IN (17,18,20,22,23,24)

 -> );


3、HASH

HASH分区主要用来确保数据在预先确定数目的分区中平均分布,你所要做的只是基于将要被哈希的列值指定一个列值或表达式,以 及指定被分区的表将要被分割成的分区数量。 

1)创建hash分区:

mysql> CREATE TABLE IF NOT EXISTS `hash_part` (

 -> `id` int(11) NOT NULL AUTO_INCREMENT COMMENT ‘评论ID‘,

 -> `comment` varchar(1000) NOT NULL DEFAULT ‘‘ COMMENT ‘评论‘,

 -> `ip` varchar(25) NOT NULL DEFAULT ‘‘ COMMENT ‘来源IP‘,

 -> PRIMARY KEY (`id`)

 -> ) ENGINE=INNODB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1

 -> PARTITION BY HASH(id)

 -> PARTITIONS 3;


4、KEY

按照KEY进行分区类似于按照HASH分区,除了HASH分区使用的用户定义的表达式,而KEY分区的哈希函数是由MySQL 服务器提供。 

1)创建key分区:   

mysql> CREATE TABLE IF NOT EXISTS `key_part` (

 -> `news_id` int(11) NOT NULL COMMENT ‘新闻ID‘,

 -> `content` varchar(1000) NOT NULL DEFAULT ‘‘ COMMENT ‘新闻内容‘,

 -> `u_id` varchar(25) NOT NULL DEFAULT ‘‘ COMMENT ‘来源IP‘,

 -> `create_time` DATE NOT NULL DEFAULT ‘0000-00-00 00:00:00‘ COMMENT ‘时间‘

 -> ) ENGINE=INNODB DEFAULT CHARSET=utf8

 -> PARTITION BY LINEAR HASH(YEAR(create_time))

 -> PARTITIONS 3;


5、Composite

也被称之为子分区。是分区表中每个分区的再次分割,子分区既可以使用HASH希分区,也可以使用KEY分区。

1)如果一个分区中创建了子分区,其他分区也要有子分区

2)如果创建了了分区,每个分区中的子分区数必有相同

3)同一分区内的子分区,名字不相同,不同分区内的子分区名子可以相同(5.1.50不适用)


4)创建子分区:

mysql> CREATE TABLE IF NOT EXISTS `sub_part` (

 -> `news_id` int(11) NOT NULL COMMENT ‘新闻ID‘,

 -> `content` varchar(1000) NOT NULL DEFAULT ‘‘ COMMENT ‘新闻内容‘,

 -> `u_id` int(11) NOT NULL DEFAULT 0s COMMENT ‘来源IP‘,

 -> `create_time` DATE NOT NULL DEFAULT ‘0000-00-00 00:00:00‘ COMMENT ‘时间‘

 -> ) ENGINE=INNODB DEFAULT CHARSET=utf8

 -> PARTITION BY RANGE(YEAR(create_time))

 -> SUBPARTITION BY HASH(TO_DAYS(create_time))(

 -> PARTITION p0 VALUES LESS THAN (1990)(SUBPARTITION s0,SUBPARTITION s1,SUBPARTITION s2),

 -> PARTITION p1 VALUES LESS THAN (2000)(SUBPARTITION s3,SUBPARTITION s4,SUBPARTITION good),

 -> PARTITION p2 VALUES LESS THAN MAXVALUE(SUBPARTITION tank0,SUBPARTITION tank1,SUBPARTITION tank3)

 -> );


四、MySQL分区管理

1、删除分区

mysql> alter table user drop partition p4;


2、新增分区

1)range添加新分区:

mysql> alter table user add partition(partition p4 values less than MAXVALUE);

2)list添加分区:

mysql> alter table list_part add partition(partition p4 values in (25,26,28));

3)hash重新分区:

mysql> alter table hash_part add partition partitions 4;

4)key重新分区:

mysql> alter table key_part add partition partitions 4;

5)子分区添加新分区:

mysql> alter table sub1_part add partition(partition p3 values less than MAXVALUE);


3、重新分区:

1)range重新分区

mysql> ALTER TABLE user REORGANIZE PARTITION p0,p1,p2,p3,p4 INTO (PARTITION p0 VALUES LESS THAN MAXVALUE);

2)list重新分区

mysql> ALTER TABLE list_part REORGANIZE PARTITION p0,p1,p2,p3,p4 INTO (PARTITION p0 VALUES in (1,2,3,4,5));



本文出自 “无名小卒” 博客,请务必保留此出处http://breezey.blog.51cto.com/2400275/1568014

MySQL分区