La historia de una optimización MySQL

Se tratará de la optimización en la base de datos MySQL.

Esto sucedió cuando creamos un sistema para boletines electrónicos. Se suponía que nuestro sistema enviaba decenas de millones de cartas al día. Enviar una carta no es una tarea fácil, aunque todo parece bastante primitivo:

1. Recopile una carta de html creativo, sustituya datos personalizados.
2. Agregue un píxel de visualización del mensaje, reemplace todos los enlaces en el mensaje con el suyo propio para rastrear los clics.
3. Compruebe antes de enviar que el correo electrónico no está en la lista negra.
4. Enviar un correo electrónico a un grupo específico.

Te contaré más sobre el segundo párrafo:
Microservice mail-builder está preparando una carta para enviar:

• encuentra todos los enlaces en la carta;
• se genera un uuid único de 32 caracteres para cada enlace;
• reemplaza el enlace original por uno nuevo y guarda los datos en la base de datos.

Por lo tanto, todos los enlaces de origen se reemplazarán con uuid y el dominio se cambiará al nuestro. Cuando recibe una solicitud GET usando este enlace, representamos la imagen original o redirigimos al enlace original. El ahorro se produce en la base de datos MySQL, guardamos el uuid generado junto con el enlace original y con cierta metainformación (correo electrónico del usuario, identificación de correo y otros datos). La desnormalización nos ayuda en 1 solicitud a obtener todos los datos necesarios para guardar estadísticas o iniciar algún tipo de cadena de activación.

Problema número 1

La generación de uuid en nosotros dependía de la marca de tiempo.

Dado que los correos generalmente ocurren en un cierto período de tiempo y se lanzan muchas instancias de microservicio para ensamblar una carta, resultó que algunos de los líquidos eran muy similares. Esto dio una baja selectividad. UPD: debido a que los datos fueron similares, trabajar con el bi-árbol no fue muy efectivo.

Resolvimos este problema usando el módulo uuid en python, donde no hay dependencia del tiempo.
Tal cosa implícita redujo la velocidad de los índices.

¿Cómo va el almacenamiento?

La estructura de la tabla era la siguiente:

CREATE TABLE IF NOT EXISTS `Messages` ( `UUID` varchar(32) NOT NULL, `Message` json NOT NULL, `Inserted` DATE NOT NULL, PRIMARY KEY (`UUID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 

En el momento de la creación, todo parecía lógico:
UUID es una clave principal y también es un índice agrupado. Cuando hacemos una selección en este campo, simplemente seleccionamos el registro, porque todos los valores se almacenan allí mismo. Esta fue una decisión deliberada. Obtenga más información sobre el índice agrupado.

Todo estuvo genial hasta que la mesa creció.

Problema número 2

Si lee más sobre el índice de clúster, puede encontrar este matiz:

Al agregar una nueva fila a la tabla, no se agrega al final del archivo, no al final de la lista plana, sino a la rama deseada de la estructura de árbol correspondiente al ordenarla.

Por lo tanto, al aumentar la carga, aumenta el tiempo de inserción.

La solución fue utilizar una estructura de tabla diferente.

 CREATE TABLE IF NOT EXISTS `Messages` ( `ID` int(11) NOT NULL AUTO_INCREMENT, `UUID` varchar(32) NOT NULL, `Message` json NOT NULL, `Inserted` DATE NOT NULL, PRIMARY KEY (`ID`), UNIQUE KEY `UUID` (`UUID`, `Inserted`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 

Dado que la clave principal ahora se incrementa automáticamente y mysql almacena el caché del último lugar de inserción, ahora la inserción siempre ocurre al final, es decir, Innodb está optimizado para escribir valores secuencialmente crecientes.

Encontré los detalles de esta optimización en el código fuente de Postgres. Mysql implementa una optimización muy similar.
Por supuesto, tuve que agregar una clave única para que no hubiera conflictos, pero aumentamos la velocidad de inserción.

Con la base creciendo aún más, pensamos en eliminar datos antiguos. Usar DELETE en el campo Insertado no es absolutamente óptimo: es mucho tiempo y el espacio no se liberará hasta que ejecutemos el comando optimizar tabla . Por cierto, esta operación bloquea completamente la mesa, esto no nos convenía en absoluto.

Por lo tanto, decidimos dividir nuestra tabla en particiones.
1 día - 1 partición, las antiguas caen automáticamente cuando llega el momento.

Problema número 3

Tuvimos la oportunidad de eliminar datos antiguos, pero no tuvimos la oportunidad de elegir la partición deseada, porque con select`e especificamos solo uuid, mysql no sabe en qué partición deberíamos buscarlo y está buscando en todos.

La solución nació del problema n. ° 1: agregue una marca de tiempo al líquido generado. Solo que esta vez lo hicimos un poco diferente: insertamos una marca de tiempo en un lugar aleatorio en la línea, no al principio o al final; antes y después agregaron un símbolo de guión para que se pueda obtener con una expresión regular.

Con esta optimización, pudimos obtener la fecha en que se generó el uuid y ya hicimos una selección que indica el valor específico del campo Insertado. Ahora leemos los datos inmediatamente de la partición que necesitamos.

Además, gracias a cosas como ROW_FORMAT = COMPRESSED y al cambiar la codificación a latin1 , ahorramos aún más espacio en el disco duro.