El uso y la introducción detallada de las particiones de tablas MySQL
La partición es dividir una tabla en varios bloques para su operación y almacenamiento, reduciendo así los datos de cada operación y mejorando el rendimiento. Es transparente para la aplicación. Lógicamente solo hay una tabla, pero físicamente esta tabla puede estar compuesta por múltiples particiones físicas. Cada partición es un objeto independiente y puede procesarse de forma independiente.
En segundo lugar, la función de la partición
1. Los datos lógicos se pueden particionar y los datos particionados pueden tener múltiples rutas de archivos físicos diferentes.
2. Puede almacenar más datos y superar el límite máximo de un solo archivo en el sistema.
3. Mejore el rendimiento, aumente la velocidad de lectura y escritura de cada partición y aumente la velocidad de consulta del rango de partición.
4. Puede eliminar datos rápidamente eliminando particiones relacionadas.
5. Al distribuir las consultas de datos entre varios discos, se mejora el rendimiento de E/S del disco.
6. Las consultas que involucran funciones agregadas como SUM() y COUNT() se pueden procesar fácilmente en paralelo.
7. Se pueden realizar copias de seguridad y restaurar particiones independientes, lo que resulta beneficioso para grandes volúmenes de datos.
En tercer lugar, los motores que pueden admitir las particiones
MySQL admite la mayoría de los motores para crear particiones, como MyISAM e InnoDB. MERGE y CSV no son compatibles para crear particiones. Todas las particiones de la misma tabla de particiones deben ser del mismo motor de almacenamiento. Vale la pena señalar que en MySQL8 versión 8, el motor de tablas MyISAM no admite la partición.
Cuarto, confirme que MySQL admite particiones.
Las funciones de partición se han introducido desde MySQL 5.1. Puedes comprobar si es compatible con:
Versión anterior utilizada: Mostrar variables como "%partition%";
Nueva versión: Mostrar complementos;
Verbo (abreviatura de verbo) tipo de partición
1. Partición de rango: asigne varias filas a las particiones en función de los valores de las columnas que pertenecen a un intervalo continuo determinado.
Por ejemplo, una tabla se puede dividir en dos partes, 2001-2010, 2011-2020.
2. Partición de listas: similar a la partición de rangos, una lista es una selección donde los valores de las columnas coinciden con los valores de un conjunto de valores discretos.
Por ejemplo, según los campos, junta los valores 1, 3 y 5, junta los demás valores 2, 4 y 6, y así sucesivamente. ...
3.Partición HASH: seleccione una partición según el valor de retorno de una expresión personalizada y utilice los valores de columna de estas filas para insertarlas en la tabla para el cálculo. La función debe producir valores enteros no negativos.
La partición se realiza mediante operaciones hash y la distribución es relativamente uniforme.
4. Partición de claves: similar a la partición hash, el servidor MySQL proporciona su propia función hash.
La partición por clave es similar a la partición por hash.
Notas sobre la creación de particiones con verbos intransitivos
1. Si hay una clave primaria o clave única en la tabla, la columna de partición debe ser parte de la clave primaria o clave única, es decir, las columnas de la función de partición solo pueden tomar un subconjunto de pk o uk.
2. Si no hay una clave principal o una clave única en la tabla, cualquier columna se puede especificar como columna de partición.
Partición hash, lista y rango anterior a la versión 3. 5.5 requiere que la clave de partición sea int; MySQL 5.5 y superiores admiten particiones de listas y rangos no enteros, es decir: columnas de rango y columnas de lista (se puede usar partición de cadenas).
7. Nomenclatura de particiones
1. El nombre de la partición básicamente sigue los principios que deben seguir otros identificadores de MySQL, como los identificadores utilizados para los nombres de tablas y bases de datos. Cabe señalar que los nombres de las particiones no distinguen entre mayúsculas y minúsculas.
2. No importa qué tipo de partición se utilice, la partición siempre se numera automáticamente cuando se crea y los registros comienzan desde 0.
8. Crear particiones
1. Partición de rango:
Crear tabla "test01" (
` dayid ' int(11) El valor predeterminado es NULL,
` mac` varchar(32) NOT NULL DEFAULT ' ',
` dtype ` varchar(50)NOT NULL DEFAULT ' '
)MOTOR = InnoDB JUEGO DE CARACTERES PREDETERMINADO = utf8
/*!50100 PARTICIÓN POR LISTA (FECHA)
(PARTICIÓN p 20171205 VALORES EN(20171205)MOTOR = InnoDB,
PARTICIÓN p20171204 valor IN(20171204)ENGINE = InnoDB,
PARTICIÓN p20171206 valor IN(20171206)ENGINE = InnoDB,
(20171207)ENGINE = partición p20171207 valor en InnoDB) */
Explicación: Lo anterior es que cuando el uuid es menor que 5, se coloca debajo de la partición p0. Cuando el uuid es mayor que 5 y menor que 10, se coloca debajo de la partición p1. Cuando el uuid es mayor que 15, se coloca debajo de la partición p2. Cuando el uuid es mayor que 15, se coloca debajo de la partición p2.
2. Partición de lista:
Crear tabla tbl_test(
uuid INT no está vacío,
título VARCHAR(20) p>
)
)
Valor de partición p0 en la lista de partición (uuid) (
(1, 2, 3, 5),
El valor de la partición p1 en (7, 9, 10),
El valor de la partición p2 en (11, 15)
)
);
Explicación: Cuando el uuid es igual a 1/2/3/5, se coloca en la partición p0, 7/9/10 se coloca en la partición p1 y 11/ 15 se coloca en la partición p2. Si se utiliza insertar en en este momento y el valor de uuid no existe en la partición p0/p1/p2, la inserción fallará y se informará un error.
3. Partición Hash:
La partición Hash se utiliza principalmente para garantizar que los datos se distribuyan uniformemente entre un número predeterminado de particiones. Se debe especificar explícitamente un valor de columna específico o un conjunto de valores de columna específicos en la partición de rango y lista para especificar qué partición se debe guardar. En la partición HASH, MySQL completará automáticamente estas tareas. Todo lo que necesita hacer es especificar una expresión basada en el valor de la columna que se va a aplicar hash y especificar en cuántas particiones se dividirá la tabla de particiones, como por ejemplo:
Crear tabla tbl_test(
uuid INT no está vacío,
título VARCHAR(20)
))
Partición hash (uuid )(
Partición 3
));
Interpretación: MySQL crea automáticamente 3 particiones y las asigna automáticamente de acuerdo con el uuid insertado a través de un algoritmo cuando insertar en se ejecuta.
Nota:
(1) Debido a que esta expresión se evalúa cada vez que se inserta, actualiza o elimina una fila, esto significa que expresiones muy complejas pueden causar problemas de rendimiento, especialmente al realizar operaciones que afectan a un gran número de filas simultáneamente (como inserciones masivas).
(2) La función hash más eficiente es calcular solo una única columna de la tabla, cuyos valores aumentan y disminuyen consistentemente con el valor de la columna, porque tiene en cuenta la "poda" en el rango de partición. Es decir, cuanto más se acerque el valor de una expresión al valor de la columna en la que se basa, más eficientemente se podrá utilizar para la partición hash.
3.1: Partición Hash Lineal
La partición Hash lineal agrega la palabra clave "LINEAR" en la cláusula "PARTICIÓN POR".
La ventaja de la partición hash lineal es que agregar, eliminar, fusionar y dividir particiones será más rápido, lo que resulta beneficioso para procesar tablas que contienen grandes cantidades de datos.
Su desventaja es que es poco probable que la distribución de datos entre particiones sea equilibrada.
4. Partición de claves
Similar a la partición HASH, la partición HASH permite expresiones personalizadas y la partición KEY no permite expresiones personalizadas. La partición HASH solo admite la partición de números enteros y la partición KEY admite otras; tipos de datos, excepto particiones de blobs y de texto.
A diferencia de la partición hash, no es necesario especificar la clave de partición al crear una tabla particionada por clave. De forma predeterminada, elegirá utilizar una clave principal o una clave única como clave de partición. Si no hay una clave principal o una clave única, se debe especificar una clave de partición.
Crear tabla tbl_test(
uuid INT no está vacío,
título VARCHAR(20)
))
Partición por clave lineal (uuid)
Partición 3;
Explicación: Partición basada en la clave de partición.
5. Subpartición
La subpartición es una repartición de cada partición en la tabla de particiones y es adecuada para almacenar cantidades muy grandes de datos.
Crear tabla tbl_test(
Hora y fecha de registro
))
Por conflicto (año (hora de registro))
Subpartición por hash (TO_DAYS(registerTime))
Subpartición 2
(
El valor de la partición p0 es menor que (2017),
El valor de la partición p1 es menor que (2020),
El valor de la partición p2 es menor que el valor máximo
);
Interpretación: Utilice claves de intervalo para particiones primarias Partición de año, hay tres particiones de intervalo. Estas tres particiones se dividen a su vez en dos subparticiones. En realidad, toda la tabla está dividida en 3 * 2 = 6 particiones. Cada subpartición tiene un hash por día. Se suman los menores a 2017, menores a 2017-2020 y mayores a 2020.
Nota:
(1) En MySQL5.1, las tablas que han sido particionadas por rango o lista se pueden particionar. Las subparticiones pueden utilizar particiones hash o particiones clave. A esto también se le llama partición compuesta.
(2) Cada partición debe tener el mismo número de subparticiones.
(3) Si se utiliza la subdivisión en cualquier partición de una tabla particionada para definir explícitamente cualquier subpartición, se deben definir todas las subparticiones.
(4) Cada cláusula de partición debe contener (al menos) el nombre de una subpartición.
(5) En cada subpartición, el nombre de la subpartición debe ser único y actualmente debe ser único en toda la tabla. Por ejemplo:
Partición por rango (AÑO(registerTime))
Subpartición por hash (TO_DAYS(registerTime))
(
El valor de la partición p0 es menor que (2017)(
Subpartición s0,
Subpartición s1
),
El valor de partición p1 menor que (2020)(
Subpartición s2,
Subpartición s3
),
El valor de la partición p2 es menor que el valor máximo (
Subpartición s4,
Subpartición s5
)
)
Las subparticiones se pueden utilizar para Las tablas, datos e índices muy grandes se pueden distribuir en varios discos. Por ejemplo:
Subpartición s0
Directorio de datos = '/disk0/data '
Directorio de índice = '/disk0/idx '
,
,
Subpartición s1
Directorio de datos = '/disk1/data '
Directorio de índice = '/disk1/ idx '
9. La partición MySQL maneja valores NULL
La partición en MySQL prohíbe el procesamiento de valores NULL, independientemente de si es un valor de columna o el valor de una expresión definida por el usuario. En términos generales, MySQL trata a null como 0 en este caso. Si desea evitar esta situación, debe declarar la columna como "NO NULA" al diseñar la tabla.
Descripción general de la gestión de particiones
Puedes agregar, eliminar, redefinir, fusionar o dividir particiones.
① Gestión de particiones de rango y lista
1. Eliminar declaraciones de partición, como alterar la tabla TBL _ test drop partición P0
Nota:
(1) Cuando se elimina una partición, también se eliminarán todos los datos de la partición.
(2) Puede usar show create table tbl _ test para ver la declaración recién creada para crear una tabla.
(3) Si es una partición de lista, no puede agregar datos eliminados porque los valores de las columnas de estas filas están incluidos en la lista de valores de la partición eliminada.
2. Agregue una declaración de partición como: alterar la tabla TBL _ probar agregar partición (el valor de la partición P3 es menor que (50)); (1) Para tablas particionadas por rango, las nuevas particiones solo se pueden agregar al principio de la lista de particiones.
(2) Para tablas particionadas por lista, no puede agregar ningún valor que ya esté incluido en la lista de valores de partición existente.
3. Si desea redefinir la partición sin perder datos, puede utilizar la siguiente instrucción:
ALTER TABLE tbl_name reorganizará la particiónpartition_list en (partition_definitions)
( 1) Divida la partición de la siguiente manera:
ALTER TABLE tbl_name reorganiza la partición partición_lista en (el valor de la partición s0 es menor que (5), el valor de la partición s1 es menor que (10 ));
O de la siguiente manera:
ALTER TABLE tbl_name reorganiza la partición p0 en (el valor de la partición s0 es (1, 2, 3), el valor de la partición s1 es (4, 5)
(2) Fusionar particiones, como por ejemplo: modificar la tabla TBL_name para reorganizar las particiones s0 y s1 (el valor de la partición P0 es (1, 2, 3, 4, 5));
4. Elimine todas las particiones, pero conserve los datos, en la forma: alterar tabla TBL _ nombre eliminar partición;
②Gestión de particiones clave y hash
1. número de particiones, como: modificar tabla TBL _ nombre fusionar partición 2
2. Agregar declaración de número de partición, como: modificar tabla TBL _ nombre agregar partición particiones 2
③Otras declaraciones de administración de particiones
1. Reconstruir partición: similar a eliminar todos los registros guardados en la partición y luego reinsertarlos, se puede usar para desfragmentar la partición. Por ejemplo: Alter Table TBL _ Name reconstruye las particiones P2, P3;
2. Optimiza la partición: si se elimina una gran cantidad de filas de la partición o si hay filas con longitud variable (es decir, VARCHAR). , columna de tipo BLOB o TEXT) se ha modificado ampliamente, puede usar Alter Table TBL_Name para optimizar la partición para recuperar espacio no utilizado y desfragmentar los archivos de datos de la partición.
Por ejemplo: modificar la tabla TBL_name optimizar la partición P2, P3;
3 Analizar la partición: leer y guardar la distribución de claves de la partición, como por ejemplo: modificar la tabla TBL_name analizar la partición P2, P3;
3. p>
4. Verifique la partición: verifique si los datos o el índice en la partición están dañados, como: modificar la tabla TBL_name verificar la partición P2, P3; Reparar la partición: Repare la partición dañada, como modificar la tabla TBL _ nombre para reparar las particiones P2, P3
Ver información de la partición
1. de información _ esquema particiones donde tabla _ esquema ='arch1'y table_name='tbl_test'g;
2. Verifique los datos en la partición: seleccione * de la partición TBL_test (P0);
3. Verifique las particiones que MySQL puede operar: Explique la partición Seleccione * De TBL _ Prueba donde UUID = 2
XI. Restricciones
1. El número máximo de particiones no puede exceder 1024. Generalmente se recomienda que el número de particiones para una sola tabla no exceda 50.
2. Si hay índices únicos o claves primarias, la columna de partición debe incluirse en todos los índices únicos o claves primarias.
3. No se admiten claves externas.
4. No se admite la indexación de texto completo. Si se crea un índice en la clave de partición de una tabla particionada, el índice también se dividirá.
5. La partición por fecha es apropiada porque se pueden utilizar muchas funciones de fecha. Pero no hay muchas funciones de asignación adecuadas para cadenas.
6. Solo se pueden subparticionar particiones de rango y lista, mientras que las particiones hash y de clave no se pueden subparticionar.
7. Las tablas temporales no se pueden particionar.
8. Las tablas particionadas no tienen ninguna ventaja para consultar registros individuales.
9. Presta atención al coste de elección de mamparas. Sin insertar una fila de datos, debe filtrar las particiones insertadas según una expresión.
10. El campo de partición no debe estar vacío en la medida de lo posible.