• Author / Uploaded
• juanaves

Citation preview

FRAGMENTACION EN MySQL Desde la versión 5.1 existe la posibilidad de particionar nuestras tablas de forma horizontal (en líneas), algo que nos puede ayudar en casos puntuales a mejorar el rendimiento de nuestra base de datos. Resumiendo, este sistema nos permite dividir lógicamente una tabla muy grande en otras más pequeñas, dentro de un rango de valores que nosotros indiquemos, de forma que la consulta de datos sea más rápida. Su uso es muy sencillo pero... ¿cuando debemos utilizarlo? 

Cuando la tabla sea tan grande que los índices no entren en RAM.



Cuando tengamos una tabla realmente grande (no hablo de megas).



Cuando almacenamos datos históricos.



Cuando los datos no paran de crecer y crecer...

Hay que tener en cuenta que este particionado es totalmente transparente para el usuario (y por lógica también para nuestra aplicación) por lo que en el caso de decidirnos por esta solución el cambio será poco dramático. Solamente tendremos que tener en cuenta estos detalles: 

La columna que utilicemos para definir el rango de las particiones debe ser un INT, no se acepta cualquier otro valor.



Si tenemos una clave única o una primary key, esta debe usarse para particionar.



Como máximo se permiten 1024 particiones.



No se permiten claves externas.

Si tenemos una tabla con millones de registros y hacemos una select, MySQL se deberá recorrer toda la tabla (en caso de no usar índices, si estos ocupan más que la RAM) o se tendrá que recorrer todos los índices. Esto, contra más grande es la tabla, mas ineficiente es:

Gracias al particionado es posible hacer una búsqueda en una fracción mucho más pequeña de nuestra tabla. Si la dividimos de forma que cada partición incluya 100 filas (particionando por ID), MySQL sabe que el dato se tiene que encontrar en la segunda partición, por lo que se evita tener que buscar en los restantes X millones de registros.

La mejora es clara, pero no siempre particionar nos va a dar mayor rendimiento. Si la tabla NO es lo suficientemente grande incluso podemos degradar el rendimiento.

Tipos de fragmentación FRAGMENTACION VERTICAL Alumno (código, Nombre, apellido, grado, edad, asignaturas) Esta relación pude ser fragmentada verticalmente de la siguiente forma: alumno1=π código, Nombre, apellido, edad ALUMNO

alumno2=π código, grado, asignatura ALUMNO La operación de reconstrucción es: ALUMNO= alumno1 join alumno2 DATOS 1 código

nombre

apellido

grado

edad

Asignatura

12345

Laura Camila

casas

2

7

Matemáticas

124568

José Antonio

Rodríguez

3

8

español

ALUMNO1 CODIGO

NOMBRE

APELLIDO

EDAD

12345

Laura Camila

Casas

7

124568

José Antonio

rodríguez

8

ALUMNO2

Código

Grado

Asignatura

12345

2

Matemáticas

124568

3

Español

FRAGMENTACION HORIZONTAL Alumno (código, Nombre, apellido, grado, edad, asignaturas)

Código nombre

Apellido

grado

edad

Asignatura

12345

Casas

2

7

Matemáticas

124568 José Antonio

Rodríguez

3

8

español

876334 Oscar Ernesto

Majares

8

Matemáticas

Laura Camila

2

Código nombre

Apellido

grado edad Asignatura

12345

Casas

2

7

Matemáticas

876334 Oscar Ernesto

Majares

2

8

Matemáticas

Código nombre

Apellido

grado edad Asignatura

124568 José Antonio

Rodríguez

3

Laura Camila

8

español

FRAGMENTACION MIXTA Alumno (código, Nombre, apellido, grado, edad, asignaturas)

Código

nombre

Apellido

grado edad

12345

Laura Camila

Casas

2

7

124568

José Antonio

Rodríguez

3

8

876334

Oscar Ernesto

Majares

Código

nombre

Apellido

grado Asignatura

12345

Laura Camila

Casas

2

Matemáticas

124568

José Antonio

Rodríguez

3

español

876334

Oscar Ernesto

Majares

Código

nombre

Apellido

grado edad

Asignatura

12345

Laura Camila

Casas

2

Matemáticas

2

2

8

Matemáticas

7

124568

José Antonio

Rodríguez

876334

Oscar Ernesto

Majares

3 2

8

español

8

Matemáticas

Fragmentación en MySQL Al utilizar comandos de fragmentado a nivel de tablas, mysqld utiliza una clave de partición y un algoritmo de particionado para determinar la división de los datos entre los fragmentos. Los algoritmos de fragmentación que tenemos son: RANGE: Si la clave de fragmentación está dentro de un rango de valores. LIST: El fragmento es seleccionado de acuerdo a una lista de valores enteros. HASH: El fragmento se elige de acuerdo a una función de hash. KEY: Un algoritmo interno es utilizado por mysqld para elegir como serán distribuidos los datos entre los fragmentos..

