Início / dba / Perguntas / 1497
Accepted
Derek Downey
Asked: 2011-03-01 12:01:05 +0800 CST

Como melhorar o desempenho do InnoDB DELETE?

  • 772

Portanto, tenho esta tabela de auditoria (rastreia ações em qualquer tabela do meu banco de dados):

CREATE TABLE `track_table` (
  `id` int(16) unsigned NOT NULL,
  `userID` smallint(16) unsigned NOT NULL,
  `tableName` varchar(255) NOT NULL DEFAULT '',
  `tupleID` int(16) unsigned NOT NULL,
  `date_insert` datetime NOT NULL,
  `action` char(12) NOT NULL DEFAULT '',
  `className` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `userID` (`userID`),
  KEY `tableID` (`tableName`,`tupleID`,`date_insert`),
  KEY `actionDate` (`action`,`date_insert`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

e preciso começar a arquivar itens desatualizados. A tabela cresceu para cerca de 50 milhões de linhas, portanto, a maneira mais rápida de excluir as linhas era excluí-la uma tabela por vez (com base em tableName).

Isso funciona muito bem, mas em algumas tabelas com muita gravação, não será concluído. Minha consulta exclui todos os itens que possuem uma deleteação associada em uma combinação tupleID/tableName:

DELETE FROM track_table WHERE tableName='someTable' AND tupleID IN (
  SELECT DISTINCT tupleID FROM track_table
  WHERE tableName='someTable' AND action='DELETE' AND date_insert < DATE_SUB(CURDATE(), INTERVAL 30 day)
)

Deixei isso rodar no meu servidor por 3 dias e nunca foi concluído para a maior tabela. A saída de explicação (se eu alternar a exclusão para selecionar:

| id | select_type        | table       | type | possible_keys      | key     | key_len | ref        | rows    | Extra                        |
|  1 | PRIMARY            | track_table | ref  | tableID            | tableID | 257     | const      | 3941832 | Using where                  |
|  2 | DEPENDENT SUBQUERY | track_table | ref  | tableID,actionDate | tableID | 261     | const,func |       1 | Using where; Using temporary |

Portanto, 4 milhões de linhas não devem levar 3 dias para serem excluídas, eu acho. Eu tenho meu innodb_buffer_pool_size definido como 3 GB e o servidor não está definido para usar one_file_per_table. De que outras maneiras posso melhorar o desempenho de exclusão do InnoDB? (Executando MySQL 5.1.43 no Mac OSX)

mysql performance

4 respostas

  1. Best Answer

    Você pode excluir dados em lotes.

    No SQL Server, a sintaxe são delete top Xlinhas de uma tabela. Você então faz isso em um loop, com uma transação para cada lote (se você tiver mais de um extrato, é claro), para manter as transações curtas e manter os bloqueios apenas por períodos curtos.

    Na sintaxe do MySQL:DELETE FROM userTable LIMIT 1000

    Existem restrições quanto a isso (não pode ser usado LIMITem exclusões com junções, por exemplo), mas neste caso você pode fazer dessa maneira.

    Há um perigo adicional em usar LIMITwith DELETEquando se trata de replicação; as linhas excluídas às vezes não são excluídas na mesma ordem no escravo em que foram excluídas no mestre.

    • 11

  2. Tente usar uma abordagem de tabela temporária. Tente algo assim:

    Passo 1)CREATE TABLE track_table_new LIKE track_table;

    Passo 2)INSERT INTO track_table_new SELECT * FROM track_table WHERE action='DELETE' AND date_insert >= DATE_SUB(CURDATE(), INTERVAL 30 day);

    Etapa 3)ALTER TABLE track_table RENAME track_table_old;

    Passo 4)ALTER TABLE track_table_new RENAME track_table;

    Passo 5)DROP TABLE track_table_old;

    Não incluí o campo tupla na Etapa 2. Veja se isso produz o efeito desejado. Se isso é o que você deseja, você pode descartar completamente o campo de tupla, a menos que use o campo de tupla por outros motivos.

    • 6

  3. A exclusão de linhas indesejadas no lote deve manter outras operações viáveis. Mas a exclusão da operação tem condições, portanto, certifique-se de que haja um índice apropriado nas colunas sobre as condições.

    Como o MySQL não suporta a função completa de varredura de índice solta, você pode tentar ajustar a sequência KEY actionDate (action, date_insert)para KEY actionDate (date_insert, action). Com o prefixo 'date_insert', o MySQL deve usar este índice para verificar as linhas anteriores à sua condição de data e hora.

    Com esse índice, você pode escrever SQL como:

    DELETE
FROM track_table
WHERE tableName='someTable'
    AND action='DELETE'
    AND date_insert < DATE_SUB(CURDATE(), INTERVAL 30 day)
LIMIT 1000 -- Your size of batch
    • 1 
  4. | id | select_type        | table       | type | possible_keys      | key     | key_len | ref        | rows    | Extra                        |
|  1 | PRIMARY            | track_table | ref  | tableID            | tableID | 257     | const      | 3941832 | Using where                  |
|  2 | DEPENDENT SUBQUERY | track_table | ref  | tableID,actionDate | tableID | 261     | const,func |       1 | Using where; Using temporary |

    -Fist, de sua explicação o key_len tão grande => você precisa diminuir o tamanho o menor possível. Para sua consulta, acho que a melhor maneira é alterar o tipo de dados do campo de ação de char(12) para tinyint, para que o mapeamento de dados fique assim:

    1: -> DELETE
2: -> UPDATE
3: -> INSERT
...

    e você pode alterar table_id em vez de tablename também. o DDL para o melhor desempenho pode:

    CREATE TABLE `track_table` (
  `id` int(11) unsigned NOT NULL,
  `userID` smallint(6) unsigned NOT NULL,
  `tableid` smallint(6) UNSIGNED NOT NULL DEFAULT 0,
  `tupleID` int(11) unsigned NOT NULL,
  `date_insert` datetime NOT NULL,
  `actionid` tinyin(4) UNSIGNED NOT NULL DEFAULT 0,
  `className` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `userID` (`userID`),
  KEY `tableID` (`tableid`,`tupleID`,`date_insert`),
  KEY `actionDate` (`actionid`,`date_insert`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `actions` (
  `id` tinyint(4) unsigned NOT NULL 
  `actionname` varchar(255) NOT NULL,
  PRIMARY KEY (`id`) 
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `table_name` (
  `id` tinyint(4) unsigned NOT NULL 
  `tablename` varchar(255) NOT NULL,
  PRIMARY KEY (`id`) 
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

    para que a consulta possa ser executada como:

    DELETE FROM track_table WHERE tableid=@tblid AND tupleID IN (
  SELECT DISTINCT tupleID FROM track_table
  WHERE tableid=@tblid AND actionid=@actionid AND date_insert < DATE_SUB(CURDATE(), INTERVAL 30 day)
).

    Mas a maneira mais rápida era usar a partição. para que você possa descartar a partição. Atualmente, minha tabela tem mais de 40mil linhas. e atualizo a cada hora (atualização de 400 mil linhas para cada vez), e posso descartar a partição curr_date e recarregar os dados na tabela. o comando drop muito rápido (<100ms). Espero que isso ajude.

    • 1

relate perguntas