5.1 Pambuka

Internet kebak aturan dogmatis babagan carane kunci kudu dipilih lan digunakake ing basis data relasional. Kadhangkala perselisihan malah dadi holivars: apa kunci alami utawa buatan kudu digunakake? Auto-increment integers utawa UUIDs?

Sawise maca sewidak papat artikel, flipping liwat bagean saka limang buku, lan takon ton pitakonan ing IRC lan StackOverflow, Aku (Joe "begriffs" Nelson, penulis artikel asli) katon wis sijine bêsik teka-teki bebarengan lan saiki bisa rekonsiliasi mungsuh. Akeh perselisihan kunci sing sejatine muncul saka salah paham babagan sudut pandang wong liya.

Ayo padha ngilangi masalah kasebut lan sijine maneh ing pungkasan. Pisanan, ayo takon - apa "kunci"?

Ayo lali babagan kunci utama, kita kasengsem ing ide sing luwih umum. Tombol yaiku kolom (kolom) utawa kolom sing ora duwe nilai duplikat ing baris . Uga, kolom kudu unik, yaiku, ora ana subset saka kolom sing nduweni keunikan iki.

Nanging pisanan, sawetara teori:

kunci utama

Kunci utamalangsung digunakake kanggo ngenali baris ing tabel. Iku kudu tundhuk karo watesan ing ngisor iki:

  • Tombol utami kudu unik saben wektu.
  • Iku kudu tansah ana ing meja lan duwe nilai.
  • Sampeyan ngirim ora ngganti regane kerep. Saenipun, mesthine ora ngganti nilai kasebut .

Biasane, kunci utama nggambarake kolom siji saka tabel, nanging bisa uga dadi kunci gabungan sing dumadi saka pirang-pirang kolom.

Kunci komposit

Kunci Kustom- kombinasi atribut (kolom) sing unik ngenali saben baris tabel. Bisa dadi kabeh kolom, lan sawetara, lan siji. Ing kasus iki, garis sing ngemot nilai atribut kasebut ora kudu diulang.

Potensi Kunci

kunci calon- nggantosi kunci gabungan minimal saka hubungan (tabel), yaiku, sakumpulan atribut sing nyukupi sawetara kondisi:

  • Irreducibility : Ora bisa dikurangi, ngemot atribut minimal sing bisa ditindakake.
  • Uniqueness : Sampeyan kudu duwe nilai unik preduli saka owah-owahan baris.
  • Anane nilai : Ora kudu duwe nilai null, yaiku kudu duwe nilai.

5.2 Kasus penasaran saka tombol utama

Apa sing mung disebut "tombol" ing bagean sadurunge biasane diarani "tombol calon". Istilah "calon" tegese kabeh kunci kasebut bersaing kanggo peran kehormatan "kunci utama" (kunci utama), lan liyane diwenehi "tombol alternatif" (tombol alternatif).

Butuh sawetara wektu kanggo implementasi SQL kanggo ngatasi mismatch antarane tombol lan model relasional, lan database paling wiwitan diarahake menyang konsep tingkat rendah kunci utama. Tombol utami ing basis data kasebut dibutuhake kanggo ngenali lokasi fisik baris ing media panyimpenan urutan. Mangkene carane Joe Celko nerangake:

Tembung "kunci" tegese tombol ngurutake file, sing dibutuhake kanggo nindakake operasi pangolahan ing sistem file sing berurutan. A pesawat saka kertu punched diwaca ing siji lan mung siji urutan; ora bisa bali. Drive tape awal niru prilaku sing padha lan ora ngidini akses bidirectional. Sing, asli Sybase SQL Server dibutuhake kanggo "mundur" meja kanggo awal kanggo maca baris sadurunge.

Ing SQL modern, sampeyan ora perlu fokus ing perwakilan fisik informasi, hubungan model tabel, lan urutan internal baris ora penting. Nanging, malah saiki SQL server minangka standar nggawe indeks clustered kanggo tombol utami lan, miturut tradisi lawas, fisik ngatur urutan baris.

