การสร้างดัชนีแบบคลัสเตอร์และแบบไม่คลัสเตอร์ใน SQL Server
ใน SQL Server มีดัชนีสองประเภท ดัชนีแบบคลัสเตอร์และไม่ใช่คลัสเตอร์ ทั้งดัชนีคลัสเตอร์และดัชนีที่ไม่ใช่คลัสเตอร์มีโครงสร้างทางกายภาพเหมือนกัน ยิ่งไปกว่านั้นทั้งสองจะถูกเก็บไว้ใน SQL Server เป็นโครงสร้าง B-Tree
ดัชนีคลัสเตอร์:
รายการคลัสเตอร์คือดัชนีชนิดหนึ่งที่จัดเรียงการจัดเก็บทางกายภาพของระเบียนในตารางใหม่ ภายใน SQL Server ดัชนีจะใช้เพื่อเร่งการดำเนินการฐานข้อมูลซึ่งนำไปสู่ประสิทธิภาพสูง ดังนั้นตารางจึงสามารถมีดัชนีคลัสเตอร์ได้เพียงดัชนีเดียวซึ่งโดยปกติจะทำกับคีย์หลัก โหนดลีฟของดัชนีคลัสเตอร์ประกอบด้วย “ หน้าข้อมูล” ตารางสามารถมีดัชนีคลัสเตอร์ได้เพียงดัชนีเดียว
ให้เราสร้างดัชนีคลัสเตอร์เพื่อให้มีความเข้าใจที่ดีขึ้น ก่อนอื่นเราต้องสร้างฐานข้อมูล
การสร้างฐานข้อมูล
เพื่อสร้างฐานข้อมูล คลิกขวาที่ “ ฐานข้อมูล” ใน object explorer และเลือก “ ฐานข้อมูลใหม่” ตัวเลือก พิมพ์ชื่อฐานข้อมูลแล้วคลิกตกลง สร้างฐานข้อมูลแล้วดังแสดงในรูปด้านล่าง
ตอนนี้เราจะสร้างตารางชื่อ “ พนักงาน” ด้วยคีย์หลักโดยใช้มุมมองออกแบบ เราสามารถเห็นได้จากภาพด้านล่างที่เราได้กำหนดให้กับไฟล์ชื่อ“ ID” เป็นหลักและเราไม่ได้สร้างดัชนีใด ๆ บนตาราง
คุณยังสามารถสร้างตารางโดยเรียกใช้รหัสต่อไปนี้
ใช้ [ทดสอบ] ไปตั้งค่า ANSI_NULLS บน GO SET QUOTED_IDENTIFIER บน GO สร้างตาราง [dbo] [พนักงาน] ([ID] [int] IDENTITY (1,1) ไม่เป็นโมฆะ, [Dep_ID] [int] NULL, [ชื่อ] [ varchar] (200) NULL, [email] [varchar] (250) NULL, [city] [varchar] (250) NULL, [address] [varchar] (500) NULL, CONSTRAINT [Primary_Key_ID] PRIMARY KEY CLUSTERED ([ID ] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) เปิด [PRIMARY]
ผลลัพธ์จะเป็นดังนี้
โค้ดด้านบนได้สร้างตารางชื่อ “ พนักงาน” ด้วยฟิลด์ ID ซึ่งเป็นตัวระบุที่ไม่ซ้ำกันเป็นคีย์หลัก ตอนนี้ในตารางนี้ดัชนีคลัสเตอร์จะถูกสร้างขึ้นโดยอัตโนมัติในรหัสคอลัมน์เนื่องจากข้อ จำกัด ของคีย์หลัก หากคุณต้องการดูดัชนีทั้งหมดบนตารางให้เรียกใช้กระบวนงานที่เก็บไว้ “ sp_helpindex” รันโค้ดต่อไปนี้เพื่อดูดัชนีทั้งหมดบนตารางที่มีชื่อว่า “ พนักงาน”. กระบวนงานร้านค้านี้ใช้ชื่อตารางเป็นพารามิเตอร์อินพุต
ใช้การทดสอบ EXECUTE sp_helpindex Employee
ผลลัพธ์จะเป็นดังนี้
อีกวิธีในการดูดัชนีตารางคือไปที่ “ ตาราง” ในตัวสำรวจวัตถุ เลือกตารางและใช้จ่าย ในโฟลเดอร์ดัชนีคุณสามารถดูดัชนีทั้งหมดที่เกี่ยวข้องกับตารางนั้น ๆ ดังแสดงในรูปด้านล่าง
เนื่องจากนี่คือดัชนีคลัสเตอร์ ดังนั้นลำดับตรรกะและทางกายภาพของดัชนีจะเหมือนกัน ซึ่งหมายความว่าหากระเบียนมี Id เป็น 3 ก็จะถูกเก็บไว้ในแถวที่สามของตาราง ในทำนองเดียวกันถ้าระเบียนที่ห้ามี id เป็น 6 ก็จะถูกเก็บไว้ใน 5ธ ตำแหน่งของโต๊ะ เพื่อให้เข้าใจลำดับของระเบียนคุณต้องเรียกใช้สคริปต์ต่อไปนี้
USE [test] GO SET IDENTITY_INSERT [dbo]. [Employee] ON INSERT [dbo]. [Employee] ([ID], [Dep_ID], [Name], [email], [city], [address]) VALUES ( 8, 6, N'Humbaerto Acevedo ', N'[email protected]', N'SAINT PAUL ', N'895 E 7th St Saint Paul Mn 551063852') INSERT [dbo]. [Employee] ([ID ], [Dep_ID], [ชื่อ], [อีเมล], [เมือง], [ที่อยู่]) ค่านิยม (9, 6, N'Humbaerto Acevedo ', N'[email protected]', N'SAINT PAUL ' , N'895 E 7th St Saint Paul Mn 551063852 ') INSERT [dbo]. [Employee] ([ID], [Dep_ID], [Name], [email], [city], [address]) VALUES (10, 7, N'Pilar Ackaerman', N'[email protected]', N'ATLANTA', N'5813 Eastern Ave Hyattsville Md 207822201') INSERT [dbo].[Employee] ([ID], [Dep_ID] , [ชื่อ], [อีเมล], [เมือง], [ที่อยู่]) ค่า (11, 1, N'Aaaronboy Gutierrez ', N'[email protected]', N'HILLSBORO ', N'5840 Ne Cornell Rd Hillsboro หรือ 97124 ') INSERT [dbo] [Employee] ([ID], [Dep_ID], [Name], [email], [city], [address]) VALUES (12, 2, N'Aabdi Maghsoudi' , N'[email protected]', N'BRENTWOOD ', N'987400 Nebraska Medical Center Omaha Ne 681987400') INSERT [dbo]. [Employee] ([ID], [Dep_ID], [Name], [email], [city], [address]) VALUES (13, 3, N'Aabharana, Sahni ', N'[email protected]', N'HYATTSVILLE ', N'2 Barlo Circle Suite A Dillsburg Pa 170191') INSERT [dbo]. [Employee] ([ ID], [Dep_ID], [Name], [email], [city], [address]) VALUES (14, 3, N'Aabharana, Sahni', N'[email protected]', N'HYATTSVILLE ', N'2 Barlo Circle Suite A Dillsburg Pa 170191') INSERT [dbo]. [Employee] ([ID], [Dep_ID], [Name], [email], [city], [address]) VALUES (1 , 1, N'Aaaronboy Gutierrez', N'[email protected]', N'HILLSBORO', N'5840 Ne Cornell Rd Hillsboro Or 97124') INSERT [dbo].[Employee] ([ID], [ Dep_ID], [ชื่อ], [อีเมล], [เมือง], [ที่อยู่]) ค่านิยม (2, 2, N'Aabdi Maghsoudi ', N'[email protected]', N'BRENTWOOD ', N'987400 Nebraska Medical Center Omaha Ne 681987400 ') INSERT [dbo]. [Employee] ([ID], [Dep_ID], [Name], [email], [city], [address]) VALUES (3, 3, N'Aabharana, Sahni ' , N'[email protected] ', N'HYATTSVILLE', N'2 Barlo Circle Suite A Dillsburg Pa 170191 ') INSERT [dbo] [Employee] ([ID], [Dep_ID], [Name], [email], [เมือง], [ที่อยู่]) VALUES (4, 3, N'Aabharana, Sahni ', N'[email protected]', N'HYATTSVILLE ', N'2 Barlo Circle Suite A Dillsburg Pa 170191 ') INSERT [dbo]. [Employee] ([ID], [Dep_ID], [Name], [email], [city], [address]) VALUES (5, 4, N'Aabish Mughal', N ' [email protected] ', N'OMAHA', N'2975 Crouse Lane Burlington Nc 272150000 ') INSERT [dbo] [Employee] ([ID], [Dep_ID], [Name], [email], [city] , [ที่อยู่]) ค่านิยม (6, 5, N'Aabram Howell', N'[email protected]', N'DILLSBURG', N'868 York Ave Atlanta Ga 303102750') INSERT [dbo].[พนักงาน ] ([ID], [Dep_ID], [ชื่อ], [อีเมล], [เมือง], [ที่อยู่]) ค่า (7, 5, N'Aabram Howell ', N'[email protected]', N 'DILLSBURG', N'868 York Ave Atlanta Ga 303102750 ') INSERT [dbo]. [Employee] ([ID], [Dep_ID], [Name], [email], [city], [address]) VALUES (15 , 4, N'Aabish Mughal ', N'abish_mughal @ gmail.com ', N'OMAHA', N'2975 Crouse Lane Burlington Nc 272150000 ') INSERT [dbo]. [Employee] ([ID], [Dep_ID], [Name], [email], [city], [ ที่อยู่]) ค่า (16, 5, N'Aabram Howell ', N'[email protected]', N'DILLSBURG ', N'868 York Ave Atlanta Ga 303102750') INSERT [dbo] [Employee] ( [ID], [Dep_ID], [ชื่อ], [อีเมล], [เมือง], [ที่อยู่]) ค่านิยม (17, 5, N'Aabram Howell ', N'[email protected]', N'DILLSBURG ', N'868 York Ave Atlanta Ga 303102750') INSERT [dbo].[Employee] ([ID], [Dep_ID], [Name], [email], [city], [address]) VALUES (18, 6 , N'Humbaerto Acevedo ', N'[email protected]', N'SAINT PAUL ', N'895 E 7th St Saint Paul Mn 551063852') INSERT [dbo]. [Employee] ([ID], [ Dep_ID], [ชื่อ], [อีเมล], [เมือง], [ที่อยู่]) ค่า (19, 6, N'Humbaerto Acevedo ', N'[email protected]', N'SAINT PAUL ', N' 895 E 7th St Saint Paul Mn 551063852 ') INSERT [dbo] [Employee] ([ID], [Dep_ID], [Name], [email], [city], [address]) VALUES (20, 7, N 'พิลาร์ อัคเคอร์แมน', N'[email protected]', N'ATLANTA ', N'5813 Eastern Ave Hyattsville Md 207822201') SET IDENTITY_INSERT [dbo]. [พนักงาน] ปิด
แม้ว่าระเบียนจะถูกเก็บไว้ในคอลัมน์ "Id" ตามลำดับค่าแบบสุ่ม แต่เนื่องจากดัชนีคลัสเตอร์ในคอลัมน์ id เร็กคอร์ดจะถูกจัดเก็บตามลำดับค่าจากน้อยไปหามากในคอลัมน์ id ในการตรวจสอบสิ่งนี้เราจำเป็นต้องรันโค้ดต่อไปนี้
เลือก * จาก test.dbo พนักงาน
ผลลัพธ์จะเป็นดังนี้
เราสามารถเห็นได้จากตัวเลขด้านบนว่ามีการดึงข้อมูลในลำดับจากน้อยไปมากของค่าในคอลัมน์ id
ดัชนีคลัสเตอร์ที่กำหนดเอง
คุณยังสามารถสร้างดัชนีคลัสเตอร์แบบกำหนดเองได้ เนื่องจากเราสร้างดัชนีคลัสเตอร์ได้เพียงดัชนีเดียว เราจึงต้องลบดัชนีก่อนหน้า ในการลบดัชนีให้รันโค้ดต่อไปนี้
ใช้ [ทดสอบ] ไปแก้ไขตาราง [dbo] [พนักงาน] วางข้อ จำกัด [Primary_Key_ID] ด้วย (ออนไลน์ = ปิด) ไป
ผลลัพธ์จะเป็นดังนี้
ตอนนี้เพื่อสร้างดัชนีดำเนินการรหัสต่อไปนี้ในหน้าต่างแบบสอบถาม ดัชนีนี้ถูกสร้างขึ้นในคอลัมน์มากกว่าหนึ่งคอลัมน์จึงเรียกว่าดัชนีคอมโพสิต
ใช้ [ทดสอบ] ไปสร้างดัชนีที่คลัสเตอร์ [ClusteredIndex-20191128-173307] บน [dbo]. [พนักงาน] ([ID] ASC, [Dep_ID] ASC) กับ (PAD_INDEX = ปิด, STATISTICS_NORECOMPUTE = ปิด, SORT_IN_TEMPDB = ปิด, DROP_IN_TEMPDB = ปิด, DROP_IN_TEMPDB = ปิด ปิด, ออนไลน์ = ปิด, ALLOW_ROW_LOCKS = เปิด, ALLOW_PAGE_LOCKS = เปิด) เปิด [หลัก] ไป
ผลลัพธ์จะเป็นดังนี้
เราได้สร้างดัชนีคลัสเตอร์ที่กำหนดเองบน ID และ Dep_ID สิ่งนี้จะจัดเรียงแถวตาม Id แล้วตามด้วย Dep_Id เพื่อดูสิ่งนี้ให้รันโค้ดต่อไปนี้ ผลลัพธ์จะเรียงลำดับจากน้อยไปมากของ ID จากนั้นตามด้วย Dep_id
SELECT [ID], [Dep_ID], [Name], [email], [city], [address] FROM [test]. [dbo]. [Employee]
ผลลัพธ์จะเป็นดังนี้
ดัชนีที่ไม่ใช่คลัสเตอร์:
ดัชนีที่ไม่จัดกลุ่มเป็นประเภทดัชนีเฉพาะซึ่งลำดับเชิงตรรกะของดัชนีไม่ตรงกับลำดับทางกายภาพของแถวที่จัดเก็บไว้ในดิสก์ โหนดลีฟของดัชนีที่ไม่ใช่คลัสเตอร์ไม่มีเพจข้อมูล แต่มีข้อมูลเกี่ยวกับแถวดัชนี ตารางสามารถมีดัชนีได้ถึง 249 รายการ โดยค่าเริ่มต้นข้อ จำกัด คีย์เฉพาะจะสร้างดัชนีที่ไม่ใช่คลัสเตอร์ ในการดำเนินการอ่านดัชนีที่ไม่ใช่คลัสเตอร์จะช้ากว่าดัชนีแบบคลัสเตอร์ ดัชนีที่ไม่ใช่คลัสเตอร์มีสำเนาของข้อมูลจากคอลัมน์ที่จัดทำดัชนีไว้ตามลำดับพร้อมกับการอ้างอิงไปยังแถวข้อมูลจริง ชี้ไปที่รายการคลัสเตอร์ถ้ามี ดังนั้นจึงเป็นความคิดที่ดีที่จะเลือกเฉพาะคอลัมน์ที่ใช้ในดัชนีแทนที่จะใช้ * วิธีนี้สามารถดึงข้อมูลได้โดยตรงจากดัชนีที่ซ้ำกัน นอกจากนี้ยังใช้ดัชนีคลัสเตอร์เพื่อเลือกคอลัมน์ที่เหลือหากมีการสร้าง
ไวยากรณ์ที่ใช้ในการสร้างดัชนีที่ไม่ใช่คลัสเตอร์จะคล้ายกับดัชนีคลัสเตอร์ อย่างไรก็ตามคำหลัก "ไม่รวม" ใช้แทน “ คลัสเตอร์” ในกรณีของดัชนีที่ไม่ใช่คลัสเตอร์ เรียกใช้สคริปต์ต่อไปนี้เพื่อสร้างดัชนีที่ไม่ใช่คลัสเตอร์
ใช้ [ทดสอบ] ไปตั้งค่า ANSI_PADDING ต่อไปสร้างดัชนีที่ไม่รวมกลุ่ม [NonClusteredIndex-20191129-104230] บน [dbo] [พนักงาน] ([ชื่อ] ASC) กับ (PAD_INDEX = ปิด, STATISTICS_NORECOMPUTE = ปิด, SORT_IN_TEMPDB = ปิด , ออนไลน์ = ปิด, ALLOW_ROW_LOCKS = เปิด, ALLOW_PAGE_LOCKS = เปิด) บน [หลัก] ไป
ผลลัพธ์จะเป็นดังนี้
ระเบียนตารางจะเรียงลำดับตามดัชนีคลัสเตอร์หากมีการสร้างขึ้น ดัชนีที่ไม่ใช่คลัสเตอร์ใหม่นี้จะจัดเรียงตารางตามคำจำกัดความและจะถูกเก็บไว้ในที่อยู่ทางกายภาพแยกต่างหาก สคริปต์ด้านบนจะสร้างดัชนีในคอลัมน์“ NAME” ของตารางพนักงาน ดัชนีนี้จะจัดเรียงตารางจากน้อยไปหามากของคอลัมน์“ ชื่อ” ข้อมูลตารางและดัชนีจะถูกเก็บไว้ในตำแหน่งที่แตกต่างกันดังที่เราได้กล่าวไว้ก่อนหน้านี้ ตอนนี้เรียกใช้สคริปต์ต่อไปนี้เพื่อดูผลกระทบของดัชนีใหม่ที่ไม่ใช่คลัสเตอร์
เลือกชื่อจากพนักงาน
ผลลัพธ์จะเป็นดังนี้
เราจะเห็นในรูปด้านบนว่าคอลัมน์ Name ของตาราง Employee แสดงตามลำดับคอลัมน์ชื่อจากน้อยไปหามากแม้ว่าเราจะไม่ได้กล่าวถึงอนุประโยค“ Order by ASC” พร้อมกับคำสั่ง select นี่เป็นเพราะดัชนีที่ไม่ใช่คลัสเตอร์ในคอลัมน์ "ชื่อ" ที่สร้างขึ้นในตารางพนักงาน ตอนนี้ถ้ามีการเขียนแบบสอบถามเพื่อดึงชื่ออีเมลเมืองและที่อยู่ของบุคคลที่ระบุ ฐานข้อมูลจะค้นหาชื่อเฉพาะนั้นภายในดัชนีก่อน จากนั้นจึงดึงข้อมูลที่เกี่ยวข้องซึ่งจะช่วยลดเวลาในการดึงข้อความค้นหา โดยเฉพาะอย่างยิ่งเมื่อข้อมูลมีขนาดใหญ่
เลือกชื่ออีเมลเมืองที่อยู่จากพนักงานโดยที่ชื่อ = 'Aaaronboy Gutierrez'
สรุป
จากการสนทนาข้างต้น เราได้ทราบว่าดัชนีคลัสเตอร์สามารถมีได้เพียงรายการเดียว ในขณะที่ดัชนีที่ไม่ใช่คลัสเตอร์สามารถมีได้หลายรายการ ดัชนีคลัสเตอร์เร็วกว่าเมื่อเทียบกับดัชนีที่ไม่ใช่คลัสเตอร์ ดัชนีคลัสเตอร์ไม่ใช้พื้นที่จัดเก็บเพิ่มเติมในขณะที่ดัชนีที่ไม่ใช่คลัสเตอร์ต้องการหน่วยความจำเพิ่มเติมเพื่อจัดเก็บ หากเราใช้ข้อ จำกัด ของคีย์หลักกับดัชนีคลัสเตอร์ของตารางจะถูกสร้างขึ้นโดยอัตโนมัติ ยิ่งไปกว่านั้นหากเราใช้ข้อ จำกัด ของคีย์ที่ไม่ซ้ำกับคอลัมน์ใด ๆ ดัชนีที่ไม่ใช่คลัสเตอร์จะถูกสร้างขึ้นโดยอัตโนมัติ ดัชนีที่ไม่ใช่คลัสเตอร์เร็วกว่าเมื่อเทียบกับดัชนีแบบคลัสเตอร์สำหรับการแทรกและการอัปเดต ตารางต้องไม่มีดัชนีที่ไม่ใช่คลัสเตอร์
