استعلامات بطيئة في Discourse

مطور
1

من سجل قاعدة البيانات، نجد بعض الاستعلامات التي تحتوي على 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% (حسب الوقت الضائع في الترتيب داخل الاستعلام الفرعي).

2

ما هي المقاييس الفعلية، بالمللي ثانية؟

3

في هذا المثال، يمكن أن يؤدي إزالة ORDER BY إلى تقليل وقت الاستعلام من 4711878 نانو ثانية إلى 585849 نانو ثانية.

4

نُجري الاختبارات في 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%).

6

من سجل قاعدة البيانات، نجد استعلامًا بطيئًا (6064379 نانوثانية) يحتوي على العديد من الاستعلامات الفرعية وعمليات الاتحاد، تم إنشاؤه بواسطة 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%).

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

من قاعدة البيانات، نجد استعلامًا بطيئًا (1141257 نانوثانية) يحتوي على 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

يستغرق الاستعلام المُحسَّن 941700 نانوثانية (تحسين بنسبة 17.49%).

10

هل تقوم بتشغيل نوع ما من ماسحات قواعد البيانات على Discourse؟

11

نُجري الاختبارات في Discourse، ومن قاعدة البيانات نجد استعلامًا بطيئًا (331729 نانوثانية) مقارنة باستعلام مماثل، ناتج عن 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

يأخذ الاستعلام المُحسّن 267383 نانوثانية (تحسن بنسبة 19%)