MySQL - SQL 튜닝의 실행 계획 파헤치기(1)

최건우·2023년 9월 5일
DatabaseRDBMSsql
0

데이터베이스/SQL

목록 보기
7/13

SQL 문 앞에 EXPLAIN, DESCRIBE, DESC 키워드를 입력하고 실행하면 옵티마이저가 만든 실행 계획이 출력된다.

EXPLAIN
SELECT 
	* 
FROM 
	사원 
WHERE 
	사원번호 BETWEEN 100001 AND 200000
;

실행계획의 id, select_type, table, type, key 등은 SQL 튜닝의 실마리를 제공한다. 각 정보가 무엇을 의미하는지 살펴보자.

id

id는 SQL 문이 수행되는 차례를 순서대로 표기한 숫자이다. 서로 다른 SQL 문이라도 조인할 때는 똑같은 ID가 표시된다.

다음 실행계획 결과에서는 총 3개의 행이 출력되는데, 처음 두 행은 조인이 발생한 것이라고 해석할 수 있다. 또한 세 번째 행의 ID가 3이므로, 조인이 이루어진 뒤에 세 번째 행이 수행된 것이라고 짐작할 수 있다.

EXPLAIN
SELECT
    사원.사원번호,
    사원.이름,
    사원.성,
    급여.연봉,
    (SELECT MAX(부서번호) FROM 부서사원_매핑 AS 매핑 WHERE 매핑.사원번호 = 사원.사원번호) AS 카운트
FROM
    사원, 급여
WHERE
    사원.사원번호 = 10001
  AND
    사원.사원번호 = 급여.사원번호
;

select_type

select_type은 SQL 문을 구성하는 SELECT 문의 유형을 출력하는 항목이다. 단순히 FROM 절에 위치한 것인지, 서브쿼리인지, UNION 절로 묶인 SELECT 문인지 등의 정보를 제공한다.

자주 출력되는 select_type의 종류를 알아보자.

SIMPLE

union이나 내부 쿼리가 없는 SELECT 문을 의미한다. 말 그대로 SELECT 구문으로만 작성된 경우를 가리킨다.

EXPLAIN
SELECT * FROM 사원 WHERE 사원번호 = 100000;

PRIMARY

서브쿼리가 포함된 SQL 문이 있을 때 첫 번째 SELECT 문에 해당하는 구문에 표시되는 유형이다. 즉, 서브쿼리를 감싸는 외부쿼리이거나, UNION이 포함된 SQL 문에서 첫 번째 SELECT 키워드가 작성된 구문에 표시된다.

  • 서브쿼리를 감싸는 외부쿼리
    EXPLAIN
SELECT
    사원.사원번호,
    사원.이름,
    사원.성,
    (SELECT MAX(부서번호) FROM 부서사원_매핑 AS 매핑 WHERE 매핑.사원번호 = 사원.사원번호) AS 카운트
FROM
    사원
WHERE
    사원.사원번호 = 10001
;

  • UNION이 포함된 SQL 문에서 처음 SELECT 구문이 작성된 쿼리. 이 쿼리에 먼저 접근한다는 의미임.

    EXPLAIN
SELECT
    사원1.사원번호,
    사원1.이름,
    사원1.성
FROM
    사원 AS 사원1
WHERE 사원1.사원번호 = 10001

UNION ALL

SELECT
    사원2.사원번호,
    사원2.이름,
    사원2.성
FROM
    사원 AS 사원2
WHERE 사원2.사원번호 = 10002
;

SUBQUERY

독립적으로 수행되는 서브쿼리를 의미한다. SELECT 절의 스칼라 서브쿼리와 WHERE 절의 중첩 서브쿼리일 경우에 해당한다.

EXPLAIN
SELECT
    (SELECT
        COUNT(*)
    FROM
        부서사원_매핑 AS 매핑
    ) AS 카운트,
    (SELECT
         MAX(연봉)
     FROM
         급여
    ) AS 급여
;

DERIVED

FROM 절에 작성된 서브쿼리라는 의미이다. 즉, FROM 절의 임시 테이블은 인라인 뷰를 말한다.

