ดัชนี SQL อธิบาย, Pt. 1

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

ในชุดนี้ ดัชนี SQL อธิบาย เราจะอธิบายเกี่ยวกับแรงจูงใจในการใช้ดัชนีเพื่อเข้าถึงข้อมูล และสำหรับการออกแบบดัชนีในลักษณะที่ RDBMS สมัยใหม่ทำทั้งหมด จากนั้นเราจะดูอัลกอริธึมที่ใช้ในการส่งคืนข้อมูลสำหรับรูปแบบการสืบค้นที่เฉพาะเจาะจง

คุณไม่จำเป็นต้องรู้อะไรมากเกี่ยวกับดัชนีเพื่อให้สามารถปฏิบัติตาม ดัชนี SQL ได้ มีเพียงสองเงื่อนไขเบื้องต้น:

• ความรู้พื้นฐานเกี่ยวกับ SQL
• ความรู้พื้นฐานของภาษาการเขียนโปรแกรมใด ๆ

หัวข้อหลัก SQL Indexes ที่อธิบาย จะได้รับคือ:

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

นี่ไม่ใช่แค่บทช่วยสอนเกี่ยวกับดัชนี SQL แต่เป็นการเจาะลึกในการทำความเข้าใจกลไกพื้นฐานของดัชนี

เราจะหาวิธีที่ RDBMS ใช้ดัชนีโดยทำแบบฝึกหัดและวิเคราะห์วิธีการแก้ปัญหาของเรา เอกสารการออกกำลังกายของเราประกอบด้วย Google ชีตแบบอ่านอย่างเดียว ในการทำแบบฝึกหัด คุณสามารถคัดลอก Google ชีต ( ไฟล์ → ทำสำเนา ) หรือคัดลอกเนื้อหาลงใน Google ชีตของคุณเอง

ในทุกแบบฝึกหัด เราจะแสดงการสืบค้น SQL ที่ใช้ไวยากรณ์ของ Oracle สำหรับวันที่ เราจะใช้รูปแบบ ISO 8601 คือ YYYY-MM-DD

แบบฝึกหัดที่ 1: การจองทั้งหมดของลูกค้า

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

 SELECT * FROM Reservations WHERE ClientID = 12;

แต่เราต้องการทำตามวิธีการเฉพาะ

วิธีที่ 1: ไม่มีการเรียงลำดับ ไม่มีการกรอง

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

รหัสเทียมนี้แสดงอัลกอริทึมสำหรับการทำงานให้สำเร็จโดยไม่ต้องเรียงลำดับ:

 For each row from Reservations If Reservations.ClientID = 12 then fetch Reservations.*

ในกรณีนี้เราต้องตรวจสอบทั้ง 841 แถวเพื่อส่งคืนและคัดลอก 73 แถวที่ตรงตามเงื่อนไข

วิธีที่ 2: การเรียงลำดับเท่านั้น

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

หลังจากการเรียงลำดับ วิธีการจะมีลักษณะดังนี้:

 For each row from Reservations If ClientID = 12 then fetch Reservations.* Else if ClientID > 12 exit

คราวนี้เราต้องตรวจสอบ “เท่านั้น” 780 แถว หากเราสามารถข้ามไปที่แถวแรกได้ จะใช้เวลาน้อยลงไปอีก

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

การใช้สิทธิที่ 2: จำนวนการจองที่เริ่มในวันที่กำหนด

ตอนนี้งานคือการนับจำนวนการเช็คอินในวันที่ 16 สิงหาคม 2020:

 SELECT COUNT (*) FROM Reservations WHERE DateFrom = TO_DATE('2020-08-16', 'YYYY-MM-DD')

ใช้สเปรดชีตจากแบบฝึกหัดที่ 1 วัดและเปรียบเทียบเวลาที่ใช้ทำงานให้เสร็จโดยมีการเรียงลำดับและไม่มีการจัดเรียง จำนวนที่ถูกต้องคือ 91

สำหรับแนวทางที่ไม่มีการเรียงลำดับ โดยพื้นฐานแล้วอัลกอริธึมจะเหมือนกับวิธีจากแบบฝึกหัดที่ 1

