Início / dba / Perguntas / 60203
Accepted
LOSTinDB
Asked: 2014-03-06 07:46:57 +0800 CST

Alguém pode explicar por que a junção de duas visualizações no mysql é tão lenta?

  • 772

Aqui está uma pergunta que fiz ontem - https://stackoverflow.com/questions/22180727/left-joining-two-views-is-slow .

Recebi uma boa resposta que me ajudou, mas não entendo por que o LEFT JOIN é muito mais lento que a pesquisa. O LEFT JOIN durou 16 segundos - e tenho certeza de que minhas tabelas estão pelo menos 90% otimizadas - e ao fazer a pesquisa, são apenas 0,14 segundos. Quando eu LEFT JOIN tabelas, não é tão lento, então por que visualizações?

mysql join

3 respostas

  1. Best Answer

    De acordo com a documentação do MySQL em visualizações

    Visualizações (incluindo visualizações atualizáveis) estão disponíveis no MySQL Server 5.6. Visualizações são consultas armazenadas que, quando invocadas, produzem um conjunto de resultados. Uma visão atua como uma tabela virtual.

    A primeira coisa que deve ser percebida sobre uma visão é que ela produz um conjunto de resultados. O conjunto de resultados que emerge da consulta invocada da exibição é uma tabela virtual porque é criada sob demanda. Não há DDL que você possa convocar posteriormente para indexar imediatamente o conjunto de resultados. Para todos os efeitos, o conjunto de resultados é uma tabela sem nenhum índice. Na verdade, o LEFT JOIN que você estava executando é basicamente um produto cartesiano com alguma filtragem.

    Para dar uma visão mais granular do JOIN de duas visualizações, vou me referir a um post que fiz no ano passado explicando os mecanismos internos que o MySQL usa para avaliar JOINs e WHEREs ( Existe uma diferença de execução entre uma condição JOIN e uma condição WHERE? ). Mostrarei o mecanismo conforme publicado em Compreendendo o MySQL Internals (Página 172):

    • Determine quais chaves podem ser usadas para recuperar os registros das tabelas e escolha a melhor para cada tabela.
    • Para cada tabela, decida se uma varredura de tabela é melhor do que a leitura de uma chave. Se houver muitos registros que correspondem ao valor da chave, as vantagens da chave são reduzidas e a verificação da tabela torna-se mais rápida.
    • Determine a ordem na qual as tabelas devem ser unidas quando mais de uma tabela estiver presente na consulta.
    • Reescreva as cláusulas WHERE para eliminar o código morto, reduzindo os cálculos desnecessários e alterando as restrições sempre que possível para abrir o caminho para o uso de chaves.
    • Elimine tabelas não utilizadas da junção.
    • Determine se as chaves podem ser usadas para ORDER BYe GROUP BY.
    • Tente simplificar as subconsultas, bem como determinar até que ponto seus resultados podem ser armazenados em cache.
    • Mesclar exibições (expandir a referência de exibição como uma macro)

    OK, parece que os índices devem ser usados. No entanto, olhe mais de perto. Se você substituir a palavra Viewpor Table, veja o que acontece com a execução do mecanismo:

    MECANISMO MODIFICADO

    • Determine quais chaves podem ser usadas para recuperar os registros viewse escolha a melhor para cada uma view.
    • Para cada view, decida se uma viewvarredura é melhor do que a leitura de uma chave. Se houver muitos registros que correspondam ao valor da chave, as vantagens da chave serão reduzidas e a viewvarredura se tornará mais rápida.
    • Determine a ordem em que viewsdeve ser unido quando mais de um viewsestiver presente na consulta.
    • Reescreva as cláusulas WHERE para eliminar o código morto, reduzindo os cálculos desnecessários e alterando as restrições sempre que possível para abrir o caminho para o uso de chaves.
    • Elimine os não utilizados viewsda junção.
    • Determine se as chaves podem ser usadas para ORDER BYe GROUP BY.
    • Tente simplificar as subconsultas, bem como determinar até que ponto seus resultados podem ser armazenados em cache.
    • Mesclar exibições (expandir a referência de exibição como uma macro)

    Cada tabela (visão) não possui índice. Assim, trabalhar com tabelas virtuais, tabelas temporárias ou tabelas sem índices torna-se realmente indistinto ao fazer um JOIN. As chaves usadas são apenas para operações JOIN, não tanto para procurar coisas mais rapidamente.

    Pense em sua consulta como pegar duas listas telefônicas, as Páginas Amarelas de 2014 e as Páginas Amarelas de 2013. Cada livro das Páginas Amarelas contém as Páginas Brancas para Números de Telefone Residenciais.

    • No final de 2012, uma tabela de banco de dados foi usada para gerar as Páginas Amarelas de 2013.
    • Durante 2013
      • As pessoas mudaram de número de telefone
      • As pessoas receberam novos números de telefone
      • As pessoas largaram os números de telefone, mudando para o celular
    • No final de 2013, uma tabela de banco de dados foi usada para gerar as páginas amarelas de 2014.

    Obviamente, existem diferenças entre as duas listas telefónicas. Fazer um JOIN de tabelas de banco de dados para descobrir as diferenças entre 2013 e 2014 não deve representar nenhum problema.

    Imagine fundir as duas listas telefônicas manualmente para localizar as diferenças. Parece insano, não é? Não obstante, isso é exatamente o que você está pedindo ao mysqld para fazer quando você junta duas views. Lembre-se, você não está juntando tabelas reais e não há índices para pegar carona.

    Agora, vamos olhar para trás na consulta real.

    SELECT DISTINCT
