[쿼리 튜닝] 2-2. 쿼리 인덱스 튜닝 / 단일 인덱스, 복합인덱스

▶ 명령어 정리

---

인덱스 생성

단일

CREATE INDEX [인덱스명] ON [테이블명] (컬럼명);

 

복합

CREATE INDEX [인덱스명] ON [테이블명] (컬럼명, 컬럼명...);

 

 

테이블의 인덱스 확인

SHOW INDEX FROM [테이블명];

 

 

인덱스 삭제

DROP INDEX FROM [테이블명];

 

 

 

 

 

 

 

 

▶ 인덱스를 이용한 읽기의 손익 분기점 판단

---

일반적인 DBMS의 옵티마이저에서는 인덱스를 통해 레코드 1건을 읽는 것이

테이블에서 직접 레코드 1건을 읽는 것보다 4~5배 정도 비용이 더 많이 드는 작업인 것으로 예측한다.

 

즉, 인덱스를 통해 읽어야 할 레코드의 건수가(옵티마이저가 판단한 예상 건수)

전체 테이블 레코드의 20~25%를 넘어서면 인덱스 사용 X

-> 이 경우 테이블 풀 스캔해서 필요한 레코드만 필터링 하는 방식으로 처리하는 것이 효율적

 

 

정리

테이블에서 읽어야 하는 데이터가 적으면 인덱스를 사용하는 게 유리하지만, 많으면 그냥 풀스캔하는 게 더 빠를 수도 있다!

그래서 DB 옵티마이저는 "읽어야 할 데이터 비율"을 보고 인덱스를 사용할지 말지를 자동으로 결정한다.

읽어야 할 데이터 비율 → 20~25% 넘으면 풀스캔

 

 

 

 

 

 

 

▶ 기본 인덱스 실습

---

✅ CREATE INDEX와 DROP INDEX를 활용하여 인덱스를 생성하고 삭제하는 방법을 배운다.

✅ 단일 컬럼 인덱스 vs 복합 인덱스를 비교하여 어떤 경우에 더 효율적인지 분석한다.

✅ EXPLAIN을 사용하여 인덱스가 쿼리에 미치는 영향을 직접 확인한다.

 

 

 

▷ 인덱스 없이 실행 계획 확인

---

customer 테이블에서 last_name이 SMITH인 고객을 찾아보자.

EXPLAIN 
SELECT * FROM customer WHERE last_name = 'SMITH';

 

이 쿼리를 EXPLAIN 돌렸을 때

 

사용할 수 있는 인덱스가 없어서

type = ALL

즉, 테이블 FULL SCAN이 발생한다.

 

인덱스를 추가해서 해결해보자.

 

 

 

▷ 인덱스 생성 및 인덱스 확인

---

CREATE INDEX idx_last_name ON customer (last_name);

이렇게 last_name 컬럼에 대해서 idx_last_name이라는 인덱스를 만들고

 

SHOW INDEX FROM customer;

이렇게 확인해보면

 

잘 생성된걸 볼 수 있다.

 

이제 다시 실행 계획을 확인해보자.

EXPLAIN
SELECT * FROM customer WHERE last_name = 'SMITH';

 

type = ALL -> ref

key = null -> idx_last_name

으로 바뀐것을 보아 인덱스를 사용하고 있음을 알 수 있다.

 

rows = 599 -> 10으로 바뀌었다.

 

인덱스를 추가한 후 TABLE FULL SCAN이 사라지고, 훨씬 빠른 검색이 가능해졌다.

 

 

 

▷ 단일 인덱스 vs 복합 인덱스 비교

---

이번에는 복합 인덱스를 사용하면 어떤 차이가 생기는지 알아보자.

CREATE INDEX idx_last_first_name ON customer (last_name, first_name);

이렇게 복합 인덱스를 만들어주고

 

EXPLAIN 
SELECT * FROM customer WHERE last_name = 'SMITH' AND first_name = 'JOHN';

WHERE 조건에 first_name = 'JOHN' 까지 넣어서 EXPLAIN을 돌려보자.

 

오호 rows가 1로 줄어들었다~~

 

 

 

▷ 단일 인덱스 vs 복합 인덱스 비교 – 언제 어떤 인덱스를 사용할까?

---

그렇다면 단일 인덱스보다 복합 인덱스가 더 좋은건가?

물론~~ 잘쓴다면 아주 좋지만 꼭 그런건 아니다.

 

인덱스를 추가한다는 것은 항상 신중해야 한다.

뭐가 더 이득인지 따져보고 비교해야한다.

 

인덱스를 저장하기 위해서는 추가적인 저장 공간이 필요하고

조회 속도는 빨라지지만 나머지 Write 연산들은 성능 저하가 발생한다.

더군다나 복합 인덱스는 두 개 이상의 컬럼을 포함하기 때문에 인덱스의 크기가 커진다.

 

그러므로 위에서 말한 손익분기점을 잘 생각해서 인덱스를 추가해야 한다.

 

사용 상황	단일 인덱스 (Single Index)	복합 인덱스 (Composite Index)
특정 컬럼만 자주 조회할 때	✅ 추천	❌ 비효율적
다중 조건(WHERE col1 = ? AND col2 = ?)을 자주 사용할 때	❌ 비효율적	✅ 추천
INSERT, UPDATE가 자주 발생하는 테이블	✅ 단일 인덱스가 부담이 적음	❌ 복합 인덱스는 관리 비용 증가
ORDER BY, GROUP BY 최적화 필요	❌ 단일 인덱스는 제한적	✅ 복합 인덱스가 더 효율적
covering index 활용 (테이블 접근 최소화)	❌ 단일 인덱스로는 한계	✅ 복합 인덱스 사용 시 성능 향상

 

📌 즉, 단일 인덱스와 복합 인덱스는 각각의 장단점이 있고,

📌 쿼리 패턴에 맞게 적절히 선택해야 한다는 것이 핵심