Kotchasan PHP Framework

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

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

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

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

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

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

เราสามารถกำหนดดัชนีได้มากกว่า 1 คอลัมน์พร้อมๆกัน เพื่อประโยชน์แตกต่างกัน ตัวอย่างรูปด้านล่างมีดัชนีอยู่ 2 รายการ
        
  1. PRIMARY ใช้ 2 คอลัมน์คู่กัน คือ owner_id และ id เป็นดัชนี
  2.     
  3. อีกอันคือ 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%'
ในกรณีที่เป็นการค้นหาส่วนหนึ่งส่วนใดของข้อความ (มี % หรือ _ ด้านหน้าหรือทั้งสองด้านของข้อความค้นหา) จะไม่สามารถใช้ประโยชน์จากดัชนีได้
ข้อสังเกต ในกรณีที่ดัชนีมีหลายคอลัมน์ เราสามารถใช้ประโยชน์จากดัชนีได้ในกรณีที่
        
  1. Query ข้อมูลทุกคอลัมน์ของดัชนี (owner_id, id)
  2.     
  3. 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 กับคอลัมน์อื่นๆของดัชนี (ที่ไม่ใช่คอลัมน์แรก) จะไม่สามารถใช้ประโยชน์จากดัชนีได้