Kunci dalam pangkalan data

5.1 Pengenalan

Internet penuh dengan ajaran dogmatik tentang cara kunci harus dipilih dan digunakan dalam pangkalan data hubungan. Kadang-kadang pertikaian malah bertukar menjadi holivar: patutkah kunci asli atau tiruan digunakan? Autokenaikan integer atau UUID?

Selepas membaca enam puluh empat artikel, menyelak bahagian lima buku, dan bertanya banyak soalan mengenai IRC dan StackOverflow, saya (Joe "begriffs" Nelson, pengarang artikel asal) nampaknya telah meletakkan kepingan teka-teki itu bersama-sama dan kini boleh mendamaikan musuh. Banyak pertikaian utama sebenarnya timbul daripada salah faham sudut pandangan orang lain.

Mari kita pisahkan masalah itu dan kumpulkan kembali pada akhirnya. Pertama, mari kita tanya soalan - apakah "kunci"?

Mari lupakan kunci utama seketika, kami berminat dengan idea yang lebih umum. Kunci ialah lajur (lajur) atau lajur yang tidak mempunyai nilai pendua dalam baris . Selain itu, lajur mestilah unik yang tidak dapat dikurangkan, iaitu tiada subset lajur yang mempunyai keunikan ini.

Tetapi pertama, beberapa teori:

kunci utama

Kunci utamadigunakan secara langsung untuk mengenal pasti baris dalam jadual. Ia mesti mematuhi sekatan berikut:

• Kunci utama mestilah unik sepanjang masa.
• Ia mesti sentiasa ada dalam jadual dan mempunyai nilai.
• Ia tidak sepatutnya menukar nilainya dengan kerap. Sebaik-baiknya, ia tidak boleh mengubah nilai sama sekali .

Biasanya, kunci utama mewakili satu lajur jadual, tetapi ia juga boleh menjadi kunci komposit yang terdiri daripada berbilang lajur.

Kunci komposit

Kunci Tersuai- gabungan atribut (lajur) yang mengenal pasti secara unik setiap baris jadual. Ia boleh menjadi semua lajur, dan beberapa, dan satu. Dalam kes ini, baris yang mengandungi nilai atribut ini tidak boleh diulang.

Kunci Potensi

kunci calon- mewakili kunci komposit minimum hubungan (jadual), iaitu, satu set atribut yang memenuhi beberapa syarat:

• Tidak boleh dikurangkan : Ia tidak boleh dikurangkan, ia mengandungi set atribut minimum yang mungkin.
• Keunikan : Ia mesti mempunyai nilai unik tanpa mengira perubahan baris.
• Kehadiran nilai : Ia mestilah tidak mempunyai nilai nol, iaitu ia mesti mempunyai nilai.

5.2 Kes ingin tahu kunci primer

Apa yang baru kami panggil "kunci" dalam bahagian sebelumnya biasanya dirujuk sebagai "kunci calon". Istilah "calon" membayangkan bahawa semua kunci tersebut bersaing untuk peranan kehormat "kunci utama" (kunci utama), dan selebihnya diberikan "kunci alternatif" (kunci ganti).

Ia mengambil sedikit masa untuk pelaksanaan SQL untuk mengatasi ketidakpadanan antara kunci dan model hubungan, dan pangkalan data terawal dituju kepada konsep peringkat rendah bagi kunci utama. Kunci utama dalam pangkalan data sedemikian diperlukan untuk mengenal pasti lokasi fizikal baris pada media storan berjujukan. Inilah cara Joe Celko menerangkannya:

Istilah "kunci" bermaksud kunci isihan fail, yang diperlukan untuk melaksanakan sebarang operasi pemprosesan pada sistem fail berjujukan. Satu set kad tebuk dibaca dalam satu dan hanya satu pesanan; adalah mustahil untuk kembali. Pemacu pita awal meniru gelagat yang sama dan tidak membenarkan akses dua arah. Iaitu, Sybase SQL Server yang asal diperlukan untuk "memundurkan" jadual ke permulaan untuk membaca baris sebelumnya.

Dalam SQL moden, anda tidak perlu menumpukan pada perwakilan fizikal maklumat, hubungan model jadual, dan susunan dalaman baris tidak penting sama sekali. Walau bagaimanapun, walaupun kini pelayan SQL secara lalai mencipta indeks berkelompok untuk kunci utama dan, mengikut tradisi lama, menyusun susunan baris secara fizikal.

