100만개의 데이터, 경우에 따른 실행 계획 확인해보기

csv을 이용해 100만 개의 데이터를 DB에 넣은 상태이다. explain 명령을 통해 각 쿼리의 실행 계획이 어떻게 변화하는지를 살펴보자.

 

 

Explain과 Analyze

Explain

우선, mysql에서 explain 키워드를 붙이면 아래와 같이 실행 계획을 조회할 수 있다. explain은 쿼리를 직접 실행하지는 않는다.

 

 

 

여기서 나오는 각 필드의 개념을 표로 정리해보았다.

항목	내용
id	실행계획의 순서. 이 순서대로 select 문이 실행
select type	SIMPLE : 단순 select문 PRIMARY : 첫번째 쿼리 DERIVED : select문으로 추출된 테이블 ( from 절에서의 서브쿼리 또는 inline view) SUBQUERY : sub query 중 첫번째 select문 UNION : UNION쿼리에서 PRIMARY를 제외한 나머지 select문
table	대상이 되는 테이블 or alias명
type	data access 타입 mysql 서버가 각 테이블의 레코드를 어떤 방식으로 읽었는 지를 나타냄
possible_keys	해당 테이블에서 데이터를 찾기 위해 선택한 인덱스 목록
key	실제로 쿼리 실행에 사용한 인덱스
key_len	쿼리를 처리하기 위해 단일, 다중 컬럼으로 구성된 인덱스의 각 레코드에서 몇 바이트까지 사용했는지
ref	행을 추출하는데 키와 함께 사용된 컬럼이나 상수 값
rows	쿼리 수행에서 예상하는 검색해야 할 행 수
extra	쿼리에 관한 추가적인 정보
filtered	where, on 절에 의해 필터링된 행의 백분율(필터링 되고 남은 레코드의 비율)

 

이것을 참고해, 실행 계획을 살펴볼 수 있다.

 

 

 

Analyze

analyze 명령어는 무엇일까?

이건 실제로 실행 한 뒤의 결과를 통해 실행 계획을 보여주는 것이다.

캡쳐한 사진을 보면, 실제 실행 시간인 actual time이라는게 보인다.

 

 

 

1. PK를 기준으로 탐색할 경우

이제 실제 쿼리문을 날려보며 실행 계획을 살펴보자. 입력한 데이터에서 기본 PK는 id 필드인 것을 전제로 진행한다.

 

아래는 id를 기준으로 탐색한 쿼리이다.

1과 123456으로 쿼리를 탐색하니, type이 range로 fullscan이 발생하지 않는 것을 볼 수 있다

 

이는 클러스터드 인덱스가 테이블의 데이터를 id 순서대로 정렬해 두었기 때문이다. 이 덕분에 id 범위 탐색 시 빠르게 조회가 가능하다.

 

그렇다면 클러스터드 인덱스가 무엇일까? 

 

클러스터드 인덱스

클러스터드 인덱스는 인덱스 자체가 데이터의 물리적 순서대로 정렬되어 저장되는 방식이다. 즉, 테이블의 리프 노드가 PK 순서에 맞춰 정렬되고 연결되어 있다.

 

이렇게 PK를 기준으로 정렬되어 있기 때문에, 쿼리가 id를 기준으로 탐색할 때 효율적으로 데이터를 조회할 수 있다. MySQL에서는 PK 인덱스가 클러스터드 인덱스로 동작한다.

 

 

커버링 인덱스

 그렇다면 이와 같은 인덱스가 id에만 해당하는 걸까? 쿼리에서 필요한 데이터가 모두 인덱스에 포함되어 있다면, 테이블 데이터에 접근하지 않고도 인덱스만으로 결과를 반환할 수 있다. 이를 커버링 인덱스라고 한다.

 

 

커버링 인덱스에 대해 눈으로 직접 더 살펴보자.

 

아래는 id를 통해 행을 불러오는 쿼리문이다.

여기서 Extra를 보면 null인 것을 알 수 있다.

 

 

그럼 id를 통해 id를 가져오는 쿼리문을 날려보면 아래와 같다.

