MySQL按日期月份分区实例解析

.分区相关SQL

/*查看数据表分区信息*/

EXPLAIN PARTITIONS SELECT * FROM [tablename] i where i.timeBETWEEN '2015-06-01 00:00:00' and '2015-06-31 00:00:00';

 

/*查询指定表的分区信息*/

select partition_name part,partition_expressionexpr,partition_description descr,table_rows
       from information_schema.partitions
       where TABLE_SCHEMA =schema() and TABLE_NAME='[tablename]';

/*重建指定表的指定分区*/

ALTER TABLE [tablename] REBUILD PARTITION[partitionname1,partitionname2,……]

 

/*整理表空间碎片*/

OPTIMIZE TABLE tablename（必须是多表空