Ing basis data umume, kunci utama minangka barang sing kepungkur, lan nyedhiyakake luwih saka bayangan utawa lokasi fisik. Contone, ing tabel PostgreSQL, nyatakake kunci utami kanthi otomatis ngetrapake kendala NOT NULLlan nemtokake kunci manca standar. Kajaba iku, tombol utama minangka kolom sing disenengi kanggo operator JOIN.

Tombol utami ora ngilangi kamungkinan kanggo ngumumake kunci liyane. Ing wektu sing padha, yen ora ana tombol sing ditugasake minangka utami, mula tabel kasebut isih bisa digunakake kanthi becik. Kilat, ing kasus apa wae, ora bakal nyerang sampeyan.

Java webinar

5.3 Nemokake tombol alam

Tombol sing dibahas ing ndhuwur diarani "alami" amarga sifat-sifat obyek sing dimodelake sing menarik, sanajan ora ana sing pengin nggawe kunci kasebut.

Wangsulan: Bab ingkang sapisanan kudu elinga nalika mriksa meja kanggo kemungkinan tombol alam kanggo nyoba ora dadi pinter banget. Pangguna sqlvogel ing StackExchange menehi saran ing ngisor iki:

Sawetara wong kangelan milih tombol "alami" amarga padha teka karo kahanan hipotetis kang tombol tartamtu bisa uga ora unik. Dheweke ora ngerti artine tugas kasebut. Tegese tombol yaiku kanggo nemtokake aturan miturut atribut sing kudu lan mesthi unik ing tabel tartamtu. Tabel kasebut ngemot data ing konteks tartamtu lan dimangerteni kanthi apik (ing "wilayah subyek" utawa "wilayah wacana"), lan siji-sijine makna yaiku aplikasi larangan ing wilayah kasebut.

Praktek nuduhake yen perlu ngenalake kendala utama nalika kolom kasebut unik karo nilai sing kasedhiya lan bakal tetep ana ing skenario sing mungkin. Lan yen perlu, watesan bisa dicopot (yen iki ngganggu sampeyan, banjur ing ngisor iki kita bakal ngomong babagan stabilitas tombol.)

Contone, database anggota klub hobi bisa duwe uniqueness ing rong kolom - first_name,. last_nameKanthi jumlah data sing cilik, duplikat ora mungkin, lan sadurunge konflik nyata muncul, cukup cukup kanggo nggunakake kunci kasebut.

Nalika basis data mundhak lan volume informasi mundhak, milih kunci alami bisa dadi luwih angel. Data sing kita simpen minangka simplifikasi saka kasunyatan eksternal, lan ora ngemot sawetara aspèk sing mbedakake obyek ing donya, kayata koordinat sing owah saka wektu. Yen obyek ora duwe kode apa wae, kepiye sampeyan bisa nemtokake rong kaleng minuman utawa rong kothak oatmeal saka susunan spasial utawa bedane bobot utawa kemasan?

Pramila badan standarisasi nggawe lan ngetrapake tandha khas kanggo produk. Kendaraan dicap nganggo Vehicle Identification Number (VIN) , buku dicithak nganggo ISBN , lan kemasan panganan duwe UPC . Sampeyan bisa mbantah manawa nomer kasebut ora katon alami. Dadi kenapa aku nyebat kunci alami?

Naturalness utawa artificiality saka sifat unik ing database iku relatif kanggo donya njaba. Tombol sing digawe nalika digawe ing badan standar utawa lembaga pemerintah dadi alami kanggo kita, amarga dadi standar ing saindenging jagad lan / utawa dicithak ing obyek.

