Ma'lumotlar bazasida kalitlar

5.1 Kirish

Internetda qanday qilib to'g'ri kalitlarni tanlash va ularni relatsion ma'lumotlar bazalarida ishlatish haqida ko'plab tartib qoidalar mavjud. Ba'zan bu munozaralar hatto shunday savollarga olib keladi: tabiiy yoki sun'iy kalitlar qo'llanishi kerakmi? Avtoincrementlik butun sonlar yoki UUID?

Bernikning muammolarini qismlarga ajratib, keyin uni qayta yig'aylik. Dastlab savol beraylik: "Kalit" nima?

Bir daqiqa birlamchi kalitlarni unuting. Bizni qiziqtirayotgan umumiy tushuncha shuki: kalit - bu qatorda takrorlanmaydigan qiymatlar bo'lgan ustun yoki ustunlar to'plami. Bundanda tashqari, ustunlar oxir-oqibat unikal bo'lishi kerak. Ya'ni, hech qanday ustunlar qismi bunday unikallikka ega bo'lmasligi kerak.

Ammo boshlanishida bir oz nazariya:

Birinchi kalit

Birinchi kalit jadvaldagi qatorlarni aniqlash uchun ishlatiladi. U quyidagi talablarni qondirishi kerak:

• Birinchi kalit har doim unikallikka ega bo'lishi kerak.
• U doimo jadvalda mavjud bo'lishi va qiymatga ega bo'lishi kerak.
• U tez-tez o'z qiymatini o'zgartirmasligi kerak va idealda qiymatini o'zgartirmasligi kerak.

Odatda, birinchi kalit jadvalning bitta ustunidan iborat, lekin u bir nechta ustunlardan tashkil topgan tarkibli bo'lishi mumkin.

Tarkibli kalit

Custom Key - bu jadvalning har bir qatorini unikallik identifikatsiya qilish uchun foydalaniladi. Bu barcha ustunlar, bir necha yoki hatto bitta ustunni o'z ichiga olishi mumkin. Ushbu atributlarning qiymatlari qatorlarda takrorlanmasligi kerak.

Potensial kalit

Candidate Key - jadvalning minimal tarkibli kaliti. Bu qator shartlarga javob beradigan atributlar to'plami.

• Yuqori foydalanuvchanlik: U qisqartirilmaydi va minimal atributlar to'plamiga ega bo'ladi.
• Unikallik: U o'zgarishlardan qat'iy nazar unikallikni saqlab qolishi kerak.
• Qiymat mavjudligi: U qiymatga ega bo'lishi kerak, NULL bo'lishi mumkin emas.

5.2 Birinchi kalitlarning qiziqarli holati

Oldingi bo'limda biz kalitlarni shunchaki "kalitlar" deb atadik. Odatda ularni "potensial kalitlar" deb atashadi. Ulardan biri "birinchi kalit" deb ataladi. Qolganlari "muqobil kalitlar" deb nomlanadi.

Bir muddat avval relatsion ma'lumotlar bazalari yaratilgan edi, ular birinchi kalitlarga asoslangan edi. Ular qatorlarni ma'lumotlar omborida joylashishini aniqlash uchun ishlatilgan edi. Joe Celko bu qanday ishlashini aytadi.

"Kalit" termini fayllar ustida bajarilishi kerak bo'lgan ishlar uchun kerak bo'lgan kalit sifatida ishlatiladi. Birinchi magnit lentasidagi yo'lovlar faqat bitta tartibda fayllarni o'qishi mumkin edi. Zamonaviy SQLda ma'lumotlarning jismoniy taqdimoti haqida qayg'urmang, qatorlarning tartibi muhim emas. Biroq, SQL-server birinchi kalitlar uchun avtomatik klasterli indeks yaratadi va qatorlar tartibini an'anaga ko'ra fiziologik tartibga soladi.

Ko'pgina ma'lumotlar bazalaridagi birinchi kalitlar o'tmishdan qolgan va odatda faqat tasvir yoki joylashuv aniqlovini taqdim etadi. Masalan, PostgreSQL jadvalida birinchi kalitni e'lon qilsangiz, biz avtomatik ravishda NOT NULL cheklanishini va standart bo'yicha tashqi kalitni olamiz. Shuningdek, birinchi kalitlar JOIN buyruqlari uchun ideal maydonlar hisoblanadi.

