[Database] MySQL Index 적용해보기

드링크리 프로젝트에서는 가게(Store) 정보를 관리하는 과정에서 가게에 등록된 여러 개의 이미지를 저장해야 했고 이를 위해 store_image 테이블을 별도로 분리하여 관리하기로 했다.

id	이미지의 고유 ID (PK)
store_id	가게 ID (FK)
storeImageUrl	이미지 URL
storeImageType	이미지 타입 (메뉴, 내부, 기타 등)
storeImageDescription	이미지 설명

이때, 특정 가게(store_id)에 속한 이미지를 빠르게 조회해야 하는 경우가 많기 때문에 MySQL Index를 적용해서 기존 방식과 성능 비교를 해보기로 했다.

 

---

조금 더 구체적으로 왜 인덱스를 도입하고자 하는지 정리해보겠다.

 

왜 인덱스를 도입하고자 하는가?

100만 건 이상의 데이터에서 인덱스 기준이 하나도 잡혀 있지 않다면?

현재는 가게 이미지를 조회해야하는 경우 인덱스를 사용하지 않고 아래와 같이 쿼리를 날리고 있다.

 

 

지금은 전체 데이터가 적어 큰 문제가 되지 않지만,
향후 가게 정보가 증가하고 store_image 테이블에 가게당 약 8개 이상의 이미지가 저장된다면 빠르게 가게 이미지 테이블의 row가 쌓이게 될것이다.

이때, store_image 테이블에서 store_id는 FK(외래 키)일 뿐 PK(기본 키)가 아니므로, 적절한 인덱스가 없을 경우 Full Table Scan이 발생하게 된다.

즉, 데이터가 많아질수록 조회 속도가 느려지고, DB 부하가 증가하여 성능 이슈가 발생할 수 있다.

 

그렇기 때문에 store_id를 기준으로 인덱스를 도입한다면 훨씬 빠르게 조회가 가능할 것이라고 판단했다.

또한 해당 Store Image 테이블의 경우 INSERT, UPDATE, DELETE 보다는 SELECT의 사용이 훨씬 빈번할 것이기 때문에 느린 쓰기를 감수하고 빠른 읽기를 선택했다.

---

단일 인덱스 적용 전

이제 Index를 적용해보기 전에 비교를 위해 store_image 테이블에 25만 개의 더미 데이터를 넣고, 인덱스를 적용하기 전의 쿼리 속도를 측정해봤다.

SELECT s.*, si.*
FROM store s
LEFT JOIN store_image si ON s.store_id = si.fk_store_id
WHERE s.store_id = 1;

이 쿼리는 store 테이블과 store_image 테이블을 store_id를 기준으로 조인하고, 특정 가게에 대한 이미지를 조회하는 쿼리이다.

✅ 실행 결과 ➡️ 쿼리 실행 시간:  0.127초

---

단일 인덱스 적용 후

위 쿼리에서 fk_store_id를 기준으로 데이터를 조회하기 때문에 fk_store_id 즉, 하나의 열을 기준으로 인덱스를 생성했다. 

CREATE INDEX idx_store_image_store_id ON store_image(fk_store_id);

 

인덱스를 걸고 난 후 쿼리 시

✅ 실행 결과 ➡️ 쿼리 실행 시간:  0.0017초

 

쿼리 실행 시간은 인덱스를 적용하기 전후로 약 8.23배 빨라졌다.
아직 더미 데이터이고 더 다양한 업체가 들어와서 더 다양한 store_id가 생긴다면 훨씬 더 효과적으로 쿼리할 수 있을 것이다.

 

인덱스를 데이터베이스에서 사용할 때의 장단점은 SELECT 쿼리뿐만 아니라 INSERT, UPDATE, DELETE와 같은 데이터 수정 연산에도 영향을 미친다.

먼저 SELECT의 경우 인덱스는 데이터 검색을 빠르게 도와주기에 큰 이득이 있을 것이다. 특히 WHERE 절에서 조건을 걸거나, JOIN 연산을 할 때, 그리고 ORDER BY나 GROUP BY로 데이터를 정렬할 때 인덱스는 매우 유용하다. 특히, 대규모 데이터를 다룰 때 인덱스가 없으면 Full Table Scan이 발생해 성능이 급격히 저하될 수 있는데, 인덱스가 있으면 이런 성능 저하를 피할 수 있다. 또한, 범위 조회나 LIKE('~~%') 검색 같은 경우에도 인덱스를 활용하면 성능이 크게 향상된다.

하지만 인덱스가 INSERT, UPDATE, DELETE와 같은 데이터 수정 연산에는 단점이 될 수 있습니다. 첫째, INSERT 시 인덱스가 추가되면, 새로운 데이터를 테이블에 삽입한 후 그 데이터에 대해 모든 인덱스를 업데이트해야 하기 때문에 삽입 성능이 떨어질 수 있다. 특히, 인덱스가 많은 테이블에 데이터를 추가할 때는 성능 저하가 발생할 수 있다. 둘째, UPDATE 연산에서는 수정된 값에 해당하는 인덱스를 다시 갱신해야 하기 때문에 성능에 영향을 미칠 수 있다. 예를 들어, 인덱스를 기준으로 값을 업데이트하면 해당 인덱스를 재정렬해야 하므로 시간이 소요된다. 셋째, DELETE 연산에서도 삭제된 데이터에 대해 인덱스를 업데이트해야 하기 때문에 삭제 성능이 영향을 받을 수 있다.

결국, 인덱스는 SELECT 쿼리에서는 성능을 극대화하는 중요한 도구지만, 데이터 수정 연산에서는 성능에 부정적인 영향을 미칠 수 있습니다. 따라서 데이터베이스 설계 시에는 조회가 빈번한 컬럼에 대해서만 인덱스를 추가하는 것이 좋고, INSERT, UPDATE, DELETE 연산이 빈번한 테이블에는 최소한의 인덱스만 사용하는 것이 성능 최적화에 도움이 된다.

---

MySQL에서 내가 적용한 해당 인덱스는 B+Tree 알고리즘을 기반으로 동작하는데 다음번에 조금 더 구체적으로 파보도록 하겠다.