Dalam kebanyakan pangkalan data, kunci utama adalah perkara yang telah berlalu, dan memberikan lebih sedikit daripada pantulan atau lokasi fizikal. Contohnya, dalam jadual PostgreSQL, mengisytiharkan kunci utama secara automatik menguatkuasakan kekangan NOT NULLdan mentakrifkan kunci asing lalai. Selain itu, kunci utama ialah lajur pilihan untuk pengendali JOIN.

Kunci utama tidak mengatasi kemungkinan mengisytiharkan kunci lain. Pada masa yang sama, jika tiada kunci ditetapkan sebagai utama, maka jadual masih akan berfungsi dengan baik. Kilat, dalam apa jua keadaan, tidak akan menyerang anda.

5.3 Mencari kunci semula jadi

Kekunci yang dibincangkan di atas dipanggil "semula jadi" kerana ia adalah sifat objek model yang menarik dalam diri mereka sendiri, walaupun tiada siapa yang mahu membuat kunci daripadanya.

Perkara pertama yang perlu diingat semasa memeriksa jadual untuk kemungkinan kunci semula jadi ialah cuba untuk tidak terlalu pintar. Pengguna sqlvogel pada StackExchange memberikan nasihat berikut:

Sesetengah orang mengalami kesukaran memilih kunci "semula jadi" kerana mereka menghasilkan situasi hipotetikal di mana kunci tertentu mungkin tidak unik. Mereka tidak memahami maksud tugas itu. Maksud kunci adalah untuk mentakrifkan peraturan mengikut mana atribut pada bila-bila masa mestilah dan akan sentiasa unik dalam jadual tertentu. Jadual mengandungi data dalam konteks khusus dan difahami dengan baik (dalam "kawasan subjek" atau "kawasan wacana"), dan satu-satunya makna ialah penerapan sekatan dalam kawasan tertentu itu.

Amalan menunjukkan bahawa adalah perlu untuk memperkenalkan kekangan utama apabila lajur adalah unik dengan nilai yang tersedia dan akan kekal begitu dalam senario yang mungkin. Dan jika perlu, sekatan boleh dialih keluar (jika ini mengganggu anda, maka di bawah kita akan bercakap tentang kestabilan utama.)

Sebagai contoh, pangkalan data ahli kelab hobi mungkin mempunyai keunikan dalam dua lajur - first_name, last_name. Dengan jumlah data yang kecil, pendua tidak mungkin berlaku, dan sebelum konflik sebenar timbul, agak munasabah untuk menggunakan kunci sedemikian.

Apabila pangkalan data berkembang dan jumlah maklumat meningkat, memilih kunci semula jadi boleh menjadi lebih sukar. Data yang kami simpan adalah penyederhanaan realiti luaran, dan tidak mengandungi beberapa aspek yang membezakan objek di dunia, seperti koordinatnya yang berubah dari semasa ke semasa. Jika objek tidak mempunyai sebarang kod, bagaimanakah anda boleh membezakan dua tin minuman atau dua kotak oatmeal selain daripada susunan ruangnya atau sedikit perbezaan dalam berat atau pembungkusan?

Itulah sebabnya badan penyeragaman mencipta dan menggunakan tanda tersendiri pada produk. Kenderaan dicop dengan Nombor Pengenalan Kenderaan (VIN) , buku dicetak dengan ISBN dan pembungkusan makanan mempunyai UPC . Anda mungkin membantah bahawa nombor ini tidak kelihatan semula jadi. Jadi mengapa saya memanggilnya kunci semula jadi?

Sifat semula jadi atau artificiality sifat unik dalam pangkalan data adalah relatif kepada dunia luar. Kunci yang tiruan apabila ia dicipta dalam badan piawai atau agensi kerajaan menjadi semula jadi kepada kami, kerana ia menjadi standard di seluruh dunia dan / atau dicetak pada objek.

Terdapat banyak piawaian industri, awam dan antarabangsa untuk pelbagai subjek, termasuk mata wang, bahasa, instrumen kewangan, bahan kimia dan diagnosis perubatan. Berikut adalah beberapa nilai yang sering digunakan sebagai kunci semula jadi:

