mysql表分区

mysql中给原有表增加分区时，要注意的是往往原来的表中有主键，这个时候增加分区是会报错的，增加分区的字段值是要设置成主键的，所以这个时候要写改表结构，把要分区的字段设置成主键。

-- 按照列增加range分区
ALTER TABLE tableName PARTITION BY RANGE COLUMNS (`out_date`)
(
PARTITION p20170101 VALUES LESS THAN ('2017-01-01'),  
PARTITION p20170201 VALUES LESS THAN ('2017-02-01'),  
PARTITION p20170301 VALUES LESS THAN ('2017-03-01'),  
PARTITION p20170401 VALUES LESS THAN ('2017-04-01'),
PARTITION p20170501 VALUES LESS THAN ('2017-05-01'),
PARTITION p20170601 VALUES LESS THAN ('2017-06-01'),
PARTITION p20170701 VALUES LESS THAN ('2017-07-01'),
PARTITION p20170801 VALUES LESS THAN ('2017-08-01'),
PARTITION p20170901 VALUES LESS THAN ('2017-09-01'),
PARTITION p20171001 VALUES LESS THAN ('2017-10-01'),
PARTITION p20171101 VALUES LESS THAN ('2017-11-01'),
PARTITION p20171201 VALUES LESS THAN ('2017-12-01'),
PARTITION p20180101 VALUES LESS THAN ('2018-01-01')
);

-- 查询分区信息
select partition_name from information_schema.partitions 
where table_name = 'tableName'; 

-- 删除分区不丢失数据
Alter table tableName remove partitioning;

-- 分析分区情况

EXPLAIN PARTITIONS SELECT * FROM tableName WHERE out_date between 'xxx' and 'xxx';