[mysql] 성능 최적화 1편 (인덱스/커버링 인덱스)

데이터베이스 성능에는 테이블, 쿼리, 설정 등 다양한 수준의 요소들이 영향을 미친다. 최적화를 위해 소프트웨어, 하드웨어 수준에서 조절할 수 있지만 이 글에서는 하드웨어 최적화는 다루지 않을 것이다.
 

데이터베이스 최적화

최적화 방법을 살펴보기 전에 아래 질문으로 어떤 점을 고려해야 하는지 간단히 알아보자.

• 테이블은 올바른 데이터 유형의 열을 가지고 있고 적절한 수의 열을 갖추고 있는가
• 인덱스가 적절히 구성되어 있는가
• 각 테이블에 적합한 스토리지 엔진을 사용하고 해당 엔진의 강점과 기능을 활용하고 있는가
• 디스크 낭비를 줄이고 I/O 시간을 단축하기 위해 압축된 테이블을 사용하고 있는가
• 동시성을 위해 적절한 락 전략을 사용하고 있는가
• 메모리 캐싱 영역을 올바른 크기로 설정했는가

 

SQL문 최적화

sql문은 데이터베이스를 조작하는 핵심 로직이므로, 최적화에 큰 영향을 미친다. 이번 포스팅에서는 `SELECT`문 최적화에 대한 일부 내용을 살펴볼 것이다. `SELECT` 최적화는 데이터베이스의 조회 작업을 향상시키는 아주 중요한 부분이다. `SELECT` 쿼리를 최적화하기 위한 주요 고려사항을 살펴보자.

• `WHERE`절에 사용되는 열에 인덱스를 설정하여 검색을 가속화한다.
• 시간이 과도하게 소요되는 쿼리를 튜닝한다.
• 풀 테이블 스캔을 최소화한다.
• `ALALYZE TABLE` 명령문을 주기적으로 실행하여 mysql 옵티마이저가 테이블의 통계를 새로 계산하고 최적의 실행 계획을 세우도록 한다.
• `EXPLAIN`을 이용하여 쿼리 세부사항을 검토한다.

자세한 내용은 여기서 확인할 수 있다.
 

인덱스 설정하기

현재 진행하고 있는 프로젝트의 `SELECT`문을 최적화할 것이다. 먼저, mysql에 접속하여 어떤 스토리지 엔진을 사용하고 있는지 확인해보자.

InnoDB를 스토리지 엔진으로 사용하고 있다!

*show engines : 사용 가능한 스토리지 엔진 목록과 각 엔진의 상태 표시

 
이제 내가 사용하는 데이터베이스를 선택하고, 테이블의 구조와 인덱스 정보를 확인할 것이다.

USE [DATABASE];
DESCRIBE [TABLE];
SHOW INDEX FROM [TABLE];

`tarot_card` 테이블에는 `id`와 `card_pack_id`가 각각 기본키와 복합키로 설정되어 있다. 여기서 `PRI`는 기본키, `MUL`은 복합키를 의미한다. `SHOW INDEX FROM...` 명령으로 인덱스 정보를 조회하면, 앞서 언급한 키들이 자동으로 인덱스로 등록된 것을 확인할 수 있다. (직접 인덱스를 설정해주지 않아도 InnoDB 스토리지 엔진이 자동으로 인덱스를 생성해준다!)

SELECT `TarotCard`.`id` AS `TarotCard_id`, `TarotCard`.`card_no` AS `TarotCard_card_no`, `TarotCard`.`ext` AS `TarotCard_ext`, `TarotCard`.`created_at` AS `TarotCard_created_at`, `TarotCard`.`updated_at` AS `TarotCard_updated_at`, `TarotCard`.`deleted_at` AS `TarotCard_deleted_at`, `TarotCard`.`card_pack_id` AS `TarotCard_card_pack_id` 
FROM `tarot_card` `TarotCard` 
WHERE ( (`TarotCard`.`card_no` = ?) ) AND ( `TarotCard`.`deleted_at` IS NULL ) LIMIT 1

위 sql문은 typeorm의 로그에 기록된, 타로 카드 정보를 조회하는 `SELECT`문이다. 하지만 `WHERE`절에 사용되는 `card_no`에 인덱스가 적용되어 있지 않다. 이는 `tarot_card` 테이블이 대량의 데이터를 포함하고 있을 때 검색 성능에 부정적인 영향을 미칠 수 있다. 따라서 `card_no` 컬럼에 인덱스를 설정하여 검색 성능을 높여야 한다.
 
