MySQL 실행 계획(좋은 쿼리는 못 만들어도 뭐같은 쿼리는 만들지 말아야지)

DB의 꽃 옵티마이저의 실행 계획 수립

RDBMS에서 가장 복잡하면서 가장 중요한 것은 옵티마이저(Optimizer)가 쿼리를 어떻게 실행할지 실행 계획을 결정하는 부분이다.

똑같은 쿼리라 할지라도 다양한 방법과 순서로 실행 될 수 있다.

어떤 실행 계획이 좋고 어떤 실행 계획이 안 좋은지 판단하는 건 온전히 옵티마이저의 몫이지만 개발자 역시도 어떤 실행 계획으로 수행되어야 좋은지를 알아야 최적의 실행 계획을 사용할 수 있도록 옵티마이저에게 힌트를 줄 수 있기 때문에 중요하고 반드시 학습해야하는 부분이다.

사전 지식

실행 계획에 대해 자세히 살펴보기 전에 아래의 사전 지식이 있어야 한다.

• 쿼리 실행 절차
  1. SQL을 SQL 파서가 파싱하여 파서 트리(parser tree)를 만든다.
  2. 파서 트리를 기준으로 옵티마이저가 분석하여 실행 계획을 만든다.
• 옵티마이저의 종류
  1. 규칙 기반 옵티마이저 : 이제는 안 쓰임
  2. 비용 기반 옵티마이저 : 쿼리 대상 테이블의 레코드 수, 선택도 등 통계 정보를 바탕으로 비용이 가장 적을 것 같은 방향으로 실행 계획 생성 → 대부분 이 방식 옵티마이저 사용(MySQL포함)
• 통계 정보가 정확할 수록 좋은 실행 계획을 만들 확률이 높다.
  • 통계 정보는 서비스중에도 수집되고 수정된다. innodb_stats_sample_pages 설정값은 통계 정보를 위해 분석할 인덱스 페이지의 개수(기본값:8)를 지정하는 것인데, 분석할 페이지 개수를 늘려서 더 정확한 통계 정보를 수집할 수 있지만, 수집 중에 테이블의 읽기/쓰기가 되지 않으므로 문제될 수 있다. 따라서 최대 16~24까지만 설정해야한다.
  • MySQL 8.0 부터는 인덱스 말고 일반 컬럼에도 통계 정보(Histogram)를 수집하기 때문에 더 정확한 통계를 토대로 실행 계획을 수립할 수 있다.
  • ANALYZE 키워드를 이용해 테이블의 통계 정보를 다시 수집할 수 있다.
• EXPLAIN 키워드를 SELECT 쿼리 앞에 붙여서 실행 계획을 살펴볼 수 있다.
  • INSERT, UPDATE, DELETE 쿼리는 실행 계획을 알 수 없다.

실행 계획 전격 분석

EXPLAIN 키워드를 붙여서 실행 계획 정보를 제공받을 때 테이블 형식으로 결과가 보여진다. (MySQL 8.0부터는 JSON이나 TREE로도 볼 수 있음)

따라서 제공받은 테이블의 각 컬럼이 어떤 의미인지 이해할 수 있어야 하기에 정리한다.

EXPLAIN SELECT *
FROM employees e
    INNER JOIN salaries s ON s.emp_no = e.emp_no
WHERE first_name='ABC';

 

id 컬럼

SELECT 쿼리를 구분하기 위한 용도의 컬럼이다. (SELECT문이 몇 개인지를 확인한다고 보면 된다.)

• SELECT 쿼리에 JOIN을 통해 여러 개의 테이블을 조회하더라도 JOIN으로 연결되어있다면 다른 SELECT 문으로 보지 않기 때문에 연관된 레코드는 같은 ID가 부여된다.
• SELECT 쿼리 내부에서 서브 쿼리(SELECT)를 사용한다든지 UNION을 사용하여 여러 테이블을 조회하는 경우가 있다. 이 때는 서브 쿼리와 UNION 쿼리로 조회되는 것은 다른 ID가 부여된다.

select_type 컬럼

SELECT 쿼리가 어떤 타입인지 나타내는 용도의 컬럼이다.

