การสร้าง Index (CREATE INDEX)

ในบทที่แล้ว เราได้เรียนรู้เกี่ยวกับ Index และความสำคัญของมันในการเพิ่มประสิทธิภาพการค้นหาข้อมูลในฐานข้อมูล MySQL บทนี้จะเจาะลึกถึงวิธีการสร้าง Index ด้วยคำสั่ง CREATE INDEX ใน SQL พร้อมทั้งอธิบายหลักการเลือก Column ที่เหมาะสมสำหรับการสร้าง Index และข้อควรระวังต่างๆ

รูปแบบคำสั่ง CREATE INDEX

คำสั่ง CREATE INDEX ใช้เพื่อสร้าง Index บน Column หนึ่งหรือหลาย Column ในตาราง MySQL รูปแบบพื้นฐานของคำสั่งคือ:

CREATE INDEX index_name
ON table_name (column_name);

index_name: ชื่อของ Index ที่เราต้องการสร้าง ควรตั้งชื่อให้สื่อความหมายและเกี่ยวข้องกับ Column ที่ Index ครอบคลุม
table_name: ชื่อของตารางที่ต้องการสร้าง Index
column_name: ชื่อของ Column ที่ต้องการสร้าง Index สามารถระบุหลาย Column ได้โดยคั่นด้วยเครื่องหมาย comma (,)

ตัวอย่าง: สมมติว่าเรามีตาราง customers ที่มี Column last_name และต้องการสร้าง Index เพื่อให้การค้นหาลูกค้าตามนามสกุลทำได้รวดเร็วขึ้น เราสามารถใช้คำสั่ง SQL ดังนี้:

CREATE INDEX idx_last_name
ON customers (last_name);

คำสั่งนี้จะสร้าง Index ชื่อ idx_last_name บน Column last_name ในตาราง customers

การสร้าง Index บนหลาย Columns (Composite Index)

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

CREATE INDEX idx_last_name_first_name
ON customers (last_name, first_name);

คำสั่งนี้จะสร้าง Index ชื่อ idx_last_name_first_name บน Column last_name และ first_name ในตาราง customers

ข้อควรจำ: ลำดับของ Columns ใน Composite Index มีความสำคัญ หากเราค้นหาข้อมูลโดยใช้ Column last_name เพียงอย่างเดียว Index นี้จะถูกใช้งาน แต่หากเราค้นหาข้อมูลโดยใช้ Column first_name เพียงอย่างเดียว Index นี้อาจจะไม่ถูกใช้งาน (ขึ้นอยู่กับ optimizer ของ MySQL)

การเลือก Column ที่เหมาะสมสำหรับการสร้าง Index

การเลือก Column ที่เหมาะสมสำหรับการสร้าง Index เป็นสิ่งสำคัญเพื่อให้ได้ประสิทธิภาพที่ดีที่สุด ไม่ใช่ทุก Column ที่เหมาะสำหรับการสร้าง Index การสร้าง Index โดยไม่จำเป็นอาจทำให้เสียพื้นที่จัดเก็บข้อมูลและทำให้การเขียนข้อมูลช้าลง

หลักการพิจารณา Column ที่เหมาะสม:

Column ที่ใช้บ่อยใน WHERE Clause: Column ที่ถูกใช้บ่อยในเงื่อนไข WHERE เพื่อกรองข้อมูลเป็น Candidate ที่ดีสำหรับการสร้าง Index
Column ที่มี Cardinality สูง: Cardinality หมายถึงจำนวนค่าที่ไม่ซ้ำกันใน Column หาก Column มีค่าที่ไม่ซ้ำกันจำนวนมาก (Cardinality สูง) Index จะมีประสิทธิภาพมากกว่า Column ที่มีค่าซ้ำกันจำนวนมาก (Cardinality ต่ำ) ตัวอย่างเช่น Column email มักจะมี Cardinality สูง ในขณะที่ Column gender มักจะมี Cardinality ต่ำ
Column ที่ใช้ในการ JOIN: Column ที่ใช้ในการ JOIN ตาราง (เช่น Foreign Key) เป็น Candidate ที่ดีสำหรับการสร้าง Index เพราะจะช่วยให้การ JOIN ทำงานได้เร็วขึ้น
Column ที่ใช้ในการ ORDER BY หรือ GROUP BY: การสร้าง Index บน Column ที่ใช้ในการ ORDER BY หรือ GROUP BY สามารถช่วยให้การเรียงลำดับและการจัดกลุ่มข้อมูลทำได้เร็วขึ้น

