Prefacio

Este artículo cubre

  • Particiones usos y no usos
  • Cómo mantener una tabla particionada de series de tiempo
  • Secretos de AUTO_INCREMENT

Primero, mis opiniones sobre el PARTICIONAMIENTO

Tomado de Rick’s RoTs – Reglas generales

  • # 1: No utilice el PARTICIONAMIENTO hasta que sepa cómo y por qué le ayudará.
  • No use PARTICIÓN a menos que tenga más de 1 millón de filas
  • No más de 50 PARTICIONES en una tabla (abiertas, mostrar el estado de la tabla, etc., se ven afectadas) (¿se corrigió en MySQL 5.6.6?; Una mejor solución llegará eventualmente en 5.7)
  • LA PARTICIÓN POR RANGO es el único método útil.
  • Las SUBPARTICIONES no son útiles.
  • El campo de partición no debe ser el primer campo en ninguna key.
  • Está bien tener un AUTO_INCREMENT como la primera parte de un compuesto key, o en un índice no ÚNICO.

Es muy tentador creer que el PARTICIONAMIENTO resolverá los problemas de desempeño. Pero a menudo está mal.

La partición divide una mesa en varias mesas más pequeñas. Pero el tamaño de la mesa rara vez es un problema de rendimiento. En cambio, el tiempo de E / S y los índices son los problemas.

Una falacia común: “El particionamiento hará que mis consultas se ejecuten más rápido”. No lo hará. Reflexione sobre lo que se necesita para una “consulta puntual”. Sin particiones, pero con un índice apropiado, hay un BTree (el índice) para profundizar y encontrar la fila deseada. Para mil millones de filas, esto podría tener 5 niveles de profundidad. Con la partición, primero se elige y se “abre” la partición, luego se perfora un BTree más pequeño (de por ejemplo 4 niveles). Bueno, los ahorros del BTree menos profundo se consumen al tener que abrir la partición. De manera similar, si observa los bloques de disco que deben tocarse y cuáles de ellos es probable que se almacenen en caché, llega a la conclusión de que es probable que se produzca aproximadamente el mismo número de accesos al disco. Dado que las visitas al disco son el costo principal de una consulta, el particionamiento no obtiene ningún rendimiento (al menos en este caso típico). El caso 2D (abajo) da la principal contradicción a esta discusión.

Casos de uso para PARTICIONAR

Caso de uso n. ° 1: serie temporal. Quizás el caso de uso más común en el que la PARTICIÓN brilla es en un conjunto de datos donde los datos “antiguos” se eliminan periódicamente de la tabla. RANGE PARTITIONing por día (u otra unidad de tiempo) le permite hacer una DROP PARTITION casi instantánea más REORGANIZE PARTITION en lugar de un DELETE mucho más lento. Gran parte de este blog se centra en este caso de uso. Este caso de uso también se analiza en Big DELETEs

La gran victoria para el Caso # 1: DROP PARTITION es mucho más rápido que BORRAR muchas filas.

Caso de uso n. ° 2: índice 2-D. Los ÍNDICES son inherentemente unidimensionales. Si necesita dos “rangos” en la cláusula WHERE, intente migrar uno de ellos a PARTITIONing.

Encontrar las 10 pizzerías más cercanas en un mapa necesita un índice 2D. La poda de particiones da una segunda dimensión. Consulte Indización de latitud / longitud que usa PARTICIÓN POR RANGO (latitud) junto con CLAVE PRIMARIA (longitud, …)

La gran victoria para el caso n. ° 2: escanear menos filas.

Caso de uso n. ° 3: punto caliente. Esto es un poco complicado de explicar. Dada esta combinación:

  • El índice de una tabla es demasiado grande para almacenarlo en caché, pero el índice de una partición se puede almacenar en caché, y
  • Se accede al índice de forma aleatoria y
  • La ingesta de datos normalmente estaría limitada por E / S debido a la actualización del índice. El particionamiento puede mantener todo el índice “activo” en la RAM, evitando así una gran cantidad de E / S.

