MySQL 5.5 partitioning-truncate partitioning

Last time we introduced:For MySQL 5.5Partitions: partitions in non-integer columns. This section describes partitions in MySQL 5.5.Truncate PartitionNext, let's take a look at this part.

Enhanced availability: truncate partitions

One of the most attractive features of partitioning is the ability to instantly remove a large number of records. DBAs like to store historical records in partition tables partitioned by date, so that they can regularly Delete outdated historical data, this method is quite useful. If the first partition stores the oldest history records, you can directly Delete the first partition and create a new partition at the end to save the latest history records, in this way, you can quickly clear historical records.

However, when you need to remove part of the data in the partition, it is not that simple. There is no problem in deleting the partition, but it is a headache if you are clearing the partition, to remove all data from a partition, but keep the partition itself, you can:

The DELETE statement is used, but we know that the performance of the DELETE statement is poor.

The drop partition statement is followed by an eorganize partitions statement to re-create a PARTITION, but this is much more costly than the previous method.

MySQL 5.5 introduces truncate partition, which is similar to the drop partition statement, but retains the PARTITION itself, which means that the PARTITION can be reused. Truncate partition should be a required tool in the DBA toolbox.

Fine-tuning function: TO_SECONDS

The partition enhancement package has a new function to process the DATE and DATETIME columns. Using the TO_SECONDS function, you can convert the DATE/time column to the number of seconds since 0 years, if you want to partition at intervals less than one day, this function can help you.

TO_SECONDS triggers partition trimming. Unlike TO_DAYS, TO_SECONDS can be used in turn, that is, FROM_DAYS. TO_SECONDS does not have such inverse functions, but it is not difficult to do DIY by yourself.

 
 

  
  
drop function if exists from_seconds;    
  
  
delimiter //    
  
  
create function from_seconds (secs bigint)    
  
  
returns DATETIME    
  
  
begin   
  
  
    declare days INT;    
  
  
    declare secs_per_day INT;    
  
  
    DECLARE ZH INT;    
  
  
    DECLARE ZM INT;    
  
  
    DECLARE ZS INT;    
  
  
    set secs_per_day = 60 * 60 * 24;    
  
  
    set days = floor(secs / secs_per_day);    
  
  
    set secssecs = secs - (secs_per_day * days);    
  
  
    set ZH = floor(secs / 3600);    
  
  
    set ZM = floor(secs / 60) - ZH * 60;    
  
  
    set ZS = secs - (ZH * 3600 + ZM * 60);    
  
  
    return CAST(CONCAT(FROM_DAYS(days), ' ', ZH, ':', ZM, ':', ZS) as DATETIME);    
  
  
end //    
  
  
delimiter ;  
 
 

With these new weapons, we can confidently create a temporary partition less than one day, such:

 
 

  
  
CREATE TABLE t2 (    
  
  
  dt datetime    
  
  
)    
  
  
PARTITION BY RANGE (to_seconds(dt))    
  
  
(    
  
  
  PARTITION p01 VALUES LESS THAN (to_seconds('2009-11-30 08:00:00')) ,    
  
  
  PARTITION p02 VALUES LESS THAN (to_seconds('2009-11-30 16:00:00')) ,    
  
  
  PARTITION p03 VALUES LESS THAN (to_seconds('2009-12-01 00:00:00')) ,    
  
  
  PARTITION p04 VALUES LESS THAN (to_seconds('2009-12-01 08:00:00')) ,    
  
  
  PARTITION p05 VALUES LESS THAN (to_seconds('2009-12-01 16:00:00')) ,    
  
  
  PARTITION p06 VALUES LESS THAN (MAXVALUE)    
  
  
);    
  
  
show create table t2\G    
  
  
*************************** 1. row ***************************    
  
  
Table: t2    
  
  
Create Table: CREATE TABLE `t2` (    
  
  
  `dt` datetime DEFAULT NULL   
  
  
) ENGINE=MyISAM DEFAULT CHARSET=latin1    
  
  
/*!50500 PARTITION BY RANGE (to_seconds(dt))    
  
  
(PARTITION p01 VALUES LESS THAN (63426787200) ENGINE = MyISAM,    
  
  
PARTITION p02 VALUES LESS THAN (63426816000) ENGINE = MyISAM,    
  
  
PARTITION p03 VALUES LESS THAN (63426844800) ENGINE = MyISAM,    
  
  
PARTITION p04 VALUES LESS THAN (63426873600) ENGINE = MyISAM,    
  
  
PARTITION p05 VALUES LESS THAN (63426902400) ENGINE = MyISAM,    
  
  
PARTITION p06 VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */  
 
 

Because we do not use the COLUMNS keyword, we cannot use it because it does not support mixed COLUMNS and functions. The record value in the table definition is the calculation result of the TO_SECONDS function.

But we still want to thank the new function. We can reverse this value and convert it into a date that is easier to understand.

 
 

  
  
select   
  
  
  partition_name part,    
  
  
  partition_expression expr,    
  
  
  from_seconds(partition_description) descr,    
  
  
  table_rows    
  
  
FROM   
  
  
INFORMATION_SCHEMA.partitions    
  
  
WHERE   
  
  
    TABLE_SCHEMA = 'test'   
  
  
    AND TABLE_NAME='t2';    
  
  
+------+----------------+---------------------+------------+    
  
  
| part | expr           | descr               | table_rows |    
  
  
+------+----------------+---------------------+------------+    
  
  
| p01  | to_seconds(dt) | 2009-11-30 08:00:00 |          0 |    
  
  
| p02  | to_seconds(dt) | 2009-11-30 16:00:00 |          0 |    
  
  
| p03  | to_seconds(dt) | 2009-12-01 00:00:00 |          0 |    
  
  
| p04  | to_seconds(dt) | 2009-12-01 08:00:00 |          0 |    
  
  
| p05  | to_seconds(dt) | 2009-12-01 16:00:00 |          0 |    
  
  
| p06  | to_seconds(dt) | 0000-00-00 00:00:00 |          0 |    
  
  
+------+----------------+---------------------+------------+  
 
 

Summary:

MySQL 5.5 is definitely good news for partition users. Although it does not provide direct performance enhancement methods, if you evaluate performance based on the response time), it is easier to use enhancements, and the truncate partition command can save DBA a lot of time, sometimes for end users.

I will introduce the truncate partition of MySQL 5.5. I hope this introduction will be helpful to you!

Source: http://lujia35.iteye.com/blog/718899.