มารู้จักกับ SQL Server Indexes และ Database Engine Tuning Advisor

มีอยู่สองเรื่องที่ผู้เขียนได้รับการร้องขอให้เขียนก็คือ Indexes และ Database Engine Tuning Advisor ซึ่งทั้งสองเรื่องนี้มีความเกี่ยวเนื่องกันอยู่

ซึ่งผู้เขียนมองว่าเป็นนิยามของ Heap (เฉพาะที่ใช้ใน Microsoft SQL Server อาจต่าง ๆ ไปจาก Heap Allocation ที่เคยเรียนกันใน Data Structure อยู่บ้าง) ที่ให้ความกระจ่างที่สุดแล้ว ก็คือ "โครงสร้างตาราง (Table Allocation) ที่ไม่มี Clustered Index" นั่นเอง

จะเห็นว่าแม้ไม่มีการสร้าง Indexes บนตารางดังกล่าว แต่ก็มีการระบุ Index Type ไว้เป็น Heap (Heap ไม่ใช่ Index แต่แสดงผลร่วมบนตารางที่เก็บ Metadata ของ Indexes ใน Microsoft SQL Server)

จะเห็นว่าภายใต้ Folder ชื่อว่า Indexes ไม่มี Indexes อะไรอยู่เลย แล้ว Heap มาจากอะไรกันแน่ ผู้เขียนเลยลองพิสูจน์ให้เห็นเพิ่มเติมด้วยตัวอย่าง

USE TestDB;
GO
CREATE TABLE TEST.TestTable_HEAP
(
       Column1 varchar(10) NOT NULL
,      Column2 varchar(10) NOT NULL
);
GO

จะเห็นว่าเรายังไม่ได้สั่งสร้าง Index แต่ระบบได้สร้าง Clustered Index บนตาราง TEST.TestTable_PKEY ให้เราโดยอัตโนมัติ เมื่อสร้าง Primary Key Constraint

จะเห็นว่าภายใต้ Folder ชื่อว่า Indexes มี Indexes ชื่อเดียวกันกับ Primary Key ปรากฏอยู่ ตัวอย่างที่ผู้เขียนทดสอบแสดงให้เห็นว่าโครงสร้างตารางมีการจัดสรรแบบ Heap ได้ แต่หากผู้พัฒนาฐานข้อมูลมีการสร้าง Primary Key Constraint บนตารางนั้น ระบบก็จะนำ Primary Key ไปสร้างเป็น Clustered Key ของ Clustered Index ทันที

เหตุที่เป็นเช่นนี้เพราะ Microsoft เกรงว่าคนจะลืมสร้าง Clustered Index กัน จึงถือวิสาสะสร้างให้เลยโดยอัตโนมัติ

ถ้าอยากใช้คอลัมน์อื่นที่ไม่ใช่คอลัมน์ที่เป็น Primary Key มาสร้างเป็น Clustered Key ล่ะ ดังนั้นเราจึงควรสร้างตารางโดยไม่มี Primary Key Constraint ขั้นตอนต่อมา คือ สร้าง Clustered Index และสุดท้ายค่อยสร้าง Primary Key Constraint

USE TestDB;
GO
CREATE TABLE TEST.TestTable_CKEY_PKEY
(
       Column1 varchar(10) NOT NULL
,      Column2 varchar(10) NOT NULL
);
GO
CREATE CLUSTERED INDEX CTIDX_NO2 ON TEST.TestTable_CKEY_PKEY(Column2);
GO
ALTER TABLE TEST.TestTable_CKEY_PKEY
ADD CONSTRAINT PK_Column1 PRIMARY KEY (Column1);
GO

ข้อความแสดง Error บอกว่า ไม่สามารถสร้าง Clustered Index บนตารางได้มากกว่าหนึ่งตัว ซึ่งต้อง Drop ของเก่าทิ้งไปก่อนถึงจะสร้างใหม่ได้

