MySQL: Które sprzężenie jest lepsze między lewym złączeniem zewnętrznym a złączeniem wewnętrznym

Które złączenie jest lepsze, jeśli wszystkie dają taki sam wynik? Na przykład mam dwie tabele employees(emp_id,name, address, designation, age, sex)i work_log(emp_id,date,hours_wored). Aby uzyskać określone wyniki zarówno inner joini left joindaje ten sam wynik. Ale wciąż mam wątpliwości, które nie ograniczają się tylko do tego pytania.

• które łączenie jest bardziej wydajne, które powinno preferować w przypadku takich samych wartości wyników?
• Jakie inne czynniki należy wziąć pod uwagę w momencie składania wniosku o przyłączenie?
• Czy istnieje związek między złączeniem wewnętrznym a łączeniem krzyżowym?

Nie ma „lepszego” ani „gorszego” typu łączenia. Mają różne znaczenie i muszą być używane w zależności od tego.

W twoim przypadku prawdopodobnie nie masz pracowników bez logu pracy (brak wierszy w tej tabeli), więc LEFT JOINi JOINbędą one równoważne w wynikach. Jednakże, jeśli miałeś coś takiego (nowy pracownik bez zarejestrowanego logu_dziennika), JOINwold pomija tego pracownika, podczas gdy lewe dołączenie (którego pierwszą tabelą są pracownicy) pokazywałoby ich wszystkich, a pola o wartościach zerowych na polach z logu_logowania_produktu, jeśli istnieją nie są dopasowaniami.

Ponownie, wydajność jest kwestią drugorzędną dla poprawności zapytań. Niektórzy mówią, że nie powinieneś używać LEFT JOINs. Prawdą jest, że LEFT JOIN zmusza optymalizator do wykonania zapytania w jednej określonej kolejności, co w niektórych przypadkach zapobiega niektórym optymalizacjom (zmianie kolejności tabel). Oto jeden przykład . Ale nie powinieneś wybierać jednego z drugiego, jeśli poprawność / znaczenie jest poświęcone, ponieważ WEJŚCIE WEWNĘTRZNE nie jest z natury gorsze. Pozostałe zwykłe optymalizacje obowiązują jak zwykle.

Podsumowując, nie używaj, LEFT JOINjeśli naprawdę masz na myśli INNER JOIN.

W MySQL CROSS JOIN, INNER JOINi JOINsą takie same. Standardowo i semantycznie a CROSS JOINjest klauzulą INNER JOINbez ON, więc otrzymujesz każdą kombinację wierszy między tabelami.

Masz przykłady wszystkich semantycznych typów łączenia na Wikipedii . W praktyce w MySQL zwykle piszemy JOINi LEFT JOIN.

Które złączenie jest lepsze, jeśli wszystkie dają taki sam wynik?

Dodam poprzednią odpowiedź, z tego co wiem, MySQL jest zoptymalizowany pod kątem takiej samej wydajności.

Przy dobrych indeksach, na przykład, klauzula JOINvs LEFT JOIN+ WHEREdo filtrowania, będzie to samo. Optymalizacja kontra czytanie ludzkie ma sens w przypadku dużych zapytań z dużą liczbą złączeń.

Korzystanie z dobrych indeksów i pamięci podręcznej jest ważniejsze.

Dobre objaśnienie procesu optymalizacji można przeczytać tutaj:

Proces optymalizacji zapytania : Zapytanie często można wykonać na wiele różnych sposobów i uzyskać ten sam wynik. Zadaniem optymalizatora jest znalezienie najlepszej opcji.

Dla mnie to nie to samo, MySql 5.7 8 vCPU, 52 GB RAM

Poniższe zapytanie zajmuje ~ 30 sekund, nie jestem pewien, dlaczego

Tabela transakcji zawiera 24 257 151 rekordów

Tabela aktywności zawiera 18 603 665 rekordów

Tabela zakupów zawiera 13 911 705 rekordów

Wszystkie wymagane indeksy są na miejscu

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;

Po zastąpieniu następującego wiersza:

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

z LEFT JOIN

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

to samo zapytanie zajmuje ~ 0,046s

Wyjaśnij przed:

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 

Wyjaśnij po:

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