mysql對曆史資料分區

mysql對曆史資料按季度分區

1.先删除原主鍵

Alter table crm_def_worksheet drop primary key;

2.對原主鍵和現有主鍵做聯合主鍵

Alter table crm_def_worksheet add primary key(`id`, `create_date`);

3.執行分區語句

ALTER TABLE crm_def_worksheet PARTITION BY RANGE (to_days(create_date))

(

PARTITION p_2018_06 VALUES LESS THAN (TO_DAYS('2018-06-01')),

PARTITION p_2018_12 VALUES LESS THAN (TO_DAYS('2018-12-01')),

PARTITION p_2019_03 VALUES LESS THAN (TO_DAYS('2019-03-01')),

PARTITION p_2019_06 VALUES LESS THAN (TO_DAYS('2019-06-01')),

PARTITION p_2019_09 VALUES LESS THAN (TO_DAYS('2019-09-01')),

PARTITION p_2019_12 VALUES LESS THAN (TO_DAYS('2019-12-01')),

PARTITION p_2020_03 VALUES LESS THAN (TO_DAYS('2020-03-01')),

PARTITION p_2020_06 VALUES LESS THAN (TO_DAYS('2020-06-01')),

PARTITION p_2020_09 VALUES LESS THAN (TO_DAYS('2020-09-01')),

PARTITION p_2020_12 VALUES LESS THAN (TO_DAYS('2020-12-01')),

PARTITION p_2021_03 VALUES LESS THAN (TO_DAYS('2021-03-01')),

PARTITION p_2021_06 VALUES LESS THAN (TO_DAYS('2021-06-01')),

PARTITION p_2021_09 VALUES LESS THAN (TO_DAYS('2021-09-01')),

PARTITION p_2021_12 VALUES LESS THAN (TO_DAYS('2021-12-01')),

PARTITION p_2022_03 VALUES LESS THAN (TO_DAYS('2022-03-01')),

PARTITION p_2022_06 VALUES LESS THAN (TO_DAYS('2022-06-01')),

PARTITION p_2022_09 VALUES LESS THAN (TO_DAYS('2022-09-01')),

PARTITION p_2022_12 VALUES LESS THAN (TO_DAYS('2022-12-01')),

PARTITION p_2023_03 VALUES LESS THAN (TO_DAYS('2023-03-01')),

PARTITION p_2023_06 VALUES LESS THAN (TO_DAYS('2023-06-01')),

PARTITION p_2023_09 VALUES LESS THAN (TO_DAYS('2023-09-01')),

PARTITION p_2023_12 VALUES LESS THAN (TO_DAYS('2023-12-01')),

PARTITION p_2024_03 VALUES LESS THAN (TO_DAYS('2024-03-01')),

PARTITION p_2024_06 VALUES LESS THAN (TO_DAYS('2024-06-01')),

PARTITION p_2024_09 VALUES LESS THAN (TO_DAYS('2024-09-01')),

PARTITION p_2024_12 VALUES LESS THAN (TO_DAYS('2024-12-01')),

PARTITION p_2025_03 VALUES LESS THAN (TO_DAYS('2025-03-01')),

PARTITION p_2025_06 VALUES LESS THAN (TO_DAYS('2025-06-01')),

PARTITION p_2025_09 VALUES LESS THAN (TO_DAYS('2025-09-01')),

PARTITION p_2025_12 VALUES LESS THAN (TO_DAYS('2025-12-01')),

PARTITION p_max VALUES LESS THAN MAXVALUE );

注意事項，如果生産資料量太大超百萬級請備份資料謹慎操作，因資料量太大，重新分區時會産生大量io影響系統正常使用，如主從延遲，鎖表時間太長導緻插入失敗等現象

————————————————

版權聲明：本文為部落客「你好龍卷風！！！」的原創文章，遵循CC 4.0 BY-SA版權協定，轉載請附上原文出處連結及本聲明。