Ana akeh standar industri, umum, lan internasional kanggo macem-macem subjek, kalebu mata uang, basa, instrumen finansial, bahan kimia, lan diagnosa medis. Ing ngisor iki sawetara nilai sing asring digunakake minangka kunci alami:

  • Kode negara ISO 3166
  • Kode basa ISO 639
  • Kode mata uang miturut ISO 4217
  • Simbol saham ISIN
  • UPC/EAN, VIN, GTIN, ISBN
  • jeneng mlebu
  • alamat email
  • nomer kamar
  • alamat mac jaringan
  • latitude, longitude kanggo titik ing lumahing bumi

Aku nyaranake wara-wara tombol kapan bisa lan cukup, Mungkin malah sawetara tombol saben meja. Nanging elinga yen kabeh ing ndhuwur bisa uga duwe pangecualian.

  • Ora saben wong duwe alamat email, sanajan iki bisa ditrima ing sawetara kahanan database. Uga, wong ngganti alamat email saka wektu kanggo wektu. (Sampeyan luwih akeh babagan stabilitas tombol mengko.)
  • Simbol saham ISIN diganti saka wektu kanggo wektu, contone, simbol GOOG lan GOOGL ora kanthi akurat nggambarake reorganisasi perusahaan saka Google menyang Alphabet. Kadhangkala kebingungan bisa muncul, kaya TWTR lan TWTRQ, sawetara investor salah tuku sing terakhir sajrone IPO Twitter.
  • Nomer Keamanan Sosial mung digunakake dening warga AS, duwe watesan privasi, lan digunakake maneh sawise mati. Kajaba iku, sawise nyolong dokumen, wong bisa njaluk nomer anyar. Pungkasan, nomer sing padha bisa ngenali wong lan pengenal pajak penghasilan.
  • Kode pos minangka pilihan sing ora apik kanggo kutha. Sawetara kutha duwe indeks umum, utawa kosok balene, ana sawetara indeks ing sawijining kutha.

5.4 Tombol Ponggawa

Amarga kunci kasebut minangka kolom kanthi nilai unik ing saben baris, salah sawijining cara kanggo nggawe yaiku ngapusi - sampeyan bisa nulis nilai unik fiktif ing saben baris. Iki minangka kunci buatan: kode sing diciptakake kanggo ngrujuk data utawa obyek.

Penting banget yen kode kasebut digawe saka database dhewe lan ora dingerteni sapa wae kajaba pangguna database kasebut. Iki sing mbedakake tombol gawean saka tombol alam standar.

Nalika tombol alami duwe kauntungan kanggo nglindhungi baris duplikat utawa ora konsisten ing tabel, tombol gawean migunani amarga luwih gampang kanggo manungsa utawa sistem liyane kanggo ngrujuk menyang baris, lan nyepetake panelusuran lan gabung amarga ora nggunakake. string (utawa multi-kolom) mbandhingaké tombol.

Pengganti

Tombol gawean digunakake minangka jangkar - ora ketompo carane aturan lan kolom diganti, siji baris tansah bisa dikenali ing cara sing padha. Tombol buatan sing digunakake kanggo tujuan iki diarani "kunci pengganti" lan mbutuhake perhatian khusus. Kita bakal nimbang surrogates ing ngisor iki.

Tombol gawean non-pengganti migunani kanggo referensi baris saka njaba database. Tombol gawean sedhela ngenali data utawa obyek: bisa ditemtokake minangka URL, ditempelake ing invoice, didikte liwat telpon, dijupuk saka bank, utawa dicithak ing piring lisensi. (Plat lisensi mobil minangka kunci alami kanggo kita, nanging dirancang dening pemerintah minangka kunci buatan.)

Tombol sintetis kudu dipilih kanthi nimbang cara transmisi kanggo nyilikake kesalahan ketik lan kesalahan. Perlu dicathet yen tombol kasebut bisa diucapake, diwaca dicithak, dikirim liwat SMS, maca tulisan tangan, diketik saka keyboard lan diselehake ing URL. Kajaba iku, sawetara tombol buatan, kayata nomer kertu kredit, ngemot checksum supaya yen ana kesalahan tartamtu, paling ora bisa diakoni.