여기서 Extra를 보면 Using Index인 것을 알 수 있다. 

 

여긴 왜 Using Index일까? 바로 커버링 인덱스 때문이다.

 

커버링 인덱스는 테이블의 데이터 파일에 접근하지 않고 인덱스만으로 쿼리 결과를 반환하는 방식이다. 이렇게 쿼리를 처리하면 불필요한 디스크 I/O를 줄이고, 쿼리 성능을 크게 향상시킬 수 있다.

 

그래서 Extra에 Using Index가 나타난 이유는, id 컬럼만 조회하는 쿼리에서 id가 포함된 인덱스를 통해 모든 데이터를 커버할 수 있기 때문이다.

 

 

 

MySQL에서 PK를 기준으로 데이터가 어떻게 정렬되어있는지, 커버링 인덱스는 무엇인지 알아볼 수 있었다

 

 

 

 

2. FK를 기준으로 탐색할 경우

이제 FK를 초점으로 탐색을 진행해보자. 현재 seller는 PK, price는 외래키인 상태이다.

 

그리고 아래는 각각 PK와 FK를 기준으로 쿼리를 날린 것이다.

사진을 보면 FK로 탐색한 시간이 1.35sec로, PK에 비해 실행 시간이 오래 걸리는 것을 볼 수 있다.

 

 

이것을 실행 계획을 통해 살펴보면,

type 필드가 ALL로 풀스캔을 하는 것을 볼 수 있다.

 

 

그럼 FK의 탐색 시간을 빠르게 하기 위해서 어떻게 할 수 있을까? 인덱스를 생성해 스캔을 해보면 가능하다!

 

3. 인덱스를 생성해서 조회 속도 향상해보기

단일 인덱스

아래의 명령어를 통해 단일 인덱스를 생성해서 스캔할 수 있다.

CREATE INDEX 인덱스명 ON 테이블명 (컬럼명)

 

taerae라는 인덱스 명으로 인덱스를 생성한 것을 볼 수 있다.

 

이제 다시 실행 계획을 보면 key가 지정한 걸로 변경된 것을 볼 수 있다.

 

 

 

다시 실행해보면

1.35sec에서 0.00sec로 실행 속도가 매우 빨라진 것을 알 수 있다.

 

 

추가로 위에서 살펴본 커버링 인덱스를 확인해보자.

sum(price)와 avg(price)로 조회하면 매우 빠른 것을 볼 수 있다.

이는 인덱스에 포함된 price 정보만으로 연산이 가능하기 때문이다. 불필요한 데이터 파일 접근을 줄이면서 쿼리를 처리할 수 있다.

 

 

인덱스 처리를 했는데 FullScan을 하는 경우

모든 쿼리가 인덱스만으로 빠르게 처리되는 것은 아니다.

여기서 보면 price를 했는데도 index가 아닌 풀스캔을 할 것을 볼 수 있다. (type 필드가 ALL)

 

여기서도 400의 범위를 넘어가니 풀스캔을 해버리는 것을 볼 수 있다.

 

 

이는 많은 데이터가 인덱스 범위를 벗어나기 때문이다. 데이터가 많은 상황에서는 세컨더리 인덱스가 전체 테이블을 Full Scan과 유사하게 수행해, 테이블의 개수에 비례하여 여러 번의 I/O를 발생시킨다.

 

아주 쉬운 예를 생각해보자. 예를 들어, 전체 데이터의 80%를 조회하는 경우, 인덱스를 사용해 각 항목을 개별적으로 찾는 것보다 그냥 Full Scan으로 한 번에 읽는 게 더 효율적일 것이다.

 

 

 

또 세컨더리 인덱스의 작동 방식을 생각해볼 수 있다.

 

 세컨더리 인덱스를 사용할 때, 인덱스의 정보를 통해 필요한 데이터의 PK를 찾고, 그 PK로 다시 테이블의 본 데이터를 찾아와야한다. 하지만 이렇게 많은 데이터를 조회하면 PK를 찾고 또 본 데이터를 읽어오는 과정이 너무 많아지면서 Full Scan처럼 느려질 수 있다.

 

 

 

 이런 경우, 인덱스 조회 대신 Full Scan이 더 효율적일 수 있다. 일반적으로 조회 대상 데이터가 전체의 15% 이상이라면 Full Scan이 오히려 더 빠른 선택이 된다.

