การทำให้เป็นมาตรฐานของตารางในฐานข้อมูล

8.1 เหตุใดการทำให้เป็นปกติจึงจำเป็น

การดำเนินการที่มีราคาแพงที่สุดในการคำนวณระหว่างตารางขนาดใหญ่คือการรวม ดังนั้นหากในหนึ่งแบบสอบถามจำเป็นต้อง "ระบาย" หลายตารางที่ประกอบด้วยแถวหลายล้านแถว DBMS จะใช้เวลาส่วนใหญ่ในการประมวลผลดังกล่าว

ผู้ใช้ในขณะนี้สามารถย้ายไปดื่มกาแฟ การโต้ตอบของการประมวลผลแทบจะหายไปและเข้าใกล้การประมวลผลแบบแบตช์ ที่แย่กว่านั้น ในโหมดแบทช์ ผู้ใช้จะได้รับข้อมูลทั้งหมดที่ร้องขอเมื่อวันก่อนในตอนเช้าและทำงานร่วมกับพวกเขาอย่างใจเย็น เตรียมคำขอใหม่สำหรับตอนเย็น

เพื่อหลีกเลี่ยงสถานการณ์ของการรวมจำนวนมาก ตารางจะถูกทำให้เป็นมาตรฐาน แต่ไม่อย่างใด มีกฎบางข้อที่อนุญาตให้คุณพิจารณาตารางดีนอร์มอลไลซ์ของธุรกรรมเป็น "นอร์มัลไลซ์" ตามกฎสำหรับการสร้างตารางสำหรับคลังข้อมูล

มีสองแผนหลักที่ถือว่า "ปกติ" ในการประมวลผลเชิงวิเคราะห์: "เกล็ดหิมะ" และ "ดาว" ชื่อสะท้อนถึงสาระสำคัญได้ดีและติดตามโดยตรงจากรูปภาพของตารางที่เกี่ยวข้อง

ในทั้งสองกรณี ตารางข้อเท็จจริงที่เรียกว่าเป็นองค์ประกอบหลักของสคีมา ซึ่งมีเหตุการณ์ ธุรกรรม เอกสาร และสิ่งที่น่าสนใจอื่นๆ ที่นักวิเคราะห์สนใจ แต่ถ้าในฐานข้อมูลการทำธุรกรรม เอกสารหนึ่งถูก "ละเลง" ในหลายตาราง (อย่างน้อยสอง: ส่วนหัวและแถวของเนื้อหา) ดังนั้นในตารางความเป็นจริง เอกสารหนึ่งอย่างแม่นยำยิ่งขึ้น แต่ละแถวหรือชุดของแถวที่จัดกลุ่มจะสอดคล้องกัน ถึงหนึ่งเรคคอร์ด

ซึ่งสามารถทำได้โดยทำให้สองตารางด้านบนไม่ปกติ

8.2 ตัวอย่างการทำให้เป็นปกติ

ตอนนี้คุณสามารถประเมินได้ว่า DBMS จะดำเนินการค้นหาเช่นประเภทต่อไปนี้ได้ง่ายขึ้นเพียงใด: เพื่อกำหนดปริมาณการขายแป้งให้กับลูกค้าของ Pirozhki LLC และ Vatrushki CJSC ในช่วงเวลานั้น

ในฐานข้อมูลธุรกรรมปกติ:

SELECT
   SUM(dl.qty) AS total qty, SUM(dl.price) AS total amount, c.name 
FROM 
   docs d
   INNER JOIN doc lines dl ON d.id doc = dl.id doc 
   INNER JOIN customers c ON d.id customer = c.id customer 
   INNER JOIN products p ON dl.id product = p.id product 
WHERE
   c.name IN (’Pirozhki LLC’,	’Vatrushki CJSC’) AND
   p.name = ’Flour’ AND
   d.date BETWEEN ’2014-01-01’ AND ’2014-02-01’
GROUP BY c.name

ในฐานข้อมูลการวิเคราะห์:

SELECT
   SUM(s.qty) AS total_qty, SUM(s.amount) AS total_amount, c.name
