Медленные запросы в Discourse

[wtune]

Из лога базы данных мы видим, что некоторые запросы содержат ненужный ORDER BY в подзапросе, генерируемом user.rb#L379. Один из них выглядит следующим образом:

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% (в зависимости от времени, затрачиваемого на сортировку в подзапросе).

[codinghorror (Jeff Atwood)]

Каковы фактические показатели в миллисекундах?

[wtune]

В этом примере удаление ORDER BY позволяет сократить время выполнения запроса с 4 711 878 наносекунд до 585 849 наносекунд.

[wtune]

Мы запускаем спецификации в Discourse, и из базы данных мы обнаружили медленный запрос (6480259 наносекунд по сравнению с аналогичным запросом) с ненужным DISTINCT, генерируемым в site_settings_controller.rb#L141, следующим образом:

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 указаны, благодаря уникальному ограничению UNIQUE (tag_id, user_id, notification_level) в таблице tag_users и первичному ключу PRIMARY KEY(id) в таблице tags, ни CROSS JOIN, ни LEFT JOIN не создают дублирующихся записей. Это означает, что мы можем удалить DISTINCT для ускорения запроса.
Оптимизированный запрос выполняется за 4538891 наносекунду (улучшение на 30%).

[wtune]

В логе базы данных обнаружен медленный запрос (6064379 наносекунд) с множеством подзапросов и оператором UNION, сгенерированный в файле group.rb#L112, который выглядит следующим образом:

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%).

[sam (Sam Saffron)]

@wtune

Очевидно, это равносильно следующему запросу:

Чем это равно? Вы ищете группы, для которых не выполняются проверки уровня видимости.

[wtune]

Извините, правильный переписанный запрос должен выглядеть так:

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).

[wtune]

В базе данных обнаружен медленный запрос (11 412 570 наносекунд) с избыточными JOIN и DISTINCT, сгенерированный directory_items_controller.rb, как показано ниже:

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 из запроса. Кроме того, благодаря уникальному ограничению UNIQUE (user_id, group_id) в таблице group_users и первичному ключу PRIMARY KEY (id) в таблице users, 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

Оптимизированный запрос выполняется за 9 417 000 наносекунд (улучшение на 17,49 %).

[codinghorror (Jeff Atwood)]

Вы запускаете какой-нибудь сканер базы данных на Discourse?

[wtune]

Мы запускаем спецификации в Discourse, и из базы данных обнаруживаем медленный запрос (331 729 наносекунд по сравнению с аналогичным запросом) с ненужным JOIN, сгенерированным в ser_badge.rb#L18, как показано ниже:

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

Этот оптимизированный запрос выполняется за 267 383 наносекунды (улучшение на 19%).