EXPLAIN
SELECT
    사원.사원번호,
    급여.연봉
FROM
    사원,
    (SELECT 사원번호, MAX(연봉) AS 연봉 FROM 급여 WHERE 사원번호 BETWEEN 10001 AND 20000 GROUP BY 사원번호) AS 급여
WHERE
    사원.사원번호 = 급여.사원번호
;

UNION

select_type의 값이 UNION인 것은, UNION 및 UNION ALL 구문으로 합쳐진 SELECT 문에서 첫 번째 SELECT 구문이 아닌 그 다음에 오는 SELECT 구문에 해당함을 나타낸다. 이때 UNION 구문의 첫 번째 SELECT 절은 PRIMARY 유형으로 출력된다.

EXPLAIN
SELECT
    'M' AS 성별,
    MAX(입사일자) AS 입사일자
FROM
    사원 AS 사원1
WHERE 성별 = 'M'

UNION ALL

SELECT
    'F' AS 성별,
    MIN(입사일자) AS 입사일자
FROM
    사원 AS 사원2
WHERE 성별 = 'F'
;

UNION RESULT

UNION RESULT는 UNION ALL이 아닌 UNION 구문으로 SELECT 절을 결합했을 때 출력된다.

UNION은 출력 결과에 중복이 없는 유일한 속성을 가지므로 각 SELECT 절에서 데이터를 가져와 정렬하여 중복 체크하는 과정을 거친다. 따라서, UNION RESULT는 별도의 메모리 또는 디스크에 임시 테이블을 만들어 중복을 제거하겠다는 의미로 해석할 수 있다.

한편 UNION 구문으로 결합되기 전의 각 SELECT 문이 중복되지 않는 결과가 보장될 때는 UNION 구문보다는 UNION ALL 구문으로 변경하는 SQL 튜닝을 수행한다.

EXPLAIN
SELECT
    사원_통합.*
FROM(
    SELECT
        MAX(입사일자) AS 입사일자
    FROM
        사원 AS 사원1
    WHERE
        성별 = 'M'

    UNION

    SELECT
        MIN(입사일자) AS 입사일자
    FROM
        사원 AS 사원2
    WHERE
        성별 = 'M'
    ) AS 사원_통합
;

DEPENDENT SUBQUERY, DEPENDENT UNION

DEPENDENT SUBQUERY

UNION 또는 UNION ALL을 사용하는 서브쿼리가 메인 테이블의 영향을 받는 경우로, UNION으로 연결된 단위 쿼리들 중에서 처음으로 작성한 단위 쿼리에 해당하는 경우이다.
즉, UNION으로 연결되는 첫 번째 단위 쿼리가 독립적으로 수행하지 못하고 메인 테이블로부터 값을 하나씩 공급받는 구조이므로 성능적으로 불리하여 SQL 문이 튜닝 대상이 된다.

DEPENDENT UNION

UNION 또는 UNION ALL을 사용하는 서브쿼리가 메인 테이블의 영향을 받는 경우로, UNION으로 연결된 단위 쿼리들 중에서 첫 번째 단위 쿼리를 제외하고 두 번째 쿼리에 해당하는 경우이다.
즉, UNION으로 연결되는 두 번째 이후의 단위 쿼리가 독립적으로 수행하지 못하고 메인 테이블로부터 값을 하나씩 공급받는 구조이므로 성능적으로 불리하여 SQL 문이 튜닝 대상이 된다.

아래의 예제는 두 가지 유형을 한 번에 보여주고 있다.

EXPLAIN
SELECT
    관리자.부서번호,
    (SELECT
        사원1.이름
     FROM
         사원 AS 사원1
     WHERE
        성별 = 'F'
       AND
         사원1.사원번호 = 관리자.사원번호   # <- 메인 테이블로부터 값을 하나씩 공급받는 구조(DEPENDENT SUBQUERY)

     UNION ALL

     SELECT
         사원2.이름
     FROM
         사원 AS 사원2
     WHERE
             성별 = 'M'
       AND
             사원2.사원번호 = 관리자.사원번호   # <- 메인 테이블로부터 값을 하나씩 공급받는 구조(DEPENDENT UNION)
     ) AS 이름
