Bagaimanakah Saya Boleh Mengoptimumkan Pertanyaan Maksimum Mengikut Kumpulan dalam MySQL dan PostgreSQL?

Mengoptimumkan Pertanyaan Maksimum Mengikut Kumpulan

Pertanyaan yang disediakan, bertujuan untuk mendapatkan semula rekod dengan ID maksimum yang dikumpulkan mengikut option_id, menghadapi masalah prestasi disebabkan pengimbasan semua barisan. Untuk menangani perkara ini, pertimbangkan untuk menggunakan indeks program yang menyimpan nilai ID maksimum untuk setiap option_id. Pendekatan ini membolehkan perolehan maksimum yang cekap dengan hanya mengimbas indeks.

Pendekatan tradisional, seperti pengindeksan (option_id, id) atau (option_id, id DESC), tidak mengoptimumkan pertanyaan dengan secukupnya.

MySQL's Pengoptimuman

MySQL 5.5 menggabungkan pengoptimuman lanjutan untuk pertanyaan ini. Dengan mengindeks (option_id, id), MySQL memanfaatkan indeks untuk operasi kumpulan mengikut, mengelakkan keperluan untuk imbasan berurutan.

Pengoptimuman Postgres dengan Jadual Rujukan

Dalam Postgres , buat jadual rujukan, pilihan, dengan option_id unik yang sepadan dengan option_id yang berbeza dalam rekod jadual.

CREATE TABLE options ( option_id INT PRIMARY KEY, option TEXT UNIQUE NOT NULL );

INSERT INTO options SELECT DISTINCT option_id, 'option' || option_id FROM records;

Gunakan subkueri berkorelasi untuk mendapatkan ID maksimum daripada rekod bagi setiap option_id:

SELECT option_id, (SELECT MAX(id) FROM records WHERE option_id = o.option_id) AS max_id
FROM options o
ORDER BY 1;

Pendekatan ini membolehkan imbasan indeks sahaja, meminimumkan akses baris dalam rekod. Indeks ideal untuk pertanyaan ini:

CREATE INDEX ON records (option_id, id DESC NULLS LAST);

Pengoptimuman Postgres dengan Sertai LATERAL (Postgres 9.3 )

Sebagai alternatif, dalam Postgres 9.3 dan ke atas, gunakan sambung LATERAL untuk mencapai pengoptimuman yang serupa:

SELECT *
FROM records
LATERAL (
  SELECT MAX(id) OVER (PARTITION BY option_id) AS max_id
  FROM records
) AS m
WHERE records.id = m.max_id;

Pendekatan ini juga memanfaatkan mengimbas indeks sahaja untuk pelaksanaan pertanyaan yang cekap.

Atas ialah kandungan terperinci Bagaimanakah Saya Boleh Mengoptimumkan Pertanyaan Maksimum Mengikut Kumpulan dalam MySQL dan PostgreSQL?. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!