MySQL分區表實現按月份歸類

Posted on by

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

下面是示例

-- 假設有個表叫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 $$
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$$
DELIMITER ;

幾點註意事項：

  • 對於Mysql 5.1以上版本來說，表分區的索引字段必須是主鍵
  • 存儲過程中，DECLARE 必須緊跟著BEGIN，否則會報看不懂的錯誤
  • 遊標的DECLARE需要在定義聲明之後，否則會報錯
  • 如果是自己安裝的Mysql，有可能Event功能是未開啟的，在創建Event時會提示錯誤；修改my.cnf,在 [mysqld] 下添加event_scheduler=1後重啟即可。

到此這篇關於MySQL分區表實現按月份歸類的文章就介紹到這瞭,更多相關mysql按月表分區內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet！

推薦閱讀：