Bagaimanakah kunci NOT ENFORCED BigQuery mempengaruhi pengoptimuman cantuman (join)?

Gesaan dan skop

Anda memiliki skema bintang BigQuery: store_sales ialah jadual fakta dan customer ialah jadual dimensi. Pasukan inginkan pengisytiharan kunci utama dan asing supaya pengoptimum boleh menggunakan metadata keunikan dan hubungan untuk mengurangkan operasi cantuman (join). Penemu duga bertanyakan tiga soalan: perkara yang dijamin oleh kekangan tidak dikuatkuasakan (unenforced constraint), penulisan semula setara yang manakah selamat, dan cara mencegah ralat senyap apabila data mengalami hanyutan (drift).

Anggap pertanyaan hanya mengunjurkan lajur fakta, kunci asing jadual fakta boleh bernilai nol (nullable), dan kunci utama dimensi adalah unik dan bukan nol. Google Cloud mendokumentasikan bahawa BigQuery tidak menguatkuasakan kekangan ini; pemilik mesti memastikan data kekal sah, dan pertanyaan ke atas kekangan yang dilanggar boleh mengembalikan hasil yang tidak betul.

Perkara yang dinilai oleh penemu duga

Sama ada anda membezakan metadata pengoptimum daripada semakan integriti masa tulis (write-time).
Sama ada anda menerbitkan penghapusan cantuman daripada keunikan dan pemadanan pilihan dan bukannya sekadar mengatakan kunci ialah indeks.
Sama ada anda mengetahui NOT ENFORCED tidak menolak kunci pendua atau kunci asing yatim (orphan).
Sama ada anda mengubah kontrak tersebut menjadi pintu muatan (load gates), pemantauan dan pengunduran (rollback) dan bukannya berhenti pada DDL semata-mata.

Jawapan yang lemah menyatakan "kunci utama adalah unik dan kunci asing merujuk kepadanya." Jawapan yang mantap menyatakan bahawa pengoptimum mungkin menulis semula pertanyaan menggunakan pengisytiharan palsu, maka kegagalannya ialah data yang salah dan bukan sekadar data yang lebih perlahan; setiap pengisytiharan memerlukan bukti yang boleh diulang.

Penjelasan sebelum menjawab

Adakah ini jadual natif BigQuery atau jadual luaran? Sokongan kekangan dan peraturan penulisan semula berbeza, jadi tentukan skop jawapan terlebih dahulu.
Adakah pertanyaan hanya mengunjurkan lajur bahagian kiri? Memilih lajur bahagian kanan biasanya menghalang penghapusan cantuman.
Adakah kunci asing boleh bernilai nol? NULL bermakna tiada pemadanan diperlukan dan mengubah penapis dalam penulisan semula yang setara.
Adakah kekangan dikekalkan oleh satu talian paip atau replikasi rentas sistem? Muatan rentas sistem memerlukan semakan sebelum dan selepas jadual ditulis.

Jawapan ini mengubah hasil: memilih lajur sebelah kanan, kunci utama pendua, atau kunci yatim bukan nol menjadikan penghapusan tidak selamat. Jika hanya ketekalan akhirnya (eventual consistency) yang tersedia, keputusan semakan mesti dijadikan pintu pelepasan (release gate).

Rangka kerja jawapan 30 saat

"Kunci BigQuery ialah metadata deklaratif dan secara lalai adalah NOT ENFORCED. Ia tidak menolak kunci utama pendua atau kunci asing yatim semasa masa tulis. Nilainya adalah memberi fakta keunikan dan hubungan kepada pengoptimum; contohnya, cantuman yang hanya mengembalikan lajur fakta kadangkala boleh dikurangkan kepada penapis bukan nol. Data mesti memenuhi pengisytiharan atau penulisan semula boleh mengembalikan hasil yang salah. Saya mula-mula mengesahkan unjuran dan semantik NULL, kemudian menjalankan semakan kunci pendua, kunci yatim dan penyelarasan kiraan selepas setiap muatan. Semakan yang gagal menyekat pelepasan kekangan atau mengundurkan kelompok data tersebut."

Penyelesaian langkah demi langkah

1. Asingkan dua peranan bagi sesuatu kunci

