MySQL partition table can be classified by month

Source of the article ： Learn through http://www.bdgxy.com/

MySQL Single table data volume , It is recommended not to exceed 2000W That's ok , Otherwise, it will have a great impact on the performance . Recently took over a project , Single table data super 7000W That's ok , A simple query statement, etc 50 No results for more than minutes , It's really uncomfortable , Final , We decided to use partition table .

Build table

General watch （innodb） After creation, there is only one idb file :

create table normal_table(id int primary key, no int)

Look at the database file ：

normal_table.ibd  

Create a partition table partitioned by month , Be careful ！ In addition to the regular primary key , Month field （ Fields used to partition ） It must also be a primary key ：

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)
);

Look at the database file ：

partition_table#p#quarter1.ibd  
partition_table#p#quarter2.ibd  
partition_table#p#quarter3.ibd  
partition_table#p#quarter4.ibd

Insert

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");

Inquire about

select count(*) from partition_table;
> 12

Query the second partition （ The second quarter ） The data of ：
select * from partition_table PARTITION(quarter2);

4 2021-04-25 tom4
5 2021-05-25 tom5
6 2021-06-25 tom6

Delete

When you drop a table , All partition files of the table will be deleted

Add ：Mysql Automatically partition by monthly table

The two stored procedures of the core ：

• auto_create_partition Create table partitions for , After calling, create a table partition for the table until the end of next month .
• auto_del_partition Delete table partition for , Facilitate the recovery of historical data space .

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 ;

Here is an example

--  Suppose there is a table called records, Set the partition condition as end_time By month 
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;

– Create a Event, Once a month , Save up to 6 Months of data
DELIMITER $$CREATEEVENT‘record{s}_{a}ut{o}_{p}artition‘ONSCHEDULEEVERY1MONTHONCOMPLETIONPRESERVEENABLEDOBEGINcallaut{o}_{c}reat{e}_{p}artition{(}^{\prime }record{s}^{\prime });callaut{o}_{d}e{l}_{p}artition{(}^{\prime }record{s}^{\prime },6);END$$
DELIMITER ;

A few notes ：

• about Mysql 5.1 For the above version , The index field of a table partition must be a primary key
• Stored procedure ,DECLARE Must follow closely BEGIN, Otherwise, you will make mistakes that you can't understand
• Cursors DECLARE After defining the declaration , Otherwise, an error will be reported
• If it's self installed Mysql, There may be Event The function is not turned on , Creating Event An error will be prompted when ; modify my.cnf, stay [mysqld] Add below event_scheduler=1 Then restart it. .
  

This is about MySQL That's all for the article on how to classify partition tables by month , More about mysql According to the content of monthly table partition, please search rookie tutorial www.piaodoo.com Previous articles or continue to browse the relevant articles below. I hope you can support rookie tutorials in the future www.piaodoo.com！