• SIMPLE : UNION이나 서브쿼리를 사용하지 않은 단순 SELECT 쿼리의 경우 SIMPLE로 표시 (JOIN한 경우도 SIMPLE 타입으로 친다.)
  • 실행 계획 테이블에서 select_type이 SIMPLE인 쿼리는 반드시 하나만 존재한다.
• PRIMARY : UNION, 서브쿼리가 포함된 SELECT 쿼리의 경우 가장 바깥쪽에 있는 SELECT 쿼리가 PRIMARY로 표기되고 마찬가지로 select_type이 PRIMARY인 쿼리는 반드시 하나만 존재한다.
• UNION : UNION을 사용한 경우 SELECT 쿼리 가운데 두 번째 이후 쿼리는 UNION으로 표시된다.
  • UNION에 사용된 첫 번째 SELECT 쿼리는 UNION의 결과를 대표하는 select_type으로 설정되어 DERIVED 라는 select_type을 갖는다.
• DEPENDENT UNION : UNION을 사용한 경우 중에서도 UNION으로 결합된 쿼리가 외부 쿼리에 의해 영향을 받는 것을 표기한다.
  • 내부 쿼리가 외부의 값을 참조해서 처리될 때 DEPENDENT 키워드가 추가된다.
• UNION RESULT : MySQL8.0부터 UNION ALL을 쓸 때는 결과를 담을 "임시 테이블"을 생성하지 않기로해서 안 보이지만 UNION 또는 UNION DISTINCT 쿼리는 임시 테이블에 결과를 담는다. 그래서 실행 계획 상 UNION RESULT는 UNION의 결과를 담는 임시 테이블을 의미한다.
• SUBQUERY : FROM절 이외에 사용된 서브 쿼리에 표기한다.
  • FROM 절에 사용된 서브 쿼리는 DERIVED 로 표기된다.
• DEPENDENT SUBQUERY : FROM절 이외에 사용된 서브 쿼리가 바깥쪽 SELECT 쿼리에서 정의된 컬럼을 사용하는 경우에 해당 서브 쿼리에 표기된다.
• DERIVED : FROM절에 사용된 서브 쿼리로 SELECT 쿼리의 결과로 메모리나 디스크에 임시 테이블을 만드는 경우를 의미한다.
• DEPENDENT DERIVED : MySQL 8.0 이전에는 FROM 절의 서브쿼리에 외부 컬럼을 사용할 수 없었으나 8.0 이후로는 가능하게 되었다. 래터럴(LATERAL) 조인으로 FROM 절의 서브 쿼리가 외부 컬럼을 참조할 수 있게 되었는데 래터럴 조인을 사용했을 때 표기된다.
• UNCACHEABLE SUBQUERY : SUBQUERY, DEPENDENT SUBQUERY는 서브 쿼리 결과를 캐시할 수 있는데 특정 조건때문에 캐시를 이용할 수 없을 때 표기된다.
  • 사용자 변수가 서브 쿼리에 들어갔다거나 UUID(), RAND() 같이 결과값이 호출할 때마다 변경되는 함수가 서브 쿼리에 들어갔다거나 NOT-DETERMINISTIC 속성의 스토어드 함수가 서브 쿼리에 들어간 경우가 특정 조건이다.
• MATERIALIZED : FROM 절이나 IN (subquery) 형태의 쿼리에 사용된 서브 쿼리를 최적화할 때 사용된다. 이 경우에 보통 서브 쿼리보다 외부 쿼리의 테이블을 먼저 읽어서 비효율적으로 실행되기 마련인데 이렇게 실행하지 않고 서브 쿼리의 내용을 임시테이블로 구체화한 후 외부 테이블과 조인하는 형태로 최적화된다. 이 때, 서브 쿼리가 먼저 구체화되었다는 것을 표기할 때 사용된다.
  • 뭔가 효율적으로 개선된 듯하지만 결국 임시테이블을 사용하므로 엄청 효율적이지는 않다.

쿼리 타입만 보고도 어떤 쿼리를 개선해야할지 감이 잡히기 때문에 잘 알아두는 것이 좋겠다.

기본적으로 DEPENDENT 는 외부 쿼리에 의존하므로 성능이 느리고 임시 테이블을 사용하는 것들도 디스크에 임시 테이블을 만들 위험이 있기에 제거 대상이다. (메모리에 임시 테이블은 그나마 좀 낫다.)

