LabEx
Únete

Particionamiento de MySQL para grandes conjuntos de datos

MySQLBeginner
Practicar Ahora

Introducción

En este laboratorio aprenderá a implementar el particionamiento de MySQL para grandes conjuntos de datos con el fin de mejorar el rendimiento de las consultas y la gestión de los datos. Se centrará en particionar una tabla por rangos, concretamente mediante la columna sale_date de una tabla llamada sales.

Primero se conectará al servidor MySQL y creará una base de datos llamada sales_data. Después, creará la tabla sales y la particionará por año según la columna sale_date, con particiones para 2020, 2021, 2022 y 2023, además de una partición para fechas futuras. En los pasos siguientes consultará datos de particiones específicas, reorganizará las particiones con ALTER TABLE y comprobará el impacto del particionamiento en la velocidad de las consultas.

Nota: Para este laboratorio, solo tendrá que acceder al shell de MySQL al principio y salir al final. Ejecute todos los comandos SQL de los pasos siguientes en la misma sesión de MySQL.

Crear una tabla particionada

En este paso creará una base de datos y una tabla particionada en MySQL. El particionamiento ayuda a gestionar grandes conjuntos de datos al dividir una tabla en partes más pequeñas y manejables según una regla determinada. Esto puede mejorar considerablemente el rendimiento de las consultas, sobre todo cuando filtran datos por la clave de particionamiento.

Primero, abra un terminal en la máquina virtual de LabEx. Ya debería encontrarse en el directorio ~/project.

Conéctese al servidor MySQL como usuario root (hágalo solo una vez, al comienzo del laboratorio):

sudo mysql -u root

Ahora está en el shell de MySQL. Ejecute todos los comandos SQL posteriores en esta sesión hasta que termine el laboratorio.

Active una transcripción para que las comprobaciones del paso puedan leer las instrucciones SQL introducidas en esta sesión mientras permanezca abierta:

tee /home/labex/project/partitioning-session.log

Mantenga activa esta transcripción hasta que termine el laboratorio. Las instrucciones SQL que introduzca aquí no se registran en el historial del terminal.

Cree una base de datos llamada sales_data para almacenar la tabla:

CREATE DATABASE sales_data;

Cambie a la base de datos recién creada:

USE sales_data;

Ahora creará una tabla llamada sales y la particionará por año según la columna sale_date. Creará particiones para los años 2020, 2021, 2022 y 2023, además de una partición general para fechas futuras.

CREATE TABLE sales (
    sale_id INT NOT NULL,
    sale_date DATE NOT NULL,
    amount DECIMAL(10, 2) NOT NULL,
    PRIMARY KEY (sale_id, sale_date)
)
PARTITION BY RANGE (YEAR(sale_date)) (
    PARTITION p2020 VALUES LESS THAN (2021),
    PARTITION p2021 VALUES LESS THAN (2022),
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION pFuture VALUES LESS THAN MAXVALUE
);

Veamos qué significa la cláusula PARTITION BY RANGE:

  • PARTITION BY RANGE (YEAR(sale_date)): especifica que la tabla se particionará según los rangos de los valores que devuelve la función YEAR() aplicada a la columna sale_date.
  • PARTITION p2020 VALUES LESS THAN (2021): crea una partición llamada p2020. Las filas cuyo año en sale_date sea anterior a 2021 (es decir, 2020) se almacenarán en esta partición.
  • PARTITION p2021 VALUES LESS THAN (2022): crea una partición llamada p2021 para los datos del año 2021.
  • PARTITION p2022 VALUES LESS THAN (2023): crea una partición llamada p2022 para los datos del año 2022.
  • PARTITION p2023 VALUES LESS THAN (2024): crea una partición llamada p2023 para los datos del año 2023.
  • PARTITION pFuture VALUES LESS THAN MAXVALUE: crea una partición llamada pFuture que almacenará los datos cuyo año en sale_date sea igual o posterior a 2024. MAXVALUE es un valor especial que siempre es superior a cualquier otro valor.

Después de ejecutar la instrucción CREATE TABLE, compruebe la estructura de la tabla y sus particiones con este comando:

