Объясните MySQL объяснить математику плана выполнения, разницу между двумя планами

У меня есть основной вопрос производительности MySQL, связанный с объяснением. У меня есть два запроса, которые возвращают один и тот же результат, и я пытаюсь понять, как понятьEXPLAIN из планов выполнения.

В таблице содержится 50000 записей, и я выполняю сравнение записей. Мой первый запрос занимает 18,625 секунд. План объяснения заключается в следующем.

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

Мой следующий запрос занимает 0,106 секунды для запуска ...

id  select_type table       type    possible_keys   key     key_len     ref     rows    filtered    Extra
-----------------------------------------------------------------------------------------------------------------------------------
1   PRIMARY       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

В документации сказано, чтоALL требует полного сканирования таблицы, и это очень плохо. Это также говорит о том, чтоfilesort требуется дополнительный проход для сортировки записей, он также говорит, чтоNot exists означает, что MySQL был в состоянии сделатьLEFT JOIN оптимизация. Также ясно, что первый метод использует индексы, тогда как второй метод неt.I»

Я пытаюсь понять, что здесь происходит и какая математика участвует. я бегуRESET QUERY CACHE между тестами, чтобы убедиться, что нетдается какое-либо несправедливое преимущество. 49520 x 413 x 4 намного меньше, чем 50000 x 49520.

Это связано сid в плане объяснения?

Когда я'Когда я тестирую эти и другие запросы, мне кажется, что мои наблюдения заключаются в том, что сложность запроса может быть аппроксимирована умножением элементов с одинаковым идентификатором и сложением результата каждого идентификатора вместе ... Это допустимое предположение?

дополнительный

Как просили в комментариях схема и запросы на всякий случай, если это помогает, но я не ищу лучшие запросы ... Просто объяснениеEXPLAIN, Стол, о котором идет речь ...

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;

Первый запрос ...

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;

Второй запрос ...

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

Я также заметил, что если я опущу индексeventScore что запрос падает до 2,531 с, а план выполнения нетак много изменений, но меняется порядок возможных_ключей, и этонеUsing index для столаb (игнорируйте небольшие изменения в количестве строк, которые я генерирую, каждый раз, когда меняю схему) ...

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

Ответы на вопрос(2)

Ваш ответ на вопрос