DERIVED 도 최대한 JOIN으로 해결해야하기에 제거 대상이 된다.

table 컬럼

EXPLAIN 키워드로 실행 계획을 확인할 때, SELECT 쿼리로 분류하는게 아니라 테이블 단위로 분류해서 결과가 나온다.

그래서 어떤 테이블에 대한 정보인지를 나타낸다.

table 컬럼에 <> 로 감싸져있는 경우를 볼 수 있는데 이 테이블은 임시 테이블을 의미한다.

partitions 컬럼

파티셔닝(partitioning)하여 테이블을 관리한다고 가정할 때, 어떤 파티션을 읽었는지를 알려주는 정보다.

5.7 이전에는 EXPLAIN PARTITION 명령을 해야 보였으나 8.0 부터는 EXPLAIN 명령만해도 보이게 되었다.

type 컬럼

실행 계획이 적절하게 인덱스를 참조했는지를 확인하는데 가장 🌈핵심인 컬럼이다.

ALL을 빼고는 다 인덱스를 참조하긴 했다는것인데 인덱스를 참조했다고해서 무조건 효율적인 것은 아니다.

아래 type 컬럼의 종류는 성능이 좋은 순서로 나열했다.

참고로 1 부터 9 까지는 일반적으로 효율적인 케이스고 10부터 12까지는 일반적으로 비효율적인 케이스다.

1. system : 레코드가 0건 또는 1건만 존재하는 테이블을 접근할 때의 방법 (InnoDB에는 없으니 생략)
2. const : 쿼리에 프라이머리 키/유니크 키 컬럼을 이용하는 where 조건에 있으면서 결과가 반드시 1건을 반환하는 쿼리로 접근할 때의 방법 (= UNIQUE INDEX SCAN)
   • ❗결과가 1개인 것을 쿼리 전에 DBMS가 예측할 수 있어야 한다. (쿼리 실행 후에 결과가 1개인 것은 의미없음)
3. eq_ref : 여러 테이블이 JOIN되는 쿼리에서만 발생, JOIN에서 처음 읽은 테이블의 컬럼 값을 두 번째 이후 읽는 테이블의 프라이머리 키/유니크 인덱스 컬럼(NOT NULL)의 동등 조건에 사용될 때 = 반드시 1건만 존재한다는 보장이 있을 때 사용되는 접근 방법
4. ref : 'eq_ref'와 달리 JOIN 순서에 상관없이 사용되며, 프라이머리 키나 유니크 인덱스 등의 제약도 상관없이 사용된다. 인덱스의 종류와 상관없이 동등(equal)조건이 사용될 때 접근 방법이다. (단, 레코드가 반드시 1건이라는 보장이 없으므로 eq_ref보다 느리지만 비교적 느릴 뿐 엄청 빠른 인덱스다.)
5. fulltext : MySQL로 전문 검색 인덱스를 사용해서 레코드에 접근하는 방법, 전문 검색할 컬럼에 인덱스가 있어야 한다. "MATCH ... AGAINST ..." 구문을 사용해서 실행된다. (잘 안 써봐서 모르겠다...)
6. ref_or_null : ref와 같은데 NULL 비교(IS NULL)가 추가된 형태 (실무에서 보기 힘든 형태???)
7. unique_subquery : where 조건에 IN (subquery) 형태를 갖을 때, 서브 쿼리에서 중복되지 않는 유니크한 값만 반환될 때 이 접근 방식을 선택한다.
8. index_subquery : IN 연산자 특성상 IN 괄호 조건에 나오는 목록에 중복값이 제거 되어야 한다. 유니크하지 않은 경우에 인덱스를 이용하여 중복을 제거하는 케이스다.
9. range : 인덱스를 하나의 값이 아니라 범위로 검색하는 경우에 사용되는 접근 방법이다. 주로 < , > , IS NULL , BETWEEN , IN , LIKE 등의 연산자로 인덱스를 검색하는 경우다. 통상적으로 '인덱스 스캔이다' 라고하면 range, const, ref를 묶어서 지칭한다.
10. index_merge : 2개 이상의 인덱스를 이용해 각각의 검색결과를 만든 후 결과를 합치는 접근 방식, 실제 우선순위가 range보다 높지만 생각보다 효율적으로 동작하지 않은 문제가 있다고 한다.
11. index : 인덱스를 처음부터 끝까지 읽어야 하는 경우에 쓰는 접근 방식으로 비효율적이다. 다음 두 가지 조건에서 발생한다. 최악은 아니고 차악정도 느낌이다.
    • 인덱스 스캔(range, const, ref) 불가능한 경우 + 인덱스에 포함된 컬럼만으로 처리할 수 있는 쿼리인 경우(굳이 데이터파일 읽지 않아도 되는 경우)
    • 인덱스 스캔(range, const, ref) 불가능한 경우 + 인덱스를 이용해 정렬이나 그룹핑 작업이 가능한 경우(정렬 작업을 피할 수 있는 경우)
