Kunci dalam database

5.1 Pendahuluan

Internet penuh dengan ajaran dogmatis tentang bagaimana kunci harus dipilih dan digunakan dalam database relasional. Terkadang perselisihan bahkan berubah menjadi holivar: haruskah kunci alami atau buatan digunakan? Bilangan bulat atau UUID kenaikan otomatis?

Setelah membaca enam puluh empat artikel, membolak-balik bagian dari lima buku, dan mengajukan banyak pertanyaan tentang IRC dan StackOverflow, saya (Joe "begriffs" Nelson, penulis artikel asli) tampaknya telah menyatukan potongan-potongan teka-teki itu dan sekarang dapat mendamaikan musuh. Banyak perselisihan kunci sebenarnya muncul dari kesalahpahaman tentang sudut pandang orang lain.

Mari kita pisahkan masalahnya dan satukan kembali di akhir. Pertama, mari kita ajukan pertanyaan - apa itu "kunci"?

Mari lupakan sejenak tentang kunci utama, kami tertarik pada ide yang lebih umum. Kunci adalah kolom (kolom) atau kolom yang tidak memiliki nilai duplikat dalam baris . Selain itu, kolom harus unik secara tak tereduksi, yaitu tidak ada subhimpunan kolom yang memiliki keunikan ini.

Tapi pertama-tama, beberapa teori:

kunci utama

Kunci utamalangsung digunakan untuk mengidentifikasi baris dalam tabel. Itu harus mematuhi batasan berikut:

• Kunci utama harus unik sepanjang waktu.
• Itu harus selalu ada di tabel dan memiliki nilai.
• Seharusnya tidak sering mengubah nilainya. Idealnya, itu tidak mengubah nilai sama sekali .

Biasanya, primary key mewakili satu kolom dari tabel, tetapi juga bisa menjadi kunci komposit yang terdiri dari beberapa kolom.

Kunci komposit

Kunci Kustom- kombinasi atribut (kolom) yang secara unik mengidentifikasi setiap baris tabel. Itu bisa semua kolom, dan beberapa, dan satu. Dalam hal ini, baris yang berisi nilai atribut tersebut tidak boleh diulang.

Kunci Potensial

kunci kandidat- mewakili kunci komposit minimum dari relasi (tabel), yaitu sekumpulan atribut yang memenuhi sejumlah kondisi:

• Irreducibility : Tidak dapat direduksi, berisi kumpulan atribut seminimal mungkin.
• Keunikan : Itu harus memiliki nilai unik terlepas dari perubahan baris.
• Kehadiran nilai : Itu tidak boleh memiliki nilai null, yaitu harus memiliki nilai.

5.2 Kasus menarik dari kunci primer

Apa yang baru saja kita sebut "kunci" di bagian sebelumnya biasanya disebut sebagai "kunci kandidat". Istilah "kandidat" menyiratkan bahwa semua kunci tersebut bersaing untuk mendapatkan peran kehormatan "kunci utama" (kunci utama), dan sisanya diberi "kunci alternatif" (kunci alternatif).

Butuh beberapa saat untuk implementasi SQL untuk mengatasi ketidaksesuaian antara kunci dan model relasional, dan database paling awal diarahkan pada konsep kunci primer tingkat rendah. Kunci utama dalam database semacam itu diperlukan untuk mengidentifikasi lokasi fisik baris pada media penyimpanan berurutan. Begini cara Joe Celko menjelaskannya:

Istilah "kunci" berarti kunci penyortiran file, yang diperlukan untuk melakukan operasi pemrosesan apa pun pada sistem file berurutan. Satu set kartu berlubang dibaca dalam satu dan hanya satu urutan; tidak mungkin untuk kembali. Tape drive awal meniru perilaku yang sama dan tidak mengizinkan akses dua arah. Artinya, Sybase SQL Server asli diharuskan untuk "memundurkan" tabel ke awal untuk membaca baris sebelumnya.

Dalam SQL modern, Anda tidak perlu fokus pada representasi fisik informasi, hubungan model tabel, dan urutan baris internal tidak penting sama sekali. Namun, bahkan sekarang SQL server secara default membuat indeks berkerumun untuk kunci primer dan, menurut tradisi lama, mengatur urutan baris secara fisik.

