Explain
많은 양의 데이터를 조회하다보면 조회 속도가 느려지기 마련이다. 이러한 문제를 조금이라도 줄이기 위해서는 쿼리를 튜닝하거나 스키마를 재설계해야 한다. 하지만 스키마를 재설계하게 되면 연계된 테이블과 해당 테이블을 사용하는 쿼리에 영향이 가게 된다. 그래서 보통 쿼리를 튜닝하는데, 이 튜닝을 할 때 사용하는 것이 Explain이다.
쿼리를 튜닝하기 전에 실행 계획 (explain)을 사용해서 어떤 쿼리가 비효율적인지 확인하는 것이다. (참고로 explain은 ‘설명하다’ 라는 뜻을 가지고 있다.)
단, EXPLAIN은 실제 실행 결과가 아닌 실행 전 예측에 기반하여 결과를 도출한다. 따라서 항상 최적의 실행 계획을 보장하지 않으며 옵티마이저에게 제공되는 통계 정보 (데이터 분포도와 인덱스 정보 등)가 부정확한 경우에는 잘못된 실행 계획을 세울 가능성이 높다.
SQL 수행 절차
- 사용자가 SQL을 실행한다.
- DB 엔진의 컴포넌트 중 하나인 파서 (parser)에서 요청된 쿼리를 토큰으로 분리하여 쿼리의 문법이 올바른지 확인한다.
- 여기서 토큰은 DB가 인식할 수 있는 최소 단위의 어휘를 의미한다.
- 파서에서 작성된 트리는 전처리기 (preprocessor)로 넘어오게 된다. 전처리기에서는 트리의 구성 요소로 권한과 존재의 여부를 확인한다.
- 여기서는 쿼리의 구조적 문제를 확인하기 위해 파서에서 넘어온 트리를 분석한다. 쿼리에 사용된 토큰을 내장 함수나 테이블 이름, 컬럼명 등에 매핑해 유효성을 확인하고 쿼리를 실행할 수 있는 권한이 있는지 확인한다.
- 전처리기에서 검증된 쿼리는 옵티마이저 (optimizer)로 넘어온다.
- 옵티마이저는 쿼리를 효율적으로 처리하기 위한 실행 계획을 결정하는 컴포넌트다. 종류는 CBO와 RBO 두 가지가 있으며, MySQL의 경우에는 기본적으로 CBO를 사용한다.
- CBO : Cost Based Optimizer는 디스크 접근 횟수 등 비용을 기준으로 실행 계획을 세우는 방식이다.
- RBO : Rule Based Optimizer는 미리 정의된 규칙을 기준으로 실행 계획을 세우는 방식이다.
- 옵티마이저의 실행 계획은 엔진 실행기 (engine executor)로 전달된다. 엔진 실행기는 스토리지 엔진 (Storage Engin)의 데이터 파일에서 데이터를 가져와 사용자에게 최종 결과를 수신한다.
사용 방법과 설명
1
| EXPLAIN [ANALYZE] SELECT (컬럼) FROM (테이블)
|
항목별 설명
아래는 explain으로 조회된 결과값의 컬럼별 설명이다.
id
- id는 실행 계획에서 각 쿼리 단계의 순서를 나타낸다.
- 값이 같으면 같은 단계에서 실행되는 것을 의미하며, 다른 값이면 별도의 쿼리나 서브 쿼리가 실행되는 것이다.
- 값이 1이면 최상위 쿼리이고 숫자가 낮아질수록 서브 쿼리 등의 하위 쿼리에서 실행되는 부분을 의미한다.
select_type
- 쿼리에서 사용되는 선택자의 유형을 나타내며, 종류는 아래와 같다.
| 구분 | 설명 |
|---|
| SIMPLE | 단순 SELECT문으로, 서브쿼리나 조인, UNION이 없는 쿼리를 말한다. |
| PRIMARY | 서브쿼리나 UNION이 포함된 최상위 SELECT문 |
| UNION | UNION 연산자를 사용한 쿼리문이다. 첫 번째 쿼리는 PRIMARY, 이후 UNION으로 연결된 SELECT문이 UNION으로 표시된다. |
| DEPENDENT_UNION | UNION을 사용할 때 하나의 쿼리가 다른 쿼리에 의존적일 경우 표시된다. 즉, UNION의 일부 쿼리가 외부 쿼리의 결과에 따라 달라지는 경우를 말한다. |
| UNION_RESET | UNION 결과를 담아두는 임시 테이블로, 실제 쿼리에서 단위 쿼리가 아니기 때문에 별도의 ID값이 부여되지 않는다. (NULL로 표시됨) |
| SUBQUERY | 쿼리 내에 서브 쿼리가 있을 때 해당 서브 쿼리를 실행하는 부분으로, 서브쿼리의 결과를 외부 쿼리에서 사용하거나 비교할 때 발생한다. |
| DEPENDENT_SUBQUERY | 서브쿼리가 외부 쿼리의 컬럼값에 의존하는 경우로, 서브쿼리의 실행이 외부 쿼리의 각 행에 대해 실행될 때 발생한다. |
| DERIVED | 서브쿼리가 FROM 절에 포함되어 테이블처럼 사용될 때 (==인라인 뷰) 나타난다. 서브쿼리가 파생된 테이블로 취급되면 그 서브쿼리는 DERIVED로 표시된다. |
| UNCACHEABLE_SUBQUERY | 서브쿼리가 캐시되지 않는 경우를 말한다. MYSQL은 서브쿼리 결과를 일시적으로 저장해서 재사용할 수 있는데, DEPENDENT_SUBQUERY거나, 비결정 함수 (RAND(), NOW() 등)가 사용되거나, 서브쿼리 내에서 결과를 정렬 (ORDER BY)하거나, 일부만 반환 (LIMIT 등)하거나 서브쿼리가 동적이거나 호출될 때마다 결과가 달라지는 경우에는 캐시할 수 없다. 이때는 서브쿼리를 JOIN으로 변경하거나 인덱스를 활용해 성능을 개선할 수 있다. |
| UNCACHEABLE_UNION | UNION 결과가 캐시되지 않는 겨우를 말한다. 이는 UNION에 포함된 쿼리가 외부 쿼리 결과에 의존하거나, ORDER BY, LIMIT 등이 UNION 내에서 사용되었거나 비결정 함수가 사용될 경우 캐시할 수 없다. 이때는 UNION 대신 UNION ALL을 쓰거나, 서브쿼리 최적화, 조인 방식 변경 등으로 성능을 개선할 수 있다. |
| METERIALIZED | 서브쿼리가 물리적으로 저장되어 실행되는 경우에 나타난다. 서브쿼리가 먼저 계산되고 그 결과가 물리적으로 저장되었다가 사용될 때 발생한다. |
table
- 데이터를 조회하는 테이블 이름을 나타낸다.
- 테이블의 원래 이름이 아닌 축약어가 조회되거나, 서브쿼리일 때는
<subquery#>이 출력된다.<>로 둘러싸인 이름은 임시 테이블을 의미하며 #은 서브쿼리가 참조하는 단위 select id를 의미한다.- 만약 table의 결과값이
<test2>라면 id가 2인 단위 쿼리를 수행한 뒤 실행된다는 의미다.
partitions
- 쿼리 실행 계획에 관련된 파이션 정보를 표시한다.
- 파티션이 적용된 테이블에서 옵티마이저가 어떤 파티션에 접근하는 지 알 수 있다.
- 만약 where 절에 조건이 없거나 (
select * from t_test;) 파티션 프루닝을 적용할 수 없는 쿼리 (select * from t_test where id = 1;) 라면 옵티마이저는 Full Table Scan을 실행한다. 이 경우에는 모든 파티션을 대상으로 작업하고 있다는 의미의 ALL이 type 컬럼에 표시된다. - 다만,
select * from t_test where new_date between '2025-01-01' and '2026-01-01'; 같이 파티션을 사용하는 경우, 옵티마이저는 위 조건의 데이터를 포함하는 파티션만 선택한다.- 이때, explain의 partitions 결과가 p1, p2처럼 표시되고 type이 all이라면, 이 두 파티션만 Full Scan을 진행하고 나머지 파티션은 조회하지 않는다.
파티션 프루닝 partition pruning은 DB에서 쿼리 성능 최적화를 위해 쿼리 조건에 따라 옵티마이저가 불필요한 파티션을 제외하는 과정을 말한다.
type
- 옵티마이저가 어떤 방법으로 row를 조회하는지를 나타내는 부분으로, 데이터 접근 방식을 표시한다.
- all 외의 접근 방식은 모두 인덱스를 사용한다. 만약 all이 반환될 경우에는 해당 쿼리로 사용할 수 있는 적절한 인덱스가 없다는 의미일 수도 있다.
| 구분 | 설명 |
|---|
| system | 테이블에 단 한 개의 데이터만 있는 경우로, 가장 빠르게 접근 가능하다. |
| const | select에서 기본키나 유니크 키를 사용해 조회하는 경우로, 성능이 가장 좋다. 단, 인덱스를 통한 범위검색은 const가 아니다. |
| eq_ref | 정확히 일치하는 하나의 행을 찾기 위해 인덱스를 사용할 경우 표시된다. 조인 수행 시 가장 유리한 경우다. |
| ref | 특정 값을 찾기 위해 인덱스를 사용해 범위 검색을 수행할 경우 표시된다. |
| ref_or_null | is null 구문에 대해 인덱스를 활용하도록 최적화된 방식이며 ref와 유사하다. 검색할 null 데이터가 적거나, 해당 방식을 활용할 때 비효율적일 경우 튜닝 대상이다. |
| index_merge | 두 개의 인덱스를 동시해 사용해서 검색이 이루어질 경우를 말한다. |
| unique_subquery | in 서브 쿼리 접근에서 기본 키 또는 고유 키를 사용한 경우를 말한다. 이 방식은 오버헤드를 줄여주기 때문에 속도가 빠르다. |
| index_subquery | unique_subquery와 유사하지만 고유 인덱스를 사용하지 않는다는 점이 다르다. |
| range | 특정 범위 내에서 인덱스를 사용해 범위 비교를 할 때 표시된다. 데이터가 방대하지 않다면 단순 조회 시에는 성능이 나쁘지 않다. 다만 스캔 범위가 넓으면 성능 저하의 요인이 될 수 있다. |
| index | 인덱스를 풀스캔 해 검색했을 경우를 나타낸다. all보다는 빠르다. (주로 인덱스를 포함한 컬럼만으로 처리할 수 있는, 즉 데이터 파일을 읽지 않아도 되는 경우나 인덱스를 이용해 정렬이나 그룹핑 작업이 가능한 경우, 즉 별도의 정렬 작업을 피할 수 있는 경우에 발생한다.) |
| all | 테이블을 풀스캔 해 검색했을 경우를 나타낸다. 검색 데이터가 전체 데이터의 20% 이상일 때는 index보다 성능이 좋을 때도 있다. |
possible_keys
- 옵티마이저가 sql문을 최적화할 때 사용할 수 있는 인덱스 목록이다.
key
- 옵티마이저가 SQL 최적화를 위해 사용한 기본 키 또는 인덱스명이다.
key_len
- 인덱스의 길이 (바이트 수)로, 인덱스가 사용하는 열의 크기를 나타낸다. 너무 길면 비효율적일 수 있다.
- 인덱스가 사용하는 열의 크기를 나타내기 때문에 풀 스캔일 경우 null이 반환된다.
ref
- reference의 축약어로, 테이블 조인 시 어떤 조건으로 해당 테이블에 액세스 되었는지 알려준다.
- 즉, 키 컬럼에 나와있는 인덱스에서 값을 찾기 위해 선행 테이블의 어떤 컬럼이 사용되었는지를 알려준다.
- 여기서 const는 테이블 레코드 건수에 관계없이 쿼리가 기본키나 유니크 키 컬럼을 이용하는 where 조건절을 가지며, 반드시 한 건을 반환하는 쿼리의 처리 방식을 의미한다.
rows
- 각 단계에서 예상되는 조회된 행 수를 나타낸다. Explain을 단독으로 썼을 경우에는 통계 정보를 바탕으로 한 예측값을 표시하기 때문에 만약 SQL의 최종 결과 건 수 와 row 수가 많이 차이날 경우에는 불필요한 조회가 존재하는 것이기 때문에 튜닝이 필요하다.
filtered
- SQL문을 통해 DB 엔진이 스토리지 엔진으로부터 가져온 데이터를 필터 조건에 따라 제거된 비율이다 (%)
- 필터 조건은 where 조건이며, 스토리지 엔진에서 10건을 가져왔고 DB 엔진의 필터 조건을 거쳐 1건이 필터링된다면 filtered 값은 10.00이 된다.
- 다만 통계 값을 바탕으로 계산한 값이기 때문에 반드시 일치하지 않는다.
- 필터가 제대로 동작하지 않을 경우에는 테이블 등을 ANALYZE해서 분석을 해야 한다.
- 옵티마이저가 SQL문을 어떻게 해석해 수행할 것인지 추가 정보를 보여주는 항목이다.
- 쿼리 실행 중 추가적인 작업이나 주요 최적화 방법을 설명한다.
- Using where : where 절의 필터 조건을 사용해 DB 엔진으로 가져온 데이터를 추출하는 경우를 말한다. 테이블에서 행을 가져온 후 추가적인 검색 조건으로 행의 범위를 축소한 것을 표시한다.
- Using index : 커버링 인덱스로, index only scan이라고도 부른다. 테이블 접근 없이 인덱스만으로 쿼리를 해결했음을 의미한다.
| 구분 | 설명 |
|---|
| Using where | where절에 필터 조건을 사용해 스토리지 엔진에서 DB 엔진으로 데이터를 추출한 경우로, 테이블에서 행을 가져온 후 추가적인 검색조건으로 행의 범위를 축소한 것을 표시한다. |
| Using index | 커버링 인덱스 라고도 하며, index only scan 이라고도 부른다. 테이블 접근 없이 인덱스에만 접근해 쿼리를 해결하는 것을 말한다. |
| Distinct | 중복을 제거하는 distinct 키워드나 union 구문이 포함된 경우 표시된다. |
| Using index for group-by | using index와 유사하지만 group by가 포함된 쿼리를 커버링 인덱스로 해결할 수 있을 때 표시된다. (인덱스 루스 스캔일 때 노출된다.) |
| Using filesort | MYSQL의 quick sort인 filesort로 행을 정렬한 것을 나타내며, 정렬이 필요한 데이터를 메모리에 올리고 작업을 수행하겠다는 의미를 가지고 있다. 추가적인 정렬 작업이라서 인덱스를 활용하는지 확인하는 것이 중요하다. |
| Using temporary | 암묵적으로 임시 테이블이 생성된 것을 표시한다. 데이터를 가져와 저장한 후 정렬을 수행하거나 중복을 제거하는 작업을 수행할 때 노출되며 임시 테이블이 생기기 때문에 튜닝 대상이 될 수 있다. |
| Using where with pushed | distinct, group by, order by 구문이 포함된 경우 표시된다. (인덱스 컨디션 푸시다운 인덱스) |
| Using index condition | 인덱스 컨디션 pushdown (ICP) 최적화가 일어났음을 표시한다. |
| Using MRR | 멀티 레인지 리드 최적화가 사용되었음을 표시한다. |
| Using join buffer(Block Nested Loop) | 조인에 적절한 인덱스가 없어서 중간 데이터 결과를 저장하는 조인 버퍼를 사용하였음을 의미한다. |
| Using join buffer(Batched Key Access) | Batched Key Access (BKAJ) 알고리즘을 위한 조인 버퍼를 사용했음을 의미한다. |
| Not exists | 하나의 일치하는 행을 찾으면 추가로 행을 더 검색하지 않아도 될 때 출력된다. left join 이나 right join에서 테이블에 존재하지 않는 데이터를 명시적으로 검색할 때 발생한다. |
| Using union | 인덱스를 합집합처럼 모두 결합하여 데이터에 접근했음을 의미한다. 보통 or 구문을 썼을 때 표시된다. |
| Using intersect | 인덱스를 교집합처럼 병합해 추출하는 방식이다. 보통 and 구문을 썼을 때 표시된다. |
| Using sort_union | Using union과 유사하지만 or 구문이 동등 조건이 아닐 때 확인할 수 있다. |
| no matching row in const table | 서브쿼리나 조인에서 상수 값과 매칭되는 데이터가 없을 때나 참조 무결성 문제가 존재할 때 발생한다. 즉, 데이터가 없을 때 반환된다. |
ICP는 옵티마이저의 최적화 방식 중 하나로, index condition pushdown 이다. 멀티 컬럼 인덱스에서 왼쪽부터 순서대로 컬럼을 지정하지 않는 경우에도 인덱스를 사용하는 실행 계획인다. 필터 조건을 스토리지 엔진으로 전달하여 필터링 작업에 대한 DB 엔진 부하를 줄이는 방식이다. 이는 스토리지 엔진의 결과를 DB 엔진으로 전송하는 데이터양을 줄여 성능 효율을 높일 수 있다.
BKAJ 알고리즘 (Batched Key Access Join)은 MySQL에서 JOIN 성능을 최적화하기 위해 사용하는 내부 알고리즘이다. 조인 버퍼를 활용해 키 액세스를 일괄 처리하여 쿼리 실행 효율을 높인다.
예시
테이블 구조
t_user 테이블
| 컬럼명 | 데이터 타입 | 제약 조건 | 설명 |
|---|
| user_id | int | PK | 사용자 ID |
| user_name | varchar(10) | | 사용자 이름 |
데이터 예시
| user_id | user_name |
|---|
| 1 | 홍길동 |
| 2 | 김영희 |
| 3 | 테스트 |
t_course 테이블
| 컬럼 이름 | 데이터 타입 | 제약 조건 | 설명 |
|---|
| course_id | INT | PRIMARY KEY | 과정 ID |
| user_id | INT | PRIMARY KEY, FOREIGN KEY | 사용자 ID (t_user의 user_id 참조) |
| course_name | VARCHAR(40) | | 과정 이름 |
데이터 예시 (각 user_id마다 과정이 2개씩 존재한다고 가정)
| course_id | user_id | course_name |
|---|
| 1 | 1 | 첫번째 과정 |
| 2 | 1 | 두번째 과정 |
| 3 | 2 | 첫번째 과정 |
| 4 | 2 | 두번째 과정 |
기본 쿼리 (전체 조회)
Explain 결과
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|---|
| 1 | SIMPLE | t_user | (null) | ALL | (null) | (null) | (null) | (null) | 3 | 100.0 | (null) |
- id : 실행한 쿼리는 한 개
- select_type : 단순한 테이블 스캔
- table : t_user 테이블
- partitions : 파티션 사용 X
- type : 전체 테이블 스캔
- possible_keys : 인덱스 사용 X
- key : 사용한 인덱스 X
- key_len : 사용한 인덱스 X -> 열 크기 확인 X
- rdf : 테이블 조인 X
- rows : 이 쿼리에서 3개의 행이 조회됨
- filtered : 스토리지 엔진으로부터 모든 데이터 (100%)를 가져옴
- Extra : 추가 정보 X
기본 쿼리 (조건 추가)
1
| select * from t_user where user_id = 1;
|
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|---|
| 1 | SIMPLE | t_user | (null) | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.0 | (null) |
- user_id는 PK이기 때문에 인덱스(기본키) 조회를 진행 -> possible_keys와 key에 PRIMARY가 표시되고 type과 ref에 const가 표시된다.
- 꼭 PK가 아니더라도 key 설정이 되어 있으면 위와 같이 보여질 수 있다.
- user_name이 KEY이고
where user_name = '홍길동'이라면 possible_keys와 key에 user_name이 표시된다.
1
| select * from t_user where user_id = -1;
|
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|---|
| 1 | SIMPLE | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | no matching row in const table |
- 위와 같이 존재할 수 없는 데이터를 검색할 경우, 모두 null이 반환되고 Extra에 이 기본키에 맞는 행이 없음을 표시한다.
1
| select * from t_user where user_name = '홍길동';
|
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|---|
| 1 | SIMPLE | t_user | (null) | ALL | (null) | (null) | (null) | (null) | 3 | 33.33 | Using where |
- id : 단일 쿼리 실행
- select_type : 단순 쿼리
- table : t_user 테이블
- partitions : 사용한 파티션 X
- type : 전체 스캔
- possible_keys : 사용할 수 있는 인덱스 없음
- key && key_len : 사용한 인덱스 없음
- ref : 액세스된 테이블 없음
- filtered : 3개의 행 중 하나만 가져왔기 때문에 33.33 표시
- Extra : where 조건절을 사용해 데이터를 추출함
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
| CREATE TABLE t_test (
id int NOT NULL,
new_date DATE NOT NULL,
PRIMARY KEY(id)
) PARTITION BY RANGE COLUMNS(new_date)
(
PARTITION p1 VALUES LESS THEN ('2022-01-01'),
PARTITION p2 VALUES LESS THEN ('2023-01-01'),
PARTITION p3 VALUES LESS THEN ('2024-01-01'),
PARTITION p4 VALUES LESS THEN ('2025-01-01'),
);
EXPLAIN
SELECT *
FROM t_test
WHERE new_date BETWEEN '2022-11-11' AND '2023-03-25';
|
- 위와 같이 파티션이 분리된 테이블이 있다고 가정하자.
- 위 조회 쿼리를 실행하면 파티션 프루닝이 일어나며 p2와 p3 파티션에 걸쳐 데이터를 전체 조회한다.
- 예상되는 explain 결과는 아래와 같다.
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|---|
| 1 | SIMPLE | t_test | p2,p3 | range | NULL | NULL | NULL | NULL | 1000 | 80.00 | Using where |
- id : 단일 쿼리
- select_type : 단순 쿼리
- table : t_test
- partitions : 쿼리가 조회하는 파티션 목록으로, 옵티마이저가 파티션 프루닝을 통해 p2와 p3 파티션만 조회한다고 판단했기 때문에 p2, p3 파티션만 사용됨
- type : 옵티마이저가 범위 검색을 통해 파티션 조회함 (new_date 컬럼을 기준으로 범위 검색)
- possible_key && key && key_len : 인덱스 X
- ref : 조인 X
- rows : 옵티마이저가 예상하는 반환 행 수 (필자는 임의의 값을 넣음)
- 필터링된 행의 비율 (필자는 임의의 값을 넣음)
- Extra : where 절 실행
JOIN 쿼리
1
2
3
4
| EXPLAIN
SELECT u.user_id, u.user_name, c.course_id, c.course_name
FROM t_user u
JOIN t_course c ON u.user_id = c.user_id;
|
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|---|
| 1 | SIMPLE | u | | ALL | PRIMARY | NULL | NULL | NULL | 3 | 100.00 | |
| 1 | SIMPLE | c | | ref | PRIMARY | PRIMARY | 4 | test.u.user_id | 2 | 100.00 | |
t_user 테이블
- t_user 테이블에는 possible_keys에서도 명시했다시피 기본 키가 존재하지만, 옵티마이저는 ALL을 선택하여 Full Table Scan이 발생한다.
t_course 테이블
- type이 ref인 것으로 보아 인덱스를 사용해 조회를 진행함. 인덱스는 기본키인 user_id를 사용해 매칭을 수행함
- ref의 test.u.user_id는 t_course에서 user_id 값을 찾는데 t_user 테이블의 user_id를 참조했다는 의미이다. 즉, 각 테이블의 user_id가 일치하는 레코드를 찾는다.
- 이때 rows가 2인 것으로 보아 옵티마이저는 t_course 테이블에서 user_id에 일치하는 행이 2개라고 예상하고 있다. 이는 각 과정이 사용자마다 2개씩 존재하도록 데이터가 구성되어 있기 때문이다.
- filtered가 100인 것으로 보아 t_course에서 user_id에 맞는 모든 행이 쿼리 조건에 맞음을 알 수 있다.
- 이 결과는 join이 left든 right든 inner든 상관 없이 동일하다.
- 만약 where 절이 추가된다면, 조건절은 보통 필터링의 역할을 하기 때문에 조인된 결과에 필터링을 적용한다.
- 필터링이 적용된 테이블의 Extra에 값이 추가될 수 있다.
서브쿼리
아래 예시는 단일 값을 반환하는 서브쿼리인 스칼라 서브쿼리의 실행 계획이다.
1
2
3
4
| EXPLAIN
SELECT user_id, user_name
FROM t_user
WHERE user_id = (SELECT user_id FROM t_course WHERE course_name = '첫번째 과정' LIMIT 1);
|
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|---|
| 1 | PRIMARY | u | null | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | null |
| 2 | SUBQUERY | c | null | index | null | PRIMARY | 8 | null | 1 | 10.00 | Using where |
실행 순서는 아래와 같다.
- t_user 테이블을 기본 키에 대한 단건 조회를 진행한다.
- 이때 단건 조회를 위한 서브 쿼리가 실행된다.
- 스칼라 서브 쿼리 부분에서 과정명 (course_name)은 인덱스가 없기 대문에 전체 인덱스를 훑어 조건에 맞는 행을 하나 반환한다.
- 참고로 서브쿼리의 key_len이 8인 이유는 복합키이기 때문이다.
- 서브쿼리에서 반환한 값을 받은 메인 쿼리는 조건에 맞는 행을 반환한다.
- 인라인 뷰나 전체 쿼리 내에서 다른 쿼리의 결과를 사용하는 방식이어도 크게 다르지 않다.
group by
1
2
3
4
| EXPLAIN
SELECT user_id, COUNT(*) AS course_count
FROM t_course
GROUP BY user_id;
|
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|---|
| 1 | SIMPLE | t_course | null | index | PRIMARY | PRIMARY | 8 | null | 3 | 100.00 | Using index |
내용은 아래와 같다.
- type이 index인 것으로 보아 인덱스 전체를 순차적으로 스캔하는 방식으로, 전체 스캔보다는 빠르지만 전체 인덱스를 모두 읽기 때문에 비효율적일 수 있다.
- key가 primary고 key_len이 8인 것으로 보아 복합키를 사용해 스캔하는 것을 알 수 있다.
- Extra가 Using index인 것으로 보아 커버링 인덱스 covering index 상황임을 알 수 있다.
커버링 인덱스는 쿼리 처리에 필요한 모든 데이터를 인덱스 자체에서 제공할 수 있어, 실제 데이터 테이블에 접근하지 않고 쿼리 결과를 반환하는 인덱스다. 간단히 말해, SELECT, WHERE, ORDER BY, GROUP BY 등에 사용되는 모든 컬럼이 인덱스의 구성 요소인 경우를 말한다.
EXPLAIN ANALYZE
ANALYZE은 ‘분석하다’ 라는 뜻을 가지고 있다. 통계 정보를 수집해 쿼리 플래너가 최적의 실행 계획을 선택할 수 있도록 지원하는 EXPLAIN과 달리 쿼리를 실행하고 실행 과정을 분석한다. 다만 쿼리 속도가 매우 느리거나, 부하가 큰 쿼리를 분석해야 할 경우에는 DB에 부담이 될 수 있으며, MySQL 8.0 이상이나 PostgreSQL에서 사용 가능하다.
다만 DB 실습 사이트나 현재 PC에서 사용하는 버전이 8.0보다 낮아서 실제로 테스트를 해볼 수는 없었다. 나중에라도 실습 후 수정할 예정이다.
1
2
| EXPLAIN FORMAT = JSON
SELECT * FROM t_user;
|
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "289211.40"
},
"table": {
"table_name": "t_user",
"access_type": "ALL",
"rows_examined_per_scan": 1000000,
"rows_produced_per_join": 1000000,
"filtered": "100.00",
"cost_info": {
"read_cost": "15153.00",
"eval_cost": "274058.40",
"prefix_cost": "289211.40",
"data_read_per_join": "2G"
},
"used_columns": [
"user_id",
"user_name"
]
}
}
}
FORMAT 옵션을 사용해서 결과를 JSON 형태로 확인할 수 있다. (데이터는 임의의 값이다.)
DESCRIBE
DESCRIBE를 사용하면 테이블 구조를 명확하게 확인할 수 있다. 이 명령어는 열 이름, 데이터 타입, NULL 여부, 키 정보, 기본값 등을 한눈에 확인할 수 있도록 결과값을 도출한다. DESC로 축약해서 사용 가능하다.
DESCRIBE t_user;
or
DESC t_user;
| Field | Type | Null | Key | Default | Extra |
|---|
| user_id | int(9) | NO | PRI | (null) | auto_increment |
| user_name | varchar(10) | YES | | (null) | |
만약 복합키가 존재한다면 Key 부분에 UNI (Unique Key)나 MUL (Multiple Occurrences)가 출력된다.
참고로 MUL은 해당 컬럼에 여러 번 같은 값이 들어갈 수 있는 다중 사용 컬럼을 의미한다. 보통 해당 컬럼이 고유하지 않은 인덱스 (non-unique index)로 설정되어 있으며 외래키로 사용될 때 주로 볼 수 있다.
1
2
| DESCRIBE
SELECT * FROM T_USER;
|
특이사항으로는 MYSQL 5.7 기준, 위와 같이 사용하면 EXPLAIN을 실행했을 때와 결과값이 유사하게 나온다.
후기
그동안은 ‘인덱스가 설정된 컬럼을 where이나 join 조건절에 넣기만 하면 된다.’ 정도의 단순한 쿼리 작성을 진행했다. 그런데 실제 현업에서는 단순 조회만 있는 것이 아니라, 서브쿼리, join, union 등 다양한 형태의 쿼리를 활용해야만 했고, 이론으로만 알고 있던 ‘join이 더 낫다’, ‘서브쿼리는 가급적 지양하는 게 좋다.’ 같은 정보만으로는 복잡한 상황을 해결하기 어려웠다. 인덱스를 설정해도 성능이 개선되지 않아서 답답함을 느끼기도 했다.
하지만 이번에 SQL의 실행 과정을 처음부터 공부하면서, 동일한 쿼리라도 조건, 데이터 분포, 인덱스 적용 여부, 옵티마이저 선택 등에 따라 성능이 크게 달라질 수밖에 없는 이유를 이해할 수 있었다. 덕분에 인덱스를 잡는다고 해서 무조건 빨라지는 게 아니라는 것을 체감하게 되었다. 특히 인덱스가 너무 많아 오히려 성능이 나빠지는 경우, 조건절이 인덱스를 제대로 활용하지 못하는 경우, 데이터 분포도 (카디널리티)가 낮은 경우, 인덱스 접근 과정에서 랜덤 I/O가 발생하는 경우, 옵티마이저가 부정확한 실행 계획을 선택하는 경우, 복합 인덱스의 컬럼 순서를 고려하지 않는 경우 등 다양한 원인 때문에 인덱스가 효과적이지 않을 수 있다는 점도 실감하게 되었다.
또한 explain 결과를 해석하는 방법을 익히다 보니, 이제는 explain 결과를 볼 때 일일이 해석을 찾아야 할 필요가 줄어든 것 같다. 항목별 의미가 명확하게 정리되어 있으니 실행 계획을 보면서 어느 부분을 튜닝해야 하는지 등을 해석하는 속도가 점점 늘고 있는 것 같다.
이번 정리를 통해 전체적인 이해도가 높아졌다고 생각하고, 앞으로는 쿼리를 작성하거나 성능을 튜닝할 때 explain을 확인하고 활용하는 습관을 들이고자 한다. 단순히 쿼리를 작성하는 것이 아니라, 왜 이런 실행 계획이 선택되는지까지 고려하면서 작성할 수 있도록 계속해서 경험을 쌓아나가야겠다.
참고
ysk(0soo) 님 - MySQL 실행계획(explain) 정리
숑숑 님 - MySQL :: 슬로우쿼리 잡는 명령어, ‘EXPLAIN ANALYZE’ 해석법
토르비욘 님 - [MySQL] 테이블 Key에서 MUL이란?