세컨더리 인덱스와 PK 인덱스 차이: 세컨더리 인덱스는 추가 비용이 발생할 수 있는 반면, PK는 기본적으로 클러스터드 인덱스로 정렬되어 있어 거의 항상 더 빠른 조회를 보장한다.

 

커버링 인덱스의 디스크 I/O 발생: 일반적으로 커버링 인덱스는 테이블의 물리적 데이터 접근을 줄여주지만, 특정 상황에서는 디스크 I/O가 생길 수 있다.
 보통 자주 쓰는 인덱스는 메모리에 저장되므로, 디스크 접근 없이 메모리에서 빠르게 처리할 수 있다. 하지만 인덱스가 커지면 모든 인덱스를 메모리에 올릴 수 없게 돼서 디스크에 저장된 인덱스를 참조하게 되고, 이때 디스크 I/O가 발생할 수 있다.
 커버링 인덱스가 테이블 데이터의 많은 부분을 포함하게 되면, 커버링 인덱스조차 메모리에 모두 올라가지 못할 수 있고, 이때 인덱스 데이터를 디스크에서 읽어 와야하기 때문에 디스크 I/O가 발생하면서 커버링 인덱스가 느려질 수 있다.
 즉, 커버링 인덱스라고 해도 인덱스 크기가 너무 커지면 메모리 한계를 넘게 되고, 디스크에 접근하면서 속도가 느려질 수 있는 것!

 

 

 

 

복합 인덱스

nickname이 유니크 키라서 인덱싱 되어 있는 상황이다.

 

 

만약 nickname과 money로도 인덱스처리하려면, 아래와 같은 명령어로 복합키를 만들어줄 수 있다.

CREATE INDEX 인덱스명 ON 테이블명 (컬럼명1, 컬럼명2...)

복합 키를 통해 만들어진 필드가 인덱스를 잘 타는 것을 볼 수 있다.

 

 

 

그렇다면 만약 nickname과 money를 바꿔서 복합키를  설정하면 어떻게 될까?

 

순서가 다르기 때문에. 당연히 달라진다. 이때 고려해야할 것이 카디널리티이다.

 

카디널리티(Cardinality)와 인덱스 순서

 카디널리티는 해당 컬럼에 저장된 고유한 값의 수를 의미한다. 카디널리티가 높다는 것은 데이터가 더 다양하게 분포되어 있다는 뜻이고, 반대로 낮다면 데이터가 비슷하게 몰려 있다는 의미이다.

 

 인덱스 생성 시 카디널리티가 높은 컬럼을 우선으로 두면, 인덱스를 검색할 때 검색 범위를 좁히는 데 효과적이다.

 

 예를 들어, nickname이 더 고유한 값이 많고 money는 그보다 고유 값이 적다면, nickname, money 순으로 인덱스를 생성하는 게 더 효율적일 수 있다. 높은 카디널리티의 컬럼을 앞에 두면 검색 범위를 먼저 좁히기 때문에, 데이터 탐색 속도가 더 빨라지는 것이다.

 

 그래서 일반적으로 카디널리티가 높은 컬럼을 먼저 두고, 그 다음으로 낮은 컬럼 순으로 인덱스를 생성하는 방식이 추천된다.

 

 

 

Explain plan을 시각적으로 보는법

지금까지 명령어를 통해 실행 계획을 살펴보았지만, Intellij와 MySQL에서 시각화로 보여주는 기능을 제공한다.

Intellij

인텔리제이의 경우 오른쪽 마우스를 통해 Explain Plan 항목으로 들어가면 아래와 같은 화면을 확인할 수 있다.

 

mysql

 

MySQL에서도 Visual Explain을 클릭하면 유사한 화면을 볼 수 있다.