วิธีการคัดแยกก็คล้ายกับวิธีในแบบฝึกหัดที่แล้ว เราจะแยกลูปออกเป็นสองส่วน:

 -- Assumption: Table reservation is sorted by DateFrom -- Find the first reservation from the 16th of August 2020. Repeat Read next row Until DateFrom = '2020-08-16' -- Calculate the count While DateFrom = '2020-08-16' Increase the count Read the next row

แบบฝึกหัดที่ 3: การสอบสวนทางอาญา

สารวัตรตำรวจขอดูรายชื่อแขกที่มาถึงโรงแรมในวันที่ 13 และ 14 สิงหาคม 2020

 SELECT ClientID FROM Reservations WHERE DateFrom BETWEEN ( TO_DATE('2020-08-13', 'YYYY-MM-DD') AND TO_DATE('2020-08-14', 'YYYY-MM-DD') ) AND HotelID = 3;

วิธีที่ 1: จัดเรียงตามวันที่เท่านั้น

ผู้ตรวจสอบต้องการรายการอย่างรวดเร็ว เรารู้แล้วว่าเราควรจัดเรียงตาราง/สเปรดชีตตามวันที่มาถึงจะดีกว่า ถ้าเราเพิ่งเสร็จสิ้นการออกกำลังกาย 2 เราโชคดีที่โต๊ะถูกจัดเรียงแล้ว ดังนั้นเราจึงใช้แนวทางที่คล้ายกับจากแบบฝึกหัดที่ 2

โปรดลองและบันทึกเวลา จำนวนแถวที่คุณต้องอ่าน และจำนวนรายการในรายการ

 -- Assumption: Table reservation is sorted by DateFrom -- Find the first reservation from the 13th of August 2020. Repeat Read next row Until DateFrom >= '2020-08-13' -- Prepare the list While DateFrom < '2020-08-15' If HotelID = 3 then write down the ClientID Read the next row

ด้วยวิธีนี้ เราต้องอ่าน 511 แถวเพื่อรวบรวมรายชื่อแขก 46 คน หากเราสามารถเลื่อนลงได้อย่างแม่นยำ จริง ๆ แล้วเราไม่ต้องอ่าน 324 จากรอบการทำซ้ำเพื่อค้นหาการมาถึงครั้งแรกในวันที่ 13 สิงหาคม อย่างไรก็ตาม เรายังต้องอ่านมากกว่า 100 แถวเพื่อตรวจสอบว่าแขกมาถึงโรงแรมด้วย HotelID 3 หรือไม่

ผู้ตรวจสอบรอตลอดเวลาแต่ไม่มีความสุข: แทนที่จะส่งชื่อแขกและข้อมูลที่เกี่ยวข้องอื่น ๆ เราส่งเฉพาะรายการบัตรประจำตัวที่ไม่มีความหมาย

เราจะกลับไปที่แง่มุมนั้นในภายหลังในซีรีส์ เรามาหาวิธีเตรียมรายการกันก่อนดีกว่า

วิธีที่ 2: จัดเรียงตามโรงแรม ตามด้วยวันที่

ในการจัดเรียงแถวตาม HotelID จากนั้น DateFrom เราสามารถเลือกคอลัมน์ทั้งหมด จากนั้นใช้ตัวเลือกเมนู Google ชีต Data → Sort range

 -- Assumption: Sorted according to HotelID and DateFrom -- Find the first reservation for the HotelID = 3. Repeat Read next row Until HotelID >= 3 -- Find the first arrival at the hotel on 13th of August While HotelID = 3 and DateFrom < '2020-08-13' Read the next row -- Prepare the list While HotelID = 3 and DateFrom < '2020-08-15' Write down the ClientID Read the next row

เราต้องข้ามขาเข้า 338 คนแรกก่อนที่จะหาคนแรกไปที่โรงแรมของเรา หลังจากนั้น เราไปก่อนหน้า 103 ที่มาถึงเพื่อค้นหาคนแรกในวันที่ 13 สิงหาคม สุดท้าย เราได้คัดลอกค่า ClientID 46 ค่าที่ต่อเนื่องกัน มันช่วยเราว่าในขั้นตอนที่สาม เราสามารถคัดลอกบล็อกของ ID ที่ต่อเนื่องกัน น่าเสียดายที่เราไม่สามารถข้ามไปที่แถวแรกจากบล็อกนั้นได้

