MySQL - caminho mais rápido para ALTER TABLE for InnoDB

Uma maneira segura de acelerar um ALTER TABLE é remover índices desnecessários

Aqui estão os passos iniciais para carregar uma nova versão da tabela

CREATE TABLE s_relations_new LIKE s_relations;
#
# Drop Duplicate Indexes
#
ALTER TABLE s_relations_new
    DROP INDEX source_persona_index,
    DROP INDEX target_persona_index,
    DROP INDEX target_persona_relation_type_index
;

Por favor observe o seguinte:

• Eu larguei source_persona_index porque é a primeira coluna em 4 outros índices

  • unique_target_persona
  • unique_target_object
  • source_and_target_object_index
  • source_target_persona_index

• Eu larguei target_persona_index porque é a primeira coluna em 2 outros índices

  • target_persona_relation_type_index
  • target_persona_relation_type_message_id_index

• Eu larguei target_persona_relation_type_index porque as 2 primeiras colunas também estão em target_persona_relation_type_message_id_index

OK Isso cuida de índices desnecessários. Existem índices com baixa cardinalidade? Aqui está a maneira de determinar isso:

Execute as seguintes consultas:

SELECT COUNT(DISTINCT sent_at)               FROM s_relations;
SELECT COUNT(DISTINCT message_id)            FROM s_relations;
SELECT COUNT(DISTINCT target_object_id)      FROM s_relations;

De acordo com sua pergunta, existem cerca de 80.000.000 de linhas. Como regra geral, o MySQL Query Optimizer não usará um índice se a cardinalidade das colunas selecionadas for maior que 5% da contagem de linhas da tabela. Nesse caso, seriam 4.000.000.

• Se COUNT(DISTINCT sent_at)> 4.000.000
  • entãoALTER TABLE s_relations_new DROP INDEX sent_at_index;
• Se COUNT(DISTINCT message_id)> 4.000.000
  • entãoALTER TABLE s_relations_new DROP INDEX message_id_index;
• Se COUNT(DISTINCT target_object_id)> 4.000.000
  • entãoALTER TABLE s_relations_new DROP INDEX target_object_index;

Uma vez determinada a utilidade ou inutilidade desses índices, você pode recarregar os dados

#
# Change the Column Name
# Load the Table
#
ALTER TABLE s_relations_new CHANGE sent_at sent_at_new int(11) DEFAULT NULL;
INSERT INTO s_relations_new SELECT * FROM s_relations;

É isso, certo? NÃO !!!

Se seu site esteve ativo todo esse tempo, pode haver INSERTs sendo executados em s_relations durante o carregamento de s_relations_new. Como você pode recuperar essas linhas ausentes?

Vá encontrar o id máximo em s_relations_new e anexe tudo depois desse ID de s_relations. Para garantir que a tabela seja congelada e usada apenas para esta atualização, você deve ter um pouco de tempo de inatividade para obter as últimas linhas que foram inseridas em s_relation_new. Aqui está o que você faz:

No sistema operacional, reinicie o mysql para que ninguém mais possa fazer login, exceto root@localhost (desativa o TCP/IP):

$ service mysql restart --skip-networking

Em seguida, faça login no mysql e carregue essas últimas linhas:

mysql> SELECT MAX(id) INTO @maxidnew FROM s_relations_new;
mysql> INSERT INTO s_relations_new SELECT * FROM s_relations WHERE id > @maxidnew;
mysql> ALTER TABLE s_relations RENAME s_relations_old;
mysql> ALTER TABLE s_relations_new RENAME s_relations;

Em seguida, reinicie o mysql normalmente

$ service mysql restart

Agora, se você não pode derrubar o mysql, você terá que fazer um bait-and-switch em s_relations. Basta acessar o mysql e fazer o seguinte:

mysql> ALTER TABLE s_relations RENAME s_relations_old;
mysql> SELECT MAX(id) INTO @maxidnew FROM s_relations_new;
mysql> INSERT INTO s_relations_new SELECT * FROM s_relations_old WHERE id > @maxidnew;
mysql> ALTER TABLE s_relations_new RENAME s_relations;

De uma chance !!!

CAVEAT : Quando estiver satisfeito com esta operação, você pode descartar a tabela antiga assim que possível:

mysql> DROP TABLE s_relations_old;