SHOW CREATE TABLE sales;

Busque la cláusula PARTITION BY RANGE en el resultado para confirmar que la tabla se creó con las particiones especificadas.

Ahora inserte algunos datos de ejemplo en la tabla sales. MySQL colocará automáticamente cada fila en la partición correspondiente según el valor de sale_date.

INSERT INTO sales (sale_id, sale_date, amount) VALUES
(1, '2020-12-31', 100.00),
(2, '2021-01-15', 150.00),
(3, '2021-12-25', 200.00),
(4, '2022-06-01', 120.00),
(5, '2022-12-31', 180.00),
(6, '2023-03-10', 250.00),
(7, '2023-09-20', 300.00),
(8, '2024-01-01', 350.00);

Ha creado correctamente una tabla particionada y ha insertado datos en ella. En el siguiente paso aprenderá a consultar datos de particiones específicas.

Consultar datos de particiones específicas

En este paso aprenderá a consultar datos de una tabla particionada de forma eficiente, indicando las particiones que quiere consultar. Esta es una de las principales ventajas del particionamiento: permite que MySQL examine solo las particiones pertinentes, lo que reduce considerablemente la cantidad de datos procesados y mejora el rendimiento de las consultas.

Recordatorio: Aún debería estar en el shell de MySQL y usando la base de datos sales_data. Si no es así, ejecute:

USE sales_data;

Para consultar datos de una partición específica, puede incluir una cláusula WHERE que filtre por la clave de particionamiento. A menudo, el optimizador de consultas de MySQL puede identificar qué particiones son pertinentes a partir de la cláusula WHERE.

Por ejemplo, para obtener todas las ventas del año 2021, ejecute esta consulta. Observe que se utiliza una condición de rango directa sobre sale_date. Si utiliza funciones como YEAR(sale_date) en la cláusula WHERE, puede impedir que MySQL aplique la eliminación de particiones, lo que le obligaría a examinar todas las particiones.

SELECT * FROM sales WHERE sale_date >= '2021-01-01' AND sale_date < '2022-01-01';

Para ver a qué particiones accede MySQL en esta consulta, use la instrucción EXPLAIN PARTITIONS:

EXPLAIN PARTITIONS SELECT * FROM sales WHERE sale_date >= '2021-01-01' AND sale_date < '2022-01-01';

En el resultado de EXPLAIN PARTITIONS, consulte la columna partitions. Debería mostrar p2021, lo que indica que MySQL solo examina la partición p2021 para ejecutar esta consulta.

+----+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | sales | p2021      | ALL  | PRIMARY       | NULL | NULL    | NULL |    2 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+

También puede consultar datos que abarquen varias particiones. Por ejemplo, para obtener los datos de ventas de 2022 y 2023:

SELECT * FROM sales WHERE sale_date >= '2022-01-01' AND sale_date < '2024-01-01';

Si vuelve a usar EXPLAIN PARTITIONS, verá que MySQL accede a las particiones p2022 y p2023:

EXPLAIN PARTITIONS SELECT * FROM sales WHERE sale_date >= '2022-01-01' AND sale_date < '2024-01-01';

La columna partitions mostrará p2022,p2023.

+----+-------------+-------+---------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | partitions    | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+---------------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | sales | p2022,p2023   | ALL  | PRIMARY       | NULL | NULL    | NULL |    4 | Using where |
+----+-------------+-------+---------------+------+---------------+------+---------+------+------+-------------+

Esto demuestra cómo el particionamiento permite que MySQL elimine las particiones que no son pertinentes durante la ejecución de una consulta. Así, obtiene resultados más rápido, sobre todo en tablas muy grandes, en las que examinar toda la tabla llevaría mucho tiempo.

Para ver el número de filas de cada partición, consulte la tabla INFORMATION_SCHEMA.PARTITIONS:

SELECT
    PARTITION_NAME,
    TABLE_ROWS
FROM
    INFORMATION_SCHEMA.PARTITIONS
WHERE
    TABLE_SCHEMA = 'sales_data' AND TABLE_NAME = 'sales';

Esta consulta muestra claramente cómo se distribuyen los datos entre las particiones.

