[스터디] 20. 실행 계획 분석

목차

10.3 실행 계획 분석

---

• MySQL 8.0 버전부터는 EXPLAIN 명령의 결과로 출력되는 실행 계획의 포맷을 기존 테이블 포맷과 JSON, TREE 형태로 선택할 수 있음.
• 실행 계획의 출력 포맷보다는 실행 계획이 어떤 접근 방법을 사용해서 어떤 최적화를 수행하는지, 그리고 어떤 인덱스를 사용하는지 등을 이해하는 것이 더 중요함.
• 아무런 옵션 없이 EXPLAIN 명령을 실행하면, 쿼리 문장의 특성에 따라 표 형태로 된 1줄 이상의 결과가 표시됨.
  • 표의 각 라인(레코드)은 쿼리 문장에서 사용된 테이블(서브쿼리로 임시 테이블을 생성한 경우 그 임시 테이블까지 포함)의 개수만큼 출력됨.
  • 실행 순서는 위에서 아래로 표시됨(UNION이나 상관 서브쿼리와 같은 경우 순서대로 표시되지 않을 수도 있음).
  • 출력된 실행 계획에서 위쪽에 출력된 결과일수록(id 칼럼의 값이 작을수록) 쿼리의 바깥(Outer) 부분이거나 먼저 접근한 테이블이고, 아래쪽에 출력된 결과일수록(id 칼럼의 값이 클수록) 쿼리의 안쪽(Inner) 부분 또는 나중에 접근한 테이블에 해당함.

10.3.1 id 칼럼

• 하나의 SELECT 문장은 다시 1개 이상의 하위(SUB) SELECT 문장을 포함할 수 있음.

mysql> SELECT ...
	FROM (SELECT ... FROM tb_test1) tb1, tb_test2 tb2
    	WHERE tb1.id=tb2.id;

• 아래처럼 SELECT 키워드 단위로 구분한 것을 "단위(SELECT) 쿼리"라고 표현했을 때, 실행 계획의 가장 왼쪽에 표시되는 id  칼럼은 단위 SELECT 쿼리별로 부여되는 식별자 값임. 예제 쿼리에서는 실행 계획에서 최소 2개의 id 값이 표시될 것임.

mysql> SELECT ... FROM tb_test1;
mysql> SELECT ... FROM tb1, tb_test2 tb2 WHERE tb1.id=tb2.id;

10.3.2 select_type 칼럼

각 단위 SELECT 쿼리가 어떤 타입의 쿼리인지 표시되는 칼럼임. select_type 칼럼에 표시될 수 있는 값은 다음과 같음.

10.3.2.1 SIMPLE

• UNION이나 서브쿼리를 사용하지 않는 단순한 SELECT 쿼리인 경우, 해당 쿼리 문장의 select_type은 SIMPLE로 표시됨(쿼리에 조인이 포함된 경우에도 마찬가지임).
• 쿼리 문장이 아무리 복잡하더라도 실행 계획에서 select_type이 SIMPLE인 단위 쿼리는 하나만 존재함.
• 일반적으로 제일 바깥 SELECT 쿼리의 select_type이 SIMPLE로 표시됨.

10.3.2.2 PRIMARY

• UNION이나 서브쿼리를 가지는 SELECT 쿼리의 실행 계획에서 가장 바깥쪽(Outer)에 있는 단위 쿼리는 select_type이 PRIMARY로 표시됨.
• SIMPLE과 마찬가지로 select_type이 PRIMARY인 단위 SELECT 쿼리는 하나만 존재함.

10.3.2.3 UNION

• UNION으로 결합하는 단위 SELECT 쿼리 가운데 첫 번째를 제외한 두 번째 이후 단위 SELECT 쿼리의 select_type은 UNION으로 표시됨.
• UNION의 첫 번째 단위 SELECT는 select_type이 UNION이 아니라 UNION되는 쿼리 결과들을 모아서 저장하는 임시 테이블(DERIVED)이 select_type으로 표시됨.

10.3.2.4 DEPENDENT UNION

• DEPENDENT UNION 또한 UNION select_type과 같이 UNION이나 UNION ALL로 집합을 결합하는 쿼리에서 표시됨.
• DEPENDENT는 UNION이나 UNION ALL로 결합된 단위 쿼리가 외부 쿼리에 의해 영향을 받는 것을 의미함.

10.3.2.5 UNION RESULT

• UNION RESULT는 UNION 결과를 담아두는 테이블을 의미함.
• MySQL 8.0 이전 버전에서는 UNION ALL이나 UNION(또는 UNION DISTINCT) 쿼리는 모두 UNION의 결과를 임시 테이블로 생성했는데, MySQL 8.0 버전부터는 UNION ALL의 경우 임시 테이블을 사용하지 않도록 기능이 개선됨.
• UNION(또는 UNION DISTINCT)은 MySQL 8.0 버전에서도 여전히 임시 테이블에 결과를 버퍼링함.
• 실행 계획 상에서 이 임시 테이블을 가리키는 라인의 select_type이 UNION RESULT임. UNION RESULT는 실제 쿼리에서 단위 쿼리가 아니기 때문에 별도의 id 값은 부여되지 않음.

