จะตรวจจับและวัดการกระจายตัวของดัชนีใน SQL Server ได้อย่างไร

วันนี้เราจะมาสำรวจแง่มุมที่น่าสนใจของ SQL Server ( wiki ) ซึ่งเป็นระบบที่ใช้จัดการฐานข้อมูล หัวข้อของเราในวันนี้คือ “การแบ่งส่วนดัชนีใน SQL Server” เราจะเรียนรู้วิธีตรวจจับและวัดค่า ไม่ต้องกังวล มันไม่ซับซ้อนอย่างที่คิด!

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

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

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

ทำความเข้าใจเกี่ยวกับการกระจายตัวของดัชนี

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

ตอนนี้ เมื่อเราเพิ่มเพลงใหม่ (หรือข้อมูล) ลบบางเพลง หรือย้ายไปรอบๆ เพลย์ลิสต์ (หรือดัชนี) ของเราอาจถูกสับหรือแยกส่วนได้ ในแง่ของฐานข้อมูล เราเรียกสิ่งนี้ว่า 'การกระจายตัวของดัชนี'

การแบ่งส่วนมีสองประเภท: ภายในและภายนอก

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

ตอนนี้ทำไมเราต้องสนใจเกี่ยวกับการกระจายตัวของดัชนี เมื่อดัชนีได้รับการแยกส่วน SQL Server จะต้องทำงานหนักขึ้นเพื่อค้นหาข้อมูลที่ต้องการ มันเหมือนกับการพยายามฟังเพลย์ลิสต์ที่สับตามลำดับเฉพาะ – มันต้องใช้ความพยายามมากกว่าใช่ไหม? ในทำนองเดียวกัน ดัชนีที่แยกส่วนอาจทำให้ประสิทธิภาพของฐานข้อมูลช้าลง ทำให้การดึงข้อมูลช้าลงและมีประสิทธิภาพน้อยลง

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

แนะนำสำหรับคุณ: SQL Injection: ยังเป็นภัยคุกคามอยู่หรือไม่ คุณจะหลีกเลี่ยงได้อย่างไร

การตรวจจับการกระจายตัวของดัชนี

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

เครื่องมือหลักที่เราใช้ใน SQL Server คือฟังก์ชันระบบที่เรียกว่า sys.dm_db_index_physical_stats เต็มปากเต็มคำเลยใช่ไหม? แต่ไม่ต้องกังวล มันไม่ซับซ้อนอย่างที่คิด ฟังก์ชันนี้เป็นเหมือนนักสืบที่สามารถตรวจสอบฐานข้อมูลของเราและบอกเราว่าดัชนีของเราแยกส่วนกันอย่างไร นี่คือวิธีที่เราใช้:

1. การเลือกฐานข้อมูลและตาราง:

ขั้นแรก เราบอกฟังก์ชันว่าเราต้องการตรวจสอบฐานข้อมูลและตารางใด เหมือนกับการเลือกเพลย์ลิสต์ที่เราต้องการตรวจสอบ

2. การเรียกใช้ฟังก์ชัน:

จากนั้นเราเรียกใช้ฟังก์ชัน สิ่งนี้ทำได้โดยการรันคำสั่ง SQL ที่มีลักษณะดังนี้:

 SELECT * FROM sys.dm_db_index_physical_stats (DB_ID(N'YourDatabaseName'), OBJECT_ID(N'YourTableName'), NULL, NULL, 'DETAILED');

ในคำสั่งนี้ ให้แทนที่ 'YourDatabaseName' และ 'YourTableName' ด้วยชื่อฐานข้อมูลและตารางของคุณ

3. การอ่านผลลัพธ์:

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

การวัดการกระจายตัวของดัชนี

เช่นเดียวกับการวัดความสูงหรือน้ำหนัก เรายังสามารถวัดดัชนีของเราที่แยกส่วนได้ ใน SQL Server เราใช้เมตริกหลักสองสามรายการในการดำเนินการนี้ คิดว่ามันเหมือนกับการวัดว่าเพลย์ลิสต์ของเราเสียไปเท่าไหร่ นี่คือวิธีที่เราทำ:

ทำความเข้าใจเมตริก:

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

เมตริกที่สำคัญอีกอย่างหนึ่งคือ page_count สิ่งนี้บอกเราถึงจำนวนดัชนีหรือหน้าข้อมูลทั้งหมดในดัชนี คิดว่าเป็นจำนวนเพลงทั้งหมดในเพลย์ลิสต์ของเรา