+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p2020          |          1 |
| p2021          |          2 |
| p2022          |          2 |
| p2023          |          2 |
| pFuture        |          1 |
+----------------+------------+

Ha consultado correctamente datos de particiones específicas y ha observado cómo MySQL aprovecha el particionamiento para optimizar las consultas.

Reorganizar y administrar particiones

En este paso aprenderá a modificar la estructura de particiones de una tabla existente mediante la instrucción ALTER TABLE. Esto resulta útil para adaptar el esquema de particionamiento a medida que crecen los datos o cambian sus necesidades.

Recordatorio: Aún debería estar en el shell de MySQL y usando la base de datos sales_data. Si no es así, ejecute:

USE sales_data;

Supongamos que queremos agregar una partición para el año 2024. Actualmente, los datos de 2024 y posteriores están en la partición pFuture. No puede agregar una partición con ADD PARTITION porque la partición pFuture está definida con VALUES LESS THAN MAXVALUE y siempre debe ser la última partición.

En su lugar, debe usar REORGANIZE para dividir la partición pFuture. La dividirá en dos particiones nuevas: una para el año 2024 (p2024) y otra llamada pFuture para todo lo posterior.

ALTER TABLE sales REORGANIZE PARTITION pFuture INTO (
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION pFuture VALUES LESS THAN MAXVALUE
);

Este comando toma la partición pFuture existente, mueve a la nueva partición p2024 los datos de 2024 y redefine pFuture para que incluya las fechas de 2025 en adelante. La fila con sale_date igual a '2024-01-01' se moverá a p2024.

Compruebe la estructura actualizada de las particiones y el número de filas:

SELECT
    PARTITION_NAME,
    TABLE_ROWS
FROM
    INFORMATION_SCHEMA.PARTITIONS
WHERE
    TABLE_SCHEMA = 'sales_data' AND TABLE_NAME = 'sales';

Debería aparecer la nueva partición p2024. La fila de 2024 ahora está en p2024.

+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p2020          |          0 |
| p2021          |          2 |
| p2022          |          2 |
| p2023          |          2 |
| p2024          |          0 |
| pFuture        |          0 |
+----------------+------------+

Ahora combinará particiones. Supongamos que queremos combinar las particiones p2020 y p2021 en una sola partición llamada p2020_2021.

ALTER TABLE sales REORGANIZE PARTITION p2020, p2021 INTO (
    PARTITION p2020_2021 VALUES LESS THAN (2022)
);

Este comando combina los datos de p2020 y p2021 en una nueva partición llamada p2020_2021. La cláusula VALUES LESS THAN (2022) define el nuevo límite de esta partición combinada.

Vuelva a comprobar la estructura de las particiones:

SELECT
    PARTITION_NAME,
    TABLE_ROWS
FROM
    INFORMATION_SCHEMA.PARTITIONS
WHERE
    TABLE_SCHEMA = 'sales_data' AND TABLE_NAME = 'sales';

Verá que p2020 y p2021 ya no están y que existe p2020_2021, con el número total de filas de ambas.

+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p2020_2021     |          3 |
| p2022          |          2 |
| p2023          |          2 |
| p2024          |          0 |
| pFuture        |          0 |
+----------------+------------+

Por último, elimine una partición. Puede quitar la partición p2024. Tenga en cuenta que esto también eliminará todos los datos que contiene.

ALTER TABLE sales DROP PARTITION p2024;

Compruebe la estructura de las particiones una última vez:

SELECT
    PARTITION_NAME,
    TABLE_ROWS
FROM
    INFORMATION_SCHEMA.PARTITIONS
WHERE
    TABLE_SCHEMA = 'sales_data' AND TABLE_NAME = 'sales';

La partición p2024 ya no debería aparecer en la lista.

+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p2020_2021     |          3 |
| p2022          |          2 |
| p2023          |          2 |
| pFuture        |          0 |
+----------------+------------+

Ha reorganizado, combinado y eliminado particiones correctamente mediante ALTER TABLE. Esto demuestra la flexibilidad que ofrece la administración de tablas particionadas a medida que evolucionan los datos.

