MySQL: Small Guide for Simple Partitioning

Una mini guia de particionamiento sencillo en MySQL 5.5 basado en fecha:

Nota(1): Hay que incluir en la PK el campo que quieras usar en el particionado, así te ahorraras el error:

ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function

CREATE TABLE IF NOT EXISTS T1 (
  ID INT NOT NULL AUTO_INCREMENT,
  PART DATE NOT NULL,
  NUM INT NOT NULL,
  PRIMARY KEY (ID,PART))
ENGINE = InnoDB 
  PARTITION BY RANGE COLUMNS(PART) ( 
    PARTITION p20140417 VALUES LESS THAN ('2014-04-18'),  
    PARTITION p20140418 VALUES LESS THAN ('2014-04-19'),  
    PARTITION p20140419 VALUES LESS THAN ('2014-04-20')
);

Nota(2): Ojo con las columnas DATE/DATETIME, se almacena la fecha tal cual (a diferencia de un TIMESTAMP que se almacena en formato UTC).

Si la tabla ya existe sin particionamiento y quiere activarse:

ALTER TABLE T1 
  PARTITION BY RANGE COLUMNS(PART) ( 
    PARTITION p20140417 VALUES LESS THAN ('2014-04-18'),  
    PARTITION p20140418 VALUES LESS THAN ('2014-04-19'),  
    PARTITION p20140419 VALUES LESS THAN ('2014-04-20')
);

Agregar nuevas particiones a un particionamiento existente:

ALTER TABLE T1 
  ADD PARTITION (
    PARTITION p20140420 VALUES LESS THAN ('2014-04-21'),
    PARTITION p20140421 VALUES LESS THAN ('2014-04-22')
);

Para hacer el DROP de particiones y los datos contenidos en ellas:

ALTER TABLE T1 
  DROP PARTITION p20140417, p20140418;

Para quitar el particionamiento (sin perder datos):

ALTER TABLE T1 REMOVE PARTITIONING;

Para ver los particionamientos que hay definidos:

SELECT TABLE_SCHEMA,TABLE_NAME,PARTITION_NAME,
       PARTITION_METHOD,PARTITION_DESCRIPTION,TABLE_ROWS 
  FROM INFORMATION_SCHEMA.PARTITIONS
 WHERE PARTITION_NAME IS NOT NULL;

O un resumen de uso:

SELECT TABLE_SCHEMA,TABLE_NAME,PARTITION_NAME,TABLE_ROWS 
  FROM INFORMATION_SCHEMA.PARTITIONS 
 WHERE PARTITION_NAME IS NOT NULL;

+--------------+------------+----------------+------------+
| TABLE_SCHEMA | TABLE_NAME | PARTITION_NAME | TABLE_ROWS |
+--------------+------------+----------------+------------+
| S1           | T1         | p20140417      |          3 |
| S1           | T1         | p20140418      |          3 |
| S1           | T1         | p20140419      |          2 |
+--------------+------------+----------------+------------+
3 rows in set (0.01 sec)

Para poder sacar los planes de acceso del particionamiento:

EXPLAIN PARTITIONS 
 SELECT * FROM T1
  WHERE ID=1 
    AND PART = UTC_DATE(); 

+----+-------------+-------+------------+-------+---------------+---------+---------+-------------+------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref         | rows | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------------+------+-------+
|  1 | SIMPLE      | T1    | p20140417  | const | PRIMARY       | PRIMARY | 11      | const,const |    1 |       |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------------+------+-------+
1 row in set (0.00 sec)

EXPLAIN PARTITIONS 
 SELECT * FROM T1
  WHERE ID=1;

+----+-------------+-------+-------------------------------+------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | partitions                    | type | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+-------+-------------------------------+------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | T1    | p20140417,p20140418,p20140419 | ref  | PRIMARY       | PRIMARY | 8       | const |    2 |       |
+----+-------------+-------+-------------------------------+------+---------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)

Y la select de prueba que usa la columna de particionamiento (PART):

-- asumiento que hoy es 2014-04-17 y tenemos 365 particionamientos (1 por dia)
SELECT * FROM T1
 WHERE ID=3 
   AND PART BETWEEN UTC_DATE()-1 AND UTC_DATE()+1;
-- en este caso la busqueda estaria entre: p20140416, p20140417 y p20140418

Nota(3): El numero maximo de particiones por tabla es de 1024.

Referencias:
Restrictions and Limitations on Partitioning
Partitioning Types
RANGE Partitioning
RANGE COLUMNS partitioning
ALTER TABLE
ALTER TABLE Partition Operations
Data Type Storage Requirements
The DATE, DATETIME, and TIMESTAMP Types
Date and Time Functions