mysql 分区表 统计_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

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

68f724bad5a33d9a9f6bd2ca7369ecb7.png

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值