FROM
   sales s
   INNER JOIN customers c ON d.id_customer = c.id_customer
   INNER JOIN products p ON dl.id_product = p.id_product
WHERE
   c.name IN ('Pirozhki LLC', 'Vatrushki CJSC') AND
   p.name = 'Flour' AND
   s.date BETWEEN '2014-01-01' AND '2014-02-01'
GROUP BY c.name

แทนที่จะใช้การรวมจำนวนมากระหว่างเอกสารสองตารางและองค์ประกอบที่มีแถวเป็นล้านแถว DBMS จะทำงานโดยตรงกับตารางข้อเท็จจริงและการรวมแสงด้วยตารางเสริมขนาดเล็ก ซึ่งคุณสามารถทำได้โดยไม่ต้องรู้ตัวระบุ

SELECT
   SUM(s.qty) AS total_qty, SUM(s.amount) AS total_amount, s.id_customer
FROM
   sales s
WHERE
   s.id_customer IN (1025, 20897) AND
   s.id_product = 67294 AND
   s.date BETWEEN '2014-01-01' AND '2014-02-01'
GROUP BY s.id_customer

กลับไปที่รูปแบบ "ดาว" และ "เกล็ดหิมะ" เบื้องหลังของภาพแรกคือตารางของลูกค้า กลุ่ม ร้านค้า ผู้ขาย และในความเป็นจริงคือสินค้า เมื่อดีนอร์มัลไลซ์ ตารางเหล่านี้เรียกว่าไดเมนชัน จะรวมเข้ากับตารางแฟคท์ด้วย หากตารางแฟคท์อ้างถึงตารางไดเมนชันที่มีลิงก์ไปยังไดเมนชันอื่น (ไดเมนชันของระดับที่สองขึ้นไป) ดังนั้นสคีมาดังกล่าวจะเรียกว่า "เกล็ดหิมะ"

อย่างที่คุณเห็น สำหรับข้อความค้นหาที่มีการกรองตามกลุ่มลูกค้า คุณต้องทำการเชื่อมต่อเพิ่มเติม

SELECT sum(amount)
FROM sales s
   INNER JOIN customers c ON s.id_customer = c.id_customer
WHERE c.id_customer_group IN (1, 2, 10, 55)

ในกรณีนี้ การทำให้เป็นปกติสามารถดำเนินการต่อและปล่อยมิติข้อมูลระดับที่สองไปที่มิติแรก ทำให้การสอบถามตารางข้อเท็จจริงทำได้ง่ายขึ้น

สคีมาซึ่งตารางข้อเท็จจริงอ้างอิงเฉพาะมิติข้อมูลที่ไม่มีระดับที่สองเรียกว่าสคีมาแบบดาว จำนวนตารางการวัดสอดคล้องกับจำนวน "รังสี" ในดาวฤกษ์

สคีมาแบบดาวจะขจัดลำดับชั้นของมิติข้อมูลและความจำเป็นในการรวมตารางที่สอดคล้องกันในแบบสอบถามเดียวอย่างสมบูรณ์

SELECT sum(amount)
FROM sales s
WHERE s.id_customer_group IN (1, 2, 10, 55)

ข้อเสียของการดีนอร์มัลไลเซชันคือความซ้ำซ้อน เสมอ ซึ่งทำให้ฐานข้อมูลมีขนาดเพิ่มขึ้นทั้งในแอปพลิเคชันธุรกรรมและการวิเคราะห์ ลองคำนวณเดลต้าโดยประมาณจากตัวอย่างข้างต้นของการแปลง "เกล็ดหิมะ" เป็น "ดาว"

ในบาง DBMS เช่น Oracle ไม่มีประเภทจำนวนเต็มพิเศษที่ระดับของข้อกำหนดสคีมาฐานข้อมูล คุณต้องใช้ประเภทบูลีนทั่วไปnumeric(N)โดยที่ N คือจำนวนบิตที่เก็บไว้ ขนาดพื้นที่เก็บข้อมูลของตัวเลขดังกล่าวคำนวณโดยใช้สูตรพิเศษที่กำหนดในเอกสารประกอบสำหรับการจัดเก็บข้อมูลจริง และตามกฎแล้ว ขนาดดังกล่าวจะมากกว่าประเภทระดับต่ำ เช่น "จำนวนเต็ม 16 บิต" 1-3 ไบต์

