Particionar

Crear particiones en MySQL

Particionar tablas en MySQL nos permite rotar la información de nuestras tablas en diferentes particiones, consiguiendo así realizar consultas más rápidas y recuperar espacio en disco al borrar los registros. El uso más común de particionado es según fecha (date). Para ver si nuestra base de datos soporta particionado simplemente ejecutamos:

SHOW VARIABLES LIKE '%partition%';

A continuación veremos un ejemplo de cómo particionar una tabla por mes y posteriormente borrar o modificar su información.

Las particiones son por tabla, es decir parto una tabla en n partes, el numero n impacta en la performance por lo tanto hay que elegir el mejor posible y ir probando. Para partir una tabla necesito un criterio de partición; por ejemplo las facturas más viejas a esta fecha se encuentran en una parte; las más viejas que esta otra fecha en otra y así … MySql implementa el particionado horizontal. Básicamente, se pueden realizar cuatro tipos de particionado, que son:

·         RANGE: la asignación de los registros de la tabla a las diferentes particiones se realiza según un rango de valores definido sobre una determinada columna de la tabla o expresión. Es decir, nosotros indicaremos el numero de particiones a crear, y para cada partición, el rango de valores que serán la condición para insertar en ella, de forma que cuando un registro que se va a introducir en la base de datos tenga un valor del rango en la columna/expresion indicada, el registro se insertara en dicha partición.

·         LIST: la asignación de los registros de la tabla a las diferentes particiones se realiza según una lista de valores definida sobre una determinada columna de la tabla o expresión. Es decir, nosotros indicaremos el numero de particiones a crear, y para cada partición, la lista de valores que serán la condición para insertar en ella, de forma que cuando un registro que se va a introducir en la base de datos tenga un valor incluido en la lista de valores, el registro se insertara en dicha partición.

·         HASH: este tipo de partición esta pensado para repartir de forma equitativa los registros de la tabla entre las diferentes particiones. Mientras en los dos particionados anteriores eramos nosotros los que teníamos que decidir, según los valores indicados, a que partición llevamos los registros, en la partición HASH es MySql quien hace ese trabajo. Para definir este tipo de particionado, deberemos de indicarle una columna del tipo integer o una función de usuario que devuelva un integer. En este caso, aplicamos una función sobre un determinado campo que devolvera un valor entero. Según el valor, MySql insertará el registro en una partición distinta.

·         KEY: similar al HASH, pero la función para el particionado la proporciona MySql automáticamente (con la función MD5). Se pueden indicar los campos para el particionado, pero siempre han de ser de la clave primaria de la tabla o de un índice único.

·         SUBPARTITIONS: Mysql permite además realizar subparticionado. Permite la división de cada partición en múltiples subparticiones.

 

Luego de toda esta teoría solo quedan ganas de partir, de partir tablas! Y se parten así:

CREATE TABLE by_year ( d DATE ) PARTITION BY RANGE (YEAR(d)) ( PARTITION P1 VALUES LESS THAN (2001), PARTITION P2 VALUES LESS THAN (2002), PARTITION P3 VALUES LESS THAN (2003), PARTITION P4 VALUES LESS THAN (MAXVALUE) );

Borrar particiones

Lo bueno de trabajar con particiones es que podemos borrar rápidamente registros sin tener que recorrer toda la tabla e inmediatamente recuperar el espacio en disco utilizado por la tabla.

Por ejemplo si queremos borrar la partición más antigua simplemente ejecutamos:

ALTER TABLE reports DROP PARTITION p201111;

Añadir particiones

En el ejemplo anterior las 2 últimas particiones creadas han sido:

PARTITION p201205 VALUES LESS THAN (TO_DAYS("2012-06-01")),
PARTITION pDefault VALUES LESS THAN MAXVALUE

El problema es que todos los INSERTs que se hagan después de mayo de 2012 se insertarán en pDefault. La solución sería añadir particiones nuevas para cubrir los próximos meses:

ALTER TABLE reports REORGANIZE PARTITION pDefault INTO (
  PARTITION p201206 VALUES LESS THAN (TO_DAYS("2012-07-01")),
  PARTITION pDefault VALUES LESS THAN MAXVALUE);

En el caso que no tuvieramos una partición del tipo pDefault simplemente ejecutamos:

ALTER TABLE reports ADD PARTITION (PARTITION p201206 VALUES LESS THAN (TO_DAYS("2012-07-01")));

Consultar particiones

Para consultar información de particiones creadas en una tabla así como también los registros que contiene cada una ejecutamos:

SELECT PARTITION_NAME,TABLE_ROWS FROM information_schema.PARTITIONS WHERE TABLE_NAME='reports';

 

Particionado de Tablas en Oracle

Basicamente, el particionado se realiza utilizando una clave de particionado (partitioning key), que determina en que partición de las existentes en la tabla van a residir los datos que se insertan. Oracle también permite realizar el particionado de indices y de tablas organizadas por indices. Cada partición ademas puede tener sus propias propiedades de almacenamiento.

