https://youtu.be/nvnl9YgnON8

매트, 토르의 MySQL 성능 최적화

• 매트, 토르의 MySQL 성능 최적화
  • 인덱스를 왜쓸까?
  • 인덱스 실행 계획
  • 인덱스 적용

인덱스를 왜쓸까?

인덱스의 장점 살펴보기

• 데이터베이스에서 성능 최적화는 디스크 I/O 와 관련이 많다.
• 실제로 하드디스크 I/O와 메모리 I/O의 속도 차이는 10만 ~ 15만 배 정도이다.
• 조회 성능 개선 - 디스크 I/O를 줄이는 것이 핵심
• 인덱스는 조회에서 이득을 얻고 수정/삭제 에서 손해를 보는데 괜찮나요?
  • 일반적으로 웹서비스 같은 경우에 CRUD에서 R과 CUD의 비율이 8:2에서 9:1 이라고 한다.
• ORDER BY와 GROUP BY에서 이점
  • ORDER BY: 인덱스를 이용해 정렬이 처리되는 경우
    • 만약에 인덱스가 없었다면 데이터를 다 읽어와서 DB에서 직접 정렬했어야 할 것이다.
    • 인덱스는 이미 정렬되어 있기 때문에 인덱스 순서대로 파일을 일기만 하면 된다.
  • GROUP BY: 인덱스를 이용해 GROUP BY를 하는 경우

인덱스 실행 계획

• all: 테이블 전체를 스캔할 때
  • 성능이 좋지 않다.
  • 인덱스가 없어서 Full Table Scan 하는 경우도 있고 인덱스가 있는데도 Full Table Scan 하는 경우가 있다.
  • 인덱스가 있는데도 Full Table Scan 하는 경우는 데이터 전체의 개수가 그렇게 많지 않거나 읽고자 하는 데이터가 전체 데이터의 25%를 넘어가면 인덱스가 있다 하더라도 Full Table Scan이 일어난다.
• range: 인덱스를 이용하여 범위 검색을 할 때
  • 이상적으로 인덱스를 잘 사용했을때 발생하는 실행 계획이다.
• index: 인덱스 전체를 스캔할 때
  • Idex Range Scan보다는 성능이 좋지 않다.

인덱스 적용

• 기본 컬럼에 인덱스 적용
  • 서비스 특성상 무엇에 대한 조회가 많이 일어나는 파악
  • 카널리티가 높은 컬럼에 대해 인덱스를 생성
• 복합 인덱스 적용
  • 복합 인덱스란?
    • 두개 이상의 컬럼을 합쳐서 인덱스를 만드는것
    • 하나의 컬럼으로 인덱스를 만들었을 때 보다 더 적은 데이터 분포를 보여 탐색할 데이터 수가 줄어든다.
    • 결합 인덱스, 다중 컬럼 인덱스, Composite Index라고도 불린다.
• 커버링 인덱스
  • 인덱스를 사용하여 처리하는 쿼리 중 가장 큰 부하를 차지하는 부분은 인덱스 검색에서 일치하는 키 값의 레코드를 읽는 것이다.
  • 인덱스 검색에서 일치하는 키 값의 데이터를 읽기 위한 디스크 I/O 발생한다.
  • N개의 인덱스를 검색할 때 최악의 경우에는 N번의 디스크 I/O가 발생할 수 있다.
  • 커러링 인덱스란?
    • 인덱스롤 설정한 컬럼만 읽어 쿼리를 모두 처리할 수 있는 인덱스인다.
    • 불필요한 디스크 I/O를 줄여 조회 시간을 단축한다.
    • 프라이머리 키인 id를 함께 조회하는 쿼리는 커버링 인덱스를 활용한다.
      • PK를 복합 인덱스로 설정하지 않았는데 커버링 인덱스를 타는 이유는 InnoDB Secondary 인덱스의 특수한 구조 덕분이다.
        • 리프 노드에는 실제 레코드 주소가 아닌 클러스터드 인덱스가 걸린 PK를 주소로 가진다.
• 인덱스 컨디션 푸시다운
  • Extra 컬럼에는 쿼리의 실행 계획에서 성능에 관련된 중요한 내용이 표시 내부적인 처리 알고리즘에 대해 조금 더 깊은 내용을 포한다.
  • Extra 컬럼의 Using where 란
    • InnoDB 스토리 엔진을 통해 테이블에서 행을 가져온 뒤, MySQL 엔진에서 추가적인 체크 조건을 활용하여 행의 범위를 축소한것이다.
  • Extra 컬럼의 Using Index Condition
    • 인덱스 컨디션 푸시다운으로 인해 표시
    • 인덱스 컨디션 푸시다운(ICP, Index Condition Pushdown)이란 MySQL이 인덱스를 사용하여 테이블에서 행을 검색하는 경우의 최적화를 의미
    • ICP를 활성화하고 인덱스의 컬럼만 사용하여 WHERE 조건의 일부를 평가할 수 있는 경우 MySQL 엔진은 WHERE 조건 부분을 스토리엔진으로 푸시