Particiones en MySQL

Cuando alguna de las tablas de tu base de datos llega a crecer tanto que el rendimiento empieza a ser un problema, es hora de empezar a leer algo sobre optimización. Índices, el comando EXPLAIN, el registro de consultas lentas, … estas son herramientas básicas que todo el mundo debería conocer. Una característica algo menos conocida, aunque se introdujo en la versión 5.1 de MySQL, son las particiones.

En el hospital en que trabajo la mayor tabla con la que tenemos que lidiar es la que almacena todos y cada uno de los contratos de todos los trabajadores que alguna pasaron por el hospital desde que se fundó en los años 50. Esto supone sólo un par de cientos de miles de tuplas, lo cuál no debería dar muchos dolores de cabeza con una base de datos bien optimizada, consultas razonables, y un hardware decente. Sin embargo, hay personas que tienen que tratar con cantidades de datos realmente obscenas, que multiplican estos números por 10 veces 10.

Una solución que nos puede venir a la cabeza, sobre todo si la mayor parte de la información se almacena a modo de histórico y no se accede a ella frecuentemente, es dividir la tabla en varias porciones. Podríamos mantener una tabla para el año en curso y otra para el resto de años, por ejemplo; o una para cada uno de los años; una por lustro; por década… dependiendo de cómo se trabaje con los datos.

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.

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
    ALTER TABLE contratos
    PARTITION BY RANGE(YEAR(fechaInicio)) (
    	PARTITION partDecada50 VALUES LESS THAN (1960),
    	PARTITION partDecada60 VALUES LESS THAN (1970),
    	PARTITION partDecada70 VALUES LESS THAN (1980),
    	PARTITION partDecada80 VALUES LESS THAN (1990),
    	PARTITION partDecada90 VALUES LESS THAN (2000),
    	PARTITION partDecada00 VALUES LESS THAN (2010),
    	PARTITION partDecada10 VALUES LESS THAN MAXVALUE
    );
  • Por listas: para construir nuestras particiones especificamos listas de valores concretos.
    ALTER TABLE contratos
    PARTITION BY LIST(YEAR(fechaInicio)) (
    	PARTITION partDecada50 VALUES IN (1950, 1951, 1952, 1953, 1954, 1955, 1956, 1957, 1958, 1959),
    	PARTITION partDecada60 VALUES IN (1960, 1961, 1962, 1963, 1964, 1965, 1966, 1967, 1968, 1969),
    	PARTITION partDecada70 VALUES IN (1970, 1971, 1972, 1973, 1974, 1975, 1976, 1977, 1978, 1979),
    	PARTITION partDecada80 VALUES IN (1980, 1981, 1982, 1983, 1984, 1985, 1986, 1987, 1988, 1989),
    	PARTITION partDecada90 VALUES IN (1990, 1991, 1992, 1993, 1994, 1995, 1996, 1997, 1998, 1999),
    	PARTITION partDecada00 VALUES IN (2000, 2001, 2002, 2003, 2004, 2005, 2006,
    2007, 2008, 2009),
    	PARTITION partDecada10 VALUES IN (2010, 2011, 2012, 2013, 2014, 2015, 2016,
    2017, 2018, 2019)
    );
  • 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.
    ALTER TABLE contratos
    PARTITION BY HASH(YEAR(fechaInicio))
    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.
    ALTER TABLE contratos
    PARTITION BY KEY()
    PARTITIONS 7;
  • Compuesta: podemos combinar los distintos métodos de particionado y crear particiones de particiones
  • Por último, un pequeño ejemplo de cómo afectaría el particionado a una consulta sencilla como obtener el número total de tuplas que cumplen una condición. Estas son las estadísticas de la consulta sin particionado (ni índices)

    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

    Y este el resultado de añadir las particiones (nótese la palabra clave PARTITIONS para que nos muestre también la información relativa a las particiones)

    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

    Como véis, el número de tuplas que MySQL tiene que comprobar se ve disminunido en 2 órdenes de magnitud.



