: 실행 계획을 이해해보자
1. id 칼럼
: 하나의 SELECT 문장은 1개 이상의 하위 SELECT 문장을 포함할 수 있다. 이는 각각의 SELECT 문장으로 해석될 수 있다.
SELECT ...
FROM (SELECT ... FROM tb_test1) tb, tb_test2 tb2
WHERE tb1.id=tb2.id;
SQL
복사
SELECT ... FROM tb_test1
SELECT ... FROM tb, tb_test2 tb2 WHERE tb1.id=tb2.id;
SQL
복사
: 실행 계획에서 가장 왼쪽에 표시되는 id 칼럼은 단위 SELECT 쿼리별로 부여되는 식별자 값, 즉 SELECT 문마다 매겨지는 식별자
: 조인 시, 조인하는 테이블의 개수만큼 실행 계획의 레코드가 표시되지만 ID는 모두 똑같다.
: 주의할 점은 실행 계획의 id 칼럼이 테이블의 접근 순서를 의미하지는 않는다는 것
2. select_type 칼럼
: 각 단위 SELECT 쿼리가 어떤 타입의 쿼리인지 표시되는 칼럼
1.
SIMPLE
: UNION이나 서브쿼리를 사용하지 않는 단순한 SELECT 쿼리인 경우, 해당 쿼리 문장의 select_type
2.
PRIMARY
: UNION이나 서브쿼리를 가지는 SELECT 쿼리의 실행 계획에서 가장 바깥족에 있는 단위 쿼리
3.
UNION
: UNION으로 결합하는 단위 SELECT 쿼리 가운데 첫 번째를 제외한 두 번째 이후 단위 SELECT 쿼리의 select_type, UNION의 첫 번째 단위 SELECT은 UNION이 아니라 UNION되는 쿼리 결과들을 모아서 저장하는 임시 테이블이 select_type으로 표시
4.
DEPENDENT UNION
: DEPENDENT UNION 또한 UNION select_type과 같이 UNION이나 UNION ALL로 집합을 결합하는 쿼리에서 표시, 여기서 DEPENDENT는 UNION이나 UNION ALL로 결합된 단위 쿼리가 외부 쿼리에 의해 영향을 받는 것을 의미
5.
UNION RESULT
: UNION RESULT는 UNION 결과를 담아두는 테이블을 의미하는데, 8.0버전에서는 UNION ALL은 임시 테이블을 사용하지 않고, UNION은 임시 테이블을 사용함
: UNION RESULT는 임시 테이블이므로 UNION에서만 표시됨
6.
SUBQUERY
: select_type의 SUBQUERY는 FROM 절 이외에서 사용되는 서브쿼리만을 의미, FROM 절에 서브쿼리를 사용해도 해당 테이블은 DERIVED로 표시, 파생 테이블은 DERIVED와 같은 의미로 이해하는 것이 편하다.
7.
DEPENDENT SUBQUERY
: 서브쿼리가 바깥쪽 SELECT 쿼리에서 정의된 칼럼을 사용하는 경우 표시되는 타입, DEPENDENT UNION과 같이 DEPENDENT SUBQUERY 또한 외부 쿼리가 먼저 수행된 후, 내부 쿼리가 실행되어야 하므로 DEPENDENT 키워드가 없는 일반 서브쿼리보다는 처리 속도가 느릴 때가 많다.
8.
DERIVED
: 8.0버전부터는 FROM 절의 서브쿼리에 대한 최적화가 많이 개선되어 불필요한 서브쿼리는 가능하다면 조인으로 재작성하여 처리
9.
DEPENDENT DERIVED
: 8.0버전부터는 LATERAL JOIN 기능이 추가되면서 FROM 절의 서브쿼리에서도 외부 칼럼을 참조할 수 있게 되었음, 즉 서브쿼리가 밖의 값과 비교 등을 수행할 때 사용 가능..!
: DEPENDENT DERIVED 키워드는 해당 테이블이 LATERAL JOIN으로 사용되었음을 의미
10.
UNCACHEABLE SUBQUERY
: 하나의 쿼리 문장에 서브쿼리가 하나만 있더라도 실제 그 서브쿼리가 한 번만 실행되는 것은 아닌데, 이때 그 반복된 경우에 대하여 이전의 실행 결과를 그대로 사용할 수 있게 내부적인 캐시 공간에 담아두는데, 이때 표시되는 select_type
11.
UNCACHEABLE UNION
: UNCACHEABLE UNION이란 UNION과 UNCACHEABLE의 속성이 혼합된 select_type
12.
MATERIALIZED
: FROM 절이나 IN(subquery) 형태의 쿼리에 사용된 서브쿼리의 최적화를 위해 사용, MATERIALIZED 키워드는 DERIVED와 비슷하게 쿼리의 내용을 임시 테이블로 생성하는 것
3. table 칼럼
: MySQL 서버의 실행 계획은 단위 SELECT 쿼리 기준이 아닌 테이블 기준으로 표시, 테이블에 별칭이 부여된 경우, 별칭이 표시됨
EXPLAIN SELECT NOW();
EXPLAIN SELECT NOW() FROM DUAL;
SQL
복사
: 두 예제 중 첫 번째 쿼리는 FROM 절 자체가 없으며 두 번째 쿼리는 FROM 절에 DUAL이라는 테이블이 사용되었음, 실제 DUAL이라는 테이블은 없지만 이 쿼리는 오류를 발생시키지
않음, 오라클 RDBMS에 익숙한 사용자를 위해 MySQL 서버는 내부적으로 DUAL이라는 테이블이 있는 것처럼 작동할 뿐임
: 실제로 MySQL 옵티마이저는 두 번째 쿼리가 요청되면 FROM DUAL 부분을 제거하고 첫 번째 쿼리와 동일하게 변형해서 처리함
: 위의 두 예제와 같이 별도의 테이블을 사용하지 않는 SELECT 쿼리인 경우에는 table 칼럼에 NULL이 표시
4. partitions 칼럼
: 8.0버전 부터는 EXPLAIN 명령으로 파티션 관련 실행 계획까지 모두 확인할 수 있게 변경됨
: 파티션 테이블을 생성한 후, 특정 값을 기준으로 나누어 저장하면 실행 계획 확인 시, 참고할 수 있음
: 파티션 테이블은 MySQL을 포함한 대부분의 RDBMS에서 물리적으로 개별 테이블처럼 별도의 저장공간을 가지기 때문에 풀 테이블 스캔으로 테이블의 일부를 읽을 수 있음
5. type 칼럼
: type 이후의 칼럼은 MySQL 서버가 각 테이블의 레코드를 어떤 방식으로 읽었는지를 나타냄, 여기서 방식은 인덱스를 사용해 인덱스를 읽었는지 테이블을 처음부터 읽었는지 등
: 일반적으로 쿼리를 튜닝할 때 인덱스를 효율적으로 사용하는지 확인하는 것이 중요하므로 실행 계획에서 type 칼럼은 반드시 체크해야할 중요한 정보
: MySQL의 메뉴얼에서 type 칼럼을 조인 타입으로 소개, 또한 MySQL에서는 하나의 테이블로부터 레코드를 읽는 작업도 JOIN처럼 처리함
1.
system
: 레코드가 1건만 존재하거나 한 건도 존재하지 않는 테이블을 참조하는 형태의 접근법, InnoDB에선 나타나지 않고 MyISAM이나 MEMORY 테이블에서만 사용되는 접근법
2.
const
: 테이블의 레코드 건수와 관계없이 쿼리가 프라이머리 키나 유니크 키 칼럼을 이용하는 WHERE 조건절을 가지고 있으며 반드시 1건을 반환하는 쿼리의 처리 방식
: 유니크 인덱스 스캔이라고도 표현, 다중 칼럼으로 구성된 프라이머리 키나 유니크 키 중에서 인덱스의 일부 칼럼을 조건으로 사용시에는 사용할 수 없음, 이 경우 ref로 표현
: 하지만 유니크 인덱스나 프라이머리 키의 모든 칼럼을 동등 조건으로 WHERE 절에 명시하면 const 접근 방법을 사용함
: 옵티마이저가 실행 계획 단계에서 상수화하므로 const라고 표현함
3.
eq_ref
: 여러 테이블이 조인되는 쿼리의 실행 계획에서만 표시되는 방법, 조인에서 처음 읽은 테이블의 칼럼값을 그 다음 읽어야할 테이블의 프라이머리 키나 유니크 키 칼럼의 검색
조건에 사용할 때를 가리겨 eq_ref라고 함
⇒ 즉 조인에서 두 번째 이후에 읽어지는 테이블에서 반드시 1건만 존재한다는 보장이 있어야 사용할 수 있는 접근 방법
4.
ref
: eq_ref와는 달리 조인의 순서와 관계없이 사용되며 프라이머리 키나 유니크 키 등의 제약 조건도 없음, 인덱스의 종류와 관계없이 동등 조건으로 검색할 때 ref 접근 방법이
사용, 반환되는 레코드가 1건이라는 보장이 없으므로 const나 ea_ref보다는 느리지만 동등한 조건으로 비교되므로 매우 빠른 축에 속하는 조회 방법
⇒ 이 방법들은 성능상의 문제를 크게 일으키지 않는 접근 방법으로 튜닝 시에도 크게 신경쓰지 않고 넘어가도 됨
5.
fulltext
: MySQL 서버의 전문 검색 인덱스를 사용해 레코드를 읽는 접근 방법을 의미, 일반적으로 전문 검색 조건을 사용하면 MySQL 서버는 fulltext 접근 방법을 주로 사용하나
저자의 경험으로 보았을 때 전문 검색 인덱스를 이용하는 fulltext보다 일반 인덱스를 이용하는 range 접근 방법이 더 효율적인 경우가 많았으니 전문 검색 쿼리를 사용할 땐
조건별로 성능을 확인해 보는 편이 좋음
6.
ref_or_null
: ref 접근 방법과 같으나 NULL 비교가 추가된 형태, 나쁘지 않은 접근 방법 정도로 기억해두면 충분
7.
unique_subquery
: WHERE 조건절에서 사용될 수 있는 IN(subquery) 형태의 쿼리를 위한 접근 방법, unique_subquery의 의미 그대로 서브쿼리에서 중복되지 않은 유니크한 값을 반환할 때 사용
8.
index_subquery
: IN 연산자의 특성상 대상이 되는 목록의 값에 중복된 값이 먼저 제거되어야 함, unique_subquery의 경우 중복된 값을 만들어내지 않는 다는 보장이 있으므로 별도의 중복을
제거할 필요가 없었으나 만약 가능성이 존재하는 칼럼에 대해 질의를 해야할 경우, 서브쿼리 결과의 중복된 값을 인덱스를 이용해서 제거할 수 있을 때 사용되는 방법
•
unique_subquery: IN 형태의 조건에서 subquery의 반환 값에 중복이 없으므로 별도의 중복 제거가 필요하지 않음
•
index_subquery: IN 형태의 조건에서 subquery의 반환 값에 중복된 값이 있을 수 있지만 인덱스를 이용해 제거할 수 있음
9.
range
: range는 익히 알고 있는 인덱스 레인지 스캔 형태의 접근 방법 주로 “<, >, IS NULL, BETWEEN, IN, LIKE” 등의 연산자를 이용해 인덱스를 검색할 때 주로 사용
: 우선순위가 상당히 낮으나 일반적으로 매우 빠른 방법으로 모든 쿼리가 이 접근 방법만 사용해도 최적의 성능이 보장된다고 할 수 있음
인덱스 레인지 스캔은 const, ref, range를 지칭하는 것임을 기억하자
10.
index_merge
: 2개의 이상의 인덱스를 이용해 각 검색 결과를 만들어낸 후, 그 결과를 병합해서 처리하는 방식, 다만 그렇게 효율적이지 않는데 다음과 같은 특징때문이다.
•
여러 인덱스를 읽어야 하므로 일반적으로 range 접근 방법보다 효율성이 떨어짐
•
전문 검색 인덱스를 사용하는 쿼리에서는 index_merge가 사용되지 않음
•
index_merge 접근 방법으로 처리된 결과는 항상 2개 이상의 집합이 되기 때문에 그 두 집합의 교집합이나 합집합, 중복 제거 등의 부가작업이 더 필요함..
11.
index
: 많은 사람들이 오해하는 접근 방법, index라서 인덱스를 효율적으로 사용하는 구나!라고 생각할 수 있지만 인덱스 풀 스캔 방법을 의미함
: range처럼 필요한것만 읽는, 썩 효율적이진 않다는 것을 기억하자
: 풀 테이블 스캔과 읽어야할 레코드 건수는 동일하나 일반적으로 데이터 파일 전체보다 크기가 작으므로 더 빠르게 처리되며 쿼리의 내용에 따라 정렬된 인덱스의 특성을
이용할 수 있으므로 훨씬 효율적이라고 할 수 있음
: index 방법은 다음과 같은 경우 처리되는 방법
•
range나 const, ref같은 방법으로 인덱스를 사용할 수 없을 때
◦
인덱스에 포함된 칼럼만으로는 처리할 수 없는 쿼리인 경우
◦
인덱스를 이용해 정렬이나 그루핑 작업이 가능한 경우
12.
ALL
: 풀 테이블 스캔을 의미하는 접근 방법, 일반적으로 index나 ALL 접근 방법은 작업 범위를 제한하는 조건이 아니므로 빠른 응답이 필요한 웹 서비스 등과 같은 온라인
트랜잭션 처리 환경에는 적합하지 않음, 테이블이 매우 작지 않다면 실제로 테이블에 데이터를 어느 정도 저장한 상탱세ㅓ 쿼리의 성능을 확인해 보고 적용하는 것이 좋음
6. possible_keys 칼럼
: 옵티마이저가 실행 계획을 최적으로 만들기 위해 후보로 선정했던 접근 방법에서 사용되는 인덱스의 목록
7. key 칼럼
: 최종 선택된 실행 계획에서 사용하는 인덱스를 의미, 쿼리를 튜닝할 때 key 칼럼에 의도했던 인덱스가 표시되는 지 확인하는 것이 중요함
8. key_len 칼럼
: 많은 사용자가 쉽게 무시하는 정보지만 사실은 매우 중요한 정보 중 하나, 실제 업무에서 사용하는 테이블은 단일 칼럼보다 다중 칼럼으로 만들어진 인덱스가 더 많음
실행 계획의 key_len 칼럼의 값은 쿼리를 처리하기 위해 다중 칼럼으로 구성된 인덱스에서 몇 개의 칼럼까지 사용했는지 알려줌, 정확하게는 다중 칼럼으로 구성된 인덱스의
각 레코드에서 몇 바이트까지 사용했는지 알려주는 값
9. ref 칼럼
: 접근 방버비 ref면 참조 조건으로 어떤 값이 제공됐는지 보여줌, 상숫값을 지정했다면 const로 표시되고 다른 테이블의 칼럼값이면 그 테이블명과 칼럼명이 표시
: 이 칼럼에 출력되는 내용은 크게 신경쓰지 않아도 무방하나 다음과 같은 케이스는 주의해야할 필요가 있음
•
ref 칼럼의 값이 func인 경우
◦
참조용으로 사용되는 값을 그대로 사용하는 것이 아니라 콜레이션 변환이나 값 자체의 연산을 거쳐서 참조됐다는 것을 의미
◦
MySQL 서버가 내부적으로 값을 변환해야할 때
◦
문자집합이 일치하지 않는 두 문자열 칼럼을 조인한다거나 숫자 타입의 칼럼과 문자열 타입의 칼럼으로 조인할 때
: 되도록이면 이런 변환이 발생하지 않도록 조인 칼럼의 타입은 일치시키는 편이 좋다.
10. rows 칼럼
: 실행 계획의 효율성 판단을 위해 예측했던 레코드 건수를 보여주는데 이 값은 스토리지 엔진별로 가지고 있는 통계 정보를 참조해 옵티마이저가 예상한 값으로 정확하지는 않다.
11. filtered 칼럼
: 옵티마이저는 각 테이블에서 일치하는 레코드 개수를 정확히 파악해야 좀 더 효율적으로 실행 계획을 수립할 수 있는데, rows 칼럼의 값은 인덱스를 사용하는 조건에만 일치하는
레코드 건수를 예측한 것, filtered 칼럼에 표시되는 값이 얼마나 정확히 예측될 수 있느냐에 따라 조인의 성능이 달라짐
12. Extra 칼럼
: 쿼리의 실행 계획에서 성능에 관련된 중요한 내용이 Extra 칼럼에 자주 표시되며 Extra 칼럼에는 고정된 몇 개의 문장이 표시되는데, 일반적으로 2~3개씩 함께 표시됨
주로 내부적인 처리 알고리즘에 대해 조금 더 깊이 있는 내용을 보여주는 경우가 많음
1.
const row not found
: const 접근 방법으로 테이블을 읽었으나 실제로 해당 테이블에 레코드가 1건도 존재하지 않는 경우
2.
Deleting all rows
: MyISAM 스토리지 엔진과 같이 스토리지 엔진의 핸들러 차원에서 테이블의 모든 레코드를 삭제하는 스토리지 엔진 테이블인 경우 표시
WHERE 조건절이 없는 DELETE 문장의 실행 게획에서 자주 표시되며 모든 레코드를 삭제하는 기능이 핸들러 기능을 통해 처리되었음을 의미
3.
Distinct
: 조회 절에 DISTINCT 사용시 표시됨
4.
FirstMatch
: 세미 조인의 여러 최적화 중에서 FirstMatch 전략이 사용되었을 때 표시됨, FirstMatch 메시지에 함께 표시되는 테이블명은 기준 테이블을 의미함
5.
Full scan on NULL Key
: IN이나 NOT IN 연산이 조회에 사용되면 표시될 수 있으며 풀 테이블 스캔이 발생했음을 표시
6.
Impossible HAVING
: 쿼리에 사용된 HAVING 절의 조건을 만족하는 레코드가 없을 때 표시됨
7.
Impossible WHERE
: WHERE 조건이 항상 FALSE가 될 수 밖에 없는 경우 표시
8.
LooseScan
: 세미 조인 최적화 중, LooseScan 최적화 전략이 사용되면 실행 계획에 표시됨
9.
No matching min/max row
: MIN()이나 MAX()와 같은 집합 함수가 있는 쿼리의 조건절에 일치하는 레코드가 한 건도 없을 때 표시
10.
no matching row in const table
: 조인에서 사용된 테이블에서 const 방법으로 접근할 때 일치하는 레코드가 없는 경우
11.
No matching rows after partition pruning
: 파티션된 테이블에 대한 UPDATE, DELETE 명령의 실행 계획에서 표시될 수 있는데 대상 레코드가 존재하지 않을 경우 표시됨
12.
No tables used
: FROM 절이 없는 쿼리 문장이나 FROM DUAL 형태의 쿼리 실행 계획에서 표시
13.
Not exists
: NOT IN 형태나 NOT EXISTS와 같이 A 테이블에 존재하지만 B 테이블에 존재하지 않는 경우에 대해 조회할 때를 안티 조인이라고 하는데, 이를 아우터 조인을 이용해 구현할 수 있음, 이때 표시되는 메시지
14.
Plan isn’t ready yet
: EXPALIN FOR CONNECTION 명령을 실행했을 때, 표시될 수 있는데 이는 해당 커넥션에서 아직 실행 계획을 수립하지 못했을 때
15.
Range checked for each record(index map:N)
: 레코드마다 인덱스 레인지 스캔을 체크하는 경우 표시
16.
Recursive
: 8.0부터는 CTE를 이용해 재귀 쿼리를 작성할 수 있게되었는데 이때 표시됨
17.
Rematerialize
: 레터럴 조인 기능을 사용했을 때 표시
18.
Select tables optimized away
: MIN() 또는 MAX()만 SELECT 절에 사용되거나 GRUOP BY로 MIN(), MAX()를 조회하는 쿼리가 인덱스를 오름차순 또는 내림차순으로 한 건만 읽는 형태의 최적화가 적용
19.
Start temporary, End temporary
: 세미 조인 최적화 중에서 Duplicated Weed-out 최적화 전략이 사용되는 경우
20.
unique row not found
: 두 개의 테이블이 각각 유니크 칼럼으로 아우터 조인을 수행하는 쿼리에서 아우터 테이블에 일치하는 레코드가 존재하지 않을 때 표시
21.
Using filesort
: ORDER BY를 처리할 때, 인덱스를 사용하지 못할 때
22.
Using index(커버링 인덱스)
: 데이터 파일을 전혀 읽지 않고 인덱스만 읽어서 쿼리를 모두 처리할 수 있을 때
23.
Using index condition
: 인덱스 컨디션 푸시 다운 최적화를 사용했을 때 표시
24.
Using index for group-by
: GROUP BY 처리가 인덱스를 이용할 때
a.
타이트 인덱스 스캔을 통한 GROUP BY 처리
: AVG(), SUM(), COUNT()처럼 조회하려는 값이 모든 인덱스를 다 읽어야할 때는 사용할 수 없으며 표시되지 않음
b.
루스 인덱스 스캔을 통한 GROUP BY 처리
: 단일 칼럼으로 구성된 인덱스에서는 그루핑 칼럼 말고는 아무것도 조회하지 않는 쿼리에서 루스 인덱스 스캔을 사용할 수 있음
25.
Using index for skip scan
: 옵티마이저가 인덱스 스킵 스캔 최적화를 사용했을 때 표시
26.
Using join buffer(Block Nested Loop), Using join buffer(Batched Key Access), Using join buffer(hash join)
: 조인이 수행될 때 드리븐 테이블의 조인 칼럼에 적절한 인덱스가 있다면 아무 문제가 없으나 없을 경우, 사용되는 전략
27.
Using MRR
: 스토리지 엔진은 MySQL 엔진이 넘겨주는 키 값을 기준으로 레코드를 한 건 한 건 읽어서 반환하므로 이를 보완하기 위한 최적화 전략
28.
Using sort_union(…), Using union(…), Using intersect(…)
: 쿼리가 index_merge 방법으로 실행되는 경우, 2개 이상의 인덱스가 사용될 수 있는데 이때 상세하게 어떻게 처리되었는지 출력하는 전략
29.
Using temporary
: 쿼리를 처리하는 동안 임시테이블을 사용했을 때 출력
30.
Using where
: MySQL 엔진에서 별도의 필터링이나 가공을 통해 데이터를 클라이언트에게 전달해야할 경우 표시
31.
Zero limit
: LIMIT을 사용하는 경우 표시