Di sebagian besar basis data, kunci utama adalah sesuatu dari masa lalu, dan memberikan sedikit lebih dari sekadar refleksi atau lokasi fisik. Misalnya, dalam tabel PostgreSQL, mendeklarasikan primary key secara otomatis memberlakukan batasan NOT NULLdan menetapkan foreign key default. Selain itu, primary key adalah kolom pilihan untuk operator JOIN.

Kunci utama tidak mengesampingkan kemungkinan mendeklarasikan kunci lainnya. Pada saat yang sama, jika tidak ada kunci yang ditetapkan sebagai kunci utama, tabel akan tetap berfungsi dengan baik. Petir, bagaimanapun, tidak akan menyerang Anda.

5.3 Menemukan kunci alami

Kunci yang dibahas di atas disebut "alami" karena merupakan properti dari objek yang dimodelkan yang menarik dengan sendirinya, bahkan jika tidak ada yang ingin membuat kunci darinya.

Hal pertama yang harus diingat saat memeriksa tabel untuk kemungkinan kunci alami adalah mencoba untuk tidak terlalu pintar. Pengguna sqlvogel di StackExchange memberikan saran berikut:

Beberapa orang mengalami kesulitan memilih kunci "alami" karena mereka datang dengan situasi hipotetis di mana kunci tertentu mungkin tidak unik. Mereka tidak mengerti arti sebenarnya dari tugas itu. Arti dari key adalah untuk mendefinisikan aturan yang menurutnya atribut pada waktu tertentu harus dan selalu unik dalam tabel tertentu. Tabel tersebut berisi data dalam konteks yang spesifik dan dipahami dengan baik (dalam "area subjek" atau "area wacana"), dan satu-satunya makna adalah penerapan batasan di area tersebut.

Praktik menunjukkan bahwa perlu untuk memperkenalkan batasan kunci ketika kolom unik dengan nilai yang tersedia dan akan tetap demikian dalam skenario yang memungkinkan. Dan jika perlu, batasan dapat dihapus (jika ini mengganggu Anda, maka di bawah ini kami akan berbicara tentang stabilitas kunci.)

Misalnya, basis data anggota klub hobi mungkin memiliki keunikan dalam dua kolom - first_name, last_name. Dengan sejumlah kecil data, duplikat tidak mungkin terjadi, dan sebelum konflik nyata muncul, cukup masuk akal untuk menggunakan kunci seperti itu.

Saat basis data tumbuh dan volume informasi meningkat, memilih kunci alami bisa menjadi lebih sulit. Data yang kami simpan merupakan penyederhanaan realitas eksternal, dan tidak mengandung beberapa aspek yang membedakan objek di dunia, seperti koordinatnya yang berubah seiring waktu. Jika suatu benda tidak memiliki kode, bagaimana Anda bisa membedakan dua kaleng minuman atau dua kotak oatmeal selain dari penataan ruangnya atau sedikit perbedaan berat atau kemasan?

Itulah sebabnya badan standardisasi membuat dan menerapkan tanda khusus pada produk. Kendaraan dicap dengan Nomor Identifikasi Kendaraan (VIN) , buku dicetak dengan ISBN , dan kemasan makanan memiliki UPC . Anda mungkin keberatan bahwa angka-angka ini tampaknya tidak alami. Jadi mengapa saya menyebutnya kunci alami?

Kealamian atau artifisial dari properti unik dalam database relatif terhadap dunia luar. Kunci yang artifisial ketika dibuat di badan standar atau lembaga pemerintah menjadi alami bagi kita, karena menjadi standar di seluruh dunia dan / atau dicetak pada benda.

Ada banyak standar industri, publik, dan internasional untuk berbagai mata pelajaran, termasuk mata uang, bahasa, instrumen keuangan, bahan kimia, dan diagnosis medis. Berikut adalah beberapa nilai yang sering digunakan sebagai kunci alami:

