MySQL分區(qū)表實現(xiàn)按月份歸類
發(fā)布日期：2022-02-05 13:18 | 文章來源：源碼之家
MySQL單表數(shù)據(jù)量，建議不要超過2000W行，否則會對性能有較大影響。最近接手了一個項目，單表數(shù)據(jù)超7000W行，一條簡單的查詢語句等了50多分鐘都沒出結(jié)果，實在是難受，最終，我們決定用分區(qū)表。
建表
一般的表（innodb）創(chuàng)建后只有一個 idb 文件:
create table normal_table(id int primary key, no int)
查看數(shù)據(jù)庫文件：
normal_table.ibd
創(chuàng)建按月份分區(qū)的分區(qū)表，注意！除了常規(guī)主鍵外，月份字段（用來分區(qū)的字段）也必須是主鍵：
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) );
查看數(shù)據(jù)庫文件：
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 查詢第二個分區(qū)（第二季度）的數(shù)據(jù)： select * from partition_table PARTITION(quarter2); 4 2021-04-25 tom4 5 2021-05-25 tom5 6 2021-06-25 tom6
刪除
當(dāng)刪除表時，該表的所有分區(qū)文件都會被刪除
補(bǔ)充：Mysql自動按月表分區(qū)
核心的兩個存儲過程：
- auto_create_partition為創(chuàng)建表分區(qū)，調(diào)用后為該表創(chuàng)建到下月結(jié)束的表分區(qū)。
- auto_del_partition為刪除表分區(qū)，方便歷史數(shù)據(jù)空間回收。
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 ;
下面是示例
-- 假設(shè)有個表叫records,設(shè)置分區(qū)條件為按end_time按月分區(qū)
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`;
-- 創(chuàng)建一個Event,每月執(zhí)行一次，同時最多保存6個月的數(shù)據(jù)
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以上版本來說，表分區(qū)的索引字段必須是主鍵
- 存儲過程中，DECLARE 必須緊跟著BEGIN，否則會報看不懂的錯誤
- 游標(biāo)的DECLARE需要在定義聲明之后，否則會報錯
- 如果是自己安裝的Mysql，有可能Event功能是未開啟的，在創(chuàng)建Event時會提示錯誤；修改my.cnf,在 [mysqld] 下添加event_scheduler=1后重啟即可。
到此這篇關(guān)于MySQL分區(qū)表實現(xiàn)按月份歸類的文章就介紹到這了,更多相關(guān)mysql按月表分區(qū)內(nèi)容請搜索本站以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持本站！
版權(quán)聲明：本站文章來源標(biāo)注為YINGSOO的內(nèi)容版權(quán)均為本站所有，歡迎引用、轉(zhuǎn)載，請保持原文完整并注明來源及原文鏈接。禁止復(fù)制或仿造本網(wǎng)站，禁止在非maisonbaluchon.cn所屬的服務(wù)器上建立鏡像，否則將依法追究法律責(zé)任。本站部分內(nèi)容來源于網(wǎng)友推薦、互聯(lián)網(wǎng)收集整理而來，僅供學(xué)習(xí)參考，不代表本站立場，如有內(nèi)容涉嫌侵權(quán)，請聯(lián)系alex-e#qq.com處理。
相關(guān)文章
關(guān)注官方微信