10.3.2.6 SUBQUERY

• select_type의 SUBQUERY는 FROM 절 이외에서 사용되는 서브쿼리만을 의미함.
• MySQL 서버의 실행 계획에서 FROM 절에 사용된 서브쿼리는 select_type이 DERIVED로 표시되고, 그밖의 위치에서 사용된 서브쿼리는 전부 SUBQUERY라고 표시됨.
• 이 책이나 MySQL 매뉴얼에서 사용하는 "파생 테이블"이라는 단어는 DERIVED와 같은 의미로 이해하면 됨.

10.3.2.7 DEPENDENT SUBQUERY

• 서브쿼리가 바깥쪽(Outer) SELECT 쿼리에서 정의된 칼럼을 사용하는 경우, select_type에 DEPENDENT SUBQUERY라고 표시됨.
• 안쪽(Inner)의 서브쿼리 결과가 바깥쪽(Outer) SELECT 쿼리의 칼럼에 의존적이기 때문에, DEPENDENT라는 키워드가 붙음.
• DEPENDENT UNION과 같이 DEPENDENT SUBQUERY 또한, 외부 쿼리가 먼저 수행된 후 내부 쿼리(서브쿼리)가 실행되어야 하므로(DEPENDENT 키워드가 없는) 일반 서브쿼리보다는 처리 속도가 느릴 때가 많음.

10.3.2.8 DERIVED

• MySQL 5.5 버전까지는 서브쿼리가 FROM 절에 사용된 경우 항상 select_type이 DERIVED인 실행 계획을 만듦.
• 하지만 MySQL 5.6 버전부터는 옵티마이저 옵션(optimizer_switch 시스템 변수)에 따라 FROM 절의 서브쿼리를 외부 쿼리와 통합하는 형태의 최적화가 수행되기도 함.
• DERIVED는 단위 SELECT 쿼리의 실행 결과로 메모리나 디스크에 임시 테이블을 생성하는 것을 의미함.
• select_type이 DERIVED인 경우에 생성되는 임시 테이블을 파생 테이블이라고도 함.
  • MySQL 5.5 버전까지는 파생 테이블에는 인덱스가 전혀 없으므로 다른 테이블과 조인할 때 성능상 불리할 때가 많음.
  • MySQL 5.6 버전부터는 옵티마이저 옵션에 따라 쿼리의 특성에 맞게 임시 테이블에도 인덱스를 추가해서 만들 수 있게 최적화되었음.

10.3.2.9 DEPENDENT DERIVED

• MySQL 8.0 이전 버전에서는 FROM 절의 서브쿼리는 외부 칼럼을 사용할 수가 없었는데, MySQL 8.0 버전부터는 래터럴 조인(LATERAL JOIN) 기능이 추가되면서 FROM 절의 서브쿼리에서도 외부 칼럼을 참조할 수 있음.
• 래터럴 조인의 경우에는 LATERAL 키워드를 사용해야 하며, LATERAL 키워드가 없는 서브쿼리에서 외부 칼럼을 참조하면 오류가 발생함.

10.3.2.10 UNCACHEABLE SUBQUERY

• 하나의 쿼리 문장에 서브쿼리가 하나만 있더라도 실제 그 서브쿼리가 한 번만 실행되는 것은 아님.
• 조건이 똑같은 서브쿼리가 실행될 때는 다시 실행하지 않고 이전의 실행 결과를 그대로 사용할 수 있게 서브쿼리의 결과를 내부적인 캐시 공간에 담아둠.
• 서브쿼리 캐시는 쿼리 캐시나 파생 테이블(DERIVED)과는 전혀 무관한 기능임.

10.3.2.11 UNCACHEABLE UNION

• UNCACHEABLE UNION: UNION과 UNCACHEABLE 키워드의 속성이 혼합된 select_type

10.3.2.12 MATERIALIZED

• MySQL 5.6 버전부터 도입된 select_type으로, 주로 FROM 절이나 IN(subquery) 형태의 쿼리에 사용된 서브쿼리의 최적화를 위해 사용됨.
  • MySQL 5.6 버전까지는 employees 테이블을 읽어서 employees 테이블의 레코드마다 salaries 테이블을 읽는 서브쿼리가 실행되는 형태로 처리됨.
  • MySQL 5.7 버전부터는 서브쿼리의 내용을 임시 테이블로 구체화(Materialization)한 후, 임시 테이블과 employees 테이블을 조인하는 형태로 최적화되어 처리됨.

