mysql分区表

分区类型
Range
Hash，一个或多个列的Hash Key进行计算，通过这个Hash码不同数值对应的数据区域进行分区
Key，Hash模式的一种延伸
List
Composite，复合模式

#子分区(复合分区)
#在每个分区内，子分区的名字必须是唯一的，目前在整个表中，也要保持唯一。例如：
CREATE TABLE tbl_users6 (
CREATE TABLE tbl_users6 (
uuid INT NOT NULL,
name VARCHAR(20),
registerTime Date
)
PARTITION BY RANGE( YEAR(registerTime) )
SUBPARTITION BY HASH( TO_DAYS(registerTime) )
SUBPARTITIONS 2
(
PARTITION p0 VALUES LESS THAN (2008),
PARTITION p1 VALUES LESS THAN (2015),
PARTITION p2 VALUES LESS THAN MAXVALUE
);

#创建表结构
drop table if exists message_all;
CREATE TABLE message_all (
id int(10) NOT NULL AUTO_INCREMENT,
createtime datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
addr varchar(200),
PRIMARY KEY (id,createtime)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY RANGE (YEAR(createtime))
(PARTITION p2015 VALUES LESS THAN (2016) ENGINE = InnoDB,
PARTITION p2016 VALUES LESS THAN (2017) ENGINE = InnoDB,
PARTITION p2017 VALUES LESS THAN (2018) ENGINE = InnoDB,
PARTITION p2018 VALUES LESS THAN MAXVALUE ENGINE = InnoDB);

drop procedure if exists test.pro_insert;
create procedure test.pro_insert(a integer,v_int integer)
begin
declare
i integer default 1;
while i<a DO
insert into message_all select i as id,date_sub(CURRENT_TIMESTAMP(),interval v_int year) as createtime,concat(i,'-addr');
set i=i+1;
end while;
end;

#insert 200w行，每个分区各写入50w
call test.pro_insert(500000,5);
call test.pro_insert(500000,4);
call test.pro_insert(500000,3);
call test.pro_insert(500000,2);
#delete from test.message_all;
#commit;
select * from test.message_all;

#查看执行计划，分区数据
explain select * from test.message_all where createtime=cast('2015-06-10 15:16:46' as datetime);
explain select * from test.message_all where createtime='2015-06-10 15:16:46';

---------------------------------

#需要指定别名a

#mysql 按月统计默认0 获取一年12个月
#month为月份获取
#date_sub( curdate( ), INTERVAL dayofyear( curdate( ) ) - 1 DAY )为本年初第1天
SELECT
DATE_FORMAT( @date, '%Y-%m' ) date,
MONTH ( @date ) AS MONTH,
@date := date_add( @date, INTERVAL 1 MONTH )
FROM
(
SELECT
@date := ( SELECT date_sub( curdate( ), INTERVAL dayofyear( curdate( ) ) - 1 DAY ) )
FROM
test.message_all
LIMIT 12
) AS a