เคยสงสัยไหม ทำไม Table ที่มี Index เยอะมากๆ เสียพื้นที่เก็บ Index เพิ่มขึ้นเยอะแยะมากมายแล้ว แต่ก็ยังช้าอยู่ ตรวจสอบ Execution Plan แล้วก็ไม่เห็นเอา Index ที่สร้างไว้มาใช้งาน

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

Clustered Index v.s. Non Clustered Index

เริ่มจากการทำความเข้าใจระหว่าง Clustered Index กับ Non Clustered Index ต่างกันอย่างไรก่อน

Clustered Index

Clustered Index หรือจะเรียกเป็น Index หลักของแต่ละ Table โดยมีได้เพียง 1 Cluster Index ต่อ 1 Table เท่านั้น โดยจะมีการจัดเรียงข้อมูลตาม Clutered Index ที่ระบุไว้ เพื่อให้เข้าถึงข้อมูลได้เร็วมากๆ และนำข้อมูลที่เจอไปใช้ได้ทันที เนื่องจากข้อมูลอยู่ในที่เดียวกันอยู่แล้ว

โดยทั่วไปแล้ว SQL Server จะสร้าง Clustered Index ให้อัตโนมัติจากการสร้าง Primary Key แต่ทั้งนี้เราสามารถบอกให้ SQL Server ยังไม่ต้องสร้าง Cluster Index ได้ แล้วเราเป็นคนเลือกเองว่าจะใช้ Column ไหนที่ใช้ในการค้นหาบ่อยๆ มาเป็น Cluster Index แทนได้

ตัวอย่างการสร้าง Table แบบไม่ใช้ Primary Key เป็น Clustered Index

CREATE TABLE table1 (c1 INT PRIMARY KEY NONCLUSTERED, c2 varchar(20))

ตัวอย่างการสร้าง Clustered Index

CREATE CLUSTERED INDEX inx_table1_c2 ON table1(c2)

Non Clustered Index

Non Clustered Index หรือจะเรียกสั้นๆ ว่า Index ที่เราคุ้นเคยกันนั่นเอง (ซึ่งต่อไปจะขอเรียกสั้นๆ ว่า Index) โดยใน 1 Table จะมี Index ได้หลายตัว และจะเป็นเนื้อหาหลักของเราในวันนี้ (สำนวนนี้มาจากใครเอ่ย?)

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

Table ตัวอย่างสำหรับอ้างอิงเรื่องการสร้าง Index

CREATE TABLE tPerson (
    id INT PRIMARY KEY, 
    code NVARCHAR(20),
    title NVARCHAR(50), 
    firstName NVARCHAR(50), 
    lastName NVARCHAR(50),
    birthDate DATETIME
)

ตัวอย่างคำสั่งในการสร้าง Index column code สำหรับ Table tPerson

CREATE INDEX inx_tPerson_code 
ON tPerson (code)

Query แบบไหนที่จะเอา Index ไปใช้ในการหาข้อมูล

เงื่อนไขที่ SQL Server จะนำ Index ไปใช้ จะต้องเป็นเงื่อนไขเท่ากับ (=) ถ้าเป็นเงื่อนไขอื่นจะใช้ประโยชน์จาก Index ได้น้อย หรือช้ากว่าแบบไม่ใช้ Index เสียอีก เช่น มากกว่า (>), น้อยกว่า (<), BETWEEN, LIKE เนื่องจากจะต้องค้นหาจาก Index แล้วไปอ่านข้อมูลที่ Index แต่ละตัวชี้ทีละตัว ซึ่งอาจจะเสียเวลามากกว่า ดังนั้นถ้า Query ที่คุณใช้ไม่ใช่ = การสร้าง Index แบบนี้จึงไม่ได้ช่วยเรื่องความเร็ว เพราะมีไว้แต่ SQL Server ก็ไม่ได้นำมาใช้ หรือถ้าบังคับให้ใช้ก็ไม่ได้เร็วขึ้นแถมยังช้ากว่าเดิมเสียอีก

ตัวอย่าง Query ที่ได้ประโยชน์จาก Index นี้

SELECT code
FROM tPerson
WHERE code = '001'

Index แบบหลาย Column

ถ้า Index ที่สร้างมีมากกว่า 1 Column ได้ โดย SQL Server ยังคงใช้ column แรกใน Index ในการจัดเรียงข้อมูล และ เรียงตาม column ถัดๆ ไป เพื่อช่วยให้ดึงข้อมูลที่มีการจัดเรียงลำดับให้โดยไม่ต้องใช้ ORDER BY

