เทคนิคในการสร้าง INDEX ให้กับ Database
1 ส.ค. 2018 , 6,587 Views , หมวดหมู่ MySQL ทุกหมวดหมู่ โค๊ดดิ้ง , ป้ายกำกับ:index, mysqlผมคิดว่าหลาย ๆ ท่าน คงมีคำถามในใจว่า ทำไมสร้าง index มาแล้ว ทำไมการดึงข้อมูล (query) ยังช้าอยู่เหมือนเดิม ไม่เห็นจะเร็วขึ้นเลย ทั้งๆ ที่ในตำราก็บอกว่าสร้าง index แล้วจะทำให้ดึงข้อมูลได้เร็วขึ้น พอผมได้เข้าไปดูเลยพบว่าคอลัมน์ที่ทำมาใช้เป็น index มันไม่เหมาะสมนี่เอง ข้อมูลหลักแสนหลักล้านในตาราง แต่ดันเอาคอลัมน์ที่มีค่าที่แตกต่างกันเพียง 7 ค่า (SELECT Distinct Column_Name) มาเป็นทำเป็น index ซะงั้น ซึ่งไม่ถูกต้องตามหลักการเลือกคอลัมน์มาเป็น Index นั่นเอง ทำให้การดึงข้อมูลก็จะยังช้าอยู่เหมือนเดิมครับ
ผมมีเทคนิคง่ายๆ ในการเลือกคอลัมน์เพื่อใช้สร้าง index มาฝากครับ โดยการใช้สูตรตามด้านล่างนี้ครับ
SELECT Distinct Column Name / Number of Rows
หมายถึงให้เรา SELECT Distinct คอลัมน์ที่เราต้องการ (ซึ่งควรจะเป็นค่าที่ไม่ซ้ำและไม่มีค่า Null ปนอยู่) แล้วดูว่า select ได้ทั้งหมดกี่แถว(row) แล้วให้นำไป หาร กับจำนวนแถวทั้งหมดที่อยู่ในตารางนั้นๆ ครับ แล้วดูผลลัพธ์ว่าได้ค่าเป็นเท่าไหร่ ถ้าได้ค่าใกล้ 1 เท่าไร ก็แสดงว่าคอลัมน์นั้นน่าจะนำไปสร้าง index ที่ดีได้ครับ
ตัวอย่างในการเลือกคอลัมน์เพื่อสร้าง index ที่ดี
ถ้าในตารางของเรามีข้อมูล 100,000 แถว และคอลัมน์ที่ต้องการจะนำมาทำเป็น index มีจำนวน 90,000 แถวที่มีค่าไม่ซ้ำกัน
จากสูตรจะได้ว่า 90,000/100,000 = 0.9
0.9 ถือว่าเป็นค่าที่ใกล้ 1 ดังนั้นคอลัมน์นี้เข้าข่ายที่จะนำมาทำ index ที่ดีได้ครับ
ตัวอย่างในการเลือกคอลัมน์เพื่อสร้าง index ที่ไม่ดี
ถ้าในตารางของเรามีข้อมูล 100,000 แถว และคอลัมน์ที่ต้องการจะนำมาทำเป็น index มีจำนวนเพียง 800 แถวที่มีค่าไม่ซ้ำกัน
จากสูตรจะได้ว่า 800/100,000 = 0.008
0.008 ถือว่าเป็นค่าที่ห่างไกลจาก 1 มาก ดังนั้นคอลัมน์นี้ไม่ควรจะนำมาทำ index ครับ ซึ่งกรณีนี้เราควรปล่อยให้การดึงข้อมูลเป็นแบบดึงข้อมูลหมดในตาราง
(Full Table) จะทำให้มีประสิทธิภาพมากกว่าการใช้คอลัมน์นี้มาทำเป็น index ครับ
ส่วนในกรณีที่เราต้องการใช้หลายๆ คอลัมน์ มาสร้างเป็น index ให้พิจารณาเลือกคอลัมน์ที่มีการเรียกใช้บ่อยๆ ในความสัมพันธ์หรือการเชื่อมโยงกันของแต่ละตาราง (join table) ใน SQL Statement ซึ่งส่วนใหญ่ก็มักจะเป็นคอลัมน์เพื่อสร้าง index ที่ดีตามสูตรที่ได้กล่าวไปแล้วครับ
ข้อควรจำ:การที่มี index มากๆ ก็ใช่ว่าจะดีเสมอไป แม้ว่า index จะช่วยให้การดึงข้อมูล (Query) การเปลี่ยนแปลงข้อมูล (Update) และการลบข้อมูล (Delete) ทำได้เร็วขึ้น แต่มันจะไปลดประสิทธิภาพของการเพิ่มข้อมูล (Insert) แทน และการที่มี index จำนวนมาก ก็จะทำให้เปลืองเนื้อที่ในฐานข้อมูลด้วย ดังนั้นเราจึงควรพิจารณาสร้าง index เท่าที่จำเป็นเท่านั้นครับ
ที่มา : www.aware.co.th/database_sql_เทคนคในการสราง_index_ใหไดผล/
