실행 계획이란?
대부분의 DBMS 의 목적은 많은 데이터를 안전하게 저장 및 관리하고 사용자가 원하는 데이터를 빠르게 조회할 수 있게 해주는것이 주 목적이다.
이러한 목적을 위해 옵티마이저가 사용자의 쿼리를 최적으로 처리될 수 있게 하는 쿼리의 실행계획을 수립할 수 있어야 한다.
하지만 옵티마이저가 항상 좋은 실행 계획을 만들어낼 수 있는것은 아니므로 이러한 문제점을 보완 할 수 있도록 EXPLAIN 명령으로 옵티마이저가 수립한 실행 계획을 확인할 수 있게 해준다.
EXPLAIN 을 활용하여 기존의 쿼리를 튜닝할 수 있을 뿐만 아니라 성능 분석, 인덱스 전략 수립 등과 같이 성능 최적화에 대한 전반적인 업무를 처리할 수 있다.
사용 방법
EXPLAIN [EXTENDED | PARTITIONS | FORMAT=JSON]
{SELECT select_options | UPDATE update_options | DELETE delete_options}Columns in EXPLAIN ... SELECT
| Column | Description |
|---|---|
| id | 테이블 JOIN 순서를 나타내는 값 |
| select_type | SELECT query 실행 종류 |
| table | 해당 단계 접근 테이블명(별칭일 경우 별칭 출력) |
| type | 테이블 내 record 조회 접근 방식 |
| possible_keys | record 에 접근하기 위한 key 또는 index 목록 |
| key | record 에 접근하기 위해 참조한 index |
| key_len | index 중 참조한 byte 정보 |
| ref | index 연산 시 비교/연산 사용 기준값 |
| rows | record 조회 시 접근해야하는 record 예측 |
| filtered | 테이블 조건으로 필터링된 행의 백분율, EXTENDED 키워드 필요 |
| Extra | 추가 정보 |
id
단위 SELECT 쿼리별로 부여되는 식별자 값이다.
테이블을 조인하면 조인되는 테이블 개수만큼 실행계획 레코드가 출력되지만
같은 id 값이 부여된다.
만약 쿼리안에 서브쿼리 등과 같이 서로 다른 실행 단위로 구성되어 있으면
각 레코드의 id 는 다르게 부여된다.
여기서 주의할점은 실행 계획의 id 컬럼이 테이블의 접근 순서를
의미하지는 않는다는것이다.
select_type
각 단위 SELECT 쿼리가 어떤 타입의 쿼리인지 표시되는 컬럼이다.
| value | description |
|---|---|
| SIMPLE | Simple SELECT 문은 UNION 또는 Sub Query를 사용하지 않는 경우 |
| PRIMARY | 가장 바깥의 SELECT 문을 의미한다. Sub Query 를 사용할 경우 Sub Query 의 외부에 있는 첫 번째 쿼리 UNION 인 경우엔 UNION의 첫 번째 SELECT 쿼리 |
| UNION | UNION 문에서 Primary 유형을 제외한 나머지 SELECT |
| DEPENDENT_UNION | UNION 과 동일하나, 외부 쿼리에 의존적인 경우 UNION 으로 결합된 단위 쿼리가 바깥쪽 쿼리에 의존적이어서 외부의 영향을 받고 있는 경우 |
| UNION_RESULT | UNION 쿼리에 대한 결과 |
| SUBQUERY | FROM 절 이외에서 사용되는 서브쿼리 중첩된 쿼리 (Nested Query): select 되는 컬럼에 사용된 서브쿼리. 서브 쿼리 (Sub Query): where 절에서 사용된 경우에는 일반적으로 그냥 서브 쿼리라고 한다. 파생 테이블 (Derived): from 절에서 사용된 서브 쿼리를 뜻하며 인라인 뷰, 서브 셀렉트 라고도 한다. |
| DEPENDENT_SUBQUERY | 안쪽의 서브쿼리 결과가 바깥쪽 SELECT 쿼리의 컬럼에 의존적인 경우 외부 쿼리가 먼저 수행된 후 내부 쿼리가 실행돼야 하므로 일반 서브쿼리보다는 처리 속도가 느릴 때가 많다. |
| DERIVED | Derived table(파생 테이블) from 절에 사용된 Sub Query(inline view)로 부터 발생한 임시 테이블을 의미한다. 임시 테이블은 메모리에 저장될 수도 있고, 디스크에 저장될 수도 있다. 가능하다면 불필요한 서브쿼리는 조인으로 해결할 수 있게 바꿔주는것이 좋다. |
| MATERIALIZED | MySQL 5.6 버전에 추가된 유형으로 IN 절 내의 Sub Query 를 임시 테이블로 만들어 조인을 하는 형태로 최적화 해준다. DERIVED 와 비슷한 개념이다. |
| UNCACHEABLE_SUBQUERY | 하나의 쿼리 문장에 서브쿼리가 하나만 있더라도 실제 그 서브쿼리가 한번만 실행되는것은 아니다. 그런데 조건이 똑같은 서브쿼리가 실행될때 다시 실행되지 않고 이전의 실행 결과를 그대로 사용할 수 있게 서브쿼리 결과를 내부적인 캐시공간에 담아둔다. 이때 이 캐시를 사용하지 못하거나 서브 쿼리에 포함된 요소에 의해 캐시 자체가 불가능한 경우이다. |
| UNCACHEABLE_UNION | 캐싱하지 못하는 UNION Sub Query |
table
단위 SELECT 쿼리 기준이 아닌 테이블 기준으로 표시된다.
테이블의 이름에 별칭이 부여된경우에는 별칭이 표시된다.
type
쿼리 실행계획에서 type 이후의 컬럼은 서버가 각 테이블의 레코드를
어떤 방식(접근 방식)으로 읽었는지를 나타낸다.
접근 방식은 대상 테이블로의 접근이 효율적일지 판단하는 데 가장 중요한 항목이다.
ALL 이외의 접근 방식은 모두 인덱스를 사용한다.
const, ref, range 의 경우 인덱스 레인지 스캔이라고도 한다.
하단의 접근 방법은 성능이 빠른 순서로 나열된 것이다.
| value | description |
|---|---|
| system | 테이블의 row 수가 1행 또는 한 건도 존재하지 않을 경우이다. InnoDB 스토리지 엔진을 사용하는 테이블에선 나타나지 않는다. |
| const | 테이블의 row 수와 관계 없이 Primary key나 Unique key 컬럼으로 where 절 조건을 가지고 있으며반드시 1건만 반환하는 쿼리 처리 방식. 다른 DBMS 에선 유니크 인덱스 스캔이라고도 한다. 다중 컬럼으로 구성된 Primary key나 Unique key 에서 일부 컬럼만 조건으로 사용하면const 가 아닌 ref 로 표시된다. |
| eq_ref | 여러 테이블이 조인되는 쿼리의 실행 계획에서만 표시된다. Primary key 나 Unique key 를 사용하여 조인하는 경우를 뜻한다. 조인되는 테이블에서 레코드가 반드시 한 건만 존재한다는 보장이 있어야 사용할 수 있는 접근 방식이다. |
| ref | eq_ref와 달리 조인의 순서와 관계없이 사용되며 인덱스의 종류와 상관없이 동등 조건으로 검색할 때 사용되는 접근 방식이다.반환되는 row 가 반드시 한 건이라는 보장이 없으므로 const 나 eq_ref보다 빠르지 않다. 하지만 동등 조건으로만 비교하므로 매우 빠른 레코드 조회 방법이다. |
| fulltext | Full Text(전문 검색) 인덱스를 사용하여 row를 읽는 방법 |
| ref_or_null | ref와 동일하면서 null 비교까지 추가된 접근 방식많이 활용되진 않지만 나쁘지 않은 접근 방법 정도로 기억해 두면 된다. |
| unique_subquery | IN 서브쿼리 접근에서 Primary key또는 Unique key를 사용하여 중복되지 않는유니크한 값만 반환할 때 사용한다. |
| index_subquery | 서브쿼리가 고유한 값만을 반환하지않을때 표현된다. 예를 들어 IN 절 안에 서브 쿼리가 존재하는 경우, 서브 쿼리가 중복된 값들을 반환한다면 IN 절 내에서 사용되기 위해서 중복 값들을 제거해야한다. 이때 인덱스를 사용해서 중복값을 제거한다면 index_subquery가 표현된다. |
| range | 인덱스 특정 범위의 row 에 접근할때 나타나는 인덱스 레인지 스캔 형태의 접근 방법이다. 인덱스를 동등 비교가 아닌 범위 비교시 발생하는 가장 많이 사용되는 방식이다. 주로 <, >, LIKE, IN, BETWEEN 이 대표적인 연산자이다.얼마나 많은 레코드가 필요한지에 따라 다르지만 상당히 빠른편에 속한다. |
| index_merge | 2개 이상의 인덱스를 사용해 검색 결과를 만들어낸 후에 그 결과를 병합하는 처리 방식 (fulltext 제외) 이름만큼 효율적인 방법은 아니다. |
| index | 인덱스를 처음부터 끝까지 읽는 인덱스 풀 스캔을 의미한다.range가 필요한 인덱스 범위를 지정해서 탐색하는 방식이라면 index는 인덱스를 전부 스캔하는 방식이다. 인덱스는 일반적으로 테이블 파일 전체보다 크기가 작으므로 풀 테이블 스캔보다 빠르게 처리된다. 위 존재하는 다른 방식들을 사용할 수는 없지만 데이터 테이블까지 가지않고 인덱스만으로 처리가 가능할 때 표현된다. |
| ALL | 풀 테이블 스캔을 의미하는 접근 방법이다. 가장 마지막에 선택하는 가장 비효율적인 방법이다. 다른 DBMS 와 같이 InnoDB 도 풀 테이블 스캔이나 인덱스 풀스캔 과 같은 대량 디스크 I/O 작업을 위해 한꺼번에 많은 페이지를 읽어 들이는 기능을 제공한다. InnoDB 에서는 이 기능을 리드 어헤드(Read Ahead) 라고 한다. |
possible_keys
옵티마이저가 최적의 실행 계획을 만들기 위해 후보로 선정했던 인덱스 목록
특별한 경우를 제외하곤 무시해도 된다.
key
최종 선택된 실행 계획에서 사용하는 인덱스를 의미한다.
쿼리를 튜닝할 때는 key 컬럼에 의도한 인덱스가 표시되는 확인하는것이 중요하다.
type 컬럼이 index_merge 가 아닌 경우에는 반드시 테이블당 하나의
인덱스만 이용할 수 있다.
key_len
인덱스의 각 레코드에서 몇 바이트까지 사용했는지를 의미한다.
많은 사용자가 쉽게 무시하는 정보지만 사실은 매우 중요한 정보이다.
쿼리를 처리하기 위해 다중 칼럼으로 구성된 인덱스에서 몇 개의 컬럼까지
사용했는지를 알려준다.
ref
접근 방법(type)이 ref 면 참조 조건(Equal 비교 조건) 으로 어떤 값이
제공됐는지 보여준다.
상숫값을 지정했다면 ref 컬럼값은 const 로 표시되며 다른 테이블의 컬럼 값이면
그 테이블명과 컬럼명이 표시된다.
func 로 표시될 경우 Function 의 줄임말로 값을 그대로 사용한것이 아닌
콜레이션 변환이나 값 자체의 연을 거쳐 참조됐다는 것을 의미한다.
rows
실행 계획의 효율성 판단을 위해 예측했던 레코드 건수를 보여준다.
이 값은 각 스토리지 엔진별로 가진 통계 정보를 참조해 MySQL 옵티마이저가
산출해낸 예상값이라서 정확하지는 않다.
반환하는 레코드의 예측치가 아니라 쿼리를 처리하기 위해 얼마나 많은 레코드를 읽고
체크해야 하는지를 의미한다.
filtered
EXPLAIN 뒤에 EXTENDED 키워드가 필요하다.
필터링되어 버려지는 레코드의 비율이 아닌 필터링되고 남은 레코드의
추정 백분율을 의미한다.
Extra
컬럼 이름과는 달리 실행 계획에서 성능에 관련된 아주 중요한 내용이 Extra 컬럼에 표시된다.
옵티마이저가 실행 계획 방식에 대한 힌트로써 EXPLAN을 사용해 옵티마이저의 행동을 파악할 때 아주 중요한 컬럼이다.
일반적으로 2~3개씩 함께 표시된다.
주로 내부적인 처리 알고리즘에 대해 조금 더 깊이 있는 내용을 보여주는 경우가 많다.
| value | description |
|---|---|
| Using where | 접근 방식을 설명한 것으로, 테이블에서 행을 가져온 후 추가적으로 검색조건을 적용해 행의 범위를 축소한 것을 표시한다. MySQL은 내부적으로 크게 MySQL 엔진과 스토리지 엔진 두 개의 레이어로 나눠서 볼 수 있다. 스토리지 엔진은 디스크나 메모리 상에서 필요한 row를 읽거나 저장하는 역할을 하며, MySQL 엔진은 스토리지 엔진으로부터 받은 row를 가공 또는 연산하는 작업을 수행한다. MySQL 엔진 레이어에서 별도의 가공을 해서 필터링 작업을 처리한 경우 이 키워드가 나타난다. |
| Using index | 테이블에는 접근하지 않고 인덱스에서만 접근해서 쿼리를 해결하는 것을 의미한다. 커버링 인덱스로 처리됨 index only scan이라고도 부른다.데이터 파일을 전혀 읽지 않고 인덱스만 읽어서 쿼리를 모두 처리할 수 있을 때 나타나는 키워드이다. 매우 빠른 속도로 처리된다. |
| Using index for group-by | GROUP BY 처리가 인덱스를 이용할때 보통의 GROUP BY 절은 그룹핑 기준 컬럼을 이용해 정렬 작업을 수행하고 다시 정렬된 결과를 그룹핑하는 형태의 부하 작업을 필요하다. 하지만 GROUP BY 처리가 인덱스를 이용하면 정렬된 인덱스 컬럼을 순서대로 읽으면서 그룹핑 작업만 수행한다.이렇게 인덱스를 이용하면 row의 정렬이 필요로 하지 않고 인덱스의 필요한 부분만 읽으면 되기 때문에 상당히 효율적이고 빠르게 처리된다. GROUP BY 처리를 위해 인덱스를 읽는 방법을 "루스 인덱스 스캔"이라고 한다. |
| const row not found | type 컬럼이 const 인데 해당 테이블에 1건도 존재하지 않으면 표시된다. |
| Distinct | Distinct 사용시 나타난다. 같은 조건을 만족하는 또 다른 행이 있는지 다시 검색하지 않는다. |
| Using filesort | ORDER BY 처리가 인덱스를 사용하지 못할 때 나타난다. 이는 조회된 레코드를 정렬용 메모리 버퍼에 복사해 퀵소트 또는 힙소트 알고리즘을 이용해 정렬을 사용하게 된다는 의미이다. |
| Using temporary | 암묵적으로 임시 테이블이 생성된 것을 표시한다. 쿼리를 처리하는 동안 중간 결과를 담아두기 위해 임시 테이블(Temporary table)을 사용하면 이 키워드가 표시된다. 임시 테이블은 메모리 상에 생성될 수도 있고, 디스크 상에 생성될 수도 있다. 이때 사용된 임시 테이블이 메모리에 생성됐는지, 디스크에 생성됐는지는 실행 계획만으로는 알 수 없다. |
| Using index condition | Index Condition Pushdown(ICP) 최적화가 일어났음을 표시한다. 인덱스 범위 조건에 사용될 수 없어도 인덱스에 포함된 필드라면 스토리지 엔진으로 전달하여 최대한 스토리지 엔진에서 걸러낸 데이터만 MySQL 엔진에 전달 되었음을 의미한다. 또는 Where 조건절의 인덱스를 이용한 조건과 체크 조건이 있을 경우 체크 조건을 스토리지 엔진이 하도록 전달할때도 나타난다. |
| Using MRR | 멀티 레인지 리드(MRR) 최적화가 사용되었음을 표시한다. |
| ... | ... |
참조