Tuladha:

  • Kanggo piring lisensi AS, ana aturan babagan panggunaan karakter sing ambigu, kayata O lan 0.
  • Rumah sakit lan apotek kudu ati-ati, amarga tulisan tangan dokter.
  • Apa sampeyan ngirim kode konfirmasi liwat pesen teks? Aja ngluwihi set karakter GSM 03.38.
  • Beda karo Base64, sing ngode data byte sing sewenang-wenang, Base32 nggunakake set karakter winates sing trep kanggo digunakake lan ditangani dening manungsa ing sistem komputer lawas.
  • Proquints minangka pengenal sing bisa diwaca, bisa ditulis, lan bisa diucapake. Iki minangka pro-nouncable QUINT-uplet saka konsonan lan vokal sing ora dingerteni.

Elinga yen sanalika sampeyan ngenalake kunci buatan menyang jagad iki, wong bakal mulai menehi perhatian khusus. Cukup deleng piring lisensi "maling" utawa ing sistem kanggo nggawe pengenal sing bisa diucapake, sing wis dadi generator kutukan otomatis sing kondhang.

Sanajan kita mbatesi tombol angka, ana tabu kaya lantai telulas. Nalika proquints duwe kapadhetan informasi sing luwih dhuwur saben suku kata sing diucapake, angka uga apik ing pirang-pirang cara: ing URL, pin-keyboard, lan cathetan tulisan tangan, anggere panampa ngerti kuncine mung angka.

Nanging, elinga yen sampeyan ora kudu nggunakake urutan urutan ing tombol angka umum, amarga iki ngidini sampeyan nggoleki sumber daya (/videos/1.mpeg, /videos/2.mpeg, lan liya-liyane) lan uga bocor informasi babagan nomer kasebut. data. Superimpose net Feistel ing urutan nomer lan ngreksa uniqueness nalika ndhelikake urutan nomer.

Siji-sijine argumentasi kanggo ngumumake kunci tambahan yaiku saben sing anyar nggawa indeks unik liyane lan nambah biaya nulis ing meja. Mesthi, gumantung carane penting bener data kanggo sampeyan, nanging, paling kamungkinan, tombol isih kudu diumumake.

Sampeyan uga kudu ngumumake sawetara tombol buatan, yen ana. Contone, organisasi duwe calon kerja (Pelamar) lan karyawan (Karyawan). Saben pegawe tau dadi calon, lan nuduhake calon kanthi pengenal dhewe, sing uga kudu dadi kunci karyawan. Conto liyane, sampeyan bisa nyetel id karyawan lan jeneng login minangka rong tombol ing Karyawan.

5.5 Kunci pengganti

Kaya sing wis kasebut, jinis kunci buatan sing penting diarani "kunci pengganti". Ora perlu ringkes lan passable kaya tombol gawean liyane, nanging digunakake minangka label internal sing tansah ngenali senar. Iki digunakake ing SQL, nanging aplikasi kasebut ora ngakses kanthi jelas.

Yen sampeyan wis kenal karo kolom sistem PostgreSQL, sampeyan bisa mikirake pengganti meh minangka parameter implementasi database (kaya ctid), sing ora nate owah. Nilai pengganti dipilih sapisan saben baris lan ora bakal diganti.

Tombol pengganti apik banget minangka kunci manca, lan alangan runtun kudu ditemtokake ON UPDATE RESTRICTkanggo cocog karo immutability pengganti.

Ing sisih liya, kunci asing kanggo kunci sing dienggo bareng umum kudu ditandhani nganggo ON UPDATE CASCADE, kanggo menehi keluwesan maksimal. Nganyari runtun mlaku ing tingkat isolasi sing padha karo transaksi ing saubengé, mula aja kuwatir babagan masalah konkurensi - database bakal apik yen sampeyan milih tingkat isolasi sing ketat.

