Cómo crear un índice en una tabla de producción MySQL grande sin bloqueo de tabla

Antecedentes del problema:

Crear un índice en una tabla MySQL grande puede ser una tarea desalentadora, especialmente en un entorno de producción donde el acceso ininterrumpido es crucial. Las declaraciones CREATE INDEX tradicionales pueden dar como resultado un bloqueo completo de la tabla, bloqueando todas las operaciones simultáneas.

Consideraciones sobre la versión de MySQL:

• En MySQL 5.6 y superior, actualizaciones de índice se realizan en línea, lo que permite que las operaciones de lectura y escritura continúen durante la creación del índice.
• Sin embargo, en MySQL 5.5 y versiones anteriores, incluidas las tablas InnoDB y MyISAM, las actualizaciones del índice se realizarán en línea. el bloque escribe en la tabla.

Enfoque de maestros circulares:

Para versiones de MySQL anteriores a 5.6, un enfoque efectivo es la técnica de maestros circulares:

1. Configurar un maestro secundario (Maestro B) que se replica desde el maestro principal (Maestro A).
2. Realice la actualización del esquema en el Maestro B (permitiendo se quede atrás durante la actualización).
3. Asegúrese de que el cambio de esquema sea compatible con la replicación de comandos del esquema de versión descendente en el Maestro A.
4. Cambie atómicamente todos los clientes del Maestro A al Maestro B.
5. Actualice el esquema en el Maestro A y conviértalo en el maestro secundario.

Cambio de esquema en línea de Percona Herramienta:

Esta herramienta automatiza el enfoque de maestros circulares al:

• Crear una nueva tabla con el esquema actualizado.
• Mantener la nueva tabla sincronizada con la tabla original usando un disparador.
• Copiando filas en lotes de la tabla original.
• Reemplazando la tabla original con la nueva table.

Consideraciones de AWS RDS:

Para bases de datos MySQL alojadas en RDS de Amazon, la función "Promoción de lectura de réplica" se puede utilizar para facilitar los cambios de esquema sin bloqueo de la mesa. Esto implica realizar cambios en un esclavo de solo lectura y luego promoverlo para que se convierta en el nuevo maestro.

Técnicas alternativas:

• Usar un servidor temporal tabla: Cree una tabla temporal con el nuevo índice, inserte datos de la tabla original y reemplace la tabla original con la tabla temporal.
• Particie el tabla: Divida la tabla en particiones más pequeñas, cree el índice en cada partición por separado y luego vuelva a fusionar las particiones.
• Utilice un proceso en segundo plano: Cree un proceso en segundo plano independiente que crea gradualmente el índice mientras la tabla permanece accesible para operaciones normales. Este enfoque no es compatible con todas las versiones de MySQL.