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);
