Particionando en MySQL 5.1

Particionado de tablas en MySQL

¿Qué es el particionado de tablas?

El particionado de tablas en MySQL nos permite distribuir porciones de tablas en un sistema de ficheros. Esta distribución se realiza de acuerdo a reglas definidas por el usuario para ajustarse a sus necesidades. Estas reglas reciben el nombre de funciones de particionado y existen varios tipos de funciones distintas: particionado por rangos o listas de valores, funciones hash internas o lineales y por clave.

¿Qué tipos de particionados tenemos disponibles?

Existen dos tipos de particionados: particionado horizontal, en el que las distintas filas de la tabla se asignan una partición en concreto; y el particionado vertical, en el que las distintas columnas de la tabla se asignan a una partición en concreto. Actualmente (versión 5.5 de MySQL), sólo está soportado el particionamiento horizontal.

¿Qué ventajas tiene el particionado de tablas?

La ventaja principal del particionado de tablas es la optimización a la hora de acceder a los datos que almacena MySQL, ya que podemos unificar datos comunes en las mismas particiones. En otras palabras, a la hora de realizar una consulta a una tabla particionada, en lugar de realizar búsquedas por toda la tabla, reduciremos la búsqueda a aquellas particiones en las que sepamos que hay datos que nos interesen.

Como ejemplo, si tenemos una base de datos en la que almacenamos las ventas que realizan distintas tiendas de una cadena, y particionamos la tabla ventas por la identificación de cada tienda, guardaremos en cada partición los datos de ventas relativos a cada tienda. Así si queremos buscar los datos de la tienda de Albacete, sólo buscaremos en la partición asignada a ésta ciudad, ya que sería absurdo buscar datos de ventas de la tienda de Albacete en cualquier otra tienda.

Otra ventaja del particionado es que nos aporta mucha flexibilidad si pensamos en escalar nuestra base de datos, ya que por ejemplo, podemos guardar cada partición en distintos discos físicos.

Hay que tener en cuenta que estas ventajas tienen sus inconvenientes, ya que la complejidad de administración de nuestra base de datos se incrementará, al igual que el diseño de las tablas, etc… Lo ideal, como siempre es alcanzar un equilibrio entre los beneficios que conseguiremos y el coste de mantener el sistema.

¿Qué limitaciones tiene el particionado de tablas?

• Debemos utilizar el mismo motor de almacenamiento para todas las particiones de la misma tabla (es decir, que no podremos utilizar MyISAM para una partición e InnoDB para otra).
• En MySQL 5.5, no podemos utilizar particionado de tablas con todos los motores de almacenamiento disponibles. En particular, si utilizamos los motores de almacenamiento MERGE, CSV o FEDERATED no podremos aplicar esta técnica.
• El particionado por clave es posible sólo si utilizamos el motor de almacenamiento NDBCLUSTER, pero no otro tipo de particionado definidos por el usuario.
• No podremos utilizar funciones o procedimientos almacenados o plugins, ni declarar variables ni variables de usuario.
• Podemos utilizar operadores aritméticos o lógicos siempre y cuando devuelvan un resultado de tipo entero oNULL (excepto en el caso de particionado por clave).
• No podremos utilizar operadores a nivel de bits.
• El tamaño máximo de particiones (incluyendo subparticiones) para una tabla es de 1024.
• Las claves fornáneas no están soportadas.
• Los índices FULLTEXT no están soportados en tablas particionadas.
• Los tipos de datos POINT o GEOMETRY no se pueden utilizar en tablas particionadas.
• Ni las tablas temporales ni las tablas de logs se pueden particionar.
• El tipo de datos de una clave que se use para el particionado debe ser un entero o una expresión que devuelva un entero como resultado o bien NULL.
• Una clave de particionado no puede ser una subconsulta.
• La caché de claves no está soportada.
• La opción DELAYED no está soportada.
• mysqlcheck y myisamchk no están soportados a la hora de aplicarlo a tablas particionadas.

Es importante tener en cuenta estas limitaciones a la hora de plantearnos el particionado, ya que podríamos acabar en un callejón sin salida (por ejemplo, si decidimos particionar sin tener en cuenta que el número de particiones de una tabla puede crecer por encima del máximo permitido).

¿Qué necesitamos para utilizar el particionado de tablas con MySQL?

El único requisito necesario para utilizar el particionado es que los ejecutables de MySQL estén compilados con soporte para particionado. Los ejecutables de la versión Community de MySQL que distribuye Sun Microsystems incluyen el soporte para particionado.

Para comprobarlo, debemos utilizar el comando de MySQL “SHOW VARIABLES”:

En versiones anteriores a MySQL 5.1.6, la variable have_partitioning recibía otro nombre:have_partition_engine.

También podemos utilizar el comando “SHOW PLUGINS” (debemos observar la línea partition…) :

¿Cómo se crean particiones para una tabla?

Para crear particiones para una tabla, debemos utilizar algunos modificadores de la sentencia “CREATE TABLE” de MySQL:
CREATE TABLE ti (id INT, amount DECIMAL(7,2), tr_date DATE)
ENGINE=INNODB
PARTITION BY HASH( MONTH(tr_date) )
PARTITIONS 6;

La línea:
PARTITION BY HASH( MONTH(tr_date) )