FROM
    부서관리자 AS 관리자
;

UNCACHEABLE SUBQUERY

메모리에 상주하여 재활용되어야 할 서브쿼리가 재사용되지 못할 때 출력하는 유형이다. 아래와 같은 경우가 이에 해당한다.

  1. 해당 서브쿼리 안에 사용자 저의 함수나 사용자 변수가 표함됨.
  2. RAND(), UUID() 함수 등 매번 조회 시마다 결과가 달라지는 경우.

자주 호출되는 SQL 문이라면 서브쿼리 결과를 메모리에 상주시키는 방향으로 SQL 튜닝을 시도해볼 수 있다.

EXPLAIN
SELECT
    *
FROM
    사원
WHERE
    사원번호 = (SELECT ROUND(RAND()*1000000))  # 매번 출력값이 달라지는 RAND() 함수 때문에 서브쿼리가 메모리에 캐시되지 못함.
;

* 2023.09.04 현재 MySQL 8.0.33 버전을 기준으로, 실제 실행 결과가 책에 명시된 것과 상이하여 쿼리만 게시합니다.

MATERIALIZED

IN 구문에 연결된 서브쿼리로 임시 테이블을 생성한 뒤, 조인이나 가공 작업을 수행할 때 출력되는 유형이다.
아래 예시에서는 IN 이후의 서브쿼리가 임시 테이블을 생성하고, 이후 사원 테이블과 조인을 수행한다.

EXPLAIN
SELECT
    *
FROM
    사원
WHERE
    사원번호 IN (
        SELECT
            사원번호
        FROM
            급여
        WHERE 시작일자 > '2020-01-01'
    )
;

* 2023.09.04 현재 MySQL 8.0.33 버전을 기준으로, 실제 실행 결과가 책에 명시된 것과 상이하여 쿼리만 게시합니다.

table

테이블명을 표시하는 항목이다. 테이블명이나 테이블 별칭(alias)를 출력한다. 서브쿼리나 임시 테이블을 만들어서 별도의 작업을 수행할 때는 <subquery#>나 <derived#>라고 출력된다.

partitions

데이터가 저장된 논리적인 영역을 표시하는 항목이다. 사전에 정의한 전체 파티션 중 특정 파티션에 선택적으로 접근하는 것이 SQL 성능 측면에서 유리하며, 만약 너무 많은 영역의 파티션에 접근하고 있다면 파티션 정의를 튜닝해봐야 한다.

파티션이 없다면 전체 저장공간을 검색해야 하지만, 파티션이 있다면 데이터가 해당하는 범위의 파티션만 검색하므로 검색 성능이 향상된다.

type

테이블의 데이터를 어떻게 찾을지에 대한 정보를 제공하는 항목이다. 테이블을 처음부터 끝까지 전부 확인할지, 아니면 인덱스를 통해 바로 데이터를 찾아갈지 등을 해석할 수 있다.

system

테이블에 데이터가 없거나 한 개만 있는 경우로, 성능상 최상의 type이라고 할 수 있다.

const

조회되는 데이터가 단 1건일 때 출력되는 유형으로, 성능상 매우 유리한 방식이다. 고유 인덱스나 기본 키를 사용하여 단 1건의 데이터에만 접근하면 되므로 속도나 리소스 사용 측면에서 지향해야 할 타입이다.

EXPLAIN
SELECT * FROM 사원 WHERE 사원번호 = 10001;

eq_ref

조인이 수행될 때 드리븐 테이블의 데이터에 접근하며 고유 인덱스 또는 기본 키로 단 1건의 데이터를 조회하는 방식이다.
드라이빙 테이블과의 조인 키가 드리븐 테이블에 유일하므로 조인이 수행될 때 성능상 가장 유리한 경우이다.

EXPLAIN
SELECT
    매핑.사원번호, 부서.부서번호, 부서.부서명
FROM
    부서사원_매핑 AS 매핑,
    부서
WHERE
    부서.부서번호 = 매핑.부서번호
AND
    매핑.사원번호 BETWEEN 10001 AND 10010;

