Mam podstawowe pytanie o wydajność MySQL związane z wyjaśnieniem. Mam dwa zapytania, które zwracają ten sam wynik i staram się zrozumieć, jak zrozumieć sensEXPLAIN planów wykonania.

Tabela zawiera 50000 rekordów i wykonuję porównanie rekordów. Moje pierwsze zapytanie trwa 18.625 sekund. Plan wyjaśnienia jest następujący.

id  select_type table   type    possible_keys                   key         key_len ref                                 rows    filtered    Extra
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
1   SIMPLE      a       ALL     NULL                            NULL        NULL    NULL                                49520   100.00  
1   SIMPLE      b       ref     scoreEvent,eventScore           eventScore  4       olympics.a.eventId                  413     100.00      Using where; Using index; Not exists
1   SIMPLE      c       ref     PRIMARY,scoreEvent,eventScore   scoreEvent  8       olympics.a.score,olympics.a.eventId 4       100.00      Using where; Using index; Not exists

Moje następne zapytanie trwa 0,106 s do uruchomienia ...

id  select_type table       type    possible_keys   key     key_len     ref     rows    filtered    Extra
-----------------------------------------------------------------------------------------------------------------------------------
1   PRIMARY     <derived2>  ALL     NULL            NULL    NULL        NULL    50000   100.00      Using temporary; Using filesort
2   DERIVED     results     ALL     NULL            NULL    NULL        NULL    49520   100.00      Using filesort

W dokumentacji to mówiALL wymaga pełnego skanowania tabeli i to jest bardzo złe. To też mówifilesort wymaga dodatkowej przepustki, aby posortować rekordyNot exists oznacza, że ​​MySQL był w stanie wykonaćLEFT JOIN optymalizacja. Jest również jasne, że pierwsza metoda używa indeksów, podczas gdy druga metoda nie.

Próbuję ustalić, co tu się dzieje i w co zaangażowana jest matematyka. BiegamRESET QUERY CACHE między testami, aby upewnić się, że nie ma żadnej nieuczciwej przewagi. 49520 x 413 x 4 jest dużo mniejszy niż 50000 x 49520.

Czy ma to związek zid w planie wyjaśnienia?

Kiedy testuję te i inne zapytania, wydaje mi się, że moje spostrzeżenia są takie, że złożoność zapytania może być przybliżona przez pomnożenie elementów o tym samym identyfikatorze i dodanie wyniku każdego identyfikatora razem ... Czy to poprawne założenie?

Dodatkowy

Zgodnie z wymaganiami w komentarzach schemat i zapytania na wszelki wypadek, ale nie szukam lepszych zapytań ... Tylko wyjaśnienieEXPLAIN. Stół, o którym mowa ...

CREATE TABLE results (
  resultId INT NOT NULL auto_increment KEY, 
  athleteId INT NOT NULL,
  eventId INT NOT NULL,
  score INT NOT NULL,
  CONSTRAINT FOREIGN KEY (athleteId) REFERENCES athletes(athleteId),
  CONSTRAINT FOREIGN KEY (eventId) REFERENCES events(eventId),
  INDEX eventScore (eventId, score),
  INDEX scoreEvent (score, eventId)
) ENGINE=innodb;

Pierwsze zapytanie ...

SELECT a.resultId, a.eventId, a.athleteId, a.score
FROM results a 

-- Find records with matching eventIds and greater scores
LEFT JOIN results b 
ON b.eventId = a.eventId 
AND b.score > a.score

-- Find records with matching scores and lesser testIds
LEFT JOIN results c
ON c.eventId = a.eventId
AND c.score = a.score
AND c.resultId < a.resultId

-- Filter out all records where there were joins
WHERE c.resultId IS NULL 
AND b.resultId IS NULL;

Drugie zapytanie ...

SELECT resultId, athleteId, eventId, score
FROM (
  SELECT resultId, athleteId, eventId, score
  FROM results
  ORDER BY eventId, score DESC, resultId
) AS a
GROUP BY eventId;

Zauważyłem też, że jeśli upuszczę indekseventScore że kwerenda spada do 2,531 s, a plan wykonania nie zmienia się tak bardzo, ale kolejność możliwych_kategorii zmienia się i nie jestUsing index na stółb (ignoruj ​​niewielkie zmiany liczby wierszy generuję dane za każdym razem, gdy zmieniam schemat) ...

id  select_type table   type    possible_keys               key         key_len ref                                 rows    filtered    Extra
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
1   SIMPLE      a       ALL     NULL                        NULL        NULL    NULL                                47457   100.00  
1   SIMPLE      b       ref     eventId,scoreEvent          eventId     4       olympics.a.eventId                  659     100.00      Using where; Not exists
1   SIMPLE      c       ref     PRIMARY,eventId,scoreEvent  scoreEvent  8       olympics.a.score,olympics.a.eventId 5       100.00      Using where; Using index; Not exists