EXPLAIN 이란?
쿼리 실행 계획을 확인 하기 위해 사용한다.
EXPLAIN 키워드 뒤에 확인하고 싶은 쿼리 문장을 적으면 된다.
EXPLAIN을 실행하면 쿼리 문장의 특성에 따라 1줄 이상의 결과로 표시되고
쿼리 문장에서 사용된 테이블의 개수만큼 출력된다. 그리고 실행 순서는 위에서 아래로 순서대로 표시 된다.
필요에 따라 실행 계획을 산출하기 위해 쿼리의 일부분을 직접 실행할 때도 있어서 실행 계획 조회도 느려질 수 있다.
# EXPLAIN
EXPLAIN
SELECT * FROM `user` AS u
id 칼럼
- 단위 SELECT 쿼리별로 부여되는 식별자 값이다.
select_type 칼럼
- 각 단위 SELECT 쿼리가 어떤 타입의 쿼리인지 표시되는 칼럼이다.
1. SIMPLE
- UNION 이나 서브 쿼리를 사용하지 않는 단순한 SELECT 쿼리인 경우 표시
- 실행 계획에서 select_type 이 SIMPLE인 단일 쿼리는 반드시 하나만 존재한다.
2. PRIMARY
- UNION 이나 서브 쿼리를 가지는 쿼리의 실행 계획에서 가장 바깥쪽 에 있는 단위 쿼리에 표시
- SIMPLE 과 마찬가지로 PRIMARY 인 단위 SELECT 쿼리는 하나만 존재 한다.
3. UNION
- UNION 으로 결합하는 단위 SELECT 쿼리 가운데 첫 번째를 제외한 두번째 이후 단위 SELECT 쿼리의 표시
- UNION 의 첫번째 단위는 UNION 되는 쿼리 결과들을 모아서 저장하는 임시 테이블 (DERIVED) 로 표시
4. DEPENDENT(의존) UNION
- UNION 이나 UNION ALL 로 집합을 결합하는 쿼리이고 결합된 단위 쿼리가 외부에 의해 영향을 받을 때 표시
- DEPENDENT 키워드가 포함된 쿼리는 외부 쿼리에 의존적이므로 외부 쿼리보다 먼저 실행될 수 없다. 그래서 비효율적인 쿼리인 경우가 많다.
5. UNION RESULT
- UNION 결과를 담아두는 테이블을 의미
- 실제 쿼리에서 단위 쿼리가 아니기 때문에 별도로 id 값은 부여되지 않는다.
6. SUBQUERY
- FROM 절 의외에서 사용되는 서브 쿼리만을 의미
7. DEPENDENT SUBQUERY
- 서브 쿼리가 바깥쪽 SELECT 쿼리에서 정의된 칼럼을 사용하는 경우
- DEPENDENT UNION 과 같이 외부 쿼리가 먼저 수행된 후 내부 쿼리가 실행돼야 하므로 속도가 느릴 때가 많다.
8. DERIVED
- 단위 SELECT 쿼리의 실행 결과를 메모리나 디스크에 임시 테이블을 생성하는 것을 의미한다.
- 생성되는 임시 테이블을 파생 테이블이라고도 한다.
9. UNCACHEABLE SUBQUERY
- 서브 쿼리에 포함된 요소에 의해 캐시 자체가 불가능할 수가 있을때 표시
10. 기타
- UNCACHEABLE UNION
- MATERIALIZED
※ TIP
- 쿼리를 튜닝하기 위해 실행 계획을 확인할 때 가장 먼저 select_type 칼럼의 값이 DERIVED 인것이 있는지 확인하고
가능하다면 조인을 사용할 것을 강력히 권장한다.
- 서브쿼리가 하나만 있더라도 실제 그 서브 쿼리가 한번만 실행되는 것은 아니다. 그런데 조건이 똑같은 서브쿼리가 실행될 때는 실행하지않고 이전의 실행결과를 그대로 사용할 수 있게 서브쿼리의 결과를 내부적인 캐시 공간에 담아둔다.
table 칼럼
- 테이블 이름이 표시되고 만약 테이블의 이름에 별칭이 부여된 경우에는 별칭이 표시된다.
- <derived N> 또는 <union M,N> 과 같이 '<>' 로 둘러싸인 이름으로 명시되는 것은 임시테이블을 의미한다.
type 칼럼
- 각 테이블의 접근방식 ( Access type )
- 인덱스를 효율적으로 사용하는지 확인하는 것이 중요하므로 type 칼럼은 반드시 체크해야 할 중요한 정보다.
1. system
- 레코드가 1건만 존재하는 테이블 또는 한 건도 존재하지 않는 테이블을 참조하는 형태
- InnoDB 스토리지 엔진을 사용하는 테이블에서는 나타나지 않는다.
- 실제 서비스에서 사용되는 쿼리에서는 거의 보이지 않는다.
2. const
- 테이블의 레코드의 건수에 관계없이 쿼리가 PK 나 UNIQUE 키 칼럼을 이용하는 WHERE 조건절을 가지고 있으며 반드시 1건을 반환하는 쿼리의 처리 방식이다.
- PK, UNIQUE 키의 일부 칼럼만 조건으로 사용할 때는 1건이라는 것을 확실할 수 없기 때문에 const 타입이 아닌 ref 타입으로 사용한다.
3. eq_ref
- 조인에서 처음읽는 테이블의 칼럼값을 그다음 읽어야 할 테이블의 PK 나 UNIQUE 키 칼럼의 검색 조건에 사용할 때 두번째 이후에 읽는 테이블의 표시된다.
- 조인에서 두번째 이후에 읽는 테이블에서 반드시 1건만 존재한다는 보장이 있어야 사용할 수 있는 접근 방법이다.
- 여러 테이블이 조인되는 커리의 실행 계획에서만 표시된다.
4. ref
- 인덱스의 종류와 관계없이 동등(Equal) 조건으로 검색할 때 사용 된다.
- 조인의 순서와 관계없이 사용되며, PK 나 UNIQUE 키등의 제약조건도 없다.
- const 나 eq_ref 보다는 빠르지 않지만 동등한 조건으로만 비교되므로 빠른 레코드 조회 방법의 하나다.
5. fulltext
- 전문 검색(Fulltext) 인덱스를 사용해 레코드를 읽는 접근 방법을 의미한다.
- MATCH.. AGAINST 구문으로 사용할 때 사용 된다.
6. ref_or_null
- ref 접근 방식과 같은데, NULL 비교가 추가된 형태다.
- 실제로 많이 사용되지도 않고, 별로 존재감이 없는 접근방법이다.
7. unique_subquery
- WHERE 조건절에서 사용될 수 있는 IN (subquery) 형태의 쿼리를 위한 접근방식이다.
- 서브 쿼리에서 중복되지 않은 유니크한 값만 반환할 때 이 접근방법을 사용한다.
8. index_subquery
- IN 형태의 조건에서 subquery 의 반환 값에 중복된 값이 있을수 있지만 인덱스를 이용해 중복된 값을 제거할 수 있을때 사용된다.
9. range
- 인덱스를 하나의 값이 아니라 범위로 검색하는 경우에 사용한다.
- 주로 "> , <, IS NULL, BETWEEN, IN, LIKE" 등의 연산자를 이용해 인덱스를 검색할 때 사용된다.
- 이 접근방법도 상당히 빠르며 모든 쿼리가 이 접근 방법만 사용해도 어느 정도의 성능은 보장된다고 볼 수 있다.
10. index_merge
- 2개 이상의 인덱스를 이용해 각각의 검색결과를 만들어낸 후 그 결과를 병합하는 처리 방식이다.
- 주로 OR 연산자를 사용하였을 때 두개 조건이 모두 각각 다른 인덱스를 최적으로 사용할 수 있을 때 사용한다.
11. index
- 인덱스를 처음부터 끝까지 읽는 인덱스 풀 스캔을 의미한다.
- range 접근 방식과 같이 효율적으로 인덱스의 필요한 부분만 가져오는게 아니다.
- 풀 테이블 스캔 방식과 비교했을 때 비교하는 레코드 건수는 같다. 하지만 인덱스는 일반적으로 데이터 파일 전체보다는 크기가 작아서 풀 테이블 스캔보다는 빠르다.
12. ALL
- 풀테이블 스캔을 의미하는 접근 방식이다.
- 테이블을 처음부터 끝까지 전부읽어서 불필요한 레코드를 제거하고 반환한다.
- 다른 정근 방법으로는 처리할 수 없을때 가장 마지막에 선택되는 가장 비효율적인 방법이다.
possible_keys 칼럼
- 옵티마이저가 최적의 실행 계획을 만들기 위해 후보로 선정했던 접근 방식에서 사용되는 인덱스의 목록일뿐이다.
- 테이블의 모든 인덱스가 목록에 포함되어 나오는 경우가 허다하기에 쿼리를 튜닝하는데 아무런 도움이 되지않는다.
- 무시해도 무방하다.
key 칼럼
- 최종 선택된 실행 계획에서 사용하는 인덱스를 의미한다.
- 쿼리를 튜닝할 때는 Key 칼럼에 의도했떤 인덱스가 표시되는지 확인하는 것이 중요하다.
- type 카럼이 index_merge 가 아닌 경우에는 반드시 테이블 하나당 하나의 인덱스만 이용할 수 있다.
- 인덱스를 사용하지 못할 때는 NULL 로 표시된다.
key_len 칼럼
- 인덱스의 각 레코드에서 몇바이트까지 사용 했는지 알려주는 값이다.
- 다중 칼럼으로 구성된 인덱스에서 몇개의 칼럼까지 사용 했는지 알려준다.
- 쉽게 무시하는 정보지만 사실은 매우 중요한 정보 중 하나다.
ref 칼럼
- 접근 방법이 ref 방식이면 참조 조건 (Equal 비교조건) 으로 어떤 값이 제공 됐는지 보여준다.
- 상수 값을 지정했다면 ref 칼럼의 값은 const 로 표시되고, 다른 테이블의 칼럼값이면 그 테이블명과 칼럼 명이 표시된다.
- ref 칼럼의 값이 "func" 일경우에는 값 자체의 연산을 거쳐서 참조됐다는 것을 의미하기 때문에 조금 주의해서 볼 필요가 있다.
rows 칼럼
- 얼마나 많은 레코드를 읽고 비교해야 하는지 예측해서 비용을 산정한다.
- 반환하는 레코드의 예측치가 아니라 쿼리를 처리하기 위해 얼마나 많은 레코드를 디스크로부터 읽고 체크해야 하는지를 의미한다.
- 각 스토리지 엔진별로 가지고 있는 통계 정보를 참조해 옵티마이저가 산출해 낸 예상값이라서 정확하지 않다.
Extra 칼럼
- 칼럼의 이름과는 달리 중요한 내용이 Extra 칼럼에 자주 표시된다.
- 일반적으로 2~3개씩 같이 표시된다.
1. Using filesort
- ORDER BY 처리가 인덱스를 사용하지 못할 때 표시된다.
- 조회된 레코드를 정렬용 메모리 버퍼에 복사해 퀵 소트 알고리즘을 수행하게 된다는 의미이다.
- 많은 부하를 일으키므로 가능하다면 쿼리를 튜닝하거나 인덱스를 생성하는 것이 좋다.
2. Using Index (커버링 인덱스)
- 데이터 파일을 전혀 읽지않고 인덱스만 읽어서 쿼리를 모두 처리할 수 있을 때 표시 된다.
3. Using index for group-by
- GROUP BY 처리가 인덱스를 이용할 때 표시된다.
- GROUP BY 에서 인덱스를 사용하려면 우선 GROUP BY 조건에서 인덱스를 사용할 수 있는 요건이 갖춰줘야하고 그 이전에 WHERE 절에서 사용하는 인덱스에 의해서도 GROUP BY 절의 인덱스 사용 여부가 영향을 받는다는 사실이 중요하다.
4. Using index condition
- 두개의 조건을 이용해서 인덱스를 최대한 활용했으며 테이블 데이터 파일을 불필요하게 읽지 않았음을 의미한다.
- 이 최적화를 인덱스 컨데션 푸시다운 이라고도 한다.
5. Using join buffer
- 조인 버퍼가 사용되는 실행 계획에 표시된다.
※ JOIN BUFFER (조인 버퍼)
- 조인시 드라이빙 테이블 ( 조인에서 먼저 읽어야하는 테이블 )에서 읽은 레코드를 임시 공간에 보관해두고 필요할 때 재사용할 수 있게 해주는데 임시로 보관해두는 메모리 공간을 JOIN BUFFER 라고 한다.
6. Using temporary
- 쿼리를 처리하는 동안 중간 결과를 담아 두기 위해 임시 테이블 (Temporary table)을 사용하는데 이 때 표시 된다.
- "Using temporary" 가 표시되지는 않지만 실제 내부적으로 임시테이블을 사용할 때도 많기 때문에 "Using temporary" 표시되지 않았다고 해서 임시 테이블을 사용하지 않는다고 판단하지 않도록 주의해야 한다.
7. Using where
- MySQL DB 엔진 레이어에서 별도의 가공을 해서 필터링 작업을 처리한 경우에 표시된다.
- 스토리지 엔진에서 받은 레코드를 별도의 필터링이나 가공없이 그 데이터를 그대로 클라이언트를 전달하면 "Using where" 가 표시 되지 않는다.
8. const row not found
- const 접근 방식으로 테이블을 읽었지만 실제로 해당 테이블에 레코드가 1건도 존재 하지 않았을 경우 표시 된다.
9. Impossible HAVING
- HAVING 절의 조건을 만족하는 레코드가 없을 때 표시된다.
- 이 메시지가 출력된다면 쿼리가 제대로 작성되 못한 경우가 대부분이므로 쿼리의 내용을 다시 점검하는 것이 좋다.
10. Impossible WHERE
- WHERE 조건이 항상 FALSE가 될 수 밖에 없는 경우
EXPLAIN
SELECT * FROM user WHERE id = 1 AND id = 2
11. No matching min/max row
- MIN() 이나 MAX() 같은 집합 함수가 있는 쿼리의 조건절에 일치하는 레코드가 한 건도 없을 때 표시된다.
12. no matching row in const table
- 조인에 사용된 테이블에서 const 방식으로 접근할 때 일치하는 레코드가 없을 때 표시 된다.
13. No tables used
- FROM 절이 없는 쿼리 문장이나 "FROM DUAL" 형태의 쿼리 실행시 표시 된다.
EXPLAIN
SELECT UNIX_TIMESTAMP()14. Not exists
- 아우터 조인을 이용해 안티-조인을 수행하는 쿼리에서 표시된다.
- NOT EXISTS 형태의 쿼리로 변환해서 처리한게 아니라 내부적으로 어떤 최적화를 했는데 그 최적화의 이름이 "Not exists" 인 것이다.
※ Anti-Join
- 일반 조인(INNER JOIN) 을 했을 때 나오지 않는 결과만 가져오는 방법
15. Scanned N databases
- INFORMATION_SCHEMA DB 에서 메타 정보를 조회할 때 표시된다.
- 애플리케이션 운영시거의 사용할일이 없다.
16. Select tables optimized away
- MIN() 또는 MAX()만 SELECT 절에 사용되거나 GROUP BY 로 MIN(), MAX() 를 조회하는 쿼리가 적절한 인덱스를 사용할 수 없을때 인덱스를 오름차순 또는 내림차순으로 1건만 읽는 형태의 최적화가 적용될 떄 표시 된다.
17. unique row not found
- 두개의 테이블이 각각 유니크(PK 포함) 칼럼으로 아우터 조인을 수행하는 쿼리에서 아우터 테이블에 일치하는 레코드가 존재하지 않을 때 표시 된다.
※ 기타 정보
https://dev.mysql.com/doc/refman/5.7/en/explain-output.html
# EXPLAIN EXTENDED (Filtered 칼럼)
EXPLAIN EXTENDED
SELECT * FROM user WHERE level = 1
- 필터링이 얼마나 효율적으로 실행됐는지를 사용자에게 알려주기 위해 실행 계획에 Filtered 라는 칼럼이 있다
- Filtered 라는 칼럼을 함께 조회하려면 EXPLAIN 명령뒤에 "EXTENDED" 라는 키워드를 추가하면 된다.
- 필터링 되어 제거된 레코드는 제외하고 최종적으로 레코드가 얼마나 남았는지의 비율이 표시된다.
- 여기에서 출력되는 Filtered 값은 실제 값이 아니라 단순히 통계정보로부터 예측된 값이다.
- EXPLAIN EXTENDED 를 실행한 뒤 "SHOW WARNINGS" 명령을 실행하면 옵티마이저가 분석해서 다시 재조합한 쿼리 문장을 확인할 수 있다.
'MySQL' 카테고리의 다른 글
| MySQL 데이터 정의 언어 DDL (Data Definition Language) (0) | 2021.03.10 |
|---|---|
| MySQL 데이터타입 (DateType) (0) | 2021.03.04 |