เรียกใช้คำสั่ง:

เราวัดการกระจายตัวของดัชนีโดยการเรียกใช้ฟังก์ชัน sys.dm_db_index_physical_stats เช่นเดียวกับที่เราทำเพื่อตรวจจับการกระจายตัว แต่คราวนี้ เราให้ความสนใจกับค่า avg_fragmentation_in_percent และ page_count

นี่คือคำสั่งอีกครั้ง:

 SELECT * FROM sys.dm_db_index_physical_stats (DB_ID(N'YourDatabaseName'), OBJECT_ID(N'YourTableName'), NULL, NULL, 'DETAILED');

อย่าลืมแทนที่ 'YourDatabaseName' และ 'YourTableName' ด้วยชื่อฐานข้อมูลและตารางของคุณ ต่อไปนี้คือตัวอย่างสิ่งที่คุณอาจเห็น โดยมีเพียงไม่กี่คอลัมน์เพื่อความง่าย:

ในตารางอย่างง่ายนี้:

• object_id คือ ID ของตาราง
• index_id คือรหัสของดัชนี
• index_type_desc อธิบายประเภทของดัชนี (เช่น “CLUSTERED INDEX”)
• avg_fragmentation_in_percent คือการกระจายตัวของดัชนีโดยเฉลี่ยในหน่วยเปอร์เซ็นต์
• fragment_count คือจำนวนของแฟรกเมนต์ (กลุ่มของเพจที่อยู่ติดกัน) ในดัชนี
• page_count คือจำนวนหน้าทั้งหมดในดัชนี

ตารางนี้ให้มุมมองที่ชัดเจนเกี่ยวกับสถานะการกระจายตัวของดัชนี

การตีความผลลัพธ์:

หาก avg_fragmentation_in_percent น้อยกว่า 5% แสดงว่าดัชนีของเราอยู่ในเกณฑ์ที่ค่อนข้างดี – เหมือนเพลย์ลิสต์ที่มีการสับเปลี่ยนเพียงเล็กน้อย หากอยู่ระหว่าง 5% ถึง 30% ดัชนีของเราอาจใช้การจัดระเบียบใหม่ และหากเกิน 30% เราอาจต้องสร้างดัชนีใหม่ทั้งหมด เช่น จัดลำดับเพลย์ลิสต์ใหม่ตั้งแต่ต้น

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

การตีความผลลัพธ์

โปรดจำไว้ว่าเรากำลังดูตารางที่บอกเราเกี่ยวกับสถานะของดัชนีของเรา เช่น รายงานการตรวจสุขภาพสำหรับฐานข้อมูลของเรา

1. ทำความเข้าใจเกี่ยวกับระดับการกระจายตัว

คอลัมน์ avg_fragmentation_in_percent เปรียบเสมือนหัวใจของดัชนีของเรา มันบอกเราว่าดัชนีของเรามีการแยกส่วนหรือไม่เป็นระเบียบอย่างไร ตัวเลขที่ต่ำ เช่น 0 หรือ 1 เปอร์เซ็นต์ หมายความว่าดัชนีของเราอยู่ในเกณฑ์ที่ดี – มีการจัดระเบียบเหมือนกับห้องสมุดที่ได้รับการดูแลเป็นอย่างดี แต่ตัวเลขที่สูง เช่น 60 หรือ 70 เปอร์เซ็นต์ หมายความว่าดัชนีของเราค่อนข้างแยกส่วน มันเหมือนห้องรกมากกว่าห้องสมุดที่เป็นระเบียบเรียบร้อย

2. จำนวนส่วนและจำนวนหน้า

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

3. เมื่อใดควรดำเนินการ

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

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

คุณอาจชอบ: วิธีใช้ GeoGraphy DataType ของ SQL ในการพัฒนา Asp.Net MVC

บทสรุป

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

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

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

ท้ายที่สุดแล้ว การรักษาดัชนีของเราเป็นส่วนสำคัญในการรักษาฐานข้อมูลของเรา การตรวจสอบและแก้ไขการกระจายตัวของดัชนีอย่างสม่ำเสมอทำให้เรามั่นใจได้ว่าฐานข้อมูลของเรายังคงทำงานได้ดีที่สุด

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

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