id_customer_groupสมมติว่าตารางการขายไม่ได้ใช้การบีบอัดข้อมูลและมีประมาณ 500 ล้านแถว และจำนวนกลุ่มลูกค้าประมาณ 1,000 ใน กรณีนี้ เราสามารถใช้จำนวนเต็มสั้น (shortint, smallint) ที่มี 2 ไบต์เป็นตัวระบุประเภท

เราจะถือว่า DBMS ของเรารองรับประเภทจำนวนเต็มสองไบต์ (เช่น PostgreSQL, SQL Server, Sybase และอื่นๆ) จากนั้นเพิ่มคอลัมน์ที่เกี่ยวข้องลงid_customer_groupในตารางการขายจะเพิ่มขนาดอย่าง500 000 000 * 2 = 1 000 000 000 byte ~ 1 GByteน้อย

8.3 จำเป็นต้องมีการทำให้เป็นปกติเมื่อใด

มาดูสถานการณ์ทั่วไปที่การทำให้เป็นปกติอาจมีประโยชน์

เข้าร่วมตารางจำนวนมาก

ในการสอบถามไปยังฐานข้อมูลที่ปรับให้เป็นมาตรฐานอย่างสมบูรณ์ คุณมักจะต้องรวมตารางมากถึงสิบตารางหรือมากกว่านั้น และการเชื่อมต่อแต่ละครั้งเป็นการดำเนินการที่ใช้ทรัพยากรมาก เป็นผลให้คำขอดังกล่าวใช้ทรัพยากรของเซิร์ฟเวอร์และดำเนินการช้า

ในสถานการณ์เช่นนี้ สามารถช่วย:

• denormalization โดยการลดจำนวนตาราง เป็นการดีกว่าที่จะรวมตารางหลายๆ ตารางที่มีขนาดเล็กเข้าด้วยกันเป็นตารางเดียว ซึ่งมีข้อมูลที่ไม่ค่อยเปลี่ยนแปลง (ดังที่พวกเขามักพูดว่า ค่าคงที่แบบมีเงื่อนไขหรือข้อมูลอ้างอิง) และข้อมูลที่เกี่ยวข้องอย่างใกล้ชิดในความหมาย
• โดยทั่วไป ถ้าคุณต้องการเข้าร่วมมากกว่าห้าหรือหกตารางในแบบสอบถามจำนวนมาก คุณควรพิจารณา denormalize ฐานข้อมูล
• การทำให้เป็นปกติโดยการเพิ่มฟิลด์เพิ่มเติมลงในตารางใดตารางหนึ่ง ในกรณีนี้ ข้อมูลซ้ำซ้อนปรากฏขึ้น จำเป็นต้องดำเนินการเพิ่มเติมเพื่อรักษาความสมบูรณ์ของฐานข้อมูล

ค่าโดยประมาณ

บ่อยครั้ง การสืบค้นช้าและใช้ทรัพยากรจำนวนมาก ซึ่งมีการคำนวณที่ซับซ้อน โดยเฉพาะอย่างยิ่งเมื่อใช้การจัดกลุ่มและฟังก์ชันการรวม (Sum, Max ฯลฯ) บางครั้งก็สมเหตุสมผลที่จะเพิ่ม 1-2 คอลัมน์เพิ่มเติมในตารางที่มีข้อมูลการคำนวณที่ใช้บ่อย (และคำนวณยาก)

สมมติว่าคุณต้องการกำหนดต้นทุนรวมของการสั่งซื้อแต่ละครั้ง ในการทำเช่นนี้ ก่อนอื่นคุณต้องกำหนดต้นทุนของแต่ละผลิตภัณฑ์ (ตามสูตร "จำนวนหน่วยผลิตภัณฑ์" * "ราคาต่อหน่วยของผลิตภัณฑ์" - ส่วนลด) หลังจากนั้น คุณต้องจัดกลุ่มค่าใช้จ่ายตามคำสั่งซื้อ

