Mysql 表分区

最近工作中遇到一个情况，需要将使用Mysql存储海量的数据，需要使用Mysql分区的技术，那么下面是一个按照日期分表的一个事例：

 CREATE TABLE `big_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `thedate` date DEFAULT NULL COMMENT '日期',
  `shop_id` bigint(20) DEFAULT NULL COMMENT 'ID',
  `auction_name` varchar(128) DEFAULT NULL COMMENT '名称',
  PRIMARY KEY (`id`,`thedate`),
  KEY `seller_id` (`seller_id`,`thedate`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 
PARTITION BY hash (dayofyear(thedate)) partitions 366;

一、创建分区表事例：

CREATE TABLE BIGTABLE
(
ID INT,
SNPTIME DATETIME NOT NULL,
VALUE VARCHAR(20),
PRIMARY KEY (SNPTIME, ID)
) ENGINE=InnoDB
partition by range (TO_DAYS(SNPTIME))
(
PARTITION p1 VALUES LESS THAN (to_days('2009-1-31')),
PARTITION p2 VALUES LESS THAN (to_days('2009-2-28')),
PARTITION p3 VALUES LESS THAN (to_days('2008-3-31')),
PARTITION p4 VALUES LESS THAN (to_days('2008-4-30')),
PARTITION p5 VALUES LESS THAN (to_days('2008-5-31')),
PARTITION p6 VALUES LESS THAN (to_days('2008-6-30')),
PARTITION p7 VALUES LESS THAN (to_days('2008-7-31')),
PARTITION p8 VALUES LESS THAN (to_days('2008-8-31')),
PARTITION p9 VALUES LESS THAN (to_days('2008-9-30')),
PARTITION p10 VALUES LESS THAN (to_days('2008-10-31')),
PARTITION p11 VALUES LESS THAN (to_days('2008-11-30')),
PARTITION p12 VALUES LESS THAN (to_days('2008-12-31')),
PARTITION p13 VALUES LESS THAN MAXVALUE
) ;

注意一点：一定要有主键，并且主键要包括分区键。

二、给已存在的表加分区

alter table 表名   
PARTITION BY RANGE (to_days(COLLECTTIME))    
(PARTITION pmin VALUES LESS THAN (to_days('2010-01-01')),   
PARTITION p201001 VALUES LESS THAN (to_days('2010-02-01')) , 
......
PARTITION pmax VALUES LESS THAN MAXVALUE );

如果表中已有数据,分区时会自动进行分区存储,不必担心数据丢失或者手动分类数据.

三、删除表中的指定分区

ALTER TABLE 表名 DROP PARTITION 分区名;

四、追加表分区

ALTER TABLE 表名 DROP PARTITION pmax;   
ALTER TABLE 表名   
ADD PARTITION (   
PARTITION p201201 VALUES LESS THAN (to_days('2012-2-1')),   
PARTITION pmax VALUES LESS THAN MAXVALUE);

五、查看标分区信息

SELECT    
        partition_name part,     
        partition_expression expr,     
        partition_description descr,     
        table_rows     
FROM    
        INFORMATION_SCHEMA.partitions     
WHERE    
        TABLE_SCHEMA = schema()    
        AND TABLE_NAME='表名';

六、查看查询语句涉及分区信息

explain partitions   
select … from 表名 where …;

相关推荐