12. ALL : 풀 테이블 스캔이다. 일반적인 조회 환경에서 가장 나쁜 방법이지만 억지로 잘못 설계된 인덱스를 타게 하는 경우보다 풀 스캔이 적절할 때도 있긴 있다.

possible_keys 컬럼

옵티마이저가 쿼리를 처리하기 위해 여러 처리 방법을 고려하던 중에 사용된 후보 인덱스 목록일 뿐이다.

key 컬럼

possible_keys 컬럼에서 보여진 후보 인덱스 목록 중 실제 사용된 인덱스를 의미한다.

인덱스 사용 못했을 경우는 NULL로 표기된다.

key_len 컬럼

실무에서 인덱스를 단일 컬럼으로 만들기 보다는 다중 컬럼으로 만들어지는 경우가 더 많다.

이 때 다중 컬럼 인덱스 중에서 몇 바이트까지 사용했는지 알려준다.

각 인덱스 컬럼에 할당된 바이트를 알 수 있으니 몇 개의 인덱스 컬럼이 사용되었는지를 추산할 수 있다.

아래 쿼리는 (dept_no, emp_no)로 다중 컬럼(2개)으로 만들어진 프라이머리 키를 포함한 dept_emp테이블을 조회하는 쿼리다.

SELECT * FROM dept_emp WHERE dept_no=3;

key_len이 4가 나온다고 해보자. 그러면 dept_no 컬럼의 타입이 INTEGER(4BYTE)이기 때문에 인덱스(dept_no + emp_no)중에서 앞에 dep_no까지만 쓰였다는 것을 확인할 수 있다.

ref 컬럼

type 컬럼에서 접근 방법이 ref 이면 어떤 컬럼이 조건에 사용되었는지를 보여준다.

"func"라고 표시될 때도 있는데 이건 단순 컬럼이 아니라 어떤 가공된 컬럼이 사용될 때 나타난다.

row 컬럼

옵티마이저가 비용을 산정하기 위해 얼마나 많은 레코드를 읽고 비교해야하는지 예측해본 레코드의 수다.

실제 테이블의 레코드 수와 일치하지 않는 경우가 많고 대략적인 통계에 의한 값이다.

Extra 컬럼

MySQL이 어떻게 쿼리를 풀었는지 부가 정보가 나온다. 꽤 많은 종류가 있고, 여러 개가 동시에 나온다.

• const row not found : const 접근 방식으로 읽었으나 레코드가 0개 일 때를 의미한다.
• distinct : JOIN시 필요한 것만 JOIN했고 중복된 값 제거했음을 의미한다.
• Full scan on NULL key : where 조건에 nullable 컬럼이 있는 경우, null을 만나면 풀 스캔을 하겠다고 경고하는 의미다.

SELECT * FROM test
WHERE col1 IN (SELECT col2 FROM test2);
-- col1이 null인 경우에 풀스캔 하겠다라고 뜬다.

• Impossible HAVING : HAVING 조건에 만족하는 레코드가 없는 경우를 의미한다. = 쿼리를 다시 짜라
• Impossible WHERE : WHERE 조건이 항상 FALSE가 될 수 밖에 없는 경우를 의미한다. = 쿼리 다시 짜라
• Impossible WHERE noticed after reading const tables : 쿼리를 실행해보기 전엔 WHERE 조건이 항상 false인지는 모르지만 실행해보니까 알게된 경우다. = 쿼리 다시 짜라
  • const 는 상수로 치환되기 때문에 옵티마이저가 쿼리 일부를 실행해보기에 이런 결과가 나올 수 있다.