• Kod negara ISO 3166
• Kod bahasa ISO 639
• Kod mata wang mengikut ISO 4217
• Simbol saham ISIN
• UPC/EAN, VIN, GTIN, ISBN
• nama log masuk
• alamat e-mel
• nombor bilik
• alamat mac rangkaian
• latitud, longitud untuk titik di permukaan Bumi

Saya syorkan untuk mengisytiharkan kunci apabila boleh dan munasabah, mungkin juga berbilang kunci setiap jadual. Tetapi ingat bahawa semua perkara di atas mungkin mempunyai pengecualian.

• Tidak semua orang mempunyai alamat e-mel, walaupun ini mungkin boleh diterima di bawah beberapa syarat pangkalan data. Selain itu, orang menukar alamat e-mel mereka dari semasa ke semasa. (Lebih lanjut mengenai kestabilan kunci kemudian.)
• Simbol saham ISIN berubah dari semasa ke semasa, sebagai contoh, simbol GOOG dan GOOGL tidak menerangkan dengan tepat penyusunan semula syarikat daripada Google kepada Alphabet. Kadangkala kekeliruan boleh timbul, seperti TWTR dan TWTRQ, sesetengah pelabur tersilap membeli yang kedua semasa IPO Twitter.
• Nombor Keselamatan Sosial hanya digunakan oleh warga AS, mempunyai sekatan privasi dan digunakan semula selepas kematian. Selain itu, selepas kecurian dokumen, orang ramai boleh mendapatkan nombor baru. Akhir sekali, nombor yang sama boleh mengenal pasti seseorang dan pengecam cukai pendapatan.
• Poskod adalah pilihan yang tidak baik untuk bandar. Sesetengah bandar mempunyai indeks biasa, atau sebaliknya, terdapat beberapa indeks dalam satu bandar.

5.4 Kekunci buatan

Memandangkan kuncinya ialah lajur dengan nilai unik dalam setiap baris, satu cara untuk menciptanya adalah dengan menipu - anda boleh menulis nilai unik rekaan dalam setiap baris. Ini adalah kunci buatan: kod ciptaan yang digunakan untuk merujuk kepada data atau objek.

Adalah sangat penting bahawa kod dijana daripada pangkalan data itu sendiri dan tidak diketahui oleh sesiapa kecuali pengguna pangkalan data. Inilah yang membezakan kunci tiruan daripada kunci semula jadi yang standard.

Walaupun kekunci semula jadi mempunyai kelebihan untuk melindungi daripada baris pendua atau tidak konsisten dalam jadual, kekunci tiruan berguna kerana ia memudahkan manusia atau sistem lain merujuk kepada baris tersebut dan ia mempercepatkan carian dan gabungan kerana ia tidak menggunakan rentetan (atau berbilang lajur) perbandingan. kunci.

Pengganti

Kekunci tiruan digunakan sebagai sauh - tidak kira bagaimana peraturan dan lajur berubah, satu baris sentiasa boleh dikenal pasti dengan cara yang sama. Kunci buatan yang digunakan untuk tujuan ini dipanggil "kunci pengganti" dan memerlukan perhatian khusus. Kami akan mempertimbangkan pengganti di bawah.

Kekunci tiruan bukan pengganti berguna untuk merujuk baris dari luar pangkalan data. Kunci tiruan secara ringkas mengenal pasti data atau objek: ia boleh ditentukan sebagai URL, dilampirkan pada invois, ditentukan melalui telefon, diperoleh daripada bank atau dicetak pada plat lesen. (Plat lesen kereta adalah kunci semula jadi bagi kami, tetapi direka oleh kerajaan sebagai kunci tiruan.)

Kekunci sintetik harus dipilih dengan mengambil kira cara penghantaran yang mungkin untuk meminimumkan kesilapan dan kesilapan. Perlu diingatkan bahawa kunci boleh dituturkan, dibaca dicetak, dihantar melalui SMS, dibaca tulisan tangan, ditaip dari papan kekunci dan dibenamkan dalam URL. Selain itu, beberapa kunci tiruan, seperti nombor kad kredit, mengandungi jumlah semak supaya jika ralat tertentu berlaku, ia sekurang-kurangnya boleh dikenali.

Contoh:

