INDEX가 자동 생성되는 컬럼

• PK, FK, UNIQUE 제약조건이 추가되면 해당 컬럼에 자동으로 INDEX가 생성된다.

INDEX 종류

INNODB에는 INDEX 종류가 두 가지가 있다. 클러스터 인덱스와 보조 인덱스이다.
클러스터 인덱스

• PK로 설정되는 인덱스이다.
• 직접 인덱스 수정은 불가능하다.
• PK를 바꾸면 수정된다. (PK를 바꾸면, fk 제약 조건과 클러스터 인덱스 수정 등 다양한 문제가 뒤따름으로 수정하지 않는게 좋다.)

보조 인덱스

• 개발자나 DBA가 생성하는 인덱스이다.
• 직접 인덱스 수정이 가능하다. (하지만, 인덱스를 생성한다는 건 엄청난 부하가 생기는 작업이므로 서비스 운영 시간에는 설정하지 않는게 좋다.)

인덱스 설계

좋은 인덱스 설계

1. 적절한 인덱스 개수 유지 (과유불급)

📌 인덱스가 너무 많으면 성능 저하 가능
• 인덱스는 조회 성능을 향상시키지만, 데이터 변경(INSERT, UPDATE, DELETE) 시 인덱스도 함께 갱신해야 하기 때문에 너무 많은 인덱스는 오히려 성능을 저하시킬 수 있음.
• 자주 조회하는 쿼리에 맞게 최소한의 인덱스만 유지하는 것이 중요.

✅ 해결책:
• EXPLAIN을 사용해 실제로 쿼리가 어떤 인덱스를 사용하고 있는지 확인하고 불필요한 인덱스는 제거
• 자주 사용되지 않는 인덱스는 삭제 (DROP INDEX index_name ON table_name;)

2. 클러스터 인덱스(Primary Key)는 AUTO_INCREMENT가 유리

📌 기본 키를 AUTO_INCREMENT로 하면 삽입 성능이 향상됨.
• 기본 키는 클러스터 인덱스(Clustered Index) 역할을 하므로, 기본 키가 변경되면 데이터 정렬이 다시 이루어져 성능 저하 발생 가능
• 랜덤한 값(예: UUID)을 기본 키로 사용하면 삽입 성능이 저하될 수 있음 (데이터 정렬 필요)

✅ 해결책

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    age INT
) ENGINE=InnoDB;

•	연속적인 값(AUTO_INCREMENT)을 사용하면 데이터 정렬이 효율적
•	UUID 같은 랜덤 값이 필요하다면 보조 인덱스로 관리

3. 다중 컬럼 인덱스(Multi-Column Index) 활용

📌 여러 개의 단일 인덱스보다는 다중 컬럼 인덱스를 고려
• WHERE 절에서 여러 컬럼을 함께 사용하는 경우 다중 컬럼 인덱스를 사용하는 것이 유리함.
• 왼쪽에서 오른쪽 방향으로 정렬된 인덱스 순서에 맞춰 조회할 때만 최적화됨 (Leftmost Prefix Rule)

✅ 해결책:

CREATE INDEX idx_employee_name_age ON employees(name, age);

•	WHERE name = 'Alice' AND age = 30 → 인덱스 사용 가능
•	WHERE age = 30 → 인덱스 사용 불가능 (name 먼저 조회해야 함)

📌 이 경우라면 name, age 대신 age, name 순서로 변경해야 함!

4. 인덱스 컬럼 개수 최소화

📌 불필요하게 많은 컬럼을 인덱스에 포함하면 인덱스 크기가 커지고 성능이 저하됨.
• 인덱스에 포함된 컬럼이 많으면 데이터를 읽고 정렬하는 비용 증가
• 자주 사용하는 컬럼만 포함하도록 인덱스를 최소화

✅ 해결책:
• 조회 빈도가 높은 컬럼만 포함
• SELECT * 대신 실제로 필요한 컬럼만 조회

5. 커버링 인덱스(Covering Index) 활용

📌 인덱스만으로 데이터를 조회할 수 있다면 성능 향상 가능
• 일반적으로 보조 인덱스는 기본 키를 통해 실제 데이터를 추가 조회(Index Lookup)해야 하지만,
인덱스에 필요한 모든 컬럼이 포함되면 데이터 조회 없이 인덱스만으로 처리 가능 → 성능 향상!

✅ 해결책:

CREATE INDEX idx_covering ON employees(name, age);
SELECT name, age FROM employees WHERE name = 'Alice';

•	name, age가 인덱스에 포함되어 있기 때문에 실제 테이블 조회 없이 인덱스만으로 결과 반환 가능

6. LIKE 검색 최적화 (접두어 매칭)

📌 LIKE 검색 시 인덱스 사용을 고려해야 함.
• LIKE '%keyword%' → 인덱스 사용 불가능
• LIKE 'keyword%' → 인덱스 사용 가능

✅ 해결책:

CREATE INDEX idx_name ON employees(name);
SELECT * FROM employees WHERE name LIKE 'A%'; -- ✅ 인덱스 사용 가능
SELECT * FROM employees WHERE name LIKE '%A%'; -- ❌ 인덱스 사용 불가능

인덱스를 활용하려면 접두어(prefix) 매칭을 사용해야 함!

📌 만약 전체 문자열 검색이 필요하다면?
• FULLTEXT INDEX 사용 (단, MyISAM 또는 InnoDB에서 지원)

7. EXPLAIN을 사용해 인덱스 최적화 검증

📌 쿼리가 실제로 어떤 인덱스를 사용하고 있는지 확인하는 것이 중요
• EXPLAIN을 사용하면 쿼리 실행 계획을 확인할 수 있음

EXPLAIN SELECT * FROM employees WHERE name = 'Alice';

•	possible_keys: 사용 가능한 인덱스
•	key: 실제 사용된 인덱스
•	rows: 조회 예상 행 수 (낮을수록 좋음)

✅ 해결책:
• key 값이 NULL이면 → 인덱스가 사용되지 않음 → 인덱스 추가 고려
• rows 값이 크면 → 인덱스 성능이 낮음 → 다중 컬럼 인덱스 또는 인덱스 재구성 고려

INDEX 걸리는 경우의 수

인덱스 설정해둔 컬럼이 where 조건에 있는 경우

• 인덱스 설정된 컬럼 하나만 있는 경우
  해당 컬럼 INDEX를 탄다.

• 인덱스 설정된 컬럼 하나, 인덱스 없는 컬럼 하나 있는 경우
  인덱스 설정된 컬럼의 INDEX를 탄다.

• 각각 인덱스 설정된 컬럼이 두개 이상인 경우
  DB 엔진이 최적화 알고리즘을 통해 제일 적합한 INDEX를 탄다.

• 복합 인덱스(두 컬럼에 동시에 인덱스가 설정됨)인 경우
  두 컬럼 모두 and 조건으로 조회를 해야 INDEX를 탄다.