Pengisytiharan kunci utama bermakna setiap baris adalah unik dan bukan nol. Pengisytiharan kunci asing bermakna setiap nilai bukan nol harus muncul dalam kunci utama yang dirujuk. BigQuery boleh membaca pengisytiharan tersebut untuk pengoptimuman, tetapi ia tidak melakukan pengesahan penulisan. NOT ENFORCED ialah kontrak eksplisit; kecacatan adalah data tidak sah, bukan sintaks tidak sah.

2. Terbitkan penghapusan inner join

Pertimbangkan pertanyaan yang hanya memilih lajur fakta:

sql

SELECT ss.*
FROM store_sales AS ss
JOIN customer AS c
  ON ss.sales_customer = c.customer_name;

Jika customer.customer_name ialah kunci utama bukan nol yang unik dan setiap ss.sales_customer bukan nol sepadan dengan satu pelanggan, cantuman tidak boleh menduplikasi baris fakta. Pengoptimum boleh menulis semula sebagai:

sql

SELECT *
FROM store_sales
WHERE sales_customer IS NOT NULL;

Penulisan semula bergantung pada dua fakta: kunci asing bukan nol mempunyai padanan, dan padanan itu paling banyak satu baris. Ia tidak setara apabila pertanyaan memerlukan lajur sebelah kanan, perlu membezakan baris yang tidak sepadan, atau kunci utama diduplikasi.

3. Sempadan untuk outer join dan susunan cantuman

Cantuman luar kiri (left outer join) juga boleh dibuang apabila kunci cantuman kanan adalah unik dan hanya lajur sebelah kiri diunjurkan. Dalam pertanyaan berbilang cantuman, metadata kunci boleh menyediakan maklumat kardinaliti untuk penyusunan semula cantuman. Ini adalah deduksi berasaskan metadata; BigQuery tidak mengimbas jadual kanan semasa masa jalanan untuk membuktikan keunikan.

4. Letakkan ketepatan dalam pintu muatan

Jalankan sekurang-kurangnya tiga semakan untuk setiap kelompok:

sql

-- Duplicate or null primary keys
SELECT customer_name, COUNT(*) AS n
FROM customer
GROUP BY customer_name
HAVING customer_name IS NULL OR n > 1;

-- Orphan foreign keys
SELECT COUNT(*) AS orphan_count
FROM store_sales AS ss
LEFT JOIN customer AS c
  ON ss.sales_customer = c.customer_name
WHERE ss.sales_customer IS NOT NULL
  AND c.customer_name IS NULL;

Selaraskan kiraan baris kelompok, kiraan kunci asing bukan nol dan kiraan kunci yang sepadan. Simpan hasilnya dalam jadual kualiti data. Hanya versi yang melepasi pintu ini harus menerbitkan pengisytiharan kekangan baharu atau membenarkan pertanyaan hiliran bergantung pada pengoptimuman.

5. Pilih pengisytiharan, pengesahan atau penulisan semula

Pengisytiharan sesuai untuk kunci dimensi yang stabil dan boleh diuji serta membolehkan pengoptimum menggunakan hubungan tersebut secara automatik. Pengesahan aplikasi sesuai untuk laluan masuk (ingress) yang mesti menolak data buruk dari awal. Penulisan semula pertanyaan secara eksplisit sesuai untuk tempoh penghijrahan apabila kontrak tidak dipercayai, dengan kos logik pendua. Ia boleh wujud bersama: sahkan dalam talian paip, isytiharkan hubungan yang dipercayai, dan selaraskan laporan kritikal.

6. Reka bentuk laluan kegagalan

Apabila semakan gagal, simpan jadual atau paparan dipercayai yang terakhir, tandakan kelompok semasa sebagai tidak boleh diterbitkan, dan maklumkan pemilik data. Jangan padamkan kekangan sebagai satu-satunya "pembetulan"; tindakan itu menyembunyikan punca sebenar. Jejak sumber kunci pendua, latensi replikasi atau susunan pemadaman, kemudian pilih main semula (replay), penyahduplikasian atau pembaikan dimensi.

Contoh jawapan berkualiti tinggi