Birinchi kalitni ko'rsatish shart emas. Jadval hatto birinchi kalit bo'lmasa ham normal ishlaydi.

5.3 Tabiiy kalitlarni topish

"Tabiiy" deb nomlangan kalitlar modellashtirilgan ob'ektning xususiyatlaridan kelib chiqadi. Ular o'z-o'zidan qiziqarli bo'lishadi, hatto kalit yaratish uchun ishlatilmasa ham.

Jadvalda mumkin bo'lgan tabiiy kalitlarni qidirishda muammolarni murakkablashtirishga hojat yo'q.

Ba'zi odamlar "tabiiy" kalitni tanlashda qiyinchiliklarga duch keladi, chunki ular aniq bir kalitning unikal bo'lmasligi mumkin bo'lgan gipotetik vaziyatlarni o'ylab topadilar. Vaholanki, vazifaning ma'nosini tushunish muhimdir. Kalit hohlagan paytda jadvaldagi atributlar yagona bo'lishini ta'minlash qoidasi bo'lishi kerak. Jadval ma'lumotni aniq ma'lum bir kontekstda olib chiqadi va cheklashni qo'llash faqat bu sohada ahamiyatli.

Amaliyot shuni ko'rsatadiki, kalitning cheklashini kiritish lozim, agar ustun unikallikni saqlab qolsagina va ehtimolli ssenariylar bilan bunday o'ziga xosli bo'lsa. Agar bu sizni bezovta qilsa, kalitning barqarorligi haqida aytib beramiz.

Masalan, hobbi-klub a'zolari bazasi ikkita ustunda unikallikka ega bo'lishi mumkin - first_name, last_name. Ma'lumotlar hajmi kichik bo'lganda, dublikatlar ehtimol emas, va haqiqiy ziddiyat paydo bo'lishidan oldin bunday kalitdan foydalanish talabga javob beradi.

Baza ma'lumotlari va ma'lumotlar hajmi o'sishi bilan tabiiy kalitni tanlash qiyinlashishi mumkin. Saqlanadigan ma'lumot haqiqatdan ko'ra soddalashtirishdan iborat va obyektlarni dunyoda farq qiluvchi ayrim jihatlarni o'z ichiga olmaydi, masalan, ularning vaqt o'tishi bilan o'zgartiriladigan koordinatalari. Agar ob'ektning qandaydir kodi bo'lmasa, unda qanday qilib ikkita ichimlik idishini yoki ikkita jo'xorie qutisini, faqat ularning joylashuvi yoki biroz og'irlik yoki qadoqlash farqlari orqali ajratish mumkin?

Shuning uchun standartlash organlari mahsulotlarga farqlovchi teglardan bosib chiqaradi. Mashinalarga Vehicle Identification Number (VIN) muhrlanadi, kitoblarida ISBN bosiladi, oziq-ovqat mahsulotlari qadoqlarida UPC. Siz bu sonlar tabiiy deb ko'rinmasligini ayta olasiz. Lekin ularni tabiiy kalitlar deb atash mumkin, chunki ular standartar bo'lib qoladi va/yoki ob'ektlarga bosiladi.

Har xil ob'ektlar uchun ko'plab sektorlar, jamoat va xalqaro standartar mavjud, jumladan valyutalar, tillar, moliyaviy vositalar, kimyoviy moddalar va tibbiy tashxislar. Mana ba'zi qiymatlar, ular ko'pincha tabiiy kalitlar sifatida ishlatiladi:

• ISO 3166 bo'yicha davlat kodlari
• ISO 639 bo'yicha til kodlari
• ISO 4217 bo'yicha valyuta kodlari
• ISIN birja belgisi
• UPC/EAN, VIN, GTIN, ISBN
• login nomlari
• elektron pochta manzillari
• xona raqamlari
• tarmoqdagi mac-manzillar
• Er yuzidagi nuqtalar uchun kenglik va uzunlik

Kalitlarni e'lon qilish tavsiya etiladi, qachonki bu mumkin va mantiqiy bo'lsa. Balki, jadvalga bir necha kalitlar hem. Lekin, esda tutingki, istisnolar bo'lishi mumkin.