10.3.3 table 칼럼

• MySQL 서버의 실행 계획은 단위 SELECT 쿼리 기준이 아니라 테이블 기준으로 표시됨.
• 테이블의 이름에 별칭이 부여된 경우에는 별칭이 표시됨.

mysql> EXPLAIN SELECT NOW();
mysql> EXPLAIN SELECT NOW() FROM DUAL;	-- MySQL 옵티마이저는 첫 번째와 동일하게 변형해서 처리함.

• table 칼럼에 또는 과 같이 "<>"로 둘러싸인 이름이 명시되는 경우가 많은데, 이 테이블은 임시 테이블을 의미함.
• "<>" 안에 항상 표시되는 숫자는 단위 SELECT 쿼리의 id 값을 지칭함.

10.3.4 partitions 칼럼

• MySQL 5.7 버전까지는 옵티마이저가 사용하는 파티션들의 목록은 EXPLAIN PARTITION 명령을 이용해 확인 가능함.
• MySQL 8.0 버전부터는 EXPLAIN 명령으로 파티션 관련 실행 계획까지 모두 확인할 수 있게 변경됨.

10.3.5 type 칼럼

• 쿼리의 실행 계획에서 type 이후의 칼럼은 MySQL 서버가 각 테이블의 레코드를 어떤 방식으로 읽었는지를 나타냄. 이 방식은 인덱스를 사용해 레코드를 읽었는지, 아니면 테이블을 처음부터 끝까지 읽는 풀 테이블 스캔으로 레코드를 읽었는지 등을 의미함.
• 일반적으로 쿼리를 튜닝할 때 인덱스를 효율적으로 사용하는지 확인하는 것이 중요하므로 실행 계획에서 type 칼럼은 반드시 체크해야 할 중요한 정보임.
• MySQL의 매뉴얼에서는 type 칼럼을 "조인 타입"으로 소개함. MySQL에서는 하나의 테이블로부터 레코드를 읽는 작업도 조인처럼 처리하기 때문임.
• type 칼럼의 값은 조인과 직접 연관 지어 생각하지 말고, 각 테이블의 접근 방법(Access type)으로 해석하면 됨.
• 실행 계획의 type 칼럼에 표시될 수 있는 값은 현재 많이 사용되는 대부분의 버전에서 거의 차이 없이 다음과 같이 표시됨.
  • system
  • const
  • eq_ref
  • ref
  • fulltext
  • ref_or_null
  • unique_subquery
  • index_subquery
  • range
  • index_merge
  • index
  • ALL
• 위의 12개 접근 방법 중에서 ALL을 제외한 나머지는 모두 인덱스를 사용하는 접근 방법임.
• ALL은 인덱스를 사용하지 않고, 테이블을 처음부터 끝까지 읽어서 레코드를 가져오는 풀 테이블 스캔 접근 방법을 의미함.
• 하나의 단위 SELECT 쿼리는 위의 접근 방법 중에서 단 하나만 사용할 수 있음.
• index_merge를 제외한 나머지 접근 방법은 하나의 인덱스만 사용함. 그러므로 실행 계획의 각라인에 접근 방법이 2개 이상 표시되지 않으며, index_merge 이외의 type에서는 인덱스 항목에도 단 하나의 인덱스 이름만 표시됨.

10.3.5.1 system

• 레코드가 1건만 존재하는 테이블 또는 한 건도 존재하지 않는 테이블을 참조하는 형태의 접근 방법을 system이라고 함.
• 이 접근 방법은 InnoDB 스토리지 엔진을 사용하는 테이블에서는 나타나지 않고, MyISAM이나 MEMORY 테이블에서만 사용되는 접근 방법임.
• 쿼리의 모양에 따라 조금은 다르겠지만 접근 방법(type 칼럼)이 ALL 또는 index로 표시될 가능성이 큼.
• system은 테이블에 레코드가 1건 이하인 경우에만 사용할 수 있는 접근 방법이므로 실제 애플리케이션에서 사용되는 쿼리에서는 거의 보이지 않는 실행 계획임.

10.3.5.2 const

• 테이블의 레코드 건수와 관계없이 쿼리가 프라이머리 키나 유니크 키 칼럼을 이용하는 WHERE 조건절을 가지고 있으며, 반드시 1건을 반환하는 쿼리의 처리 방식을 const라고 함.
• 다른 DBMS에서는 이를 유니크 인덱스 스캔(UNIQUE INDEX SCAN)이라고도 표현함.

10.3.5.3 eq_ref

