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

linux及mysql交流群：387283109

 

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