ref

조인을 수행할 때 드리븐 테이블의 데이터 접근 범위가 2개 이상일 경우를 의미한다. 즉, 드라이빙 테이블과 드리븐 테이블이 조인을 수행하면 1:다 관계가 되므로, 드라이빙 테이블의 1개 값이 드리븐 테이블에서는 2개 이상의 데이터로 존재한다. 드리븐 테이블의 데이터양이 많다면 접근해야 할 데이터 범위가 넓어지므로, 성능 저하의 원인이 되는지 확인해야 한다.

한편으로는 =, <, > 등의 연산자를 사용해 인덱스로 생성된 열을 비교할 때도 출력된다.

ref_or_null

ref 유형과 유사하지만 IS NULL구문에 대해 인덱스를 활용하도록 최적화된 방식이다. 이때 NULL은 가장 앞쪽에 정렬된다. 테이블에서 검색할 NULL 데이터양이 적다면 ref_of_null 방식을 활용했을 때 효율적인 SQL 문이 될 것이나, 검색할 NULL 데이터 양이 많다면 SQL 튜닝의 대상이 된다.

EXPLAIN
SELECT
    *
FROM
    사원출입기록
WHERE
    출입문 IS NULL  # 출입문 열에 인덱스가 생성되어 있다고 가정한다.
OR
    출입문 = 'A'
;

range

테이블 내의 연속된 데이터 범위를 조회하는 유형으로, =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN 연산을 통해 범위 스캔을 수행하는 방식이다. 주어진 데이터 범위가 넓을수록 스캔할 범위가 넓어지기 때문에 성능 저하의 요인이 될 수 있다. 따라서 SQL 튜닝 검토 대상이 된다.

EXPLAIN
SELECT * FROM 사원 WHERE 사원번호 BETWEEN 10001 AND 100000;

fulltext

텍스트 검색을 빠르게 처리하기 위해 전문 인덱스(full text index)를 사용하여 데이터에 접근하는 방식이다.

index_merge

결합된 인덱스들이 동시에 사용되는 유형이다. 특정 테이블에 생성된 두 개 이상의 인덱스가 병합되어 동시에 적용된다.
이때 full text index는 제외된다.

EXPLAIN
SELECT
	*
FROM
	사원
WHERE
	사원번호 BETWEEN 10001 AND 100000
AND
	입사일자 = '1985-11-21'
;

입사일자 인덱스와 PRIMARY KEY인 사원번호를 모두 통합해서 사용하리라 예측할 수 있다.

index

index 유형은 인덱스 풀 스캔을 의미한다. 인덱스 블록을 처음부터 끝까지 훑는 방식을 말한다. 테이블 풀 스캔과 유사하나, 데이터 스캔 대상이 인덱스이며 인덱스는 보통 테이블보다 크기가 작으므로 테이블 풀 스캔 방식보다는 빠를 가능성이 높다.

EXPLAIN
SELECT
    사원번호
FROM
    직급
WHERE
    직급명 = 'Manager'
;

ALL

테이블을 처음부터 끝까지 읽는 테이블 풀 스캔 방식에 해당하는 유형이다. 활용할 수 있는 인덱스가 없거나, 옵티마이저가 인덱스를 활용하는 게 오히려 비효율적이라고 판단했을 때 선택된다. 인덱스를 새로 추가하거나 기존 인덱스를 변경하여 인덱스를 활용하는 방식으로 SQL 튜닝을 할 수 있으나, 전체 테이블 중 10~20% 이상의 분량을 데이터를 조회할 때는 ALL 유형이 오히려 성능상 유리할 수 있다.

EXPLAIN
SELECT * FROM 사원;

possible_keys

옵티마이저가 SQL문을 최적화하고자 사용할 수 있는 인덱스 목록을 출력한다. 다만 실제 사용한 인덱스가 아닌, 사용할 수 있는 후보군의 기본 키와 인덱스 목록만 보여주므로 SQL 튜닝의 효용성은 없다.

key

