Limpar a base de dados do PrestaShop: o guia SQL

Porque é que uma base inchada sai cara

A base de dados do PrestaShop de uma loja ativa cresce continuamente, e a maior parte desse volume não tem qualquer valor para o negócio. As consequências vêem-se depressa: back-office lento, cópias de segurança que ultrapassam a quota do alojamento, reposição que demora horas justamente no dia em que é preciso ser rápido, e exportação impossível pela interface de administração da base.

A limpeza é uma das intervenções com melhor relação ganho/esforço. Desde que se saiba o que se está a apagar.

Identificar o que pesa

Antes de mais, meça. Esta consulta ordena as tabelas por tamanho:

SELECT table_name,
       ROUND((data_length + index_length) / 1024 / 1024, 1) AS taille_mo,
       table_rows
FROM information_schema.TABLES
WHERE table_schema = DATABASE()
ORDER BY (data_length + index_length) DESC
LIMIT 25;

Na grande maioria dos casos, a lista é dominada pelos mesmos culpados:

• ps_connections, ps_connections_source, ps_connections_page, ps_guest: o registo da navegação dos visitantes;
• ps_page_viewed, ps_statssearch: as estatísticas nativas;
• ps_search_index e ps_search_word: o índice da pesquisa interna;
• ps_cart e ps_cart_product: os carrinhos, cuja esmagadora maioria nunca deu origem a uma encomenda;
• ps_log, ps_mail, ps_customer_thread: registos, historial de e-mails e mensagens, muitas vezes cheios de spam.

Antes de apagar seja o que for

Faça uma cópia de segurança completa e confirme que ela se repõe. Uma limpeza é irreversível.

Trabalhe primeiro em SELECT COUNT(*). Para cada eliminação que pondere, conte antes as linhas afetadas. Um DELETE lançado com uma condição mal escrita não tem volta.

Coloque a loja em modo de manutenção nas limpezas grandes, para evitar bloqueios em tabelas que estão a ser escritas.

Apague por lotes. Um DELETE sobre milhões de linhas pode saturar o registo de transações. Acrescente uma cláusula LIMIT 50000 e repita.

As limpezas seguras

As estatísticas de navegação. Só têm valor se usar mesmo as estatísticas nativas do PrestaShop, o que é raro quando há uma ferramenta de análise externa instalada.

DELETE FROM ps_connections_page WHERE id_connections IN (
  SELECT id_connections FROM ps_connections WHERE date_add < DATE_SUB(NOW(), INTERVAL 6 MONTH)
) LIMIT 50000;

DELETE FROM ps_connections_source WHERE date_add < DATE_SUB(NOW(), INTERVAL 6 MONTH) LIMIT 50000;
DELETE FROM ps_connections WHERE date_add < DATE_SUB(NOW(), INTERVAL 6 MONTH) LIMIT 50000;

Respeite esta ordem: primeiro as tabelas filhas, depois a tabela-mãe.

Os carrinhos sem encomenda. Conte antes de apagar:

SELECT COUNT(*) FROM ps_cart c
LEFT JOIN ps_orders o ON o.id_cart = c.id_cart
WHERE o.id_order IS NULL AND c.date_add < DATE_SUB(NOW(), INTERVAL 6 MONTH);

Depois apague as linhas de carrinho associadas antes dos próprios carrinhos. Atenção: se usa os lembretes de carrinho abandonado, mantenha uma janela mais larga, de seis meses no mínimo.

Os registos da aplicação.

DELETE FROM ps_log WHERE date_add < DATE_SUB(NOW(), INTERVAL 3 MONTH) LIMIT 50000;

O historial dos e-mails. A tabela ps_mail só serve para consultar o que foi enviado. Um ano de retenção é mais do que suficiente.

O que nunca se deve apagar

As encomendas, faturas e notas de crédito. Existe uma obrigação legal de conservação. Uma loja a que se apagou a ps_orders para poupar espaço não é recuperável.

Os clientes associados a uma encomenda. Mesmo os «inativos». A eliminação parte o historial e as faturas.

As tabelas de estatísticas, se algum módulo depender delas. Alguns módulos de atribuição ou de painel de controlo leem a ps_connections. Confirme antes, sob pena de esvaziar os relatórios desses módulos.

As tabelas de configuração, seja qual for o tamanho aparente.

Reindexar e reorganizar

Depois de uma limpeza, o espaço não é devolvido enquanto as tabelas não forem reorganizadas:

OPTIMIZE TABLE ps_connections, ps_connections_page, ps_cart, ps_log;

A seguir, regenere o índice de pesquisa em Parâmetros da loja > Pesquisa > Reindexar toda a base de dados. Num catálogo grande, a interface ultrapassa o tempo de execução permitido: use então o URL da tarefa agendada dedicada, ou processe por lotes.

Por fim, confirme que nenhuma tabela ficou em MyISAM depois de migrações antigas:

SELECT table_name, engine FROM information_schema.TABLES
WHERE table_schema = DATABASE() AND engine <> 'InnoDB';

O InnoDB gere os bloqueios ao nível da linha e não da tabela: a conversão elimina bloqueios invisíveis, mas bem reais, numa loja ativa.

Transformar isto numa rotina

Uma limpeza anual não serve de grande coisa: seis meses depois a base está outra vez saturada. Implemente uma limpeza automática mensal nas tabelas de estatísticas e de registos, com uma retenção decidida de uma vez por todas.

Se o seu back-office continuar lento depois da limpeza, o problema está noutro sítio: veja o nosso artigo sobre como acelerar uma loja PrestaShop.

Auditar a sua base de dados

Medimos, limpamos e implementamos a rotina de limpeza, sem nunca mexer nos dados contabilísticos. Veja a nossa oferta de manutenção PrestaShop.