"Saya menganggap kunci utama dan asing BigQuery sebagai kontrak pengoptimuman, bukan kekangan transaksi. Mula-mula saya mengesahkan bahawa pertanyaan hanya mengunjurkan lajur fakta, menentukan maksud kunci asing boleh bernilai nol, dan mengesahkan keunikan kunci dimensi. Di bawah syarat tersebut, inner join boleh menjadi penapis fakta bukan nol, beberapa left join boleh hilang, dan susunan cantuman boleh menggunakan kardinaliti yang diisytiharkan. Risikonya ialah BigQuery tidak menguatkuasakan kontrak tersebut: kunci utama pendua atau kunci asing yatim membolehkan pengoptimum menulis semula menggunakan metadata palsu, yang boleh mengubah hasil secara senyap.

"Sebelum pelepasan, saya menyemak kunci utama nol dan pendua, kunci asing yatim, serta penyelarasan kiraan baris untuk setiap kelompok. Kelompok yang gagal tidak boleh menerbitkan jadual atau kekangan baharu; versi dipercayai sebelumnya kekal aktif. Semasa penghijrahan dengan metadata yang tidak dipercayai, saya menyahdayakan penulisan semula bersandar atau menggunakan cantuman eksplisit sehingga beberapa kelompok berjaya lulus. Itu mengekalkan faedah pengoptimuman sambil memastikan ketepatan boleh diaudit."

Kesilapan lazim

Kesilapan: Mengatakan PRIMARY KEY menolak pendua → Sebab ia gagal: BigQuery mendokumentasikan kekangan sebagai tidak dikuatkuasakan → Pembetulan: Letakkan pengesanan pendua dalam pintu muatan.
Kesilapan: Membuang setiap cantuman yang mempunyai kunci asing → Sebab ia gagal: Kunci mungkin yatim dan pertanyaan mungkin memerlukan lajur sebelah kanan → Pembetulan: Sahkan data dan syarat unjuran terlebih dahulu.
Kesilapan: Menyemak data sejarah sekali sahaja → Sebab ia gagal: Muatan tokokan, backfill dan latensi replikasi boleh memasukkan semula kunci yang buruk → Pembetulan: Jalankan semakan kelompok secara berterusan dan simpan metrik.
Kesilapan: Memadamkan kekangan selepas mendapat hasil yang salah → Sebab ia gagal: Metadata pengoptimuman hilang manakala kecacatan sumber kekal → Pembetulan: Bekukan pelepasan, cari punca dan pulihkan versi yang dipercayai.

Soalan susulan dan jawapan

Bagaimana jika dimensi mendapat kunci pendua hari ini?

Jeda pertanyaan yang bergantung pada pengisytiharan tersebut, beralih kepada snapshot dinyahduplikasi yang eksplisit atau snapshot dipercayai, tandakan kelompok yang terjejas, dan mainkan semula penyelarasan. Pulihkan pengisytiharan hanya selepas dimensi dibaiki dan lulus semakan.

Mengapakah kunci asing boleh bernilai nol memerlukan penapis bukan nol?

NULL bermakna baris fakta tidak mempunyai padanan pelanggan. Inner join menggugurkan baris tersebut, jadi penulisan semula yang setara mesti mengekalkan WHERE sales_customer IS NOT NULL; meninggalkannya akan mengubah hasil.

Bagaimanakah anda membuktikan penghapusan cantuman mengekalkan hasil?

Jalankan pertanyaan asal dan pertanyaan yang ditulis semula secara bersebelahan pada sekatan (partitions) yang mewakili. Bandingkan kiraan baris, set kunci utama dan agregat, serta rekod versi semakan kekangan. Promosikan penulisan semula hanya selepas kedua-dua semakan kualiti data dan penyelarasan keputusan lulus.

Bilakah anda akan mengelakkan pengoptimuman berasaskan kekangan?

Elakkannya apabila kekangan datang daripada replikasi yang perlahan atau tidak boleh diaudit, backfill kerap berlaku, atau tiada pintu kelompok. Satu imbasan tambahan adalah lebih baik daripada pertanyaan lebih murah yang mempercayai metadata palsu.

Bolehkah kekangan BigQuery menggantikan transaksi rentas jadual?

Tidak. Ia tidak menguatkuasakan ketekalan penulisan atau menyediakan komit atom rentas jadual. Semantik transaksi adalah milik sistem huluan atau orkestrator muatan, dengan hasil pengesahan akhir dibawa ke dalam gudang data.

Rujukan

Dokumentasi Google Cloud: BigQuery primary and foreign keys.
Blog Google Cloud: Join Optimizations with BigQuery Primary and Foreign Keys.