Particionado de tablas para aumentar el rendimiento

base de datosCon la aparición de la versión 5.1 de MySQL, se incluyó en éste el particionado de tablas (algo que en PostgreSQL ya existía hace tiempo) por lo que me he animado a escribir sobre el tema.

El particionado de tablas es una técnica que se usa para reducir la cantidad de lecturas físicas a la base de datos cuando ejecutamos consultas, existen dos principales modalidades de particionado: horizontal y vertical. ¡Vamos a los detalles!

  • Horizontal: Esta modalidad consiste en tener varias tablas con las mismas columnas en cada una de ellas y distribuir la cantidad de registros en estas tablas (generalmente se particiona separando la data por años, meses, etc). Ejemplo: tenemos tres tablas registro2001, registro2002, registro2003 y en cada tabla guardamos los registros de los años correspondientes, esto nos garantiza una mejora en el rendimiento considerable cuando realicemos consultas sobre las tablas ya que la data estará distribuida en tres partes y ya sabríamos dependiendo del año en cual tabla buscar.
  • Vertical: Esta modalidad generalmente la aplicamos en nuestros diseños de base de datos sin darnos cuenta, por ejemplo cuando tenemos una columna de tipo BLOB con una fotografía o un texto muy largo que no leemos frecuentemente y decidimos ponerla en otra tabla referenciandola con la clave foránea.

Ahora bien, el particionado horizontal es el que vamos a comentar, ya que el problema radica en cómo hacer para que nuestras aplicaciones sepan en que tabla guardar el registro dependiendo del año (porque obviamente no le vamos a agregar esas condiciones a nuestra aplicación); esto se logra agregando una serie de sentencias y condiciones en la definición de las tablas.

En los siguientes enlaces se muestra como hacerlo: 

En el ejemplo de MySQL se puede observar la gran diferencia de rendimiento con dos tablas que tienen exactamente la misma data (8 millones de registros), una sin particionar y la otra particionada por años. Al realizar una consulta filtrando por la columna en la cual se basó el particionado se obtuvieron los siguientes resultados:

  • Tabla sin particionado: 38.30 segundos
  • Tabla con particionado: 0.34 segundos

¡Asombroso nooo!

Compartir
  • Invitado

    por lo que me he animado ha escribir sobre el tema.<br /><br />Barbaridad ortográfica. Anímate a encontrarla... :-P<br /><br />PD: buen artículo ;-)

  • y el editor (yo) lo pasó por alto nomás :P corregido!

  • Invitado

    ¿Particionar produce que las consultas se ejecuten más rapidas que si se indexa el campo que se sabe que se va a buscar?<br /><br />¿O se pueden convinar las dos técnicas?

  • cchacin

    La indexación de los campos no debe pasarse por alto, el particionado es una funcionalidad adicional que nos ofrecen ciertos manejadores de base de datos que no permite de alguna manera segmentar la data para que al realizar consultas el manejador se dirija directamente al segmento adecuado y por ende seria más rápida la consulta. Saludos!

  • cchacin

    Gracias por corregir ese error, prometo verificar mejor en próximas oportunidades. Saludos

  • Invitado

    Hola, <br /><br />yo tengo una tabla de 104 millones de registros y necesito particionarla. <br /><br />Los campos que tengo identificados que sería idóneos para hacerlo son tres fechas: fecha de creación, fecha de alta y fecha de baja. <br /><br />El problema es que no se que campo utilizar porque los informes que genero que tiran de esta tabla utilizan las tres fechas indiferentemente y no se por cual hacer la partición. <br /><br />Se supone que el campo que utilice para particionar se tiene que utilizar en la clausula where para que se optimicen las consultas y si particiono por la fecha de creación y los informes van por fecha de cierre no servirá para nada el particionamiento, ¿no?<br /><br />¿Me podéis ayudar?, ¿dar alguna idea de por donde contunuar?<br /><br />Gracias.

  • Invitado

    No conozco como trabaja mysql o postgres con las particiones. En lo que a Oracle respecta, la partición es muy útil para la administración de la base de datos. En este sentido, si se tiene una tabla particionada, se puede definir con el usuario qué cantidad de registros mantener en una tabla productiva. Entonces, sin hacer desarrollo extra, podrías tener un esquema productivo con las N particiones que el usuario requiera en línea. Por ejemplo, los registros dados de alta los últimos 6 meses...<br />Las particiones que van quedando viejas, se pueden descolgar de la base productiva. Es posible tener una base con información histórica y ahí ir poniendo las particiones que van quedando viejas.<br />Esto te permite una administración controlada de registros en producción.<br />Para hacer uso de esto, es más cómodo que los índices sean locales a la partición. De modo que no sea un problema la regeneración de índices.<br />Y con los campos de fechas planteados, quizás teniendo particiones por fecha de alta o de creación del registro. Y luego, la necesidad de tener otro índice por alguna búsqueda que siga resultando crítica. <br />No dejes de tener en cuenta que el campo de la partición, tendrá que ser pk de la tabla, y entiendo que una fecha de baja pudiera ser nuleable, con lo cual -a priori- la descartaría como candidata para el rango de particiones.<br />Bueno es un tema interesante de analizar, conociendo el uso de los datos.<br />Espero no haberte generado más confusión.<br />Saludos.

  • Invitado

    nena si quieres me escribes a mi correo y te puedo enviar ejemplo de base de dato con mysql<br /><br />mi correo es loraine023@hotmail.com

  • Invitado

    Hola yo tengo una consulta en relación con lo que ustedes dicen tengo un mantenimiento de productos con 25000 y tengo 20 sucursales y debo de presupuestar cada producto para cada sucursal por 12 meses, mi consulta es con respecto al rendimiento que es mas optimo crear una tabla general o particionarla por sucursal, considerando que despues debo de mostrar informes de presupuestado vs real de ventas

  • Invitado

    Saludos hermano, parece ser que las particiones son mucho mas sencillas de hacer en mysql que en postgres, pero a tu parecer para grandes volumenes de datos cual manejador escogerias.

Deja tus comentarios

Post comment as a guest

0

El nuevo Dos Ideas.

Nuevo logo, nuevo buscador, nueva portada, podcast mensual... ¡y muchas novedades más!

Más novedades en Dos Ideas

Los Comentarios.

Hi . . . I like your commenting stage really I like your work with deeply, Thanks for providing us t...
EFRAIN DELGADO
ME HA DADO UN MEJOR PANORAMA Y NUEVAS IDEAS
Robyir Loreto
jeje...siempre hago referencia a ese cuento a las personas que se quejan de lo que les cobro
edu
para mi esta relativamente bien, pero el clasico es 100000 veces menjor, de echo repudio el echo de ...

Inspiración.

"Si tú tienes una manzana y yo tengo una manzana e intercambiamos las manzanas, entonces tanto tú como yo seguiremos teniendo una manzana cada uno. Pero si tú tienes una idea y yo tengo una idea, e intercambiamos las ideas, entonces ambos tendremos dos ideas"

Bernard Shaw