Indica el tipo de particionado que vamos a aplicar, en este caso por hash interno. La distribución de los datos debería ser lo más homogénea posible si el objetivo es la distribución equitativa de los datos a través de las particiones. Ésto dependerá directamente de la calidad de la función hash.

La línea:
PARTITIONS 6;
establece el número de particiones que vamos a utilizar para la tabla.

Notas:

El particionado de una tabla afectará tanto a los índices de la tabla como a los datos. No podemos particionar sólo los índices o sólo los datos.

Lo que sí que podemos hacer es asignar para cada partición un directorio para los datos y otro directorio para los índices.
En caso de que utilicemos el motor de almacenamiento InnoDB, la asignación de distintos directorios para índices y para datos no tendrá efecto.

En Windows se ignoran las opciones de separación de datos e índices en distintos directorios.

Particionado por rango

Este tipo de particionado asigna a cada partición un rango de valores. Podemos utilizar este particionado cuando:

• Necesitamos borrar datos que ya no nos sirven. Por ejemplo, si hemos particionado por años y queremos deshacernos de los datos de uno de los años, lo único que tendremos que hacer es eliminar la partición correspondiente utilizando la sentencia “ALTER TABLE” con las opciones adecuadas relativas al particionado.
• Cuando queremos utilizar una columna que contenga valores de fechas o tiempo.
• Cuando ejecutamos de forma frecuente consultas que dependen directamente en la columna que hemos utilizado para realizar el particionado.

Un subtipo de particionado parecido al de rango es el de lista, en el que se particiona según una lista de valores discretos proporcionados a la hora de crear la tabla.

Particionado por hash

Este tipo de particionado se utiliza cuando queremos una distribución homogénea de los datos en todas las particiones.

Como hemos mencionado anteriormente, debemos asignar una función de hash interna que el motor de MySQL ejecutará cada vez que se realiza una inserción o una actualización y eventualmente los borrados.

Existe un tipo especial de particionado por hash llamado hash lineal. Sin entrar en mucho detalle, elhash lineal proporciona más velocidad a la hora de añadir, borrar, fusionar y separar particiones. Este tipo de particionado es especialmente beneficioso cuando manejamos tablas con muchos datos (del orden de terabytes). La desventaja de este tipo de particionado es que la distribución de datos no es tan homogénea como con el método por hash estándar.

Particionado por clave

El particionado por clave es similar al particionado por hash, con la excepción de que es el usuario quién define la función hash.

Subparticionado

También llamado particionado compuesto, nos permite combinar varios tipos de particionado. Por ejemplo, podemos particionar por rango (particionando por año, como hemos visto anteriormente), y subparticionar utilizando un hash interno para repartir los datos de forma equitativa entre las subparticiones.

Sin embargo, debemos tener en cuenta que las subparticiones están limietadas a particionado por hasho clave. Además, una tabla particionada por hash o clave no puede ser subparticionada.

Debemos recordar en este punto que podemos separar físicamente las subparticiones –al igual que lo haríamos con las particiones–, en distintos directorios, discos, etc…

¿Qué optimizaciones podemos realizar utilizando el particionado?

La optimización principal se conoce como poda (del inglés pruning). El concepto lo hemos explicado previamente, y se trata de realizar las búsquedas sólo en aquellas particiones de una tabla en la que sabemos que vamos a encontrar información que nos interesa.

El detalle más importante de este concepto es que esta poda se realiza de forma transparente para la aplicación (o usuario) que consulta la tabla, ya que no tendremos que especificar en qué particiones queremos buscar. Por el contrario, realizaremos una consulta a una tabla de forma “normal”, y el motor de MySQL ejecutará la consulta en las particiones adecuadas (dependiendo del método de particionamiento que tenga la tabla).

Referencias utilizadas:
Capítulo 17 del manual de MySQL.

En este video explican todos los tipos de particionamiento que existen y sobre todo con ejemplos. Altamente recomendable.

Ejemplo : 

CREATE TABLE IF NOT EXISTS `D_CALCULO` (
`id_calculo` int(10) unsigned NOT NULL AUTO_INCREMENT,
`id_bl_conte` int(11) NOT NULL DEFAULT '0',
`id_cliente` int(11) NOT NULL DEFAULT '0',
`factura` varchar(10) NOT NULL DEFAULT '0',
`id_usuario` int(3) NOT NULL DEFAULT '0',
`pago_realizado` decimal(10,2) NOT NULL DEFAULT '0.00',
`pago_pendiente` decimal(10,2) NOT NULL DEFAULT '0.00',
`pago_pendiente_iva` decimal(10,2) NOT NULL DEFAULT '0.00',
`st_tipo_calculo` char(1) NOT NULL DEFAULT '',
`dias_vig_tar1` int(8) NOT NULL DEFAULT '0',
`fecha_captura` date NOT NULL DEFAULT '0000-00-00',
PRIMARY KEY (`id_calculo`,`fecha_captura`),
KEY `idx_id_bl_conte` (`id_bl_conte`),
KEY `idx_id_cliente` (`id_cliente`)
)
ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=979829
PARTITION BY RANGE ( YEAR(fecha_captura) ) (
PARTITION p0 VALUES LESS THAN (2008) ENGINE=InnoDB,
PARTITION p1 VALUES LESS THAN (2009) ENGINE=InnoDB,
PARTITION p2 VALUES LESS THAN (2010) ENGINE=InnoDB,
PARTITION p3 VALUES LESS THAN MAXVALUE ENGINE=InnoDB
);