ตัวอย่างการใช้งานดัชนีกับฐานข้อมูล

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

อธิบายก่อน ว่า Index คือการจัดทำดัชนีให้กับฐานข้อมูล ซึ่งยังมีอีกหลายชื่อที่ทำงานคล้ายๆกัน (แต่มีรายละเอียดปลีกย่อยแตกต่างกัน) เช่น Primary, Uniqe, Spatial และ Fulltext ดัชนีเปรียบเสมือนการเก็บของ ถ้าเรารู้ว่าของเก็บไว้ที่ไหน เราก็จะหาของชิ้นนั้นได้เร็วขึ้น

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

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

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

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

เราสามารถกำหนดดัชนีได้มากกว่า 1 คอลัมน์พร้อมๆกัน เพื่อประโยชน์แตกต่างกัน ตัวอย่างรูปด้านล่างมีดัชนีอยู่ 2 รายการ

PRIMARY ใช้ 2 คอลัมน์คู่กัน คือ owner_id และ id เป็นดัชนี
อีกอันคือ no ใช้คอลัมน์ no เป็นดัชนีเพียงคอลัมน์เดียว

ก่อนอื่นอธิบายตารางด้านบนก่อนว่าคอลัมน์ owner_id จะเก็บข้อมูล id ของเจ้าของ และคอลัมน์ id เก็บข้อมูลรายการปกติ (เหมือนคอลัมน์ id ทั่วไป) ทำให้ในตารางนี้ owner_id จะไม่สามารถซ้ำกันได้เลยในขณะที่ id สามารถซ้ำได้หาก owner_id แตกต่างกัน ส่วนคอลัมน์ no เก็บรหัสของรายการ (VARCHAR) ซึ่งมีการใช้ประโยชน์จากดัชนีในการค้นหารายการอย่างรวดเร็ว (ข้อมูลใน no จะไม่มีรายการใดซ้ำกันเลย)
SELECT * FROM table_name WHERE owner_id=1 AND id=1
Query ด้านบนสามารถใช้ประโยชน์จากดัชนี PRIMARY ได้ (Query รายการที่ 1 ของ สมาชิก 1) ซึ่งจะคืนค่าผลลัพท์เพียงรายการเดียวเท่านั้น
SELECT * FROM table_name WHERE owner_id=1
Query ด้านบนสามารถใช้ประโยชน์จากดัชนี PRIMARY ได้เช่นกัน เนืองจาก owner_id จะมีได้แค่รายการเดียวเท่านั้น (โดยเป็นการ Query ข้อมูลทั้งหมดของสมาชิก 1)
SELECT * FROM table_name WHERE id=1
Query ด้านบนไม่สามารถใช้ประโยชน์จากดัชนีได้ ถึงแม้ว่า id จะเป็นดัชนี (Query ข้อมูล id เท่ากับ 1 ของสมาชิกทุกคน)
SELECT * FROM table_name WHERE no='text'
ในกรณีที่ดัชนีเป็นอักขระ (no) การเปรียบเทียบข้อมูลแบบ "ทั้งข้อมูล" เช่น = หรือ != > < สามารถใช้ประโยชน์จากดัชนีได้
SELECT * FROM table_name WHERE no LIKE 'text%'
ส่วนการใช้ LIKE จะสามารถใช้ดัชนีได้ในกรณีที่เป็นการค้นหาตั้งแต่ตัวอักษรตัวแรกเท่านั้น (ไม่มี % หรือ _ อยู่หน้าข้อความค้นหา)
SELECT * FROM table_name WHERE no LIKE '%text%'
ในกรณีที่เป็นการค้นหาส่วนหนึ่งส่วนใดของข้อความ (มี % หรือ _ ด้านหน้าหรือทั้งสองด้านของข้อความค้นหา) จะไม่สามารถใช้ประโยชน์จากดัชนีได้

ข้อสังเกต ในกรณีที่ดัชนีมีหลายคอลัมน์ เราสามารถใช้ประโยชน์จากดัชนีได้ในกรณีที่

Query ข้อมูลทุกคอลัมน์ของดัชนี (owner_id, id)
Query ข้อมูลคอลัมน์แรกของดัชนี (owner_id)

ในการ JOIN ก็เช่นเดียวกันหากตารางที่นำมา JOIN ต้องจับคู่กันมากกว่า 1 คอลัมน์แล้ว คอลัมน์ที่นำมา JOIN ก็ควรเป็นดัชนีด้วยเหมือนกัน

SELECT *

FROM table_name AS Q

INNER JOIN table_name2 AS R ON R.owner_id=Q.owner_id

WHERE Q.owner_id=1

เช่นเดียวกัน การ JOIN กับดัชนีคอลัมน์แรก สามารถใช้ประโยชน์จากดัชนีได้

SELECT *

FROM table_name AS Q

INNER JOIN table_name2 AS R ON R.owner_id=Q.owner_id AND R.id=Q.id AND R.category_id=Q.category_id

WHERE Q.owner_id=1

และการ JOIN กับดัชนีทุกคอลัมน์ก็สามารถใช้ประโยชน์จากดัชนีได้เช่นกัน

SELECT *

FROM table_name AS Q

INNER JOIN table_name2 AS R ON R.category_id=Q.category_id

WHERE Q.owner_id=1

ส่วน การ JOIN กับคอลัมน์อื่นๆของดัชนี (ที่ไม่ใช่คอลัมน์แรก) จะไม่สามารถใช้ประโยชน์จากดัชนีได้