5.1 บทนำ
อินเทอร์เน็ตเต็มไปด้วยกฎเกณฑ์ที่ดันทุรังเกี่ยวกับวิธีที่ควรเลือกและใช้คีย์ในฐานข้อมูลเชิงสัมพันธ์ บางครั้งข้อพิพาทก็กลายเป็นโฮลิวาร์: ควรใช้คีย์ธรรมชาติหรือคีย์เทียม? จำนวนเต็มหรือ UUID ที่เพิ่มขึ้นโดยอัตโนมัติ
หลังจากอ่านบทความหกสิบสี่เรื่อง พลิกดูส่วนต่างๆ ของหนังสือห้าเล่ม และถามคำถามมากมายเกี่ยวกับ IRC และ StackOverflow ฉัน (โจ "ขอทาน" เนลสัน ผู้เขียนบทความต้นฉบับ) ดูเหมือนจะนำชิ้นส่วนของปริศนามารวมกันและ สามารถประนีประนอมกับศัตรูได้แล้ว ข้อพิพาทที่สำคัญหลายอย่างเกิดขึ้นจากความเข้าใจผิดในมุมมองของคนอื่น
เรามาแยกปัญหาออกจากกันและรวมเข้าด้วยกันในตอนท้าย ก่อนอื่นมาถามคำถามกันก่อนว่า "กุญแจ" คืออะไร?
ลืมเกี่ยวกับคีย์หลักไปสักครู่ เราสนใจแนวคิดทั่วไปมากกว่านี้ คีย์คือคอลัมน์ (คอลัมน์) หรือคอลัมน์ที่ไม่มีค่าซ้ำกันในแถว . นอกจากนี้ คอลัมน์จะต้องไม่ซ้ำกันแบบลดทอนไม่ได้ นั่นคือ ไม่มีส่วนย่อยของคอลัมน์ใดที่มีความเป็นเอกลักษณ์นี้
แต่ก่อนอื่น ทฤษฎีบางอย่าง:
คีย์หลัก
คีย์หลักใช้โดยตรงเพื่อระบุแถวในตาราง จะต้องเป็นไปตามข้อจำกัดดังต่อไปนี้:
- คีย์หลักต้องไม่ซ้ำกันตลอดเวลา
- จะต้องอยู่ในตารางและมีค่าเสมอ
- ไม่ควรเปลี่ยนค่าบ่อยๆ ตามหลักการแล้วไม่ควรเปลี่ยนค่าเลย
โดยทั่วไปแล้ว คีย์หลักจะแสดงคอลัมน์เดียวของตาราง แต่ก็สามารถเป็นคีย์ผสมที่ประกอบด้วยหลายคอลัมน์ได้เช่นกัน
คีย์ผสม
คีย์ที่กำหนดเอง- การรวมกันของแอตทริบิวต์ (คอลัมน์) ที่ระบุแต่ละแถวของตารางโดยไม่ซ้ำกัน สามารถเป็นได้ทั้งคอลัมน์และหลายคอลัมน์และหนึ่งคอลัมน์ ในกรณีนี้ ไม่ควรทำซ้ำบรรทัดที่มีค่าของแอตทริบิวต์เหล่านี้
คีย์ที่มีศักยภาพ
คีย์ผู้สมัคร- แสดงถึงคีย์ผสมขั้นต่ำของความสัมพันธ์ (ตาราง) นั่นคือชุดของแอตทริบิวต์ที่ตรงตามเงื่อนไขจำนวนหนึ่ง:
- Irreducibility : ไม่สามารถลดได้ มีชุดแอตทริบิวต์ขั้นต่ำที่เป็นไปได้
- ความเป็นเอกลักษณ์ : จะต้องมีค่าที่ไม่ซ้ำกันโดยไม่คำนึงถึงการเปลี่ยนแปลงแถว
- การมีอยู่ของค่า : จะต้องไม่มีค่า Null นั่นคือต้องมีค่า
5.2 กรณีที่น่าสงสัยของคีย์หลัก
สิ่งที่เราเพิ่งเรียกว่า "คีย์" ในส่วนก่อนหน้านี้มักเรียกว่า "คีย์ผู้สมัคร" คำว่า "ผู้สมัคร" หมายถึงคีย์ดังกล่าวทั้งหมดแข่งขันกันเพื่อชิงตำแหน่งกิตติมศักดิ์ของ "คีย์หลัก" (คีย์หลัก) และคีย์ที่เหลือถูกกำหนดให้เป็น "คีย์สำรอง" (คีย์สำรอง)
การใช้งาน SQL ต้องใช้เวลาสักครู่เพื่อแก้ไขความไม่ตรงกันระหว่างคีย์และโมเดลเชิงสัมพันธ์ และฐานข้อมูลรุ่นแรกสุดมุ่งสู่แนวคิดระดับต่ำของคีย์หลัก คีย์หลักในฐานข้อมูลดังกล่าวจำเป็นต้องระบุตำแหน่งทางกายภาพของแถวบนสื่อเก็บข้อมูลแบบเรียงลำดับ นี่คือวิธีที่ Joe Celko อธิบาย:
คำว่า "คีย์" หมายถึงคีย์การจัดเรียงไฟล์ ซึ่งจำเป็นสำหรับการดำเนินการประมวลผลใดๆ บนระบบไฟล์ตามลำดับ ชุดไพ่ที่เจาะถูกอ่านในลำดับเดียว เป็นไปไม่ได้ที่จะกลับไป เทปไดร์ฟในยุคแรกๆ เลียนแบบการทำงานแบบเดียวกันและไม่อนุญาตให้เข้าถึงแบบสองทิศทาง นั่นคือ Sybase SQL Server เดิมจำเป็นต้อง "ย้อนกลับ" ตารางไปยังจุดเริ่มต้นเพื่ออ่านแถวก่อนหน้า
ใน SQL สมัยใหม่ คุณไม่จำเป็นต้องมุ่งเน้นไปที่การแสดงข้อมูลทางกายภาพ ความสัมพันธ์ของแบบจำลองตาราง และลำดับภายในของแถวนั้นไม่สำคัญเลย อย่างไรก็ตาม แม้กระทั่งตอนนี้ SQL Server จะสร้างดัชนีคลัสเตอร์สำหรับคีย์หลักตามค่าเริ่มต้น และตามประเพณีเดิม จะจัดลำดับของแถวทางกายภาพ
ในฐานข้อมูลส่วนใหญ่ คีย์หลักเป็นเรื่องของอดีต และให้มากกว่าการสะท้อนหรือตำแหน่งทางกายภาพเล็กน้อย ตัวอย่างเช่น ในตาราง PostgreSQL การประกาศคีย์หลักจะบังคับใช้ข้อจำกัดโดยอัตโนมัติNOT NULLและกำหนดคีย์นอกเริ่มต้น นอกจากนี้ คีย์หลักยังเป็นคอลัมน์ที่ต้องการสำหรับตัวดำเนินJOINการ
คีย์หลักไม่ได้แทนที่ความเป็นไปได้ของการประกาศคีย์อื่น ในเวลาเดียวกัน หากไม่มีการกำหนดคีย์เป็นหลัก ตารางก็จะยังทำงานได้ดี ไม่ว่าในกรณีใดสายฟ้าจะไม่โจมตีคุณ
5.3 การค้นหาคีย์ธรรมชาติ
คีย์ที่กล่าวถึงข้างต้นเรียกว่า "ธรรมชาติ" เพราะเป็นคุณสมบัติของวัตถุจำลองที่มีความน่าสนใจในตัวเอง แม้ว่าจะไม่มีใครต้องการทำคีย์ออกมาก็ตาม
สิ่งแรกที่ต้องจำเมื่อตรวจสอบตารางสำหรับคีย์ธรรมชาติที่เป็นไปได้คือพยายามอย่าฉลาดเกินไป ผู้ใช้ sqlvogel บน StackExchange ให้คำแนะนำต่อไปนี้:
บางคนมีปัญหาในการเลือกคีย์ "ธรรมชาติ" เนื่องจากพวกเขาเกิดสถานการณ์สมมุติขึ้นซึ่งคีย์หนึ่งๆ อาจไม่ซ้ำกัน พวกเขาไม่เข้าใจความหมายของงาน ความหมายของคีย์คือการกำหนดกฎตามที่แอตทริบิวต์ ณ เวลาใดก็ตามจะต้องไม่ซ้ำกันในตารางใดตารางหนึ่งเสมอ ตารางประกอบด้วยข้อมูลในบริบทที่เฉพาะเจาะจงและเป็นที่เข้าใจกันดี (ใน "สาขาวิชา" หรือ "พื้นที่วาทกรรม") และความหมายเพียงอย่างเดียวคือการประยุกต์ใช้ข้อจำกัดในพื้นที่นั้นๆ
การปฏิบัติแสดงให้เห็นว่าจำเป็นต้องแนะนำข้อ จำกัด ที่สำคัญเมื่อคอลัมน์ไม่ซ้ำกับค่าที่มีอยู่และจะยังคงเป็นเช่นนั้นในสถานการณ์ที่เป็นไปได้ และถ้าจำเป็นก็สามารถลบข้อ จำกัด ได้ (หากสิ่งนี้รบกวนคุณเราจะพูดถึงความเสถียรของคีย์ด้านล่าง)
ตัวอย่างเช่น ฐานข้อมูลของสมาชิกชมรมงานอดิเรกอาจมีเอกลักษณ์ในสองคอลัมน์- first_name, last_nameด้วยข้อมูลจำนวนเล็กน้อย การทำซ้ำจึงไม่น่าเป็นไปได้ และก่อนที่จะเกิดความขัดแย้งขึ้นจริง การใช้คีย์ดังกล่าวค่อนข้างสมเหตุสมผล
เมื่อฐานข้อมูลเติบโตขึ้นและปริมาณข้อมูลเพิ่มขึ้น การเลือกคีย์ธรรมชาติอาจกลายเป็นเรื่องยากขึ้น ข้อมูลที่เราจัดเก็บเป็นการทำให้ความเป็นจริงภายนอกง่ายขึ้น และไม่มีคุณลักษณะบางอย่างที่แยกแยะวัตถุต่างๆ ในโลก เช่น พิกัดของวัตถุที่เปลี่ยนแปลงตลอดเวลา หากวัตถุไม่มีรหัส คุณจะบอกได้อย่างไรว่าเครื่องดื่มสองกระป๋องหรือข้าวโอ๊ตสองกล่องแยกจากการจัดพื้นที่หรือความแตกต่างเล็กน้อยของน้ำหนักหรือบรรจุภัณฑ์
นั่นคือเหตุผลที่หน่วยงานกำหนดมาตรฐานสร้างและใช้เครื่องหมายเฉพาะกับผลิตภัณฑ์ ยานพาหนะถูกประทับด้วยหมายเลขประจำตัวยานพาหนะ (VIN)หนังสือพิมพ์ด้วยISBNและบรรจุภัณฑ์อาหารมีUPC คุณอาจคัดค้านว่าตัวเลขเหล่านี้ดูไม่เป็นธรรมชาติ เหตุใดฉันจึงเรียกมันว่ากุญแจธรรมชาติ
ความเป็นธรรมชาติหรือการประดิษฐ์ของคุณสมบัติเฉพาะในฐานข้อมูลนั้นสัมพันธ์กับโลกภายนอก กุญแจที่ประดิษฐ์ขึ้นเมื่อถูกสร้างขึ้นในหน่วยงานมาตรฐานหรือหน่วยงานของรัฐกลายเป็นเรื่องปกติสำหรับเรา เพราะมันกลายเป็นมาตรฐานทั่วโลกและ/หรือพิมพ์บนวัตถุต่างๆ
มีมาตรฐานอุตสาหกรรม สาธารณะ และมาตรฐานสากลมากมายสำหรับวิชาต่างๆ รวมถึงสกุลเงิน ภาษา เครื่องมือทางการเงิน สารเคมี และการวินิจฉัยทางการแพทย์ ต่อไปนี้คือค่าบางส่วนที่มักใช้เป็นคีย์ธรรมชาติ:
- รหัสประเทศ ISO 3166
- รหัสภาษา ISO 639
- รหัสสกุลเงินตามมาตรฐาน ISO 4217
- สัญลักษณ์หุ้น ISIN
- UPC/EAN, VIN, GTIN, ISBN
- ชื่อเข้าสู่ระบบ
- ที่อยู่อีเมล
- หมายเลขห้อง
- ที่อยู่เครือข่าย Mac
- ละติจูด ลองจิจูดของจุดต่างๆ บนพื้นผิวโลก
ฉันแนะนำให้ประกาศคีย์เมื่อใดก็ตามที่เป็นไปได้และเหมาะสม หรือแม้กระทั่งหลายคีย์ต่อตาราง แต่โปรดจำไว้ว่าทั้งหมดข้างต้นอาจมีข้อยกเว้น
- ไม่ใช่ทุกคนที่มีที่อยู่อีเมล แม้ว่าสิ่งนี้อาจยอมรับได้ภายใต้เงื่อนไขของฐานข้อมูลบางประการ นอกจากนี้ ผู้คนเปลี่ยนที่อยู่อีเมลเป็นครั้งคราว (เพิ่มเติมเกี่ยวกับความเสถียรของคีย์ในภายหลัง)
- สัญลักษณ์หุ้น ISIN เปลี่ยนแปลงเป็นครั้งคราว ตัวอย่างเช่น สัญลักษณ์ GOOG และ GOOGL ไม่ได้อธิบายการปรับโครงสร้างองค์กรของบริษัทจาก Google เป็น Alphabet อย่างถูกต้อง บางครั้งความสับสนอาจเกิดขึ้นได้ เช่นเดียวกับ TWTR และ TWTRQ นักลงทุนบางรายซื้ออย่างผิดพลาดระหว่างการเสนอขายหุ้นของ Twitter
- หมายเลขประกันสังคมจะใช้โดยพลเมืองสหรัฐฯ เท่านั้น มีข้อจำกัดด้านความเป็นส่วนตัว และใช้ซ้ำหลังจากเสียชีวิต นอกจากนี้หลังจากการขโมยเอกสารผู้คนสามารถรับหมายเลขใหม่ได้ สุดท้าย หมายเลขเดียวกันสามารถระบุทั้งบุคคลและตัวระบุภาษีเงินได้
- รหัสไปรษณีย์เป็นทางเลือกที่ไม่ดีสำหรับเมืองต่างๆ บางเมืองมีดัชนีร่วมกัน หรือในทางกลับกัน มีหลายดัชนีในเมืองเดียว
5.4 คีย์เทียม
เนื่องจากคีย์เป็นคอลัมน์ที่มีค่าไม่ซ้ำกันในแต่ละแถว วิธีหนึ่งในการสร้างคีย์คือการโกง - คุณสามารถเขียนค่าที่ไม่ซ้ำที่สมมติขึ้นในแต่ละแถวได้ นี่คือคีย์เทียม: รหัสที่ประดิษฐ์ขึ้นเพื่ออ้างถึงข้อมูลหรือวัตถุ
สิ่งสำคัญคือต้องสร้างรหัสจากฐานข้อมูลเองและไม่มีใครรู้จักนอกจากผู้ใช้ฐานข้อมูล นี่คือสิ่งที่ทำให้คีย์เทียมแตกต่างจากคีย์ธรรมชาติมาตรฐาน
ในขณะที่คีย์ธรรมชาติมีข้อได้เปรียบในการป้องกันแถวที่ซ้ำกันหรือไม่สอดคล้องกันในตาราง คีย์เทียมมีประโยชน์เพราะทำให้มนุษย์หรือระบบอื่นๆ อ้างอิงถึงแถวได้ง่ายขึ้น และเพิ่มความเร็วในการค้นหาและรวมเนื่องจากไม่ได้ใช้ การเปรียบเทียบสตริง (หรือหลายคอลัมน์) คีย์
ตัวแทน
คีย์เทียมถูกใช้เป็นตัวยึด - ไม่ว่ากฎและคอลัมน์จะเปลี่ยนไปอย่างไร หนึ่งแถวสามารถระบุได้ด้วยวิธีเดียวกันเสมอ กุญแจเทียมที่ใช้สำหรับจุดประสงค์นี้เรียกว่า "กุญแจตัวแทน" และต้องการการดูแลเป็นพิเศษ เราจะพิจารณาตัวแทนด้านล่าง
คีย์เทียมที่ไม่ใช่ตัวแทนมีประโยชน์สำหรับการอ้างอิงแถวจากภายนอกฐานข้อมูล คีย์เทียมระบุข้อมูลหรือวัตถุโดยสังเขป: สามารถระบุเป็น URL แนบกับใบแจ้งหนี้ เขียนตามคำบอกทางโทรศัพท์ ได้รับจากธนาคาร หรือพิมพ์บนป้ายทะเบียน (ป้ายทะเบียนรถยนต์เป็นกุญแจธรรมดาสำหรับเรา แต่ออกแบบโดยรัฐบาลให้เป็นกุญแจประดิษฐ์)
ควรเลือกคีย์สังเคราะห์โดยคำนึงถึงวิธีการส่งที่เป็นไปได้ เพื่อลดการพิมพ์ผิดและข้อผิดพลาดให้เหลือน้อยที่สุด ควรสังเกตว่าคีย์สามารถพูด อ่าน พิมพ์ ส่งผ่าน SMS อ่านลายมือ พิมพ์จากแป้นพิมพ์ และฝังอยู่ใน URL นอกจากนี้ คีย์เทียมบางคีย์ เช่น หมายเลขบัตรเครดิต จะมีผลรวมตรวจสอบ ดังนั้นหากเกิดข้อผิดพลาดขึ้น อย่างน้อยก็สามารถรับรู้ได้
ตัวอย่าง:
- สำหรับป้ายทะเบียนในสหรัฐอเมริกา มีกฎเกี่ยวกับการใช้อักขระกำกวม เช่น O และ 0
- โรงพยาบาลและร้านขายยาต้องระวังเป็นพิเศษเนื่องจากลายมือของแพทย์
- คุณส่งรหัสยืนยันทางข้อความหรือไม่? อย่าไปเกินกว่าชุดอักขระ GSM 03.38
- ซึ่งแตกต่างจาก Base64 ซึ่งเข้ารหัสข้อมูลไบต์ตามอำเภอใจ Base32 ใช้ชุดอักขระที่จำกัดซึ่งสะดวกสำหรับมนุษย์ในการใช้และจัดการกับระบบคอมพิวเตอร์รุ่นเก่า
- Proquints เป็นตัวระบุที่อ่านได้ เขียนได้ และออกเสียงได้ สิ่งเหล่านี้คือ QUINT-uplets ของ PRO-nouncable ของพยัญชนะและสระที่เข้าใจได้อย่างชัดเจน
โปรดทราบว่าทันทีที่คุณแนะนำคีย์เทียมของคุณให้โลกรู้ ผู้คนจะเริ่มให้ความสนใจเป็นพิเศษอย่างแปลกประหลาด เพียงแค่ดูที่ป้ายทะเบียน "ขโมย" หรือที่ระบบสำหรับสร้างตัวระบุที่ออกเสียงได้ ซึ่งกลายเป็นเครื่องกำเนิดคำสาปอัตโนมัติที่น่าอับอาย
แม้ว่าเราจะจำกัดตัวเองไว้ที่ปุ่มตัวเลข แต่ก็มีข้อห้ามเช่นชั้นที่สิบสาม แม้ว่า proquints จะมีความหนาแน่นของข้อมูลต่อพยางค์พูดที่สูงกว่า แต่ตัวเลขก็ใช้ได้หลายวิธีเช่นกัน: ใน URL, แป้นพิมพ์แบบพิน และโน้ตที่เขียนด้วยลายมือ ตราบใดที่ผู้รับรู้ว่าคีย์คือตัวเลขเท่านั้น
อย่างไรก็ตาม โปรดทราบว่าคุณไม่ควรใช้ลำดับตามลำดับในคีย์ตัวเลขสาธารณะ เนื่องจากจะทำให้คุณสามารถค้นหาแหล่งข้อมูล (/videos/1.mpeg, /videos/2.mpeg และอื่นๆ) และยังทำให้ข้อมูลเกี่ยวกับหมายเลขรั่วไหลได้ ข้อมูล. วาง Feistel net ทับบนลำดับของตัวเลขและรักษาเอกลักษณ์ในขณะที่ซ่อนลำดับของตัวเลข
อาร์กิวเมนต์เดียวที่ต่อต้านการประกาศคีย์เพิ่มเติมคือแต่ละคีย์ใหม่จะนำดัชนีที่ไม่ซ้ำกันมาเพิ่มและเพิ่มค่าใช้จ่ายในการเขียนลงในตาราง แน่นอนว่าขึ้นอยู่กับความถูกต้องของข้อมูลที่สำคัญสำหรับคุณ แต่ส่วนใหญ่แล้วควรประกาศคีย์
นอกจากนี้ยังมีมูลค่าการประกาศคีย์เทียมหลายรายการ หากมี ตัวอย่างเช่น องค์กรมีผู้สมัครงาน (ผู้สมัคร) และพนักงาน (พนักงาน) ครั้งหนึ่งพนักงานแต่ละคนเคยเป็นผู้สมัคร และอ้างอิงถึงผู้สมัครตามตัวระบุของตนเอง ซึ่งควรเป็นรหัสของพนักงานด้วย อีกตัวอย่างหนึ่ง คุณสามารถตั้งค่ารหัสพนักงานและชื่อล็อกอินเป็นสองคีย์ในพนักงาน
5.5 กุญแจแทน
ดังที่กล่าวไปแล้ว กุญแจเทียมประเภทหนึ่งที่สำคัญเรียกว่า "กุญแจตัวแทน" ไม่จำเป็นต้องกระชับและผ่านได้เหมือนคีย์เทียมอื่นๆ แต่ใช้เป็นป้ายกำกับภายในที่ระบุสตริงเสมอ มันถูกใช้ใน SQL แต่แอปพลิเคชันไม่ได้เข้าถึงอย่างชัดเจน
หากคุณคุ้นเคยกับคอลัมน์ระบบของ PostgreSQL คุณอาจคิดว่าตัวแทนเกือบจะเป็นพารามิเตอร์การใช้งานฐานข้อมูล (เช่น ctid) ซึ่งอย่างไรก็ตามจะไม่มีวันเปลี่ยนแปลง ค่าตัวแทนจะถูกเลือกหนึ่งครั้งต่อแถวและจะไม่เปลี่ยนแปลงหลังจากนั้น
กุญแจตัวแทนนั้นยอดเยี่ยมพอ ๆ กับกุญแจต่างประเทศ และต้องระบุข้อจำกัดการเรียงซ้อนON UPDATE RESTRICTเพื่อให้ตรงกับความไม่เปลี่ยนรูปของตัวแทน
ในทางกลับกัน คีย์นอกสำหรับคีย์สาธารณะควรทำเครื่องหมายด้วยON UPDATE CASCADEเพื่อให้มีความยืดหยุ่นสูงสุด การอัปเดตแบบต่อเรียงจะทำงานที่ระดับการแยกเดียวกันกับธุรกรรมโดยรอบ ดังนั้นอย่ากังวลเกี่ยวกับปัญหาการทำงานพร้อมกัน - ฐานข้อมูลจะไม่มีปัญหาหากคุณเลือกระดับการแยกที่เข้มงวด
อย่าทำกุญแจแทน "ธรรมชาติ" เมื่อคุณแสดงค่าของคีย์ตัวแทนแก่ผู้ใช้ปลายทาง หรือแย่กว่านั้นคือ ปล่อยให้พวกเขาทำงานกับค่านั้น (โดยเฉพาะผ่านการค้นหา) แสดงว่าคุณให้ค่าคีย์ได้อย่างมีประสิทธิภาพ จากนั้นคีย์ที่แสดงจากฐานข้อมูลของคุณสามารถกลายเป็นคีย์ธรรมชาติในฐานข้อมูลของคนอื่นได้
การบังคับให้ระบบภายนอกใช้คีย์ประดิษฐ์อื่นๆ ที่ออกแบบมาโดยเฉพาะสำหรับการส่งข้อมูล ช่วยให้เราสามารถเปลี่ยนคีย์เหล่านั้นได้ตามต้องการเพื่อตอบสนองความต้องการที่เปลี่ยนแปลง ในขณะที่ยังคงรักษาความสมบูรณ์ของการอ้างอิงภายในด้วยตัวแทน
INT/BIGINT ที่เพิ่มขึ้นโดยอัตโนมัติ
การใช้งานทั่วไปสำหรับคีย์ตัวแทนคือ คอลัมน์ "bigserial" ที่ เพิ่ม ขึ้นโดยอัตโนมัติ หรือที่เรียกว่าIDENTITY (อันที่จริงแล้ว PostgreSQL 10 รองรับโครงสร้าง IDENTITY เช่นเดียวกับ Oracle โปรดดูที่ สร้างตาราง)
อย่างไรก็ตาม ฉันเชื่อว่าจำนวนเต็มที่เพิ่มขึ้นโดยอัตโนมัติเป็นทางเลือกที่ไม่ดีสำหรับคีย์ตัวแทน ความคิดเห็นนี้ไม่เป็นที่นิยมดังนั้นให้ฉันอธิบาย
ข้อเสียของรหัสซีเรียล:
- หากลำดับทั้งหมดเริ่มต้นที่ 1 และเพิ่มขึ้นเรื่อยๆ แถวจากตารางต่างๆ จะมีค่าคีย์เหมือนกัน ตัวเลือกนี้ไม่เหมาะ แต่ยังคงดีกว่าที่จะใช้ชุดของคีย์ที่แยกจากกันในตาราง ตัวอย่างเช่น เคียวรีไม่สามารถสร้างความสับสนให้กับค่าคงที่โดยไม่ตั้งใจ
JOINและส่งคืนผลลัพธ์ที่ไม่คาดคิด (อีกวิธีหนึ่ง เพื่อให้แน่ใจว่าไม่มีจุดตัด เราอาจสร้างลำดับแต่ละลำดับจากผลคูณของจำนวนเฉพาะที่แตกต่างกัน แต่การดำเนินการนี้ค่อนข้างลำบาก) - การเรียกร้อง
nextval()ให้สร้างลำดับใน SQL แบบกระจายในปัจจุบันส่งผลให้ทั้งระบบปรับขนาดได้ไม่ดีนัก - การใช้ข้อมูลจากฐานข้อมูลที่ใช้คีย์ลำดับด้วยจะส่งผลให้เกิดความขัดแย้ง เนื่องจากค่าลำดับจะไม่ซ้ำกันในระบบต่างๆ
- จากมุมมองทางปรัชญา การเพิ่มจำนวนตามลำดับนั้นสัมพันธ์กับระบบเก่าที่ลำดับของบรรทัดถูกบอกเป็นนัย ถ้าตอนนี้คุณต้องการเรียงลำดับแถว ให้ทำอย่างชัดเจนด้วยคอลัมน์ประทับเวลาหรือสิ่งที่เหมาะสมในข้อมูลของคุณ มิฉะนั้นรูปแบบปกติแรกจะถูกละเมิด
- เหตุผลที่อ่อนแอ แต่ตัวระบุสั้น ๆ เหล่านี้ดึงดูดให้ใครบางคนบอก
UUID
ลองดูตัวเลือกอื่น: การใช้จำนวนเต็มขนาดใหญ่ (128 บิต) ที่สร้างขึ้นตามรูปแบบสุ่ม อัลกอริทึมสำหรับการสร้างตัวระบุยูนิเวอร์แซลยูนิเวอร์แซล (UUIDs) มีความเป็นไปได้ต่ำมากที่จะเลือกค่าเดียวกันสองครั้ง แม้ว่าจะทำงานบนโปรเซสเซอร์สองตัวที่ต่างกันในเวลาเดียวกัน
ในกรณีนั้นUUIDดูเหมือนจะเป็นทางเลือกตามธรรมชาติที่จะใช้เป็นกุญแจแทนใช่ไหม หากคุณต้องการติดป้ายกำกับแถวด้วยวิธีที่ไม่เหมือนใคร ไม่มีอะไรจะดีไปกว่าป้ายกำกับที่ไม่เหมือนใคร!
เหตุใดจึงไม่ทุกคนใช้มันใน PostgreSQL มีเหตุผลหลายประการสำหรับสิ่งนี้และมีเหตุผลเดียวที่สามารถแก้ไขได้ และฉันจะนำเสนอเกณฑ์มาตรฐานเพื่ออธิบายประเด็นของฉัน
อันดับแรก ฉันจะพูดถึงเหตุผลที่ห่างไกล บางคนคิดว่า UUID เป็นสตริงเพราะเขียนในรูปแบบเลขฐานสิบหกแบบดั้งเดิมโดยมีเส้นประ5bd68e64-ff52-4f54-ace4-3cd9161c8b7f: แท้จริงแล้ว ฐานข้อมูลบางประเภทไม่มีประเภท uuid แบบกะทัดรัด (128 บิต) แต่ PostgreSQL มีและมีขนาดเป็นสองbigintเช่น เมื่อเทียบกับจำนวนข้อมูลอื่นๆ ในฐานข้อมูล โอเวอร์เฮดถือว่าเล็กน้อย
UUID ยังถูกกล่าวหาอย่างไม่เป็นธรรมว่ายุ่งยาก แต่ใครจะออกเสียง พิมพ์ หรืออ่าน? เรากล่าวว่าเหมาะสมที่จะแสดงคีย์เทียม แต่ไม่มีใคร (ตามคำจำกัดความ) ควรเห็น UUID ตัวแทน เป็นไปได้ว่า UUID จะได้รับการจัดการโดยนักพัฒนาที่เรียกใช้คำสั่ง SQL ใน psql เพื่อดีบักระบบ แต่นั่นก็เกี่ยวกับเรื่องนี้ และนักพัฒนายังสามารถอ้างถึงสตริงโดยใช้คีย์ที่สะดวกกว่าหากได้รับ
ปัญหาที่แท้จริงของ UUIDs คือค่าที่สุ่มอย่างมากนำไปสู่การขยายการเขียนเนื่องจากการเขียนเต็มหน้าไปยังบันทึกการเขียนล่วงหน้า (WAL ) อย่างไรก็ตาม การลดลงของประสิทธิภาพนั้นขึ้นอยู่กับอัลกอริทึมการสร้าง UUID
มาวัดการขยายการเขียนกัน เถอะ ความจริงแล้วปัญหาอยู่ในระบบไฟล์ที่เก่ากว่า เมื่อ PostgreSQL เขียนลงดิสก์ มันจะเปลี่ยน "หน้า" บนดิสก์ หากคุณปิดเครื่องคอมพิวเตอร์ ระบบไฟล์ส่วนใหญ่จะยังคงรายงานว่าเขียนสำเร็จก่อนที่ข้อมูลจะถูกจัดเก็บไว้ในดิสก์อย่างปลอดภัย หาก PostgreSQL รับรู้อย่างไร้เดียงสาว่าการกระทำดังกล่าวเสร็จสิ้นแล้ว ฐานข้อมูลจะเสียหายระหว่างการบูตระบบครั้งถัดไป
เนื่องจาก PostgreSQL ไม่สามารถเชื่อถือการกำหนดค่าระบบปฏิบัติการ/ระบบไฟล์/ดิสก์ส่วนใหญ่เพื่อให้มีความต่อเนื่องได้ ฐานข้อมูลจึงบันทึกสถานะทั้งหมดของหน้าดิสก์ที่เปลี่ยนแปลงไปยังบันทึกการเขียนล่วงหน้าที่สามารถใช้เพื่อกู้คืนจากข้อขัดข้องที่อาจเกิดขึ้น การทำดัชนีค่าแบบสุ่มอย่างสูงเช่น UUID มักเกี่ยวข้องกับหน้าดิสก์ที่แตกต่างกันจำนวนมาก และส่งผลให้ขนาดหน้าเต็ม (ปกติคือ 4 หรือ 8 KB) ถูกเขียนไปยัง WAL สำหรับแต่ละรายการใหม่ นี่คือสิ่งที่เรียกว่าการเขียนแบบเต็มหน้า (การเขียนแบบเต็มหน้า, FPW)
อัลกอริทึมการสร้าง UUID บางตัว (เช่น "เกล็ดหิมะ" ของ Twitter หรือ uuid_generate_v1() ในส่วนขยาย uuid-ossp ของ PostgreSQL) สร้างค่าที่เพิ่มขึ้นแบบจำเจในแต่ละเครื่อง วิธีการนี้รวมการเขียนลงในหน้าดิสก์น้อยลง และลด FPW
5.6 ข้อสรุปและข้อเสนอแนะ
ตอนนี้เราได้เห็นประเภทต่างๆ ของคีย์และการใช้งานแล้ว ฉันต้องการแสดงรายการคำแนะนำของฉันสำหรับการใช้คีย์เหล่านี้ในฐานข้อมูลของคุณ
สำหรับแต่ละตาราง:
- กำหนดและประกาศคีย์ธรรมชาติทั้งหมด
- สร้างคีย์ตัวแทน
<table_name>_idประเภทUUIDด้วยค่าเริ่มต้นuuid_generate_v1()ที่ คุณสามารถทำเครื่องหมายเป็นคีย์หลักได้ หากคุณเพิ่มชื่อตารางให้กับตัวระบุนี้ จะทำให้ง่ายขึ้นJOINเช่น รับJOIN foo USING (bar_id)แทนJOIN foo ON (foo.bar_id = bar.id). อย่าส่งคีย์นี้ไปยังไคลเอ็นต์และอย่าเปิดเผยนอกฐานข้อมูลเลย - สำหรับตารางระดับกลางที่ผ่าน
JOINให้ประกาศคอลัมน์คีย์นอกทั้งหมดเป็นคีย์หลักแบบผสมเดียว - หรือเพิ่มคีย์เทียมที่สามารถใช้ใน URL หรือตัวบ่งชี้การอ้างอิงสตริงอื่นๆ ใช้ตาราง Feistelหรือpg_hashidsเพื่อปกปิดจำนวนเต็มที่เพิ่มขึ้นโดยอัตโนมัติ
- ระบุข้อจำกัดแบบเรียงซ้อน
ON UPDATE RESTRICTโดยใช้ UUID แทนเป็นคีย์นอกและคีย์แปลกปลอมON UPDATE CASCADEเทียม เลือกคีย์ธรรมชาติตามตรรกะของคุณเอง
วิธีการนี้ทำให้มั่นใจได้ถึงความเสถียรของคีย์ภายในในขณะที่อนุญาตและแม้แต่ปกป้องคีย์ธรรมชาติ นอกจากนี้ คีย์เทียมที่มองเห็นได้จะไม่ติดอยู่กับสิ่งใดเลย เมื่อเข้าใจทุกอย่างถูกต้องแล้ว คุณไม่สามารถวางสายเฉพาะ "คีย์หลัก" และใช้ความเป็นไปได้ทั้งหมดของการใช้คีย์