• Kode negara ISO 3166
• Kode bahasa ISO 639
• Kode mata uang menurut ISO 4217
• Simbol saham ISIN
• UPC/EAN, VIN, GTIN, ISBN
• nama masuk
• alamat email
• nomor kamar
• alamat mac jaringan
• lintang, bujur untuk titik-titik di permukaan bumi

Saya merekomendasikan mendeklarasikan kunci bila memungkinkan dan masuk akal, bahkan mungkin beberapa kunci per tabel. Tetapi ingat bahwa semua hal di atas mungkin memiliki pengecualian.

• Tidak semua orang memiliki alamat email, meskipun hal ini dapat diterima dalam beberapa kondisi database. Juga, orang mengubah alamat email mereka dari waktu ke waktu. (Lebih lanjut tentang stabilitas kunci nanti.)
• Simbol saham ISIN berubah dari waktu ke waktu, misalnya simbol GOOG dan GOOGL tidak secara akurat menggambarkan reorganisasi perusahaan dari Google ke Alphabet. Terkadang kebingungan dapat muncul, seperti TWTR dan TWTRQ, beberapa investor salah membeli yang terakhir selama IPO Twitter.
• Nomor Jaminan Sosial hanya digunakan oleh warga negara AS, memiliki batasan privasi, dan digunakan kembali setelah kematian. Selain itu, setelah pencurian dokumen, orang bisa mendapatkan nomor baru. Terakhir, nomor yang sama dapat mengidentifikasi orang dan pengidentifikasi pajak penghasilan.
• Kode pos adalah pilihan yang buruk untuk kota. Beberapa kota memiliki indeks yang sama, atau sebaliknya, terdapat beberapa indeks dalam satu kota.

5.4 Kunci buatan

Mengingat kuncinya adalah kolom dengan nilai unik di setiap baris, salah satu cara untuk membuatnya adalah dengan menipu - Anda dapat menulis nilai unik fiktif di setiap baris. Ini adalah kunci buatan: kode yang ditemukan digunakan untuk merujuk ke data atau objek.

Sangat penting bahwa kode dihasilkan dari basis data itu sendiri dan tidak diketahui siapa pun kecuali pengguna basis data. Inilah yang membedakan kunci buatan dari kunci alami standar.

Sementara kunci alami memiliki keuntungan untuk melindungi baris duplikat atau tidak konsisten dalam tabel, kunci buatan berguna karena mempermudah manusia atau sistem lain untuk merujuk ke baris, dan mempercepat pencarian dan penggabungan karena tidak menggunakan string (atau multi-kolom) perbandingan kunci.

Pengganti

Kunci buatan digunakan sebagai jangkar - tidak peduli bagaimana aturan dan kolom berubah, satu baris selalu dapat diidentifikasi dengan cara yang sama. Kunci buatan yang digunakan untuk tujuan ini disebut "kunci pengganti" dan membutuhkan perhatian khusus. Kami akan mempertimbangkan pengganti di bawah ini.

Kunci buatan non-pengganti berguna untuk mereferensikan baris dari luar database. Kunci buatan secara singkat mengidentifikasi data atau objek: dapat ditentukan sebagai URL, dilampirkan pada faktur, didiktekan melalui telepon, diperoleh dari bank, atau dicetak pada pelat nomor. (Plat nomor mobil adalah kunci alami bagi kami, tetapi dirancang oleh pemerintah sebagai kunci buatan.)

Kunci sintetis harus dipilih dengan mempertimbangkan kemungkinan sarana transmisi untuk meminimalkan kesalahan ketik dan kesalahan. Perlu dicatat bahwa kunci dapat diucapkan, dibaca, dicetak, dikirim melalui SMS, dibaca tulisan tangan, diketik dari keyboard dan disematkan di URL. Selain itu, beberapa kunci buatan, seperti nomor kartu kredit, berisi checksum sehingga jika terjadi kesalahan tertentu, setidaknya dapat dikenali.

Contoh:

