文章来源: 学习通http://www.bdgxy/
目录
- 建表
- 查看数据库文件:
- 插入
- 查询
- 删除
- 补充:Mysql自动按月表分区
MySQL单表数据量,建议不要超过2000W行,否则会对性能有较大影响。最近接手了一个项目,单表数据超7000W行,一条简单的查询语句等了50多分钟都没出结果,实在是难受,最终,我们决定用分区表。
建表
一般的表(innodb)创建后只有一个 idb 文件:
create table normal_table(id int primary key, no int)
查看数据库文件:
normal_table.ibd
创建按月份分区的分区表,注意!除了常规主键外,月份字段(用来分区的字段)也必须是主键:
create table partition_table(id int AUTO_INCREMENT, create_date date, name varchar(10),
primary key(id, create_date)) ENGINE=INNODB DEFAULT CHARSET=utf8
partition by range(month(create_date))(
partition quarter1 values less than(4),
partition quarter2 values less than(7),
partition quarter3 values less than(10),
partition quarter4 values less than(13)
);
查看数据库文件:
partition_table#p#quarter1.ibd
partition_table#p#quarter2.ibd
partition_table#p#quarter3.ibd
partition_table#p#quarter4.ibd
插入
insert into partition_table(create_date, name) values("2021-01-25", "tom1");
insert into partition_table(create_date, name) values("2021-02-25", "tom2");
insert into partition_table(create_date, name) values("2021-03-25", "tom3");
insert into partition_table(create_date, name) values("2021-04-25", "tom4");
insert into partition_table(create_date, name) values("2021-05-25", "tom5");
insert into partition_table(create_date, name) values("2021-06-25", "tom6");
insert into partition_table(create_date, name) values("2021-07-25", "tom7");
insert into partition_table(create_date, name) values("2021-08-25", "tom8");
insert into partition_table(create_date, name) values("2021-09-25", "tom9");
insert into partition_table(create_date, name) values("2021-10-25", "tom10");
insert into partition_table(create_date, name) values("2021-11-25", "tom11");
insert into partition_table(create_date, name) values("2021-12-25", "tom12");
查询
select count(*) from partition_table;
> 12
查询第二个分区(第二季度)的数据:
select * from partition_table PARTITION(quarter2);
4 2021-04-25 tom4
5 2021-05-25 tom5
6 2021-06-25 tom6
删除
当删除表时,该表的所有分区文件都会被删除
补充:Mysql自动按月表分区
核心的两个存储过程:
- auto_create_partition为创建表分区,调用后为该表创建到下月结束的表分区。
- auto_del_partition为删除表分区,方便历史数据空间回收。
DELIMITER $$
DROP PROCEDURE IF EXISTS auto_create_partition$$
CREATE PROCEDURE `auto_create_partition`(IN `table_name` varchar(64))
BEGIN
SET @next_month:=CONCAT(date_format(date_add(now(),interval 2 month),'%Y%m'),'01');
SET @SQL = CONCAT( 'ALTER TABLE `', table_name, '`',
' ADD PARTITION (PARTITION p', @next_month, " VALUES LESS THAN (TO_DAYS(",
@next_month ,")) );" );
PREPARE STMT FROM @SQL;
EXECUTE STMT;
DEALLOCATE PREPARE STMT;
END$$
DROP PROCEDURE IF EXISTS auto_del_partitionKaTeX parse error: Expected 'EOF', got '&' at position 362: …on_description &̲lt; TO_DAYS(CON…
DELIMITER ;
下面是示例
-- 假设有个表叫records,设置分区条件为按end_time按月分区
DROP TABLE IF EXISTS `records`;
CREATE TABLE `records` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`start_time` datetime NOT NULL,
`end_time` datetime NOT NULL,
`memo` varchar(128) CHARACTER SET utf8mb4 NOT NULL,
PRIMARY KEY (`id`,`end_time`)
)
PARTITION BY RANGE (TO_DAYS(end_time))(
PARTITION p20200801 VALUES LESS THAN ( TO_DAYS('20200801'))
);
DROP EVENT IF EXISTS records_auto_partition
;
– 创建一个Event,每月执行一次,同时最多保存6个月的数据
DELIMITER
C
R
E
A
T
E
E
V
E
N
T
‘
r
e
c
o
r
d
s
a
u
t
o
p
a
r
t
i
t
i
o
n
‘
O
N
S
C
H
E
D
U
L
E
E
V
E
R
Y
1
M
O
N
T
H
O
N
C
O
M
P
L
E
T
I
O
N
P
R
E
S
E
R
V
E
E
N
A
B
L
E
D
O
B
E
G
I
N
c
a
l
l
a
u
t
o
c
r
e
a
t
e
p
a
r
t
i
t
i
o
n
(
′
r
e
c
o
r
d
s
′
)
;
c
a
l
l
a
u
t
o
d
e
l
p
a
r
t
i
t
i
o
n
(
′
r
e
c
o
r
d
s
′
,
6
)
;
E
N
D
CREATE EVENT `records_auto_partition` ON SCHEDULE EVERY 1 MONTH ON COMPLETION PRESERVE ENABLE DO BEGIN call auto_create_partition('records'); call auto_del_partition('records',6); END
CREATEEVENT‘recordsautopartition‘ONSCHEDULEEVERY1MONTHONCOMPLETIONPRESERVEENABLEDOBEGINcallautocreatepartition(′records′);callautodelpartition(′records′,6);END
DELIMITER ;
几点注意事项:
- 对于Mysql 5.1以上版本来说,表分区的索引字段必须是主键
- 存储过程中,DECLARE 必须紧跟着BEGIN,否则会报看不懂的错误
- 游标的DECLARE需要在定义声明之后,否则会报错
- 如果是自己安装的Mysql,有可能Event功能是未开启的,在创建Event时会提示错误;修改myf,在 [mysqld] 下添加event_scheduler=1后重启即可。
到此这篇关于MySQL分区表实现按月份归类的文章就介绍到这了,更多相关mysql按月表分区内容请搜索菜鸟教程www.piaodoo以前的文章或继续浏览下面的相关文章希望大家以后多多支持菜鸟教程www.piaodoo!
更多推荐
MySQL分区表实现按月份归类
发布评论