Bagaimanakah Saya Boleh Mengoptimumkan GROUP BY Queries untuk Mendapatkan Baris Terkini dengan Cekap untuk Setiap Pengguna?

Strategi Pengoptimuman untuk GROUP BY Query untuk Mendapatkan Baris Terkini bagi setiap Pengguna

Diberikan jadual dengan mesej pengguna yang distrukturkan sebagai log_date, user_id dan muatan , tugasnya adalah untuk mendapatkan semula rekod terkini untuk setiap pengguna dengan cekap sebelum sesuatu yang khusus tarikh.

Indeks Berbilang Ruang

Untuk meningkatkan prestasi bacaan, cipta indeks berbilang lajur pada user_id dan log_date:

CREATE INDEX log_combo_idx ON log (user_id, log_date DESC NULLS LAST);

Indeks- Hanya Imbasan dengan Indeks Penutup

Untuk indeks sahaja imbasan, tentukan indeks penutup yang merangkumi lajur muatan:

CREATE INDEX log_combo_covering_idx ON log (user_id, log_date DESC NULLS LAST) INCLUDE (payload);

SELECT DISTINCT ON()

Untuk jadual kecil atau beberapa baris setiap user_id, menggunakan SELECT DISTINCT ON() boleh menjadi cekap:

SELECT DISTINCT ON(user_id) log_date, payload
FROM log
WHERE log_date <= :mydate
ORDER BY user_id, log_date DESC;

Imbasan Langkau Indeks Emulasi

Untuk jadual besar dengan banyak baris setiap user_id, pertimbangkan untuk meniru imbasan langkau indeks menggunakan CTE rekursif dengan sambung LATERAL:

WITH RECURSIVE cte AS (
   (
   SELECT user_id, log_date, payload
   FROM   log
   WHERE  log_date <= :mydate
   ORDER  BY user_id, log_date DESC NULLS LAST
   LIMIT  1
   )
   UNION ALL
   SELECT l.*
   FROM   cte c
   CROSS  JOIN LATERAL (
      SELECT l.user_id, l.log_date, l.payload
      FROM   log l
      WHERE  l.user_id > c.user_id  -- lateral reference
      AND    log_date <= :mydate    -- repeat condition
      ORDER  BY l.user_id, l.log_date DESC NULLS LAST
      LIMIT  1
      ) l
   )
TABLE  cte
ORDER  BY user_id;

Jadual Pengguna Asingkan

Jika jadual pengguna yang berasingan wujud, penyelesaian yang dipermudahkan mungkin:

LATERAL Sertai

SELECT u.user_id, l.log_date, l.payload
FROM   users u
CROSS  JOIN LATERAL (
   SELECT l.log_date, l.payload
   FROM   log l
   WHERE  l.user_id = u.user_id         -- lateral reference
   AND    l.log_date <= :mydate
   ORDER  BY l.log_date DESC NULLS LAST
   LIMIT  1
   ) l;

Subkueri Berkaitan

SELECT user_id, (combo1).*              -- note parentheses
FROM (
   SELECT u.user_id
        , (SELECT (l.log_date, l.payload)::combo
           FROM   log l
           WHERE  l.user_id = u.user_id
           AND    l.log_date <= :mydate
           ORDER  BY l.log_date DESC NULLS LAST
           LIMIT  1) AS combo1
   FROM   users u
   ) sub;

Pengoptimuman ini meningkatkan prestasi pertanyaan dengan menggunakan indeks , meniru imbasan langkau dan memanfaatkan jadual berasingan untuk maklumat pengguna.

Atas ialah kandungan terperinci Bagaimanakah Saya Boleh Mengoptimumkan GROUP BY Queries untuk Mendapatkan Baris Terkini dengan Cekap untuk Setiap Pengguna?. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!