• Untuk pelat nomor AS, ada aturan tentang penggunaan karakter ambigu, seperti O dan 0.
• Rumah sakit dan apotek harus sangat berhati-hati, mengingat tulisan tangan dokter.
• Apakah Anda mengirim kode konfirmasi melalui pesan teks? Jangan melampaui rangkaian karakter GSM 03.38.
• Tidak seperti Base64, yang menyandikan data byte arbitrer, Base32 menggunakan rangkaian karakter terbatas yang mudah digunakan dan ditangani oleh manusia pada sistem komputer lama.
• Proquint adalah pengidentifikasi yang dapat dibaca, ditulisi, dan diucapkan. Ini adalah QUINT-uplet PRO-nouncable dari konsonan dan vokal yang dipahami secara jelas.

Perlu diingat bahwa segera setelah Anda memperkenalkan kunci buatan Anda ke dunia, anehnya orang akan mulai memberikan perhatian khusus padanya. Lihat saja pelat nomor "pencuri" atau sistem untuk membuat pengenal yang dapat diucapkan, yang telah menjadi pembuat kutukan otomatis yang terkenal.

Bahkan jika kita membatasi diri pada kunci numerik, ada pantangan seperti lantai tiga belas. Sementara proquint memiliki kepadatan informasi yang lebih tinggi per suku kata yang diucapkan, angka juga baik dalam banyak hal: di URL, pin-keyboard, dan catatan tulisan tangan, selama penerima tahu kuncinya hanya angka.

Namun, harap perhatikan bahwa Anda tidak boleh menggunakan urutan berurutan dalam kunci numerik publik, karena ini memungkinkan Anda mengobrak-abrik sumber daya (/videos/1.mpeg, /videos/2.mpeg, dan seterusnya) dan juga membocorkan informasi tentang nomor tersebut data. Tempatkan jaring Feistel pada urutan angka dan pertahankan keunikan sambil menyembunyikan urutan angka.

Satu-satunya argumen yang menentang deklarasi kunci tambahan adalah bahwa setiap kunci baru membawa serta indeks unik lainnya dan meningkatkan biaya penulisan ke tabel. Tentu saja, itu tergantung pada seberapa penting kebenaran data bagi Anda, tetapi, kemungkinan besar, kuncinya tetap harus dideklarasikan.

Perlu juga mendeklarasikan beberapa kunci buatan, jika ada. Misalnya, sebuah organisasi memiliki calon pekerjaan (Pelamar) dan karyawan (Employees). Setiap karyawan pernah menjadi kandidat, dan merujuk ke kandidat dengan pengenal mereka sendiri, yang juga harus menjadi kunci karyawan. Contoh lain, Anda dapat menetapkan id karyawan dan nama login sebagai dua kunci di Karyawan.

5.5 Kunci pengganti

Seperti yang telah disebutkan, jenis kunci buatan yang penting disebut "kunci pengganti". Tidak perlu ringkas dan lumayan seperti kunci buatan lainnya, tetapi digunakan sebagai label internal yang selalu mengidentifikasi string. Ini digunakan dalam SQL, tetapi aplikasi tidak secara eksplisit mengaksesnya.

Jika Anda terbiasa dengan kolom sistem PostgreSQL, Anda dapat menganggap pengganti hampir sebagai parameter implementasi basis data (seperti ctid), yang, bagaimanapun, tidak pernah berubah. Nilai pengganti dipilih sekali per baris dan tidak pernah diubah setelahnya.

Kunci pengganti sangat bagus sebagai kunci asing, dan batasan kaskade harus ditentukan ON UPDATE RESTRICTagar sesuai dengan kekekalan pengganti.

Di sisi lain, kunci asing untuk kunci yang dibagikan secara publik harus ditandai dengan ON UPDATE CASCADE, untuk memberikan fleksibilitas maksimum. Pembaruan kaskade berjalan pada tingkat isolasi yang sama dengan transaksi di sekitarnya, jadi jangan khawatir tentang masalah konkurensi - database akan baik-baik saja jika Anda memilih tingkat isolasi yang ketat.