옵티마이저가 SQL 문을 최적화하고자 사용한 PK 또는 인덱스명을 의미한다. 어느 인덱스로 데이터를 검색했는지 확인할 수 있으므로, 비효율적인 인덱스를 사용했거나 인덱스 자체를 사용하지 않았다면 SQL 튜닝의 대상이 된다.

# PRIMARY KEY를 활용하는 예

EXPLAIN
SELECT
    사원번호
FROM
    직급
WHERE
    직급명 = 'Manager'
;

# PK나 인덱스를 전혀 사용하지 않는 예
EXPLAIN
SELECT * FROM 사원;

key_len

사용한 인덱스의 바이트(bytes) 수를 의미한다. 인덱스 전체를 사용했는지, 일부 인덱스만 사용했는지 알 수 있다.

# 직급 테이블은 사원번호(INT), 직급명(VARCHAR(50)), 시작일자(DATE) 총 3개의 컬럼이 각각 인덱스가 적용되었다고 가정한다.

EXPLAIN
SELECT
    사원번호
FROM
    직급
WHERE
    직급명 = 'Manager'
;

MySQL에서는 UTF-8 캐릭터셋 기준으로 INT 데이터 유형은 단위당 4bytes, VARCHAR 데이터 유형은 단위당 3bytes이다.
사원번호는 INT 이므로 4bytes, 직급명은 VARCHAR(50) 이므로 (50+1) * 3bytes = 155bytes에 해당한다. 따라서 총 3개의 인덱스 중 사원번호와 직급명만 사용하여 key_len이 159bytes로 출력되었음을 알 수 있다.

ref

테이블 조인을 수행할 때 어떤 조건으로 해당 테이블에 액세스되었는지를 알려주는 정보이다(reference).

EXPLAIN
SELECT
    사원.사원번호,
    직급.직급명
FROM
    사원, 직급
WHERE
    사원.사원번호 = 직급.사원번호
AND
    사원.사원번호 BETWEEN 10001 AND 100000
;

사원 테이블과 직급 테이블의 조인을 수행했으며, 드리븐 테이블인 직급 테이블의 데이터에 접근하면 사원번호로 데이터를 검색한다는 것을 확인할 수 있다.

rows

SQL 문을 수행하고자 접근하는 데이터의 모든 행 수를 나타내는 예측 항목이다. 수시로 변동되는 통계정보를 참고하여 산출하는 값이므로 수치가 정확하진 않다. 최종 출력된 행 수가 아니라는 점에 유의해야 한다.

다만, SQL 문의 최종 결과 건수와 비교해 rows의 값이 크게 차이 날 때는 불필요하게 MySQL 엔진까지 데이터를 많이 가져왔다는 뜻이므로 SQL 튜닝의 대상이 될 수 있다.

filtered

SQL 문을 통해 DB 엔진으로 가져온 데이터 대상으로 필터 조건에 따라 어느 정도의 비율로 데이터를 제거했는지를 의미한다.

스토리지 엔진에서 DB 엔진(=MySQL 엔진)으로 100건의 데이터를 가져온 후 WHERE 절에서 BETWEEN 1 AND 10 조건으로 100건 중 10건으로 필터링했다면, filtered에는 10라는 정보가 출력된다(단위: %).

Extra

SQL 문을 어떻게 수행할 것인지에 관한 추가 정보를 보여주는 항목으로, 세미콜론(;)으로 구분하여 여러 가지 정보를 나열한다. 정보의 종류는 약 30여 가지 정도 되며, 여기서는 자주 만나는 몇 가지만 살펴본다.

Distinct

중복 제거가 포함되는 DISTINCT 키워드나 UNION 구문이 포함된 경우 출력된다.

Using where

WHERE 절의 필터 조건을 사용해 MySQL 엔진으로 가져온 데이터를 추출할 것이라는 의미이다. 자주 볼 수 있는 extra 정보이다.

Using temporary

임시 테이블을 생성하여 데이터의 중간 결과를 저장한다는 의미이다. 데이터를 가져와 저장한 뒤에 정렬 또는 중복 제거 등의 작업을 수행한다. 보통 DISTINCT, GROUP BY, ORDER BY 구문이 포함된 경우에 출력된다.

