主页 / dba / 问题 / 326150
Accepted
Elikill58
Asked: 2023-04-19 04:15:11 +0800 CST

如何解决 row_number 和多模式的性能问题？

  • 772

在收到如何获得有效排名计数器的答案后 ，我适应了我自己的系统。但是现在，我遇到了性能问题。我所有的请求都非常快（大多数请求不到 0.0005 秒），但是当使用ROW_NUMBER()多个模式时，它需要超过 0.2 秒。

这是一个完整的例子：

模式 1，名为sanctions，有一个名为的表bans，由以下内容组成：

  • id, 自增字段
  • uuid, 带有索引的 varchar
  • 其他内容与问题无关

该表实际有 400 多行。

模式 2，名为stats，有一个名为的表players，由以下内容组成：

  • id, 自增字段
  • uuid, 带有索引的 varchar
  • coins， 双倍的
  • 其他内容与问题无关

该表实际有 2000 多行。

我的完整查询是这样的：

SELECT
   uuid,
   (SELECT count(*) FROM sanctions.bans WHERE uuid = p.uuid) as nb,
   row_number() OVER (order by coins DESC) counter
FROM stats.players p;

大约需要 0.22 秒。

现在，让我们逐部分检查：

运行时SELECT count(*) FROM arkbans.litebans_bans WHERE uuid = p.uuid（并用p.uuid一个值替换），我从不超过 0.0002 秒。

运行时：

SELECT
   uuid,
   row_number() OVER (order by coins DESC) counter
FROM stats.players p;

大约需要 0.0017 秒。

用ANALYZE钥匙：

在此处输入图像描述

随着ANALYZE FORMAT=JSON：在这里

查询分析：

WITH Bans AS
(
    SELECT uuid, COUNT(*) AS BanCount
    FROM sanctions.bans
    GROUP BY uuid
)
 
SELECT
   p.uuid,
   COUNT(b.BanCount) as nb,
   row_number() OVER (order by MAX(p.coins) DESC) counter
FROM stats.players p
LEFT JOIN Bans b ON p.uuid = b.uuid

我该如何解决这个性能问题？

注意：这里的“硬币”栏是一个例子。实际上，超过 60 个列将使用此请求。所以像这样为每一列添加索引对我来说不是一个选项（索引太多，值太多）

注意 2：抱歉，不能做 db fiddle，因为它在多个数据库上并且有很多数据。

query-performance

1 个回答

  1. Best Answer

    运行时SELECT count(*) FROM arkbans.litebans_bans WHERE uuid = p.uuid（并用p.uuid一个值替换），我从不超过 0.0002 秒。

    是的，单次运行仅需 0.0002 秒，但您的表中有uuid多少行？如果将该计数乘以 0.0002 秒，那么现在的总运行时间是多长时间？当您的列表中有一个内联表达式对外表的给定行进行过滤时，这实际上就是您正在做的事情。（这不是完全相同的东西，但足够接近让你明白这个想法。）uuidstats.playersSELECT

    您应该以更具相关性的方式编写查询，使用实际的JOIN，如下所示：

    WITH Bans AS
(
    SELECT uuid, COUNT(1) AS BanCount
    FROM sanctions.bans
    GROUP BY uuid
)

SELECT
   p.uuid,
   IFNULL(b.BanCount, 0) as nb,
   row_number() OVER (order by p.coins DESC) counter
FROM stats.players p
LEFT JOIN Bans b
    ON p.uuid = b.uuid;

    通过直接连接然后对结果进行分组，您可能会发现这种重写的性能更高：

    SELECT
   p.uuid,
   COUNT(b.BanCount) as nb,
   row_number() OVER (order by MAX(p.coins) DESC) counter
FROM stats.players p
LEFT JOIN sanctions.bans b
    ON p.uuid = b.uuid
GROUP BY p.uuid;

    (uuid, coins)如评论中所述，表上的索引stats.players可能更适合您的查询类型。

    您ANALYZE FORMAT=JSON表明大部分时间都花在了sanctions.bans. 特别是这一行很有趣：

    “attached_condition”：“trigcond（stats.p.uuid = convert（b.uuid 使用 utf8mb4））”

    这convert(b.uuid using utf8mb4)向我表明您uuid在表中的列sanctions.bans与表中的列是不同的字符集stats.players。这称为隐式转换，可能会导致性能问题。直觉上我觉得这是你的瓶颈。请验证并确保这两个字段是相同的字符集（和排序规则）。

    • 2

相关问题