• No matching min/max row : MIN(), MAX()와 같은 집합 함수가 있는 쿼리의 WHERE 조건절에 일치하는 레코드가 하나도 없는 경우를 의미한다. = 쿼리 다시 짜라
• No matching row in const table : const 방식으로 접근할 때 일치하는 레코드가 없는 경우를 의미한다. = 쿼리 다시 짜라
• No tables used : FROM 절이 없거나 "FROM DUAL" 형태의 쿼리의 경우를 의미한다.
• Not exists : A 테이블에는 존재하지만 B 테이블에 존재하지 않는 값을 조회하는 경우 두 가지 방법을 쓴다.
  • 안티조인(Anti-JOIN) : NOT IN (subquery) 형태나 NOT EXISTS 연산자를 사용한 방법
  • 아우터조인(LEFT OUTER JOIN) : 레코드가 많을 때 안티조인보다 더 나은 방법
    • 아우터조인으로 처리할 때, 안티 조인을 수행하는 쿼리에서는 B테이블의 레코드가 존재하는지 아닌지만 판단하기에 최적화 할 수 있기에 이 때, MySQL 내부적으로 최적화를 NOT EXISTS 스타일로 했다는 것으로 적절히 처리된다.

SELECT * FROM dept_emp de LEFT JOIN departments d ON de.dept_no = d.dept_no
WHERE d.dept_no IS NULL;

• Range checked for each record (index map: N) : 두 테이블을 조인하는 경우에 WHERE 조건에 변수가 두 개가 사용되어 계속해서 변수 값이 바뀐다. 즉 매 레코드마다 인덱스를 탈지 풀 스캔을 할지 결정해야하는 쿼리다.

SELECT * FROM employees e1, employees e2
WHERE e1.emp_no >= e2.emp_no;
-- 이 경우 e1 테이블의 레코드를 읽고 e2의 레코드를 읽는데 e1 테이블 레코드를 읽을 때마다 값이 달라진다.

• Scanned N databases : DB 메타정보(테이블,컬럼,인덱스등...)가 저장되어 있는 DB(INFORMATION_SCHEMA)를 조회하는 경우를 의미한다. 애플리케이션에서 메타정보가 있는 DB에 쿼리할 일이 거의 없으므로 실무에서 볼일이 없다.
• Select tables optimized away : MIN() 또는 MAX()만 SELECT에 사용되거나 GROUP BY로 MIN(), MAX()를 조회하는 쿼리가 적절한 인덱스를 사용해서 오름차순 또는 내림차순의 레코드 1건만 읽는 형태로 최적화되었음을 의미한다.
  • 인덱스는 정렬되어있기 때문에 제일 앞 또는 제일 뒤 레코드가 최소, 최대값이다.
• unique row not found : 두 개의 테이블이 프라이머리 키 또는 유니크 컬럼으로 아우터 조인을 수행하는 쿼리에서 아우터 테이블에 일치하는 레코드가 없을 때를 의미한다.
  • "A LEFT OUTER JOIN B"에서 A테이블도 B테이블도 유니크한테 A테이블에만 있는 레코드로 조인하려고 하면 B랑 조인하는 컬럼 값이 없는 애도 생긴다.
• Using filesort : ORDER BY를 처리하기 위해 인덱스를 이용할 수도 있지만 적절한 인덱스를 사용하지 못하는 경우, MySQL에서 정렬을 한 번 해야하는데 소트 버퍼에 레코드를 복사해서 정렬하는 비효율적인 작업을 하는 것을 의미한다.
  • 실무에서 정렬은 거의 필수라 그런지 제일 자주 보이는 비효율적인 케이스라고 볼 수 있다. (= 튜닝 대상이 된다.)
• Using index : 데이터 파일을 전혀 읽지 않고 인덱스만 읽어서 쿼리를 처리할 수 있는 경우(= 커버링 인덱스를 탔을 때)를 의미한다. (극강의 효율이다.)
  • 간혹 type 컬럼의 index(=인덱스 풀 스캔)와 Extra 컬럼의 Using index (=커버링 인덱스)를 헷갈리는 경우가 있는데 완전 반대되게 비효율적인 것과 효율적인 것이다.