• Untuk plat lesen AS, terdapat peraturan tentang penggunaan aksara yang tidak jelas, seperti O dan 0.
• Hospital dan farmasi mesti berhati-hati, memandangkan tulisan tangan doktor.
• Adakah anda menghantar kod pengesahan melalui mesej teks? Jangan melampaui set aksara GSM 03.38.
• Tidak seperti Base64, yang mengekod data bait sewenang-wenangnya, Base32 menggunakan set aksara terhad yang mudah digunakan dan dikendalikan oleh manusia pada sistem komputer lama.
• Proquints ialah pengecam yang boleh dibaca, boleh ditulis dan boleh disebut. Ini adalah KUINT-uplet PRO-kata nama bagi konsonan dan vokal yang tidak jelas difahami.

Perlu diingat bahawa sebaik sahaja anda memperkenalkan kunci tiruan anda kepada dunia, peliknya orang akan mula memberikannya perhatian khusus. Lihat sahaja pada plat lesen "pencuri" atau pada sistem untuk mencipta pengecam yang boleh disebut, yang telah menjadi penjana sumpahan automatik yang terkenal.

Walaupun kita menghadkan diri kita kepada kunci berangka, terdapat pantang larang seperti tingkat tiga belas. Walaupun proquints mempunyai kepadatan maklumat yang lebih tinggi bagi setiap suku kata yang dituturkan, nombor juga baik dalam banyak cara: dalam URL, papan kekunci pin dan nota tulisan tangan, asalkan penerima tahu kuncinya hanyalah nombor.

Walau bagaimanapun, sila ambil perhatian bahawa anda tidak boleh menggunakan tertib berurutan dalam kekunci angka awam, kerana ini membolehkan anda menyelongkar sumber (/videos/1.mpeg, /videos/2.mpeg, dan sebagainya) dan juga membocorkan maklumat tentang nombor itu. data. Letakkan jaring Feistel pada urutan nombor dan kekalkan keunikan sambil menyembunyikan susunan nombor.

Satu-satunya hujah menentang pengisytiharan kunci tambahan ialah setiap kunci baharu membawa indeks unik lain dan meningkatkan kos penulisan ke jadual. Sudah tentu, ia bergantung pada betapa pentingnya ketepatan data kepada anda, tetapi, kemungkinan besar, kunci masih harus diisytiharkan.

Ia juga bernilai mengisytiharkan beberapa kunci tiruan, jika ada. Contohnya, sesebuah organisasi mempunyai calon pekerjaan (Pemohon) dan pekerja (Pekerja). Setiap pekerja pernah menjadi calon, dan merujuk kepada calon dengan pengecam mereka sendiri, yang juga harus menjadi kunci pekerja. Contoh lain, anda boleh menetapkan id pekerja dan nama log masuk sebagai dua kunci dalam Pekerja.

5.5 Kunci pengganti

Seperti yang telah disebutkan, jenis kunci buatan yang penting dipanggil "kunci pengganti". Ia tidak perlu ringkas dan boleh dilalui seperti kekunci buatan lain, tetapi digunakan sebagai label dalaman yang sentiasa mengenal pasti rentetan. Ia digunakan dalam SQL, tetapi aplikasi tidak mengaksesnya secara eksplisit.

Jika anda biasa dengan lajur sistem PostgreSQL, maka anda boleh menganggap pengganti hampir sebagai parameter pelaksanaan pangkalan data (seperti ctid), yang bagaimanapun, tidak pernah berubah. Nilai pengganti dipilih sekali setiap baris dan tidak pernah berubah selepas itu.

Kunci pengganti adalah hebat sebagai kunci asing, dan kekangan berlatarkan mesti ditentukan ON UPDATE RESTRICTuntuk memadankan kebolehubahan pengganti.

Sebaliknya, kunci asing kepada kunci yang dikongsi secara terbuka hendaklah ditandakan dengan ON UPDATE CASCADE, untuk memberikan fleksibiliti maksimum. Kemas kini berlatarkan berjalan pada tahap pengasingan yang sama seperti urus niaga sekeliling, jadi jangan risau tentang isu konkurensi - pangkalan data akan baik jika anda memilih tahap pengasingan yang ketat.