CREATE INDEX inx_tPerson_code_firstName_LastName
ON tPerson (code, firstName, LastName)

ใน SQL Server มีการเพิ่ม Include เพื่อช่วยให้ index ดูง่ายขึ้น และช่วยลดเวลาในการจัดเรียงข้อมูลตอนเพิ่ม/แก้ไข/ลบ ดังนี้

CREATE INDEX inx_tPerson_code
ON tPerson (code)
INCLUDE (firstName, lastName)

ตัวอย่าง Code ที่ได้ประโยชน์จาก Index นี้ เนื่องจากมีข้อมูล firstName และ lastName อยู่ใน Index

SELECT code, firstName, lastName
FROM tPerson
WHERE code = '001'

ตัวอย่าง code ที่ได้ประโยชน์จาก Index ระดับหนึ่ง แต่ยังต้องเสียเวลาเข้าถึงข้อมูลอื่นๆ จาก Table เพิ่มเติม

SELECT code, title, firstName, lastName
FROM tPerson
WHERE code = '001'
-- หรือ
SELECT *
FROM tPerson
WHERE code = '001'

ดังนั้น หลักการสร้าง Index ควรเลือก Column แรกที่เป็นข้อมูลที่จำเป็นต้องใช้ในการค้นหามากที่สุด และใช้กับเงื่อนไขเท่ากับ (=) จึงมีการนำ Index ไปใช้ และถ้าเป็นข้อมูลที่ต้องใช้คู่กันบ่อยให้ใส่ไว้ที่ Include และไม่ควรใช้ * ใน Query เพราะจะต้องมีการดึง Column จากทุก Column ซึ่งถ้าไม่มี Column นั้นไว้ใน Index จะต้องวิ่งไปหาที่ Table อีกรอบ

Index แบบเน้นเร็ว ไม่แคร์เรื่องพื้นที่เก็บข้อมูล

กรณีมีเหตุจำเป็นต้องทำ Index ให้อ่านข้อมูลได้เร็วที่สุด แบบใช้พื้นที่เท่าไหร่ก็ยอม (แบบเปลืองเวอร์ๆ) แต่จะช่วยให้สามารถค้นหาโดยใช้เงื่อนไขแบบช่วง เช่น มากกว่า, น้อยกว่า, BETWEEN, LIKE ได้

CREATE INDEX inx_tPerson_code
ON tPerson (code)
INCLUDE (title, firstName, lastName, birthDate)

CREATE INDEX inx_tPerson_title
ON tPerson (title)
INCLUDE (code, firstName, lastName, birthDate)

CREATE INDEX inx_tPerson_firstName
ON tPerson (firstName)
INCLUDE (code, title, lastName, birthDate)

CREATE INDEX inx_tPerson_lastName
ON tPerson (lastName)
INCLUDE (code, title, firstName, birthDate)

CREATE INDEX inx_tPerson_birthDate
ON tPerson (birthDate)
INCLUDE (code, title, firstName, lastName)

Index ที่ซ้ำซ้อน เลือกแค่อันเดียวก็พอ

ตัวอย่างด้านล่าง เป็น Index ที่เทียบเท่ากัน หรือ แทบไม่ได้ประโยชน์จากการมีเยอะๆ เก็บไว้แค่ตัวเดียวก็พอแล้ว เนื่องจาก column แรกของ index เป็น code เหมือนกัน

CREATE INDEX inx_tPerson_code
ON tPerson (code)
INCLUDE (title, firstName, lastName, birthDate)

CREATE INDEX inx_tPerson_code_title
ON tPerson (code, title)
INCLUDE (firstName, lastName, birthDate)

CREATE INDEX inx_tPerson_code_firstName
ON tPerson (code, firstName)
INCLUDE (title, lastName, birthDate)

CREATE INDEX inx_tPerson_code_firstName_lastName
ON tPerson (code, firstName, lastName)
INCLUDE (title, birthDate)

CREATE INDEX inx_tPerson_code_title_firstName_lastName
ON tPerson (code, title, firstName, lastName)
INCLUDE (birthDate)

CREATE INDEX inx_tPerson_code_birthDate
ON tPerson (code, birthDate)
INCLUDE (title, firstName, lastName)

CREATE INDEX inx_tPerson_code_title_firstName_lastName_birthDate
ON tPerson (code, title, firstName, lastName, birthDate)

ทำไมทำ Index ไว้แล้วไม่ช่วยให้เร็วขึ้น