การดำเนินการค้นหานี้ค่อนข้างซับซ้อน และหากฐานข้อมูลจัดเก็บข้อมูลเกี่ยวกับคำสั่งซื้อจำนวนมาก อาจใช้เวลานาน แทนที่จะดำเนินการค้นหาดังกล่าว คุณสามารถกำหนดต้นทุนได้ในขั้นตอนของการสั่งซื้อและจัดเก็บไว้ในคอลัมน์แยกต่างหากของตารางคำสั่งซื้อ ในกรณีนี้ เพื่อให้ได้ผลลัพธ์ที่ต้องการ ก็เพียงพอที่จะแยกค่าที่คำนวณล่วงหน้าออกจากคอลัมน์นี้

การสร้างคอลัมน์ที่มีค่าที่คำนวณล่วงหน้าจะช่วยประหยัดเวลาได้มากเมื่อเรียกใช้คิวรี แต่คุณต้องอัปเดตข้อมูลในคอลัมน์นั้นในเวลาที่เหมาะสม

ปีกยาว

หากเรามีตารางขนาดใหญ่ในฐานข้อมูลที่มีเขตข้อมูลแบบยาว (Blob, Long ฯลฯ) เราก็สามารถเร่งการดำเนินการสืบค้นไปยังตารางดังกล่าวได้อย่างจริงจังหากเราย้ายเขตข้อมูลแบบยาวไปยังตารางแยกต่างหาก เราต้องการสร้างแคตตาล็อกภาพถ่ายในฐานข้อมูล รวมถึงการจัดเก็บภาพถ่ายในฟิลด์หยด (คุณภาพระดับมืออาชีพ ความละเอียดสูง และขนาดที่เหมาะสม) จากมุมมองของการทำให้เป็นมาตรฐาน โครงสร้างตารางต่อไปนี้จะถูกต้องอย่างยิ่ง:

• รูปถ่ายประจำตัว
• รหัสผู้แต่ง
• รหัสรุ่นของกล้อง
• ภาพถ่ายเอง (ช่องหยด)

ทีนี้ลองนึกดูว่าคิวรีจะทำงานนานแค่ไหนโดยนับจำนวนภาพที่ถ่ายโดยผู้เขียนคนใด ...

วิธีแก้ไขที่ถูกต้อง (แม้ว่าจะละเมิดหลักการของการทำให้เป็นมาตรฐาน) ในสถานการณ์เช่นนี้คือการสร้างตารางอื่นที่ประกอบด้วยฟิลด์เพียงสองฟิลด์เท่านั้น นั่นคือ ID รูปภาพและฟิลด์หยดที่มีรูปถ่าย จากนั้นการเลือกจากตารางหลัก (ซึ่งไม่มีฟิลด์หยดขนาดใหญ่อีกต่อไป) จะไปทันที แต่เมื่อเราต้องการดูรูปถ่ายก็รอ ...

จะทราบได้อย่างไรว่าเมื่อใดที่ denormalization เป็นธรรม?

8.4 ข้อดีและข้อเสียของการทำให้เป็นปกติ

วิธีหนึ่งที่จะตัดสินว่าขั้นตอนบางอย่างสมเหตุสมผลหรือไม่คือการวิเคราะห์ในแง่ของต้นทุนและผลประโยชน์ที่เป็นไปได้ โมเดลข้อมูลที่ไม่ปกติจะมีราคาเท่าใด

กำหนดข้อกำหนด (สิ่งที่เราต้องการให้บรรลุ) → กำหนดข้อกำหนดข้อมูล (สิ่งที่เราต้องทำตาม) → ค้นหาขั้นตอนขั้นต่ำที่เป็นไปตามข้อกำหนดเหล่านี้ → คำนวณค่าใช้จ่ายในการดำเนินการ → ดำเนินการ

