Im Datenbankprotokoll finden wir einige Abfragen mit unnötigen ORDER BY-Klauseln in Unterabfragen, die von user.rb#L379 erzeugt werden. Eine davon sieht wie folgt aus:
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
Die ORDER BY-Klausel in der Unterabfrage ist sinnlos und kann entfernt werden, um die Abfrage zu beschleunigen. Laut unseren Tests kann dies die Leistung der Abfrage um 18 % bis 90 % verbessern (abhängig von der Zeit, die für die Sortierung in der Unterabfrage aufgewendet wird).
Wir führen Tests in Discourse durch und haben in der Datenbank eine langsame Abfrage festgestellt (6.480.259 Nanosekunden im Vergleich zu einer ähnlichen Abfrage), die durch eine unnötige DISTINCT-Klausel verursacht wird, die von site_settings_controller.rb#L141 generiert wird, wie folgt:
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
)
Wenn tags.id und notification_level spezifische Werte haben, erzeugen sowohl der CROSS JOIN als auch der LEFT JOIN aufgrund der UNIQUE-Einschränkung (tag_id, user_id, notification_level) in tag_users und des PRIMARY KEY(id) in tags keine duplizierten Datensätze. Das bedeutet, dass wir DISTINCT entfernen können, um die Abfrage zu beschleunigen.
Diese optimierte Abfrage benötigt 4.538.891 Nanosekunden (Verbesserung um 30 %).
Aus dem Datenbank-Log lässt sich eine langsame Abfrage (6064379 Nanosekunden) mit vielen Unterabfragen und UNIONs identifizieren, die von group.rb#L112 generiert wurde, wie folgt:
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
Offensichtlich entspricht dies der folgenden Abfrage:
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
Diese optimierte Abfrage benötigt 378062 Nanosekunden (Verbesserung um 93 %).
Entschuldigung, die korrekte umgeschriebene Abfrage lautet:
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
Die Unterabfrage mit dem Prädikat visibility_level = 0,1,2 kann durch groups.visibility_level IN (0, 1, 2) ersetzt werden, und die Unterabfrage mit dem Prädikat visibility_level = 3,4 kann durch OR (groups.visibility_level IN (3,4) and gu.owner) ersetzt werden.
In der Datenbank finden wir eine langsame Abfrage (1.141.257 Nanosekunden) mit unnötigen JOINs und DISTINCT, die von directory_items_controller.rb erzeugt wird, wie folgt:
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
Es wurden keine Daten aus user_stats abgerufen, und groups.id = 2898 kann durch group_users.group_id = 2898 ersetzt werden. Das bedeutet, dass wir die Tabellen groups und user_stats aus der Abfrage entfernen können. Gleichzeitig erzeugt der JOIN aufgrund der UNIQUE-Einschränkung (user_id, group_id) in group_users und des PRIMARY KEY (id) in users keine duplizierten Datensätze. Daher können wir auch DISTINCT aus der Abfrage entfernen, wie unten gezeigt:
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
Diese optimierte Abfrage benötigt 941.700 Nanosekunden (Verbesserung um 17,49 %).
Wir führen die Tests in Discourse aus und finden in der Datenbank eine langsame Abfrage ((331729 Nanosekunden) im Vergleich zu einer ähnlichen Abfrage) mit einer unnötigen JOIN-Operation, die durch ser_badge.rb#L18 erzeugt wird, wie folgt:
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
Diese Unterabfrage kann durch eine einfache Bedingung badges.enabled ersetzt werden, wie unten gezeigt:
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
Diese optimierte Abfrage benötigt 267383 Nanosekunden (Verbesserung um 19 %).