La gran victoria para el caso n. ° 3: mejorar el almacenamiento en caché para disminuir la E / S y acelerar las operaciones.

Caso de uso n. ° 4: espacio de tabla transportable. Utilizando la partición EXPORTAR / IMPORTAR para archivar o importar datos rápidamente. (Importar puede ser complicado debido a la partición key.) Consulte también Espacios de tabla transportables para particiones InnoDB. Ese enlace habla de 5.7, pero tiene una sección “¿Pero cómo hacer esto en 5.6?”

Consulte también FLUSH TABLES … FOR EXPORT, que no era compatible con tablas particionadas InnoDB hasta MySQL 5.6.17 / MariaDB 10.0.8.

La gran victoria para el caso n. ° 4: mover rápidamente una partición entre tablas (o servidores).

Caso de uso n. ° 5 – Todavía tengo que encontrar un quinto caso de uso.

Tenga en cuenta que casi siempre, estos casos de uso involucran particiones RANGE, no las otras formas.

AUTO_INCREMENT en PARTITION

  • Para que AUTO_INCREMENT funcione (en cualquier tabla), debe ser el primer campo en algún índice. Período. No hay otros requisitos para indexarlo.
  • Ser el primer campo en algún índice permite al motor encontrar el valor ‘siguiente’ al abrir la tabla.
  • AUTO_INCREMENT no necesita ser ÚNICO. Lo que pierde: prevención de insertar explícitamente una identificación duplicada. (De todos modos, esto rara vez es necesario).

Ejemplos (donde id es AUTO_INCREMENT):

  • LLAVE PRIMARIA (…), INDICE (id)
  • PRIMARY KEY (…), UNIQUE (id, partition_key) – no es útil
  • ÍNDICE (id), ÍNDICE (…) (pero no ÚNICO keys)
  • PRIMARY KEY (id), … – funciona solo si id es la partición key (no muy útil)

Mantenimiento de PARTICIÓN para el caso de la serie temporal

Centrémonos en la tarea de mantenimiento involucrada en el Caso # 1, como se describe arriba.

Tienes una mesa grande que crece en un extremo y se poda en el otro. Los ejemplos incluyen noticias, registros y otra información transitoria. PARTITION BY RANGE es un vehículo excelente para una mesa de este tipo.

  • DROP PARTITION es mucho más rápido que DELETE. (Esta es la gran razón para hacer este tipo de partición).
  • Las consultas a menudo se limitan a datos ‘recientes’, aprovechando así la “poda de particiones”.

Según el tipo de datos y cuánto tiempo antes de que caduquen, es posible que tenga particiones diarias, semanales o por horas (etc.).

No existe una declaración SQL simple para “eliminar particiones de más de 30 días” o “agregar una nueva partición para mañana”. Sería tedioso hacerlo a mano todos los días.

Vista de alto nivel del código

ALTERTABLE tbl
    DROPPARTITION from20120314;ALTERTABLE tbl
    REORGANIZE PARTITION future INTO(PARTITION from20120415 VALUES LESS THAN (TO_DAYS('2012-04-16')),PARTITION future     VALUES LESS THAN MAXVALUE);

Después de lo cual tienes …

CREATETABLE tbl (
        dt DATETIMENOTNULL,-- or DATE...PRIMARYKEY(..., dt),UNIQUEKEY(..., dt),...)PARTITIONBY RANGE (TO_DAYS(dt))(startVALUES LESS THAN (0),
        from20120315 VALUES LESS THAN (TO_DAYS('2012-03-16')),
        from20120316 VALUES LESS THAN (TO_DAYS('2012-03-17')),...
        from20120414 VALUES LESS THAN (TO_DAYS('2012-04-15')),
        from20120415 VALUES LESS THAN (TO_DAYS('2012-04-16')),
        future       VALUES LESS THAN MAXVALUE
    );

