Bagaimanakah Saya Boleh Mengoptimumkan Pertanyaan SQL untuk Mendapatkan Item Terbaru dengan Cekap daripada Setiap Kategori dalam Pangkalan Data Besar?

Meningkatkan Prestasi Pertanyaan SQL untuk Set Data Besar

Pertanyaan SQL yang cekap adalah penting untuk mengekalkan prestasi pangkalan data, terutamanya apabila berurusan dengan pangkalan data yang besar dan pengambilan data yang kompleks. Ini amat kritikal apabila bekerja dengan berbilang jadual yang saling berkaitan.

Cabaran: Mendapatkan semula Item Terkini bagi setiap Kategori

Bayangkan pangkalan data menyimpan item yang dikategorikan mengikut ID. Matlamatnya adalah untuk memaparkan setiap kategori dengan empat item yang paling baru ditambah. Kaedah tradisional menggunakan berbilang pertanyaan bagi setiap kategori membawa kepada beban pangkalan data yang berlebihan, terutamanya dengan sejumlah besar kategori.

Pertanyaan Dioptimumkan: Memanfaatkan Pengumpulan dan Menyertai

Pertanyaan dioptimumkan berikut menggunakan gabungan luar dan pengumpulan untuk mendapatkan semula data yang dikehendaki dalam satu pas pangkalan data:

<code class="language-sql">SELECT i1.*
FROM item i1
LEFT OUTER JOIN item i2
  ON (i1.category_id = i2.category_id AND i1.item_id < i2.item_id)
GROUP BY i1.item_id
HAVING COUNT(*) < 4;</code>

Pertanyaan ini membandingkan setiap item (i1) dengan item yang lebih baharu (i2) dalam kategori yang sama. Jika kurang daripada empat item baharu wujud, i1 disertakan dalam keputusan.

Pendekatan Alternatif

Kaedah lain, seperti pembolehubah pengguna MySQL untuk penomboran baris dalam setiap kategori, menawarkan penyelesaian alternatif:

<code class="language-sql">SELECT *
FROM (
    SELECT i.*, @r := IF(@g = category_id, @r+1, 1) AS rownum, @g := category_id
    FROM (SELECT @g:=null, @r:=0) AS _init
    CROSS JOIN item i
    ORDER BY i.category_id, i.date_listed
) AS t
WHERE t.rownum <= 4;</code>

Untuk MySQL 8.0.3 dan yang lebih baru, fungsi tetingkap SQL menyediakan pendekatan piawai:

<code class="language-sql">WITH numbered_item AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY item_id DESC) AS rownum
  FROM item
)
SELECT * FROM numbered_item WHERE rownum <= 4;</code>

Faedah Prestasi

Pertanyaan yang dioptimumkan ini meningkatkan prestasi dengan ketara apabila bilangan kategori bertambah. Dengan meminimumkan beban pangkalan data dan mengoptimumkan penggunaan sumber, aplikasi memastikan pengambilan data yang cekap walaupun dengan set data yang luas.

Atas ialah kandungan terperinci Bagaimanakah Saya Boleh Mengoptimumkan Pertanyaan SQL untuk Mendapatkan Item Terbaru dengan Cekap daripada Setiap Kategori dalam Pangkalan Data Besar?. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!