MySQL單表數據量，建議不要超過2000W行，否則會對性能有較大影響。最近接手了一個項目，單表數據超7000W行，一條簡單的查詢語句等了50多分鐘都沒出結果，實在是難受，最終，我們決定用分區表。
建表
一般的表（innodb）創建后只有一個 idb 文件:
|
1
|
create table normal_table(id int primary key, no int) |
查看數據庫文件：
|
1
|
normal_table.ibd |
創建按月份分區的分區表，注意！除了常規主鍵外，月份字段（用來分區的字段）也必須是主鍵：
|
1
2
3
4
5
6
7
8
|
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
插入
|
1
2
3
4
5
6
7
8
9
10
11
12
|
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"); |
查詢
|
1
2
3
4
5
6
7
8
9
10
|
select count(*) from partition_table;> 12 查詢第二個分區（第二季度）的數據：select * from partition_table PARTITION(quarter2);4 2021-04-25 tom45 2021-05-25 tom56 2021-06-25 tom6 |
刪除
當刪除表時，該表的所有分區文件都會被刪除
補充：Mysql自動按月表分區
核心的兩個存儲過程：
- auto_create_partition為創建表分區，調用后為該表創建到下月結束的表分區。
- auto_del_partition為刪除表分區，方便歷史數據空間回收。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
|
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 ; |
下面是示例
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
|
-- 假設有個表叫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 PRESERVEENABLEDOBEGINcall 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按月表分區內容請搜索服務器之家以前的文章或繼續瀏覽下面的相關文章希望大家以后多多支持服務器之家！
原文鏈接：https://blog.csdn.net/qq_40310224/article/details/119921331