• Particionado Range: la clave de particionado viene determinada por un rango de valores, que determina la partición donde se almacenara un valor.
• Particionado Hash: la clave de particionado es una función hash, aplicada sobre una columna, que tiene como objetivo realizar una distribución equitativa de los registros sobre las diferentes particiones. Es útil para particionar tablas donde no hay unos criterios de particionado claros, pero en la que se quiere mejor el rendimiento.
• Particionado List: la clave de particionado es una lista de valores, que determina cada una de las particiones.
• Particionado Composite: los particionados anteriores eran del tipo simples (single o one-level), pues utilizamos un unico método de particionado sobre una o mas columnas. Oracle nos permite utilizar metodos de particionado compuestos, utilizando un primer particionado de un tipo determinado, y luego para cada particion, realizar un segundo nivel de particionado utilizando otro metodo. Las combinaciones son las siguientes (se han ido ampliando conforme han ido avanzando las versiones): range-hash, range-list, range-range, list-range, list-list, list-hash y hash-hash (introducido en la versión 11g).
• Particionado Interval: tipo de particionado introducido igualmente en la versión 11g. En lugar de indicar los rangos de valores que van a determinar como se realiza el particionado, el sistema automáticamente creara las particiones cuando se inserte un nuevo registro en la b.d. Las técnicas de este tipo disponible son Interval, Interval List, Interval Range e Interval Hash (por lo que el particionado Interval es complementario a las técnicas de particionado vistas anteriormente).
• Particionado System: se define la tabla particionada indicando las particiones deseadas, pero no se indica una clave de particionamiento. En este tipo de particionado, se delega la gestión del particionado a las aplicaciones que utilicen la base de datos (por ejemplo, en las sentencias sql de inserción deberemos de indicar en que partición insertamos los datos).

Particionado Range

Esta forma de particionamiento requiere que los registros estén identificado por un “partition key” relacionado por un predefinido rango de valores. El valor de las columnas “partition key” determina la partición a la cual pertenecerá el registro.

CREATE TABLE sales
  ( prod_id       NUMBER(6)
  , cust_id       NUMBER
  , time_id       DATE
  , channel_id    CHAR(1)
  , promo_id      NUMBER(6)
  , quantity_sold NUMBER(3)
  , amount_sold   NUMBER(10,2)
  )
 PARTITION BY RANGE (time_id)
 ( PARTITION sales_q1_2006 VALUES LESS THAN (TO_DATE('01-APR-2006','dd-MON-yyyy'))     TABLESPACE tsa
 , PARTITION sales_q2_2006 VALUES LESS THAN (TO_DATE('01-JUL-2006','dd-MON-yyyy'))    TABLESPACE tsb
 , PARTITION sales_q3_2006 VALUES LESS THAN (TO_DATE('01-OCT-2006','dd-MON-yyyy'))    TABLESPACE tsc
 , PARTITION sales_q4_2006 VALUES LESS THAN (TO_DATE('01-JAN-2007','dd-MON-yyyy'))    TABLESPACE tsd
 );

 

Particionado Hash

Los registros de la tabla tienen su localización física determinada aplicando un valor hash a la columna del partition key. La funcion hash devuelve un valor automatico que determina a que partición irá el registro. Es una forma automática de balancear el particionado. Hay varias formas de construir este particionado. En el ejemplo siguiente vemos una definición sin indicar los nombres de las particiones (solo el número de particiones):

CREATE TABLE dept (deptno NUMBER, deptname VARCHAR(32))
     PARTITION BY HASH(deptno) PARTITIONS 16;

Igualmente, se pueden indicar los nombres de cada particion individual o los tablespaces donde se localizaran cada una de ellas:

CREATE TABLE dept (deptno NUMBER, deptname VARCHAR(32))
     STORAGE (INITIAL 10K)
     PARTITION BY HASH(deptno)
       (PARTITION p1 TABLESPACE ts1, PARTITION p2 TABLESPACE ts2,
        PARTITION p3 TABLESPACE ts1, PARTITION p4 TABLESPACE ts3);

Particionado List

Este tipo de particionado fue añadido por Oracle en la versión 9, permitiendo determinar el particionado según una lista de valores definidos sobre el valor de una columna especifica.

CREATE TABLE sales_list (salesman_id NUMBER(5), salesman_name VARCHAR2(30),
sales_state VARCHAR2(20),
sales_amount NUMBER(10),
sales_date DATE)
PARTITION BY LIST(sales_state)
(
PARTITION sales_west VALUES('California', 'Hawaii'),
PARTITION sales_east VALUES ('New York', 'Virginia', 'Florida'),
PARTITION sales_central VALUES('Texas', 'Illinois')
PARTITION sales_other VALUES(DEFAULT)
);

Este particionado tiene algunas limitaciones, como que no soporta múltiples columnas en la clave de particionado (como en los otros tipos), los valores literales deben ser únicos en la lista, permitiendo el uso del valor NULL (aunque no el valor MAXVALUE, que si puede ser utilizado en particiones del tipo Range).

Particionado Interval

El particionado Interval ha sido introducido en la versión 11g para habilitar un mantenimiento de particiones desasistido. Normalmente, cuando realizamos un particionado sobre una tabla, indicamos una lista de valores o rangos para crear de antemano las particiones.

un ejemplo:

 CREATE TABLE T_11G(C1 NUMBER(38,0),
 C2 VARCHAR2(10),
 C3 DATE)
 PARTITION BY RANGE (C3) INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
 (PARTITION P0902 VALUES LESS THAN (TO_DATE('2009-03-01 00:00:00','YYYY-MM-DD HH24:MI:SS')));

 

Particionado System

Una de las nuevas funcionalidades introducida en la version 11g es el denominado partitioning interno o de sistema. En este particionado Oracle no realiza la gestión del lugar donde se almacenaran los registros, sino que seremos nosotros los que tendremos que indicar en que partición se hacen las inserciones.

create table t (c1 int,
                c2 varchar2(10),
                c3 date)
     partition by system
    (partition p1,
     partition p2,
     partition p3);