Aja nggawe kunci pengganti "alami". Sawise sampeyan nuduhake nilai kunci pengganti kanggo pangguna pungkasan, utawa luwih elek, ayo padha nggarap nilai kasebut (utamane liwat panelusuran), sampeyan kanthi efektif menehi nilai kunci kasebut. Banjur tombol sing ditampilake saka database sampeyan bisa dadi kunci alami ing database wong liya.

Meksa sistem eksternal nggunakake tombol buatan liyane sing dirancang khusus kanggo transmisi ngidini kita ngganti tombol kasebut yen perlu kanggo nyukupi kabutuhan sing ganti, nalika njaga integritas referensial internal karo pengganti.

Tambah otomatis INT / BIGINT

Panggunaan sing paling umum kanggo kunci pengganti yaiku kolom "bigserial" sing nambah otomatis , uga dikenal minangka IDENTITAS . (Nyatane, PostgreSQL 10 saiki ndhukung konstruk IDENTITAS, kaya Oracle, deleng CREATE TABLE.)

Nanging, aku percaya yen integer nambah otomatis minangka pilihan sing ora apik kanggo kunci pengganti. Panemu iki ora populer, mula aku nerangake.

Kekurangan saka tombol serial:

  • Yen kabeh urutan diwiwiti ing 1 lan tambah akeh, larik saka tabel beda bakal duwe nilai kunci sing padha. Opsi iki ora becik, luwih becik nggunakake set tombol sing dipisahake ing tabel, supaya, contone, pitakon ora bisa ora sengaja mbingungake konstanta JOINlan ngasilake asil sing ora dikarepake. (Utawa, kanggo mesthekake yen ora ana persimpangan, siji bisa mbangun saben urutan saka kelipatan prima beda, nanging iki bakal rada laborious.)
  • Telpon nextval() kanggo ngasilake urutan ing asil SQL sing disebarake saiki ing kabeh sistem ora ukurane apik.
  • Ngonsumsi data saka database sing uga nggunakake kunci urutan bakal nyebabake konflik amarga nilai urutan ora unik ing kabeh sistem.
  • Saka sudut pandang filosofis, paningkatan nomer urut-urutan digandhengake karo sistem lawas ing ngendi urutan garis kasebut diwenehake. Yen sampeyan saiki pengin supaya baris, banjur nindakake kanthi tegas karo kolom stempel wektu utawa soko sing cocog karo data sampeyan. Yen ora, wangun normal pisanan dilanggar.
  • Alasan sing lemah, nanging pengenal sing cendhak iki nggodha kanggo ngandhani wong liya.

UUID

Ayo goleki opsi liyane: nggunakake integer gedhe (128-bit) sing digawe miturut pola acak. Algoritma kanggo ngasilake pengenal unik universal kasebut (UUIDs) duwe kemungkinan sing sithik banget kanggo milih nilai sing padha kaping pindho, sanajan mlaku ing rong prosesor sing beda ing wektu sing padha.

Yen ngono, UUID katon kaya pilihan alami kanggo digunakake minangka kunci pengganti, ta? Yen sampeyan pengin menehi label baris kanthi cara sing unik, mula ora ana sing bisa ngalahake label unik!

Dadi, kenapa ora kabeh wong nggunakake ing PostgreSQL? Ana sawetara alasan contrived kanggo iki lan siji logis sing bisa digarap, lan aku bakal saiki benchmarks kanggo ilustrasi titik sandi.

Kaping pisanan, aku bakal ngomong babagan alasan sing ora jelas. Sawetara wong mikir yen UUID minangka senar amarga ditulis ing notasi heksadesimal tradisional kanthi garis: 5bd68e64-ff52-4f54-ace4-3cd9161c8b7f. Pancen, sawetara database ora duwe kompak (128-dicokot) jinis uuid, nanging PostgreSQL lan duwe ukuran loro bigint, IE dibandhingake karo jumlah informasi liyane ing database, overhead dijarno.

