[데이터베이스] 실행 계획과 쿼리 튜닝

목차
1. 실행계획
2. 실행계획에서 무엇을 봐야하는가
3. 쿼리 튜닝하기

 

 

 

실행계획

 실행 계획(Execution Plan)은 DB가 해당 쿼리를 어떻게 실행할지에 대한 전략이다.

 

“이 쿼리를 처리하기 위해 어떤 순서로, 어떤 방식으로 데이터를 읽을 것인가”

 

 

SELECT * FROM orders WHERE user_id = 10;

 

 

 

 orders 라는 테이블에 대해 user_id 값에 인덱스가 선언되어 있다면, 쿼리 실행 시 인덱스를 탔다는 가정하에 매우 빠르게 실행될 것이다. 근데 인덱스를 선언했지만 우리가 의도한 대로 쿼리를 타지 않는 경우가 있을 수 있다. 이런 경우 테이블 전체에대해 full scan을 실행하므로 안타까운 상황이 발생하게 된다.

 

 인덱스를 탈지 타지 않을 지는 눈으로 보고도 판단할 수 있지만, 실제로 판단하는 것은 DB의 몫이므로 우리는 쿼리에 대한 실행 계획이 개발자의 의도대로 돌아가는지를 직접 확인할 필요가 있다.

 

 

 

 

 

 

실행계획에서 무엇을 봐야하는가

쿼리 실행계획은 'explain'을 통해 확인 가능하며, 주의 깊게 봐야할 값들은 type, key, rows, filtered, extra 이다. 각 값들의 의미에 대해 정리해보자.

 

Type (접근 방식)

 

 쿼리 실행 시 접근 방식에 대해 정의한다. 각 값들의 의미는 아래 표를 통해 확인 가능하며, 데이터의 수가 많은 테이블에서 ALL(full scan)방식으로 접근하는 쿼리는 필수적으로 수정해야할 대상이다.

 

Type	의미	성능
ALL	Full Table Scan	🔴 최악
index	인덱스 전체 스캔	🔴
range	범위 조회	🟡
ref	단일 조건 조회	🟢
eq_ref	PK/Unique 조회	🟢 매우 좋음
const	상수 조회	🟢 최고

 

 

 

Key (사용된 인덱스)

 

 쿼리 실행 시 사용하는 인덱스를 나타낸다. NULL 값인 경우 인덱스를 타지 않는 쿼리라는 의미이다.

 

 

Rows (예상 스캔 행 수)

 

 DB가 읽을 것으로 예상하는 데이터 수를 나타낸다. 값이 너무 클 경우 속도가 느려질 수 있다.

 

 

Filtered

 

 조건에 의해서 얼마나 필터링되는지(%)를 나타낸다. 예를들어 값이 10%라면, 100개의 행을 읽고 실제로 뽑아서 사용하는 값은 10개 행 이라는 의미이다. 낮을 수록 비효율적이다.

 

 

Extra (추가 정보)

 

 위에서 언급된 값들 외에 쿼리에 대한 추가 정보를 나타낸다. 주의 깊게 봐야할 값들은 아래와 같다.

# Using filesort

• 정렬을 위해 추가 작업 수행
• 인덱스 정렬 사용 못함

 정의된 인덱스들로 정렬이 안되기 때문에 별도의 정렬 작업이 들어가는 상태를 의미한다. 인덱스를 추가하거나 쿼리를 수정하여 해결할 수 있다.

 

# Using temporary

• 임시 테이블 생성
• GROUP BY, ORDER BY에서 발생

 

# Using index

• 인덱스만으로 조회 (커버링 인덱스)

 정의된 인덱스를 통해 조회한 상태를 의미하며 가장 이상적인 상태이다.

 

 

 

 

 

 

쿼리 튜닝하기

 그럼 예시를 통해 실행 계획을 참고하여 쿼리를 튜닝하는 과정에 대해서 살펴보자. orders라는 테이블에 user_id, created_at 에 대한 인덱스를 걸어놨다고 가정하자.

 

CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    created_at DATETIME,
    amount INT
);

-- 인덱스
CREATE INDEX idx_user_created ON orders(user_id, created_at);

 

 

 

 

 이제 아래와 같은 쿼리에 대해 실행 계획을 확인해보자. 우선 인덱스부터 확인해보자면, Key 값에 우리가 의도한 인덱스를 타고 실행되었음을 알 수 있다.

 

 하지만 rows를 보면 인덱스를 탔음에도 불구하고 5만건이나 되는 데이터를 조회하고 있어 의도한 게 아니라면 느리게 동작할 가능성이 있다.

 

 가장 문제인 것은 Using filesort 이다. 인덱스를 선언했으나 그것을 이용하지 못하고 별도의 정렬이 발생하면서 속도저하가 발생하고 있다.

 

EXPLAIN SELECT *
FROM orders
WHERE user_id > 1000
ORDER BY created_at;

type: range
key: idx_user_created
rows: 50000
Extra: Using where; Using filesort

 

 

 

 

 

 

 우리가 선언한 인덱스는 실제로는 아래와 같이 저장이 되어있다. 하지만 쿼리에서 원한 정렬 값은 create_at 기준이기 때문에 이 인덱스를 활용하여 접근할 수 없으므로 새로 정렬을 하는것이다. 여기서 우리는 이 쿼리의 성능 개선을 위해 몇가지 선택지가 있다.

 

(1001, 2024-01-01)
(1002, 2024-01-02)
(1001, 2024-01-03)
(1002, 2024-01-05)
...

 

 

 

 

현실적인 튜닝 방향

# 정말 이 정렬이 필요한지 확인

WHERE user_id > 1000
ORDER BY created_at

 

 

 이 요구사항이 정말 필요한가? 정확히는 'user_id 값을 조건으로 scan하고 정렬은 create_at을 기준으로 해야하는가?' 이다. 예를들어 아래와 같이 쿼리를 수정하면 기존에 선언된 인덱스를 타기 때문에 filesort가 발생하지 않는다.

 

WHERE user_id > 1000
ORDER BY user_id, created_at

 

 

 만약 정렬 요구사항을 바꿀 수 있다면 (user_id, created_at) 인덱스로 해결이 가능하다.

 

 

# LIMIT가 있다면 성능 손해가 적을 수 있음

 탐색할 쿼리에 상위 N건 조회 조건이 걸려 있다면 정렬 우선 인덱스 INDEX (created_at, user_id)를 고려해볼 수 있다.

 

SELECT *
FROM orders
WHERE user_id > 1000
ORDER BY created_at
LIMIT 20;

 

 

 이유는 created_at 순으로 읽다가 user_id > 1000인 행만 모아서 20건 찾으면 빨리 끝날 수도 있기 때문이다. 즉 전체 정렬이 아니라 상위 N건 조회라면 정렬 우선 인덱스가 실전에서 먹히는 경우가 있다. 일반적으로는 실무에서 데이터가 많은 테이블에 대해 LIMIT를 걸지않고 전체 조회하는 경우가 많지 않으므로 인덱스를 추가하는 것도 현실적인 해결 방법이다.

 

# 결과 집합이 크면 정렬은 감수해야 할 수도 있음

 이것도 현실적인 판단이다. 만약 요구사항을 바꿀 수 없고, user_id > 1000 조건으로 결과가 수십만 건 나온다면, 어느 정도 정렬 비용은 피하기 어렵다. 이때는 개발자 입장에서는 더 개선할 방법이 없다. 나의 경우 실무에서 이런 상황을 만나게 되면 기획팀과 요구사항 수정에 관해 말씀을 나눌 것 같다.