Planet MySQL :: Planet MySQL - Archives - Particionado Lógico (Parte II)

Para las prácticas haremos uso de una BBDD de prueba que podemos descargar aquí:

Lo bueno de esta BBDD es que ya viene repletita de datos, por ejemplo la tabla salaries tiene en torno a dos millones de registros. La particionaremos de forma que logremos mejorar el rendimiento. Hay que tener en cuenta que las pruebas se van a hacer sobre un Netbook, por lo que los resultados no son 100% fiables. Nunca pongáis un netbook como servidor de bases de datos en producción u os quedareis ciegos.

El particionado se puede hacer por rangos, listas, hashes y keys:

RANGO


CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT NOT NULL,
    store_id INT NOT NULL
)
PARTITION BY RANGE (store_id) (
    PARTITION p0 VALUES LESS THAN (6),
    PARTITION p1 VALUES LESS THAN (11),
    PARTITION p2 VALUES LESS THAN (16),
    PARTITION p3 VALUES LESS THAN (21),
    PARTITION p3 VALUES LESS THAN MAXVALUE
);

Es el particionado más sencillo. En el ejemplo se divide usando el campo store_id, de forma que los valores menores de 6 van a la partición p0, los menores de 11 a la p1, los menores de 16 a la p2, etc. Es importante tener en cuenta la inclusión del MAXVALUE. Ahí es donde irán a parar aquellos registros que no encajen dentro del resto de tablas. Si no pusiéramos un MAXVALUE, MySQL no sabría donde meterlo y daría error.

LISTAS


CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT,
    store_id INT
)
PARTITION BY LIST(store_id) (
    PARTITION pNorth VALUES IN (3,5,6,9,17),
    PARTITION pEast VALUES IN (1,2,10,11,19,20),
    PARTITION pWest VALUES IN (4,12,13,14,18),
    PARTITION pCentral VALUES IN (7,8,15,16)
);

En este caso, el particionado se realiza en base a una lista de posibles valores. Al contrario que con RANGE, aquí no tenemos MAXVALUE. Por lo que es de suponer, según el diseño de la web, que nunca tendremos valores distintos a los indicados.

HASHES

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT,
    store_id INT
)
PARTITION BY HASH( YEAR(hired) )
PARTITIONS 4;

Como podéis comprobar, estamos haciendo uso de una columna que no es INT, pero mediante la función YEAR la convertiremos y hacemos uso de ella en la partición. Este truco se suele utilizar muy a menudo para evitar la limitación de particiones en INT. A parte de todo eso, el uso de HASH nos permite dividir los datos de forma equitativa entre todas las particiones, cosa que con otros tipos de particiones podría no pasar. De esta forma, si estamos trabajando una tabla enorme y la queremos dividir en 10 particiones, estas tendrán un número de valores muy similar.

KEY

CREATE TABLE k1 (
    id INT NOT NULL PRIMARY KEY,
    name VARCHAR(20)
)
PARTITION BY KEY()
PARTITIONS 2;

Con Key no es necesario indicar la columna que deseamos para particionar, en ese caso hará uso de la clave primaria. KEY es muy parecida a HASH, solo que en lugar de indicarle nosotros el HASH mediante una expresión, lo hará el propio MySQL usando MD5.

Dicho esto, comenzamos a particionar nuestra BBDD. La tabla en cuestión será, como ya indique, la de salarios.

mysql [localhost] {msandbox} (employees) > select count() from salaries;
+----------+
| count() |
+----------+
|  2844047 |
+----------+
1 row in set (1.66 sec)

Casi 3 millones de registros, no está nada mal :) Vamos a hacer una búsqueda para saber cuantos corresponden al año 2000.

mysql [localhost] {msandbox} (employees) > select count() from salaries where from_date between
'2000-01-01' and '2000-12-31';
+----------+
| count() |
+----------+
|   255785 |
+----------+
1 row in set (2.39 sec)

Fijaros en el tiempo que ha tardado MySQL en realizar la consulta, 2.39 segundos. Bastante tiempo, si en lugar de una sola querie fuesen 100 concurrentes estaríamos ante un verdadero problema de rendimiento.

mysql [localhost] {msandbox} (employees) > explain select count(*) from salaries where from_date
between '2000-01-01' and '2000-12-31'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: salaries
         type: index
possible_keys: NULL
          key: emp_no
      key_len: 4
          ref: NULL
         rows: 2844513
        Extra: Using where; Using index
1 row in set (0.00 sec)

La querie se tiene que recorrer los 3 millones de registros usando un índice (lo cual no está mal) y aún así el rendimiento es pobre. Entonces, pidamos ayuda a nuestras amigas las particiones. Vamos a particionar por año :)

ALTER TABLE salaries 
PARTITION by range (year(from_date))
(
  partition p001 VALUES LESS THAN (1986)
, partition p002 VALUES LESS THAN (1987)
, partition p003 VALUES LESS THAN (1988)
, partition p004 VALUES LESS THAN (1989)
, partition p005 VALUES LESS THAN (1990)
, partition p006 VALUES LESS THAN (1991)
, partition p007 VALUES LESS THAN (1992)
, partition p008 VALUES LESS THAN (1993)
, partition p009 VALUES LESS THAN (1994)
, partition p010 VALUES LESS THAN (1995)
, partition p011 VALUES LESS THAN (1996)
, partition p012 VALUES LESS THAN (1997)
, partition p013 VALUES LESS THAN (1998)
, partition p014 VALUES LESS THAN (1999)
, partition p015 VALUES LESS THAN (2000)
, partition p016 VALUES LESS THAN (2001)
, partition p017 VALUES LESS THAN (2002)
, partition p018 VALUES LESS THAN (2003)
);

Si ahora hacemos la misma select de antes:

mysql [localhost] {msandbox} (employees) > select count() from salaries where
from_date between '2000-01-01' and '2000-12-31';
+----------+
| count() |
+----------+
|   255785 |
+----------+
1 row in set (0.22 sec)

La búsqueda de los salarios del año 2000. Antes 2,39 segundos, ahora 0,22. La mejora es impresionante.

Un explain partitions nos indica que partición ha usado para la select:

mysql [localhost] {msandbox} (employees) > explain partitions select count(*) from salaries where from_date between '2000-01-01' and '2000-12-31'\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: salaries partitions: p016 type: index possible_keys: NULL key: emp_no key_len: 4 ref: NULL rows: 2830488 Extra: Using where; Using index 1 row in set (0.00 sec)

MySQL Links