¿Por qué?

Quizás notó algunas cosas extrañas en el ejemplo. Déjame explicarte.

  • Nomenclatura de particiones: Hágalas útiles.
  • from20120415 … 04-16: Tenga en cuenta que MENOS DE es la fecha del día siguiente
  • La partición de “inicio”: consulte el párrafo siguiente.
  • La partición “futura”: normalmente está vacía, pero puede detectar desbordamientos; más tarde.
  • El rango key (dt) debe incluirse en cualquier PRIMARIO o ÚNICO key.
  • El rango key (dt) debe ser el último en cualquier keys está en – Ya lo ha “podado”; es casi inútil en el índice, especialmente al principio.
  • DATETIME, etc.: elegí este tipo de datos porque es típico de una serie temporal. Las versiones más nuevas de MySQL permiten TIMESTAMP. INT se puede utilizar; etc.
  • Hay un día adicional (03-16 al 04-16): El último día está solo parcialmente lleno.

¿Por qué la partición de “inicio” falsa? Si se usara una fecha y hora no válida (31 de febrero), la fecha y hora se convertiría en NULL. Los NULL se colocan en la primera partición. Dado que cualquier SELECT podría tener una fecha no válida (sí, esto estira las cosas), el eliminador de particiones siempre incluye la primera partición en el conjunto resultante de particiones para buscar. Entonces, si SELECT debe escanear la primera partición, sería un poco más eficiente si esa partición estuviera vacía. De ahí la falsa partición de “inicio”. Una discusión más larga, por The Data Charmer 5.5 elimina la verificación falsa, pero solo si cambia a una nueva sintaxis:

