mysql - 解释 MySQL 解释执行计划数学，两个计划之间的区别

Question

我有一个与解释相关的基本 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     <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

在文档中它说 ALL 需要全表扫描，这非常糟糕。它还说 filesort 需要额外的传递来对记录进行排序，它还说 Not exists 意味着 MySQL 能够执行 LEFT JOIN优化。很明显，第一种方法使用索引，而第二种方法没有。

我正在尝试弄清楚这里发生了什么以及涉及到什么数学。我在测试之间运行 RESET QUERY CACHE 以确保不会获得任何不公平的优势。 49520 x 413 x 4 比 50000 x 49520 小很多。

跟解释计划中的id有关吗？

当我测试这些和其他查询时，我的观察似乎是查询复杂性可以通过将具有相同 id 的项目相乘并将每个 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 秒，并且执行计划没有太大变化，但是 possible_keys 的顺序发生了变化，而不是 为表 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

Answer 1

事实上，当您看到时，您不应该乘法，而应该对这些数字求和。在你的例子中比较 (49520 x 413 x 4) 和 (50000 + 49520)。

一般规则很简单:汇总所有段(DERIVED、PRIMARY)并在每个段内乘以行。

id select_type  ... rows
1  PRIMARY           1
1  PRIMARY           2
2  DERIVED           3
2  DERIVED           4
3  DERIVED           5
3  DERIVED           6

复杂度为:1*2 + 3*4 + 5*6