Jangan membuat kunci pengganti "alami". Setelah Anda menunjukkan nilai kunci pengganti kepada pengguna akhir, atau lebih buruk lagi, biarkan mereka bekerja dengan nilai tersebut (terutama melalui pencarian), Anda secara efektif memberikan nilai pada kunci tersebut. Kemudian kunci yang ditampilkan dari database Anda dapat menjadi kunci alami di database orang lain.

Memaksa sistem eksternal untuk menggunakan kunci buatan lain yang dirancang khusus untuk transmisi memungkinkan kita mengubah kunci tersebut sesuai kebutuhan untuk memenuhi kebutuhan yang berubah, sambil mempertahankan integritas referensial internal dengan pengganti.

Peningkatan otomatis INT/BIGINT

Penggunaan kunci pengganti yang paling umum adalah kolom "bigserial" yang bertambah secara otomatis , juga dikenal sebagai IDENTITY . (Faktanya, PostgreSQL 10 sekarang mendukung konstruksi IDENTITY, seperti halnya Oracle, lihat CREATE TABLE.)

Namun, saya percaya bahwa bilangan bulat yang bertambah secara otomatis adalah pilihan yang buruk untuk kunci pengganti. Pendapat ini tidak populer, jadi izinkan saya menjelaskan.

Kerugian dari kunci serial:

• Jika semua urutan dimulai dari 1 dan bertambah secara bertahap, maka baris dari tabel yang berbeda akan memiliki nilai kunci yang sama. Opsi ini tidak ideal, masih lebih baik menggunakan kumpulan kunci yang terpisah dalam tabel, sehingga, misalnya, kueri tidak dapat mengacaukan konstanta secara tidak sengaja JOINdan mengembalikan hasil yang tidak diharapkan. (Atau, untuk memastikan bahwa tidak ada persimpangan, seseorang dapat membangun setiap urutan dari kelipatan bilangan prima yang berbeda, tetapi ini akan agak melelahkan.)
• Panggilan nextval() untuk menghasilkan urutan dalam hasil SQL terdistribusi hari ini di seluruh sistem tidak diskalakan dengan baik.
• Mengonsumsi data dari database yang juga menggunakan kunci sekuensial akan mengakibatkan konflik karena nilai sekuensial tidak akan unik di seluruh sistem.
• Dari sudut pandang filosofis, peningkatan jumlah yang berurutan dikaitkan dengan sistem lama yang menyiratkan urutan garis. Jika sekarang Anda ingin mengurutkan baris, lakukan secara eksplisit dengan kolom stempel waktu atau sesuatu yang masuk akal dalam data Anda. Jika tidak, bentuk normal pertama dilanggar.
• Alasan lemah, tetapi pengidentifikasi singkat ini menggoda untuk memberi tahu seseorang.

UUID

Mari kita lihat opsi lain: menggunakan bilangan bulat besar (128-bit) yang dibuat menurut pola acak. Algoritme untuk menghasilkan pengidentifikasi unik universal (UUID) memiliki kemungkinan yang sangat rendah untuk memilih nilai yang sama dua kali, bahkan ketika dijalankan pada dua prosesor yang berbeda pada waktu yang sama.

Dalam hal ini, UUID tampak seperti pilihan alami untuk digunakan sebagai kunci pengganti, bukan? Jika Anda ingin memberi label baris dengan cara yang unik, tidak ada yang mengalahkan label unik!

Jadi mengapa tidak semua orang menggunakannya di PostgreSQL? Ada beberapa alasan yang dibuat-buat untuk ini dan satu alasan logis yang dapat diselesaikan, dan saya akan menyajikan tolok ukur untuk mengilustrasikan maksud saya.

Pertama, saya akan berbicara tentang alasan yang dibuat-buat. Beberapa orang berpikir bahwa UUID adalah string karena ditulis dalam notasi heksadesimal tradisional dengan tanda hubung: 5bd68e64-ff52-4f54-ace4-3cd9161c8b7f. Memang, beberapa database tidak memiliki tipe uuid yang kompak (128-bit), tetapi PostgreSQL memiliki dan memiliki ukuran dua bigint, yaitu, dibandingkan dengan jumlah informasi lain dalam database, overhead dapat diabaikan.

