[DB 성능 최적화] 실행 계획(Explain)

이 글은 인프런의 MySQL 성능 최적화 입문/실전 (SQL 튜닝편) (박재성) 강의를 듣고 개인적으로 정리하는 글임을 알립니다.

---

실행 계획(Explain) 개념

옵티마이저가 SQL문을 어떤 방식으로 어떻게 처리할 지를 계획한 걸 의미한다.

이 실행 계획을 보고 비효율적으로 처리하는 방식이 있는 지 점검하고, 비효율적인 부분이 있다면 더 효율적인 방법으로 SQL문을 실행하게끔 튜닝을 하는 게 목표다.

 

실행 계획(Explain)은 데이터베이스가 SQL 쿼리를 실행할 때 어떤 경로로 데이터에 접근하고 처리하는지를 보여주는 일종의 “설계도”라고 할 수 있다.

즉, 쿼리가 어떻게 실행될지에 대한 세부적인 계획을 제공한다.

 

쉽게 설명하자면

 

데이터베이스에 어떤 SQL 쿼리를 보내면, 데이터베이스는 그 쿼리를 실행할 때 최적의 경로를 선택해야 한다. 이 경로는 여러 가지 요소에 따라 달라진다. 예를 들어, 테이블이 크다면 인덱스를 사용할지, 아니면 테이블 전체를 스캔할지 등을 결정해야 한다.

실행 계획(Explain)을 사용하면 데이터베이스가 쿼리를 처리하는 과정을 미리 알 수 있고, 이를 통해 성능을 최적화할 수 있다.

예를 들어, 실행 계획을 보면 쿼리가 테이블 전체를 스캔하고 있는지, 아니면 인덱스를 잘 활용하고 있는지를 확인할 수 있다.

 

실행 계획 조회하기

EXPLAIN SELECT * FROM users
WHERE age = 23;

 

• id : 실행 순서
• table : 조회한 테이블 명
• type : 테이블의 데이터를 어떤 방식으로 조회하는지
• possible keys : 사용할 수 있는 인덱스 목록을 출력 ⭐️
• key : 데이터 조회할 때 실제로 사용한 인덱스 값 ⭐️
• ref : 테이블 조인 상황에서 어떤 값을 기준으로 데이터를 조회했는지
• rows : SQL문 수행을 위해 접근하는 데이터의 모든 행의 수 (= 데이터 액세스 수) ⭐️⭐️⭐️
  → 이 값을 줄이는 게 SQL 튜닝의 핵심
• filtered : 필터 조건에 따라 어느 정도의 비율로 데이터를 제거했는 지 의미
  → filtered의 값이 30이라면 100개의 데이터를 불러온 뒤 30개의 데이터만 실제로 응답하는데 사용했음을 의미
  → filtered 비율이 낮을 수록 쓸데없는 데이터를 많이 불러온 것.
• Extra : 부가적인 정보를 제공 ⭐️
  → ex. Using where, Using index

rows, filtered의 값은 정확한 수치가 아닌 추정값이기 때문에 오차가 있을 수 있다.

 

실행 계획에 대한 자세한 정보 조회

EXPLAIN ANALYZE SELECT * FROM users
WHERE age = 23;

• Table scan on users : users 테이블을 풀 스캔했다.
  -rows : 접근한 데이터의 행의 수
  -actual time=0.0437..0.0502
    ->0.0437 (앞에 있는 숫자) : 첫 번째 데이터에 접근하기까지의 시간
    ->0.0502 (뒤에 있는 숫자) : 마지막 데이터까지 접근한 시간
• Filter: (users.age = 23) : 필터링을 통해 데이터를 추출했다. (필터링을 할 때의 조건은 users.age = 23)

 

위 작업을 한 번에 이어서 해석해보면 다음과 같다.

• users 테이블의 모든 데이터(7개)에 접근했다.
• 그리고 그 데이터 중 age = 23의 조건을 만족하는 데이터만 필터링해서 조회해왔다. 

 

실행 계획에서 Type 의미 분석

실행 계획(EXPLAIN)을 조회했을 때 나오는 결과값 중 하나인 type은 성능 최적화에 있어서 이 값의 의미를 파악하는 게 굉장히 중요하다. 

 

ALL(Full Table Scan)

풀 테이블 스캔(Full Table Scan)이란 인덱스를 활용하지 않고 테이블을 처음부터 끝까지 전부 다 뒤져서 데이터를 찾는 방식이다.

처음부터 끝까지 전부 다 뒤져서 필요한 데이터를 찾는 방식이다보니 비효율적이다.

