0️⃣ 전제 조건
MySQL과 기본 스토리지 엔진인 8.0 InnoDB
인덱스를 활용한 쿼리 최적화
Table
1️⃣ 인덱스를 왜 쓸까?
1.1 조회 성능 개선
: 디스크 I/O를 줄이는 것이 핵심
1.2 장점
- ORDER BY: 인덱스를 이용해 정렬이 처리되는 경우
- GROUP BY: 인덱스를 이용해 GROUP BY를 하는 경우
1.3 실행 계획
all: 테이블 전체를 스캔할 때 (Full Table Scan)
range: 인덱스를 이용하여 범위 검색을 할 때
- 필요한 데이터만 읽게 된다.
index: 인덱스 전체를 스캔할 때 (Index Full Scan)
- all 보다 성능은 좋으나 인덱스를 전체 스캔하기 때문에 range보다는 성능이 좋지 않다.
2️⃣ 인덱스 적용 사례
2.1 기본 컬럼에 인덱스 적용
- 서비스의 특성상 무엇에 대한 조회가 많이 일어나는지 파악
- 카디널리티가 높은 컬럼에 대해 인덱스를 생성
2.1.1 예시) nickname에 range 적용
2.2 복합 인덱스 적용
2.2.1 복합 인덱스란
- 두 개 이상의 컬럼을 합쳐서 인덱스를 만드는 것
- 하나의 컬럼으로 인덱스를 만들었을 때보다 더 적은 데이터 분포를 보여 탐색할 데이터 수가 줄어듬
- 결합 인덱스, 다중 컬럼 인덱스, Composite Index라고도 불림
2.2.2 예시) 나이, 닉네임 순 정렬
2.2.3 쿼리문에 대한 탐색 범위 줄어듬
2.2.4 아래의 경우에는 Full Table Scan을 하게 왼다.
2.3 커버링 인덱스
인덱스를 사용하여 처리하는 쿼리 중 가장 큰 부하를 차지하는 부분 -> 인덱스 검색에서 일치하는 키 값의 레코드를 읽는 것
2.3.1 그림 살펴보기
N개의 인덱스 검색할 때 최악의 경우 N번의 디스크 I/O 발생
쿼리 최적화의 가장 큰 목적은 디스크 I/O를 줄이는 것
2.3.2 예시
옵티마이저는 전체 데이터의 20 ~ 25% 이상을 조회하는 경우 인덱스를 통해 조회하는 것보다 데이터 파일을 바로 읽는 것(Full Table Scan)이 효율적이라 판단한다.
옵티마이저: 가장 효율적인 방법으로 SQL을 수행할 최적의 처리 경로를 생성해주는 DBMS의 핵심 엔진
[DB] 데이터베이스 옵티마이저(Optimizer)에 대하여
2.3.3 커버링 인덱스 적용 예시
커버링 인덱스
: 인덱스로 설정한 컬럼만 읽어 쿼리를 모두 처리할 수 있는 인덱스
불필요한 디스크 I/O를 줄여 조회 시간 단축
2.3.4 적용
2.3.5 활용
2.3.6 실행 계획
2.3.7 성능
2.3.8 숨겨진 비밀!!
2.4 인덱스 컨디션 푸시다운
2.4.1 예시
2.4.2 살펴보기
2.4.3 실행 계획
2.4.4 Extra 컬럼의 Using where 함정
Extra 컬럼에는 쿼리의 실행 계획에서 성능에 관련된 중요한 내용이 표시
내부적인 처리 알고리즘에 대해 조금 더 깊은 내용을 포함Using where란, InnoDB 스토리지 엔진을 통해 테이블에서 행을 가져온 뒤, MySQL 엔진에서 추가적인 체크 조건을 활용하여 행의 범위를 축소한 것
2.4.5 그림 살펴보기
- InnoDB 스토리지 엔진이 디스크 파일에서 불필요하게 많은 데이터를 전달한다.
- 이 부분을 복합 인덱스(type, created_at)를 통해 개선할 수 있다.
2.4.6 복합 인덱스를 통한 개선
2.4.7 실행 계획
- Extra 컬럼의 Using Index Condition은 인덱스 컨디션 푸시다운으로 인해 표시
- 인덱스 컨디션 푸시다운(ICP, Index Condition Pushdown)이란, MySQL이 인덱스를 사용하여 테이블에서 행을 검색하는 경우의 최적화를 의미
- ICP를 활성화하고 인덱스의 컬럼만 사용하여 WHERE 조건의 일부를 평가할 수 있는 경우
MySQL 엔진은 WHERE 조건 부분을 스토리지 엔진으로 푸시- ICP는 최신 버전의 MySQL을 사용하고 있으면 기본적으로 활성화된 옵션
2.4.8 그림 살펴보기
2.4.9 성능
실행 계획을 판단할 때, type만 보고 실행 계획이 인덱스를 탔다, 안 탔다 판단하는 것보다 Extra 컬럼까지 함께 고려해서 인덱스가 적절히 탔는지를 고려해줘야 한다.
3️⃣ 더 나아가기
- 인덱스 스킵 스캔
- 루스 인덱스 스캔
- 유니크 인덱스
- 전문 검색 인덱스
- 옵티마이저
- ...
📌 출처
📞피드백 너무나 환영