Como criar um índice em uma grande tabela de produção MySQL sem bloqueio de tabela

Antecedentes do problema:

Criar um índice em uma tabela MySQL grande pode ser uma tarefa difícil, especialmente em um ambiente de produção onde o acesso ininterrupto é crucial. As instruções CREATE INDEX tradicionais podem resultar em um bloqueio completo da tabela, bloqueando todas as operações simultâneas.

Considerações sobre a versão do MySQL:

• No MySQL 5.6 e superior, atualizações de índice são realizadas on-line, permitindo que as operações de leitura e gravação continuem durante a criação do índice.
• No entanto, no MySQL 5.5 e anteriores, incluindo tabelas InnoDB e MyISAM, as atualizações de índice bloco grava na tabela.

Abordagem Circular Masters:

Para versões do MySQL anteriores a 5.6, uma abordagem eficaz é a técnica circular masters:

1. Configure um mestre secundário (Mestre B) replicando a partir do mestre primário (Mestre A).
2. Realize a atualização do esquema no Mestre B (permitindo ele ficará para trás durante a atualização).
3. Certifique-se de que a alteração do esquema seja compatível com a replicação de comandos do esquema de downversion no Mestre A.
4. Alterne atomicamente todos os clientes do Mestre A para o Mestre B.
5. Atualize o esquema no Mestre A e torne-o o mestre secundário.

pt-online-schema-change da Percona Ferramenta:

Esta ferramenta automatiza a abordagem de mestres circulares ao:

• Criando uma nova tabela com o esquema atualizado.
• Mantendo a nova tabela sincronizada com a tabela original usando um gatilho.
• Copiando linhas em lotes da tabela original.
• Substituindo a tabela original pela nova tabela.

Considerações sobre AWS RDS:

Para bancos de dados MySQL hospedados no RDS da Amazon, o recurso "Promoção de réplica de leitura" pode ser usado para facilitar alterações de esquema sem bloqueio de mesa. Isso envolve fazer alterações em um escravo somente leitura e, em seguida, promovê-lo para se tornar o novo mestre.

Técnicas alternativas:

• Use um temporário tabela: Crie uma tabela temporária com o novo índice, insira dados da tabela original e substitua a tabela original pela tabela temporária.
• Particione o tabela: Divida a tabela em partições menores, crie o índice em cada partição separadamente e depois mescle as partições novamente.
• Use um processo em segundo plano: Crie um processo em segundo plano separado que cria gradualmente o índice enquanto a tabela permanece acessível para operações normais. Esta abordagem não é suportada em todas as versões do MySQL.