viewA.TRID, 
viewA.hits,
viewA.department,
viewA.admin,
viewA.publisher,
viewA.employee,
viewA.logincount,
viewA.registrationdate,
viewA.firstlogin,
viewA.lastlogin,
viewA.`month`,
viewA.`year`,
viewA.businesscategory,
viewA.mail,
viewA.givenname,
viewA.sn,
viewA.departmentnumber,
viewA.sa_title,
viewA.title,
viewA.supemail,
viewA.regionname
FROM
viewA
LEFT JOIN viewB ON viewA.TRID = viewB.TRID
WHERE viewB.TRID IS NULL

    Você está usando uma tabela virtual (tabela sem índices), viewA, unindo-a a outra tabela virtual, viewB. A tabela temporária sendo gerada intermitentemente seria tão grande quanto viewA. Em seguida, você executa uma classificação interna na grande tabela temporária para torná-la distinta.

    EPÍLOGO

    Dados os mecanismos internos de avaliação de JOINs, juntamente com a natureza transitória e sem índice do conjunto de resultados de uma exibição, sua consulta original (LEFT JOIN de duas exibições) deve obter tempos de execução que são ordens de magnitude. Ao mesmo tempo, a resposta que você obteve do StackOverflow deve ter um bom desempenho, dado o mesmo algoritmo JOIN que acabei de descrever.

    Espero que os detalhes sangrentos que acabei de postar respondam à sua pergunta sobre o porquê.

    • 11

  2. EXPLAIN EXTENDED [select query]e, em seguida SHOW WARNINGS, mostrará a forma reescrita da exibição. A partir daqui, é mais fácil analisar as características de desempenho.

    As consultas de verificação visual geralmente não são fáceis de otimizar.

    • 1

  3. A resposta tem a ver com o método de realização de cada uma dessas operações.

    Como as exibições são inerentemente não indexadas, as operações JOIN usando campos de exibições levarão mais tempo do que as operações JOIN usando tabelas, pois a varredura não pode usar um índice.

    Nesse caso, a pesquisa também limita o número de registros que devem ser retornados no processamento - ela apenas extrai registros de uma visualização que não existem na outra. O JOIN extrai todos os registros e verifica se existem registros em ambos.

    • -2

relate perguntas