• eq_ref 접근 방법은 여러 테이블이 조인되는 쿼리의 실행 계획에서만 표시됨.
• 조인에서 처음 읽은 테이블의 칼럼값을, 그다음 읽어야 할 테이블의 프라이머리 키나 유니크 키 칼럼의 검색 조건에 사용할 때를 가리켜 eq_ref라고 함.
  • 두 번째 이후에 읽는 테이블의 type 칼럼에 eq_ref가 표시됨.
  • 또한 두 번째 이후에 읽히는 테이블을 유니크 키로 검색할 때 그 유니크 인덱스는 NOT NULL이어야 하며, 다중 칼럼으로 만들어진 프라이머리 키나 유니크 인덱스라면 인덱스의 모든 칼럼이 비교 조건에 사용되어야만 eq_ref 접근 방법이 사용될 수 있음.
  • 조인에서 두 번째 이후에 읽는 테이블에서 반드시 1건만 존재한다는 보장이 있어야 사용할 수 있는 접근 방법임.

10.3.5.4 ref

• ref 접근 방법은 eq_ref와는 달리 조인의 순서와 관계없이 사용되며, 또한 프라이머리 키나 유니크 키 등의 제약 조건도 없음.
• 인덱스의 종류와 관계없이 동등(Equal) 조건으로 검색할 때는 ref 접근 방법이 사용됨.
• ref 타입은 반환되는 레코드가 반드시 1건이라는 보장이 없으므로 const나 eq_ref보다는 빠르지 않음. 하지만 동등한 조건으로만 비교되므로 매우 빠른 레코드 조회 방법임.
• 실행 계획의 type에 대해 간단히 비교하면 아래와 같음.
  1. const: 조인의 순서와 관계없이 프라이머리 키나 유니크 키의 모든 칼럼에 대해 동등(Equal) 조건으로 검색(반드시 1건의 레코드만 반환)
  2. eq_req: 조인에서 첫 번째 읽은 테이블의 칼럼값을 이용해 두 번째 테이블을 프라이머리 키나 유니크 키로 동등 (Equal) 조건 검색(두 번째 테이블은 반드시 1건의 레코드만 반환)
  3. ref: 조인의 순서와 인덱스의 종류에 관계없이 동등(Equal) 조건으로 검색(1건의 레코드만 반환된다는 보장이 없어도 됨)
• 이 세 가지 접근 방법 모두 WHERE 조건절에 사용하는 비교 연산자는 동등 비교 연산자여야 한다는 공통점이 있음.
• 동등 비교 연산자는 "=" 또는 "<=>"을 의미함. "<=>" 연산자는 NULL에 대한 비교 방식만 조금 다를 뿐 "=" 연산자와 같은 연산자임.
• 또한 세 가지 모두 인덱스의 분포도가 나쁘지 않다면 성능상의 문제를 일으키지 않는 접근 방법임.

10.3.5.5 fulltext

• fulltext 접근 방법은 MySQL 서버의 전문 검색(Full-text Search) 인덱스를 사용해 레코드를 읽는 접근 방법을 의미함.
• 전문 검색 인덱스는 통계 정보가 관리되지 않으며, 전문 검색 인덱스를 사용하려면 전혀 다른 SQL 문법을 사용해야 함.
• MySQL 서버에서 전문 검색 조건은 우선순위가 상당히 높음.
• 쿼리에서 전문 인덱스를 사용하는 조건과 그 이외의 일반 인덱스를 사용하는 조건을 함께 사용하면 일반 인덱스의 접근 방법이 const나 eq_ ref, ref가 아니면 일반적으로 MySQL은 전문 인덱스를 사용하는 조건을 선택해서 처리함.
• 전문 검색은 "MATCH (...) AGAINST (...)" 구문을 사용해서 실행하는데, 이때 반드시 해당 테이블에 전문 검색용 인덱스가 준비돼 있어야만 함. 테이블에 전문 인덱스가 없다면 쿼리는 오류가 발생하고 중지될 것임.

10.3.5.6 ref_or_null

• 이 접근 방법은 ref 접근 방법과 같은데, NULL 비교가 추가된 형태임.
• 접근 방법의 이름 그대로 ref 방식 또는 NULL 비교(IS NULL) 접근 방법을 의미함.

10.3.5.7 unique_subquery

• WHERE 조건절에서 사용될 수 있는 IN(subquery) 형태의 쿼리를 위한 접근 방법임.
• unique_subquery의 의미 그대로 서브쿼리에서 중복되지 않는 유니크한 값만 반환할 때 이 접근 방법을 사용함.

 

정말 오랫동안 공부한 것 같습니다. 'Real MySQL 8.0 (1권)' 스터디는 이렇게 끝났습니다. 이어서 '자바/스프링 개발자를 위한 실용주의 프로그래밍' 책으로 학습을 진행할 것 같습니다. 정말 어려웠지만, 많은 것을 배울 수 있었습니다.

 

이 글은 『Real MySQL 8.0 (1권)』 책을 학습한 내용을 정리한 것입니다.