MySQL可以从事件调度器创建新的分区吗

I'm having a table looking something like this:

我有一张这样的桌子:

CREATE TABLE `Calls` (
  `calendar_id` int(11) NOT NULL,
  `db_date` timestamp NOT NULL,
  `cgn` varchar(32) DEFAULT NULL,
  `cpn` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`calendar_id`),
  KEY `db_date_idx` (`db_date`)
) 
 PARTITION BY RANGE (calendar_id)(
   PARTITION p20091024 VALUES LESS THAN (20091024) ,
   PARTITION p20091025 VALUES LESS THAN (20091025));

Can I somehow use the mysql scheduler to automatically add a new partition(2 days in advance) - I'm looking for an example that would, every day add a new partition - it'd run something like

我是否可以使用mysql调度器自动添加一个新的分区(提前2天)——我正在寻找一个例子，它每天都会添加一个新的分区——它会运行类似的东西

alter table Calls add partition (partition p20091026 values less than(20091026));

Where p20091026/20091026 is constructed when the scheduled task run, deriving the value from now + 2 day. (Or am I better of scripting this through cron ?)

其中，当计划任务运行时构造p20091026/20091026，从现在+ 2天派生值。(还是我更适合通过cron编写脚本?)

2 个解决方案

#1

Yes, you can do this.

是的，你可以这么做。

Note that the scheduler isn't active by default (see Event Scheduler Configuration), so it's not a zero-risk option. For example, if your operations team migrates your app to a new server, but forgets to enable the scheduler, your app will get hosed. There's also special privileges needed, which again may need to be set up on a new server.

注意，调度器在默认情况下不是活动的(请参阅事件调度器配置)，因此它不是零风险选项。例如，如果您的操作团队将您的应用程序迁移到一个新的服务器，但是忘记启用调度程序，您的应用程序将被调用。还需要一些特权，这些特权可能需要在新服务器上设置。

My advice: first, create a stored procedure (see code sample below) which handles periodic partition maintenance: dropping old partitions if the table gets too big, and adding enough new partitions (e.g. 1 week) so that even if the maintenance proc isn't run for a while, your app won't die.

我的建议:首先，创建一个存储过程(请参阅下面的代码示例)来处理周期性的分区维护:如果表太大，删除旧的分区，并添加足够的新分区(例如，1周)，这样即使维护proc没有运行一段时间，您的应用程序也不会死。

Then redundantly schedule calls to that stored proc. Use the MySQL scheduler, use a cron job, and use any other way you like. Then if one scheduler isn't working, the other can pick up the slack. If you design the sproc correctly, it should be cheap to execute a no-op if it doesn't need to do anything. You might even want to call it from your app, e.g. as the first statement when generating a long-running report, or as part of your daily ETL process (if you have one). My point is that the achilles heel of scheduled tasks is ensuring that the scheduler is actually working-- so think about redundancy here.

然后冗余地调度对存储的proc的调用。使用MySQL调度器，使用cron作业，并使用您喜欢的任何其他方式。如果一个调度器不工作，另一个调度器可以补上这个空缺。如果您设计的sproc是正确的，那么如果不需要执行任何操作，那么执行no-op应该是很便宜的。您甚至可能想从您的应用程序中调用它，例如在生成一个长时间运行的报表时的第一个语句，或者作为您的日常ETL过程的一部分(如果您有的话)。我的观点是，计划任务的致命弱点是确保调度程序实际上正在工作——所以请考虑这里的冗余。

Just make sure not to schedule all the calls at the same time so they won't step on each other! :-)

确保不要同时安排所有的电话，这样他们就不会互相踩到对方!:-)

Here's a code sample for what your maintenance proc could look like-- first it prunes old partitions, then adds new ones. I left error checking and preventing multiple simultaneous executions as an exerise for the reader.

下面是维护过程的代码示例——首先删除旧分区，然后添加新分区。我将错误检查和防止多次同时执行作为读者的exerise。

DELIMITER $$

