首页 > 代码库 > mysql的分区
mysql的分区
查看mysql的分区功能是否启用:
mysql> show variables like ‘%part%‘; +-------------------+-------+ | Variable_name | Value | +-------------------+-------+ | have_partitioning | YES | +-------------------+-------+ 1 row in set (0.00 sec)
创建一个分区表:
CREATE TABLE `test100` ( `xuhao` int(11) NOT NULL, `xuhao2` int(11) NOT NULL, ) ENGINE=innodb DEFAULT CHARSET=utf8 PARTITION BY RANGE (xuhao) (PARTITION p201303 VALUES LESS THAN (unix_timestamp(‘2013-04-01‘)),PARTITION p201304 VALUES LESS THAN (unix_timestamp(‘2013-05-01‘)),PARTITION p201305 VALUES LESS THAN (unix_timestamp(‘2013-06-01‘)),PARTITION p201306 VALUES LESS THAN (unix_timestamp(‘2013-07-01‘)),PARTITION p201307 VALUES LESS THAN (unix_timestamp(‘2013-08-01‘)),PARTITION p201308 VALUES LESS THAN (unix_timestamp(‘2013-09-01‘)),PARTITION p201309 VALUES LESS THAN (unix_timestamp(‘2013-10-01‘)),PARTITION p201310 VALUES LESS THAN (unix_timestamp(‘2013-11-01‘)),PARTITION p201311 VALUES LESS THAN (unix_timestamp(‘2013-12-01‘)),PARTITION p201312 VALUES LESS THAN (unix_timestamp(‘2014-01-01‘)),PARTITION p201401 VALUES LESS THAN (unix_timestamp(‘2014-02-01‘)) );
添加分区:
alter table xxxxxxx add partition (partition p0 values less than(1991)); //只能添加大于分区键的分区
删除分区:
alter table xxx drop partition p0; //可以删除任意分区
删除分区数据
alter table xxxxxx truncate partition p1,p2;alter table xxxxxx truncate partition all; 或delete from xxxxxx where separated < ‘2006-01-01‘ or (separated >= ‘2006-01-01‘ and separated<‘2011-01-01‘);
重定义分区(包括重命名分区,伴随移动数据;合并分区)
alter table xxxxx reorganize partition p1,p3,p4 into (partition pm1 values less than(2006),partition pm2 values less than(2011));
rebuild重建分区
alter table xxxxxx rebuild partition pm1/all; //相当于drop所有记录,然后再reinsert;可以解决磁盘碎片
优化表
alter table tt2 optimize partition pm1; //在大量delete表数据后,可以回收空间和碎片整理。但在5.5.30后支持。在5.5.30之前可以通过recreate+analyze来替代,如果用rebuild+analyze速度慢
analzye表
alter table xxxxxx analyze partition pm1/all;
check表
alter table xxxxxx check partition pm1/all;
show create table employees2; //查看分区表的定义show table status like ‘employees2‘\G; //查看表时候是分区表 如“Create_options: partitioned”select * from information_schema.KEY_COLUMN_USAGE where table_name=‘employees2‘; //查看索引SELECT * FROM information_schema.partitions WHERE table_name=‘employees2‘ //查看分区表explain partitions select * from employees2 where separated < ‘1990-01-01‘ or separated > ‘2
本文出自 “王家东哥” 博客,谢绝转载!
mysql的分区
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。