[Index] MySQL 성능 최적화 (+실행 계획)

이번 글에선 MySQL 8.0과 기본 스토리지 엔진인 InnoDB를 사용할 것이고 인덱스를 활용한 쿼리 최적화를 진행할 예정이다. 

 

 

INDEX에 대해 처음 들어보거나 학습하고싶다면 아래 글을 참고 바람

https://dgjinsu.tistory.com/38

[INDEX] 데이터베이스 인덱스 파헤치기

 

 

1. 실행 계획

실행 계획도 정말 여러가지가 있지만 가장 중요하고 많이 나오는 3가지는 다음과 같다.

 

- all: 테이블 전체를 스캔

 

 

full table scan을 의미한다. full table scan을 하는 경우는 두가지가 있다.  

 

1. 인덱스가 없어서 full table scan 하는 경우

2. 인덱스가 있지만 full table scan 하는 경우

 

두번째 경우는 의아할수도 있지만

인덱스가 있지만 데이터 전체의 개수가 얼마되지 않거나

인덱스가 있지만 읽고자 하는 데이터가 25%가 넘어간다면 full table scan이 일어난다. 

 

 

- range: 인덱스를 이용하여 범위 검색을 할 때

 

 

이상적으로 인덱스를 잘 걸었을 때 나타나는 실행 계획이다. 필요한 부분만 데이터를 읽기 때문에 disk io를 줄일 수 있다. 

 

 

- index: 인덱스 전체를 스캔할 때 

 

 

실행 계획 all 과 유사하지만 data를 다 읽는것이 아닌 index를 전부 읽는 것이다.

index는 데이터보단 파일 크기가 작기 때문에 full table scan보단 성능이 우수하고, 당연히 range scan보다는 성능이 좋지 못하다.

 

 

2.1 인덱스 적용 사례

아래 사진과 같은 테이블이 있을 때 어느 컬럼에 인덱스를 걸어야할까??

 

이전 index 게시글에도 나와있었지만 아래 두가지 정도로 기준을 정할 수 있다. 

 

1. 카디널리티가 높은 컬럼에 대해 인덱스를 생성

2. 서비스의 특성상 무엇에 대한 조회가 많이 일어나는지 파악

 

nickname에 대한 조회가 많이 일어나고 nickname 컬럼의 카디널리티 또한 높다고 가정하고 진행하겠다.  

 

 

- 더미데이터 insert

실험에 앞서 dummy data를 넣어줘야한다. 

나같은 경우 mysql workbench를 통해 sql문을 실행시켜 10만건의 데이터를 넣어주었다. 

DELIMITER $$
DROP PROCEDURE IF EXISTS loopInsert$$
 
CREATE PROCEDURE loopInsert()
BEGIN
    DECLARE i INT DEFAULT 1;
        
    WHILE i <= 100000 DO
        INSERT INTO crew(id, nickname, track, age)
          VALUES(i, concat('dgjinsu',i), 'backend', FLOOR(RAND() * 11) + 20);
        SET i = i + 1;
    END WHILE;
END$$
DELIMITER $$


CALL loopInsert;

 

 

- 인덱스를 걸지 않았을 때 (실패 사례)

SELECT * FROM test.crew c where c.nickname < "dgjinsu50000";

 

위와 같이 nickname을 조건절로 두고 쿼리를 날려보았다. 

총 걸린 시간은 0.109sec 소모되었고 실행계획은 ALL로 나타났다

 

 

 

- 인덱스를 걸었을 때 (실패 사례)

create index idx_crew_nickname ON test.crew (nickname);

 

create index문으로 nickname에 인덱스를 걸어주고 다시 똑같은 쿼리를 날려보자.

 

똑같이 ALL이 나왔다. 왜 그럴까?

 

아까 위에서도 말했듯이 인덱스가 있지만 읽고자 하는 데이터가 25%가 넘어간다면 full table scan이 일어난다. 10만건의 데이터를 넣어두었고 5만건의 데이터를 조회하니 당연히 실행 계획이 ALL이 나왔다. 

 

 

 

- 인덱스를 걸지 않았을 때 

이번엔 100만건의 데이터로 늘리고 이번엔 age가 21 미만인 데이터를 조회하였다. 

 

다음과 같이 약 9만건의 데이터가 조회되었고 0.984 sec이 걸렸다. 

실행 계획은 역시 ALL 이다. 

 

- 인덱스를 걸었을 때

CREATE INDEX idx_crew_age ON test.crew (age);

 

이번엔 age에 인덱스를 걸어주고 다시 똑같은 쿼리를 날려보자. 

똑같이 9만건의 데이터가 조회되었고 0.719 sec이 걸렸다. 

 

실행 계획은 range로 바뀌었다.

 

rows는 확인하는 row의 수 이다. 인덱스를 걸기 전엔 997090 행을 탐색했지만 이번엔 181552의 행을 탐색했다. 

 

 

 

 

 

2.2 인덱스 적용 사례

- 복합 인덱스란?

두개 이상의 컬럼을 합쳐서 인덱스를 만드는 것이다.

하나의 컬럼으로 인덱스를 만들었을 때 보다 더 적은 데이터 분포를 보여 탐색할 데이터 수가 줄어듬