Jangan jadikan kunci pengganti "semula jadi". Sebaik sahaja anda menunjukkan nilai kunci pengganti kepada pengguna akhir, atau lebih teruk lagi, biarkan mereka bekerja dengan nilai tersebut (terutamanya melalui carian), anda secara berkesan memberikan kunci nilai. Kemudian kunci yang ditunjukkan daripada pangkalan data anda boleh menjadi kunci semula jadi dalam pangkalan data orang lain.

Memaksa sistem luaran menggunakan kunci tiruan lain yang direka khusus untuk penghantaran membolehkan kami menukar kunci tersebut mengikut keperluan untuk memenuhi keperluan yang berubah-ubah, sambil mengekalkan integriti rujukan dalaman dengan pengganti.

Autokenaikan INT/BIGINT

Penggunaan paling biasa untuk kunci pengganti ialah lajur "bersiri besar" yang meningkat secara automatik , juga dikenali sebagai IDENTITY . (Malah, PostgreSQL 10 kini menyokong konstruk IDENTITI, seperti Oracle, lihat CREATE TABLE.)

Walau bagaimanapun, saya percaya bahawa integer kenaikan automatik adalah pilihan yang tidak baik untuk kunci pengganti. Pendapat ini tidak popular, jadi saya jelaskan.

Kelemahan kunci bersiri:

• Jika semua jujukan bermula pada 1 dan meningkat secara berperingkat, maka baris daripada jadual yang berbeza akan mempunyai nilai kunci yang sama. Pilihan ini tidak sesuai, masih lebih baik untuk menggunakan set kekunci berpisah dalam jadual, supaya, sebagai contoh, pertanyaan tidak boleh mengelirukan pemalar secara tidak sengaja JOINdan mengembalikan hasil yang tidak dijangka. (Sebagai alternatif, untuk memastikan tiada persimpangan, seseorang boleh membina setiap jujukan daripada gandaan bilangan prima yang berbeza, tetapi ini agak menyusahkan.)
• Panggilan nextval() untuk menjana urutan dalam SQL yang diedarkan hari ini menyebabkan keseluruhan sistem tidak berskala dengan baik.
• Menggunakan data daripada pangkalan data yang turut menggunakan kunci berjujukan akan mengakibatkan konflik kerana nilai jujukan tidak akan unik merentas sistem.
• Dari sudut pandangan falsafah, pertambahan nombor secara berurutan dikaitkan dengan sistem lama di mana susunan baris tersirat. Jika anda kini ingin memesan baris, maka lakukan secara eksplisit dengan lajur cap masa atau sesuatu yang masuk akal dalam data anda. Jika tidak, bentuk normal pertama dilanggar.
• Alasan yang lemah, tetapi pengecam pendek ini menggoda untuk memberitahu seseorang.

UUID

Mari lihat pilihan lain: menggunakan integer besar (128-bit) yang dijana mengikut corak rawak. Algoritma untuk menjana pengecam unik universal (UUID) sedemikian mempunyai kebarangkalian yang sangat rendah untuk memilih nilai yang sama dua kali, walaupun semasa berjalan pada dua pemproses berbeza pada masa yang sama.

Dalam kes itu, UUID kelihatan seperti pilihan semula jadi untuk digunakan sebagai kunci pengganti, bukan? Jika anda ingin melabelkan baris dengan cara yang unik, maka tiada apa yang mengalahkan label unik!

Jadi mengapa tidak semua orang menggunakannya dalam PostgreSQL? Terdapat beberapa sebab yang direka untuk ini dan satu logik yang boleh diselesaikan, dan saya akan membentangkan tanda aras untuk menggambarkan maksud saya.

Pertama, saya akan bercakap tentang sebab-sebab yang tidak masuk akal. Sesetengah orang berpendapat bahawa UUID adalah rentetan kerana ia ditulis dalam tatatanda heksadesimal tradisional dengan sempang: 5bd68e64-ff52-4f54-ace4-3cd9161c8b7f. Malah, sesetengah pangkalan data tidak mempunyai jenis uuid padat (128-bit), tetapi PostgreSQL mempunyai dan mempunyai saiz dua bigint, iaitu, berbanding dengan jumlah maklumat lain dalam pangkalan data, overhed boleh diabaikan.