• Using index for group-by : GROUP BY 처리를 위해 (이미 정렬된) 인덱스를 이용하는 경우 즉 루스 인덱스 스캔을 의미한다.
• Using join buffer : 일반적으로 조인에 사용되는 컬럼은 인덱스를 생성한다.
  • 조인하기 위해 먼저 읽은 테이블을 드라이빙(Driving)테이블이라 하고 나중에 읽는 테이블을 드리븐(Driven) 테이블이라 한다.
    • 드리븐 테이블에 JOIN되는 컬럼에 인덱스가 없으면 조인 버퍼(JOIN BUFFER)라는 임시 공간을 사용해서 JOIN한다. 이럴 때 나타난다.
    • 추가로 조인 조건이 없는 카테시안 조인을 수행하는 경우는 항상 조인 버퍼를 사용한다.
• Using sort_union, Using union, Using intersect : 쿼리가 "index_merge" type의 접근 방법을 사용할 때만 2개 이상의 인덱스가 사용될 수 있다고 했다. 이 때 두 인덱스로부터 읽은 결과를 어떻게 합쳤는지를 보여줄 때 나타난다.
  • Using intersect : 각각 인덱스를 사용하는 조건이 AND로 연결된 경우, 처리 결과에서 교집합을 추출했다는 의미
  • Using union : 각각 인덱스를 사용하는 조건이 OR로 연결된 경우, 처리 결과에서 합집합을 추출했다는 의미
  • Using sort_union : Using union과 같은 작업을 수행하지만 Using union으로 처리될 수 없는 경우(OR로 연결된 레코드가 대량의 range조건)이다. 프라이머리 키만 읽어서 먼저 정렬하고 병합한 후에야 레코드를 읽어서 반환할 수 있다.
• Using temporary : MySQL이 쿼리를 처리하는 동안 중간 결과를 담아 두기 위해 임시 테이블을 사용한다. 임시테이블이 사용되었을 경우에 나타나는데 임시 테이블을 썼을 때 안 나타나기도 한다(?)
  • FROM 절에 서브쿼리를 쓰면 무조건 임시테이블(Derived table)이 생성된다.
  • "COUNT(DISTINCT column1)"를 포함하는 쿼리도 인덱스를 사용할 수 없는 경우에 임시 테이블이 만들어진다.
  • UNION, UNION ALL이 사용된 쿼리도 항상 임시 테이블을 사용한다.
  • 인덱스를 사용하지 못하는 정렬 작업도 임시 테이블을 사용한다.
• Using where : MySQL엔진이 별도의 가공, 필터링 작업을 처리한 경우일 때만 나타난다. 범위 조건은 스토리지 엔진에서 처리되어 레코드를 리턴해주지만, 체크 조건은 MySQL엔진에서 처리된다. (5.1 플러그인 버전부터는 이렇게 불합리하게 처리되지 않는다고 한다. keyword : condition push down)

SELECT * FROM employees WHERE emp_no BETWEEN 100 AND 110 AND gender = 'F';
-- 범위조건 BETWEEN은 스토리지 엔진에 의해 10개의 레코드가 리턴되고
-- 10개중에 체크 조건 gender='F' 인 것을 MySQL엔진에 의해 필터링되어 Using Where 등장

Filtered 컬럼

Extra 컬럼에 표시되는 "Using where"이 스토리지 엔진이 리턴해준 레코드들을 MySQL엔진이 필터링하면 등장한다는 것을 알았다.

그런데 정확하게 얼마나 많은 레코드가 필터링되었는 알 수 없었다.

이것을 표현하는 것이 Filtered 컬럼의 역할이다.

Filtered 컬럼을 확인하려면 EXPLAIN 키워드에 추가로 EXTENDED 키워드를 붙이면 된다.

EXPLAIN EXTENDED
SELECT * FROM employees
WHERE emp_no BETWEEN 100 AND 110 AND gender = 'F';

그러면 Filtered 컬럼에 값이 나오는데 이 값은 필터링, 가공 후 레코드가 몇 퍼센트(%)나 남았을지를 알려준다.

웃긴건 이것도 실제 값이 아니라 통계 예측 값이라는 것이다...

MySQL 8.0에서는 그냥 EXPLAIN으로 나온다.