นี่เป็นเครื่องยืนยันว่า Clustered Index เป็นของสงวนมีได้เพียงอันเดียวต่อตารางเท่านั้น เหตุผลก็คือเรื่องนี้เป็นเรื่องของการจัดสรรข้อมูลในตาราง ซึ่งมีได้เพียง เป็น Heap หรือเป็น Clustered Index อย่างใดอย่างหนึ่งเท่านั้น

การจัดสรรแบบ Heap บน Microsoft SQL Server ที่บอกว่ามักไม่ค่อยมีประสิทธิภาพ ถ้าเทียบกับ Clustered Index ก็เพราะ Heap นั้นมีวิธีการเอาแถวข้อมูลใส่ลงใน Page ข้อมูลแบบ PFS (Page Free Space) ซึ่งเป็นวิธีง่ายๆ คือ เมื่อต้องการ Insert แถวข้อมูลก็จะหา Page ของตารางนั้น ๆ ที่ใกล้ที่สุดแล้วมีพื้นที่เพียงพอที่ใส่แถวข้อมูลก็จะบันทึกลง

แต่สำหรับการจัดสรรโครงสร้างตารางด้วย Clustered Index ข้อมูลจะมีการเรียงลำดับตาม Clustered Key ไว้ก่อน หมายถึง Pages ข้อมูลของตารางเดียวกันก็จะมีการเรียงลำดับ และแถวข้อมูลใน Page ก็จะมีการเรียงลำดับด้วย

จากตัวอย่าง ตาราง TEST.Customers และตาราง TEST.Orders นั้นมีการจัดสรรโครงสร้างแบบ Heap จึงต้องทำการ Scan ตารางทั้งสองตั้งแต่แถวข้อมูลแรกไปจนแถวสุดท้าย

จะเห็นว่าในฝั่งตาราง TEST.Customers การดำเนินการที่เกิดขึ้นคือ Clustered Index Seek พอเห็นคำว่า Seek แสดงว่ามีการ Search ตามกลไกของ Index แต่สำหรับตาราง TEST.Orders การดำเนินการ คือ Clustered Index Scan

จะเห็นว่าแม้เราจะมี Clustered Index อยู่แล้วก็ตาม แต่เงื่อนไขการ JOIN หรือ Predicate ในจุดอื่น ๆ ของการ Query ไม่ได้ใช้ส่วนของ Clustered Index เลย การดำเนินการก็จะเป็นเพียง Clustered Index Scan เราจึงจำเป็นต้องนำ Non-Clustered Index มาช่วย

สำหรับ Non-Clustered Index ไม่ได้ช่วยบนโครงสร้าง Clustered Index แต่กับ Heap ก็ช่วยได้มากเช่นกัน

Database Engine Tuning Advisor

เครื่องมือนี้จะช่วยในการวิเคราะห์และแนะนำ Indexes และ Statistics ที่เหมาะสมให้กับเรา (นอกเหนือจาก Index และ statistics แล้วยังมีเรื่องของวางแผนทางกายภาพต่าง ๆ อีกด้วย) ซึ่งคำแนะนำมักพบอยู่เสมอเมื่อนำไปวิเคราะห์

ใน Template นี้จะเก็บ Statement ต่าง ๆ เก็บการประมวลผล Stored Procedures นำทุกอย่างมาวิเคราะห์ ซึ่งหากเราได้กลุ่มตัวอย่างหลากหลายพอเช่นไม่ได้มีแต่ SELECT แต่ยังเก็บคำสั่งอื่น ๆ อาทิ Insert, Update, Delete รวมอยู่ด้วย ผลการวิเคราะห์ก็จะมีคุณภาพสูงขึ้น

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

บทสรุป: Index ใน SQL Server มีสองชนิดหลัก คือ Clustered Index ที่กำหนดลำดับการจัดเก็บข้อมูลจริงในตาราง มีได้เพียงอันเดียวต่อตาราง และ Non-Clustered Index ที่สร้างโครงสร้างแยก Database Engine Tuning Advisor เป็นเครื่องมือที่ช่วยวิเคราะห์และแนะนำ Index ที่เหมาะสมอัตโนมัติ