Pular para o conteúdo principal

VillageSQL is a drop-in replacement for MySQL with extensions.

All examples on this page work on VillageSQL. Install Now →
Executar ALTER TABLE em uma tabela com tráfego ativo levanta uma pergunta: esta instrução vai bloquear a aplicação enquanto é executada? Este guia mede isso diretamente, em uma tabela real, com uma conexão concorrente observando o tempo todo. Migrações de Esquema no MySQL cobre como organizar e versionar os arquivos de migração. Este guia cobre o que uma tabela ativa realmente faz enquanto um desses arquivos de migração é executado contra ela.

Configuração do Teste

Todos os números deste guia vieram da mesma tabela: uma tabela orders carregada com 3.276.802 linhas (168,7 MB de dados) em um servidor VillageSQL local.
As linhas foram geradas com um cross join de uma tabela de números e, em seguida, duplicadas algumas vezes com INSERT ... SELECT a partir da própria tabela, até chegar a alguns milhões de linhas. O tamanho exato não importa para as conclusões abaixo, mas a tabela precisa ser grande o suficiente para que uma reconstrução completa leve mais do que uma fração de segundo, ou você não consegue observar nada sendo executado de forma concorrente a ela.

Um ALTER Online Não Bloqueia o Tráfego

Adicionar um índice secundário com ALGORITHM=INPLACE, LOCK=NONE na tabela de 3,27 milhões de linhas acima:
levou 1,003 segundo de ponta a ponta. Enquanto estava em execução, o SHOW PROCESSLIST em uma segunda conexão o mostrou como uma operação real, em andamento:
A information_schema.INNODB_TRX mostra a mesma instrução como uma transação em execução, a partir de um ADD INDEX separado na mesma tabela:
A partir de uma terceira conexão, aberta e executada enquanto o ALTER acima ainda estava no estado altering table, um INSERT e um SELECT na mesma tabela foram ambos concluídos imediatamente:
Nenhuma das instruções esperou o ALTER terminar. Esse é todo o sentido do LOCK=NONE: a reconstrução da tabela acontece em segundo plano, usando o log de DDL online do InnoDB para capturar as alterações concorrentes, e o DML na tabela prossegue sobre as estruturas de dados originais até que a reconstrução alcance as alterações e seja colocada no lugar.

ALGORITHM=INSTANT vs. ALGORITHM=INPLACE

Nem toda operação online custa o mesmo. Adicionar uma coluna no final da tabela com ALGORITHM=INSTANT altera somente os metadados da tabela; nunca lê nem reescreve uma única linha:
Forçando o mesmo tipo de alteração, a adição de uma coluna com valor padrão, por meio de ALGORITHM=INPLACE em vez disso:
Nesta tabela de 3,27 milhões de linhas, o INSTANT foi cerca de 20 vezes mais rápido do que o INPLACE para o que é, funcionalmente, a mesma adição de coluna. O INPLACE ainda precisa tocar nas entradas do dicionário de dados da tabela e se coordenar com qualquer DML concorrente; o INSTANT não faz nenhuma das duas coisas. A diferença cresce com o tamanho da tabela: o INSTANT permanece constante, independentemente do número de linhas, enquanto o INPLACE cresce com ele. O INSTANT não está disponível para todas as operações. Adicionar uma coluna com um valor padrão BLOB/TEXT, ou alterar o tipo de uma coluna existente, recai em INPLACE ou COPY. Adicionar uma coluna em qualquer posição, inclusive no meio de uma tabela, também usa INSTANT (MySQL 8.0.29+).

Quando o DDL Online Não Pode Ajudar: Uma Alteração de Tipo de Coluna

Nem todo ALTER TABLE consegue evitar um bloqueio. Alterar o tipo de dados de uma coluna exige que o MySQL reescreva todas as linhas, e o INPLACE não consegue fazer isso:
Solicitar ALGORITHM=INSTANT para a mesma alteração falha da mesma forma, com um motivo diferente:
O único algoritmo que o MySQL aceita para esta instrução é o COPY, que reconstrói a tabela inteira e mantém um bloqueio exclusivo durante todo o processo. Executá-lo na mesma tabela e inserir uma linha a partir de uma segunda conexão durante a reconstrução demonstra a diferença diretamente:
O INSERT concorrente, emitido 0,3 segundo depois do início do ALTER, só retornou quando o ALTER estava quase concluído:
O SHOW PROCESSLIST durante essa janela mostrou o ALTER no estado copy to tmp table e o INSERT bloqueado ao lado dele, no estado Waiting for table metadata lock. Esse estado é o diagnóstico: ele nomeia a instrução que está travada e diz que ela está na fila atrás de um bloqueio, e não sendo executada lentamente. Tentar falhar rápido com LOCK=NONE nessa mesma instrução é exatamente como você descobre, antes de executá-la em uma tabela de produção, que ela vai bloquear os gravadores por todo o tempo que a reescrita levar.

Chaves Estrangeiras e DDL Online

