[MySQL] MySQL 성능 개선 - 인덱스, 실제 개선 사례

맥락 공유

상황

• 4월 인보이스 발행 과정에서 DB에 부하가 발생하고 있는 것을 확인함.
• AWS Performance Insight 라는 모니터링도구를 통해 문제되는 쿼리를 발견함. (과거 인보이스 조회하는 쿼리)
• 인보이스에 인덱스 추가하여 부하 문제 해결하고 성능 개선하는 이슈를 진행함.

결론 (인덱스 적용 후)

• DB 부하 문제 해결.
• 13분 넘게 걸리던 인보이스 발행 성능을 1분 30초 정도로 개선.

 

1. 쿼리 성능 저하 원인

쿼리 느린 이유 -> 디스크 I/O

• 디스크에서 파일을 가져오는 건 매우 비효율적이고 느린 작업.
• 병목으로 작용하는 디스크 I/O를 개선하기 위해 활용하는 것이 캐시나 인메모리 DB

성능 저하 원인 -> 디스크 I/O 부하 증가

• Block(Page): 데이터 읽고 쓰는 단위 
• 블록 늘어날수록 디스크 I/O 부하 증가 -> 디스크 경합 심화 -> 급격한 성능 저하 
• Table Full Scan: 모든 블록 읽어 데이터 찾는 방식

성능 개선 방법

• 고성능 장비 구매
• 캐시 늘리기
• 파티셔닝
• 인덱스

2. Index?

대용량 테이블에서 필요한 데이터를 빠르고 효율적으로 액세스하기 위해 사용하는 오브젝트, 자료구조

구조와 동작

• 주로 B+, B* 트리 인덱스
  • 모든 데이터는 리프 노드에 존재한다. 
  • Double Linked List 구조로 양쪽으로 수평 탐색이 가능하다.
  • 리프 노드까지 수직 탐색하여 시작 지점을 찾고, 수평 탐색을 통해 테이블 블록을 찾는다.
  • 검색, 삽입, 삭제 시간복잡도 모두 O(log n)
• 리프 블록에서 얻은 주소 정보로 테이블 블록에 액세스해 데이터 읽는 방식 -> 논리적, 물리적 I/O 부하가 감소

장점

• (인덱스 설계가 잘 되어 있다면) 데이터 검색 속도가 개선된다.
• (인덱스 설계가 잘 되어 있다면) 데이터양이 증가해도 쿼리 성능에 크게 영향을 받지 않는다.

3. 음? 그럼 Index는 만능인가?

단점

• DML(Insert, Update, Delete) 성능 저하 (인덱스 업데이트)
• 데이터베이스 사이즈 증가 (디스크 낭비 가능성)
• 데이터베이스 관리 및 운영 비용 상승
• 잘못 설계하면 오히려 Table Full Scan 방식보다 성능이 떨어질 수 있음.

인덱스를 이용한 테이블 액세스는 고비용이다.

• 인덱스 ROWID 이용한 테이블 액세스는 생각보다 고비용이다.
  • 한 번에 하나의 블록만 읽어옴. (캐시 x)
  • 물리적, 논리적으로 떨어져 있는 블록을 읽을 가능성이 높다.
• Table Full Scan 은 효율적인 액세스 방식을 사용한다.

• 인덱스를 잘못 설계해서 선택도가 높은 경우 오히려 Table Full Scan이 유리할 수 있다.
• 손익분기점은 보통 추출 건수 5~20% 낮은 수준에서 결정. (데이터가 많으면 많을수록 더 낮아짐)

4. Index 설계

• 기준
  • 조건절에 항상 사용하거나, 자주 사용하는 컬럼.
  • 카디널리티가 높으면 좋다.
    • 카디널리티: 컬럼이 가질 수 있는 고유한 값의 수
      • SW 팀 이름: 13
      • SW 팀 성별: 2
  • 선택도가 낮으면 좋다.
    • 선택도: 값을 검색했을 때 반환되는 데이터의 비율 
      • SELECT * FROM 이큐브랩SW WHERE type = ‘사람’ -> 100%
      • SELECT * FROM 이큐브랩SW WHERE nickname = ‘x’ -> 8%?
• 순서
  • 결합 인덱스의 경우 선두 컬럼은 조건절에 항상 사용되는 것을 우선해야한다. (선두 컬럼 없으면 인덱스 검색 x)
  • 일치(=) 조건으로 자주 조회하는 컬럼을 앞쪽에 둔다.
    • 일치 조건의 경우 선택도는 순서와 상관 없다.

5. Index 적용 사례

1. 쿼리 분석 (RDS Performance Insight)

인보이스 조회 쿼리가 문제구나?

2. 인덱스 컬럼 후보 리스트업

• accountId, publishOn, type, statues이 공통적으로 사용되는구먼?

3. 인덱스 기준 컬럼, 순서 결정 (카디널리티, 선택도 고려)

accountId, publishOn, type, statues  →  accountId, publishOn

4. 테스트 (accountId)

CREATE INDEX IX_invoice_accountId ON invoice (accountId);

4. 테스트 (accountId, publishOn)

CREATE INDEX IX_invoice_accountId_publishOn ON invoice (accountId, publishOn);

5. 결과

 

결론

• 인덱스를 잘 활용하면 큰 성능 개선을 이뤄낼 수 있다. 
• 다만 인덱스 단점, 특징 고려하여 적절한 상황에 잘 써먹자.
• 인덱스가 만능은 아니다. 맹신하지는 말자.
• Table Full Scan이 유리한 경우도 있다.