MySQL表分区效能基础

mysql表分区功能基础
创建分区表

    CREATE TABLE `表名` (  
        `EQUIPMENTID` char(17) NOT NULL,  
        `ATTRIBUTEID` char(4) NOT NULL,  
        `VALUE` varchar(20) NOT NULL,  
        `COLLECTTIME` datetime NOT NULL 
    )   
    ENGINE=InnoDB (适用大部分引擎,可根据需要调整)  
    DEFAULT CHARSET=gbk(编码可根据需要修改)   
    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 p201002 VALUES LESS THAN (to_days(‘2010-03-01’)) ,  
    PARTITION p201003 VALUES LESS THAN (to_days(‘2010-04-01’)) ,   
    PARTITION p201004 VALUES LESS THAN (to_days(‘2010-05-01’)) ,  
    PARTITION p201005 VALUES LESS THAN (to_days(‘2010-06-01’)) ,   
    PARTITION p201006 VALUES LESS THAN (to_days(‘2010-07-01’)) ,  
    PARTITION p201007 VALUES LESS THAN (to_days(‘2010-08-01’)) ,   
    PARTITION p201008 VALUES LESS THAN (to_days(‘2010-09-01’)) ,  
    PARTITION p201009 VALUES LESS THAN (to_days(‘2010-10-01’)) ,   
    PARTITION p201010 VALUES LESS THAN (to_days(‘2010-11-01’)),  
    PARTITION p201011 VALUES LESS THAN (to_days(‘2010-12-01’)),  
    PARTITION p201012 VALUES LESS THAN (to_days(‘2011-01-01’)),  
    PARTITION p201101 VALUES LESS THAN (to_days(‘2011-02-01’)),  
    PARTITION p201102 VALUES LESS THAN (to_days(‘2011-03-01’)),  
    PARTITION p201103 VALUES LESS THAN (to_days(‘2011-04-01’)),  
    PARTITION p201104 VALUES LESS THAN (to_days(‘2011-05-01’)),  
    PARTITION p201105 VALUES LESS THAN (to_days(‘2011-06-01’)),  
    PARTITION p201106 VALUES LESS THAN (to_days(‘2011-07-01’)),  
    PARTITION p201107 VALUES LESS THAN (to_days(‘2011-08-01’)),  
    PARTITION p201108 VALUES LESS THAN (to_days(‘2011-09-01’)),  
    PARTITION p201109 VALUES LESS THAN (to_days(‘2011-10-01’)),  
    PARTITION p201110 VALUES LESS THAN (to_days(‘2011-11-01’)),  
    PARTITION p201111 VALUES LESS THAN (to_days(‘2011-12-01’)),  
    PARTITION p201112 VALUES LESS THAN (to_days(‘2012-01-01’)),  
    PARTITION pmax 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 p201002 VALUES LESS THAN (to_days(‘2010-03-01’)) ,  
    PARTITION p201003 VALUES LESS THAN (to_days(‘2010-04-01’)) ,   
    PARTITION p201004 VALUES LESS THAN (to_days(‘2010-05-01’)) ,  
    PARTITION p201005 VALUES LESS THAN (to_days(‘2010-06-01’)) ,   
    PARTITION p201006 VALUES LESS THAN (to_days(‘2010-07-01’)) ,  
    PARTITION p201007 VALUES LESS THAN (to_days(‘2010-08-01’)) ,   
    PARTITION p201008 VALUES LESS THAN (to_days(‘2010-09-01’)) ,  
    PARTITION p201009 VALUES LESS THAN (to_days(‘2010-10-01’)) ,   
    PARTITION p201010 VALUES LESS THAN (to_days(‘2010-11-01’)),  
    PARTITION p201011 VALUES LESS THAN (to_days(‘2010-12-01’)),  
    PARTITION p201012 VALUES LESS THAN (to_days(‘2011-01-01’)),  
    PARTITION p201101 VALUES LESS THAN (to_days(‘2011-02-01’)),  
    PARTITION p201102 VALUES LESS THAN (to_days(‘2011-03-01’)),  
    PARTITION p201103 VALUES LESS THAN (to_days(‘2011-04-01’)),  
    PARTITION p201104 VALUES LESS THAN (to_days(‘2011-05-01’)),  
    PARTITION p201105 VALUES LESS THAN (to_days(‘2011-06-01’)),  
    PARTITION p201106 VALUES LESS THAN (to_days(‘2011-07-01’)),  
    PARTITION p201107 VALUES LESS THAN (to_days(‘2011-08-01’)),  
    PARTITION p201108 VALUES LESS THAN (to_days(‘2011-09-01’)),  
    PARTITION p201109 VALUES LESS THAN (to_days(‘2011-10-01’)),  
    PARTITION p201110 VALUES LESS THAN (to_days(‘2011-11-01’)),  
    PARTITION p201111 VALUES LESS THAN (to_days(‘2011-12-01’)),  
    PARTITION p201112 VALUES LESS THAN (to_days(‘2012-01-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 …;  

该操作只查看查询语句相关的分区信息,不会返回查询结果.

喜欢 (0)赏

[搞代码]

分享 (0)