Comprobar el impacto de las particiones en la velocidad de las consultas

En este paso explorará cómo puede afectar el particionamiento al rendimiento de las consultas. Aunque el conjunto de datos actual es pequeño, podrá observar el principio de eliminación de particiones: MySQL solo examina las particiones necesarias. El efecto es mucho más notable con conjuntos de datos grandes.

Recordatorio: Aún debería estar en el shell de MySQL y usando la base de datos sales_data. Si no es así, ejecute:

USE sales_data;

Para observar el impacto del particionamiento, puede usar la instrucción EXPLAIN, que muestra el plan de ejecución de una consulta. En concreto, EXPLAIN PARTITIONS muestra a qué particiones se accede.

Ejecute una consulta que filtre por la clave de particionamiento (el año de sale_date):

EXPLAIN PARTITIONS SELECT * FROM sales WHERE sale_date >= '2023-01-01' AND sale_date < '2024-01-01';

Observe la columna partitions del resultado. Debería indicar que solo se examina la partición p2023.

Ahora ejecute una consulta que no filtre directamente por la clave de particionamiento, sino por otra columna (amount):

EXPLAIN PARTITIONS SELECT * FROM sales WHERE amount > 200;

En este caso, como la condición de la consulta no se aplica directamente a la clave de particionamiento (sale_date), es posible que MySQL tenga que examinar varias particiones o todas ellas para encontrar las filas que coinciden. La columna partitions del resultado de EXPLAIN PARTITIONS mostrará qué particiones se tuvieron en cuenta. Con nuestro pequeño conjunto de datos, es posible que se examinen todas las particiones.

Para ver con más detalle el proceso de ejecución de las consultas y el tiempo empleado, puede usar la función de creación de perfiles de MySQL.

Active la creación de perfiles:

SET profiling = 1;

Ahora vuelva a ejecutar las dos consultas:

SELECT * FROM sales WHERE sale_date >= '2023-01-01' AND sale_date < '2024-01-01';
SELECT * FROM sales WHERE amount > 200;

Consulte los resultados de los perfiles:

SHOW PROFILES;

El resultado mostrará las consultas ejecutadas y su duración. Después podrá examinar los detalles de una consulta específica mediante su Query_ID:

SHOW PROFILE FOR QUERY [Query_ID];

Reemplace [Query_ID] por el ID de la consulta que quiera analizar, según el resultado de SHOW PROFILES. Observe las distintas etapas de ejecución y el tiempo empleado en cada una.

Con nuestro pequeño conjunto de datos, la diferencia de tiempo puede ser insignificante. Sin embargo, en un caso real con millones de filas, las consultas que pueden aprovechar la eliminación de particiones (como la que filtra por YEAR(sale_date)) serán mucho más rápidas que las que necesitan examinar varias particiones o todas ellas.

Por último, desactive la creación de perfiles:

SET profiling = 0;

En este paso ha aprendido a usar EXPLAIN PARTITIONS y la creación de perfiles para comprender cómo afecta el particionamiento a la ejecución y al rendimiento de las consultas.

Resumen

En este laboratorio ha aprendido a implementar el particionamiento de MySQL para grandes conjuntos de datos con el fin de mejorar el rendimiento de las consultas y la gestión de los datos. Primero creó una base de datos y una tabla particionada por rangos según el año de una columna de fecha. Después practicó cómo consultar datos de particiones específicas y observó cómo MySQL utiliza la eliminación de particiones para optimizar las consultas. Por último, aprendió a reorganizar particiones agregándolas, dividiéndolas y combinándolas mediante la instrucción ALTER TABLE, y exploró cómo utilizar EXPLAIN PARTITIONS y la creación de perfiles para comprender el impacto del particionamiento en la velocidad de las consultas. El particionamiento es una técnica eficaz para gestionar y consultar grandes tablas en MySQL.

Cuando haya completado todos los pasos, salga del shell de MySQL escribiendo:

exit;
Relacionado MySQL Cursos
MySQL para Principiantes

MySQL para Principiantes

databasemysql
Laboratorios Prácticos Avanzados de MySQL

Laboratorios Prácticos Avanzados de MySQL

databasemysql