그 전에 `EXPLAIN`*을 통해 현재 쿼리의 실행 계획을 살펴보자.

*EXPLAIN : 쿼리가 어떻게 실행되는지에 대한 정보 (어떤 인덱스가 사용되고, 어떤 조인이 이루어지는지 등)

현재 쿼리는 모든 레코드를 검색하고 있다. (type=ALL) 사진을 보면 인덱스가 적용되지 않아 `key` 부분이 비어있다.
 
아래는 `EXPLAIN ALALYZE SELECT...`의 결과다. 테이블을 풀스캔하므로 (Table scan...) 필터링에 약 7 밀리초의 시간이 소요되었다. (Filter...) 매번 실행할 때마다 결과가 다르게 나오긴 하지만 인덱스를 설정하지 않으면 항상 6 밀리초 이상 걸렸다. 

+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                                                                                                                                        |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 -> Limit: 1 row(s)  (cost=554 rows=1) (actual time=7.12..7.12 rows=1 loops=1)
    -> Filter: ((tarot_card.card_no = 0) and (tarot_card.deleted_at is null))  (cost=554 rows=53) (actual time=7.12..7.12 rows=1 loops=1)
        -> Table scan on tarot_card  (cost=554 rows=5301) (actual time=0.104..4.59 rows=3116 loops=1)
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

 
이제 `WHERE`절에 사용되는 `card_no`열에 인덱스를 생성해보자.

CREATE INDEX card_no_idx ON tarot_card (card_no);

인덱스를 생성 후 `SHOW INDEX...`를 수행하면 `card_no_idx`라는 인덱스가 추가된 것을 확인할 수 있다.

`EXPLAIN` 명령을 실행하여 쿼리 실행 계획을 확인했다. `card_no_idx`가 적용되었다!

인덱스를 적용한 후 `EXPLAIN ANALYZE SELECT...`를 실행하면 약 0.03 밀리초에서 0.07 밀리초 사이의 값이 나온다.  풀스캔 대신 인덱스를 사용하여 검색하고 (Index lookup...) 이후에 필터링을 수행함으로써 (Filter...) 소량의 행만을 필터링하게 되어 약 0.03 밀리초 정도로 단축되었다. 인덱스를 설정함으로써 성능이 약 10배 정도 향상된 것을 확인할 수 있다.

+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                                                                                                                                                    |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 -> Limit: 1 row(s)  (cost=0.26 rows=0.1) (actual time=0.0368..0.0375 rows=1 loops=1)
    -> Filter: (tarot_card.deleted_at is null)  (cost=0.26 rows=0.1) (actual time=0.0332..0.0332 rows=1 loops=1)
        -> Index lookup on tarot_card using card_no_idx (card_no=0)  (cost=0.26 rows=1) (actual time=0.0297..0.0297 rows=1 loops=1)
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

 

커버링 인덱스 설정하기

더 나아가 이번엔 커버링 인덱스를 활용해볼 것이다. 커버링 인덱스는 인덱스 자체에 결과에 필요한 모든 열을 포함하고 있다. 이 경우, 데이터 레코드를 참조하지 않고, 인덱스의 리프 노드만을 참조하여 결과를 생성한다. (디스트 I/O 작업이 줄어든다!)

export class TarotCardDto {
  @IsUrl()
  @ApiProperty({ description: '타로 카드 이미지 URL', required: true })
  readonly cardUrl: string;

  static fromEntity(entity: TarotCard): TarotCardDto {
    return { cardUrl: `${BUCKET_URL}/basic/${entity.cardNo}${entity.ext}` };
  }
}

앞서 조회한 타로 카드 정보를 기반으로 DTO 객체를 생성한다. `id`, `created_at`, `updated_at` 등 다양한 정보를 불러왔지만 막상 활용하는 정보는 `card_no`, `ext`, (아직 개발 중이라 코드에는 없지만) `card_pack_id` 뿐이다. 커버링 인덱스에 해당 정보들을 담고, 불러오는 데이터 컬럼을 조정하여 인덱스만 참조하도록 수정해보자.

