Início / dba / Perguntas / 73087
Accepted
ursitesion
Asked: 2014-08-05 00:22:47 +0800 CST

MySQL: Qual junção é melhor entre a junção externa esquerda e a junção interna

  • 772

Qual junção tem melhor desempenho se todas elas fornecerem o mesmo resultado? Por exemplo, eu tenho duas tabelas employees(emp_id,name, address, designation, age, sex)e work_log(emp_id,date,hours_wored). Para obter alguns resultados específicos tanto inner joine left joindá o mesmo resultado. Mas, ainda tenho algumas dúvidas que não se limitam apenas a esta questão.

  • qual junção é mais eficiente, qual deve preferir no caso de valores de resultados iguais?
  • Quais são os outros fatores que devem ser considerados no momento da aplicação da associação?
  • Existe alguma relação entre junção interna e junção cruzada?
mysql performance

3 respostas

  1. Best Answer

    Não existe um tipo de junção "melhor" ou "pior". Eles têm significados diferentes e devem ser usados ​​dependendo dele.

    No seu caso, você provavelmente não tem funcionários sem work_log(nenhuma linha nessa tabela), então LEFT JOINe JOINserão equivalentes nos resultados. No entanto, se você tivesse uma coisa dessas (um novo funcionário sem registro work_log), JOINomitiria esse funcionário, enquanto uma junção à esquerda (cuja primeira tabela é funcionários) mostraria todos eles e nulos nos campos de work_logse não houver fósforos.

    Explicação visual dos tipos de JOIN
    Imagem de CL Moffatt no Code Project

    Novamente, o desempenho é uma coisa secundária para a correção da consulta. Algumas pessoas dizem que você não deve usar LEFT JOINs. É verdade que a LEFT JOINforça o otimizador a executar a consulta em uma determinada ordem, impedindo algumas otimizações (reordenação de tabelas) em alguns casos. Aqui está um exemplo . Mas você não deve escolher um sobre o outro se a correção/significado for sacrificado, pois INNER JOINnão é inerentemente pior. O restante das otimizações usuais se aplica normalmente.

    Em resumo, não use LEFT JOINse você realmente quer dizer INNER JOIN.

    No MySQL CROSS JOIN, INNER JOINe JOINsão os mesmos. No padrão, e semanticamente, a CROSS JOINé um INNER JOINsem ONcláusula, então você obtém todas as combinações de linhas entre as tabelas.

    Você tem exemplos de todos os tipos semânticos de junção na Wikipedia . Na prática, no MySQL, tendemos a escrever apenas JOINe LEFT JOIN.

    • 23

  2. Qual junção tem melhor desempenho se todas elas fornecerem o mesmo resultado?

    Além da resposta anterior, pelo que sei, o MySQL é otimizado para ter o mesmo desempenho.

    Com bons índices, por exemplo, cláusula JOINvs LEFT JOIN+ WHEREpara filtrar, será a mesma coisa. Otimização versus leitura humana faz sentido em consultas grandes com muitas junções.

    Usar bons índices e cache é mais importante.

    Você pode ler uma boa explicação do processo de otimização aqui:

    O Processo de Otimização de Consulta : Uma consulta geralmente pode ser executada de muitas maneiras diferentes e produzir o mesmo resultado. O trabalho do otimizador é encontrar a melhor opção.

    • 2

  3. Não é o mesmo para mim, MySql 5.7 8 vCPU, 52 GB de RAM

    A consulta a seguir leva cerca de 30 segundos, não sei por que

    A tabela de transações possui 24.257.151 registros

    A tabela de atividades possui 18.603.665 registros

    A tabela de compras possui 13.911.705 registros

    Todos os índices necessários estão em vigor

    SELECT
    `trx`.`transaction_pk`,
    `trx`.`created`,
    `trx`.`updated`,
    `p`.`amount`,
    `trxst`.`name`,
    COALESCE ( a.units, 0 ) AS units
FROM
    `transaction` AS `trx`
    INNER JOIN `transaction_sub_type` AS `trxst` ON  `trx`.`transaction_sub_type_fk`= `trxst`.`transaction_sub_type_pk`
    left JOIN `activity` AS `a` ON `a`.`transaction_fk` = `trx`.`transaction_pk`
    LEFT JOIN `purchases` AS `p` ON `p`.`transaction_fk` = `trx`.`transaction_pk` 
WHERE
    `trx`.`entity_fk` IN ( 1234) 
    AND `trx`.`transaction_sub_type_fk` IN (
    2, 4, 5, 15, 16, 33, 37, 38, 85, 86, 87, 88, 102, 103 
    ) 
ORDER BY
    `trx`.`transaction_pk` DESC LIMIT 100 OFFSET 0;

    Após substituir a seguinte linha:

    INNER JOIN `transaction_sub_type` AS `trxst` ON  `trx`.`transaction_sub_type_fk`= `trxst`.`transaction_sub_type_pk`

    com LEFT JOIN

    LEFT JOIN `transaction_sub_type` AS `trxst` ON  `trx`.`transaction_sub_type_fk`= `trxst`.`transaction_sub_type_pk`

    a mesma consulta leva ~0,046s

    Explique antes:

    1   SIMPLE  trxst       ALL PRIMARY             101 37.62   Using where; Using temporary; Using filesort
1   SIMPLE  trx     ref transaction_sub_type_fk,entity_fk   transaction_sub_type_fk 4   trxst.transaction_sub_type_pk   2548    0.36    Using where
1   SIMPLE  a       ref transaction_fk  transaction_fk  4   trx.transaction_pk  1   100 
1   SIMPLE  p       ref transaction_fk  transaction_fk  4   trx.transaction_pk  1   100

    Explique depois:

    1   SIMPLE  trx     ref transaction_sub_type_fk,entity_fk   entity_fk   4   const   81474   83.65   Using where
1   SIMPLE  trxst       eq_ref  PRIMARY PRIMARY 4   trx.transaction_sub_type_fk 1   100 
1   SIMPLE  a       ref transaction_fk  transaction_fk  4   trx.transaction_pk  1   100 
1   SIMPLE  p       ref transaction_fk  transaction_fk  4   trx.transaction_pk  1   100
    • 1

relate perguntas