首页 > 代码库 > 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分区