결합 인덱스, 다중 컬럼 인덱스, Composite Index라고도 불린다.

 

복합 인덱스를 사용하면 아래와 같이 정렬된다.

age, nickname 두 컬럼을 복합 인덱스로 정의한 예시이다. 

 

복합 인덱스는 조심히 사용해야 한다.

만약 age를 기준으로 쿼리를 날린다면 그냥 인덱스를 걸었을 때와 비슷한 성능을 보일 것이다.

age와 nickname을 사용하여 쿼리를 날린다면 기존보다 훨씬 좋은 성능을 보일 것이다. 

 

하지만 nickname만을 조건으로 쿼리를 날린다면 어떻게 될까??

위 사진에서 볼 수 있듯이 전체적으로 보면 nickname은 정열되어있지 않다. 같은 age에 한에서만 정렬된 형태이다. 

 

따라서 full table scan이 일어난다. 

 

복합 인덱스를 거는 방법은 다음과 같다.

CREATE INDEX idx_crew_age_nickname ON test.crew (age, nickname);

 

 

 

 

2.3 인덱스 적용 사례

- 커버링 인덱스란?

인덱스로 설정한 컬럼만 읽어 쿼리를 모두 처리할 수 있는 인덱스이다. 

불필요한 디스크 I/O를 줄여 조회 시간을 단축할 수 있다. 

 

 인덱스 적용 사례 2.1 에서 인덱스를 걸었지만 20% 이상의 데이터를 조회해야 하는 일이 생겨 full table scan이 일어났었다. 

이를 인덱스만 읽는 방식으로 해결할 수 있다. 

 

현재 nickname을 인덱스로 걸어두었다. 

다음과 같이 조회하면 어떻게 될까??

explain SELECT * FROM test.crew c where c.nickname < "dgjinsu9";

 

dgjinsu1.. 부터 dgjinsu9.. 까지 있기 때문에 90% 가량의 데이터를 조회하는 경우이다. 

실행 계획을 살펴보면 역시 ALL이다. 

 

 

여기서 select 절에 인덱스만 조회하면 다음과 같은 결과가 나타난다. 

explain SELECT c.nickname FROM test.crew c where c.nickname < "dgjinsu9";

 

 

커버링 인덱스를 타게 되면 Extra 컬럼에 Using index가 표시된다. 

 

 

pk 값인 id도 함께 조회한다면 어떤 결과가 일어날까? 

 

마찬가지로 type은 range이고 Extra엔 Using index가 나타난다. 

 

복합 인덱스로 설정하지 않았는데 왜 같은 결과가 나온 것일까??

 

 

이유는 InnoDB의 세컨더리 인덱스의 특수한 구조 덕분이다. 

 

리프 노드에는 실제 레코드 주소가 아닌 클러스터드 인덱스가 걸린 PK를 주소 가진다. 

그렇기 때문에 nickname, id 모두 활용 가능했던 것이다. 

 

 

 

2.4 인덱스 적용 사례

- 인덱션 컨디션 푸시다운

 

아래와 같은 쿼리를 날린다고 가정해보자.

인덱스는 type 기준으로 생성했다. 

 

실행 계획은 다음과 같고 인덱스를 잘 탄걸 볼 수 있다. 하지만 Extra탭을 살펴보면 Using where로 표시된다. 

 

Extra컬럼에는 쿼리의 실행 계획에서 성능에 관련된 중요한 내용이 표시된다. 내부적인 처리 알고리즘에 대해 조금 더 깊은 내용을 포함한다. 

 

Using  where 란,

InnoDB 스토리지 엔진을 통해 테이블에서 행을 가져온 뒤, MySQL 엔진에서 추가적인 체크 조건을 활용하여 행의 범위를 축소한 것이다. 

 

이걸 그림을 살펴보면 다음과 같다.

 

 

인덱스로 걸린 50만개의 데이터를 mysql엔진으로 전달하고 인덱스에 걸리지 않은 created_at 조건에 대해 9천개의 데이터를 필터링한다. mysql엔진의 입장에선 InnoDB 스토리지 엔진에서 불필요한 많은 데이터를 받은 것이다.

 

이를 복합 인덱스를 통해 개선 가능하다. 

 

type, created_at 으로 복합인덱스를 생성하고 다시 쿼리를 날려보면 다음과 같은 실행계획을 확인할 수 있다. 

 

 

Extra 컬럼의 Using Index Condition은 인덱스 컨디션 푸시다운 으로 인해 표시된다. 

인덱스 컨디션 푸시다운(ICP, Index Condition Pushdown)이란, MySQL이 인덱스를 사용하여 테이블에서 행을 검색하는 경우의 최적화를 의미한다. 

 

ICP를 활성화하고 인덱스의 컬럼만 사용하여 where 조건의 일부를 평가할 수 있는 경우 MSQL엔진은 where 조건 부분을 스토리지 엔진으로 푸시한다. 

 

ICP는 최신 버전의 MySQL을 사용할 경우 기본적으로 활성화됨.

 

위 과정을 그림으로 살펴보면 다음과 같다.

 

 

 

 

참고

https://www.youtube.com/watch?v=nvnl9YgnON8