ค่าใช้จ่ายรวมถึงลักษณะทางกายภาพ เช่น พื้นที่ดิสก์ ทรัพยากรที่จำเป็นในการจัดการโครงสร้างนี้ และโอกาสที่สูญเสียไปเนื่องจากความล่าช้าของเวลาที่เกี่ยวข้องกับการรักษากระบวนการนี้ คุณต้องจ่ายสำหรับการทำให้เป็นปกติ ฐานข้อมูลที่ไม่ปกติจะเพิ่มความซ้ำซ้อนของข้อมูล ซึ่งสามารถปรับปรุงประสิทธิภาพการทำงาน แต่ต้องใช้ความพยายามมากขึ้นในการควบคุมข้อมูลที่เกี่ยวข้อง กระบวนการสร้างแอปพลิเคชันจะยากขึ้น เนื่องจากข้อมูลจะถูกทำซ้ำและติดตามได้ยากขึ้น นอกจากนี้ การนำ Referential Integrity ไปใช้นั้นไม่ใช่เรื่องง่าย ข้อมูลที่เกี่ยวข้องจะถูกแบ่งออกเป็นตารางต่างๆ

ประโยชน์ต่างๆ ได้แก่ ประสิทธิภาพการค้นหาที่เร็วขึ้นและความสามารถในการรับการตอบกลับที่เร็วขึ้น คุณยังสามารถเก็บเกี่ยวผลประโยชน์อื่นๆ ได้ เช่น ปริมาณงานที่เพิ่มขึ้น ความพึงพอใจของลูกค้า และประสิทธิภาพการทำงาน รวมถึงการใช้เครื่องมือสำหรับนักพัฒนาจากภายนอกอย่างมีประสิทธิภาพมากขึ้น

อัตราคำขอและความสม่ำเสมอของประสิทธิภาพ

ตัวอย่างเช่น 72% ของแบบสอบถาม 1,000 รายการที่สร้างขึ้นทุกวันโดยองค์กรเป็นแบบสอบถามระดับสรุป ไม่ใช่แบบสอบถามเจาะลึก เมื่อใช้ตารางสรุป การสืบค้นจะใช้เวลาประมาณ 6 วินาทีแทนที่จะเป็น 4 นาที ส่งผลให้เวลาในการประมวลผลลดลง 3,000 นาที แม้หลังจากปรับเวลา 100 นาทีที่ต้องใช้ในการดูแล pivot table ในแต่ละสัปดาห์แล้ว นั่นก็ช่วยประหยัดเวลาได้ 2,500 นาทีต่อสัปดาห์ ซึ่งเป็นเหตุผลในการสร้าง pivot table เมื่อเวลาผ่านไป อาจเกิดขึ้นที่ข้อความค้นหาส่วนใหญ่จะไม่ได้ระบุถึงข้อมูลสรุป แต่จะส่งไปยังข้อมูลโดยละเอียด ยิ่งแบบสอบถามที่ใช้ตารางสรุปน้อยลงเท่าใด การทิ้งตารางก็จะง่ายขึ้นโดยไม่ส่งผลกระทบต่อกระบวนการอื่นๆ

และ…

เกณฑ์ที่ระบุไว้ข้างต้นไม่ใช่เกณฑ์เดียวที่ควรพิจารณาเมื่อตัดสินใจว่าจะดำเนินการขั้นตอนถัดไปในการเพิ่มประสิทธิภาพหรือไม่ จำเป็นต้องพิจารณาปัจจัยอื่นๆ รวมถึงลำดับความสำคัญของธุรกิจและความต้องการของผู้ใช้ปลายทาง ผู้ใช้ต้องเข้าใจว่าจากมุมมองทางเทคนิค สถาปัตยกรรมระบบได้รับผลกระทบจากความต้องการของผู้ใช้ที่ต้องการให้คำขอทั้งหมดเสร็จสิ้นภายในไม่กี่วินาทีอย่างไร วิธีที่ง่ายที่สุดในการทำความเข้าใจนี้คือการสรุปค่าใช้จ่ายที่เกี่ยวข้องกับการสร้างและจัดการตารางดังกล่าว

8.5 วิธีการใช้ denormalization อย่างมีประสิทธิภาพ

บันทึกตารางรายละเอียด

