php - MySQL best/first 分差查询优化

Question

谁能帮我优化这个查询？我有下表:

cdu_user_progress:
--------------------------------------------------------------
|id    |uid     |lesson_id    |game_id    |date    |score    |
--------------------------------------------------------------

对于每个用户，我试图获得特定 lesson_id 的特定 game_id 的最佳分数和第一分数之间的差异，并根据该差异(我的查询中的“进度”)对结果进行排序:

SELECT ms.uid AS id, ms.max_score - fs.first_score AS progress
FROM (
    SELECT up.uid, MAX(CASE WHEN game_id = 3 THEN score ELSE NULL END) AS max_score
    FROM cdu_user_progress up
    WHERE  (up.uid IN  ('1671', '1672', '1673', '1674', '1675', '1676', '1679', '1716', '1725',         '1726', '1937', '1964', '1996', '2062', '2065', '2066', '2085', '2086')) AND (up.lesson_id = '65') AND (up.score > '-1')
GROUP BY up.uid
) ms
LEFT JOIN (
    SELECT up.uid, up.score AS first_score 
    FROM cdu_user_progress up
    INNER JOIN (
        SELECT up.uid, MIN(CASE WHEN game_id = 3 THEN date ELSE NULL END) AS first_date
        FROM cdu_user_progress up
        WHERE  (up.uid IN  ('1671', '1672', '1673', '1674', '1675', '1676', '1679', '1716', '1725', '1726', '1937', '1964', '1996', '2062', '2065', '2066', '2085', '2086')) AND (up.lesson_id = '65') AND (up.score > '-1') 
        GROUP BY up.uid
    ) fd ON fd.uid = up.uid AND fd.first_date = up.date
) fs ON fs.uid = ms.uid
ORDER BY progress DESC

如有任何帮助，我们将不胜感激!

Answer 1

缺少任何 EXPLAIN 输出或索引定义，我们无法提出任何建议。 (我在评论中指出，如果我们不能保证 (uid,date) 中的 cdu_user_progress 元组的唯一性，那么看起来有些连接谓词丢失了……我们有可能获得用于不同的 lesson_id 或不大于 '-1' 的分数。

在查询文本中，紧接在 ) fs 之前 ，我会添加

        AND up.lesson_id = '65'
        AND up.score > '-1'
      GROUP BY up.uid

我还会包装 up.score聚合函数中的列(在 fd View 的 SELECT 列表中)，MIN()或 MAX() , 为了符合 ANSI 标准(即使当 SQL_MODE 不包括 ONLY_FULL_GROUP_BY 时 MySQL 不需要它)

---

如果我没有定义合适的索引，我会考虑添加一个索引:

... ON cdu_user_progress (lesson_id, uid, score, game_id, date)

派生表(具体化内联 View )有一些开销，并且这些派生表不会在它们上面有索引(在 MySQL 5.5 和更早版本中。)但是 GROUP BY在每个内联 View 中确保我们的行数少于 20，因此这不会成为问题。

因此，如果存在性能问题，它会出现在 View 查询中。同样，我们确实需要查看 EXPLAIN 的输出以及索引定义和一些基数估计，以便提出建议。

---

跟进

鉴于 (uid,date) 没有唯一约束，我会在 fs 中添加这些谓词查看查询。我还会在查询中使用唯一的表别名(对于每个对 cdu_user_progress 的引用)以使语句和 EXPLAIN 输出更易于阅读。此外，添加 GROUP BY fd 中的子句和聚合函数查看...我会这样写查询:

SELECT ms.uid AS id
     , ms.max_score - fs.first_score AS progress
  FROM ( SELECT up.uid
              , MAX(CASE WHEN up.game_id = 3 THEN up.score ELSE NULL END) AS max_score
           FROM cdu_user_progress up
          WHERE up.uid IN ('1671','1672','1673','1674','1675','1676','1679','1716','1725','1726','1937','1964','1996','2062','2065','2066','2085','2086')
            AND up.lesson_id = '65'
            AND up.score > '-1'
          GROUP BY up.uid
       ) ms
  LEFT
  JOIN ( SELECT uo.uid
              , MIN(uo.score) AS first_score
           FROM ( SELECT un.uid
                       , MIN(CASE WHEN un.game_id = 3 THEN un.date ELSE NULL END) AS first_date
                    FROM cdu_user_progress un
                   WHERE un.uid IN ('1671','1672','1673','1674','1675','1676','1679','1716','1725','1726','1937','1964','1996','2062','2065','2066','2085','2086')
                     AND un.lesson_id = '65' 
                     AND un.score > '-1' 
                   GROUP BY un.uid
                ) fd
           JOIN cdu_user_progress uo
             ON uo.uid = fd.uid
            AND uo.date = fd.first_date
            AND uo.lesson_id = '65'
            AND uo.score > '-1'
          GROUP BY uo.uid
       ) fs
    ON fs.uid = ms.uid
 ORDER BY progress DESC

而且我相信这将使我在上面推荐的索引适用于所有对 cdu_user_progress 的引用。 .