Problema de particionamiento de tabla en MySQL.
Estoy tratando de entender la partición de tablas de MySQL, a continuación se detallan los detalles y el ejemplo que intenté.
Versión de MySQL: 8.0.27
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
)
ENGINE=INNODB
PARTITION BY RANGE(YEAR(separated))(
PARTITION p0 VALUES LESS THAN(1991),
PARTITION p1 VALUES LESS THAN(1996),
PARTITION p2 VALUES LESS THAN(2001),
PARTITION p3 VALUES LESS THAN MAXVALUE
);
Declaración de inserción de ejemplo:
insert into employees values (1,'ron', 'nath', '2019-10-10', '2021-10-12',123,123);
insert into employees values (2,'ram', 'nath', '1991-10-10', '1999-10-12',123,123);
Verificar que los datos se cargaron en la partición:
SELECT PARTITION_NAME, TABLE_ROWS
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME='employees';
Esto muestra datos en las particiones p2 y p3. Pero cuando ejecuto la siguiente consulta SELECT y hago un EXPLAIN, veo que se están referenciando todas las particiones.
EXPLAIN select * from employees where year(separated)='1999';
Como se puede ver en las capturas de pantalla, se están referenciando todas las particiones, no estoy seguro por qué. Esperaba que solo se hiciera referencia a una partición específica cuando se aplicara el filtro de la cláusula WHERE para obtener datos de una partición específica.
davy.ai
Comprendiendo la partición de tablas de MySQL
La partición de tablas de MySQL es una técnica útil utilizada para la gestión de bases de datos. Consiste en dividir una tabla grande en particiones más pequeñas y manejables. Cada partición tiene su propio motor de almacenamiento, archivos de datos e índices. La partición puede realizarse en función de varios criterios como rango, hash, clave y lista.
En el ejemplo proporcionado, la tabla “employees” se particiona por rango basado en el año de separación. La tabla se divide en cuatro particiones y se insertan datos en dos particiones.
Para verificar si los datos se han cargado correctamente en las particiones, se ejecuta la siguiente instrucción SQL:
Esta instrucción SQL devuelve dos particiones p2 y p3, ya que se han insertado datos para los años 1999 y posteriores.
Sin embargo, al ejecutar la siguiente instrucción SQL con una cláusula
EXPLAIN:Se devuelve que se están haciendo referencia a todas las particiones, lo cual es inesperado ya que solo debería hacer referencia a una partición según la cláusula WHERE. Hay ciertos puntos a considerar al ejecutar una consulta en tablas particionadas.
En primer lugar, la columna de partición debe mencionarse explícitamente en la cláusula WHERE, de lo contrario, no se aplicará la optimización de poda de particiones.
En segundo lugar, la cláusula WHERE debe utilizar la misma expresión utilizada para definir la partición de la tabla.
Por lo tanto, para lograr el resultado deseado, la instrucción SQL debe modificarse de la siguiente manera para que MySQL reconozca la columna de partición:
La palabra clave
PARTITIONespecifica que solo se deben buscar la o las particiones especificadas, en lugar de todas las particiones. Esta instrucción SQL modificada resultaría en que solo se hace referencia a la partición relevante en el plan de EXPLAIN.