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.



Comentarios
  1. Esta va dedicada a TeKNo dUKe, que tenía ganas de leer alguna cosilla más técnica.

    Responder

  2. gracias por la info, nunca me he visto en la necesidad de utilizar algo así pero es bueno saber que se puede usar esto.

    Responder

  3. Excelente documento 🙂

    Responder

  4. Uno más

    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

    Responder

    • Anónimo

      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.

      Responder

    • rekiem87

      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

      Responder

    • Luiko

      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.

      Responder

  5. Troll

    Por cosas como está es que sigo mundo geek.
    Un saludo

    Responder

  6. ahora, me gustaría aprender mysql, guardo esto en favoritos para practicar mas adelante……

    Responder

  7. Interesante, aunque por más datos que he tenido en una tabla nunca he visto la necesidad de usar algo así. Nunca está de más saberlo.

    Responder

  8. 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!

    Responder

  9. Zen

    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 😀

    Responder

  10. Un post realmente interesante. Espero que algún día tenga tantos datos en mi web que tenga que aplicarlo. Me lo guardo en los favoritos. Saludos!

    Responder

  11. [UT]_Roxx

    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!

    Responder

  12. William Bravo

    Excelente aportación 😀

    Responder

  13. Se te ha colado un partDecada00 cuando deberia ser partDecada10 en la particion por valores.

    Responder

  14. wen tutorial

    Responder

  15. 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.

    Responder

  16. ANGELES

    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 ???

    Responder

  17. Arturo

    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

    Responder

  18. Pujencio

    Me sirvió de mucho gracias.

    Responder

  19. afiugud

    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.

    Responder

  20. Fernanda

    Una pregunta, alguien sabe que quiere decir ese 7 en partition de hash y clave please =/

    Responder

    • Jonny

      El 7 que comentas quiere decir que le vas a pedir a MySQL que realice un máximo de 7 particiones según el hash que resuelva.

      Responder

  21. rvv

    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

Deja un comentario