출처 : 업무에 바로 쓰는 SQL 튜닝 (책)

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    age INT
);

INSERT INTO users (name, age) VALUES 
('Alice', 30),
('Bob', 23),
('Charlie', 35);

EXPLAIN SELECT * FROM users WHERE age = 23; # type : ALL

users 테이블의 데이터는 age를 기준으로 정렬되어 있지 않고 id를 기준으로 정렬되어 있다.

그래서 age = 23의 값을 가진 데이터를 찾으려면 테이블의 처음부터 끝까지 다 뒤져봐야 한다.

그래서 실행 계획의 type이 ALL로 나온 것이다. 

 

Index(Full Index Scan)

풀 인덱스 스캔(Full Index Scan)이란 인덱스 테이블을 처음부터 끝까지 다 뒤져서 데이터를 찾는 방식이다.

인덱스의 테이블은 실제 테이블보다 크기가 작기 때문에, 풀 테이블 스캔(Full Table Scan)보다 효율적이다.

하지만 인덱스 테이블 전체를 읽어야 하기 때문에 아주 효율적이라고 볼 수는 없다.

출처 : 업무에 바로 쓰는 SQL 튜닝 (책)

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    age INT
);

CREATE INDEX idx_name ON users (name);

EXPLAIN SELECT * FROM users 
ORDER BY name 
LIMIT 10;

type이 index인걸로 봐서 풀 인덱스 스캔을 한것을 확인할 수 있다. 즉, 인덱스를 처음부터 끝까지 다 읽어서 필요한 데이터를 뽑아냈다.

 

Const

조회하고자 하는 1건의 데이터를 헤매지 않고 단번에 찾아올 수 있을 때 const가 출력된다. 그러다보니 고유 인덱스 또는 기본 키를 사용해서 1건의 데이터만 조회한 경우에 const가 출력된다.

이 방식은 아주 효율적인 방식이다.

 

• 인덱스가 없다면 특정 값을 일일이 다 뒤져야 한다. 그래서 1건의 데이터를 바로 찾을 수 없다.
• 인덱스가 있는데 고유하지 않다면(NOT UNIQUE) 원하는 1건의 데이터를 찾았다고 하더라도, 나머지 데이터에 같은 값이 있을 지도 모르므로 다른 데이터들도 체크해봐야 한다.
• 고유하다면(UNIQUE) 1건의 데이터를 찾는 순간, 나머지 데이터는 아예 볼 필요가 없어진다. 왜냐하면 찾고자 하는 데이터가 유일한 데이터이기 때문이다. 
  → UNIQUE 제약조건이 있는 컬럼과 기본 키는 전부 UNIQUE한 특성을 가지고 있다.

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    account VARCHAR(100) UNIQUE
);

실행 계획 조회하기
EXPLAIN SELECT * FROM users WHERE id = 3;
EXPLAIN SELECT * FROM users WHERE account = 'user3@example.com';

UNIQUE 속성을 가진 컬럼은 인덱스가 자동으로 생성된다. 

 

Range(Index Range Scan)

인덱스 레인지 스캔(Index Range Scan)은 인덱스를 활용해 범위 형태의 데이터를 조회한 경우를 의미한다.

범위 형태란 BETWEEN, 부등호(<, >, ≤, ≥), IN, LIKE를 활용한 데이터 조회를 뜻한다.

이 방식은 인덱스를 활용하기 때문에 효율적인 방식이다.

하지만 인덱스를 사용하더라도 데이터를 조회하는 범위가 클 경우 성능 저하의 원인이 되기도 한다.

출처 : 업무에 바로 쓰는 SQL 튜닝 (책)

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    age INT
);

CREATE INDEX idx_age ON users(age);

EXPLAIN SELECT * FROM users
WHERE age BETWEEN 10 and 20;

EXPLAIN SELECT * FROM users
WHERE age IN (10, 20, 30);

EXPLAIN SELECT * FROM users
WHERE age < 20;

 

Ref

고유하지 않은 인덱스(비고유 인덱스)를 사용한 경우 (= UNIQUE가 아닌 컬럼의 인덱스를 사용한 경우) type에 ref가 출력된다. 

출처 : 업무에 바로 쓰는 SQL 튜닝 (책)

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100)
);

CREATE INDEX idx_name ON users(name);

EXPLAIN SELECT * FROM users WHERE name = '박재성';

 

이외에 eq_ref, index_merge, ref_or_null 등 다양한 타입들이 존재한다.