Início / dba / Perguntas / 334094
Accepted
Rohan
Asked: 2023-12-15 20:19:47 +0800 CST

Como adicionar índice a uma tabela grande com 60 milhões de registros sem tempo de inatividade?

  • 772

temos lutado com um problema nos últimos dias. Queremos adicionar um índice a uma tabela enorme com 60 milhões de registros. No início, tentamos adicioná-lo com a sintaxe básica do MySQL. Mas isso obstruiu nosso banco de dados de produção. Essa tabela é usada com muita frequência em consultas de produção. Então tudo sofreu.

Nosso banco de dados está hospedado no AWS RDS. É Mysql 5.7. Estamos usando Laravel como nosso framework PHP

A próxima coisa que lemos foi que podemos copiar a tabela atual para uma nova. Em seguida, adicione o índice à nova tabela. Em seguida, mude o modelo laravel para usar a nova tabela. Achamos que fazia sentido e que seria bastante fácil

Mas copiar os dados da tabela de uma tabela para a nova demorava muito tempo. Nossos cálculos mostraram que levaria dias. Tentamos usar Laravel e também comandos SQL. Mas foi muito lento de qualquer maneira.

Depois tentamos exportar os dados como CSV e importá-los, mas, novamente, muito lento. Os primeiros milhões de registros seriam inseridos rapidamente, mas a inserção da tabela se tornaria extremamente lenta.

Finalmente tentamos mysqldumpe percebemos que ele também bloqueia a nova tabela durante a inserção, talvez por isso seja rápido o suficiente. Demorou cerca de 6 horas para copiar a tabela para uma nova. MAS faltavam 2 milhões de registros neste método. Também verificamos quantos registros entraram na tabela existente durante a exportação/importação, eram apenas cerca de 100K. Portanto, faltavam 1,9 milhão de registros na exportação/importação e não conseguimos descobrir o porquê.

Depois de passar por todos esses caminhos diferentes, decidimos colocar o aplicativo em tempo de inatividade e adicionar o índice na enorme mesa

Eu queria saber se outras pessoas também enfrentam esse problema? Existe uma maneira de adicionar índices em uma tabela enorme sem causar tempo de inatividade na produção? Ou existe uma maneira mais rápida de copiar uma grande tabela MySQL sem perda de dados?

mysql

1 respostas

  1. Best Answer

    Para este exemplo, digamos que você tenha o seguinte no banco de dados mydb

    CREATE TABLE mytable (
    id INT NOT NULL AUTO_INCREMENT,
    num INT DEFAULT 0,
    dat VARCHAR(32),
    PRIMARY KEY (id)
);

    e você deseja criar dois índices da seguinte maneira:

    ALTER TABLE mytable
    ADD INDEX num_ndx (ndx)
   ,ADD INDEX dat_ndx (dat)
;

    Existem dois métodos que você realmente precisa examinar.

    MÉTODO #1: Use DDL Online

    Você pode iniciar sua alteração com ALTER TABLE usando a seguinte sintaxe

    ALTER TABLE mytable
    ADD INDEX num_ndx (ndx)
   ,ADD INDEX dat_ndx (dat)
   ,ALGORITHM=INPLACE,LOCK=NONE
;

    Como você está adicionando um índice e não alterando nenhum tipo de dados em nenhuma coluna, os dados das linhas serão verificados para gerar páginas de índice.

    MÉTODO # 2: Use pt-online-schema-change

    Esta ferramenta tem sido um salva-vidas para muitos ao longo dos anos

    Você precisa executar isso em duas fases

    FASE #1: Teste a seco

    Esta fase, que é basicamente uma verificação de sintaxe, leva menos de 5 segundos para ser executada

    MYSQL_HOST=...
MYSQL_USER=...
MYSQL_PASS=...
ALTER_TABLE_CLAUSE="ADD INDEX num_ndx (ndx),ADD INDEX dat_ndx (dat)"
RUNMODE"--dry-run"
PTOSC_OPTIONS="--alter-foreign-keys-method=auto"
PTOSC_OPTIONS="${PTOSC_OPTIONS} --print"
PTOSC_OPTIONS="${PTOSC_OPTIONS} --check-interval 10"
PTOSC_OPTIONS="${PTOSC_OPTIONS} --max-lag 300"
PTOSC_OPTIONS="${PTOSC_OPTIONS} ${RUNMODE}"
DB-mydb
TB=mytable

pt-online-schema-change --alter "${ALTER_TABLE_CLAUSE}" \ 
    h=${MYSQL_HOST},u=${MYSQL_USER},p=${MYSQL_PASS},D=${DB},t=${TB} \
    ${PTOSC_OPTIONS} >dryrun.log 2>&1

    FASE #2: Corrida ao Vivo

    MYSQL_HOST=...
MYSQL_USER=...
MYSQL_PASS=...
ALTER_TABLE_CLAUSE="ADD INDEX num_ndx (ndx),ADD INDEX dat_ndx (dat)"
RUNMODE"--execute"
PTOSC_OPTIONS="--alter-foreign-keys-method=auto"
PTOSC_OPTIONS="${PTOSC_OPTIONS} --print"
PTOSC_OPTIONS="${PTOSC_OPTIONS} --check-interval 10"
PTOSC_OPTIONS="${PTOSC_OPTIONS} --max-lag 300"
PTOSC_OPTIONS="${PTOSC_OPTIONS} ${RUNMODE}"
DB-mydb
TB=mytable

pt-online-schema-change --alter "${ALTER_TABLE_CLAUSE}" \ 
    h=${MYSQL_HOST},u=${MYSQL_USER},p=${MYSQL_PASS},D=${DB},t=${TB} \
    ${PTOSC_OPTIONS} >liverun.log 2>&1

    Aqui está o que pt-online-schema-change fará

    CREATE TABLE _mytable_new LIKE mytable;
ALTER TABLE _mytable_new ADD INDEX num_ndx (ndx),ADD INDEX dat_ndx (dat);

    pt-online-schema-change cria três gatilhos

    • INSERT AFTER Trigger de mytable para _mytable_new
    • UPDATE AFTER Trigger de mytable para _mytable_new
    • DELETE AFTER Trigger de mytable para _mytable_new

    Então, pt-online-schema-change copiará as linhas de mytable para _mytable_new. Se houver INSERTs, UPDATEs ou DELETEs durante a execução, os gatilhos preencherão essas alterações em _mytable_new

    Depois que cada linha for copiada para _mytable_new, pt-online-schema-change fará isso

    ANALYZE TABLE _mytable_new;
RENAME TABLE mytable TO _mytable_old,_mytable_new TO mytable;
DROP TABLE _mytable_old;

    Os três gatilhos são eliminados no final.

    Você pode ver essas etapas no arquivo dryrun.log

    Você pode ver o progresso em liverun.log

    NOTA: Execute o liverun em uma sessão de tela ou em um processo em segundo plano

    • 5

relate perguntas