เพื่อไม่ให้จำกัดความสามารถของฐานข้อมูลที่มีความสำคัญต่อธุรกิจ จึงจำเป็นต้องปรับใช้กลยุทธ์การอยู่ร่วมกัน ไม่ใช่การแทนที่ กล่าวคือ เก็บตารางรายละเอียดสำหรับการวิเคราะห์เชิงลึก เพิ่มโครงสร้างที่ไม่ปกติเข้าไป ตัวอย่างเช่น Hit Counter สำหรับธุรกิจ คุณจำเป็นต้องทราบจำนวนการเข้าชมหน้าเว็บ แต่สำหรับการวิเคราะห์ (ตามช่วงเวลา ตามประเทศ...) เราน่าจะต้องการข้อมูลโดยละเอียด - ตารางที่มีข้อมูลเกี่ยวกับการเยี่ยมชมแต่ละครั้ง

การใช้ทริกเกอร์

เป็นไปได้ที่จะทำให้โครงสร้างฐานข้อมูลไม่ปกติและยังคงได้รับประโยชน์จากการทำให้เป็นมาตรฐานโดยใช้ทริกเกอร์ฐานข้อมูลเพื่อรักษาความintegrityสมบูรณ์ของข้อมูลที่ซ้ำกัน

ตัวอย่างเช่น เมื่อเพิ่มเขตข้อมูลจากการคำนวณ แต่ละคอลัมน์ซึ่งขึ้นอยู่กับเขตข้อมูลจากการคำนวณ จะถูกวางสายด้วยทริกเกอร์ที่เรียกใช้กระบวนงานที่เก็บไว้เพียงขั้นตอนเดียว (นี่เป็นสิ่งสำคัญ!) ซึ่งจะเขียนข้อมูลที่จำเป็นลงในเขตข้อมูลจากการคำนวณ จำเป็นเท่านั้นที่จะไม่ข้ามคอลัมน์ใด ๆ ที่ขึ้นอยู่กับเขตข้อมูลจากการคำนวณ

การสนับสนุนซอฟต์แวร์

หากคุณไม่ได้ใช้ทริกเกอร์ในตัวและขั้นตอนการจัดเก็บ ผู้พัฒนาแอปพลิเคชันควรดูแลความสอดคล้องของข้อมูลในฐานข้อมูลที่ไม่ปกติ

โดยการเปรียบเทียบกับทริกเกอร์ ควรมีฟังก์ชันหนึ่งที่อัปเดตฟิลด์ทั้งหมดที่ขึ้นอยู่กับฟิลด์ที่กำลังเปลี่ยนแปลง

ข้อสรุป

สิ่งสำคัญคือต้องรักษาสมดุลระหว่างการเพิ่มความเร็วของฐานข้อมูลและการเพิ่มความเสี่ยงของข้อมูลที่ไม่สอดคล้องกัน ระหว่างการทำให้ชีวิตง่ายขึ้นสำหรับผู้เขียนโปรแกรมSelect-sและทำให้งานที่ซับซ้อนของผู้ที่ให้ข้อมูลประชากรฐานข้อมูลและการอัปเดตข้อมูล ดังนั้นจึงจำเป็นต้อง denormalize ฐานข้อมูลอย่างระมัดระวัง คัดเลือกมาก เฉพาะที่จำเป็นเท่านั้น

หากไม่สามารถคำนวณข้อดีและข้อเสียของการดีนอร์มัลไลเซชันล่วงหน้าได้ ขั้นแรกจำเป็นต้องใช้โมเดลที่มีตารางนอร์มัลไลซ์ และจากนั้นจึงทำดีนอร์มัลไลเซชันเพื่อเพิ่มประสิทธิภาพการสืบค้นที่มีปัญหา

สิ่งสำคัญคือต้องแนะนำ denormalization อย่างค่อยเป็นค่อยไป และเฉพาะในกรณีที่มีการดึงข้อมูลที่เกี่ยวข้องกันซ้ำๆ จากตารางต่างๆ โปรดจำไว้ว่า เมื่อทำสำเนาข้อมูล จำนวนของระเบียนจะเพิ่มขึ้น แต่จำนวนของการอ่านจะลดลง นอกจากนี้ยังสะดวกในการจัดเก็บข้อมูลจากการคำนวณในคอลัมน์เพื่อหลีกเลี่ยงการเลือกแบบรวมที่ไม่จำเป็น