文档章节

MariaDB自动分区维护

分秒
 分秒
发布于 2017/09/11 18:26
字数 4338
阅读 11
收藏 0
点赞 0
评论 0

最近遇到一个问题,就是如何自动删除6个月之前的旧的分区。就MariaDB而言,并没有类似机制,可以让我们开箱即用,去完成这件事情。所以我们需要换一种思路,或许问题就可以很简单的被解决。我们可以创建一个存储过程和一个事件,然后按照预定的时间表来调用这个存储过程。实际上,我们还可以进一步创建一个存储过程,让该过程自动添加新的分区。

在本篇文章中,我们将展示如何编写执行这些任务的存储过程。

分区表的定义

在该演示中,我们将使用MySQL文档RANGE分区示例给出的数据表,稍做一些改动:

DROP TABLE IF EXISTS db1.quarterly_report_status;
CREATE TABLE db1.quarterly_report_status (
   report_id INT NOT NULL,
   report_status VARCHAR(20) NOT NULL,
   report_updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB
PARTITION BY RANGE ( UNIX_TIMESTAMP(report_updated) ) (
   PARTITION p_first VALUES LESS THAN ( UNIX_TIMESTAMP('2016-10-01 00:00:00')),
   PARTITION p201610 VALUES LESS THAN ( UNIX_TIMESTAMP('2016-11-01 00:00:00')),
   PARTITION p201611 VALUES LESS THAN ( UNIX_TIMESTAMP('2016-12-01 00:00:00')),
   PARTITION p201612 VALUES LESS THAN ( UNIX_TIMESTAMP('2017-01-01 00:00:00')),
   PARTITION p201701 VALUES LESS THAN ( UNIX_TIMESTAMP('2017-02-01 00:00:00')),
   PARTITION p201702 VALUES LESS THAN ( UNIX_TIMESTAMP('2017-03-01 00:00:00')),
   PARTITION p201703 VALUES LESS THAN ( UNIX_TIMESTAMP('2017-04-01 00:00:00')),
   PARTITION p201704 VALUES LESS THAN ( UNIX_TIMESTAMP('2017-05-01 00:00:00')),
   PARTITION p201705 VALUES LESS THAN ( UNIX_TIMESTAMP('2017-06-01 00:00:00')),
   PARTITION p201706 VALUES LESS THAN ( UNIX_TIMESTAMP('2017-07-01 00:00:00')),
   PARTITION p201707 VALUES LESS THAN ( UNIX_TIMESTAMP('2017-08-01 00:00:00')),
   PARTITION p201708 VALUES LESS THAN ( UNIX_TIMESTAMP('2017-09-01 00:00:00')),
   PARTITION p_future VALUES LESS THAN (MAXVALUE)
);

最重要的改动是分区命名方案是基于日期的。这将使我们更容易地确定要删除哪些分区。

存储过程定义(创建新的分区)

存储过程本身也会包含一些注释,来说明它所作的事情。需要提出说明的是,我们没有使用 ALTER TABLE ... ADD PARTITION 这样的语句,因为 p_future 分区已经覆盖了到 MAXVALUE 的结束范围。所以我们需要使用 ALTER TABLE ... REORGANIZE PARTITION 语句来代替。

DROP PROCEDURE IF EXISTS db1.create_new_partitions;
DELIMITER $
CREATE PROCEDURE db1.create_new_partitions(p_schema varchar(64), p_table varchar(64), p_months_to_add int)
   LANGUAGE SQL
   NOT DETERMINISTIC
   SQL SECURITY INVOKER
BEGIN  
   DECLARE done INT DEFAULT FALSE;
   DECLARE current_partition_name varchar(64);
   DECLARE current_partition_ts int;
   -- We'll use this cursor later to check
   -- whether a particular already exists.
   -- @partition_name_to_add will be
   -- set later.
   DECLARE cur1 CURSOR FOR 
   SELECT partition_name 
   FROM information_schema.partitions 
   WHERE TABLE_SCHEMA = p_schema 
   AND TABLE_NAME = p_table 
   AND PARTITION_NAME != 'p_first'
   AND PARTITION_NAME != 'p_future'
   AND PARTITION_NAME = @partition_name_to_add;
   -- We'll also use this cursor later 
   -- to query our temporary table.
   DECLARE cur2 CURSOR FOR 
   SELECT partition_name, partition_range_ts 
   FROM partitions_to_add;
   DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
   DROP TEMPORARY TABLE IF EXISTS partitions_to_add;
   CREATE TEMPORARY TABLE partitions_to_add (
      partition_name varchar(64),
      partition_range_ts int
   );
   SET @partitions_added = FALSE;
   SET @months_ahead = 0;
   -- Let's go through a loop and add each month individually between
   -- the current month and the month p_months_to_add in the future.
   WHILE @months_ahead <= p_months_to_add DO
      -- We figure out what the correct month is by adding the
      -- number of months to the current date
      SET @date = CURDATE();
      SET @q = 'SELECT DATE_ADD(?, INTERVAL ? MONTH) INTO @month_to_add';
      PREPARE st FROM @q;
      EXECUTE st USING @date, @months_ahead;
      DEALLOCATE PREPARE st;
      SET @months_ahead = @months_ahead + 1;
      -- Then we format the month in the same format used
      -- in our partition names.
      SET @q = 'SELECT DATE_FORMAT(@month_to_add, ''%Y%m'') INTO @formatted_month_to_add';
      PREPARE st FROM @q;
      EXECUTE st;
      DEALLOCATE PREPARE st;
      -- And then we use the formatted date to build the name of the
      -- partition that we want to add. This partition name is
      -- assigned to @partition_name_to_add, which is used in
      -- the cursor declared at the start of the procedure.
      SET @q = 'SELECT CONCAT(''p'', @formatted_month_to_add) INTO @partition_name_to_add';
      PREPARE st FROM @q;
      EXECUTE st;
      DEALLOCATE PREPARE st;
      SET done = FALSE; 
      SET @first = TRUE;
      -- And then we loop through the results returned by the cursor,
      -- and if a row already exists for the current partition, 
      -- then we do not need to create the partition.
      OPEN cur1;
      read_loop: LOOP
         FETCH cur1 INTO current_partition_name;
         -- The cursor returned 0 rows, so we can create the partition.
         IF done AND @first THEN
            SELECT CONCAT('Creating partition: ', @partition_name_to_add);
            -- Now we need to get the end date of the new partition.
            -- Note that the date is for the non-inclusive end range,
            -- so we actually need the date of the first day of the *next* month.
            -- First, let's get a date variable for the first of the partition month
            SET @q = 'SELECT DATE_FORMAT(@month_to_add, ''%Y-%m-01 00:00:00'') INTO @month_to_add';
            PREPARE st FROM @q;
            EXECUTE st;
            DEALLOCATE PREPARE st; 
            -- Then, let's add 1 month
            SET @q = 'SELECT DATE_ADD(?, INTERVAL 1 MONTH) INTO @partition_end_date';
            PREPARE st FROM @q;
            EXECUTE st USING @month_to_add;
            DEALLOCATE PREPARE st;
            -- We need the date in UNIX timestamp format.  
            SELECT UNIX_TIMESTAMP(@partition_end_date) INTO @partition_end_ts;
            -- Now insert the information into our temporary table
            INSERT INTO partitions_to_add VALUES (@partition_name_to_add, @partition_end_ts);
            SET @partitions_added = TRUE;
         END IF;
         -- Since we had at least one row returned, we know the
         -- partition already exists.
         IF ! @first THEN
            LEAVE read_loop;
         END IF;
         SET @first = FALSE;
      END LOOP;
     CLOSE cur1;
   END WHILE;
   -- Let's actually add the partitions now.
   IF @partitions_added THEN
      -- First we need to build the actual ALTER TABLE query.
      SET @schema = p_schema;
      SET @table = p_table;
      SET @q = 'SELECT CONCAT(''ALTER TABLE '', @schema, ''.'', @table, '' REORGANIZE PARTITION p_future INTO ( '') INTO @query';
      PREPARE st FROM @q;
      EXECUTE st;
      DEALLOCATE PREPARE st;
      SET done = FALSE;
      SET @first = TRUE;
      OPEN cur2;
      read_loop: LOOP
         FETCH cur2 INTO current_partition_name, current_partition_ts;
        IF done THEN
            LEAVE read_loop;
         END IF;
         -- If it is not the first partition, 
         -- then we need to add a comma
         IF ! @first THEN
            SET @q = 'SELECT CONCAT(@query, '', '') INTO @query';
            PREPARE st FROM @q;
            EXECUTE st;
            DEALLOCATE PREPARE st;
         END IF;
         -- Add the current partition
         SET @partition_name =  current_partition_name;
         SET @partition_ts =  current_partition_ts;         
         SET @q = 'SELECT CONCAT(@query, ''PARTITION '', @partition_name, '' VALUES LESS THAN ('', @partition_ts, '')'') INTO @query';
         PREPARE st FROM @q;
         EXECUTE st;
         DEALLOCATE PREPARE st;
         SET @first = FALSE;
      END LOOP;
      CLOSE cur2;
      -- We also need to include the p_future partition
      SET @q = 'SELECT CONCAT(@query, '', PARTITION p_future VALUES LESS THAN (MAXVALUE))'') INTO @query';
      PREPARE st FROM @q;
      EXECUTE st;
      DEALLOCATE PREPARE st;
      -- And then we prepare and execute the ALTER TABLE query.
      PREPARE st FROM @query;
      EXECUTE st;
      DEALLOCATE PREPARE st;  
   END IF;
   DROP TEMPORARY TABLE partitions_to_add;
END$
DELIMITER ;

让我们运行一下新的过程:

MariaDB [db1]> SHOW CREATE TABLE db1.quarterly_report_status\G
*************************** 1. row ***************************
       Table: quarterly_report_status
Create Table: CREATE TABLE `quarterly_report_status` (
  `report_id` int(11) NOT NULL,
  `report_status` varchar(20) NOT NULL,
  `report_updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE ( UNIX_TIMESTAMP(report_updated))
(PARTITION p_first VALUES LESS THAN (1475294400) ENGINE = InnoDB,
 PARTITION p201610 VALUES LESS THAN (1477972800) ENGINE = InnoDB,
 PARTITION p201611 VALUES LESS THAN (1480568400) ENGINE = InnoDB,
 PARTITION p201612 VALUES LESS THAN (1483246800) ENGINE = InnoDB,
 PARTITION p201701 VALUES LESS THAN (1485925200) ENGINE = InnoDB,
 PARTITION p201702 VALUES LESS THAN (1488344400) ENGINE = InnoDB,
 PARTITION p201703 VALUES LESS THAN (1491019200) ENGINE = InnoDB,
 PARTITION p201704 VALUES LESS THAN (1493611200) ENGINE = InnoDB,
 PARTITION p201705 VALUES LESS THAN (1496289600) ENGINE = InnoDB,
 PARTITION p201706 VALUES LESS THAN (1498881600) ENGINE = InnoDB,
 PARTITION p201707 VALUES LESS THAN (1501560000) ENGINE = InnoDB,
 PARTITION p201708 VALUES LESS THAN (1504238400) ENGINE = InnoDB,
 PARTITION p_future VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
1 row in set (0.00 sec)
MariaDB [db1]> CALL db1.create_new_partitions('db1', 'quarterly_report_status', 3);
+--------------------------------------------------------+
| CONCAT('Creating partition: ', @partition_name_to_add) |
+--------------------------------------------------------+
| Creating partition: p201709                            |
+--------------------------------------------------------+
1 row in set (0.01 sec)
+--------------------------------------------------------+
| CONCAT('Creating partition: ', @partition_name_to_add) |
+--------------------------------------------------------+
| Creating partition: p201710                            |
+--------------------------------------------------------+
1 row in set (0.02 sec)
+--------------------------------------------------------+
| CONCAT('Creating partition: ', @partition_name_to_add) |
+--------------------------------------------------------+
| Creating partition: p201711                            |
+--------------------------------------------------------+
1 row in set (0.02 sec)
Query OK, 0 rows affected (0.09 sec)
MariaDB [db1]> SHOW CREATE TABLE db1.quarterly_report_status\G
*************************** 1. row ***************************
       Table: quarterly_report_status
Create Table: CREATE TABLE `quarterly_report_status` (
  `report_id` int(11) NOT NULL,
  `report_status` varchar(20) NOT NULL,
  `report_updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE ( UNIX_TIMESTAMP(report_updated))
(PARTITION p_first VALUES LESS THAN (1475294400) ENGINE = InnoDB,
 PARTITION p201610 VALUES LESS THAN (1477972800) ENGINE = InnoDB,
 PARTITION p201611 VALUES LESS THAN (1480568400) ENGINE = InnoDB,
 PARTITION p201612 VALUES LESS THAN (1483246800) ENGINE = InnoDB,
 PARTITION p201701 VALUES LESS THAN (1485925200) ENGINE = InnoDB,
 PARTITION p201702 VALUES LESS THAN (1488344400) ENGINE = InnoDB,
 PARTITION p201703 VALUES LESS THAN (1491019200) ENGINE = InnoDB,
 PARTITION p201704 VALUES LESS THAN (1493611200) ENGINE = InnoDB,
 PARTITION p201705 VALUES LESS THAN (1496289600) ENGINE = InnoDB,
 PARTITION p201706 VALUES LESS THAN (1498881600) ENGINE = InnoDB,
 PARTITION p201707 VALUES LESS THAN (1501560000) ENGINE = InnoDB,
 PARTITION p201708 VALUES LESS THAN (1504238400) ENGINE = InnoDB,
 PARTITION p201709 VALUES LESS THAN (1506830400) ENGINE = InnoDB,
 PARTITION p201710 VALUES LESS THAN (1509508800) ENGINE = InnoDB,
 PARTITION p201711 VALUES LESS THAN (1512104400) ENGINE = InnoDB,
 PARTITION p_future VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
1 row in set (0.00 sec)

我们可以看到,跟我们预期的效果是一样的。

存储过程定义(删除旧的分区)

以下存储过程也包含一些注释,解释它的作用。

值得指出的是,该存储过程,通过 ALTER TABLE ... DROP PARTITION 语句分别删除旧分区。然后通过 ALTER TABLE ... REORGANIZE PARTITION 语句增加了 p_first 分区的范围,这样就填补了后面留下的空白。

DROP PROCEDURE IF EXISTS db1.drop_old_partitions;
DELIMITER $
CREATE PROCEDURE db1.drop_old_partitions(p_schema varchar(64), p_table varchar(64), p_months_to_keep int, p_seconds_to_sleep int)
   LANGUAGE SQL
   NOT DETERMINISTIC
   SQL SECURITY INVOKER
BEGIN  
   DECLARE done INT DEFAULT FALSE;
   DECLARE current_partition_name varchar(64);
   -- We'll use this cursor later to get
   -- the list of partitions to drop.
   -- @last_partition_name_to_keep will be
   -- set later.
   DECLARE cur1 CURSOR FOR 
   SELECT partition_name 
   FROM information_schema.partitions 
   WHERE TABLE_SCHEMA = p_schema 
   AND TABLE_NAME = p_table 
   AND PARTITION_NAME != 'p_first'
   AND PARTITION_NAME != 'p_future'
   AND PARTITION_NAME < @last_partition_name_to_keep;
   DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
   -- Now we get the last month of data that we want to keep
   -- by subtracting p_months_to_keep from the current date.
   -- Note that it will actually keep p_months_to_keep+1 partitions,
   -- since the current month is not complete.
   SET @date = CURDATE();
   SET @months_to_keep = p_months_to_keep;   
   SET @q = 'SELECT DATE_SUB(?, INTERVAL ? MONTH) INTO @last_month_to_keep';
   PREPARE st FROM @q;
   EXECUTE st USING @date, @months_to_keep;
   DEALLOCATE PREPARE st;
   -- Then we format the last month in the same format used
   -- in our partition names.
   SET @q = 'SELECT DATE_FORMAT(@last_month_to_keep, ''%Y%m'') INTO @formatted_last_month_to_keep';
   PREPARE st FROM @q;
   EXECUTE st;
   DEALLOCATE PREPARE st;
   -- And then we use the formatted date to build the name of the
   -- last partition that we want to keep. This partition name is
   -- assigned to @last_partition_name_to_keep, which is used in
   -- the cursor declared at the start of the procedure.
   SET @q = 'SELECT CONCAT(''p'', @formatted_last_month_to_keep) INTO @last_partition_name_to_keep';
   PREPARE st FROM @q;
   EXECUTE st;
   DEALLOCATE PREPARE st;
   SELECT CONCAT('Dropping all partitions before: ', @last_partition_name_to_keep);
   SET @first = TRUE;
   -- And then we loop through all partitions returned by the cursor,
   -- and those partitions are dropped.
   OPEN cur1;
   read_loop: LOOP
      FETCH cur1 INTO current_partition_name;
      IF done THEN
         LEAVE read_loop;
      END IF;
      IF ! @first AND p_seconds_to_sleep > 0 THEN
         SELECT CONCAT('Sleeping for ', p_seconds_to_sleep, ' seconds');
         SELECT SLEEP(p_seconds_to_sleep);
      END IF;
      SELECT CONCAT('Dropping partition: ', current_partition_name);
      -- First we build the ALTER TABLE query.
      SET @schema = p_schema;
      SET @table = p_table;
      SET @partition = current_partition_name;
      SET @q = 'SELECT CONCAT(''ALTER TABLE '', @schema, ''.'', @table, '' DROP PARTITION '', @partition) INTO @query';
      PREPARE st FROM @q;
      EXECUTE st;
      DEALLOCATE PREPARE st;
      -- And then we prepare and execute the ALTER TABLE query.
      PREPARE st FROM @query;
      EXECUTE st;
      DEALLOCATE PREPARE st;
      SET @first = FALSE;
   END LOOP;
   CLOSE cur1;
   -- If no partitions were dropped, then we can also skip this.
   IF ! @first THEN
      -- Then we need to get the date of the new first partition.
      -- We need the date in UNIX timestamp format.
      SET @q = 'SELECT DATE_FORMAT(@last_month_to_keep, ''%Y-%m-01 00:00:00'') INTO @new_first_partition_date';
      PREPARE st FROM @q;
      EXECUTE st;
      DEALLOCATE PREPARE st;     
      SELECT UNIX_TIMESTAMP(@new_first_partition_date) INTO @new_first_partition_ts;
      -- We also need to get the date of the second partition
      -- since the second partition is also needed for REORGANIZE PARTITION.
      SET @q = 'SELECT DATE_ADD(@new_first_partition_date, INTERVAL 1 MONTH) INTO @second_partition_date';
      PREPARE st FROM @q;
      EXECUTE st;
      DEALLOCATE PREPARE st;
      SELECT UNIX_TIMESTAMP(@second_partition_date) INTO @second_partition_ts;
      SELECT CONCAT('Reorganizing first and second partitions. first partition date = ', @new_first_partition_date, ', second partition date = ', @second_partition_date);
      -- Then we build the ALTER TABLE query.
      SET @schema = p_schema;
      SET @table = p_table;
      SET @q = 'SELECT CONCAT(''ALTER TABLE '', @schema, ''.'', @table, '' REORGANIZE PARTITION p_first, '', @last_partition_name_to_keep, '' INTO ( PARTITION p_first VALUES LESS THAN ( '', @new_first_partition_ts, '' ), PARTITION '', @last_partition_name_to_keep, '' VALUES LESS THAN ( '', @second_partition_ts, '' ) ) '') INTO @query';
      PREPARE st FROM @q;
      EXECUTE st;
      DEALLOCATE PREPARE st;
      -- And then we prepare and execute the ALTER TABLE query.
      PREPARE st FROM @query;
      EXECUTE st;
      DEALLOCATE PREPARE st;
   END IF;
END$
DELIMITER ;

让我们运行一下新的过程:

MariaDB [db1]> SHOW CREATE TABLE db1.quarterly_report_status\G
*************************** 1. row ***************************
       Table: quarterly_report_status
Create Table: CREATE TABLE `quarterly_report_status` (
  `report_id` int(11) NOT NULL,
  `report_status` varchar(20) NOT NULL,
  `report_updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE ( UNIX_TIMESTAMP(report_updated))
(PARTITION p_first VALUES LESS THAN (1475294400) ENGINE = InnoDB,
 PARTITION p201610 VALUES LESS THAN (1477972800) ENGINE = InnoDB,
 PARTITION p201611 VALUES LESS THAN (1480568400) ENGINE = InnoDB,
 PARTITION p201612 VALUES LESS THAN (1483246800) ENGINE = InnoDB,
 PARTITION p201701 VALUES LESS THAN (1485925200) ENGINE = InnoDB,
 PARTITION p201702 VALUES LESS THAN (1488344400) ENGINE = InnoDB,
 PARTITION p201703 VALUES LESS THAN (1491019200) ENGINE = InnoDB,
 PARTITION p201704 VALUES LESS THAN (1493611200) ENGINE = InnoDB,
 PARTITION p201705 VALUES LESS THAN (1496289600) ENGINE = InnoDB,
 PARTITION p201706 VALUES LESS THAN (1498881600) ENGINE = InnoDB,
 PARTITION p201707 VALUES LESS THAN (1501560000) ENGINE = InnoDB,
 PARTITION p201708 VALUES LESS THAN (1504238400) ENGINE = InnoDB,
 PARTITION p_future VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
1 row in set (0.00 sec)
MariaDB [db1]> CALL db1.drop_old_partitions('db1', 'quarterly_report_status', 6, 5);
+--------------------------------------------------------------------------+
| CONCAT('Dropping all partitions before: ', @last_partition_name_to_keep) |
+--------------------------------------------------------------------------+
| Dropping all partitions before: p201702                                  |
+--------------------------------------------------------------------------+
1 row in set (0.00 sec)
+--------------------------------------------------------+
| CONCAT('Dropping partition: ', current_partition_name) |
+--------------------------------------------------------+
| Dropping partition: p201610                            |
+--------------------------------------------------------+
1 row in set (0.00 sec)
+---------------------------------------------------------+
| CONCAT('Sleeping for ', p_seconds_to_sleep, ' seconds') |
+---------------------------------------------------------+
| Sleeping for 5 seconds                                  |
+---------------------------------------------------------+
1 row in set (0.02 sec)
+---------------------------+
| SLEEP(p_seconds_to_sleep) |
+---------------------------+
|                         0 |
+---------------------------+
1 row in set (5.02 sec)
+--------------------------------------------------------+
| CONCAT('Dropping partition: ', current_partition_name) |
+--------------------------------------------------------+
| Dropping partition: p201611                            |
+--------------------------------------------------------+
1 row in set (5.02 sec)
+---------------------------------------------------------+
| CONCAT('Sleeping for ', p_seconds_to_sleep, ' seconds') |
+---------------------------------------------------------+
| Sleeping for 5 seconds                                  |
+---------------------------------------------------------+
1 row in set (5.03 sec)
+---------------------------+
| SLEEP(p_seconds_to_sleep) |
+---------------------------+
|                         0 |
+---------------------------+
1 row in set (10.03 sec)
+--------------------------------------------------------+
| CONCAT('Dropping partition: ', current_partition_name) |
+--------------------------------------------------------+
| Dropping partition: p201612                            |
+--------------------------------------------------------+
1 row in set (10.03 sec)
+---------------------------------------------------------+
| CONCAT('Sleeping for ', p_seconds_to_sleep, ' seconds') |
+---------------------------------------------------------+
| Sleeping for 5 seconds                                  |
+---------------------------------------------------------+
1 row in set (10.05 sec)
+---------------------------+
| SLEEP(p_seconds_to_sleep) |
+---------------------------+
|                         0 |
+---------------------------+
1 row in set (15.05 sec)
+--------------------------------------------------------+
| CONCAT('Dropping partition: ', current_partition_name) |
+--------------------------------------------------------+
| Dropping partition: p201701                            |
+--------------------------------------------------------+
1 row in set (15.05 sec)
+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
| CONCAT('Reorganizing first and second partitions. first partition date = ', @new_first_partition_date, ', second partition date = ', @second_partition_date) |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Reorganizing first and second partitions. first partition date = 2017-02-01 00:00:00, second partition date = 2017-03-01 00:00:00                            |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (15.06 sec)
Query OK, 0 rows affected (15.11 sec)
MariaDB [db1]> SHOW CREATE TABLE db1.quarterly_report_status\G
*************************** 1. row ***************************
       Table: quarterly_report_status
Create Table: CREATE TABLE `quarterly_report_status` (
  `report_id` int(11) NOT NULL,
  `report_status` varchar(20) NOT NULL,
  `report_updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE ( UNIX_TIMESTAMP(report_updated))
(PARTITION p_first VALUES LESS THAN (1485925200) ENGINE = InnoDB,
 PARTITION p201702 VALUES LESS THAN (1488344400) ENGINE = InnoDB,
 PARTITION p201703 VALUES LESS THAN (1491019200) ENGINE = InnoDB,
 PARTITION p201704 VALUES LESS THAN (1493611200) ENGINE = InnoDB,
 PARTITION p201705 VALUES LESS THAN (1496289600) ENGINE = InnoDB,
 PARTITION p201706 VALUES LESS THAN (1498881600) ENGINE = InnoDB,
 PARTITION p201707 VALUES LESS THAN (1501560000) ENGINE = InnoDB,
 PARTITION p201708 VALUES LESS THAN (1504238400) ENGINE = InnoDB,
 PARTITION p_future VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
1 row in set (0.00 sec)

我们可以看到,正如我们所预期的,除了旧的分区被删除,我们还可以看到 p_first 的日期范围也被更新了。

存储过程定义(和其他过程捆绑)

在大多数情况下,同一时间执行所有分区维护可能会更好。因此,我们可以创建另一个存储过程,调用我们的其他两个存储过程。

DROP PROCEDURE IF EXISTS db1.perform_partition_maintenance;
DELIMITER $
CREATE PROCEDURE db1.perform_partition_maintenance(p_schema varchar(64), p_table varchar(64), p_months_to_add int, p_months_to_keep int, p_seconds_to_sleep int)
   LANGUAGE SQL
   NOT DETERMINISTIC
   SQL SECURITY INVOKER
BEGIN 
   CALL db1.drop_old_partitions(p_schema, p_table, p_months_to_keep, p_seconds_to_sleep);
   CALL db1.create_new_partitions(p_schema, p_table, p_months_to_add);
END$
DELIMITER ;

我们将分区表重新设置为原来的状态,然后运行新的存储过程。

MariaDB [db1]> SHOW CREATE TABLE db1.quarterly_report_status\G
*************************** 1. row ***************************
       Table: quarterly_report_status
Create Table: CREATE TABLE `quarterly_report_status` (
  `report_id` int(11) NOT NULL,
  `report_status` varchar(20) NOT NULL,
  `report_updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE ( UNIX_TIMESTAMP(report_updated))
(PARTITION p_first VALUES LESS THAN (1475294400) ENGINE = InnoDB,
 PARTITION p201610 VALUES LESS THAN (1477972800) ENGINE = InnoDB,
 PARTITION p201611 VALUES LESS THAN (1480568400) ENGINE = InnoDB,
 PARTITION p201612 VALUES LESS THAN (1483246800) ENGINE = InnoDB,
 PARTITION p201701 VALUES LESS THAN (1485925200) ENGINE = InnoDB,
 PARTITION p201702 VALUES LESS THAN (1488344400) ENGINE = InnoDB,
 PARTITION p201703 VALUES LESS THAN (1491019200) ENGINE = InnoDB,
 PARTITION p201704 VALUES LESS THAN (1493611200) ENGINE = InnoDB,
 PARTITION p201705 VALUES LESS THAN (1496289600) ENGINE = InnoDB,
 PARTITION p201706 VALUES LESS THAN (1498881600) ENGINE = InnoDB,
 PARTITION p201707 VALUES LESS THAN (1501560000) ENGINE = InnoDB,
 PARTITION p201708 VALUES LESS THAN (1504238400) ENGINE = InnoDB,
 PARTITION p_future VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
1 row in set (0.00 sec)
MariaDB [db1]> CALL db1.perform_partition_maintenance('db1', 'quarterly_report_status', 3, 6, 5);
+--------------------------------------------------------------------------+
| CONCAT('Dropping all partitions before: ', @last_partition_name_to_keep) |
+--------------------------------------------------------------------------+
| Dropping all partitions before: p201702                                  |
+--------------------------------------------------------------------------+
1 row in set (0.00 sec)
+--------------------------------------------------------+
| CONCAT('Dropping partition: ', current_partition_name) |
+--------------------------------------------------------+
| Dropping partition: p201610                            |
+--------------------------------------------------------+
1 row in set (0.00 sec)
+---------------------------------------------------------+
| CONCAT('Sleeping for ', p_seconds_to_sleep, ' seconds') |
+---------------------------------------------------------+
| Sleeping for 5 seconds                                  |
+---------------------------------------------------------+
1 row in set (0.02 sec)
+---------------------------+
| SLEEP(p_seconds_to_sleep) |
+---------------------------+
|                         0 |
+---------------------------+
1 row in set (5.02 sec)
+--------------------------------------------------------+
| CONCAT('Dropping partition: ', current_partition_name) |
+--------------------------------------------------------+
| Dropping partition: p201611                            |
+--------------------------------------------------------+
1 row in set (5.02 sec)
+---------------------------------------------------------+
| CONCAT('Sleeping for ', p_seconds_to_sleep, ' seconds') |
+---------------------------------------------------------+
| Sleeping for 5 seconds                                  |
+---------------------------------------------------------+
1 row in set (5.03 sec)
+---------------------------+
| SLEEP(p_seconds_to_sleep) |
+---------------------------+
|                         0 |
+---------------------------+
1 row in set (10.03 sec)
+--------------------------------------------------------+
| CONCAT('Dropping partition: ', current_partition_name) |
+--------------------------------------------------------+
| Dropping partition: p201612                            |
+--------------------------------------------------------+
1 row in set (10.03 sec)
+---------------------------------------------------------+
| CONCAT('Sleeping for ', p_seconds_to_sleep, ' seconds') |
+---------------------------------------------------------+
| Sleeping for 5 seconds                                  |
+---------------------------------------------------------+
1 row in set (10.06 sec)
+---------------------------+
| SLEEP(p_seconds_to_sleep) |
+---------------------------+
|                         0 |
+---------------------------+
1 row in set (15.06 sec)
+--------------------------------------------------------+
| CONCAT('Dropping partition: ', current_partition_name) |
+--------------------------------------------------------+
| Dropping partition: p201701                            |
+--------------------------------------------------------+
1 row in set (15.06 sec)
+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
| CONCAT('Reorganizing first and second partitions. first partition date = ', @new_first_partition_date, ', second partition date = ', @second_partition_date) |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Reorganizing first and second partitions. first partition date = 2017-02-01 00:00:00, second partition date = 2017-03-01 00:00:00                            |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (15.08 sec)
+--------------------------------------------------------+
| CONCAT('Creating partition: ', @partition_name_to_add) |
+--------------------------------------------------------+
| Creating partition: p201709                            |
+--------------------------------------------------------+
1 row in set (15.16 sec)
+--------------------------------------------------------+
| CONCAT('Creating partition: ', @partition_name_to_add) |
+--------------------------------------------------------+
| Creating partition: p201710                            |
+--------------------------------------------------------+
1 row in set (15.17 sec)
+--------------------------------------------------------+
| CONCAT('Creating partition: ', @partition_name_to_add) |
+--------------------------------------------------------+
| Creating partition: p201711                            |
+--------------------------------------------------------+
1 row in set (15.17 sec)
Query OK, 0 rows affected (15.26 sec)
MariaDB [db1]> SHOW CREATE TABLE db1.quarterly_report_status\G
*************************** 1. row ***************************
       Table: quarterly_report_status
Create Table: CREATE TABLE `quarterly_report_status` (
  `report_id` int(11) NOT NULL,
  `report_status` varchar(20) NOT NULL,
  `report_updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE ( UNIX_TIMESTAMP(report_updated))
(PARTITION p_first VALUES LESS THAN (1485925200) ENGINE = InnoDB,
 PARTITION p201702 VALUES LESS THAN (1488344400) ENGINE = InnoDB,
 PARTITION p201703 VALUES LESS THAN (1491019200) ENGINE = InnoDB,
 PARTITION p201704 VALUES LESS THAN (1493611200) ENGINE = InnoDB,
 PARTITION p201705 VALUES LESS THAN (1496289600) ENGINE = InnoDB,
 PARTITION p201706 VALUES LESS THAN (1498881600) ENGINE = InnoDB,
 PARTITION p201707 VALUES LESS THAN (1501560000) ENGINE = InnoDB,
 PARTITION p201708 VALUES LESS THAN (1504238400) ENGINE = InnoDB,
 PARTITION p201709 VALUES LESS THAN (1506830400) ENGINE = InnoDB,
 PARTITION p201710 VALUES LESS THAN (1509508800) ENGINE = InnoDB,
 PARTITION p201711 VALUES LESS THAN (1512104400) ENGINE = InnoDB,
 PARTITION p_future VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
1 row in set (0.00 sec)

该存储过程执行也跟我们预期结果一样。

过程多次执行

应该注意的是,这些存储过程可以比必要时更频繁地运行。如果在不需要添加或删除分区时运行这些过程,则该过程将不会执行任何工作。让我们重置表定义,然后试一下。

MariaDB [db1]> CALL db1.perform_partition_maintenance('db1', 'quarterly_report_status', 3, 6, 5);
+--------------------------------------------------------------------------+
| CONCAT('Dropping all partitions before: ', @last_partition_name_to_keep) |
+--------------------------------------------------------------------------+
| Dropping all partitions before: p201702                                  |
+--------------------------------------------------------------------------+
1 row in set (0.00 sec)
+--------------------------------------------------------+
| CONCAT('Dropping partition: ', current_partition_name) |
+--------------------------------------------------------+
| Dropping partition: p201610                            |
+--------------------------------------------------------+
1 row in set (0.00 sec)
+---------------------------------------------------------+
| CONCAT('Sleeping for ', p_seconds_to_sleep, ' seconds') |
+---------------------------------------------------------+
| Sleeping for 5 seconds                                  |
+---------------------------------------------------------+
1 row in set (0.03 sec)
+---------------------------+
| SLEEP(p_seconds_to_sleep) |
+---------------------------+
|                         0 |
+---------------------------+
1 row in set (5.03 sec)
+--------------------------------------------------------+
| CONCAT('Dropping partition: ', current_partition_name) |
+--------------------------------------------------------+
| Dropping partition: p201611                            |
+--------------------------------------------------------+
1 row in set (5.03 sec)
+---------------------------------------------------------+
| CONCAT('Sleeping for ', p_seconds_to_sleep, ' seconds') |
+---------------------------------------------------------+
| Sleeping for 5 seconds                                  |
+---------------------------------------------------------+
1 row in set (5.06 sec)
+---------------------------+
| SLEEP(p_seconds_to_sleep) |
+---------------------------+
|                         0 |
+---------------------------+
1 row in set (10.06 sec)
+--------------------------------------------------------+
| CONCAT('Dropping partition: ', current_partition_name) |
+--------------------------------------------------------+
| Dropping partition: p201612                            |
+--------------------------------------------------------+
1 row in set (10.06 sec)
+---------------------------------------------------------+
| CONCAT('Sleeping for ', p_seconds_to_sleep, ' seconds') |
+---------------------------------------------------------+
| Sleeping for 5 seconds                                  |
+---------------------------------------------------------+
1 row in set (10.08 sec)
+---------------------------+
| SLEEP(p_seconds_to_sleep) |
+---------------------------+
|                         0 |
+---------------------------+
1 row in set (15.09 sec)
+--------------------------------------------------------+
| CONCAT('Dropping partition: ', current_partition_name) |
+--------------------------------------------------------+
| Dropping partition: p201701                            |
+--------------------------------------------------------+
1 row in set (15.09 sec)
+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
| CONCAT('Reorganizing first and second partitions. first partition date = ', @new_first_partition_date, ', second partition date = ', @second_partition_date) |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Reorganizing first and second partitions. first partition date = 2017-02-01 00:00:00, second partition date = 2017-03-01 00:00:00                            |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (15.11 sec)
+--------------------------------------------------------+
| CONCAT('Creating partition: ', @partition_name_to_add) |
+--------------------------------------------------------+
| Creating partition: p201709                            |
+--------------------------------------------------------+
1 row in set (15.18 sec)
+--------------------------------------------------------+
| CONCAT('Creating partition: ', @partition_name_to_add) |
+--------------------------------------------------------+
| Creating partition: p201710                            |
+--------------------------------------------------------+
1 row in set (15.18 sec)
+--------------------------------------------------------+
| CONCAT('Creating partition: ', @partition_name_to_add) |
+--------------------------------------------------------+
| Creating partition: p201711                            |
+--------------------------------------------------------+
1 row in set (15.18 sec)
Query OK, 0 rows affected (15.28 sec)
MariaDB [db1]> CALL db1.perform_partition_maintenance('db1', 'quarterly_report_status', 3, 6, 5);
+--------------------------------------------------------------------------+
| CONCAT('Dropping all partitions before: ', @last_partition_name_to_keep) |
+--------------------------------------------------------------------------+
| Dropping all partitions before: p201702                                  |
+--------------------------------------------------------------------------+
1 row in set (0.01 sec)
Query OK, 0 rows affected (0.02 sec)

正如我们从上面的输出中看到的,这个过程第二次没有执行任何工作。

事件定义

我们希望我们的存储过程每个月自动运行,这样我们就可以使用一个事件来完成这个过程。在测试这个事件之前,我们需要做两件事:

  • 我们需要用原始的定义重新创建表,这样它就有了所有的原始分区。
  • 我们需要确保 event_scheduler=ON 已经设置好,如果没有设置,我们需要设置上。
MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'event_scheduler';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| event_scheduler | OFF   |
+-----------------+-------+
1 row in set (0.00 sec)
MariaDB [(none)]> SET GLOBAL event_scheduler=ON;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'event_scheduler';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| event_scheduler | ON    |
+-----------------+-------+
1 row in set (0.00 sec)

然后,我们可以运行以下内容:

DROP EVENT db1.monthly_perform_partition_maintenance_event;
CREATE EVENT db1.monthly_perform_partition_maintenance_event
   ON SCHEDULE
   EVERY 1 MONTH
   STARTS NOW()
DO
   CALL db1.perform_partition_maintenance('db1', 'quarterly_report_status', 3, 6, 5);

然而,我们可以在这里修改完善一下。每月运行一次这个过程可能并不太理想,因为如果这个过程因为某种原因失败了,那么直到下个月它可能都不会再一次执行。出于这个原因,最好更频繁一下去运行这个过程,比如每天一次。正如上面所提到的,只有当分区维护是必要的时候,这个过程才会起作用,所以更频繁地执行这个过程也不会引起任何问题。

如果我们想每天运行一次程序,那么事件定义就变成:

DROP EVENT db1.monthly_perform_partition_maintenance_event;
CREATE EVENT db1.monthly_perform_partition_maintenance_event
   ON SCHEDULE
   EVERY 1 DAY
   STARTS NOW()
DO
   CALL db1.perform_partition_maintenance('db1', 'quarterly_report_status', 3, 6, 5);

结论

由于存储过程和事件的灵活性,在MariaDB中自动执行分区维护相对容易。

© 著作权归作者所有

共有 人打赏支持
分秒
粉丝 8
博文 30
码字总数 75264
作品 0
朝阳
技术主管
centos7 mysql(mariadb)数据库安装和配置

一、系统环境 yum update升级以后的系统版本为 [root@yl-web yl]# cat /etc/redhat-releaseCentOS Linux release 7.1.1503 (Core) 二、mysql安装 一般网上给出的资料都是 #yum install mysql...

學無止境 ⋅ 01/18 ⋅ 0

MariaDB 10.3 首个 GA 版本 10.3.7 发布,带来多项更新

MariaDB 10.3.7 发布了,MariaDB 10.3.7 是一个 GA 稳定版。MariaDB主要由开源社区在维护,采用 GPL 授权许可。 MariaDB 的目的是完全兼容 MySQL,包括 API 和命令行。 发布日期: 2018.05.2...

mia0x75 ⋅ 05/25 ⋅ 0

MariaDB TX 3.0:挑战 IT 巨头商用数据库解决方案

这次发布的 MariaDB TX 3.0 版,支持甲骨文 SQL 语言 PL/SQL,并且,甲骨文、微软 SQL Server 商用数据库所提供的Temporal Tables 功能,在 MariaDB TX 3.0 版也有提供。 去年开源数据库 Ma...

达尔文 ⋅ 05/26 ⋅ 1

MariaDB 10.2.15 发布,MySQL 分支版本

MariaDB 10.2.15 发布,MariaDB 数据库管理系统是 MySQL 的一个分支,主要由开源社区在维护,采用 GPL 授权许可 MariaDB 的目的是完全兼容 MySQL,包括 API 和命令行。显著变更如下: PCRE u...

淡漠悠然 ⋅ 05/18 ⋅ 0

初识MariaDB之6——半同步复制

一、背景介绍 MySQL主从复制能解决一定的单点故障问题,但其异步的工作特性存在一定的隐患,比如主节点事务提交后还未写入binlog,此时主节点故障后,但主节点认为事务已提交,从节点无法从主...

qiao645 ⋅ 05/10 ⋅ 0

Docker Swarm 部署Mysql/Mariadb高可用主从复制集群

本文为转载,原文:Docker Swarm 部署Mysql/Mariadb高可用主从复制集群 1. MariaDB与MySQL MariaDB是MySQL源代码的一个分支,在意识到Oracle会对MySQL许可做什么后分离了出来(MySQL先后被S...

ChainZhang ⋅ 05/16 ⋅ 0

Mysql MariaDB修改最大连接数

1.mariadb数据库最大连接数,默认为151 MariaDB [(none)]> show variables like 'max_connections'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ ......

lanjian28 ⋅ 06/09 ⋅ 0

CentOS 6.4 安装 MariaDB 10.3

本文介绍如何使用yum在CentOS 6.4 安装 MariaDB 10.3 1. 创建mysql组 groupadd mysql 2. 创建mysql用户并添加到mysql组 useradd -g mysql mysql 3.修改mysql密码 passwd mysql 4. 配置 Mari...

易语随风去 ⋅ 06/01 ⋅ 0

5月24日任务MariaDB安装、Apache安装

11.6 MariaDB安装 1. wget https://downloads.mariadb.com/MariaDB/mariadb-10.2.6/bintar-linux-glibc214-x8664/mariadb-10.2.6-linux-glibc214-x8664.tar.gz //下载MariaDB 2. tar -zvxf ......

吕湘颖 ⋅ 05/10 ⋅ 0

Centos 7 下 mariadb 的安装与配置

文前说明 作为码农中的一员,需要不断的学习,我工作之余将一些分析总结和学习笔记写成博客与大家一起交流,也希望采用这种方式记录自己的学习之旅。 本文仅供学习交流使用,侵权必删。 不用...

羽杰 ⋅ 2017/09/22 ⋅ 0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

RabbitMQ学习以及与Spring的集成(三)

本文介绍RabbitMQ与Spring的简单集成以及消息的发送和接收。 在RabbitMQ的Spring配置文件中,首先需要增加命名空间。 xmlns:rabbit="http://www.springframework.org/schema/rabbit" 其次是模...

onedotdot ⋅ 30分钟前 ⋅ 0

JAVA实现仿微信红包分配规则

最近过年发红包拜年成为一种新的潮流,作为程序猿对算法的好奇远远要大于对红包的好奇,这里介绍一种自己想到的一种随机红包分配策略,还请大家多多指教。 算法介绍 一、红包金额限制 对于微...

小致dad ⋅ 42分钟前 ⋅ 0

Python 数电表格格式化 xlutils xlwt xlrd的使用

需要安装 xlutils xlwt xlrd 格式化前 格式化后 代码 先copy读取的表格,然后按照一定的规则修改,将昵称中的学号提取出来替换昵称即可 from xlrd import open_workbookfrom xlutils.copy ...

阿豪boy ⋅ 今天 ⋅ 0

面试题:使用rand5()生成rand7()

前言 读研究生这3 年,思维与本科相比变化挺大的,这几年除了看论文、设计方案,更重要的是学会注重先思考、再实现,感觉更加成熟吧,不再像个小P孩,人年轻时总会心高气傲。有1 道面试题:给...

初雪之音 ⋅ 今天 ⋅ 0

Docker Toolbox Looks like something went wrong

Docker Toolbox 重新安装后提示错误:Looks like something went wrong in step ´Checking if machine default exists´ 控制面板-->程序与应用-->启用或关闭windows功能:找到Hyper-V,如果处......

随你疯 ⋅ 今天 ⋅ 0

Guacamole 远程桌面

本文将Apache的guacamole服务的部署和应用,http://guacamole.apache.org/doc/gug/ 该链接下有全部相关知识的英文文档,如果水平ok,可以去这里仔细查看。 一、简介 Apache Guacamole 是无客...

千里明月 ⋅ 今天 ⋅ 0

nagios 安装

Nagios简介:监控网络并排除网络故障的工具:nagios,Ntop,OpenVAS,OCS,OSSIM等开源监控工具。 可以实现对网络上的服务器进行全面的监控,包括服务(apache、mysql、ntp、ftp、disk、qmail和h...

寰宇01 ⋅ 今天 ⋅ 0

AngularDart注意事项

默认情况下创建Dart项目应出现以下列表: 有时会因为不知明的原因导致列表项缺失: 此时可以通过以下步骤解决: 1.创建项目涉及到的包:stagehand 2.执行pub global activate stagehand或pub...

scooplol ⋅ 今天 ⋅ 0

Java Web如何操作Cookie的添加修改和删除

创建Cookie对象 Cookie cookie = new Cookie("id", "1"); 修改Cookie值 cookie.setValue("2"); 设置Cookie有效期和删除Cookie cookie.setMaxAge(24*60*60); // Cookie有效时间 co......

二营长意大利炮 ⋅ 今天 ⋅ 0

【每天一个JQuery特效】淡入淡出显示或隐藏窗口

我是JQuery新手爱好者,有时间就练练代码,防止手生,争取每天一个JQuery练习,在这个博客记录下学习的笔记。 本特效主要采用fadeIn()和fadeOut()方法显示淡入淡出的显示效果显示或隐藏元...

Rhymo-Wu ⋅ 今天 ⋅ 0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

返回顶部
顶部