• Hammasi elektron pochta manziliga ega emas, ayrim hollarda qabul qilinishi mumkin bo'lishi mumkin. Shuningdek, odamlar vaqti-vaqti bilan o'z manzillarini o'zgartiradilar. (Kalitlarning barqarorligi haqida keyinro ko'proq.)
• ISIN birja belgisi ba'zan o'zgaradi. Masalan, Goog va GOOGL belgilari Google kompaniyasining Alphabet re-organizatsiyasini aniq aks ettirmaydi. Bu, masalan, TWTR va TWTRQ ning Twitter IPO-si davrida sotib olishda chalkashlikka olib kelishi mumkin. Ba'zi sarmoyadorlar so'nggi belgilarni noto'g'ri sotib olib ham olishgan.
• SSN AQSh fuqarolari uchun maxfiylik va o'lishdan keyingi qayta identifikatsiya bilan bog'liq. O'g'irlikdan so'ng almashtirilishi mumkin. Bitta raqam shaxsni va soliq identifikatorini aniqlashi mumkin.
• Shaharlar indekslari - yomon tanlov. Bir shahar ichida umumiy indeks yoki bir nechta indekslar bo'lishi mumkin.

5.4 Sun'iy kalitlar

Har bir qatorda unikallik qiymatlari bo'lgan ustun bo'lgan kalitni yaratishda, bir variant sun'iy kalitlardan foydalanishdir. Bu ma'lumot yoki obyektlarga havol qilish uchun foydalaniladigan ixtiro qilingan kod.

Ma'lumotlar bazasidan yaratilgan kod ma'lumot bazasining foydalanuvchilari tashqari hech kimga ma'lum emas. Bu sun'iy kalitlarni standartlashtirilgan tabiiy kalitlardan ajratadi.

Tabiiy kalitlar qatord an takrorlanishni va zid kelishuvni himoya qiladi. Sun'iy kalitlar esa insonlar yoki tizimlarga qatorga havola qilishda va qidiruvni va birlashtirishni tezlashtirishda yordam beradi, satrli kalitlarni taqqoslamasdan.

Surrogatlar

Quvonchli jazo: Surrogatlar maxsus e'tiborni talab qiladi. Bu haqida keyinchalik tushuntiramiz.

