Discourse 中的慢查询

开发
1

从数据库日志中，我们发现由 user.rb#L379 生成的子查询中存在一些不必要的 ORDER BY。其中一个示例如下：

SELECT 
  "group_users"."group_id" 
FROM 
  "group_users" 
WHERE 
  "group_users"."group_id" IN (
    SELECT 
      "groups"."id" 
    FROM 
      "groups" 
    WHERE 
      (groups.id > 0) 
    ORDER BY 
      name ASC
  ) 
  AND "group_users"."user_id" = 762

子查询中的 ORDER BY 毫无意义，移除它可以加速查询。根据我们的测试，这可以将查询性能提升 18% 至 90%（具体取决于子查询中排序所浪费的时间）。

2

实际的基准测试是多少毫秒？

3

在此示例中，移除 ORDER BY 可将查询时间从 4711878 纳秒减少到 585849 纳秒。

4

我们在 Discourse 中运行测试，并从数据库中发现了一个慢查询（耗时 6480259 纳秒，与类似查询相比），该查询由 site_settings_controller.rb#L141 生成了不必要的 DISTINCT，如下所示：

SELECT 
  DISTINCT users.id 
FROM 
  "users" CROSS 
  JOIN tags t 
  LEFT JOIN tag_users tu ON users.id = tu.user_id 
  AND t.id = tu.tag_id 
WHERE 
  (
    t.id IN (1825) 
    AND tu.notification_level IS NULL
  )

当 tags.id 和 notification_level 指定具体值时，由于 tag_users 表存在 UNIQUE (tag_id, user_id, notification_level) 约束，且 tags 表存在 PRIMARY KEY(id) 约束，因此 CROSS JOIN 和 LEFT JOIN 都不会产生重复记录。这意味着我们可以移除 DISTINCT 以加速查询。
优化后的查询耗时 4538891 纳秒（性能提升 30%）。

6

从数据库日志中我们发现了一条由 group.rb#L112 生成的慢查询（耗时 6064379 纳秒），该查询包含多个子查询和 UNION 操作，内容如下：

SELECT 
  "groups"."id", 
  "groups"."name" 
FROM 
  "groups" 
  INNER JOIN "group_users" ON "groups"."id" = "group_users"."group_id" 
WHERE 
  "group_users"."user_id" = 296 
  AND (groups.id > 0) 
  AND (
    groups.id IN (
      SELECT 
        id 
      FROM 
        groups 
      WHERE 
        visibility_level = 0 
      UNION ALL 
      SELECT 
        id 
      FROM 
        groups 
      WHERE 
        visibility_level = 1 
        AND 296 IS NOT NULL 
      UNION ALL 
      SELECT 
        g.id 
      FROM 
        groups g 
        JOIN group_users gu ON gu.group_id = g.id 
        AND gu.user_id = 296 
      WHERE 
        g.visibility_level = 2 
      UNION ALL 
      SELECT 
        g.id 
      FROM 
        groups g 
        LEFT JOIN group_users gu ON gu.group_id = g.id 
        AND gu.user_id = 296 
        AND gu.owner 
      WHERE 
        g.visibility_level = 3 
        AND (
          gu.id IS NOT NULL 
          OR FALSE
        ) 
      UNION ALL 
      SELECT 
        g.id 
      FROM 
        groups g 
        JOIN group_users gu ON gu.group_id = g.id 
        AND gu.user_id = 296 
        AND gu.owner 
      WHERE 
        g.visibility_level = 4
    )
  ) 
ORDER BY 
  name ASC

显然，该查询等价于以下查询：

SELECT 
  "groups"."id", 
  "groups"."name" 
FROM 
  "groups" 
  INNER JOIN "group_users" ON "groups"."id" = "group_users"."group_id" 
WHERE 
  "group_users"."user_id" = 296 AND
  groups.visibility_level IN (0, 1, 2, 3, 4) 
ORDER BY 
  name ASC

此优化后的查询耗时为 378062 纳秒（性能提升 93%）。

7

它怎么会等同呢？你正在寻找没有可见性级别检查的组。

8

很抱歉，正确的重写查询应为

SELECT 
  "groups"."id", 
  "groups"."name" 
FROM 
  "groups" 
  INNER JOIN "group_users" gu ON "groups"."id" = gu."group_id" 
WHERE 
  "group_users"."user_id" = 296 
   AND (groups.id > 0) 
   AND (
    groups.visibility_level IN (0, 1, 2) 
    OR (groups.visibility_level IN (3,4) and gu.owner)
   ) 
ORDER BY 
  name ASC

包含谓词 visibility_level = 0,1,2 的子查询可替换为 groups.visibility_level IN (0, 1, 2)，包含谓词 visibility_level = 3,4 的子查询可替换为 or (groups.visibility_level IN (3,4) and gu.owner)。

9

从数据库中我们发现了一个由 directory_items_controller.rb 生成的慢查询（耗时 1141257 纳秒），其中包含了不必要的 JOIN 和 DISTINCT 操作，具体如下：

SELECT 
  COUNT(DISTINCT "directory_items"."id") 
FROM 
  "directory_items" 
  LEFT OUTER JOIN "users" ON "users"."id" = "directory_items"."user_id" 
  LEFT OUTER JOIN "group_users" ON "group_users"."user_id" = "users"."id" 
  LEFT OUTER JOIN "groups" ON "groups"."id" = "group_users"."group_id" 
  LEFT OUTER JOIN "user_stats" ON "user_stats"."user_id" = "directory_items"."user_id" 
WHERE 
  "directory_items"."period_type" = 1 
  AND "groups"."id" = 2898

该查询并未从 user_stats 表中检索到任何数据，且 groups.id = 2898 可以替换为 group_users.group_id = 2898，这意味着我们可以从查询中移除 groups 和 user_stats 表。同时，由于 group_users 表中存在 UNIQUE (user_id, group_id) 约束，而 users 表中存在 PRIMARY KEY (id) 约束，JOIN 操作不会产生重复记录，因此我们也可以从查询中移除 DISTINCT 操作，如下所示：

SELECT 
  COUNT("directory_items"."id") 
FROM 
  "directory_items" 
  LEFT OUTER JOIN "users" ON "users"."id" = "directory_items"."user_id" 
  LEFT OUTER JOIN "group_users" ON "group_users"."user_id" = "users"."id" 
WHERE 
  "directory_items"."period_type" = 1 
  AND "group_users"."group_id"  = 2898

优化后的查询耗时为 941700 纳秒（性能提升 17.49%）。

10

你正在对 Discourse 运行某种数据库扫描吗？

11

我们在 Discourse 中运行测试，从数据库中发现了一个慢查询（331729 纳秒，与类似查询相比），该查询由 ser_badge.rb#L18 生成了不必要的 JOIN，如下所示：

SELECT 
  COUNT(*) 
FROM 
  "badges" 
  INNER JOIN "user_badges" ON "badges"."id" = "user_badges"."badge_id" 
WHERE 
  "user_badges"."user_id" = 2112 
  AND (
    user_badges.badge_id IN (
      SELECT 
        id 
      FROM 
        badges 
      WHERE 
        enabled
    )
  ) 
  AND "badges"."id" = 1

该子查询可以替换为简单的谓词 badges.enabled，如下所示：

SELECT 
  COUNT(*) 
FROM 
  "badges" 
  INNER JOIN "user_badges" ON "badges"."id" = "user_badges"."badge_id" 
WHERE 
  "user_badges"."user_id" = 2112 
  AND badges.enabled 
  AND "badges"."id" = 1

优化后的查询耗时为 267383 纳秒（性能提升 19%）。