วิธีที่ 3: จัดเรียงตามโรงแรมเท่านั้น

ลองใช้แบบฝึกหัดเดียวกันนี้โดยใช้สเปรดชีตที่เรียงลำดับโดย HotelID เท่านั้น

อัลกอริทึมที่ใช้กับตารางที่เรียงลำดับโดย HotelID เท่านั้นมีประสิทธิภาพน้อยกว่าเมื่อเราจัดเรียงตาม HotelID และ DateFrom (ตามลำดับ):

 -- Assumption: Sorted according to HotelID -- Find the first reservation for the HotelID = 3. Repeat Read next row Until HotelID >= 3 -- Prepare the list While HotelID = 3 If DateFrom between '2020-08-13' and '2020-08-14' Write down the ClientID Read the next row

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

วิธีที่ 4: เรียงตามวันที่ ตามด้วยโรงแรม

มันสำคัญจริง ๆ ไหมว่าเราจะจัดเรียงตาม HotelID ก่อนแล้วค่อย DateFrom หรือกลับกัน? มาดูกันดีกว่า: ลองจัดเรียงตาม DateFrom ก่อน แล้วตามด้วย HotelID

 -- Assumption: Sorted according to DateFrom and HotelID -- Find the first arrival on 13th of August While DateFrom < '2020-08-13' Read the next row --Find the first arrival at the Hotel While HotelID < 3 and DateFrom < '2020-08-15' Read the next row Repeat If HotelID = 3 Write down the ClientID Read the next row Until DateFrom > '2020-08-14' or (DateFrom = '2020-08-14' and HotelID > 3)

เราพบแถวแรกพร้อมวันที่ที่เกี่ยวข้อง จากนั้นอ่านเพิ่มเติมจนกว่าเราจะพบตำแหน่งแรกที่มาถึงโรงแรม หลังจากนั้น สำหรับแถวหลายแถว เป็นไปตามเงื่อนไขทั้งสอง วันที่ถูกต้อง และโรงแรมที่ถูกต้อง อย่างไรก็ตาม หลังจากมาถึงโรงแรม 3 เราก็มาถึงโรงแรม 4, 5 และอื่นๆ ในวันเดียวกัน หลังจากพวกเขา เราต้องอ่านแถวสำหรับโรงแรม 1 และ 2 อีกครั้งในวันถัดไป จนกว่าเราจะสามารถอ่านการมาถึงโรงแรมที่เราสนใจได้อย่างต่อเนื่อง

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

แนวทางที่ 4 มีข้อมูลที่ตรงกันอย่างสมบูรณ์ในสองช่วงตึก แต่แนวทางที่ 2 เป็นแนวทางเดียวที่ข้อมูลเป้าหมายอยู่ในบล็อกเดียวติดต่อกัน

จากตัวเลขก็ชัดเจนว่าวิธีที่ 2 มีข้อดีมากที่สุดในกรณีนี้

อธิบายดัชนี SQL: บทสรุปและอะไรต่อไป

การทำแบบฝึกหัดเหล่านี้ควรทำให้ประเด็นต่อไปนี้ชัดเจน:

1. การอ่านจากตารางที่จัดเรียงอย่างเหมาะสมจะเร็วขึ้น
2. หากยังไม่ได้จัดเรียงตาราง การเรียงลำดับจะใช้เวลามากกว่าการอ่านจากตารางที่ไม่ได้จัดเรียง
3. การค้นหาวิธีข้ามไปยังแถวแรกที่ตรงกับเงื่อนไขการค้นหาภายในตารางที่จัดเรียงจะช่วยประหยัดการอ่านได้มาก
4. การจัดเรียงตารางล่วงหน้าจะช่วยได้มาก
5. การรักษาสำเนาที่เรียงลำดับของตารางสำหรับข้อความค้นหาที่ใช้บ่อยที่สุดจะเป็นประโยชน์

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