UUID juga secara tidak adil dituduh tidak praktis, tetapi siapa yang akan mengucapkannya, mengetiknya, atau membacanya? Kami mengatakan bahwa masuk akal untuk menampilkan kunci buatan, tetapi tidak seorang pun (menurut definisi) harus melihat UUID pengganti. Mungkin UUID akan ditangani oleh pengembang yang menjalankan perintah SQL di psql untuk men-debug sistem, tapi hanya itu saja. Dan pengembang juga dapat merujuk ke string menggunakan kunci yang lebih nyaman, jika diberikan.

Masalah sebenarnya dengan UUID adalah bahwa nilai yang sangat acak menyebabkan amplifikasi tulis karena penulisan halaman penuh ke log tulis-depan (WAL) . Namun, penurunan kinerja sebenarnya bergantung pada algoritma pembuatan UUID.

Mari kita ukur amplifikasi tulis . Sebenarnya, masalahnya ada pada sistem file lama. Ketika PostgreSQL menulis ke disk, itu mengubah "halaman" pada disk. Jika Anda mematikan daya komputer, sebagian besar sistem file masih akan melaporkan penulisan yang berhasil sebelum data disimpan dengan aman di disk. Jika PostgreSQL secara naif menganggap tindakan seperti itu selesai, database akan rusak selama boot sistem berikutnya.

Karena PostgreSQL tidak dapat mempercayai sebagian besar sistem operasi/sistem file/konfigurasi disk untuk memberikan kontinuitas, database menyimpan status penuh dari halaman disk yang diubah ke log tulis yang dapat digunakan untuk memulihkan dari kemungkinan crash. Pengindeksan nilai yang sangat acak seperti UUID biasanya melibatkan sekumpulan halaman disk yang berbeda dan menghasilkan ukuran halaman penuh (biasanya 4 atau 8 KB) yang ditulis ke WAL untuk setiap entri baru. Inilah yang disebut penulisan halaman penuh (penulisan halaman penuh, FPW).

Beberapa algoritma pembuatan UUID (seperti "kepingan salju" Twitter atau uuid_generate_v1() dalam ekstensi uuid-ossp PostgreSQL) menghasilkan nilai yang meningkat secara monoton pada setiap mesin. Pendekatan ini menggabungkan penulisan ke halaman disk yang lebih sedikit dan mengurangi FPW.

5.6 Kesimpulan dan rekomendasi

Sekarang kita telah melihat berbagai jenis kunci dan penggunaannya, saya ingin membuat daftar rekomendasi saya untuk menggunakannya di database Anda.

Untuk setiap tabel:

• Tentukan dan nyatakan semua kunci alami.
• Buat kunci pengganti <table_name>_idtipe UUID dengan nilai default uuid_generate_v1(). Anda bahkan dapat menandainya sebagai kunci utama. Jika Anda menambahkan nama tabel ke pengidentifikasi ini, ini akan disederhanakan JOIN, yaitu. menerima JOIN foo USING (bar_id)bukannya JOIN foo ON (foo.bar_id = bar.id). Jangan berikan kunci ini kepada klien dan jangan memaparkannya sama sekali di luar database.
• Untuk tabel perantara yang melewati JOIN, nyatakan semua kolom kunci asing sebagai satu kunci primer gabungan.
• Opsional, tambahkan kunci buatan yang dapat digunakan di URL atau indikasi referensi string lainnya. Gunakan kisi Feistel atau pg_hashids untuk menutupi bilangan bulat yang bertambah secara otomatis.
• Tentukan batasan kaskade ON UPDATE RESTRICTmenggunakan UUID pengganti sebagai kunci asing dan untuk kunci asing buatan ON UPDATE CASCADE. Pilih kunci alami berdasarkan logika Anda sendiri.

Pendekatan ini memastikan stabilitas kunci internal sekaligus memungkinkan dan bahkan melindungi kunci alami. Selain itu, kunci buatan yang terlihat tidak melekat pada apa pun. Setelah memahami semuanya dengan benar, Anda tidak dapat terpaku hanya pada "kunci utama" dan menggunakan semua kemungkinan menggunakan kunci.