async findTarotCardByCardNo(cardNo: number): Promise<TarotCardDto> {
    try {
      const startTime = new Date();
      const tarotCard: TarotCard | null =
        await this.tarotCardRepository.findOneBy({
          cardNo: cardNo,
          cardPack: undefined,
        });
      const endTime = new Date();
      const diff = endTime.getTime() - startTime.getTime();
      console.log(`findOneBy execution time: ${diff} milliseconds`);

      if (!tarotCard) {
        throw new NotFoundException(ERR_MSG.TAROT_CARD_NOT_FOUND);
      }

      return TarotCardDto.fromEntity(tarotCard);
    } catch (err: unknown) {
      throw err;
    }
}

데이터를 조회하는 로직 앞 뒤에 실행 시간을 측정하기 위한 코드를 추가한다. 커버링 인덱스를 적용하기 전에는 약 34 밀리초가 소요되었다.

DROP INDEX card_no_idx ON tarot_card;
CREATE INDEX idx1 ON tarot_card (card_no, ext, deleted_at, card_pack_id);

위 명령어로 기존에 있던 인덱스를 삭제하고 여러 정보들이 담긴 커버링 인덱스를 생성한다.

이제 서비스 코드를 수정하여 DTO에 사용되는 열만 조회하도록 수정하자.

const tarotCard: TarotCard | null =
  await this.tarotCardRepository.findOne({
    where: {
      cardNo: cardNo,
      cardPack: undefined,
    },
    select: ['cardNo', 'ext', 'cardPack'], // 정말 필요한 열만 조회하도록 수정
  });

위와 같이 수정한 후 서비스 코드를 실행하면 약 28 밀리초가 나온다. 6 밀리초 정도 단축되었다! 

💡 커버링 인덱스의 장단점

• 쿼리 성능 향상 - 데이터를 인덱스에서 가져오므로 디스크 I/O가 감소한다. 이는 곧 빠른 응답 시간으로 이어진다.
• 인덱스 업데이트 오버헤드 - 커버링 인덱스를 적용한 열이 자주 변경되는 경우, 정렬하기 위한 오버헤드가 발생한다.
• 디스크 공간 낭비 - 커버링 인덱스에 필요한 열을 모두 포함하면 그만큼 디스크 공간이 낭비된다.

 

카디널리티를 고려한 인덱스

카디널리티는 데이터의 고유한 값의 수를 의미한다. 즉, 얼마나 다양한 값이 있는지를 나타내는 지표다. 성별을 예로 들어보자. 성별을 나타내는 컬럼에서 카디널리티는 2다. 왜냐하면 남성과 여성이라는 두 가지 값만 존재하기 때문이다. 반면, 주민등록번호는 고유한 번호이기 때문에 카디널리티가 매우 높다.

 

인덱스를 설정할 때 카디널리티가 높은 컬럼을 선택하는 것이 좋다. 왜냐하면 해당 컬럼의 값이 다양하기 때문에 더 적은 레코드를 검색하기 때문이다.

CREATE INDEX idx1 ON tarot_card (card_no, ext, deleted_at, card_pack_id);

위 코드는 커버링 인덱스를 생성하는 sql문이다. 인덱스 순서도 카디널리티를 고려하여 작성해야 한다. 카디널리티가 높은 열을 먼저 나열하여 성능을 높여보자.

DROP INDEX idx1 ON tarot_card;
CREATE INDEX idx2 ON tarot_card (card_no, card_pack_id, ext, deleted_at);

기존 인덱스를 삭제하고 카디널리티를 고려하여 인덱스 순서를 재배치한다.

EXPLAIN ANALYZE SELECT SQL_NO_CACHE card_no, ext, card_pack_id FROM tarot_card WHERE card_no = -5 AND deleted_at IS NULL AND card_pack_id = '216d8543-c90d-11ee-a2b3-0242ac170003' LIMIT 1;

위 sql문으로 각각 10번씩 테스트 한 결과, 아주 미세하게 성능이 향상(?)되었다. 인덱스 간 데이터의 다양성이 거의 차이나지 않아 성능 향상을 체감하지 못했지만, 다양성 차이가 큰 인덱스의 경우, 성능 향상이 크게 나타날 것으로 예상된다!

카디널리티를 고려하지 않은 경우, 실행시간의 합	카디널리티를 고려한 경우, 실행시간의 합
약 0.3342 밀리초	약 0.3247 밀리초

---

참고자료

• https://dev.mysql.com/doc/refman/8.0/en/optimize-overview.html
• https://dev.mysql.com/doc/refman/8.0/en/statement-optimization.html
• https://dev.mysql.com/doc/refman/8.0/en/optimizing-innodb-queries.html
• https://jojoldu.tistory.com/243