A resposta correta depende da versão do mecanismo MySQL que você está usando.

Se estiver usando 5.6+, as renomeações e a adição/remoção de índices são realizadas online , ou seja, sem copiar todos os dados da tabela.

Basta usar ALTER TABLEcomo de costume, será principalmente instantâneo para renomeações e quedas de índice e razoavelmente rápido para adição de índice (tão rápido quanto ler toda a tabela uma vez).

Se estiver usando 5.1+ e o plug-in InnoDB estiver ativado, a adição/remoção de índices também estará online. Não tenho certeza sobre renomeações.

Se estiver usando uma versão mais antiga, ALTER TABLEainda é a mais rápida, mas provavelmente será terrivelmente lenta porque todos os seus dados serão reinseridos em uma tabela temporária sob o capô.

Finalmente, hora de desmascarar os mitos. Infelizmente não tenho carma suficiente aqui para comentar as respostas, mas sinto que é importante corrigir a resposta mais votada. Isso está errado :

Como regra geral, o MySQL Query Optimizer não usará um índice se a cardinalidade das colunas selecionadas for maior que 5% da contagem de linhas da tabela

Na verdade é o contrário .

Os índices são úteis para selecionar poucas linhas, por isso é importante que tenham alta cardinalidade, o que significa muitos valores distintos e estatisticamente poucas linhas com o mesmo valor.

Tive o mesmo problema com o Maria DB 10.1.12, então após ler a documentação descobri que existe a opção de realizar a operação "in-place" que elimina a cópia da tabela. Com esta opção a alter table é muito rápida. No meu caso foi:

alter table user add column (resettoken varchar(256),
  resettoken_date date, resettoken_count int), algorithm=inplace;

isso é muito rápido. Sem a opção de algoritmo, nunca terminaria.

https://mariadb.com/kb/en/mariadb/alter-table/

Para renomear a coluna,

ALTER TABLE tablename CHANGE columnname newcolumnname datatype;

deve estar bem e não levar qualquer tempo de inatividade.

Para os índices, a instrução CREATE INDEX bloqueará a tabela. Se for um escravo não utilizado como você mencionou, isso não é um problema.

Uma outra opção seria criar uma nova tabela que tenha os nomes e índices de coluna apropriados. Então você pode copiar todos os dados para ele, então executar uma série de

BEGIN TRAN;
ALTER TABLE RENAME tablename tablenameold;
ALTER TABLE RENAME newtablename tablename;
DROP TABLE tablenameold;
COMMIT TRAN;

Isso minimizaria o tempo de inatividade ao custo de usar temporariamente o dobro do espaço.

Eu também tenho esse problema e usei este SQL:

/*on créé la table COPY SANS les nouveaux champs et SANS les FKs */
CREATE TABLE IF NOT EXISTS prestations_copy LIKE prestations;

/* on supprime les FKs de la table actuelle */
ALTER TABLE `prestations`
DROP FOREIGN KEY `fk_prestations_pres_promos`,
DROP FOREIGN KEY `fk_prestations_activites`;

/* on remet les FKs sur la table copy */
ALTER TABLE prestations_copy 
    ADD CONSTRAINT `fk_prestations_activites` FOREIGN KEY (`act_id`) REFERENCES `activites` (`id`) ON UPDATE NO ACTION ON DELETE NO ACTION,
    ADD CONSTRAINT `fk_prestations_pres_promos` FOREIGN KEY (`presp_id`) REFERENCES `pres_promos` (`id`) ON UPDATE NO ACTION ON DELETE NO ACTION;

/* On fait le transfert des données de la table actuelle vers la copy, ATTENTION: il faut le même nombre de colonnes */
INSERT INTO prestations_copy
SELECT * FROM prestations;

/* On modifie notre table copy de la façon que l'on souhaite */
ALTER TABLE `prestations_copy`
    ADD COLUMN `seo_mot_clef` VARCHAR(50) NULL;

/* on supprime la table actuelle et renome la copy avec le bon nom de table */
SET FOREIGN_KEY_CHECKS=0;
DROP TABLE prestations;
RENAME TABLE prestations_copy TO prestations;
SET FOREIGN_KEY_CHECKS=1;   

Espero que possa ajudar alguém

Cumprimentos,

Vai