Particiones de tablas 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 comandoEXPLAIN, 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

       

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

  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)

  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.

Anuncios
Tagged with:
Publicado en Uncategorized

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s

A %d blogueros les gusta esto: