MySQL 构造查询

Question

我一直在尝试构造一个庞大的查询，并且我已经成功并且能够真正完成查询。但是，我从开发环境(小型数据库)转到实时环境(大型数据库)进行测试，但遇到了性能问题。

我认为可以在这里找到答案:https://dba.stackexchange.com/a/16376

但真的没有别的办法吗？我什至将子查询放在 VIEW 中的原因是因为它们具有更复杂的结构。

View /查询示例:

pjl View :

    (SELECT `pj`.`id` AS `id`,`pj`.`globalId` AS `globalId`,`pj`.`date` AS `date`,`pj`.`serverId` AS `serverId`,`pj`.`playerId` AS `playerId`,'playerjoins' AS `origin`
    FROM `playerjoins` `pj`) 
    UNION ALL 
    (SELECT `pl`.`id` AS `id`,`pl`.`globalId` AS `globalId`,`pl`.`date` AS `date`,`pl`.`serverId` AS `serverId`,`pl`.`playerId` AS `playerId`,'playerleaves' AS `origin`
    FROM `playerleaves` `pl`)

ll_below View :

    SELECT `ll`.`id` AS `id`,`ll`.`globalId` AS `globalId`,`ll`.`date` AS `date`,`ll`.`serverId` AS `serverId`,`ll`.`gamemodeId` AS `gamemodeId`,`ll`.`mapId` AS `mapId`,`pjl`.`origin` AS `origin`,`pjl`.`date` AS `pjldate`,`pjl`.`playerId` AS `playerId`
    FROM `pjl`
    JOIN `levelsloaded` `ll` 
    ON `pjl`.`date` <= `ll`.`date`

现在简单的查询:

    SELECT * FROM
    (
            (SELECT * FROM ll_below WHERE playerId = 976) llbelow
            INNER JOIN
            (SELECT id, MAX(pjldate) AS maxdate FROM ll_below WHERE playerId = 976 GROUP BY id) llbelow_inner
            ON llbelow.id = llbelow_inner.id AND llbelow.pjldate = llbelow_inner.maxdate
    )
    WHERE origin = 'playerjoins'
    ORDER BY date DESC

我可以将所有内容放在一个大查询中，但在我看来，它会变得一团糟。

我也知道为什么性能受到如此严重的影响，因为 MySQL 不能对 pjl View 使用 MERGE 算法，因为有一个 UNION ALL 在里面。如果我将 WHERE playerId = 976 子句放在正确的位置，那么性能不会受到影响，但我还会有一个由 50 行或其他内容组成的查询。

如果我想要性能和仍然简洁的查询，有人可以建议我该怎么做吗？

Answer 1

本条款:

WHERE origin = 'playerjoins'

意味着您根本不需要执行 UNION，因为在查询结束时您没有使用 pl 中的任何行。

您是对的， View 可能会强制使用临时表而不是使用合并算法。

UNION ALL 也创建了自己的临时表。根据 Bug #50674 Do not create temporary tables for UNION ALL，这种情况在 MySQL 5.7.3(在撰写本文时仍为 pre-alpha)中进行了优化。 .

此外，GROUP BY 可能正在创建第三级临时表。

我看到您还在执行 greatest-n-per-group 操作，以将行与每个 ID 的最大日期相匹配。这种类型的操作有不同的解决方案，它们不使用子查询。例如，请参阅我的答案:

• Retrieving the last record in each group
• Fetch the row which has the Max value for a column

根据行数和其他条件，我发现针对每组最多 n 个查询的两种解决方案都提供了更好的性能。因此，您应该测试这两种解决方案，看看在给定数据的状态和大小的情况下，哪一种更好。

我认为您应该解开 View 、联合和子查询。在进行连接和聚合之前，看看您是否可以直接对基表应用各种 WHERE 条件(如 playerId=976)。这样应该可以大大减少检查的行数，避免view和union and group by造成的多层临时表。

---

回复你的评论:

您似乎想要的查询是某个特定玩家每个级别的最近加入。

像这样:

SELECT ll.id, 
  ll.globalId,
  ll.date AS leveldate,
  ll.serverId,
  ll.gamemodeId,
  ll.mapId,
  pj.date AS joindate,
  pj.playerId
FROM levelsloaded AS ll
INNER JOIN playerjoins AS pj
  ON pj.date <= ll.date
LEFT OUTER JOIN playerjoins AS pj2
  ON pj.playerId = pj2.playerId AND pj2.date <= ll.date AND pj.date < pj2.date 
WHERE pj.playerId = 976
  AND pj2.playerID IS NULL
ORDER BY joindate DESC

(我没有测试过这个查询，但它应该能让你入门。)