ตัวอย่าง: พิจารณาตาราง products ที่มี Column ดังนี้:

product_id (INT, Primary Key)
product_name (VARCHAR)
category_id (INT, Foreign Key)
price (DECIMAL)
stock_quantity (INT)

หากเรามักจะค้นหาสินค้าตาม category_id เราควรสร้าง Index บน Column นี้:

CREATE INDEX idx_category_id
ON products (category_id);

หากเรามักจะค้นหาสินค้าตามช่วงราคา เราอาจพิจารณาสร้าง Index บน Column price:

CREATE INDEX idx_price
ON products (price);

อย่างไรก็ตาม หากเรามักจะค้นหาสินค้าโดยใช้ทั้ง category_id และ price เราอาจพิจารณาสร้าง Composite Index:

CREATE INDEX idx_category_id_price
ON products (category_id, price);

ข้อควรระวังในการสร้าง Index

แม้ว่า Index จะมีประโยชน์ในการเพิ่มประสิทธิภาพการค้นหา แต่ก็มีข้อควรระวังที่ต้องคำนึงถึง:

พื้นที่จัดเก็บข้อมูล: Index ต้องการพื้นที่จัดเก็บข้อมูลเพิ่มเติม การสร้าง Index จำนวนมากเกินไปอาจทำให้ฐานข้อมูลมีขนาดใหญ่ขึ้น
ประสิทธิภาพในการเขียนข้อมูล: เมื่อมีการเพิ่ม, แก้ไข, หรือลบข้อมูลในตารางที่มี Index, MySQL จะต้องอัปเดต Index ด้วย ซึ่งจะทำให้การเขียนข้อมูลช้าลง
การเลือก Index ที่ผิด: การสร้าง Index บน Column ที่ไม่เหมาะสมอาจไม่ได้ช่วยเพิ่มประสิทธิภาพ และอาจทำให้เสียพื้นที่จัดเก็บข้อมูลโดยเปล่าประโยชน์

แนวปฏิบัติที่ดี:

วิเคราะห์ Query: ก่อนที่จะสร้าง Index ควรวิเคราะห์ Query ที่ใช้งานบ่อย เพื่อระบุ Column ที่เหมาะสม
ทดสอบประสิทธิภาพ: หลังจากสร้าง Index แล้ว ควรทดสอบประสิทธิภาพของ Query เพื่อให้แน่ใจว่า Index ช่วยเพิ่มประสิทธิภาพจริง
ตรวจสอบ Index ที่ไม่ได้ใช้งาน: ตรวจสอบ Index ที่ไม่ได้ใช้งานเป็นประจำ และลบ Index ที่ไม่จำเป็นออก
พิจารณา Cardinality: พิจารณา Cardinality ของ Column ก่อนสร้าง Index Column ที่มี Cardinality ต่ำอาจไม่จำเป็นต้องมี Index

สรุป

การสร้าง Index ด้วยคำสั่ง CREATE INDEX เป็นเครื่องมือสำคัญในการเพิ่มประสิทธิภาพการค้นหาข้อมูลในฐานข้อมูล MySQL การเลือก Column ที่เหมาะสมและการคำนึงถึงข้อควรระวังต่างๆ จะช่วยให้เราสามารถใช้ Index ได้อย่างมีประสิทธิภาพสูงสุด ในบทถัดไป เราจะเรียนรู้วิธีการลบ Index ที่ไม่จำเป็นออกด้วยคำสั่ง DROP INDEX