[MySQL] 실행 계획

  서버 개발자라면 운영하는 서비스의 데이터를 어떻게 잘 보관하고 다룰지 늘상 고민하는 것이 가장 중요하다고 생각합니다. 방대한 양의 데이터 사이에서 데이터를 조회할 때, 효율적인 접근 방식을 탐색하고 개선하는 방법을 고민하면서 이번 기회에 MySQL에서의 실행 계획에 대한 개념을 정리하고자 합니다.

 

---

 

실행 계획

 

  SELECT 쿼리 앞에 EXPLAIN 명령문을 위치하고 실행하면 MySQL은 옵티마이저로부터 쿼리의 실행 계획을 가져와서 출력해 줍니다. 이를 통해서 옵티마이저가 최적의 순서로 테이블을 조인할 수 있는지 검사할 수 있고, 조회가 지체되는 구간을 확인해서 인덱스 구성에 대한 고민과 함께 성능 향상을 위한 개선 방안을 찾을 수 있습니다.

여기서 옵티마이저란, 쿼리를 최적으로 실행하기 위해 각 테이블의 데이터가 어떤 분포로 저장돼 있는지를 참조하고, 데이터를 기반으로 최적의 실행 계획을 수립해 주는 역할을 합니다.

EXPLAIN
SELECT *
FROM booking
WHERE 1=1

위의 쿼리를 실행하면 출력되는 실행 계획 결과 테이블은 아래의 컬럼을 가집니다.

• id: 쿼리 내 `SELECT`의 식별값
• select_type: 실행되는 SELECT문의 종류
  • SIMPLE: Union이나 Sub Query가 없는 단순한 쿼리
  • PRIMARY, UNION, DEPENDENT UNION, …
• table: 참조하는 테이블
• partitions: 파티션의 목록
• type: 쿼리의 테이블 접근 방법 (Best → Worst 순서)
  • system: 테이블이 하나의 행만 가지는 특별한 테이블
  • const
    • PRIMARY KEY 또는 UNiQUE 인덱스가 적용된 열을 상수 값으로 비교하는 방식
    • 한 번만 읽히고, 읽힌 값은 상수로 인식되어 이후 처리에서 매우 빠르게 동작

    SELECT *
    FROM booking b
    WHERE b.pid = 1; # PK 열을 상수 값으로 비교

  • eq_ref
    • JOIN 시 등호(=) 연산자를 사용하여 PK나 UNIQUE 제약이 걸린 컬럼 값을 사용하는 방식
    • 두 테이블 간의 매칭이 확실히 하나로 정해져서 하나의 행만 조회

    SELECT *
    FROM customer c
    INNER JOIN booking b ON c.pid = b.customer_pid;

  • ref
    • JOIN 시 등호(=) 연산자 또는 NULL 비교를 사용하여 인덱스를 기반으로 조인할 때 사용하는 방식 (PK나 UNIQUE 인덱스일 필요는 없다.)
    • 인덱스를 사용하여 해당 값과 일치하는 여러 개의 행을 조회

    SELECT *
    FROM booking b
    INNER JOIN customer c ON b.customer_pid = c.pid # 둘 다 인덱스가 걸려 있다고 가정

  • ref_or_null: ref와 유사, NULL 값을 포함한 행을 검색하는 방식
  • index_merge: 여러 개의 인덱스를 조합하여 쿼리를 수행하는 방식
  • unique_subquery: IN절 내 Sub Query에서 PK가 걸리는 경우
  • index_subquery: IN절 내 Sub Query에서 인덱스가 걸리는 경우
  • range: 인덱스를 범위로 검색하는 방식

    SELECT *
    FROM booking
    WHERE booking_no BETWEEN 10 AND 20;

  • index: 인덱스를 Full Scan 하는 방식
  • ALL: 테이블을 Full Scan 하는 방식
• possible_keys: 쿼리에 사용될 수 있는 인덱스의 목록
• key: 실제로 사용할 예정인 인덱스
  • FORCE INDEX
  • USE INDEX
  • IGNORE INDEX
• key_len
  • 사용하기로 결정한 키의 길이를 의미
  • 복합 인덱스(다중-컬럼 인덱스)를 사용할 때, 실제로 몇 개의 컬럼을 사용하는지 확인할 수 있다.
• ref: 테이블에서 행을 선택하기 위해 key 컬럼 내에 명명된 인덱스를 어떤 컬럼 또는 상수와 비교하는지 보여 준다.
• row: 쿼리를 실행하기 위해 조사해야 하는 열의 숫자
• filtered: 각 실행 단계에서 반환되는 행의 비율
• Extra: 쿼리 해석에 대한 추가적인 정보 (Best → Worst 순서)
  • Using index: 인덱스를 사용하여 데이터 추출
  • Using where: where 조건으로 데이터 추출
  • Using temporary: 임시 테이블을 생성하여 추출
  • Using filesort: ORDER BY 수행 시 인덱스를 사용하지 않고 정렬하는 경우

 

---

 

마치며

 

  기존에는 비즈니스 로직에만 집중해서 데이터베이스에서 데이터를 꺼내 오는 방법만 고민할 뿐, “어떻게” “잘” 꺼내올 것인지에 대한 고민을 미뤄 왔던 것 같습니다. 운영되는 서비스에서 (다행히 관리자 페이지였지만) 조회가 지체되어 사용자가 불편함을 겪는 사례가 생기면서 데이터베이스에 대한 공부의 필요성을 느꼈습니다.

위에 정리한 내용과 같이 EXPLAIN 결과를 통해서 기존의 테이블 관계를 개선할 수 있는 방법을 모색하고, 최적화를 통해 효과적으로 데이터를 조회하는 방법을 알아낼 수 있습니다.

이어서 실행 계획에 대한 분석 결과로 기존의 프레임을 개선하는 과정과 인덱스에 대한 개념 정리를 진행하려고 합니다.

 

📚 Reference

- MySQL 5.0 한글 메뉴얼
- https://monday9pm.com/mysql-%EC%BF%BC%EB%A6%AC-%ED%8A%9C%EB%8B%9D%EC%9D%98-%EC%B2%AB%EA%B1%B8%EC%9D%8C-3ff0e5e1c964