Início / dba / Perguntas / 226445
Accepted
clarkk
Asked: 2019-01-07 07:02:29 +0800 CST

A consulta é extremamente lenta após ALTER e implantação de atualizações

  • 772

Eu corro um servidor de teste e um servidor de produção.

Quando faço algumas alterações na estrutura do db no servidor de teste eu salvo todas as consultas SQL para depois executá-las no servidor de produção quando eu implantar as atualizações

Hoje eu implantei algumas atualizações no servidor de produção, mas pelo menos uma consulta agora está tão lenta que o tempo limite é esgotado (leva vários minutos).. Costumava levar de 1 a 2 segundos

No servidor de teste, a mesma consulta ainda funciona sem problemas, como costumava

consulta

SELECT dv.id,dv.client_id,b.name block_name,dv.is_ocr_pending,dv.time,dv.label,dv.is_pdf_broken,dv.is_pdf_scan,dv.is_pic,dv.file_sha1,dv.file_ext,dv.file_ext_thumb,dv.file_size,dv.file_size_original,dv.num_pages,dv.dpi,dv.ocr_confidence,dv.ocr_recall_id,dv.ocr_vatno,cv.name ocr_vatno_name,dv.ocr_vatno_country,dv.ocr_is_vatno_verified,dv.ocr_invoice_id_,dv.ocr_invoice_time,dv.ocr_invoice_time_due,dv.ocr_fi_type,dv.ocr_fi_payment_id_,dv.ocr_is_fi_payment_verified,dv.ocr_fi_creditorno,dv.ocr_bank_code,dv.ocr_bank_code_id,dv.ocr_is_bank_code_verified,dv.ocr_bank_account,dv.ocr_is_bank_account_verified,dv.ocr_bank_iban,dv.ocr_is_bank_iban_verified,dv.ocr_bank_swift,dv.ocr_bank_swift_id,dv.ocr_is_bank_swift_verified,dv.ocr_products_pattern,dv.ocr_total,dv.ocr_is_total_verified,dv.ocr_vat,dv.ocr_currency
FROM `data_voucher` dv
LEFT JOIN `block` b ON b.id=dv.block_id
LEFT JOIN `cache_vatno` cv ON cv.vatno=dv.ocr_vatno
ORDER BY dv.time DESC,dv.id DESC
LIMIT 0,25

Não faz sentido que o tempo limite em um servidor, mas não no outro .. Quando eu despejo as estruturas da tabela, a consulta afeta os despejos são exatamente os mesmos?

Se eu excluir a linha com LEFT JOIN cache_vatno cv ON cv.vatno=dv.ocr_vatnoe remover o campo cv.name ocr_vatno_nameda cláusula select, ele será executado e concluído como antes da implantação

Eu não sei o que fazer?

Agora usei o extrabackup do perconas para despejar o banco de dados de produção e implantá-lo no servidor de teste. Agora o servidor de teste tem os mesmos problemas?

Eu posso navegar na tabela cache_vatnono phpmyadmin sem problemas

A tabela data_vouchertem mais de 100 mil linhas

Tentei reiniciar o mysql

Adicionando EXPLAINà consulta, dê isso

insira a descrição da imagem aqui

mysql

1 respostas

  1. Best Answer

    10 vezes mais lento? E se você rodasse novamente, voltaria ao normal?

    Isso é normal. Antes do big ALTER, todos os dados dessa consulta eram armazenados em cache na RAM. Em seguida, a ALTERmaior parte bateu para fora. Depois disso, a consulta teve que ir para o disco, portanto, 10x mais lenta. Executar novamente deve vê-lo no cache e ser rápido novamente.

    Se isso não se encaixar com o que você está vendo, forneça o seguinte para que possamos aprofundar:

    • SHOW CREATE TABLEpara cada mesa
    • O tamanho de cada mesa
    • A pergunta
    • Valor deinnodb_buffer_pool_size
    • O ALTER
    • Os JOINs são 1:1? Nesse caso, provavelmente há uma reescrita significativa para acelerá-lo, mesmo os 1-2 segundos.

    Observe que a consulta deve ler todas as linhas de todas as 3 tabelas e colocar todos os dados em uma tabela temporária para classificação.

    Você não parece precisar LEFT, você pode removê-lo?

    Índices possíveis (pendente de ver as informações acima):

    block -- already has PRIMARY KEY(id)?
cache_vatno:  INDEX(vatno)
data_voucher:  INDEX(time, id)

    Se as junções são 1:1? Supondo que sejam, experimente esta reescrita:

    SELECT  SQL_CALC_FOUND_ROWS dv.id,dv.client_id,
        ( SELECT b.name FROM block b WHERE b.id=dv.block_id ) AS block_name,
        dv.is_ocr_pending,dv.time,dv.label,dv.is_pdf_broken,dv.is_pdf_scan,
        dv.is_pic,dv.file_sha1,dv.file_ext,dv.file_ext_thumb,
        dv.file_size,dv.file_size_original,dv.num_pages,dv.dpi,
        dv.ocr_confidence,dv.ocr_recall_id,dv.ocr_vatno,
        ( SELECT cv.name FROM `cache_vatno` cv
              WHERE cv.vatno=dv.ocr_vatno ) AS ocr_vatno_name,
        dv.ocr_vatno_country,dv.ocr_is_vatno_verified,dv.ocr_invoice_id_,
        dv.ocr_invoice_time,dv.ocr_invoice_time_due,dv.ocr_fi_type,
        dv.ocr_fi_payment_id_,dv.ocr_is_fi_payment_verified, dv.ocr_fi_creditorno,
        dv.ocr_bank_code,dv.ocr_bank_code_id,dv.ocr_is_bank_code_verified,
        dv.ocr_bank_account,dv.ocr_is_bank_account_verified,dv.ocr_bank_iban,
        dv.ocr_is_bank_iban_verified,dv.ocr_bank_swift,dv.ocr_bank_swift_id,
        dv.ocr_is_bank_swift_verified,dv.ocr_products_pattern,
        dv.ocr_total,dv.ocr_is_total_verified,dv.ocr_vat,dv.ocr_currency
    FROM  `data_voucher` dv
    ORDER BY  dv.time DESC,dv.id DESC
    LIMIT  0,25

    Justificativa: Transformei os JOINs em subconsultas, tornando óbvio que as coisas são 1:1 e deixando o Otimizador focar apenas em uma tabela ( dv). Isso deve permitir que ele use INDEX(time, id)o ORDER BYe o LIMIT, parando assim após 25 linhas.

    Infelizmente, isso ainda deixa o processamento de arquivos SQL_CALC_FOUND_ROWS. Ele deve continuar a contar o restante das linhas nesse índice. Portanto, remover o CALC pode acelerar significativamente a consulta. A interface do usuário realmente precisa disso?

    ...

    • 1

relate perguntas