Mysql 分区常用sql

查询需求语句

SELECT SQL_NO_CACHE count(*) sum, award_id 
FROM `yx_sweepstake` 
WHERE `time` BETWEEN 1508889500 AND 1510047200 GROUP BY award_id;

查询分区情况

SELECT PARTITION_NAME,PARTITION_METHOD,PARTITION_EXPRESSION,
PARTITION_DESCRIPTION,TABLE_ROWS,SUBPARTITION_NAME,
SUBPARTITION_METHOD,SUBPARTITION_EXPRESSION
FROM information_schema.PARTITIONS
WHERE TABLE_SCHEMA=SCHEMA() AND TABLE_NAME='yx_sweepstake';

删除分区

ALTER TABLE yx_sweepstake REMOVE PARTITIONING;

添加范围分区

ALTER TABLE yx_sweepstake PARTITION BY RANGE(time div 100000)(
         PARTITION p15089 VALUES LESS THAN (15089),
         PARTITION p15090 VALUES LESS THAN (15090),
         PARTITION p0 VALUES LESS THAN (MAXVALUE)
);

追加分区

ALTER TABLE yx_sweepstake PARTITION by range(time div 100000)(
         partition par0 values less than (15089)
);

评论

暂无评论