MySQL 인덱스를 통한 성능 최적화: 효과적인 인덱스 설계와 쿼리 튜닝 방법

머리말,

실무를 진행하면서 개발했던 서비스에 추가 요구사항이 생겨 테이블 변경이 필요한 상황이었습니다.

1:1 관계였던 테이블이 N:M 관계로 변해 반정규화나 매핑 테이블을 추가해서 요구사항에 맞게 재설계 과정이 필요했습니다.

 

Why 일대일 관계가 다대다 관계가 됐는지,

기존 서비스에서는 특정 시간 대에 하나의 객실에 대해 문의 예약건을 받아 처리했습니다. (문의 하나당 객실 하나를 담당한다.)

하지만, 해당 기능은 사용자뿐만 아니라 관리자(사내 임직원)들도 사용하는 기능이어서 문의 하나 당 하나의 객실을 담당하게 되면 문의를 등록하는 데 불편한 점이 있었습니다.

 

그래서 이를 하나의 문의에 여러 개의 객실을 관리할 수 있도록 ERD를 재설계하였습니다.

* 1:N 관계로 하게 되는 경우 객실 정보를 제외한 나머지 데이터가 중복으로 저장되어, 조회 뿐만 아니라 수정, 삭제 상황에서 데이터 일관성이 깨질 수 있는 문제가 발생할 것이라 판단하여 N:M 관계로 설계하고 매핑 테이블을 통해 분리하였습니다.

* 이러한 데이터 관리와 추가 요구사항을 받아들이기 위해 매핑 테이블을 추가하여 하나의 문의에 여러 개의 객실 정보를 등록할 수 있도록 하였습니다.

 

매핑 테이블 추가로 인한 Join 횟수 증가

기존 문의 목록 조회에서 3번의 Join이 발생했었는데 여기서 매핑 테이블이 추가되면서 4번의 Join이 필요했습니다.

Query를 분리해서 조회할 수 있지만, 목록 조회를 할 때 Slot에 대한 검색 기능이 필수 기능이어서 4번 Join이 불가피한 상황이었습니다.

 

기존 상황

.from(fi)
.join(member).on(fi.userId.eq(member.id))
.leftJoin(fic).on(fi.inquiryCategoryId.eq(fic.id))
.leftJoin(slot).on(fislot.slotId.eq(slot.id))

 

변경 상황

.from(fi)
.join(member).on(fi.userId.eq(member.id))
.leftJoin(fic).on(fi.inquiryCategoryId.eq(fic.id))
.leftJoin(fislot).on(fi.id.eq(fislot.inquiryId)) // 추가됨
.leftJoin(slot).on(fislot.slotId.eq(slot.id))

 

결론 : 인덱스 추가를 통해 약 11배 쿼리 성능 최적화

매핑 테이블에서 inquiry_id 를 기준으로 Join을 실행할 때 일반 컬럼이어서 Data Full Scan이 발생하여 속도가 크게 저하되는 문제가 있었습니다.
이에 대해 매핑 테이블 inquiry_slots 에 index를 추가하여 Index Scan으로 조회하여 성능을 최적화하였습니다.

 

매핑 테이블에 인덱스 추가를 통해 약 11배 쿼리 성능을 개선하였습니다.

매핑 테이블에 저장된 값은 inquiry_id, slot_id를 저장하고 있는데 데이터의 특성을 분석해 본 결과 아래와 같았습니다.

1. slot_id는 제거되거나 변경될 일이 전혀 없음
2. inquiry_id의 경우 현재 시설문의 데이터를 Hard Delete 하지 않고 모두 Soft Delete 하고 있어서 데이터 삭제가 발생하지 않는다.
   1. 현재, 문의에 등록한 객실 정보를 변경하는 기능을 제공하고 있지 않아 데이터 업데이트도 발생하지 않는다.
   2. 하지만, 추가로 객실 정보를 변경할 수도 있겠지만 변경되는 상황보다 시설문의 목록을 조회하는 상황이 더 빈번하다고 판단되어, 인덱스를 추가하는 것이 더 효율적이라 판단됨!