As chaves estrangeiras adicionam restrições que são independentes de ALGORITHM e LOCK. Em uma tabela filha order_items com FOREIGN KEY (order_id) REFERENCES orders(id), um ADD COLUMN comum ainda é concluído com ALGORITHM=INSTANT em milissegundos, chaves estrangeiras incluídas. Mas duas operações falham de imediato, independentemente do algoritmo: Excluir a chave primária da tabela pai da qual a chave estrangeira depende:
Alterar o tipo da coluna filha independentemente da coluna pai que ela referencia:
Ambos os erros acontecem no momento da análise e da validação, antes de o MySQL escolher um algoritmo. Se uma migração precisa alterar uma coluna envolvida em uma chave estrangeira, a chave estrangeira precisa ser excluída e recriada como parte da mesma migração, e os tipos das colunas pai e filha precisam permanecer compatíveis em ambos os lados a cada etapa.

gh-ost para Alterações que o DDL Online Não Consegue Fazer com Segurança

O caso MODIFY COLUMN ... ALGORITHM=COPY acima é aquele em que o DDL online não pode ajudar: ele precisa da reescrita completa das linhas, mas você não quer manter um bloqueio durante todo o processo em uma tabela ativa. O gh-ost lida com isso construindo uma cópia da tabela (uma tabela “fantasma”) com o novo esquema e, em seguida, copiando as linhas existentes para ela em segundo plano, enquanto transmite o log binário, em vez de usar gatilhos, para reproduzir todas as gravações feitas na tabela original durante a cópia. Ele termina com uma breve renomeação atômica para fazer a troca. O gh-ost se recusa a migrar uma tabela que outra tabela referencia com uma chave estrangeira: a tabela order_items da seção anterior tem uma FOREIGN KEY (order_id) REFERENCES orders(id), e executar o gh-ost contra orders com essa restrição ainda em vigor falha imediatamente com FATAL found 1 parent-side foreign keys on ... orders. Parent-side foreign keys are not supported. Bailing out. Exclua essa chave estrangeira primeiro (ou passe --discard-foreign-keys, que a exclui para você e não a recria) antes de executar o gh-ost contra uma tabela que outras tabelas referenciam. Ele foi instalado e executado, com a chave estrangeira de order_items excluída, contra a mesma tabela de 3.276.802 linhas, exatamente para esse tipo de alteração:
Com uma senha real, passe-a por meio de um arquivo -conf em vez de --password na linha de comando — uma senha na linha de comando fica visível a qualquer outro usuário do host por meio do ps e acaba no histórico do shell. Sem --execute, o gh-ost é executado em modo de simulação: ele se conecta, transmite o binlog e imprime a instrução CREATE TABLE que usaria para a tabela fantasma, mas não faz nenhuma alteração. Com --execute, ele executou a migração completa em 13,112 segundos, imprimindo o progresso ao longo do caminho:
Três segundos depois do início de uma segunda execução da mesma migração, um INSERT concorrente na tabela orders ativa retornou em 0,007 segundo, e a própria linha de progresso do gh-ost confirmou que ele havia capturado a gravação e a reproduzido (Applied: 1):
Depois da troca, o SHOW CREATE TABLE orders mostrou customer_id como bigint, a contagem de linhas correspondeu exatamente (3.276.803, a contagem original mais a única linha inserida durante a migração), e a linha inserida no meio da migração estava presente com o seu valor correto. O gh-ost renomeou a tabela original para _orders_del em vez de excluí-la, portanto a tabela anterior à migração ainda está lá para comparação, ou para exclusão manual depois que você confirmar a troca. Isso foi executado em um servidor autônomo com binlog_format=ROW e log_bin=ON já definidos, que o gh-ost exige para transmitir as alterações; ele não funciona com replicação baseada em instruções. Ele também exige que a tabela tenha uma chave primária ou uma chave única. Nenhum dos dois precisou de configuração extra aqui, porque são padrões comuns, mas confirme ambos antes de confiar no gh-ost em uma tabela que você não verificou.

Perguntas Frequentes

Como sei de antemão se um ALTER TABLE vai bloquear a minha tabela?

Adicione ALGORITHM=INPLACE, LOCK=NONE (ou apenas LOCK=NONE) à instrução antes de executá-la. Se o MySQL conseguir fazer a alteração sem bloquear, ela é executada. Se não conseguir, ele retorna um erro imediatamente, em vez de executar e bloquear. De qualquer forma, você obtém uma resposta definitiva sem adivinhar nem ler o código-fonte.

Por que o ALGORITHM=INSTANT falhou para uma alteração de tipo de coluna?

O INSTANT só funciona quando o MySQL consegue expressar a alteração apenas como uma atualização de metadados. Alterar o tipo armazenado de uma coluna significa que a representação em disco de cada linha existente está errada para o novo tipo, o que exige ler e reescrever as linhas. Isso descarta o INSTANT e, especificamente para uma alteração de tipo, também descarta o INPLACE; somente o COPY consegue fazê-lo.

O gh-ost precisa de replicação configurada?

Não. Ele lê o log binário do servidor que está migrando, que qualquer servidor com binlog_format=ROW já produz, independentemente de ter réplicas. O gh-ost também permite executar contra uma réplica e fazer a troca no servidor principal, que é o padrão mais seguro para tabelas muito grandes ou muito ativas, mas isso é uma escolha de implantação, não um requisito para que ele funcione.

O que acontece com a tabela antiga depois que o gh-ost termina?

Por padrão, o gh-ost renomeia a tabela original com um sufixo (_del na execução capturada acima), em vez de excluí-la. Ela permanece no esquema, sem uso, até que você mesmo a exclua.

Solução de Problemas

Veja também