MySQL分区表实现按月份归类

文章来源： 学习通http://www.bdgxy.com/

目录

• 建表
  
• 查看数据库文件：
  
• 插入
  
• 查询
  
• 删除
  
• 补充：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` v