Bagaimana untuk Memilih Baris Rawak dengan Cekap daripada Jadual PostgreSQL Besar?

Kaedah pemilihan baris rawak PostgreSQL

Kaedah pemilihan baris rawak tradisional tidak cekap dan perlahan apabila berurusan dengan jadual besar yang mengandungi berjuta-juta malah berbilion-bilion rekod. Dua kaedah biasa ialah:

• Gunakan random() untuk menapis:

    select * from table where random() < 0.001;

• Gunakan order by random() dan limit:

    select * from table order by random() limit 1000;

Namun, disebabkan keperluan untuk imbasan jadual penuh atau pengisihan, kaedah ini bukanlah pilihan terbaik untuk jadual dengan bilangan baris yang banyak dan akan menyebabkan kesesakan prestasi.

Kaedah pengoptimuman untuk meja besar

Untuk jenis jadual berikut, pertimbangkan kaedah pengoptimuman berikut, yang jauh lebih pantas:

• Lajur ID berangka dengan jurang kecil atau sederhana (diindeks untuk carian yang lebih pantas)
• Tiada atau operasi tulis minimum semasa pemilihan

Pertanyaan:

WITH params AS (
  SELECT 1 AS min_id, -- 可选：自定义最小ID起始值
       5100000 AS id_span -- 近似ID范围（最大ID - 最小ID + 缓冲）
)
SELECT *
FROM (
  SELECT DISTINCT 1 + trunc(random() * p.id_span)::integer AS id
  FROM params p, generate_series(1, 1100) g
  GROUP BY 1
) r
INNER JOIN big ON r.id = big.id
LIMIT 1000;

Cara ia berfungsi:

• Anggaran julat ID:

  • Jika tidak diketahui dengan tepat, tanya jadual untuk menganggarkan minimum, maksimum dan jumlah rentang (maks - min) lajur ID.

• Penjanaan ID rawak:

  • Jana set nombor rawak yang berbeza dalam julat ID anggaran.

• Penyingkiran lebihan dan pendua:

  • Kumpulkan nombor yang dijana untuk mengalih keluar pendua, mengurangkan kemungkinan memilih baris yang hilang atau baris yang sudah dipilih.

• Gabungan jadual dan sekatan:

  • Sertai nombor rawak dengan jadual sebenar menggunakan lajur ID (mesti diindeks). Gabungan cekap ini mendapatkan semula data yang sepadan untuk baris yang dipilih.
  • Akhir sekali, gunakan had untuk mendapatkan semula bilangan baris yang diperlukan.

Mengapa ia pantas:

• Penggunaan indeks minimum:

  • Pertanyaan hanya melakukan imbasan indeks pada lajur ID, yang jauh lebih pantas daripada imbasan jadual penuh atau operasi isihan.

• Penjanaan nombor rawak yang dioptimumkan:

  • Nombor rawak yang dijana diedarkan pada julat ID anggaran, meminimumkan kemungkinan baris hilang atau bertindih.

• Penyingkiran lebihan dan pendua:

  • Menghimpunkan nombor yang dijana memastikan bahawa hanya baris yang berbeza dipilih, mengurangkan keperluan untuk penapisan tambahan atau gabungan untuk menghapuskan pendua.

Pilihan lain:

• CTE rekursif untuk menangani jurang:

  • Untuk jadual dengan jurang dalam jujukan ID, tambahkan CTE tambahan untuk mengendalikan jurang ini.

• Pembungkus fungsi untuk digunakan semula:

  • Tentukan fungsi yang mengambil peratusan had dan jurang sebagai parameter, membolehkan konfigurasi mudah dan digunakan semula dengan jadual yang berbeza.

• Fungsi universal untuk mana-mana jadual:

  • Buat fungsi generik yang menerima sebarang jadual dengan lajur integer sebagai parameter.

• Realisasikan paparan untuk kelajuan:

  • Pertimbangkan untuk mencipta paparan terwujud berdasarkan pertanyaan yang dioptimumkan untuk mendapatkan semula (kuasi) baris yang dipilih secara rawak dengan lebih pantas.

• TABLE SAMPLE dalam PostgreSQL 9.5:

  • Manfaatkan ciri "TABLE SAMPLE SYSTEM" PostgreSQL untuk melaksanakan kaedah pensampelan baris yang lebih pantas tetapi kurang rawak, memastikan bilangan baris yang tepat dikembalikan. Walau bagaimanapun, perlu diingat bahawa sampel mungkin tidak rawak sepenuhnya disebabkan oleh kesan pengelompokan.

Atas ialah kandungan terperinci Bagaimana untuk Memilih Baris Rawak dengan Cekap daripada Jadual PostgreSQL Besar?. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!