文章目录[隐藏]
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, cre<i style="color:transparent">本文来源gaodai$ma#com搞$$代**码)网8</i>ate_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_partition$$
CREATE PROCEDURE `auto_del_partition`(IN `table_name` varchar(64),IN `reserved_month` int)
BEGIN
DECLARE v_finished INTEGER DEFAULT 0;
DECLARE v_part_name varchar(100) DEFAULT "";
DECLARE part_cursor CURSOR FOR
select partition_name from information_schema.partitions where table_schema = schema()
and table_name=@table_name and partition_description < TO_DAYS(CONCAT(date_format(date_sub(now(),interval reserved_month month),'%Y%m'),'01'));
DECLARE continue handler FOR
NOT FOUND SET v_finished = TRUE;
OPEN part_cursor;
read_loop: LOOP
FETCH part_cursor INTO v_part_name;
if v_finished = 1 then
leave read_loop;
end if;
SET @SQL = CONCAT( 'ALTER TABLE `', table_name, '` DROP PARTITION ', v_part_name, ";" );
PREPARE STMT FROM @SQL;
EXECUTE STMT;
DEALLOCATE PREPARE STMT;
END LOOP;
CLOSE part_cursor;
END$$
DELIMITER ;
搞代码网（gaodaima.com）提供的所有资源部分来自互联网，如果有侵犯您的版权或其他权益，请说明详细缘由并提供版权或权益证明然后发送到邮箱[email protected]，我们会在看到邮件的第一时间内为您处理，或直接联系QQ：872152909。本网站采用BY-NC-SA协议进行授权
转载请注明原文链接：MySQL分区表实现按月份归类
转载请注明原文链接：MySQL分区表实现按月份归类