28 comentarios en «Particiones en MySQL»

  1. Quién no utiliza explain, ni indices, no sabe de base de datos.. Y tener que utilizar este método de francionar datos para optimizar, me parece extraño. Si estos datos no los utilizas, una, es que no se sabes tratar la información para tener un beneficio, o simpemente guardas basura.. Si por lo contrario, todos los datos lo utilizas y con índicies y optimización de consultas es ineficiente, lo mejor es utlizar sphinx o otros sistemas de base de datos como nosql, etc..

    Saludos

    1. Llega un momento en el que el uso de índices puede ser contraproducente. Las particiones son un muy buen método de optimización en determinadas situaciones. Antes de que se implementara en los SGDB los DBA Oracle lo hacían a mano. Me alegra ver que en MySQL al fin lo tienen.

    2. LOL, quien piensa que separar los datos mediante particiones es «extraño», no solo no sabe de bases de datos, no sabe de computación XD, otra: guardar basura durante 10 años por que al 9 alguien querrá un registro, convierte el registro en histórico, no en basura, y si has trabajado para sistemas reales sabrás que muchas veces es necesario (por ejemplo, el historial de facturas hacienda exige que sea guardado por varios, vaaarios años), esta solución hace un paso que ayuda bastante y es transparente a la programación, obviamente no es la panacea, pero como aquí lo dicen es una de tantas herramientas y en ciertos casos una solución efectiva y eficiente a los problemas que se le presentan a uno como programador

    3. Uno más. Me parece que estás en un error. Trabajo en una banco en que manejamos muchas tablas de más de 1800 millones de filas (Oracle) y esta técnica es muy interesante y útil. Sólo te pongo un ejemplo de algo que se hace cada mes, y con particiones es muy eficiente. Si queremos borrar información de un mes (80 millones de filas), no hace falta hacer un «delete …», sino que hacemos un truncate de la particón (y actualización de índices) y va como un tiro.

    4. Que ignorante resulta siendo alguien que asegura que los demas no saben de algun tema en particular, sobre todo si como en este caso el argumento es invalido, las particiones estan incluidas en las herramientas de las bases de datos y han evolucionado no precisamente porque sean inutiles. Como se nota que el «experto» que hizo este comentario solo ha trabajado con bases de datos de unos pocos registros….
      Por otra parte, este tipo de articulos son muy utiles para aprender cada vez mas acerca de las bases de datos.

  2. En el instituto me enseñaron que esto se podía hacer con Oracle, pero no me imaginé que se pudiera también en MySQL, gracias por el dato me ayudas un montón!

  3. Una vez intenté aplicar esta técnica a una tabla con miles y miles de resultados de un algoritmo. La traba es que las particiones no se llevan bien con las llaves foráneas, al menos en InnoDB 😀

  4. Esta es muy util Zootropo, gracias por el post. Además aprovecho para comentar un poco en general, que está muy bien el hecho de mezclar las cosas técnicas con otras mas de ocio o simples curiosidades. Después de tiempo siguiéndolo, diría que es la esencia del blog, y está muy bien hacerlo así 🙂

    Saludos!

  5. Excelente, me da pie para poder iniciar con particiones, y es verdad, a veces los indices no son suficientes, en mi caso, tenemos millones de registros, y otros cuantos miles que se guardan diariamente, por lo cual hacemos «Historico», y ahora vamos a necesitar particiones, ya que los indices por mas correctos que sean pueden ser insuficientes.

    Gracias.

  6. Hola , tengo un problema para crear tablas con partitiones en mysql marca error 1064 (42000) me dice que tengo error de syntaxis, esta misma syntaxis si corre en otra maquina que tiene instalado mysql, cual puede ser el motivo del error ???

  7. Solo un pequeño gran detalle, y es que MySQL por
    desgracia no soporta en su función de partición
    columnas que no pertenezcan a una llave primaria
    o única. Por ejemplo, la explicación del post solo
    es válida en alguno de éstos dos casos:

    a)fechaInicio debe ser llave primaria, o parte de
    una declaración de llave primaria o única como por
    ejemplo primary key(x1,x2,fechaInicio). Lo cual es
    una joda por que tu campo a particionar A FUERZA tiene que ser llave primaria o única.

    b) Que tu tabla no tenga ni llaves primarias ni únicas, lo cual es absolutamente ridículo.

    La idea del particionamiento es excelente, por descgracia al menos a mi, MySQL no me convence para la tarea

  8. Muy interesante, tengo un proyecto en el que debo trabajar con tablas de varios millones de datos, en concreto una tabla de socios con cerca de 2 millones. El problema que tengo es que constantemente recibe datos externos y debe cargarse mediante triggers sobre otras tablas que deben buscar sobre esta tabla en ciertos campos alfanuméricos. He intentado añadir todo tipo de índices, combinaciones de campos, etc, pero no consigo reducir los tiempos de carga y las pilas. Esto parece una solución interesante, pero si es cierto que sólo funciona con claves primarias me han jodido bien. El problema es que si divido la tabla de socios en varias tablas más pequeñas, tengo el problema de que me tocará unirla constantemente para las cargas y me olvidaré de los triggers y procedimientos que tengo y me tocará picarlo a mano, que tampoco va a ayudar demasiado. ¿Puede ser que necesite mejorar el servidor o hay algún modo de solucionar el problema con este tipo de tablas gigantescas?. Gracias de antemano.

  9. Error Code: 1506. Foreign key clause is not yet supported in conjunction with partitioning
    — Alter
    alter table Promocion
    add constraint FK_Promocion_Categoria
    foreign key (Categoria)
    references Categoria (idCategoria);

    — Tabla con particiones
    create table if not exists Promocion(
    idPromocion bigint unsigned auto_increment,
    Nombre varchar (100) not null,
    FechaInicio date not null,
    FechaFin date null,
    ImageURL longtext not null,
    Categoria int,
    Estado tinyint default 1,
    unique key (idPromocion,FechaFin)
    )engine = myisam
    partition by range (year(FechaFin))(
    partition a2015 values less than (2016),
    partition a2016 values less than (2017),
    partition a2017 values less than (2018),
    partition a2018 values less than (2019),
    partition a2019 values less than (2020),
    partition a2020 values less than (2021),
    PARTITION partDecada10 VALUES LESS THAN MAXVALUE
    );

Responder a 44ld Cancelar respuesta

Este sitio usa Akismet para reducir el spam. Aprende cómo se procesan los datos de tus comentarios.