自動歸檔冷資料功能Data Lifecycle Management(簡稱DLM)支援將低頻使用的冷資料定期自動地從PolarStore轉存到低成本的OSS儲存介質上,以達到降本增效的效果。
前提條件
叢集版本需滿足以下條件之一:
您可以通過查詢版本號碼,來確認叢集版本。
說明 叢集版本為PolarDB MySQL版8.0.2版本且小版本為8.0.2.2.11.1或以上時,使用DLM功能不記錄Binlog日誌。
使用DLM策略需要先開啟冷資料歸檔。
說明 如果您未開啟冷資料歸檔功能,報錯結果如下:
ERROR 8158 (HY000): [Data Lifecycle Management] DLM storage engine is not support. The value of polar_dlm_storage_mode is OFF.
使用限制
DLM功能目前僅支援分區表,分區表不能包含二級分區。分區方式需要為RANGE COLUMN類型。
暫不支援在建立全域二級索引(GSI)的分區表上使用DLM功能。
PolarDB MySQL版不支援修改DLM策略,您可以先刪除原有策略,再建立策略。
當前表上存在DLM策略時,如果執行某些DDL操作,使歸檔表和原表的表結構不一致(例如,加減列、修改列類型等),會導致後續歸檔的資料無法被解析。因此在執行此類DDL操作時,需要先刪除當前表上的DLM策略。後續如需使用資料自動歸檔功能,可以重新建立DLM策略,並指定歸檔表名為新的表名,新表名與之前歸檔的表名不能重複。
建議使用INTERVAL RANGE分區功能自動拓展分區,同時使用DLM功能將低頻使用的分區的資料歸檔到OSS上。
說明 當前僅PolarDB MySQL版8.0.2版本且小版本為8.0.2.2.0及以上的叢集支援INTERVAL RANGE分區。
注意事項
冷資料歸檔完成後,OSS上的歸檔表唯讀,且查詢效能較差。您需要提前測試資料歸檔後能否滿足您的查詢效能要求。
將分區表中的分區歸檔至OSS後,歸檔至OSS上的分區中的資料唯讀,且不支援對該分區表執行DDL操作。
執行備份操作時,不會備份已轉存至OSS上的資料,且OSS上的資料不支援按時間點恢複。
建立DLM策略
在CREATE TABLE時建立DLM策略
CREATE TABLE [IF NOT EXISTS] tbl_name
(create_definition,...)
[table_options]
[partition_options]
[dlm_add_options]
dlm_add_options:
DLM ADD
[(dlm_policy_definition [, dlm_policy_definition] ...)]
dlm_policy_definition:
POLICY policy_name
[TIER TO TABLE/TIER TO PARTITION/TIER TO NONE]
[ENGINE [=] engine_name]
[STORAGE SCHEMA_NAME [=] storage_schema_name]
[STORAGE TABLE_NAME [=] storage_table_name]
[STORAGE [=] OSS]
[READ ONLY]
[COMMENT 'comment_string']
[EXTRA_INFO 'extra_info']
ON [(PARTITIONS OVER num)]
在ALTER TABLE時建立DLM策略
ALTER TABLE tbl_name
[alter_option [, alter_option] ...]
[partition_options]
[dlm_add_options]
dlm_add_options:
DLM ADD
[(dlm_policy_definition [, dlm_policy_definition] ...)]
dlm_policy_definition:
POLICY policy_name
[TIER TO TABLE/TIER TO PARTITION/TIER TO NONE]
[ENGINE [=] engine_name]
[STORAGE SCHEMA_NAME [=] storage_schema_name]
[STORAGE TABLE_NAME [=] storage_table_name]
[STORAGE [=] OSS]
[READ ONLY]
[COMMENT 'comment_string']
[EXTRA_INFO 'extra_info']
ON [(PARTITIONS OVER num)]
DLM策略參數說明
參數 | 是否必選 | 說明 |
tbl_name | 是 | 表名稱。 |
policy_name | 是 | 策略名稱稱。 |
TIER TO TABLE | 是 | 歸檔至表。 |
TIER TO PARTITION | 是 | 將分區歸檔至OSS。
說明 該功能目前處於灰階階段。如需使用,請前往配額中心,根據配額ID polardb_mysql_hybrid_partition 找到配額名稱,在對應的操作列單擊申請來開通該功能。 僅支援將PolarDB MySQL版8.0.2版本且修訂版本為8.0.2.2.17及以上的叢集中的分區表的分區歸檔至OSS。 使用該功能時,需要確保分區表中總的分區數量不超過8192個。
|
TIER TO NONE | 是 | 直接刪除需要歸檔的資料。 |
engine_name | 否 | 歸檔資料存放的引擎,目前僅支援將資料歸檔到CSV引擎中。 |
storage_schema_name | 否 | 歸檔為表時,表所在的資料庫。預設為當前表所在資料庫。 |
storage_table_name | 否 | 歸檔為表時,表的名稱,您可以指定表名稱。預設為當前表名_當前DLM策略名稱 。 |
STORAGE [=] OSS | 否 | 歸檔後的資料存放區在OSS引擎上(預設)。 |
READ ONLY | 否 | 歸檔後的資料唯讀(預設)。 |
comment_string | 否 | DLM策略的備忘。 |
extra_info | 否 | 指目標OSS表上的OSS_FILE_FILTER資訊。 EXTRA_INFO 的格式為{"oss_file_filter":"field_filter[,field_filter]"} ,其中field_filter 的格式為:
field_filter := field_name[:filter_type]
filter_type := bloom
|
ON(PARTITIONS OVER num) | 是 | 分區數量大於num時,進行資料歸檔。 |
變更DLM策略
使DLM策略生效。
ALTER TABLE table_name DLM ENABLE POLICY [(dlm_policy_name [, dlm_policy_name] ...)]
使DLM策略失效。
ALTER TABLE table_name DLM DISABLE POLICY [(dlm_policy_name [, dlm_policy_name] ...)]
刪除DLM策略。
ALTER TABLE table_name DLM DROP POLICY [(dlm_policy_name [, dlm_policy_name] ...)]
其中,table_name
是當前表名稱,dlm_policy_name
是當前需要修改的策略名稱稱,策略名稱稱可以配置多個。
執行DLM策略
執行當前叢集上所有表上的DLM策略。
CALL dbms_dlm.execute_all_dlm_policies();
執行單張表上的DLM策略。
CALL dbms_dlm.execute_table_dlm_policies('database_name', 'table_name');
其中,database_name
是當前表所在的資料庫名稱,table_name
是當前表的表名稱。
您可以通過mysql event功能,在您的叢集營運期間執行DLM策略,既能避免在業務高峰期執行DLM而影響資料庫效能,也可以定期轉移到期資料,減少您的資料庫儲存費用。通過EVENT執行DLM策略文法:
CREATE
EVENT
[IF NOT EXISTS]
event_name
ON SCHEDULE schedule
[COMMENT 'comment']
DO event_body;
schedule: {
EVERY interval
[STARTS timestamp [+ INTERVAL interval] ...]
}
interval:
quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |
WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |
DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}
event_body: {
CALL dbms_dlm.execute_all_dlm_policies();
| CALL dbms_dlm.execute_table_dlm_policies('database_name', 'table_name');
}
參數說明如下:
參數 | 是否必選 | 說明 |
event_name | 是 | 當前EVENT的名稱。 |
schedule | 是 | 當前EVENT的執行時間和周期。 |
comment | 否 | 當前EVENT的備忘。 |
event_body | 是 | 當前EVENT具體執行的內容。需要設定為通過EVENT執行DLM策略的語句。
說明 使用CALL dbms_dlm.execute_all_dlm_policies() 時,當前EVENT會執行叢集上的所有DLM策略,所以一個叢集需要建立一個EVENT。 使用CALL dbms_dlm.execute_table_dlm_policies('database_name', 'table_name'); 時,當前EVENT僅會執行某個表上的所有DLM策略,所以每一張帶有DLM策略的表,都需要建立對應的EVENT,讓該表在指定的時間進行資料歸檔。
|
interval | 是 | EVENT的執行循環。 |
timestamp | 是 | 開始執行EVENT的時間。 |
database_name | 是 | 資料庫名稱。 |
table_name | 是 | 表名稱。 |
MySQL EVENT功能說明請參見MySQL EVENT官方文檔。
使用樣本請參見冷資料歸檔到OSS樣本。
樣本
將分區表中的資料歸檔至OSS外表
建立DLM策略
以下樣本將建立sales
分區表,該表以order_time
列作為分區鍵,按時間間隔劃分分區。同時,該表存在INTERVAL和DLM兩種策略:
建立帶有DLM策略的sales
表。
CREATE TABLE `sales` (
`id` int DEFAULT NULL,
`name` varchar(20) DEFAULT NULL,
`order_time` datetime NOT NULL,
PRIMARY KEY (`order_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
PARTITION BY RANGE COLUMNS(order_time) INTERVAL(YEAR, 1)
(PARTITION p20200101000000 VALUES LESS THAN ('2020-01-01 00:00:00') ENGINE = InnoDB,
PARTITION p20210101000000 VALUES LESS THAN ('2021-01-01 00:00:00') ENGINE = InnoDB,
PARTITION p20220101000000 VALUES LESS THAN ('2022-01-01 00:00:00') ENGINE = InnoDB)
DLM ADD POLICY test_policy TIER TO TABLE ENGINE=CSV STORAGE=OSS READ ONLY
STORAGE TABLE_NAME = 'sales_history' EXTRA_INFO '{"oss_file_filter":"id,name:bloom"}' ON (PARTITIONS OVER 3);
該表的DLM策略名稱稱為test_policy
,當分區數量大於3時,會將當前表的冷資料轉存為CSV格式,並儲存在OSS上。轉存後的表名稱為sales_history
,資料唯讀。同時,如果歸檔表OSS不存在,會自動建立OSS表,並在id
和name
列上建立OSS_FILE_FILTER。
當前表的DLM策略會儲存在系統資料表mysql.dlm_policies
中,您可以通過該表查看DLM策略的詳細資料。表mysql.dlm_policies
詳情請參見表結構說明。查看mysql.dlm_policies
表結構資訊。
mysql> SELECT * FROM mysql.dlm_policies\G
執行結果如下:
*************************** 1. row ***************************
Id: 3
Table_schema: gg
Table_name: sales
Policy_name: test_policy
Policy_type: TABLE
Archive_type: PARTITION COUNT
Storage_mode: READ ONLY
Storage_engine: CSV
Storage_media: OSS
Storage_schema_name: gg
Storage_table_name: sales_history
Data_compressed: OFF
Compressed_algorithm: NULL
Enabled: ENABLED
Priority_number: 10300
Tier_partition_number: 3
Tier_condition: NULL
Extra_info: {"oss_file_filter": "id,name:bloom,order_time"}
Comment: NULL
1 row in set (0.03 sec)
目前sales
表的分區數量為3,不會進行資料歸檔。
向sales
分區表中插入3000的測試資料,保證資料超過當前表的分區定義,觸發INTERVAL自動建立新的分區。
DROP PROCEDURE IF EXISTS proc_batch_insert;
delimiter $$
CREATE PROCEDURE proc_batch_insert(IN begin INT, IN end INT, IN name VARCHAR(20))
BEGIN
SET @insert_stmt = concat('INSERT INTO ', name, ' VALUES(? , ?, ?);');
PREPARE stmt from @insert_stmt;
WHILE begin <= end DO
SET @ID1 = begin;
SET @NAME = CONCAT(begin+begin*281313, '@stiven');
SET @TIME = from_days(begin + 737600);
EXECUTE stmt using @ID1, @NAME, @TIME;
SET begin = begin + 1;
END WHILE;
END;
$$
delimiter ;
CALL proc_batch_insert(1, 3000, 'sales');
此時,觸發了INTERVAL自動建立新的分區,導致sales
表的分區增加,表結構變為:
mysql> SHOW CREATE TABLE sales\G
執行結果如下:
*************************** 1. row ***************************
Table: sales
Create Table: CREATE TABLE `sales` (
`id` int(11) DEFAULT NULL,
`name` varchar(20) DEFAULT NULL,
`order_time` datetime NOT NULL,
PRIMARY KEY (`order_time`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50500 PARTITION BY RANGE COLUMNS(order_time) */
/*!99990 800020200 INTERVAL(YEAR, 1) */
/*!50500 (PARTITION p20200101000000 VALUES LESS THAN ('2020-01-01 00:00:00') ENGINE = InnoDB,
PARTITION p20210101000000 VALUES LESS THAN ('2021-01-01 00:00:00') ENGINE = InnoDB,
PARTITION p20220101000000 VALUES LESS THAN ('2022-01-01 00:00:00') ENGINE = InnoDB,
PARTITION _p20230101000000 VALUES LESS THAN ('2023-01-01 00:00:00') ENGINE = InnoDB,
PARTITION _p20240101000000 VALUES LESS THAN ('2024-01-01 00:00:00') ENGINE = InnoDB,
PARTITION _p20250101000000 VALUES LESS THAN ('2025-01-01 00:00:00') ENGINE = InnoDB,
PARTITION _p20260101000000 VALUES LESS THAN ('2026-01-01 00:00:00') ENGINE = InnoDB,
PARTITION _p20270101000000 VALUES LESS THAN ('2027-01-01 00:00:00') ENGINE = InnoDB,
PARTITION _p20280101000000 VALUES LESS THAN ('2028-01-01 00:00:00') ENGINE = InnoDB) */
1 row in set (0.03 sec)
已經建立了新的分區,且分區數量大於3,滿足DLM策略的執行條件,可以進行資料歸檔。
執行DLM策略
您可以通過SQL語句直接執行DLM策略,或者通過MySQL的EVENT功能,定期執行DLM策略。假設從2022-10-11開始,您的叢集營運開始時間是每天淩晨1點,則每天淩晨1點開始執行DLM策略。建立對應的執行EVENT如下:
CREATE EVENT dlm_system_base_event
ON SCHEDULE EVERY 1 DAY
STARTS '2022-10-11 01:00:00'
do CALL
dbms_dlm.execute_all_dlm_policies();
1點後,這個EVENT會執行所有表上的DLM策略。
執行以下命令,查看sales
表結構資訊。
mysql> SHOW CREATE TABLE sales\G
執行結果如下:
*************************** 1. row ***************************;
Table: sales
Create Table: CREATE TABLE `sales` (
`id` int(11) DEFAULT NULL,
`name` varchar(20) DEFAULT NULL,
`order_time` datetime NOT NULL,
PRIMARY KEY (`order_time`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50500 PARTITION BY RANGE COLUMNS(order_time) */
/*!99990 800020200 INTERVAL(YEAR, 1) */
/*!50500 (PARTITION _p20260101000000 VALUES LESS THAN ('2026-01-01 00:00:00') ENGINE = InnoDB,
PARTITION _p20270101000000 VALUES LESS THAN ('2027-01-01 00:00:00') ENGINE = InnoDB,
PARTITION _p20280101000000 VALUES LESS THAN ('2028-01-01 00:00:00') ENGINE = InnoDB) */
1 row in set (0.03 sec)
當前表僅剩3個分區。
您可以通過mysql.dlm_progress
表查看DLM策略的執行記錄,表dlm_progress
定義請參見表結構說明。執行以下命令,查看mysql.dlm_progress
表結構資訊。
mysql> SELECT * FROM mysql.dlm_progress\G
執行結果如下:
*************************** 1. row ***************************;
Id: 1
Table_schema: test
Table_name: sales
Policy_name: test_policy
Policy_type: TABLE
Archive_option: PARTITIONS OVER 3
Storage_engine: CSV
Storage_media: OSS
Data_compressed: OFF
Compressed_algorithm: NULL
Archive_partitions: p20200101000000, p20210101000000, p20220101000000, _p20230101000000, _p20240101000000, _p20250101000000
Archive_stage: ARCHIVE_COMPLETE
Archive_percentage: 0
Archived_file_info: null
Start_time: 2024-07-26 17:56:20
End_time: 2024-07-26 17:56:50
Extra_info: null
1 row in set (0.00 sec)
存放低頻冷資料的分區,包括p20200101000000、p20210101000000、p20220101000000、_p20230101000000、 _p20240101000000、 _p20250101000000都已轉存到OSS外表上。
執行以下命令,查看OSS外表的表結構。
mysql> SHOW CREATE TABLE sales_history\G
執行結果如下:
*************************** 1. row ***************************;
Table: sales_history
Create Table: CREATE TABLE `sales_history` (
`id` int(11) DEFAULT NULL,
`name` varchar(20) DEFAULT NULL,
`order_time` datetime DEFAULT NULL,
PRIMARY KEY (`order_time`)
) /*!99990 800020213 STORAGE OSS */ ENGINE=CSV DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!99990 800020204 NULL_MARKER='NULL' */ /*!99990 800020223 OSS META=1 */ /*!99990 800020224 OSS_FILE_FILTER='id,name:bloom,order_time' */
1 row in set (0.15 sec)
該表變為資料存放區在OSS引擎的CSV表,查詢方法與查詢本地表的方法相同。同時,指定的列已經加入到OSS_FILE_FILTER中,並且由於order_time
是分區鍵,也會自動建立 OSS_FILE_FILTER。
分別查詢sales
和sales_history
兩張表上的資料。
SELECT COUNT(*) FROM sales;
+----------+
| count(*) |
+----------+
| 984 |
+----------+
1 row in set (0.01 sec)
SELECT COUNT(*) FROM sales_history;
+----------+
| count(*) |
+----------+
| 2016 |
+----------+
1 row in set (0.57 sec)
可以看到資料總和正好為3000,與開始時sales
表插入的資料量一致。
在OSS外表上通過OSS_FILE_FILTER查詢(需要開啟OSS_FILE_FILTER開關):
mysql> explain select * from sales_history where id = 9;
+----+-------------+---------------+------------+------+---------------+------+---------+------+------+----------+-----------------------------------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------------+------------+------+---------------+------+---------+------+------+----------+-----------------------------------------------------------------------------+
| 1 | SIMPLE | sales_history | NULL | ALL | NULL | NULL | NULL | NULL | 2016 | 10.00 | Using where; With pushed engine condition (`test`.`sales_history`.`id` = 9) |
+----+-------------+---------------+------------+------+---------------+------+---------+------+------+----------+-----------------------------------------------------------------------------+
1 row in set, 1 warning (0.59 sec)
mysql> select * from sales_history where id = 9;
+------+----------------+---------------------+
| id | name | order_time |
+------+----------------+---------------------+
| 9 | 2531826@stiven | 2019-07-04 00:00:00 |
+------+----------------+---------------------+
1 row in set (0.19 sec)
將分區表中的分區歸檔至OSS
建立DLM策略
以下樣本將建立一張表名為sales
的分區表,該表以order_time
列作為分區鍵,按時間間隔劃分分區。同時,該表存在INTERVAL和DLM兩種策略:
建立sales
表。
CREATE TABLE `sales` (
`id` int DEFAULT NULL,
`name` varchar(20) DEFAULT NULL,
`order_time` datetime NOT NULL,
PRIMARY KEY (`order_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
PARTITION BY RANGE COLUMNS(order_time) INTERVAL(YEAR, 1)
(PARTITION p20200101000000 VALUES LESS THAN ('2020-01-01 00:00:00') ENGINE = InnoDB,
PARTITION p20210101000000 VALUES LESS THAN ('2021-01-01 00:00:00') ENGINE = InnoDB,
PARTITION p20220101000000 VALUES LESS THAN ('2022-01-01 00:00:00') ENGINE = InnoDB)
DLM ADD POLICY policy_part2part TIER TO PARTITION ENGINE=CSV STORAGE=OSS READ ONLY ON (PARTITIONS OVER 3);
該表的DLM策略名稱稱為policy_part2part
,當分區數量大於3時,會將舊的分區轉存至OSS。
在mysql.dlm_policies
表中查看DLM策略。
SELECT * FROM mysql.dlm_policies\G
執行結果如下:
*************************** 1. row ***************************
Id: 2
Table_schema: test
Table_name: sales
Policy_name: policy_part2part
Policy_type: PARTITION
Archive_type: PARTITION COUNT
Storage_mode: READ ONLY
Storage_engine: CSV
Storage_media: OSS
Storage_schema_name: NULL
Storage_table_name: NULL
Data_compressed: OFF
Compressed_algorithm: NULL
Enabled: ENABLED
Priority_number: 10300
Tier_partition_number: 3
Tier_condition: NULL
Extra_info: null
Comment: NULL
1 row in set (0.03 sec)
使用proc_batch_insert
預存程序向sales
分區表中插入一定的測試資料,以觸發INTERVAL策略來自動建立新的分區。
CALL proc_batch_insert(1, 3000, 'sales');
執行結果如下,表示資料插入成功:
Query OK, 1 row affected, 1 warning (0.99 sec)
執行以下命令,查看sales
表的結構資訊。
SHOW CREATE TABLE sales \G
執行結果如下:
*************************** 1. row ***************************
Table: sales
Create Table: CREATE TABLE `sales` (
`id` int(11) DEFAULT NULL,
`name` varchar(20) DEFAULT NULL,
`order_time` datetime DEFAULT NULL,
PRIMARY KEY (`order_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50500 PARTITION BY RANGE COLUMNS(order_time) */ /*!99990 800020200 INTERVAL(YEAR, 1) */
/*!50500 (PARTITION p20200101000000 VALUES LESS THAN ('2020-01-01 00:00:00') ENGINE = InnoDB,
PARTITION p20210101000000 VALUES LESS THAN ('2021-01-01 00:00:00') ENGINE = InnoDB,
PARTITION p20220101000000 VALUES LESS THAN ('2022-01-01 00:00:00') ENGINE = InnoDB,
PARTITION _p20230101000000 VALUES LESS THAN ('2023-01-01 00:00:00') ENGINE = InnoDB,
PARTITION _p20240101000000 VALUES LESS THAN ('2024-01-01 00:00:00') ENGINE = InnoDB,
PARTITION _p20250101000000 VALUES LESS THAN ('2025-01-01 00:00:00') ENGINE = InnoDB,
PARTITION _p20260101000000 VALUES LESS THAN ('2026-01-01 00:00:00') ENGINE = InnoDB,
PARTITION _p20270101000000 VALUES LESS THAN ('2027-01-01 00:00:00') ENGINE = InnoDB,
PARTITION _p20280101000000 VALUES LESS THAN ('2028-01-01 00:00:00') ENGINE = InnoDB) */
1 row in set (0.03 sec)
執行DLM策略
通過以下命令來執行DLM策略。
CALL dbms_dlm.execute_all_dlm_policies();
通過mysql.dlm_progress
表查看DLM執行記錄。
SELECT * FROM mysql.dlm_progress \G
執行結果如下:
*************************** 1. row ***************************
Id: 4
Table_schema: test
Table_name: sales
Policy_name: policy_part2part
Policy_type: PARTITION
Archive_option: PARTITIONS OVER 3
Storage_engine: CSV
Storage_media: OSS
Data_compressed: OFF
Compressed_algorithm: NULL
Archive_partitions: p20200101000000, p20210101000000, p20220101000000, _p20230101000000, _p20240101000000, _p20250101000000
Archive_stage: ARCHIVE_COMPLETE
Archive_percentage: 100
Archived_file_info: null
Start_time: 2023-09-11 18:04:39
End_time: 2023-09-11 18:04:40
Extra_info: null
1 row in set (0.02 sec)
執行以下命令,查看sales
表結構資訊。
SHOW CREATE TABLE sales \G
執行結果如下:
*************************** 1. row ***************************
Table: sales
Create Table: CREATE TABLE `sales` (
`id` int(11) DEFAULT NULL,
`name` varchar(20) DEFAULT NULL,
`order_time` datetime DEFAULT NULL,
PRIMARY KEY (`order_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci CONNECTION='default_oss_server'
/*!99990 800020205 PARTITION BY RANGE COLUMNS(order_time) */ /*!99990 800020200 INTERVAL(YEAR, 1) */
/*!99990 800020205 (PARTITION p20200101000000 VALUES LESS THAN ('2020-01-01 00:00:00') ENGINE = CSV,
PARTITION p20210101000000 VALUES LESS THAN ('2021-01-01 00:00:00') ENGINE = CSV,
PARTITION p20220101000000 VALUES LESS THAN ('2022-01-01 00:00:00') ENGINE = CSV,
PARTITION _p20230101000000 VALUES LESS THAN ('2023-01-01 00:00:00') ENGINE = CSV,
PARTITION _p20240101000000 VALUES LESS THAN ('2024-01-01 00:00:00') ENGINE = CSV,
PARTITION _p20250101000000 VALUES LESS THAN ('2025-01-01 00:00:00') ENGINE = CSV,
PARTITION _p20260101000000 VALUES LESS THAN ('2026-01-01 00:00:00') ENGINE = InnoDB,
PARTITION _p20270101000000 VALUES LESS THAN ('2027-01-01 00:00:00') ENGINE = InnoDB,
PARTITION _p20280101000000 VALUES LESS THAN ('2028-01-01 00:00:00') ENGINE = InnoDB) */
1 row in set (0.03 sec)
由查詢到的表結構資訊可以看出,sales
分區表中的p20200101000000
、p20210101000000
、p20220101000000
、_p20230101000000
、_p20240101000000
以及_p20250101000000
分區均轉存至OSS,InnoDB引擎中僅保留了3個熱資料分區_p20260101000000
、_p20270101000000
和_p20280101000000
。sales
表變為了混合分區表,查詢混合分區表中的資料操作方法請參見查詢混合分區。
將冷資料直接刪除
建立DLM策略
以下樣本將建立sales
分區表,該表以order_time
列作為分區鍵,按時間間隔劃分分區。同時,該表存在INTERVAL和DLM兩種策略:
建立帶有DLM策略的sales
表。
CREATE TABLE `sales` (
`id` int DEFAULT NULL,
`name` varchar(20) DEFAULT NULL,
`order_time` datetime DEFAULT NULL,
PRIMARY KEY (`order_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
PARTITION BY RANGE COLUMNS(order_time) INTERVAL(YEAR, 1)
(PARTITION p20200101000000 VALUES LESS THAN ('2020-01-01 00:00:00') ENGINE = InnoDB,
PARTITION p20210101000000 VALUES LESS THAN ('2021-01-01 00:00:00') ENGINE = InnoDB,
PARTITION p20220101000000 VALUES LESS THAN ('2022-01-01 00:00:00') ENGINE = InnoDB)
DLM ADD POLICY test_policy TIER TO NONE ON (PARTITIONS OVER 3);
該表的DLM策略名稱稱為test_policy
,策略的執行條件為分區數量大於3。執行DLM策略時會直接刪除冷資料。
執行以下命令,查看mysql.dlm_policies
表。
SELECT * FROM mysql.dlm_policies\G
執行結果如下:
*************************** 1. row ***************************
Id: 4
Table_schema: test
Table_name: sales
Policy_name: test_policy
Policy_type: NONE
Archive_type: PARTITION COUNT
Storage_mode: NULL
Storage_engine: NULL
Storage_media: NULL
Storage_schema_name: NULL
Storage_table_name: NULL
Data_compressed: OFF
Compressed_algorithm: NULL
Enabled: ENABLED
Priority_number: 50000
Tier_partition_number: 3
Tier_condition: NULL
Extra_info: null
Comment: NULL
1 row in set (0.01 sec)
向sales
分區表中插入一定的測試資料,以觸發INTERVAL自動建立新的分區。使用proc_batch_insert
預存程序插入新的資料。表結構如下:
CALL proc_batch_insert(1, 3000, 'sales');
Query OK, 1 row affected, 1 warning (0.99 sec)
執行如下命令,查看sales
表結構資訊。
SHOW CREATE TABLE sales \G
執行結果如下:
*************************** 1. row ***************************
Table: sales
Create Table: CREATE TABLE `sales` (
`id` int(11) DEFAULT NULL,
`name` varchar(20) DEFAULT NULL,
`order_time` datetime DEFAULT NULL,
PRIMARY KEY (`order_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50500 PARTITION BY RANGE COLUMNS(order_time) */ /*!99990 800020200 INTERVAL(YEAR, 1) */
/*!50500 (PARTITION p20200101000000 VALUES LESS THAN ('2020-01-01 00:00:00') ENGINE = InnoDB,
PARTITION p20210101000000 VALUES LESS THAN ('2021-01-01 00:00:00') ENGINE = InnoDB,
PARTITION p20220101000000 VALUES LESS THAN ('2022-01-01 00:00:00') ENGINE = InnoDB,
PARTITION _p20230101000000 VALUES LESS THAN ('2023-01-01 00:00:00') ENGINE = InnoDB,
PARTITION _p20240101000000 VALUES LESS THAN ('2024-01-01 00:00:00') ENGINE = InnoDB,
PARTITION _p20250101000000 VALUES LESS THAN ('2025-01-01 00:00:00') ENGINE = InnoDB,
PARTITION _p20260101000000 VALUES LESS THAN ('2026-01-01 00:00:00') ENGINE = InnoDB,
PARTITION _p20270101000000 VALUES LESS THAN ('2027-01-01 00:00:00') ENGINE = InnoDB,
PARTITION _p20280101000000 VALUES LESS THAN ('2028-01-01 00:00:00') ENGINE = InnoDB) */
1 row in set (0.03 sec)
執行DLM策略
通過以下命令直接執行DLM策略。
CALL dbms_dlm.execute_all_dlm_policies();
DLM策略執行時,查看mysql.dlm_progress
表中資料。
SELECT * FROM mysql.dlm_progress \G
表中結果如下:
*************************** 1. row ***************************
Id: 1
Table_schema: test
Table_name: sales
Policy_name: test_policy
Policy_type: NONE
Archive_option: PARTITIONS OVER 3
Storage_engine: NULL
Storage_media: NULL
Data_compressed: OFF
Compressed_algorithm: NULL
Archive_partitions: p20200101000000, p20210101000000, p20220101000000, _p20230101000000, _p20240101000000, _p20250101000000
Archive_stage: ARCHIVE_COMPLETE
Archive_percentage: 100
Archived_file_info: null
Start_time: 2023-01-09 17:31:24
End_time: 2023-01-09 17:31:24
Extra_info: null
1 row in set (0.03 sec)
存放低頻冷資料的分區,包括p20200101000000
、p20210101000000
、p20220101000000
、_p20230101000000
、_p20240101000000
和_p20250101000000
都已被刪除。
sales
表結構如下:
SHOW CREATE TABLE sales \G
執行結果如下:
*************************** 1. row ***************************
Table: sales
Create Table: CREATE TABLE `sales` (
`id` int(11) DEFAULT NULL,
`name` varchar(20) DEFAULT NULL,
`order_time` datetime DEFAULT NULL,
PRIMARY KEY (`order_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50500 PARTITION BY RANGE COLUMNS(order_time) */ /*!99990 800020200 INTERVAL(YEAR, 1) */
/*!50500 (PARTITION _p20260101000000 VALUES LESS THAN ('2026-01-01 00:00:00') ENGINE = InnoDB,
PARTITION _p20270101000000 VALUES LESS THAN ('2027-01-01 00:00:00') ENGINE = InnoDB,
PARTITION _p20280101000000 VALUES LESS THAN ('2028-01-01 00:00:00') ENGINE = InnoDB) */
1 row in set (0.02 sec)
在ALTER TABLE時建立或刪除DLM策略
在ALTER TABLE
時建立DLM策略。
ALTER TABLE t DLM ADD POLICY test_policy TIER TO TABLE ENGINE=CSV STORAGE=OSS READ ONLY
STORAGE TABLE_NAME = 'sales_history' ON (PARTITIONS OVER 3);
表t
的DLM策略名稱稱為test_policy
,策略的執行條件為分區數量大於3。執行該DLM策略且分區數量大於3時,會將表t
中舊的分區中的資料歸檔至OSS,且歸檔在OSS的表名為sales_history
。
讓表t
上的DLM策略test_policy
生效。
ALTER TABLE t DLM ENABLE POLICY test_policy;
讓表t
上的DLM策略test_policy
失效。
ALTER TABLE t DLM DISABLE POLICY test_policy;
刪除表t
上的DLM策略test_policy
。
ALTER TABLE t DLM DROP POLICY test_policy;
執行錯誤處理
DLM策略執行過程中,可能因為配置原因,出現DLM策略執行錯誤的情況。此時,錯誤記錄會儲存在mysql.dlm_progress
表中。通過如下命令查看錯誤記錄:
SELECT * FROM mysql.dlm_progress WHERE Archive_stage = "ARCHIVE_ERROR";
在Extra_info欄位中找到錯誤的詳細資料,確認錯誤原因後,刪除目前記錄或者修改目前記錄的Archive_stage為ARCHIVE_COMPLETE,再通過call dbms_dlm.execute_all_dlm_policies;
命令手動執行DLM策略或者等待下一個執行循環自動執行。
說明 如果當前策略的執行記錄中,存在狀態為ARCHIVE_ERROR的記錄,考慮到資料安全的原因,該條策略不會再自動執行,您需要在確認執行失敗原因並修改對應的記錄後,該條策略才會繼續執行。