Fragmentado RANGE (Por rango de valores) Suponiendo que tenemos la siguiente tabla definida: CREATE TABLE empleados ( Id_empleado INT NOT NULL, nombre VARCHAR(30) NOT NULL, apellidos VARCHAR(30) NOT NULL, fecha_inicio DATE NOT NULL DEFAULT ’2000-01-01’, fecha_termino DATE DEFAULT NULL, salario DECIMAL (8,2) NOT NULL, codigo_de_trabajo INT NOT NULL, id_almacenamiento INT NOT NULL ); Suponiendo que queremos almacenar los datos de 300 empleados en tres tablas de a 100 cada una, procedemos con la siguiente instrucción: ALTER TABLE empleados PARTITION BY RANGE (id_almacenamiento) ( PARTITION p0 VALUES LESS THAN (101), PARTITION p1 VALUES LESS THAN (201), PARTITION p2 VALUES LESS THAN (301), );

Esta instrucción utiliza el atributo id_almacenamiento como clave de fragmentado (partition key), y las particiones son p0, p1, p2, , en este caso las tres particiones corresponden a los registros en el rango desde 0 hasta 300, en el caso de que quisiéramos insertar un registro con un valor mayor a 300, nos generaría un error como el siguiente: ERROR 1526 (HY000): Table has no partition for value 301. Si desde la definición de la tabla queremos especificar un esquema de fragmentado, usaríamos la siguiente instrucción: CREATE TABLE empleados ( Id_empleado INT NOT NULL, nombre VARCHAR(30) NOT NULL, apellidos VARCHAR(30) NOT NULL, fecha_inicio DATE NOT NULL DEFAULT ’2000-01-01’, fecha_termino DATE DEFAULT NULL, salario DECIMAL (8,2) NOT NULL, codigo_de_trabajo INT NOT NULL, id_almacenamiento INT NOT NULL ) PARTITION BY RANGE (id_almacenamiento) ( PARTITION p0 VALUES LESS THAN (101), PARTITION p1 VALUES LESS THAN (201), PARTITION p2 VALUES LESS THAN (301), PARTITION pfinal VALUES LESS THAN MAXVALUE); Fragmentado LIST (Por lista definida) Suponiendo que ahora tenemos la misma tabla, pero lo que queremos es distribuir a los empleados de acuerdo a una lista de valores definida, los valores se distribuirán de acuerdo a los valores definidos en cada lista de cada partición. CREATE TABLE empleados ( Id_empleado INT NOT NULL, nombre VARCHAR(30) NOT NULL, apellidos VARCHAR(30) NOT NULL, fecha_inicio DATE NOT NULL DEFAULT ’2000-01-01’, fecha_termino DATE DEFAULT NULL, salario DECIMAL (8,2) NOT NULL, codigo_de_trabajo INT NOT NULL, id_almacenamiento INT NOT NULL ) PARTITION BY LIST (id_almacenamiento) ( PARTITION Sureste VALUES IN (1,2,3,4,5,6,7,8,9,10,15,17,18,20,21,24), PARTITION AtlanticoMedio VALUES IN (11,12,13,14,16,19,22,23,25,26,27,28), PARTITION Noreste VALUES IN (29,30,33,38,40,41,50,56,64,65,75), PARTITION EsteMedio VALUES IN (32,34,35,42,43,49,51,61,62,63,71), PARTITION Noroeste VALUES IN (46,53,58,67,68,69,72,74), PARTITION Canada VALUES IN (31,47,52,59,73), PARTITION Inlaterra VALUES IN (39,55)

); Fragmentado HASH (Por dispersión) El uso de HASH nos permite dividir los datos de forma equitativa entre todas las particiones, cosa que con otros tipos de particiones podría no pasar. De esta forma, si estamos trabajando una tabla enorme y la queremos dividir en 10 particiones, estas tendrán un número de valores muy similar.

CREATE TABLE empleados ( Id_empleado INT NOT NULL, nombre VARCHAR(30) NOT NULL, apellidos VARCHAR(30) NOT NULL, fecha_inicio DATE NOT NULL DEFAULT ’2000-01-01’, fecha_termino DATE DEFAULT NULL, salario DECIMAL (8,2) NOT NULL, codigo_de_trabajo INT NOT NULL, id_almacenamiento INT NOT NULL ) PARTITION BY HASH (id_almacenamiento) PARTITIONS 16;

Fragmentado KEY (Fragmentado por clave) Con Key NO es necesario indicar la columna que deseamos para particionar, en ese caso hará uso de la clave primaria. KEY es muy parecida a HASH, solo que en lugar de indicarle nosotros el HASH mediante una expresión, lo hará el propio MySQL usando MD5. CREATE TABLE empleados ( Id_empleado INT NOT NULL PRIMARY KEY, nombre VARCHAR(30) NOT NULL, apellidos VARCHAR(30) NOT NULL, fecha_inicio DATE NOT NULL DEFAULT ’2000-01-01’, fecha_termino DATE DEFAULT NULL, salario DECIMAL (8,2) NOT NULL, codigo_de_trabajo INT NOT NULL, id_almacenamiento INT NOT NULL ) PARTITION BY HASH (Id_empleado) PARTITIONS 16;

Ejercicio con la BD employees 1. Particiona la Tabla employees mediante RANGO por el campo store_id en 5 partes (parte1 >6, parte 2