UUID juga dituduh secara tidak adil sebagai menyusahkan, tetapi siapa yang akan menyebutnya, menaipnya atau membacanya? Kami mengatakan bahawa adalah masuk akal untuk kunci tiruan ditunjukkan, tetapi tiada siapa (mengikut definisi) harus melihat UUID pengganti. Ada kemungkinan bahawa UUID akan ditangani oleh pembangun yang menjalankan perintah SQL dalam psql untuk menyahpepijat sistem, tetapi itu sahaja. Dan pembangun juga boleh merujuk kepada rentetan menggunakan kekunci yang lebih mudah, jika ia diberikan.

Masalah sebenar dengan UUID ialah nilai rawak tinggi membawa kepada penguatan menulis kerana penulisan halaman penuh ke log tulis ke hadapan (WAL) . Walau bagaimanapun, kemerosotan prestasi sebenarnya bergantung pada algoritma penjanaan UUID.

Mari kita ukur penguatan tulis . Sebenarnya, masalahnya adalah dalam sistem fail yang lebih lama. Apabila PostgreSQL menulis pada cakera, ia menukar "halaman" pada cakera. Jika anda mematikan kuasa komputer, kebanyakan sistem fail masih akan melaporkan penulisan yang berjaya sebelum data disimpan dengan selamat pada cakera. Jika PostgreSQL secara naif menganggap tindakan sedemikian telah selesai, maka pangkalan data akan rosak semasa but sistem seterusnya.

Memandangkan PostgreSQL tidak boleh mempercayai kebanyakan sistem pengendalian/sistem fail/konfigurasi cakera untuk menyediakan kesinambungan, pangkalan data menyimpan keadaan penuh halaman cakera yang diubah kepada log tulis ke hadapan yang boleh digunakan untuk pulih daripada kemungkinan ranap. Pengindeksan nilai rawak tinggi seperti UUID biasanya melibatkan sekumpulan halaman cakera yang berbeza dan menyebabkan saiz halaman penuh (biasanya 4 atau 8 KB) ditulis ke WAL untuk setiap entri baharu. Ini adalah apa yang dipanggil menulis halaman penuh (tulis halaman penuh, FPW).

Sesetengah algoritma penjanaan UUID (seperti "snowflake" Twitter atau uuid_generate_v1() dalam sambungan uuid-ossp PostgreSQL) menjana nilai yang meningkat secara monoton pada setiap mesin. Pendekatan ini menggabungkan penulisan ke dalam halaman cakera yang lebih sedikit dan mengurangkan FPW.

5.6 Kesimpulan dan cadangan

Memandangkan kita telah melihat pelbagai jenis kunci dan kegunaannya, saya ingin menyenaraikan cadangan saya untuk menggunakannya dalam pangkalan data anda.

Untuk setiap jadual:

• Tentukan dan isytiharkan semua kunci semula jadi.
• Buat kunci pengganti <table_name>_idjenis UUID dengan nilai lalai uuid_generate_v1(). Anda juga boleh menandakannya sebagai kunci utama. Jika anda menambah nama jadual pada pengecam ini, ini akan memudahkan JOIN, i.e. terima JOIN foo USING (bar_id)bukannya JOIN foo ON (foo.bar_id = bar.id). Jangan berikan kunci ini kepada pelanggan dan jangan dedahkannya di luar pangkalan data sama sekali.
• Untuk jadual perantaraan yang melalui JOIN, isytiharkan semua lajur kunci asing sebagai kunci utama komposit tunggal.
• Secara pilihan, tambahkan kunci buatan yang boleh digunakan dalam URL atau petunjuk rujukan rentetan lain. Gunakan grid Feistel atau pg_hashids untuk menutup integer kenaikan automatik.
• Tentukan kekangan melata ON UPDATE RESTRICTmenggunakan UUID pengganti sebagai kunci asing dan untuk kunci asing tiruan ON UPDATE CASCADE. Pilih kekunci semula jadi berdasarkan logik anda sendiri.

Pendekatan ini memastikan kestabilan kunci dalaman sambil membenarkan dan juga melindungi kunci semula jadi. Di samping itu, kunci tiruan yang boleh dilihat tidak melekat pada apa-apa. Setelah memahami semuanya dengan betul, anda tidak boleh menutup telefon hanya pada "kunci utama" dan menggunakan semua kemungkinan menggunakan kunci.