UUID uga dituduh ora adil minangka rumit, nanging sapa sing bakal ngucapake, ngetik, utawa maca? We ngandika iku ndadekake pangertèn kanggo tombol Ponggawa ditampilake, nanging ora ana siji (kanthi definisi) kudu ndeleng surrogate UUID. Sampeyan bisa uga yen UUID bakal ditangani dening pangembang sing nglakokake perintah SQL ing psql kanggo debug sistem, nanging babagan iki. Lan pangembang uga bisa ngrujuk strings nggunakake tombol luwih trep, yen padha diwenehi.

Masalah nyata karo UUID yaiku nilai kanthi acak banget nyebabake amplifikasi nulis amarga kaca lengkap nulis menyang log nulis (WAL) . Nanging, degradasi kinerja pancen gumantung marang algoritma generasi UUID.

Ayo ngukur amplifikasi nulis . Bener, masalah kasebut ana ing sistem file lawas. Nalika PostgreSQL nulis menyang disk, ngganti "kaca" ing disk. Yen sampeyan mateni daya komputer, umume sistem file isih bakal nglaporake tulisan sing sukses sadurunge data disimpen kanthi aman ing disk. Yen PostgreSQL naif ngerteni tumindak kasebut wis rampung, mula database bakal rusak sajrone boot sistem sabanjure.

Wiwit PostgreSQL ora bisa dipercaya paling sistem operasi / filesystems / konfigurasi disk kanggo nyedhiyani lampahing, database nyimpen negara lengkap kaca disk diganti menyang log nulis-ahead sing bisa digunakake kanggo mbalekake saka kamungkinan kacilakan. Ngindeks nilai-nilai kanthi acak kaya UUID biasane nglibatake pirang-pirang kaca disk sing beda-beda lan ngasilake ukuran kaca lengkap (biasane 4 utawa 8 KB) ditulis menyang WAL kanggo saben entri anyar. Iki sing diarani full-page write (full-page write, FPW).

Sawetara algoritma generasi UUID (kayata "snowflake" Twitter utawa uuid_generate_v1 () ing extension uuid-ossp PostgreSQL) ngasilake nilai tambah monoton ing saben mesin. Pendekatan iki nggabungake nulis dadi luwih sithik kaca disk lan nyuda FPW.

5.6 Kesimpulan lan rekomendasi

Saiki kita wis ndeleng macem-macem jinis kunci lan panggunaane, aku pengin dhaptar rekomendasi kanggo nggunakake ing database sampeyan.

Kanggo saben tabel:

  • Netepake lan wara-wara kabeh tombol alam.
  • Gawe kunci pengganti <table_name>_idsaka jinis UUID kanthi nilai standar uuid_generate_v1(). Sampeyan bisa malah menehi tandha minangka kunci utama. Yen sampeyan nambahake jeneng tabel kanggo pengenal iki, iki bakal nyederhanakake JOIN, i.e. nampa JOIN foo USING (bar_id)tinimbang JOIN foo ON (foo.bar_id = bar.id). Aja pass tombol iki kanggo klien lan aja mbukak ing njaba database ing kabeh.
  • Kanggo tabel penengah sing ngliwati JOIN, wara-wara kabeh kolom kunci asing minangka kunci utama gabungan siji.
  • Opsional, tambahake kunci buatan sing bisa digunakake ing URL utawa indikasi referensi senar liyane. Gunakake kothak Feistel utawa pg_hashids kanggo nutupi otomatis incrementing integer.
  • Nemtokake kendala runtun ON UPDATE RESTRICTnggunakake UUID pengganti minangka kunci asing lan kanggo kunci asing buatan ON UPDATE CASCADE. Pilih tombol alami adhedhasar logika sampeyan dhewe.

Pendekatan iki njamin stabilitas tombol internal nalika ngidini lan malah nglindhungi tombol alami. Kajaba iku, tombol gawean katon ora dadi ditempelake apa-apa. Sawise ngerti kabeh kanthi bener, sampeyan ora bisa digantung mung ing "tombol utama" lan nggunakake kabeh kemungkinan nggunakake tombol.