Hernández González 2013: Actividad #16 Particiones MySQL y Oracle

El particionado es un concepto parecido, aunque automatizado, que puede ahorrarnos muchos quebraderos de cabeza. Consiste en dividir los datos en particiones más pequeñas (hasta 1024) procurando, porque de otra forma sería absurdo, que sólo haya que acceder a una partición a la hora de buscar una tupla.

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.

Crear particiones en MySQL

1.- Creamos la tabla reports:

CREATE TABLE reports (
  id int(10) NOT NULL AUTO_INCREMENT,
  date datetime NOT NULL,
  report TEXT,
  PRIMARY KEY (id,date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Como se puede ver hemos añadido como índice de tabla el campo date, esto es necesario si luego queremos particionar por fecha.

2.- Ahora que tenemos la tabla creada vamos a particionar por mes:

ALTER TABLE reports PARTITION BY RANGE(TO_DAYS(date))(
  PARTITION p201111 VALUES LESS THAN (TO_DAYS("2011-12-01")),
  PARTITION p201112 VALUES LESS THAN (TO_DAYS("2012-01-01")),
  PARTITION p201201 VALUES LESS THAN (TO_DAYS("2012-02-01")), 
  PARTITION p201202 VALUES LESS THAN (TO_DAYS("2012-03-01")),
  PARTITION p201203 VALUES LESS THAN (TO_DAYS("2012-04-01")),
  PARTITION p201204 VALUES LESS THAN (TO_DAYS("2012-05-01")),
  PARTITION p201205 VALUES LESS THAN (TO_DAYS("2012-06-01")),
  PARTITION pDefault VALUES LESS THAN MAXVALUE
);

La última partición (pDefault) tendrá todos los registros que no entren en las particiones anteriores. De esta manera nos aseguramos que la información nunca dejará de insertarse en la tabla.

Se puede particionar una tabla de 5 maneras diferentes:

Por rango: para construir nuestras particiones especificamos rangos de valores. Por ejemplo, podríamos segmentar los datos en 12 particiones: una para los contratos de 1950 a 1960, otra para los años 60, los 70, 80, 90, la década del 2000 y la década actual
1. ALTER TABLE contratos
2. PARTITION BY RANGE(YEAR(fechaInicio)) (
3. PARTITION partDecada50 VALUES LESS THAN (1960),
4. PARTITION partDecada60 VALUES LESS THAN (1970),
5. PARTITION partDecada70 VALUES LESS THAN (1980),
6. PARTITION partDecada80 VALUES LESS THAN (1990),
7. PARTITION partDecada90 VALUES LESS THAN (2000),
8. PARTITION partDecada00 VALUES LESS THAN (2010),
9. PARTITION partDecada10 VALUES LESS THAN MAXVALUE
10. );
Por listas: para construir nuestras particiones especificamos listas de valores concretos.
1. ALTER TABLE contratos
2. PARTITION BY LIST(YEAR(fechaInicio)) (
3. PARTITION partDecada50 VALUES IN (1950, 1951, 1952, 1953, 1954, 1955, 1956, 1957, 1958, 1959),
4. PARTITION partDecada60 VALUES IN (1960, 1961, 1962, 1963, 1964, 1965, 1966, 1967, 1968, 1969),
5. PARTITION partDecada70 VALUES IN (1970, 1971, 1972, 1973, 1974, 1975, 1976, 1977, 1978, 1979),
6. PARTITION partDecada80 VALUES IN (1980, 1981, 1982, 1983, 1984, 1985, 1986, 1987, 1988, 1989),
7. PARTITION partDecada90 VALUES IN (1990, 1991, 1992, 1993, 1994, 1995, 1996, 1997, 1998, 1999),
8. PARTITION partDecada00 VALUES IN (2000, 2001, 2002, 2003, 2004, 2005, 2006,
9. 2007, 2008, 2009),
10. PARTITION partDecada10 VALUES IN (2010, 2011, 2012, 2013, 2014, 2015, 2016,
11. 2017, 2018, 2019)
12. );
Por hash: MySQL se encarga de distribuir las tuplas automáticamente usando una operación de módulo. Sólo hay que pasarle una columna o expresión que resulte en un entero (el hash) y el número de particiones que queramos crear.
1. ALTER TABLE contratos
2. PARTITION BY HASH(YEAR(fechaInicio))
3. PARTITIONS 7;
Por clave: similar a la partición por hash, pero en este caso no necesitamos pasarle un entero; MySQL utilizará su propia función de hash para generarlo. Si no se indica ninguna columna a partir de la que generar el hash, se utiliza la clave primaria por defecto.
1. ALTER TABLE contratos
2. PARTITION BY KEY()
3. PARTITIONS 7;
Compuesta: podemos combinar los distintos métodos de particionado y crear particiones de particiones

EXPLAIN SELECT COUNT(*)
FROM contratos
WHERE fechaInicio BETWEEN '1950-01-01' AND '1955-12-31'

select_type	table	type	key	rows	Extra
SIMPLE	contratos	ALL		239796	Using where

EXPLAIN PARTITIONS SELECT COUNT(*)
FROM contratos
WHERE fechaInicio BETWEEN '1950-01-01' AND '1955-12-31'

select_type	table	partitions	type	key	rows	Extra
SIMPLE	contratos	partDecada50	ALL		8640	Using where

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

TIPOS DE PARTICIONADO EN ORACLE

Oracle 8.0: Particionamiento por Rango.
• Oracle 8i: Particionamiento por Rango, Hash y Composite.
• Oracle 9iR2/10g: Particionamiento por Rango, Hash, Composite y el tipo List
• Oracle 11g: Columnas virtuales para particionar (que no existen fisicamente en la tabla), particionado de Sistema (donde podemos gestionar directamente en que partición de la tabla se insertan los registros) y el particionado por Intervalos.

PARTICIONAMIENTO POR RANGO

Los datos se distribuyen de acuerdo con el rango de valores de la clave de particionamiento. La distribución de datos es continua.
Se 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.
Se deben considerar las siguientes reglas:

• Cada partición se define con la clausula VALUES LESS THAN, la que indica el límite superior no inclusive para las particiones, cualquier valor de la clave de la partición igual o superior, es añadida a la próxima partición.
• Todas las particiones, excepto la primera, tienen un límite inferior, especificado en la clausula VALUES LESS THAN de la partición previa.
• Un literal MAXVALUE puede ser definido para la última partición; representa un valor virtual de infinito.

Ejemplo
create table ESTUDIANTES (
CEDULA VARCHAR2(10) not null,
NOMBRES VARCHAR2(40),
APELLIDOS VARCHAR2(40),
SEXO CHAR(1),
ID_ESCUELA INTEGER,
constraint PK_ESTUDIANTES primary key (CEDULA)
)
PARTITION BY RANGE (id_escuela)
(PARTITION EISIC VALUES LESS THAN (2) TABLESPACE TEISIC,
PARTITION EITEX VALUES LESS THAN (3) TABLESPACE TEITEX,
PARTITION CIME VALUES LESS THAN (4) TABLESPACE TCIME,
PARTITION CIERCOM VALUES LESS THAN (5) TABLESPACE TCIERCOM;

PARTICIONAMIENTO POR LISTA

El particionamiento por lista no soporta claves de particionamiento formada por varios atributos. La clave de particionado es una lista de valores, que determina cada una de las particiones, la distribución de datos se define por el listado de valores de la clave de partición.

Ejemplo:
create table MATERIAS (
ID_MATERIA INTEGER not null,
MATERIA VARCHAR2(50),
ID_ESCUELA INTEGER,
MATERIA_ANTERIOR INTEGER,
ID_NIVEL INTEGER,
constraint PK_MATERIAS primary key (ID_MATERIA)
)
PARTITION BY LIST (id_escuela)
(
PARTITION EISIC values(1)TABLESPACE TEISIC,
PARTITION EITEX values(2)TABLESPACE TEITEX,
PARTITION CIME values(3)TABLESPACE TCIME,
PARTITION CIERCOM values(4)TABLESPACE TCIERCOM,
PARTITION ESDYM values(5)TABLESPACE TESDYM,
PARTITION ESIIN values(6)TABLESPACE TESIIN)

PARTICIONAMIENTO POR HASH
La correspondencia entre las filas y las particiones se realiza a través de una función de hash. Es una opción útil cuando:
• Se desconoce la correspondencia en función de los rangos o no hay unos criterios de particionado claros.
• El rango de las particiones difiere sustancialmente o es difícil balancearla manualmente.

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.
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.
Se puede definir la partición sin indicar los nombres de las particiones, solo poniendo el número de particiones deseadas.

Ejemplo:
create table DOCENTES (
CEDULA_DOCENTE VARCHAR2(12) not null,
NOMBRES VARCHAR2(40),
APELLIDOS VARCHAR2(40),
ID_TITULOS INTEGER,
constraint PK_DOCENTES primary key (CEDULA_DOCENTE)
)
PARTITION BY HASH (cedula_docente)
partitions 4 store in (tdocente1, tdocente2,tdocente3,tdocente4);

PARTICIÓN POR COMPOSICIÓN
Oracle nos permite utilizar métodos de particionado compuestos, ya que se conjuga el uso de dos particionados a la vez. Primero la tabla se particiona con un primer método de distribución de datos

y luego cada partición se vuelve a dividir en subparticiones utilizando un segundo método de distribución de datos.
Las técnicas de partición compuesta disponibles son:
• Rango - Hash
• Rango - List
• Rango - Rango
• List - Rango

• List - List
• List - Hash

Las particiones más generales se hacen con el método de rango, cada partición se sub-particiona con el método de hash o por lista.

Ejemplo:
/*==============================================================*/
/* Table: ESTUDIANTES PARTITION COMPOSICION RANGE Y HASH */
/*==============================================================*/
create table ESTUDIANTES (
CEDULA VARCHAR2(10) not null,
NOMBRES VARCHAR2(40),
APELLIDOS VARCHAR2(40),
SEXO CHAR(1),
ID_ESCUELA INTEGER,
constraint PK_ESTUDIANTES primary key (CEDULA)
)
PARTITION BY RANGE (id_escuela)
SUBPARTITION BY HASH (SEXO)
SUBPARTITION TEMPLATE (
SUBPARTITION MASCULINO,
SUBPARTITION FEMENINO)
(PARTITION EISIC VALUES LESS THAN (2) TABLESPACE TEISIC,
PARTITION EITEX VALUES LESS THAN (3) TABLESPACE TEITEX,
PARTITION CIME VALUES LESS THAN (4) TABLESPACE TCIME,
PARTITION CIERCOM VALUES LESS THAN (5) TABLESPACE TCIERCOM,);

Hernández González 2013

Actividad #16 Particiones MySQL y Oracle

Crear particiones en MySQL

Se puede particionar una tabla de 5 maneras diferentes:

Borrar particiones

Añadir particiones

Consultar particiones

No hay comentarios:

Publicar un comentario

Archivo del blog