임시 테이블을 메모리에 생성하거나, 메모리 크기를 초과하여 디스크에 임시 테이블을 생성하면 Using temporary는 성능 저하의 원인이 될 수 있다. 따라서 이 항목의 정보가 출력되는 쿼리는 SQL 튜닝의 대상이 될 수 있다.

Using index

물리적인 데이터 파일을 읽지 않고 인덱스만을 읽어서 SQL 문의 요청사항을 처리할 수 있는 경우를 의미한다. 커버링 인덱스(covering index) 방식이라 부르며, SQL 문에서 인덱스로 구성된 열만 사용할 경우 이 방식을 활용한다.

물리적으로도 테이블보다 인덱스가 작고 정렬되어있으므로, 적은 양의 데이터에 접근할 때 성능 면에서 효율적이다.

Using filesort

정렬이 필요한 데이터를 메모리에 올리고 정렬 작업을 수행한다는 의미이다. 인덱스를 사용하지 못할 때는 따로 정렬을 해줘야 하므로 메모리 영역에 데이터를 올리게 된다. 이는 추가적인 정렬 작업이므로, 인덱스를 활용하도록 SQL 튜닝 검토 대상이 될 수 있다.

Using join buffer

조인을 수행하기 위해 중간 데이터 결과를 저장하는 조인 버퍼를 사용한다는 의미이다. 드라이빙 테이블의 데이터에 먼저 접근한 결과를 조인 버퍼에 담고 난 뒤, 조인 버퍼와 드리븐 테이블 간에 서로 일치하는 조인 키값을 찾는 과정을 수행한다.

Using union / Using intersect / Using sort_union

type 항목이 index_merge유형일 때, 즉 두 개 이상의 인덱스가 병합되어 실행되는 SQL 문에서 인덱스를 어떻게 병합했는지에 관한 상세 정보가 출력된다.

  • Using union: OR 구문으로 작성된 경우에 해당하며, 인덱스들을 합집합처럼 모두 결합하여 데이터에 접근한다는 뜻이다.
  • Using intersect: AND 구문으로 작성된 경우에 해당하며, 인덱스들을 교집합처럼 추출하는 방식이다.
  • Using sort_union: Using union과 유사하지만, WHERE 절의 OR 구문이 동등조건이 아닐 때 확인할 수 있는 정보이다.

Using index condition

필터 조건을 스토리지 엔진으로 전달하여 필터링 작업에 대한 MySQL 엔진의 부하를 줄이는 방식이다. 스토리지 엔진에서 MySQL로 전송하는 데이터양을 줄여 성능 효율을 높일 수 있는 옵티마이저의 최적화 방식이다.

Using index condition(BKA)

데이터를 검색하기 위해 배치 키 액세스(Batch key access)를 사용하는 방식이다. 전체적으로 Using index condition 유형과 비슷하다.

Using index for group-by

GROUP BY나 DISTINCT 구문이 포함되어 최적화를 위해 인덱스 루즈 스캔(index loose scan, 인덱스로 정렬 작업을 수행)을 할 때 출력되는 부가 정보이다.

Not exists

하나의 일치하는 행을 찾으면 추가로 행을 더 검색하지 않아도 될 때 출력되는 유형이다.
LEFT OUTER JOIN 또는 RIGHT OUTER JOIN에서 테이블에 존재하지 않는 데이터를 명시적으로 검색할 때 발생한다.

SELECT
	*
FROM 
	t1 LEFT JOIN t2 ON (...)
WHERE
	t2.not_null_column IS NULL;

위 예시에서 t1과 t2 테이블의 조건에 일치하는 데이터가 없는 경우에는 그 값이 NULL이 될 수 있으므로, 일치하는 행을 하나 찾았으니 검색을 중지하게 된다. 이때 Not exists가 출력된다.





profile
최건우
부족한 경험을 채우기 위한 나만의 기록 공간
이전 포스트

MySQL - SQL 튜닝 용어(4): 개념적인 튜닝 용어

다음 포스트

MySQL - SQL 튜닝의 실행 계획 파헤치기(2)

0개의 댓글