PARTITIONBY RANGE COLUMNS(dt)(PARTITION day_20100226 VALUES LESS THAN ('2010-02-27'),...

Más sobre la partición “futura”. Tarde o temprano, el cron / EVENT para agregar la partición de mañana no se ejecutará. Lo peor que podría pasar es que se pierdan los datos del mañana. La forma más fácil de evitarlo es tener una partición lista para capturarla, incluso si esta partición normalmente siempre está vacía.

Tener la partición “futura” hace que el script ADD PARTITION sea un poco más complejo. En cambio, necesita tomar los datos de mañana del “futuro” y colocarlos en una nueva partición. Esto se hace con el comando REORGANIZE que se muestra. Normalmente no es necesario mover nada y ALTER no tarda prácticamente nada en hacerlo.

¿Cuándo hacer los ALTER?

  • DROP si la partición más antigua es “demasiado antigua”.
  • Agregue “mañana” cerca del final de hoy, pero no intente agregarlo dos veces.
  • No cuente las particiones, hay dos más. Utilice los nombres de las particiones o information_schema.PARTITIONS.PARTITION_DESCRIPTION.
  • DROP / Add solo una vez en el script. Vuelva a ejecutar el script si necesita más.
  • Ejecute el script con más frecuencia de la necesaria. Para particiones diarias, ejecute el script dos veces al día o incluso cada hora. ¿Por qué? Reparación automática.

Variantes

Como he dicho muchas veces, en muchos lugares, BY RANGE es quizás la única variante útil. Y una serie de tiempo es el uso más común para PARTICIONAR.

  • (como se explica aquí) DATETIME / DATE con TO_DAYS ()
  • DATETIME / DATE con TO_DAYS (), pero con intervalos de 7 días
  • TIMESTAMP con TO_DAYS (). (versión 5.1.43 o posterior)
  • PARTICIÓN POR COLUMNAS DE RANGO (FECHA Y HORA) (5.5.0)
  • PARTICIÓN POR RANGO (TIMESTAMP) (versión 5.5.15 / 5.6.3)
  • PARTICIÓN POR RANGO (TO_SECONDS ()) (5.6.0)
  • INT UNSIGNED con constantes calculadas como marcas de tiempo de Unix.
  • INT UNSIGNED con constantes para algunas series no basadas en el tiempo.
  • MEDIUMINT UNSIGNED que contiene un “ID de hora”: FLOOR (FROM_UNIXTIME (marca de tiempo) / 3600)
  • Meses, trimestres, etc.: invente una notación que funcione.

Cuantas particiones?

  • Por ejemplo, con 5 particiones, obtiene muy pocos beneficios.
  • Más de, digamos, 50 particiones, y encuentra ineficiencias en otros lugares.
  • Ciertas operaciones (MOSTRAR ESTADO DE LA TABLA, abrir la tabla, etc.) abren todas las particiones.
  • MyISAM, antes de la versión 5.6.6, ¡bloquearía todas las particiones antes de podar!
  • La poda de particiones no ocurre en INSERT (hasta la Versión 5.6.7), por lo que INSERT necesita abrir todas las particiones.
  • Un posible caso de uso de 2 particiones: http://forums.mysql.com/read.php?24,633179,633179
  • 8192 particiones es un límite estricto (1024 antes de 5.6.7).
  • Antes de las “particiones nativas” (5.7.6), cada partición consumía una parte de la memoria.

Código detallado

Implementación de referencia, en Perl, con demostración de particiones diarias

La complejidad del código está en el descubrimiento de los nombres de las PARTICIONES, especialmente del más antiguo y del ‘siguiente’.

Para ejecutar la demostración,

  • Instale Perl y DBIx :: DWIW (desde CPAN).
  • copie el archivo txt (enlace de arriba) a demo_part_maint.pl
  • ejecute perl demo_part_maint.pl para obtener el resto de las instrucciones

El programa generará y ejecutará (cuando sea necesario) cualquiera de estos:

ALTERTABLE tbl REORGANIZE PARTITION
        future
   INTO(PARTITION from20150606 VALUES LESS THAN (736121),PARTITION future VALUES LESS THAN MAXVALUE
   )ALTERTABLE tbl
                    DROPPARTITION from20150603

Postlog

Escritura original: octubre de 2012; Casos de uso agregados: octubre de 2014; Actualizado: junio de 2015; 8.0: septiembre de 2016

Diapositivas de Percona Amsterdam 2015

El PARTICIONAMIENTO requiere al menos MySQL 5.1

Los consejos de este documento se aplican a MySQL, MariaDB y Percona.

Futuro (según lo previsto en 2016):

  • MySQL 5.7.6 tiene “particiones nativas para InnoDB”.
  • Soporte de FOREIGN KEY, quizás en un 8.0.xx posterior.
  • “ÍNDICE GLOBAL”: esto evitaría la necesidad de colocar la partición key en cada índice único, pero hacen que DROP PARTITION sea costoso. Esto será más lejano en el futuro.

MySQL 8.0, lanzado en septiembre de 2016, aún no disponible para uso general)

  • Solo se pueden particionar las tablas InnoDB; es probable que MariaDB continúe manteniendo el particionamiento en tablas que no son InnoDB, pero Oracle claramente no lo está.
  • Algunos de los problemas que tienen muchas particiones se reducen con el diccionario de datos en una tabla.

La partición nativa dará:

  • Esto mejorará ligeramente el rendimiento al combinar dos “controladores” en uno.
  • Disminución del uso de memoria, especialmente cuando se usa una gran cantidad de particiones.

Ver también

Rick James amablemente nos permitió usar este artículo en la Base de conocimientos.

El sitio de Rick James tiene otros consejos útiles, procedimientos, optimizaciones y consejos de depuración.

Fuente original: http://mysql.rjweb.org/doc.php/partitionmaint

El contenido reproducido en este sitio es propiedad de sus respectivos dueños, y MariaDB no revisa este contenido con anticipación. Los puntos de vista, la información y las opiniones expresadas por este contenido no representan necesariamente las de MariaDB o de cualquier otra parte.