DROP PROCEDURE IF EXISTS `test`.`UpdatePartitions` $$
CREATE PROCEDURE `test`.`UpdatePartitions` ()
BEGIN

  DECLARE maxpart_date date;
  DECLARE partition_count int;
  DECLARE minpart date;
  DECLARE droppart_sql date;
  DECLARE newpart_date date;
  DECLARE newpart_sql varchar(500);

  SELECT COUNT(*)
    INTO partition_count
    FROM INFORMATION_SCHEMA.PARTITIONS
    WHERE TABLE_NAME='Calls' AND TABLE_SCHEMA='test';

  -- first, deal with pruning old partitions
  -- TODO: set your desired # of partitions below, or make it parameterizable
  WHILE (partition_count > 1000)
  DO

    -- optionally, do something here to deal with the parition you're dropping, e.g.
    -- copy the data into an archive table

     SELECT MIN(PARTITION_DESCRIPTION)
       INTO minpart
       FROM INFORMATION_SCHEMA.PARTITIONS
       WHERE TABLE_NAME='Calls' AND TABLE_SCHEMA='test';

     SET @sql := CONCAT('ALTER TABLE Calls DROP PARTITION p'
                        , CAST((minpart+0) as char(8))
                        , ';');

     PREPARE stmt FROM @sql;
     EXECUTE stmt;
     DEALLOCATE PREPARE stmt;

    SELECT COUNT(*)
      INTO partition_count
      FROM INFORMATION_SCHEMA.PARTITIONS
      WHERE TABLE_NAME='Calls' AND TABLE_SCHEMA='test';


  END WHILE;

  SELECT MAX(PARTITION_DESCRIPTION)
    INTO maxpart_date
    FROM INFORMATION_SCHEMA.PARTITIONS
    WHERE TABLE_NAME='Calls' AND TABLE_SCHEMA='test';

  -- create enough partitions for at least the next week
  WHILE (maxpart_date < CURDATE() + INTERVAL 7 DAY)
  DO

    SET newpart_date := maxpart_date + INTERVAL 1 DAY;
    SET @sql := CONCAT('ALTER TABLE Calls ADD PARTITION (PARTITION p'
                        , CAST((newpart_date+0) as char(8))
                        , ' values less than('
                        , CAST((newpart_date+0) as char(8))
                        , '));');

    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    SELECT MAX(PARTITION_DESCRIPTION)
      INTO maxpart_date
      FROM INFORMATION_SCHEMA.PARTITIONS
      WHERE TABLE_NAME='Calls' AND TABLE_SCHEMA='test';

  END WHILE;

END $$

DELIMITER ;

BTW, partition maintenance (ensuring new partitions are created in advance, pruning old partitions, etc.) is, IMHO, critically important to automate. I've personally seen a large enterprise data warehouse go down for a day because a year's worth of partitions was cretaed initially but no one remembered to create more partitions once the next year came around. So it's very good you're thinking about automation here-- it bodes well for the project you're working on. :-)

顺便说一句，分区维护(确保预先创建新分区、修剪旧分区等等)对自动化来说至关重要。我个人曾看到过一个大型企业数据仓库宕机一天，因为最初一年的分区数量被压缩了，但没有人记得在第二年创建更多的分区。所以你想到自动化很好，这对你正在做的项目来说是个好兆头。:-)

#2

Excellent solution from Justin there. I took his code as the starting point for my current project and would like to mention a few things that came up while I was implementing it.

来自犹斯丁的极好的解决方案。我把他的代码作为我当前项目的起点，并且在我实施它的时候，我想提到一些事情。

1. The existing partition structure in the table you run this on should not include a MAXVALUE type partition - all partitions must be delimited by literal dates. This is because SELECT MAX(PARTITION_DESCRIPTION) will return 'MAXVALUE' which fails to be converted to a date in the next step. If you get odd message when calling the procedure saying something like: illegal mix of collations for '<', this could be the problem.

   运行此表的现有分区结构不应该包含MAXVALUE类型的分区——所有分区必须由文字日期分隔。这是因为SELECT MAX(PARTITION_DESCRIPTION)将返回“MAXVALUE”，该值不能在下一步中转换为日期。如果在调用过程时收到一些奇怪的消息，比如:非法混合'<'排序，这可能是问题所在。

2. It's a good idea to add: "AND TABLE_SCHEMA = 'dbname'" when selecting partition names from the INFORMATION_SCHEMA table, because while more than one partition can exist with the same name for the same table (in different databases), they are all listed in the INFORMATION_SCHEMA table together. Without the TABLE_SCHEMA specification your select eg. MAX(PARTITION_DESCRIPTION) will give you the max partition name among every existing partition for tables of that name in every database.

   在从INFORMATION_SCHEMA表中选择分区名时，最好添加:“和TABLE_SCHEMA = 'dbname'”，因为虽然可以有多个分区为同一个表(在不同的数据库中)使用相同的名称，但它们都在INFORMATION_SCHEMA表中列出。如果没有TABLE_SCHEMA规范，请选择eg。MAX(PARTITION_DESCRIPTION)将为每个数据库中的表提供每个现有分区之间的最大分区名。

3. Somewhere along the way I had problems with the ALTER TABLE xxx ADD PARTITION as it is in Justin's solution, I think it was that the same format for the partition name (yyyymmdd) was being used as the partition delimiter which expected yyyy-mm-dd (v5.6.2).

   在这个过程中，我遇到了与Justin解决方案中一样的ALTER TABLE xxx ADD PARTITION的问题，我认为分区名(yyyyymmdd)的相同格式被用作分区分隔符，期望使用yyyyyy -mm-dd (v5.6.2)。

