การแก้ปัญหาคอขวดด้วยดัชนี SQL และพาร์ติชั่น
เผยแพร่แล้ว: 2022-03-11ในบทเรียนแรกของ SQL Indexes Explained เราได้เรียนรู้ว่าการสืบค้นข้อมูล SELECT นั้นเร็วขึ้นเมื่อข้อมูลถูกจัดเรียงตามค่าของคอลัมน์เฉพาะแล้ว
ในบทเรียนที่สอง เราได้เรียนรู้โครงสร้างพื้นฐานของดัชนี B-tree และวิธีใช้ดัชนีเหล่านี้เพื่อลดปริมาณข้อมูลที่เราเข้าถึงขณะดำเนินการค้นหา นอกจากนี้เรายังพบวิธีใช้งานการสืบค้นร่วมกับหลายตารางและวิธีที่ดัชนีสามารถเร่งความเร็วการสืบค้นดังกล่าว
เรายังเน้นถึงสองสถานการณ์ที่การใช้ดัชนีใน SQL มีประโยชน์ เมื่อดัชนีครอบคลุมดัชนี ซึ่งมีคอลัมน์ทั้งหมดจากการสืบค้น จากเงื่อนไข WHERE เงื่อนไข JOIN และรายการ SELECT เราจะหลีกเลี่ยงการอ่านตารางที่เกี่ยวข้องทั้งหมด อีกทางหนึ่ง ดัชนีสามารถช่วยได้เมื่อลดจำนวนบล็อกข้อมูลที่เข้าถึงได้เพียงเศษเสี้ยวหนึ่งของขนาดตาราง
มิฉะนั้น การสแกนทั้งตารางจะมีประสิทธิภาพมากกว่าการอ่านจากดัชนีและข้ามไปมาแบบสุ่มไปยังแถวของตารางที่เกี่ยวข้อง
แบบสอบถามช่วง SQL
แบบสอบถามที่สามารถใช้ประโยชน์จากดัชนีมักจะรวมถึงเงื่อนไขที่ลดช่วงของค่าที่เป็นไปได้หนึ่งคอลัมน์หรือมากกว่าอย่างมีนัยสำคัญ การสืบค้นข้อมูลตามช่วงจะจำกัดข้อมูลตามเงื่อนไข เช่น "ค่าของคอลัมน์ A ต้องอยู่ระหว่าง X และ Y"
ตัวอย่างที่ดีคือคำถามจากแบบฝึกหัดที่ 4 ของบทเรียนที่สอง:
SELECT c.ClientName FROM Reservations r JOIN Clients c ON r.ClientID = c.ClientID WHERE r.DateFrom BETWEEN ( TO_DATE('2020-08-13', 'YYYY-MM-DD') AND TO_DATE('2020-08-14', 'YYYY-MM-DD') ) AND r.HotelID = 3; ที่นี่เรามีสองช่วง ช่วงแรกคือช่วงวันที่ ช่วงเวลาระหว่างวันที่ 13 สิงหาคม 2020 และ 14 สิงหาคม 2020 ช่วงที่สองคือช่วงตัวเลขที่เล็กที่สุดที่เป็นไปได้ เงื่อนไขเทียบเท่ากับ r.HotelID BETWEEN 3 AND 3
การใช้สิทธิที่ 1: ช่วงเวลา (การสอบถามช่วงวันที่และเวลา)
มาเพิ่มคอลัมน์ที่เรียกว่า CheckInTime ลงในตารางการ Reservations กัน คุณสามารถดูตัวอย่างข้อมูลในสเปรดชีตนี้ สังเกตว่ามีดัชนีเดียวที่ครอบคลุมทั้ง CheckInTime และ ClientId
เขียนแบบสอบถามที่จะส่งคืนชื่อของลูกค้าที่เช็คอินในวันที่ 15 สิงหาคม 2020
นักพัฒนา SQL ที่ไม่มีประสบการณ์มักจะเขียนแบบสอบถามต่อไปนี้:
SELECT c.ClientName FROM Reservations r JOIN Clients c ON r.ClientID = c.ClientID WHERE TO_DATE(r.CheckInTime, 'YYYY-MM-DD') = '2020-08-15';พวกเขาถือว่าการดำเนินการค้นหาจะมีลักษณะดังนี้:
Get first row from IX_CheckInTime_ClientID where TO_DATE(CheckInTime, 'YYYY-MM-DD') = '2020-08-15' While found and TO_DATE(CheckInTime, 'YYYY-MM-DD') = '2020-08-15' Fetch Clients.* where ClientID = IX_CheckInTime_ClientID.ClientID Write down Clients.ClientName Get next row from IX_CheckInTime_ClientID ปัญหาคือไม่มี RDBMS เดียวในขณะที่เขียนนี้สามารถสร้างแผนการดำเนินการดังกล่าวได้ พวกเขาเห็น TO_DATE (ไวยากรณ์ของ Oracle) เป็นฟังก์ชันที่แปลงค่าของคอลัมน์ CheckInTime เป็นสิ่งที่ไม่ได้ทำดัชนี ดังนั้น แผนการดำเนินการที่พวกเขามักจะสร้างจึงมีลักษณะดังนี้:
For each row from IX_CheckInTime_ClientID If TO_DATE(CheckInTime, 'YYYY-MM-DD') = '2020-08-15' then Fetch Clients.* where ClientID = IX_CheckInTime_ClientID.ClientID Write down Clients.ClientName การดำเนินการนี้จะเร็วกว่าการอ่านแถวทั้งหมดจากตารางการ Reservations จ่าย เนื่องจากแถวดัชนีจะแคบกว่าแถวของตาราง แถวที่เล็กกว่าหมายความว่าจะต้องเข้าถึงบล็อกน้อยลงจากดิสก์
อย่างไรก็ตาม เรารู้ว่าแผนปฏิบัติการครั้งแรกจะมีประสิทธิภาพมากกว่ามาก เพื่อเกลี้ยกล่อม RDBMS ของเราให้ใช้วิธีนั้น เราจำเป็นต้องเขียนแบบสอบถามใหม่:
SELECT c.ClientName FROM Reservations r JOIN Clients c ON r.ClientID = c.ClientID WHERE r.CheckInTime >= TO_DATE('2020-08-15 00:00:00', 'YYYY-MM-DD HH:MI:SS') AND r.CheckInTime < TO_DATE('2020-08-16 00:00:00', 'YYYY-MM-DD HH:MI:SS'); นี่คือการสืบค้นช่วงที่เหมาะสม ซึ่งเป็นแบบสอบถามที่ RDBMS ที่ดีทุกแห่งเข้าใจ RDBMS ของเราพบว่าเราต้องการข้อมูลจากตารางการ Reservations โดยที่ค่าของ CheckInTime ไม่ใช่ค่าที่ได้มาจากค่าดังกล่าว ซึ่งอยู่ในช่วงที่กำหนดไว้อย่างดี แผนการดำเนินการที่สร้างขึ้นจะเป็นดังนี้:
Get first row from IX_CheckInTime_ClientID where CheckInTime >= '2020-08-15 00:00:00' While found and CheckInTime < '2020-08-16 00:00:00' Fetch Clients.* where ClientID = IX_CheckInTime_ClientID.ClientID Write down Clients.ClientName Get next row from IX_CheckInTime_ClientIDนั่นคือสิ่งที่เราต้องการจริงๆ: ไม่เพียงแต่ใช้ประโยชน์จากดัชนีเท่านั้น แต่ยังรวมถึงข้อเท็จจริงที่มันถูกจัดเรียงด้วย
แบบฝึกหัดที่ 2: LIKE With a Wildcard at the Start
คราวนี้นักสืบของเรามาที่โรงแรมพร้อมข้อมูลคลุมเครือเกี่ยวกับผู้ต้องสงสัย นามสกุลลงท้ายด้วย "-son" นักสืบต้องการชื่อและนามสกุลของแขกทุกคน:
SELECT FirstName, LastName FROM Clients WHERE LastName LIKE '%son'; สำหรับตาราง Clients และดัชนีบน LastName เราจะใช้สเปรดชีตนี้ เขียนผลลัพธ์ที่แบบสอบถามจะส่งคืน ลองนึกถึงแนวทางต่างๆ ที่คุณนำไปใช้ได้
วิธีสแกนตาราง
กลยุทธ์ที่ง่ายที่สุดคืออ่านข้อมูลทั้งหมดจากตารางและจดชื่อแขกเมื่อนามสกุลลงท้ายด้วย "-son":
For each row from Clients If LastName like '%son' then write down FirstName, LastNameที่นี่เราจะต้องอ่านทั้งตารางตามลำดับ
การใช้ดัชนี
ลองใช้ประโยชน์จากดัชนีในคอลัมน์ LastName ไปที่ชีต IX_LastName ใช้เพื่อค้นหาไคลเอนต์ทั้งหมดที่ตรงตามเกณฑ์ที่กำหนด และจดชื่อของพวกเขา
ปรากฎว่าคุณต้องอ่านดัชนีทั้งหมดเพื่อค้นหา Andersons, Robinsons และ Thompsons ทั้งหมดจากตาราง จะดีกว่าการใช้การสแกนตารางหรือไม่? นอกจากการอ่านดัชนีทั้งหมดแล้ว คุณยังต้องค้นหาสำหรับรายการที่ตรงกันทุกแถวจากตารางโดยใช้ค่า rowAddress จากนั้นจด FirstName จากที่นั่น:
For each row from IX_LastName If LastName like '%son' then Fetch Clients.* where RowAddress = IX_LastName.RowAddress Write down FirstName, LastNameสำหรับเรา การอ่านตารางตามลำดับจะง่ายกว่าและเร็วกว่า สำหรับ RDBMS ของเรา จะขึ้นอยู่กับเปอร์เซ็นต์ของแถวที่ตรงตามเกณฑ์ หากมี Andersons, Robinsons และ Thompsons เพียงไม่กี่คนในตารางขนาดใหญ่ RDBMS จะอ่านบล็อคข้อมูลน้อยลงจากรายการดัชนีที่แคบกว่ามาก แม้ว่าจะต้องอ่านช่วงสองสามช่วงตึกจากตารางเมื่อพบการจับคู่ มิฉะนั้น การสแกนตารางจะใช้เวลาน้อยลง
การสั่งซื้อข้อมูลในดัชนีไม่ได้ให้ความช่วยเหลือใดๆ กับแบบสอบถามดังกล่าว ขนาดที่เล็กกว่าของแถวดัชนีอาจมีประโยชน์—แต่ในบางครั้งเท่านั้น
แบบฝึกหัดที่ 3: LIKE ด้วยสัญลักษณ์แทนในตอนท้าย
ครั้งต่อไปที่นักสืบของเรามา เราต้องหาลูกค้าทั้งหมดที่มีนามสกุลขึ้นต้นด้วย “ร็อบ-”
SELECT FirstName, LastName FROM Clients WHERE LastName LIKE 'Rob%';ลองดึงข้อมูลที่ตรงกับคิวรีออกจากสเปรดชีตเดียวกัน
หากคุณใช้วิธีการสแกนตาราง คุณพลาดโอกาสที่จะใช้ประโยชน์จากดัชนี IX_LastName อย่างเต็มที่ การค้นหารายการแรกจากดัชนีที่ขึ้นต้นด้วย “Rob-” (Roberts) นั้นเร็วกว่ามาก อ่านแถวถัดมา (ทั้ง Robertses และ Robinsons) และหยุดเมื่อ LastName ไม่ตรงกับเกณฑ์อีกต่อไป:
Get first row from IX_LastName where LastName <= 'Rob' While found and LastName < 'Roc' Fetch Clients.* where rowAddress = IX_LastName.rowAddress Write down FirstName, LastName Get next from IX_LastNameในกรณีนี้ หลังจากการค้นหา B-tree สำหรับรายการแรก เราจะอ่านเฉพาะรายการที่ตรงตามเกณฑ์เท่านั้น เราหยุดอ่านทันทีที่เราอ่านชื่อที่ไม่ตรงกับเกณฑ์
การจัดการปัญหาการขูดหินปูนของ B-tree
โดยปกติเมื่อเราปรับใช้ฐานข้อมูลใหม่ จะมีตารางค้นหาและตารางธุรกรรมที่ว่างเปล่า ระบบทำงานได้อย่างราบรื่นตั้งแต่เริ่มต้น โดยเฉพาะอย่างยิ่งถ้าเราปฏิบัติตามแนวทางปฏิบัติที่ดีของการออกแบบฐานข้อมูลโดยการปรับตารางให้เป็นมาตรฐาน การสร้างคีย์หลัก คีย์นอก และคีย์เฉพาะ และรองรับกุญแจต่างประเทศพร้อมดัชนีที่เกี่ยวข้อง
หลังจากผ่านไปสองสามเดือนหรือหลายปี เมื่อปริมาณข้อมูลเพิ่มความซับซ้อนของระบบและฐานข้อมูลอย่างเห็นได้ชัด เราเริ่มสังเกตเห็นว่าประสิทธิภาพลดลง มีความคิดเห็นเกี่ยวกับสาเหตุที่ระบบทำงานช้าลงและต้องทำอย่างไร
ความคิดเห็นที่ได้รับความนิยมมักเกิดจากขนาดของฐานข้อมูลเป็นตัวการหลัก วิธีแก้ปัญหาน่าจะเป็นการลบข้อมูลในอดีตที่เราไม่ต้องการในแต่ละวัน และใส่ไว้ในฐานข้อมูลแยกต่างหากสำหรับการรายงานและการวิเคราะห์
มาสำรวจสมมติฐานหลักกันก่อน
แบบสอบถามช่วง SQL: เวลาดำเนินการขึ้นอยู่กับขนาดตารางหรือไม่
พิจารณาคิวรีช่วงทั่วไปจากตารางเดียว:
SELECT Column1, …, ColumnN FROM Table WHERE Column BETWEEN X AND Y; สมมติว่ามีดัชนีบน Column แผนการดำเนินการที่เหมาะสมคือ:
Get first row from IX_Column where Column between X and Y While found and Column <= Y Fetch Table.* where rowAddress = IX_Column.rowAddress Write down Column1, …, ColumnN Get next row from IX_Columnมานับบล็อคที่ RDBMS จะต้องอ่านเพื่อส่งคืนข้อมูลนี้
ส่วน Get first row ถูกใช้งานโดยการค้นหา B-tree ที่เราแนะนำในบทเรียนที่สอง จำนวนบล็อคที่ต้องอ่านเท่ากับความลึกของ B-tree หลังจากนั้น เราอ่านรายการต่อจากระดับลีฟของดัชนี
เมื่อใช้คำสั่ง OLTP โดยทั่วไปจะพบผลลัพธ์ทั้งหมดภายในบล็อกดัชนีเดียว (บางครั้งอาจพบสองบล็อกแต่อาจน้อยกว่านี้) นอกจากนั้น สำหรับแต่ละรายการดัชนี เรามีสิทธิ์เข้าถึงบล็อกในตารางเพื่อค้นหาแถวที่เกี่ยวข้องตามที่อยู่ แถวของตารางบางแถวอาจอยู่ภายในบล็อกตารางเดียวกันกับที่เราโหลดไว้แล้ว แต่เพื่อให้การประมาณง่ายขึ้น สมมติว่าเราโหลดบล็อกใหม่ทุกครั้ง
ดังนั้นสูตรคือ:
B = D + 1 + R
B คือจำนวนบล็อกทั้งหมดที่อ่าน D คือความลึกของ B-tree และ R คือจำนวนแถวที่ส่งคืนโดยแบบสอบถาม
พารามิเตอร์เดียวที่ขึ้นอยู่กับจำนวนแถวในตารางคือ D ซึ่งเป็นความลึกของ B-tree
เพื่อให้การคำนวณง่ายขึ้นและสร้างประเด็น สมมติว่ารายการดัชนี 1,000 รายการพอดีกับหนึ่งบล็อก D = 1 ตราบใดที่มีน้อยกว่า 1,000 แถวในตาราง สำหรับตารางที่มีธุรกรรมทางธุรกิจ นั่นอาจเป็นวันทำการแรกหลังจากการปรับใช้ระบบ ในไม่ช้าความลึกของต้นไม้ B จะเพิ่มขึ้น ตราบใดที่มีน้อยกว่า 1 ล้านแถวในตาราง ดัชนีจะประกอบด้วยสองระดับ
หากเรากังวลเกี่ยวกับเวลาตอบสนองของฐานข้อมูลที่ช้าและตำหนิปริมาณข้อมูลสำหรับสิ่งนี้ โปรดทราบว่าตารางธุรกรรมมักจะมีแถวนับล้านเท่านั้น เนื่องจากมีเพียง 1 ล้านแถวเท่านั้นที่พอดีกับดัชนี B-tree สองระดับ ความลึกจึงต้องมีอย่างน้อยสาม ความลึกจะไม่เพิ่มขึ้นเป็นสี่เว้นแต่ว่ามีมากกว่า 1 พันล้านแถวในตาราง ตอนนี้เรามีค่าประมาณที่แม่นยำยิ่งขึ้น:
B = 4 + R
ถ้า R มีขนาดเล็ก การลดความลึกของ B-tree กลับไปเป็นสองจะทำให้การสืบค้นเร็วขึ้นอย่างมาก เมื่อเราค้นหาด้วยค่าคีย์หลักหรือค่าคีย์ที่ไม่ซ้ำ ระบบจะอ่านบล็อกสี่ช่วงแทนที่จะเป็นห้า ซึ่งเป็นการปรับปรุง 20% ถ้าคิวรีส่งกลับแถวมากขึ้น การปรับปรุงอาจไม่สังเกตเห็นได้ ปัญหาคือ สำหรับหลายๆ แอปพลิเคชัน เราอาจไม่สามารถสนับสนุนการดำเนินธุรกิจที่จำเป็นโดยเก็บธุรกรรมน้อยกว่า 1 ล้านรายการในฐานข้อมูล
ดังนั้นข้อสรุปจึงดูเหมือนว่าขนาดโต๊ะไม่สำคัญ กล่าวอีกนัยหนึ่ง การย้ายข้อมูลในอดีตเป็นการเสียเวลาและทรัพยากรไปเปล่าๆ
แต่ไม่เร็วนัก: มาเรียนรู้เพิ่มเติมเกี่ยวกับโครงสร้างของดัชนี B-tree และผลกระทบต่อการเปลี่ยนแปลงของข้อมูล
รายละเอียดการใช้งานดัชนี B-tree
ในเนื้อหาเกี่ยวกับดัชนี B-tree ในบทเรียนที่สอง เราเห็นว่าต้นไม้ทุกระดับมีความสมดุล (ทางกายภาพ) เรียงตามค่าคอลัมน์หลัก อย่างไรก็ตาม เมื่อเราต้องการแทรก อัปเดต หรือลบรายการ เรามักจะต้องย้ายข้อมูลจำนวนมากเพื่อรักษาลำดับ
สมมติว่าเรากำลังแทรกกลางบล็อกที่เต็ม เราต้องแยกบล็อก จัดเรียงข้อมูลใหม่ และบางครั้งถึงกับอัปเดตข้อมูลในระดับ B-tree อื่นที่ชี้ไปยังระดับปัจจุบัน
เพื่อให้กรณีดังกล่าวมีประสิทธิภาพมากขึ้น แต่ละรายการดัชนีจะมีตัวชี้ไปยังแถวก่อนหน้าและแถวถัดไป ทำให้มีการเชื่อมโยงสองครั้ง สำหรับการแทรกโดยทั่วไป นี่หมายความว่าเราเพียงแค่เขียนรายการใหม่ให้ใกล้เคียงกับรายการก่อนหน้ามากที่สุดและแก้ไขพอยน์เตอร์
เมื่อเราต้องแยกบล็อกด้วย เราต้องเขียนรายการใหม่ในระดับ B-tree ก่อนหน้า นี่เป็นเพียงเรื่องของการแก้ไขพอยน์เตอร์อีกสองสามข้อ—ไม่จำเป็นต้องเขียนส่วนขนาดใหญ่ของแผนผังใหม่ หลังจากแยกแล้ว ข้อมูลทั้งสองช่วงจะเต็มประมาณครึ่งหนึ่ง ขึ้นอยู่กับตำแหน่งที่มีเนื้อที่ว่างบนดิสก์ บล็อก "เพื่อนบ้าน" อาจอยู่ห่างไกลจากกัน
หลังจากเวลาผ่านไป การกระจายตัวของดัชนีจะเพิ่มขึ้น และการเรียกใช้คิวรีช้าลงจะสังเกตเห็นได้ชัดเจน เมื่อใช้ RDBMS ในการสืบค้นตามที่เราอธิบาย สมมติฐานของลำดับและความใกล้เคียงของรายการจะถูกต้องน้อยลงเรื่อยๆ ส่งผลให้มีการอ่านมากขึ้น ในกรณีที่เลวร้ายที่สุด โดยที่บล็อคข้อมูลทั้งหมดว่างเปล่าครึ่งหนึ่ง ระบบจะต้องอ่านบล็อคมากกว่าสองเท่า
การบำรุงรักษาดัชนี B-tree
วิธีแก้ไขคือการจัดเรียงข้อมูลดัชนี (หรือ "การทำดัชนีใหม่") RDBMS ทุกตัวมีคุณสมบัติในการสร้างดัชนีใหม่ทั้งหมด หลังจากสร้างดัชนีใหม่แล้ว ดัชนีจะถูกจัดเรียงตามร่างกายอีกครั้ง
การทำดัชนีใหม่เป็นการดำเนินการที่ค่อนข้างรวดเร็ว แม้ว่าจะอ่านและเขียนข้อมูลปริมาณมากก็ตาม โดยทั่วไปแล้ว RDBMS สมัยใหม่จะมีโหมดการทำดัชนีใหม่สองโหมด โดยโหมดที่เร็วกว่าซึ่งกำหนดให้ต้องล็อกตารางระหว่างการประมวลผล ไม่ว่าจะด้วยวิธีใด ควรทำดัชนีใหม่ในช่วงนอกชั่วโมงเร่งด่วน มิฉะนั้น การประมวลผลอาจทำให้ประสิทธิภาพของฐานข้อมูลช้าลง
การลบข้อมูลย้อนหลัง
เมื่อเรามีตารางที่มีแถวเป็นพันล้านหรือหลายร้อยล้านแถว อาจเป็นไปไม่ได้ที่จะทำดัชนีใหม่ให้เสร็จสิ้นในระหว่างชั่วโมงเร่งด่วน
เพื่อหลีกเลี่ยงสถานการณ์นี้ การย้ายข้อมูลในอดีตออกจากฐานข้อมูล OLTP อาจเป็นวิธีแก้ปัญหา อย่างไรก็ตาม หากเราเพียงแค่ลบแถวที่เก่ากว่าเกณฑ์ที่กำหนด เราจะทำให้ดัชนีมีการแยกส่วนมากยิ่งขึ้น และเราจำเป็นต้องสร้างดัชนีใหม่ให้บ่อยขึ้น
การแบ่งพาร์ติชัน SQL เพื่อช่วยเหลือ?
มีวิธีหลีกเลี่ยงการกระจายตัวที่เกิดจากการลบข้อมูลในอดีต โดยเก็บเฉพาะธุรกรรมที่ "ใช้งานอยู่" ในฐานข้อมูลที่ใช้งานจริง แนวคิดที่ว่า RDBMS หลักทั้งหมดนำไปใช้คือการแบ่งตารางออกเป็นชิ้นเล็ก ๆ (เรียกว่า partitions ) และจัดให้มีความสามารถในการเพิ่ม ลบออก และแม้กระทั่งสลับระหว่างตาราง (เช่น จากตารางที่ใช้งานอยู่ไปเป็นตารางในอดีตที่เหมือนกัน โครงสร้าง).
มาดูตารางการ Reservations ที่แบ่งพาร์ติชั่นในสเปรดชีตนี้กัน ตารางจะแบ่งตามเดือน โดยมีชื่อพาร์ติชั่นที่แมปกับช่วงวันที่และสเปรดชีตอื่นๆ หากต้องการดูวิธีดำเนินการสืบค้นข้อมูลในตารางที่แบ่งพาร์ติชัน เราจะทำแบบฝึกหัดสองสามข้อ
แบบฝึกหัดที่ 4: แบบสอบถามพาร์ติชันในSQL
จากสเปรดชีตที่ลิงก์ด้านบน ให้พยายามดึงข้อมูลที่ร้องขอโดยข้อความค้นหาต่อไปนี้ โดยไม่ต้องใช้ดัชนีใดๆ
SELECT HotelID, ReservationID, ClientID, DateFrom, DateTo FROM Reservations WHERE DateFrom BETWEEN TO_DATE('2021-03-01','YYYY-MM-DD') AND TO_DATE('2021-03-03');คุณอาจพบว่าคุณต้องดูที่แผ่นงานการแมปพาร์ติชั่นก่อนแล้วจึงค้นหาพาร์ติชั่นที่มีการจองตั้งแต่เดือนมีนาคม 2021 หลังจากนั้น คุณเปิดพาร์ติชั่นที่เกี่ยวข้อง อ่านข้อมูลตามลำดับ และกรองแถวที่ไม่ตรงตามข้อกำหนด สภาพ.
แม้ว่าจะตรงไปตรงมา แต่คุณอาจไม่ชอบเก็บแถวไม่กี่แถวหลังจากอ่านหลายๆ แถว การอ่านพาร์ติชั่นเดือนมีนาคมดีกว่าการอ่านตารางการจองทั้งหมด แต่ก็ยังไม่เหมาะ แล้วดัชนีล่ะ?
ดัชนีโลก
RDBMS ช่วยให้เราสร้าง ดัชนีส่วนกลาง เพื่อให้ครอบคลุมทุกพาร์ติชั่นของตารางที่แบ่งพาร์ติชั่น แต่ไม่มีความแตกต่างระหว่างวิธีการทำงานของดัชนีส่วนกลางและดัชนีปกติด้านล่าง: ดัชนีส่วนกลางไม่รับรู้ถึงพาร์ติชั่น ดังนั้น CRUD เคียวรีโดยใช้ดัชนีโกลบอลจึงไม่เกี่ยวข้องกับการแมปพาร์ติชั่นสำหรับตาราง
เราจำเป็นต้องอัปเดตแผนที่พาร์ติชั่นก็ต่อเมื่อเราปล่อยทั้งพาร์ติชั่น จากนั้นเราต้องลบแถวใดๆ ออกจากดัชนีที่ชี้ไปยังพาร์ติชั่นที่ถูกลบ นั่นหมายความว่าทั้งดัชนีทั่วโลกจำเป็นต้องสร้างใหม่
หน้าต่างการหยุดทำงานยังคงมีความจำเป็นเนื่องจากไม่สามารถใช้ดัชนีได้จนกว่าจะนำรายการที่ล้าสมัยออก หากเราสามารถทิ้งพาร์ติชั่นได้เป็นประจำ โดยจำกัดจำนวนพาร์ติชั่นที่ใช้งานอยู่ การดำเนินการจัดทำดัชนีใหม่อาจพอดีกับหน้าต่างการหยุดทำงาน ดังนั้นการใช้พาร์ติชั่นจะช่วยแก้ปัญหาเดิมได้โดยการลดเวลาที่จำเป็นสำหรับงานบำรุงรักษา ซึ่งรวมถึงการบำรุงรักษาดัชนีส่วนกลาง
แต่ถ้าเรายังไม่สามารถจ่ายไฟดับได้ล่ะ?
ดัชนีที่แบ่งพาร์ติชันทั่วโลก
กลยุทธ์นี้แก้ปัญหานั้นได้: เราแบ่งพาร์ติชั่นดัชนีแบบเดียวกับที่เราแบ่งพาร์ติชั่นตาราง ในสเปรดชีตที่สเปรดชีตของพาร์ติชั่นลิงก์ไป แต่ละพาร์ติชั่นจะมีส่วนของตาราง Reservations และชีตดัชนีชื่อ IX_DateFrom ซึ่งแบ่งพาร์ติชั่นโดย DateFrom
ในการดำเนินการค้นหาจากแบบฝึกหัดที่ 4 อันดับแรก RDBMS จะดูที่แผนที่พาร์ติชั่นดัชนีและระบุว่าพาร์ติชั่นใดมีวันที่จากช่วง (ในกรณีของเรา มันเป็นพาร์ติชั่นดัชนีเพียงหนึ่งพาร์ติชั่น) หลังจากนั้น จะใช้การค้นหาแบบ B-tree วนไปยังระดับลีฟ และสุดท้ายเข้าถึงตารางโดยใช้ที่อยู่แถวที่เกี่ยวข้อง
เมื่อเราปล่อยพาร์ติชั่นจากตาราง ก็เพียงพอแล้วที่จะดร็อปพาร์ติชั่นที่เกี่ยวข้องจากดัชนี ไม่จำเป็นต้องหยุดทำงาน
ดัชนีท้องถิ่น
ข้อเสียเปรียบหลักของดัชนีที่แบ่งพาร์ติชันทั่วโลกคือเราต้องดูแลการวางทั้งตารางและพาร์ติชันดัชนีที่เกี่ยวข้อง มีค่าใช้จ่ายเพิ่มเติมเพียงเล็กน้อยที่เกี่ยวข้องกับการอ่านและบำรุงรักษาพาร์ติชั่นดัชนีเอง
ดัชนีท้องถิ่นเกี่ยวข้องกับแนวทางที่คล้ายกันแต่แตกต่างกันเล็กน้อย แทนที่จะแบ่งพาร์ติชั่นดัชนีโกลบอลเดี่ยว เราสร้างดัชนีโลคัล ภายใน แต่ละพาร์ติชั่นของตาราง ในการทำเช่นนั้น ดัชนีในเครื่องจะแบ่งปันข้อได้เปรียบหลักของดัชนีที่แบ่งพาร์ติชันทั่วโลก กล่าวคือ ไม่มีการหยุดทำงาน ในขณะที่หลีกเลี่ยงข้อเสียของดัชนีเหล่านั้น
ดูเหมือนจะเป็นทางออกที่สมบูรณ์แบบ แต่ก่อนที่เราจะเฉลิมฉลอง เรามาตรวจสอบแผนการดำเนินการที่เป็นไปได้ของคำถามสองสามข้อก่อน
แบบฝึกหัดที่ 5: Locally Partitioned Index
ลองเรียกใช้แบบสอบถามอีกครั้ง คราวนี้โดยใช้ดัชนีที่แบ่งพาร์ติชันในเครื่องบน DateFrom
คุณอาจใช้แผนปฏิบัติการนี้:
For all partitions where [StartDateFrom, StartDateTo) intersects ['2021-03-01', '2021-03-03'] Get first row from IX_DateFrom where DateFrom between '2021-03-01' and '2021-03-03' While found and DateFrom < '2021-03-04' Fetch Reservations.* where RowAddress = IX_DateFrom.RowAddress Write down HotelID, ReservationID, ClientID, DateFrom, DateTo Get next row from IX_DateFromเราโชคดีที่วันที่ทั้งหมดอยู่ในพาร์ติชันเดียว เราจึงต้องสำรวจดัชนีในเครื่องเพียงดัชนีเดียว หากช่วงเวลานั้นยาวหกเดือน เราจะต้องอ่านดัชนีท้องถิ่นหกรายการ
แบบฝึกหัดที่ 6: ตรงกันข้าม
งานของคุณคือใช้แผนที่พาร์ทิชันการจองอีกครั้ง คราวนี้เพื่อสร้างรายการช่วงเวลาที่ลูกค้า 124 เยี่ยมชมโรงแรม 1:
SELECT DateFrom, DateTo FROM Reservations WHERE ClientID = 124 AND HotelID = 1; ที่นี่เราสามารถเห็นข้อเสียเปรียบหลักของดัชนีท้องถิ่น เราต้องอ่านแผ่นดัชนีท้องถิ่น IX_HotelID_CientID จากทุกพาร์ติชั่นของตารางการ Reservations :
For all partitions Get first row from IX_HotelID_ClientID where ClientID = 124 and HotelID = 1 While found and ClientID = 124 and HotelID = 1 Fetch Reservations.* where RowAddress = IX_HotelID_ClientID.RowAddress Write down DateFrom, DateTo Get next row from IX_HotelID_ClientIDการดำเนินการนี้จะอ่านบล็อคจำนวนมากขึ้นอย่างชัดเจนและใช้เวลานานกว่าถ้าตารางไม่ถูกแบ่งพาร์ติชัน
ดังนั้นในขณะที่เราพบวิธีที่จะรักษาประสิทธิภาพของดัชนีของเราในช่วงที่อยู่ในช่วงไม่พีค กลยุทธ์ยังทำให้การสืบค้นบางรายการของเราช้าลงด้วย
หากโมเดลธุรกิจของเราอนุญาตให้เราเก็บพาร์ติชั่นจำนวนน้อยหรืออย่างน้อยการสืบค้นที่บ่อยที่สุดมีเกณฑ์ที่อนุญาตให้ RDBMS อ่านเพียงหนึ่งหรือสองพาร์ติชั่น โซลูชันนี้อาจเป็นสิ่งที่เราต้องการ มิฉะนั้น เราควรหลีกเลี่ยงการแบ่งพาร์ติชั่นและพยายามปรับปรุงโมเดลข้อมูล ดัชนี และคิวรี—และเพิ่มประสิทธิภาพเซิร์ฟเวอร์ฐานข้อมูล
ดัชนีใน SQL: สิ่งที่ต้องเรียนรู้ต่อไป
นี่คือจุดสิ้นสุดของการเดินทางของเรา ในการอธิบายดัชนี SQL ฉันเน้นที่การใช้ดัชนีที่เหมือนกันกับ RDBMS สมัยใหม่ทั้งหมด ฉันยังเน้นไปที่หัวข้อที่นักพัฒนาแอปพลิเคชันสนใจ ด้วยค่าใช้จ่ายของหัวข้อที่มักเกี่ยวข้องกับผู้ดูแลระบบฐานข้อมูล อย่างหลังน่าจะทำได้ดีในการวิจัยผลกระทบของปัจจัยเติมต่อการกระจายตัวของดัชนี แต่ผู้ที่อยู่ในบทบาททั้งสองมักจะพบว่ามีประโยชน์ในการอ่านเพิ่มเติมเกี่ยวกับ:
- การแคชข้อมูลและดัชนี
- โครงสร้างดัชนีแบบ non-B-tree เช่น hash, GiST, bitmap และ columnstore indexes
- ดัชนีคลัสเตอร์ (เรียกว่า ตารางที่จัดดัชนี ใน Oracle)
- ดัชนีการทำงาน
- ดัชนีบางส่วน
วิธีการแบ่งพาร์ติชั่นที่เราพูดถึงคือ การแบ่งช่วง เป็นประเภทการแบ่งพาร์ติชันที่ใช้บ่อยที่สุด แต่ก็มีประเภทอื่นๆ เช่น การแบ่งพาร์ติชันแฮช และการแบ่งพาร์ติชันรายการ นอกจากนี้ RDBMS บางตัวยังมีตัวเลือกการแบ่งพาร์ติชั่นหลายระดับอีกด้วย
สุดท้ายนี้ นักพัฒนา SQL จะพยายามสำรวจหัวข้อสำคัญอื่นๆ เกี่ยวกับการดำเนินการค้นหา RDBMS ก่อน การแยกวิเคราะห์แบบสอบถาม จากนั้นจึงรวบรวมแผนการดำเนินการตามต้นทุน การแคช และการนำกลับมาใช้ใหม่
สำหรับ RDMBS สี่รายการที่ฉันมีประสบการณ์ ฉันขอแนะนำแหล่งข้อมูลเหล่านี้เป็นขั้นตอนต่อไป:
Oracle
- ภาพรวมของตัวเพิ่มประสิทธิภาพ
- ดัชนีและตารางที่จัดดัชนี
- การจัดการดัชนี
- ภาพรวมของพาร์ติชั่น
- คู่มือการแบ่งพาร์ติชัน
- ถามทอม
PostgreSQL
- การประมวลผลคำค้นหา
- ดัชนีใน PostgreSQL
- ดัชนีใน PostgreSQL (เอกสารอย่างเป็นทางการ)
- การจัดการบัฟเฟอร์
- การแบ่งตาราง
- คู่มือการแบ่งพาร์ติชัน
Microsoft SQL Server
- สถาปัตยกรรมการประมวลผลแบบสอบถาม
- ดัชนี
- แบ่งตารางและดัชนี
MySQL/MariaDB
- ทำความเข้าใจกับแผนการดำเนินการสืบค้น
- การเพิ่มประสิทธิภาพและดัชนี
- การแบ่งพาร์ติชัน - พื้นฐาน
- การแบ่งพาร์ติชัน - เอกสารประกอบ
- เอกสาร MariaDB: การเพิ่มประสิทธิภาพการค้นหาและดัชนี