home

หน้าแรก
 Computer >> บทช่วยสอนคอมพิวเตอร์ >  >> การเขียนโปรแกรม >> MySQL

เมื่อไหร่ควรใช้ Composite Index (Multi-column Index) ใน MySQL

Composite Index (Multi-column Index) คือ Index ที่สร้างจากหลายคอลัมน์รวมกัน ใช้ได้เมื่อ Query มีเงื่อนไขหลายคอลัมน์พร้อมกัน

เมื่อไหร่ควรใช้ Composite Index

  • JOIN: ON table1.col1 = table2.col1 AND table1.col2 = table2.col2
  • Filtering (WHERE): WHERE col1 = ? AND col2 = ? AND col3 = ?
  • Sorting (ORDER BY): ORDER BY col1, col2, col3
  • Grouping (GROUP BY): GROUP BY col1, col2
  • Covering Index: SELECT col1, col2 FROM table WHERE col3 = ?

Syntax

-- ตอนสร้างตาราง
CREATE TABLE MultipleIndexDemo (
    id INT,
    FirstName VARCHAR(100),
    LastName VARCHAR(100),
    Address VARCHAR(200),
    INDEX idx_composite (id, LastName, Address)
);

-- หรือเพิ่มภายหลัง
CREATE INDEX idx_composite ON MultipleIndexDemo (id, LastName, Address);

-- หรือ
ALTER TABLE MultipleIndexDemo ADD INDEX idx_composite (id, LastName, Address);

ตรวจสอบ Index ที่สร้าง

SHOW INDEX FROM MultipleIndexDemo;
+-------------------+------------+--------------+--------------+-------------+-----------+
| Table             | Non_unique | Key_name     | Seq_in_index | Column_name | Collation |
+-------------------+------------+--------------+--------------+-------------+-----------+
| multipleindexdemo | 1          | idx_composite| 1            | id          | A         |
| multipleindexdemo | 1          | idx_composite| 2            | LastName    | A         |
| multipleindexdemo | 1          | idx_composite| 3            | Address     | A         |
+-------------------+------------+--------------+--------------+-------------+-----------+

⚠️ หลักการสำคัญ: Leftmost Prefix Rule

Index (A, B, C) ใช้ได้กับ Query ที่ Filter/Sort เริ่มจาก คอลัมน์ซ้ายสุด

Query Conditionใช้ Index (A,B,C) ได้ไหม
WHERE A = 1✅ ใช้ได้
WHERE A = 1 AND B = 2✅ ใช้ได้
WHERE A = 1 AND B = 2 AND C = 3✅ ใช้ได้
WHERE B = 2❌ ไม่ได้ (ข้าม A)
WHERE B = 2 AND C = 3❌ ไม่ได้
WHERE A = 1 AND C = 3⚠️ ใช้ได้แค่ A (ข้าม B)
ORDER BY A, B✅ ใช้ได้
ORDER BY B, A❌ ไม่ได้ (ลำดับผิด)

ลำดับคอลัมน์ใน Composite Index (Column Order)

  1. Equality Columns ก่อน (=, IN) - Selectivity สูงสุด
  2. Range Columns ต่อ (>, <, >=, <=, LIKE 'prefix%')
  3. Sort/Group Columns ท้าย (ORDER BY, GROUP BY)

ตัวอย่างลำดับที่ดี

-- Query: WHERE status = 'active' AND created_at > '2024-01-01' ORDER BY id
-- Index ที่ดี: (status, created_at, id)
-- status = Equality (High selectivity)
-- created_at = Range
-- id = Sort

ข้อควรระวัง

  • Composite Index ใหญ่ = ใช้พื้นที่ Disk มาก, ช้า ตอน Write
  • ไม่ควรเกิน 3-5 คอลัมน์ต่อ Index
  • ใช้ EXPLAIN ตรวจสอบว่า Index ถูกใช้จริง (key_len, ref, Extra)