VillageSQL is a drop-in replacement for MySQL with extensions.
All examples on this page work on VillageSQL. Install Now →
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 tabelaorders carregada com 3.276.802 linhas (168,7 MB de dados) em um servidor VillageSQL local.
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 comALGORITHM=INPLACE, LOCK=NONE na tabela de 3,27 milhões de linhas acima:
SHOW PROCESSLIST em uma segunda conexão o mostrou como uma operação real, em andamento:
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:
ALTER acima ainda estava no estado altering table, um INSERT e um SELECT na mesma tabela foram ambos concluídos imediatamente:
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 comALGORITHM=INSTANT altera somente os metadados da tabela; nunca lê nem reescreve uma única linha:
ALGORITHM=INPLACE em vez disso:
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 todoALTER 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:
ALGORITHM=INSTANT para a mesma alteração falha da mesma forma, com um motivo diferente:
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:
INSERT concorrente, emitido 0,3 segundo depois do início do ALTER, só retornou quando o ALTER estava quase concluído:
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 deALGORITHM 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:
gh-ost para Alterações que o DDL Online Não Consegue Fazer com Segurança
O casoMODIFY 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:
-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:
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):
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?
AdicioneALGORITHM=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?
OINSTANT 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 combinlog_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, ogh-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
- Migrações de Esquema no MySQL — organizando e versionando os arquivos de migração que produzem as instruções
ALTER TABLEque este guia executa - Entendendo os Índices do MySQL — como o índice que o MySQL constrói durante um
ADD INDEXonline afeta o desempenho das consultas depois - Lendo a Saída do EXPLAIN no MySQL — verificando se um índice recém-adicionado está realmente sendo usado depois que a migração é concluída