매핑 테이블 inquiry_slots의 경우 데이터 Write 작업이 거의 일어나지 않으므로 인덱스를 추가하여 조회 성능을 높이는 것이 효과적이라 판단하여 인덱스를 추가하였습니다.

 

Index 추가를 통한 성능 비교

참조 키에 인덱스를 안 걸었을 때

→ 쿼리 실행 시간 2s 872ms 소모

→ 데이터 응답까지 걸린 시간 3.74s

 

매핑 테이블과 조인하는 부분에서 인덱스가 설정되어 있지 않아, 모든 데이터를 Full Scan하는 문제가 있었습니다.
이는 하나의 문의 건에 대해 게속해서 N번의 조회를 진행하게 되어 조회 속도가 현저하게 느려져, 사용자들이 자주 사용하는 목록 조회, 검색, 필터링 기능에 불편함을 줄 수 있겠다 생각하여 성능을 최적화해야 했습니다.

 

 

인덱스 추가했을 때

create index inquiry_id on inquiry_slots(inquiry_id);
create index slot_id on inquiry_slots(slot_id);

→ 쿼리 실행 시간 247ms 소모

→ 데이터 응답까지 걸린 시간 470ms

 

매핑 테이블에 인덱스를 추가하여 Ref (부분 참조) 타입 조회를 통해 성능을 약 11배 개선시켰습니다.
이제, 인덱스 테이블을 통해 정렬된 구조에서 inquiry_id에 해당하는 데이터를 찾고 Slots 테이블과 빠르게 Join이 가능하게 되어
2s 872ms -> 247ms 라는 속도 개선을 이루어낼 수 있었습니다.

 

slot_id 컬럼에 인덱스는 왜 걸었어요?

목록 조회에서는 inquiry_id 컬럼의 인덱스를 통해 조인이 이루어져 slot_id 컬럼의 인덱스를 사용하지 않고 있습니다.

하지만, 목록 조회 말고 단일 조회나 캘린더 일정을 조회하는 부분에서는 조회 쿼리를 분리해서 Slot 정보를 가져오고 있어, 다른 API에서 사용하고자 추가하였습니다.

 

인덱스 추가를 했을 때 문제가 없는지?

1. 인덱스를 추가한 데이터 필드는 삭제되는 않는 데이터로 매핑 테이블 인덱스에 영향을 미치지 않습니다.
2. 카디널리티(cardinaility)가 높은 편으로 인덱스를 설정에 용이했습니다.
   • 매핑 테이블이어서 같은 값을 가질 수 있겠지만, 해당 값들은 동시에 조회되어야 하는 데이터여서 문제가 없다고 판단되었습니다.
3. 인덱스를 추가하는 경우 추가로 인덱스 테이블을 저장해야 하지만 해당 기능은 조회하는 경우가 빈번하게 일어나는 기능이어서 인덱스 추가가 더 효율적이었습니다.

 

Query 최적화를 하면서 느낀점

실무를 하기 전에는 Index는 "PK랑 검색하는 컬럼에만 설정하면 되는거 아닌가" 라고 생각했는데, Query 성능을 최적화하기 위해서 공부를 하면서 Index에 대해 조금 더 알게 되는 시간이었습니다.

검색하려는 필드뿐만 아니라, 탐색(Join)이 이루어지는 컬럼에도 인덱스를 추가하여 더 적은 데이터 검색 횟수로 성능을 최적화할 수 있음을 깨달았습니다.

 

무조건적인 인덱스 추가가 전부 성능 개선으로 이루어지는 것은 아니지만, 서비스 흐름을 분석했을 때 데이터 변경이 자주 발생하지 않고 조회 성능이 중요할 때에는 인덱스를 추가하는 게 성능 개선에 도움이 되는 것을 알 수 있었습니다.

 

다음글,

* 여러 테이블에 여러 인덱스를 추가했는데, 사용하지 않는 인덱스와 중복된 인덱스를 제거하는 방법

* 단일 인덱스와 복합 인덱스는 무엇이 다르고, 언제 복합 인덱스를 사용하는 것이 좋은지