MySql表分区

查询表分区

SELECT
  partition_name part,
  partition_expression expr,
  partition_description descr,
  FROM_DAYS(partition_description) lessthan_sendtime,
  table_rows
FROM
  INFORMATION_SCHEMA. PARTITIONS
WHERE
  TABLE_SCHEMA = SCHEMA ()
AND TABLE_NAME = '表名';

分区操作

注意 分区字段create_time_必须为主键之一

ALTER TABLE `zsmy_sys_log` partition by range (to_days(`create_time_`))
(
PARTITION p20160301 VALUES LESS THAN (to_days('2016-03-01')),
PARTITION p20160401 VALUES LESS THAN (to_days('2016-04-01')) ,
PARTITION p20160501 VALUES LESS THAN (to_days('2016-05-01')) ,
PARTITION p20160601 VALUES LESS THAN (to_days('2016-06-01')) ,
PARTITION p20160701 VALUES LESS THAN (to_days('2016-07-01')) ,
PARTITION p20160801 VALUES LESS THAN (to_days('2016-08-01')) ,
PARTITION p20160901 VALUES LESS THAN (to_days('2016-09-01')) ,
PARTITION p20161001 VALUES LESS THAN (to_days('2016-10-01')) ,
PARTITION p20161101 VALUES LESS THAN (to_days('2016-11-01')) ,
PARTITION p20161201 VALUES LESS THAN (to_days('2016-12-01')) ,
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='zsmy_sys_log';  

测试效率

select * from zsmy_sys_log where create_time_ BETWEEN '2017-02-01 00:00:00' and '2017-02-28 23:59:59'

追加分区

因为用了MAXVALUE，所以只能在最后的分区里面继续分

ALTER TABLE `zsmy_sys_log` reorganize partition pmax into 
(
PARTITION p20170101 VALUES LESS THAN (to_days('2017-01-01')) ,
PARTITION p20170201 VALUES LESS THAN (to_days('2017-02-01')) ,
PARTITION pmax VALUES LESS THAN MAXVALUE 
);