如何使用row\u number()高效地获取子查询或联接表的行索引

5vf7fwbs 于 2021-07-26 发布在 Java

关注(0)|答案(1)|浏览(497)

我有一个“游戏”表，其中一些游戏（但不是全部）按活动id分组为“活动”。
我正在尝试编写一个sql查询，它将获得一个包含有关游戏的各种信息的数据集，但特别是：如果给定的游戏是某个活动的一部分，那么该活动中总共有多少个游戏（我有这个功能）以及该活动中该游戏的索引（例如，活动中最早的游戏是索引“1”，下一个是“2”，以此类推）。
我已经做到了这一点，但执行计划看起来很糟糕，显而易见的解决办法不起作用，但我超越了自己。
以下是工作查询，删除了一些无关的内容：

g1.`id` AS `game_id`,
        (SELECT
            COUNT(*)
            FROM `games` g3
            WHERE g3.`campaign` = g1.`campaign`
        ) AS `campaign_length`,
        ca2.`ri` AS `campaign_index`,
        ca1.`id` AS `campaign_id`, ca1.`name` AS `campaign_name`
    FROM `games` g1
    LEFT JOIN `campaigns` ca1 ON ca1.`id` = g1.`campaign`
    LEFT JOIN (
        SELECT 
            g4.`id` AS `id`,
            ROW_NUMBER() OVER (
                PARTITION BY g4.`campaign`
                ORDER BY g4.`start` ASC) AS `ri`
            FROM `games` g4
        ) AS ca2 ON ca2.`id` = g1.`id`
    WHERE g1.`end` > CURRENT_TIMESTAMP()
    AND g1.`gamemaster` = 25
    ORDER BY g1.`start` ASC
    ;

这个版本的问题是，对于表g4，执行计划列出了一个完整的表扫描-这在目前是好的，因为只有几百条记录，但长期的性能将是可怕的，特别是因为这个查询（或非常类似的查询）将在我的网站的许多不同的页面上执行。我认为这是因为row\u number（）函数需要对所有行进行编号，然后left join的on语句才能将它们筛选为我真正需要的行。
显而易见的解决办法是补充一句，虽然我试过，但没有用 WHERE g4.campaign= g1.campaign之后 `FROM `games` g4` ; 这样，row_number（）只需要对数据集中有可能返回的记录进行编号。但是这不起作用，因为 `g1.`campaign 不在范围内。
我能做到 WHERE g4.campaignIS NOT NULL 它至少可以将执行计划降到一个索引条件，而不是一个完整的表扫描，但是它仍然不能很好地扩展，因为活动中的游戏数量会随着时间的增长而增长。
我知道我的“显而易见的解决方案”不会因为范围问题而起作用，但是有没有人建议我如何在没有糟糕的执行计划的情况下实现我想要做的事情？

sql subquery mariadb sql-execution-plan row-number

来源：https://stackoverflow.com/questions/67285003/how-to-efficiently-get-the-row-indicies-of-a-subquery-or-joined-table-using-row

1条答案

按热度按时间

exdqitrt1#

根据你的评论 campaign_index 必须在 WHERE 条款适用。这意味着 campaign_index 将始终需要一个完整的表扫描，因为 WHERE 子句不能减少正在计算的行。
但是，您可以使用窗口函数，而不是自联接和相关子查询。。。

WITH
  games AS
(
  SELECT
    *,
    COUNT(*)
      OVER (
        PARTITION BY `campaign`
      )
        AS `campaign_length`,
    ROW_NUMBER()
      OVER (
        PARTITION BY `campaign`
            ORDER BY `start`
      )
        AS `campaign_index`
    FROM
        games
)
SELECT
    games.*,
    campaigns.`name`   AS `campaign_name`
FROM
  games
LEFT JOIN
  campaigns
    ON campaigns.`id` = games.`campaign`
WHERE
      games.`end`        > CURRENT_TIMESTAMP()
  AND games.`gamemaster` = 25
ORDER BY
  games.`start`
;

赞(0）回复(0）举报 2021-07-26

我来回答

如何使用row\u number()高效地获取子查询或联接表的行索引

1条答案

相关问题

热门标签

最新问答