Blog de Gonzalo

FRAGMENTACIÓN HORIZONTAL EN MYSQL

Hace poco hablé sobre las fragmentación de tablas en MySQL y en este post quiero hablar de la fragmentación horizontal en mysql ya que es útil si se van a manejar grandes volúmenes de datos.
La partición horizontal permite distribuir los datos en diferentes tablas que van a tener la misma estructura permitiendo consultas más rápidas.
En Mysql 5.1 las particiones tenían que ser con columnas de tipo intefer pero a partir de la versión 5.5 las particiones pueden ser de cualquier tipo de dato.
Los campos que se eligen para la fragmentación tienen que ser indices. La fragmentación en horizontal tiene cuatro tipos:

  • RANGE: Los datos se guardan en una tabla u otra según un rango de valores definido sobre una determinada columna de la tabla o expresión. Es decir, nosotros indicaremos el numero de particiones a crear, y para cada partición, el rango de valores que seran la condición para insertar en ella, de forma que cuando un registro que se va a introducir en la base de datos tenga un valor del rango en la columna/expresion indicada, el registro se guardará en dicha partición. Por ejemplo una fecha, un id de provincia, etc...
  • LIST: Los datos se guardan en una tabla u otra según una lista de valores definida sobre una determinada columna de la tabla o expresión. Es decir, nosotros indicaremos el numero de particiones a crear, y para cada partición, la lista de valores que serán la condición para insertar en ella, de forma que cuando un registro que se va a guardar en la base de datos tenga un valor incluído en la lista de valores, el registro se guardará en dicha partición. Por ejemplo por volumen de ventas, etc...
  • HASH: Esta partición los datos se guardarán de forma equitativa entre las diferentes particiones. En esta partición MySql quien hace ese trabajo. Para definir este tipo de particionado, deberemos de indicarle una columna del tipo integer o una función de usuario que devuelva un integer. En este caso, se ejecutará una función sobre un determinado campo que devolvera un valor entero. Según el valor, MySql insertará el registro en una partición distinta.
  • KEY: Este tipo de fragmentación es similar al tipo HASH, pero la función para el particionado la proporciona MySql automáticamente (con la función MD5). Se pueden indicar los campos para el particionado, pero siempre han de ser de la clave primaria de la tabla o de un indice único.
  • SUBPARTITIONS: Mysql permite hacer subparticionados. Permite la división de cada partición en multiples subparticiones.


CREATE TABLE sales (
  id int(10) NOT NULL AUTO_INCREMENT,
  sales_date datetime NOT NULL,
  amount int(11),
  PRIMARY KEY (id,amount)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

//RANGE
ALTER TABLE sales PARTITION BY RANGE(amount)(
	PARTITION P1 VALUES LESS THAN (100000),
	PARTITION P2 VALUES LESS THAN (200000),
	PARTITION P3 VALUES LESS THAN (300000),
	PARTITION P4 VALUES LESS THAN (400000),
	PARTITION P5 VALUES LESS THAN (500000),
	PARTITION P6 VALUES LESS THAN (600000),
	PARTITION P7 VALUES LESS THAN (700000),
	PARTITION P8 VALUES LESS THAN (800000),
	PARTITION P9 VALUES LESS THAN (900000),
	PARTITION P10 VALUES LESS THAN (1000000),
	PARTITION P11 VALUES LESS THAN (1100000)
);
//RANGE 2 con fechas
ALTER TABLE sales PARTITION BY RANGE(sales_date)(
	PARTITION P1 ( sales_date between 01-01-2018 to 31-03-2018),
	PARTITION P2 ( sales_date between 01-04-2018 to 30-06-2018),
	PARTITION P3 ( sales_date between 01-07-2018 to 30-09-2018),
        PARTITION P4 ( sales_date between 01-10-2018 to 31-12-2018),
);

//HASH (number of partitions = 6)
ALTER TABLE sales PARTITION BY HASH(id)
    PARTITIONS 6;
);

//Subpartición
PARTITION BY RANGE(YEAR(sales_date) )
SUBPARTITION BY HASH(TO_DAYS(sales_date))
SUBPARTITIONS 4 (
PARTITION p0 VALUES LESS THAN (1990),
PARTITION p1 VALUES LESS THAN (2000),
PARTITION p2 VALUES LESS THAN (2010),
PARTITION p3 VALUES LESS THAN MAXVALUE
);

PARTITION BY LIST(YEAR(sales_date) )
PARTITION p0 VALUES IN (1990,1991,1992,1993),
PARTITION p1 VALUES IN (2000),
PARTITION p2 VALUES IN (2010),
);
//LIST
CREATE TABLE IF NOT EXISTS `article` (
  `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `id_category` tinyint(3) unsigned NOT NULL,
  `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `title` varchar(100) COLLATE utf8_bin NOT NULL,
  `article` longtext COLLATE utf8_bin NOT NULL,
  `slug` varchar(100) COLLATE utf8_bin NOT NULL,

  KEY (`id`),
  KEY `titulo` (`titulo`),
  KEY `fecha` (`fecha`),
  KEY `id_categoria` (`id_categoria`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=251 
PARTITION  BY LIST(id_category) (
PARTITION p1 VALUES IN (1,2,3), 
PARTITION p2 VALUES IN (4),
PARTITION p3 VALUES IN (5),

Si se quiere añadir un valor después de haber creado la tabla y las particiones hay que ejecutar la query:

ALTER TABLE article ADD PARTITION (PARTITION p4 VALUES in (6)) 

Aqui os he puesto varios ejemplos de particonado. Creando y modificando la tabla. Es bastante sencillo de entender.
AVISO: En el tema de las fragmentaciones los campos que se van a usar en la fragmentación horizontal tienen que ser clave y como se puede ver en el último ejemplo no hay clave primaria.
Si se quiere añadir clave primaria el campo de la fragmentación tiene que ser parte de esa clave primaria o ser la clave primaria sino dará error

Compartir en twitter