4. The default behaviour is to only add partitions in the future as necessary. If you want to create partitions for the past, you will need to first set up a partition for a date older than the oldest partition you want. Eg. if you are keeping data for the past 30 days, first add a partition for say, 35 days ago and then run the procedure. Granted, it may only be feasible to do this on an empty table, but I thought it worth mentioning.

   默认行为是只在将来必要时添加分区。如果您想为过去创建分区，您需要首先为比您想要的最老分区更老的日期设置分区。如。如果您正在保存过去30天的数据，那么首先为35天前添加一个分区，然后运行这个过程。当然，在一个空的桌子上做这个可能是可行的，但是我认为它值得提及。

5. In order to create the desired span of past/future partitions as in 4. you will initially need to run the procedure twice. For the example in 4. above, the first run will create partitions for -35 days to present, and the necessary future partitions. The second run will then trim the partitions between -35 and -30 away.

   为了创建所需的过去/未来分区跨度，如4所示。您首先需要运行这个过程两次。对于4中的示例。上面，第一次运行将创建分区，时间为-35天，并创建必要的未来分区。第二次运行将在-35和-30之间修剪分区。

Here is what I am using at the moment. I added some parameters to make it a bit more flexible from the caller's point of view. You can specify the database, table, current date, and how many partitions to keep for both past and future.

这是我现在正在使用的。我添加了一些参数，使它从调用者的角度更灵活一些。您可以指定数据库、表、当前日期，以及为过去和将来保留多少个分区。

I also altered the naming of partitions so that the partition named p20110527 represents the day starting from 2011-5-27 00:00 instead of the day ending at that time.

我还更改了分区的命名，以使命名为p20110527的分区表示从2011-5-27 00:00开始的日期，而不是在那个时候结束的日期。

There is still no error checking or prevention of simultaneous execution :-)

仍然没有错误检查或防止同时执行:-)

DELIMITER $$

DROP PROCEDURE IF EXISTS UpdatePartitions $$

-- Procedure to delete old partitions and create new ones based on a given date.
-- partitions older than (today_date - days_past) will be dropped
-- enough new partitions will be made to cover until (today_date + days_future)
CREATE PROCEDURE UpdatePartitions (dbname TEXT, tblname TEXT, today_date DATE, days_past INT, days_future INT)
BEGIN

DECLARE maxpart_date date;
DECLARE partition_count int;
DECLARE minpart date;
DECLARE droppart_sql date;
DECLARE newpart_date date;
DECLARE newpart_sql varchar(500); 

SELECT COUNT(*)
INTO partition_count
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME=tblname
AND TABLE_SCHEMA=dbname;

-- SELECT partition_count;

-- first, deal with pruning old partitions
WHILE (partition_count > days_past + days_future)
DO
-- optionally, do something here to deal with the parition you're dropping, e.g.
-- copy the data into an archive table

 SELECT STR_TO_DATE(MIN(PARTITION_DESCRIPTION), '''%Y-%m-%d''')
   INTO minpart
   FROM INFORMATION_SCHEMA.PARTITIONS
   WHERE TABLE_NAME=tblname
   AND TABLE_SCHEMA=dbname;

-- SELECT minpart;

 SET @sql := CONCAT('ALTER TABLE '
                    , tblname
                    , ' DROP PARTITION p'
                    , CAST(((minpart - INTERVAL 1 DAY)+0) as char(8))
                    , ';');

 -- SELECT @sql;
 PREPARE stmt FROM @sql;
 EXECUTE stmt;
 DEALLOCATE PREPARE stmt;

SELECT COUNT(*)
  INTO partition_count
  FROM INFORMATION_SCHEMA.PARTITIONS
  WHERE TABLE_NAME=tblname
  AND TABLE_SCHEMA=dbname;

-- SELECT partition_count;

END WHILE;

SELECT STR_TO_DATE(MAX(PARTITION_DESCRIPTION), '''%Y-%m-%d''')
INTO maxpart_date
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME=tblname
AND TABLE_SCHEMA=dbname;

-- select maxpart_date;
-- create enough partitions for at least the next days_future days
WHILE (maxpart_date < today_date + INTERVAL days_future DAY)
DO

-- select 'here1';
SET newpart_date := maxpart_date + INTERVAL 1 DAY;
SET @sql := CONCAT('ALTER TABLE '
                    , tblname
                    , ' ADD PARTITION (PARTITION p'
                    , CAST(((newpart_date - INTERVAL 1 DAY)+0) as char(8))
                    , ' VALUES LESS THAN ('''
                    , newpart_date
                    , '''));');

-- SELECT @sql;
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SELECT STR_TO_DATE(MAX(PARTITION_DESCRIPTION), '''%Y-%m-%d''')
  INTO maxpart_date
  FROM INFORMATION_SCHEMA.PARTITIONS
  WHERE TABLE_NAME=tblname
  AND TABLE_SCHEMA=dbname;

SET maxpart_date := newpart_date;

END WHILE;

END $$

DELIMITER ;