Particiones en MySQL

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
  view plaincopy to clipboardprint?

  1. ALTER TABLE contratos  
  2. PARTITION BY RANGE(YEAR(fechaInicio)) (  
  3.     PARTITION partDecada50 VALUES LESS THAN (1960),  
  4.     PARTITION partDecada60 VALUES LESS THAN (1970),  
  5.     PARTITION partDecada70 VALUES LESS THAN (1980),  
  6.     PARTITION partDecada80 VALUES LESS THAN (1990),  
  7.     PARTITION partDecada90 VALUES LESS THAN (2000),  
  8.     PARTITION partDecada00 VALUES LESS THAN (2010),  
  9.     PARTITION partDecada10 VALUES LESS THAN MAXVALUE  
  10. );  
• Por listas: para construir nuestras particiones especificamos listas de valores concretos.
  view plaincopy to clipboardprint?

  1. ALTER TABLE contratos  
  2. PARTITION BY LIST(YEAR(fechaInicio)) (  
  3.     PARTITION partDecada50 VALUES IN (1950, 1951, 1952, 1953, 1954, 1955, 1956, 1957, 1958, 1959),  
  4.     PARTITION partDecada60 VALUES IN (1960, 1961, 1962, 1963, 1964, 1965, 1966, 1967, 1968, 1969),  
  5.     PARTITION partDecada70 VALUES IN (1970, 1971, 1972, 1973, 1974, 1975, 1976, 1977, 1978, 1979),  
  6.     PARTITION partDecada80 VALUES IN (1980, 1981, 1982, 1983, 1984, 1985, 1986, 1987, 1988, 1989),  
  7.     PARTITION partDecada90 VALUES IN (1990, 1991, 1992, 1993, 1994, 1995, 1996, 1997, 1998, 1999),  
  8.     PARTITION partDecada00 VALUES IN (2000, 2001, 2002, 2003, 2004, 2005, 2006,  
  9. 2007, 2008, 2009),  
  10.     PARTITION partDecada10 VALUES IN (2010, 2011, 2012, 2013, 2014, 2015, 2016,  
  11. 2017, 2018, 2019)  
  12. );  
• 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.
  view plaincopy to clipboardprint?

  1. ALTER TABLE contratos  
  2. PARTITION BY HASH(YEAR(fechaInicio))  
  3. 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.
  view plaincopy to clipboardprint?

  1. ALTER TABLE contratos  
  2. PARTITION BY KEY()  
  3. 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)
view plaincopy to clipboardprint?
1. EXPLAIN SELECT COUNT(*)  
2. FROM contratos  
3. 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)
view plaincopy to clipboardprint?
1. EXPLAIN PARTITIONS SELECT COUNT(*)  
2. FROM contratos  
3. 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.