Sun'iy kalitlar, surrogatlar bo'lmagan holda, ma'lumotlar bazasidan tashqaridagi qatorlarga havola qilish uchun qulaydir. Ular ma'lumot yoki obyektlarni qisqacha aniqlay oladi: URLda ko'rsatilishi, hisobga qo'yilishi, telefon orqali chiqarilayotgan, bankda qabul qilinishi yoki registr raqamida chop etilishi mumkin. (Davlat tomonidan ishlab chiqiladigan va sun'iy kalit sifatida tayyorlangan avtomobil registr raqami biz uchun tabiiy kalit hisoblanadi.)

Sun'iy kalitlarni tanlashda mumkin bo'lgan uzatish usullarini hisobga olish kerak, shunda xatolar va xatolik kam bo'ladi. Masalan, kalit aytib berilishi, qoralangani, SMS orqali uzatilishi, qo'lda yozilganidan o'qilishi, klaviatura orqali kirilishi va URLga joylashtirilishi mumkin. Bundan tashqari, ayrim sun'iy kalitlar, masalan, kredit karta raqamlari, xatolarni aniqlash uchun nazorat summasini o'z ichiga oladi.

Misollar:

• AQSh registr raqamlari O va 0 dan foydalanish qoidalariga ega.
• Kasxona va dorixonalar shifokorlar talablariga rioya qilishda ehtiyotkorlik bilan ishlashi kerak.
• ESEMES formatida tasdiqlash kodini uzatayapsizmi? GSM 03.38 dan boshqa belgilarni ishlatmang.
• Base64dan farqli o'laroq, arbitrary bytelarni kodlovchi Base32 cheklangan belgi to'plamini ishlatadi, bu odamlar uchun qulayroq va eski kompyuter tizimlarida qayta ishlash osonroq.
• Proquints - o'qilishi, yozilishi va aytilishi mumkin bo'lgan identifikatorlar. Bu bir ma'no anglatadigan kelishuvli va unli harflarning ma'lum birliklaridir.

Yodda tutingki, sun'iy kalitni dunyoga taqdim qilsangiz, odamlar unga o'ziga xos munosabat bilan qaray boshlaydi. Dastlabki registr raqamlarini yoki tanilgan avtomatlashtirilgan qarg'ish generatoriga aylangan talaffuzlarni yaratish tizimini ko'rishga bir qarash kifoya.

Raqamli kalitlardan foydalangan vaqtingizda 13-qavat kabi maxsus raqamlar bor. Proquints talaffuz qilinadigan slognlardagi ma'lumot zichligi yuqori, lekin raqamlar ham ko'plab holatlarda foydalidir: URLda, klaviatura yahshisda va qo'lda yozilgan yozuvlarda, agar qabul qiluvchi kalit faqat raqamlardan iboratligini bilsa.

Biroq, efiopiyalik raqamli kalitlarda ketma-ket tartibdan foydalanmaslik lozim. Bu resurslarda qidiruv qilish imkonini beradi (masalan, /videos/1.mpeg, /videos/2.mpeg va hokazo), shuningdek, ma'lumotlar sonini ochib beradi. Unikal bo'lishi va raqamlar tartibini yashirish uchun Feistel bikemini yoki boshqa algoritmalardan foydalaning.

Qo'shimcha kalitlarni qo'shish davolanishni talab qiladi, chunki ularning har biri unikal indeks yaratishni va jadvalga yozish xarajatlarini oshiradi. Ma'lumotlarning to'g'riligi siz uchun qanchalik muhimligiga qarab, kalitlarni e'lon qilish yaxshiroqdir, balki.

Agar mavjud bo'lsa, bir necha sun'iy kalitlarni ko'rsating. Masalan, tashkilotda naqadnomalar (Applicants) va xodimlar (Employees) mavjud. Har bir xodim naqadnoma bo'lgan va o'z identifikatoriga ega, bu ham xodim kaliti sifatida ishlatilishi mumkin. Yana bir misol: xodimlar jadvalida xodim identifikatori va login nomini ikki kalit sifatida beradi.

5.5 Surrogat kalitlar

"Surrogat kalit" sun'iy kalitning muhim turi. U boshqa sun'iy kalitlar singari qisqa va uzatilishi shart emas. U qatorlarni aniqlash uchun ichki belgi sifatida ishlatiladi. SQL da ishlatiladi, lekin ilova unga bevosita murojaat qilinmaydi.

Agar siz PostgreSQL tizimi ustunlari haqidagi bilan tanish bo'lsangiz, surrogatlarni ma'lumotlar bazasining amalga oshirish parametri, masalan ctid sifatida qabul qilishingiz mumkin. Surrogat qiymati har bir qatorda bir marta tanlanadi va o'zgartirilmaydi.

Surrogat kalitlar tashqi kalitlar uchun juda mos keladi. Surrogatning o'zgartirilmasligini saqlash uchun ON UPDATE RESTRICT cheklovni ko'rsatish kerak.

Shu bilan birga, omma oldida ko'rsatiladigan kalitlar uchun ON UPDATE CASCADE bilan ko'rsating, bu kasallik moslashuvchanlikni ta'minlash uchun. Bunga etishish uchun tranzaksiyani o'rab olgan izolyatsiya darajasida kaskadli yangilash ishlatiladi. Bu paralel kirish muammosini oldini olish uchun qattiq izolyatsiya darajasini tanlang.

Surrogat kalitlariga "tabiiy" maqom tayinlamang. Agar biror vaqt siz surrogat kalit qiymatini oxirgi foydalanuvchiga ko'rsatadigan yoki undan ham yomoni, u bilan ishlash imkonini bersangiz (masalan, qidiruv orqali), bu kalit ahamiyat kasb etadi. Keyin ko'rsatilgan kalit sizning ma'lumotlar bazangizdan boshqa bir baza ma'lumotining tabiiy kaliti bo'lishi mumkin.

Tashqi tizimlar maxsus uzatish uchun moslashgan boshqa sun'iy kalitlardan foydalanishga da'vat qilinishi mumkin. Bu kalitlarni o'zgaruvchan ehtiyojlar bilan yangilashimizga imkon beradi, shu bilan birga surrogatlar bilan havolalarni to'g'ri ushlab turishimizga.

Avtoinkrementlik INT/BIGINT

Surrogat kalitlar uchun eng keng tarqalgan holda, avtoinkrementlik bigserial ustuni, tanilgan kishilarida IDENTITY sifatida tanilgan. (Haqiqatan ham, PostgreSQL 10 endi Oracle kabi IDENTITY konstruktsiyasini qo'llab-quvvatlaydi, CREATE TABLE ga qarang.)

Lekin, avtoinkrementlik butun sun'iy kalitlar uchun yaxshi tanlov emas.

Ketma-ket kalitlarning kamchiliklari:

Agar barcha ketma-ketliklar 1 dan boshlansa va bosqichma-bosqich ortib boradigan bo'lsa, turli jadvaldagi qatorlar bir xil kalit qiymatlariga ega bo'ladi. Bu ideal emas. Barcha jadvallarda kalitlarning ikkilanmagan turli yig'indilarini ishlatish yaxshiroqdir. Bu, masalan, JOIN bajarishda xatoliklardan qochishga yordam beradi. Boshqa alternativ sifatida, turli oddiy sonlardan ko'payadigan sonlar ketma-ketligidan foydalanish mumkin. Ammo bu juda qiyin ish bo'ladi.

Zamonaviy taqsimlangan SQLda ketma-ketlik uchun nextval() chaqiruvi tizimning o'lcham barqarorligini yomonlashadi.

Ketma-ket kalitlar qo'llangan ma'lumotlar bazasidan ma'lumotlarni eksporta qilish ziddiyatlar yaratishi mumkin, chunki ketma-ket qiymatlar turli tizimlarda unikallikni ko'rsata olmaydi.

Falsafiy nuqtai nazardan, sonlarning navbatma-navbat oshishi qatorlarning tartibini nazarda tutuvchi eski tizimlar bilan bog'liq. Qatorlarni tartiblash uchun aniq usuldan foydalaning, masalan, vaqt yorlig'i ustuni yoki sizning ma'lumotlaringiz uchun ahamiyatli bo'lgan biror narsa. Aks holda, birinchi normal formani buzasiz.

UUID

Keling, boshqa variantni ko'rib chiqaylik: katta butun sonlardan (128-bitli) foydalanish, tasodifiy shablon asosida hosil qilingan. Bunday universal unikallik identifikatorlari (UUID) yaratish algoritmlari, hatto ikki turli protsessorlarda bir vaqtning o'zida bajarilishida ham bir xil qiymatni takrorlash ehtimoli juda kichik.

Bu holda UUID surrogat kalitlari sifatida ishlatish uchun afzalroq ko'rinadi. Agar siz qatorlarni unikallik bilan belgilashni xohlasangiz, hech narsa unikalli belgidan ustun turmaydi.

Ba'zi odamlar UUID - bu qatorlar deb o'ylashadi, chunki ular an'anaviy oltilik formatda defis bilan yoziladi: 5bd68e64-ff52-4f54-ace4-3cd9161c8b7f. Biroq, PostgreSQL birlashtirilgan (128-bitli) uuid tipiga ega, bu ikki bigintni o'z ichiga oladi, ya'ni, boshqa ma'lumotlar bazasidagi ma'lumot hajmi bilan solishtirganda xarajatlar ahamiyatsizdir.

UUIDlarni ko'pincha kattaligi sababli ayblashadi, lekin qachon ularni aytib o'tish, chop etish yoki o'qish kerak bo'ladi? Biz aytganimizdek, bu ko'rsatiladigan sun'iy kalitlar uchun ahamiyatga ega. Lekin hech kim (ta'rif bo'yicha) surrogat UUIDni ko'rmasligi kerak. Dasturchi tizimni tuzatishni o'rganish uchun psqlda SQL buyruqlarini ishga tushirish uchun UUID dan foydalanadi. U qatorlarga havola qilish uchun tayinlangan kalitlardan foydalanishi mumkin, agar ular kiritilgan bo'lsa.

UUID bilan haqiqiy muammo shundaki, juda tasodifiy qiymatlar yozuv hajmini (yozuvni kengayish) oshiradi, ularning oldindan yozilgan jurnalga (write-ahead log, WAL) boshqa sahifalarning to'liq yozuvlarini kiritadi. Keling, bu yozuv kengayishini o'lchaylik. Haqiqatan ham, muammo eski fayl tizimlarida. Har safar PostgreSQL diskka yozadi, u diskdagi "sahifani" o'zgartiradi. Quvvat olish buzilganda, ko'p fayl tizimlari yozuv tugallanganligini xabar beradi, hatto ma'lumotlar xavfsiz diska yozilishidan oldin. Agar PostgreSQL bu faoliyatni muvaffaqiyatli deb qabul qilsa, tizimni keyingi yuklashda ma'lumotlar bazasi buzilib qoladi.

PostgreSQL ko'pgina operatsion tizimlar, fayl tizimlari va disk konfiguratsiyalariga ishonib bo'lmaydi. Shuning uchun ma'lumotlar bazasi o'zgartirilgan disk sahifasining to'liq holatini oldindan yozilgan jurnalga (write-ahead log) saqlaydi. Bu, mumkin bo'lgan xatolikdan so'ng, ma'lumotlarni tiklash imkonini beradi.

Muvaffaqiyatsız qiymatlarning indeksatsiyasi, masalan UUID, odatda diskning ko'plab turli sahifalarini ta'sir qiladi. Bu har bir yangi yozuv uchun to'liq sahifa hajmini (odatda 4 yoki 8 KB) WALga yozish (to'liq sahifa yozuvi, FPW) kiritadi.

Kichik noanikan UUIDni yaratish algoritmlari (masalan, Twitter's snowflake yoki PostgreSQL uchun uuid_generate_v1() uuid-ossp kengaytmasidan) har bir mashinada monoton oshadigan qiymatlarni yaratadi. Bu yondashuv yozuv ta'sir qilgan disk sahifalar sonini kamaytiradi va FPWni qisqartiradi.

5.6 Natijalar va tavsiyalar

Endi biz turli kalit turlari va ularning mumkin bo'lgan foydalanishlari bilan tanish bo'lganimizdan so'ng, ma'lumotlar bazalarida ulardan foydalanish bo'yicha tavsiyalar bilan o'rtoqlashmoqchiman.

Har bir jadval uchun:

• Barcha tabiiy kalitlarni aniqlang va e'lon qiling.
• <table_name>_id nomli UUID tipidagi surrogat kalit yarating va uuid_generate_v1() standart qiymat bilan. Hatto uni birinchi kalit sifatida belgilashingiz mumkin. Jadval nomini ushbu identifikatorda kiritib, JOINni osonlashtirasiz, ya'ni JOIN foo USING (bar_id) ni JOIN foo ON (foo.bar_id = bar.id) o'rniga ko'rasiz. Ushbu kalitni mijozlarga uzatmang va ma'lumotlar bazasidan tashqariga chiqarmang.
• Oraliq jadval orqali JOIN amalga oshirilsa, barcha ustunlarni tashqi kalitlar sifatida e'lon qiling. Ularni bitta katta birinchi kalit sifatida ko'rsating.
• Qo'shimcha himoya uchun URLda yoki qatorga havola qiluvchi boshqa ko'rsatmalarda sun'iy kalitdan foydalaning. Avtoinkrement butun sonlarni yashirish uchun Feistel Grid yoki pg_hashidsdan foydalaning.
• UUIDni tashqi kalit sifatida foydalanib, ON UPDATE RESTRICT cheklovini kiritish. Tashqi sun'iy kalitlar uchun ON UPDATE CASCADE dan foydalaning. Mantiqingizga mos keladigan tabiiy kalitlar tanlang.

Bunday yondashuv ichki kalitlarning barqarorligini ta'minlaydi va hatto tabiiy kalitlarni himoya qiladi. Ko'rinadigan sun'iy kalitlar hech qanday majburiyatlarga bog'lanmagan. Buni tushungan holda, kalitlarni qo'llash imkoniyatlarini to'liq kuzatish mumkin, faqat "birinchi kalitlar" bilan cheklanmaslikdan chiqish mumkin.