MySQL 실행계획 분석

학습자료 : 업무에 바로 쓰는 SQL 튜닝, 양바른 저

  • 쿼리의 실행계획을 보기 위한 명령어 키워드

    • EXPALIN 쿼리;

    • DESCRIBE 쿼리;

    • DESC 쿼리;

실행계획 조회시 의미하는 정보들

1. id

  • 실행 순서를 표시하는 숫자(순서가 낮을 수록 먼저 실행되었음을 의미)

  • 조인 또는 서브쿼리를 사용하였을 때, 순서를 확인할 수 있음

  • 조인되었다면, 같은 순서로 표시함

2. select_type

  • select 쿼리의 유형을 표시함

SIMPLE

  • union 또는 내부 쿼리가 없는 경우를 표시함

  • 단순 select 쿼리

PRIMARY

  • 서브쿼리를 감싸는 외부쿼리

  • union이 포함된 쿼리에서 첫 번째로 select를 사용한 쿼리

SUBQUERY

  • 독립적으로 수행된 서브쿼리를 표시

  • select 절의 스칼라 서브쿼리와 where절의 중첩 서브쿼리

DERIVED

  • from 절에 작성된 서브쿼리를 의미

  • 별도의 임시 테이블인 인라인 뷰

UNION

  • UNION 또는 UNION ALL 구문으로 합쳐진 select 문에서 첫번째 select 이외의 select문

UNION RESULT

  • UNION ALL이 아닌 UNION 구문으로 SELECT 절을 결합했을 때, 표시함

  • UNION의 경우, 임시 테이블을 생성하여 메모리에서 정렬하여 중복체크하는 과정을 거침

  • UNION RESULT는 이러한 과정을 진행했다는 것으로 해석할 수 있음

  • 데이터의 유일성을 보장할 수 있다면, UNION ALL로 쿼리를 튜닝하는 편이 성능적으로 유리함

DEPENDENT SUBQUERY

  • UNION 또는 UNION ALL을 사용하는 서브쿼리가 메인 테이블의 영향을 받는 경우에, UNION으로 연결된 쿼리 중에 첫 번째 단위 쿼리를 의미함

  • 단위 쿼리가 메인 테이블로부터 값을 하나씩 공급 받아야하는 구조이기에, 쿼리 튜닝 대상이 됨

DEPENDENT UNION

  • DEPENDENT SUBQUERY와 같이 첫 번째 단위쿼리가 아닌 두 번째 단위쿼리를 의미함

  • 쿼리 튜닝 대상

UNCACHEABLE SUBQUERY

  • 메모리에서 캐싱되어 재활용되어야 할 서브쿼리가 재사용되지 못함을 의미함

  • 서브쿼리 내부에 사용자 정의함수나 사용자 변수가 포함된 경우

  • RAND(), UUID() 함수 등을 통해서 매번 조회시마다 결과가 달라지는 경우

  • 서브쿼리의 결과가 메모리에 상주할 수 있도록, 튜닝을 고려해보는 것이 좋음

MATERIALIZED

  • IN 절 구문에 연결된 서브쿼리가 임시 테이블을 생성한 뒤, 조인이나 가공작업을 수행할 때 표시함

3. table

  • 테이블명을 표시하는 항목

  • 테이블명 혹은 테이블 별칭(alias)

  • 서브쿼리나 임시테이블 생성의 경우

  • <subquery#>, <derived#> 으로 표시함(#의 경우 id를 표시)

4. partitions

  • 데이터가 저장된 논리적인 영역을 표시하는 항목

  • 파티션이 나눠져있다면, 특정 파티션에서만 데이터를 조회하는 것이 성능적으로 유리함

5. type

  • 테이블의 데이터를 어떻게 찾을지에 관한 정보를 제공하는 항목

system

  • 테이블에 데이터가 없거나, 하나만 있는 경우

  • 최고의 성능

const

  • 조회되는 데이터가 단 1건 출력되는 유형

  • 고유 인덱스 또는 기본 키를 사용하여 1건만 조회

eq_ref

  • 조인이 수행될 때, 드리븐 테이블의 데이터에 접근할 때, 고유 인덱스 또는 기본키로 1건의 데이터만 조회하는 유형

  • 조인이 수행될 때, 성능상 가장 좋음

ref

  • eq_ref와 흡사하지만, 드리븐 테이블의 데이터 접근 범위가 2개 이상인 경우를 의미함

  • 드리븐 테이블의 데이터가 많을 경우, 성능 저하의 원인이 되는지 파악할 필요가 있음

  • =, >, < 등의 연산자를 사용해서 인덱스로 생성된 열을 비교하는 경우 또한 의미함

ref_or_null

  • ref 유형과 흡사하지만 IS NULL 구문에 대해서 인덱스를 활용화도록 최적화된 방식

  • NULL에 대해서도 인덱스를 활용할 수 있음, 이 때 NULL이 가장 앞쪽에 정렬되어 있음

  • NULL 데이터 양이 적다면, 효율적인 쿼리지만, NULL이 많다면 튜닝 대상이 될 수 있음

range

  • 테이블 내의 연속된 데이터 범위를 조회하는 유형

  • =, <>, >, >=, < , <= , IS NULL, <=>, BETWEEN, IN 연산을 활용하는 경우

  • 범위가 넓으면 성능 저하의 원인이 될 수 있음

fulltext

  • 텍스트 검색을 위해서 full text index를 활용하는 유형

index_merge

  • 특정 테이블에 생성된 2개 이상의 인덱스가 병합되어 동시에 적용되는 경우

  • full text index는 제외

index

  • index를 전체 탐색하는 경우(full scan)

  • 인덱스 또한 물리적인 영역으로 저장되어 있지만, table 보다는 크기가 작기 때문에 table full scan보다는 유리함

ALL

  • table full scan

  • 인덱스가 없거나, 옵티마이저가 인덱스를 활용하는 것이 더 비효율적이라고 판단했을 경우에 해당함

  • 전체 테이블의 10~20% 이상의 데이터를 조회할 경우 full scan이 성능상 유리할 수 있음

6. possible_keys

  • 옵티마이저가 쿼리를 최적화하고자 사용할 수 있는 인덱스 목록을 표시함

  • 사용할 수 있는 기본 키와 인덱스 목록을 보여주는 것으로, 실제 사용하는 인덱스가 아님

7. key

  • 쿼리를 최적화하고자 사용한 기본 키 또는 인덱스명을 의미함

  • 어떤 인덱스를 활용했는지 확인할 수 있어서, 비효율적인 인덱스 혹은 인덱스를 사용하지 않았다면 튜닝의 대상이 됨

8. key_len

  • 인덱스를 사용할 때, 인덱스 전체 혹은 일부를 사용하게 됨

  • 사용한 인덱스의 바이트 수를 의미함

  • INT 4바이트, VARCHAR 3바이트 / UTF-8기준

9. ref

  • reference의 약자로 테이블 조인을 수행할 때 어떤 조건으로 해당 테이블에 엑세스 되었는지를 보여줌

10. rows

  • 쿼리를 수행하고자 접근하는 데이터의 모든 행의 수를 나타내는 예측 항목을 의미

  • 메모리에서 처리해야 할 행 수를 예상하는 값으로 수치가 정확하진 않고, 최종 출력될 행 수가 아님

  • 최종 결과 건수와 비교해 차이가 많다면, 튜닝의 대상이 될 수 있음

11. filtered

  • DB 엔진으로 가져온 데이터 대상으로 필터 조건에 따라 어느 정도의 비율로 데이터를 제거했는지를 의미하는 항목

  • 단위는 %

12. extra

  • SQL문을 어떻게 수행할 것인지에 관한 추가 정보를 보여주는 항목, ; 으로 구분

  • 30여 가지 항목으로 정리할 수 있음

  • MySQL에서는 extra에서 수행되는 정보가 모두 출력되지 않음

Distinct

  • 중복이 제거되어 유일값을 찾을 때

  • distinct 키워드 혹은 union 구문 활용

Using where

  • where 절의 필터 조건을 사용해서 MySQL 엔진으로 가져온 데이터를 추출할 것을 의미

Using temporary

  • 테이블의 중간 결과를 저장하고자 임시 테이블을 생성하겠다는 의미

  • 데이터를 가져와 정렬 작업을 수행하거나 중복을 제거하는 작업 등을 수행

  • DISTINCT, GROUP BY, ORDER BY 구문이 포함된 경우 표시됨

  • 튜닝의 대상이 될 수 있음

Using index

  • 물리적인 데이터 파일을 읽지 않고 인덱스만을 읽는 경우

  • 커버링 인덱스

Using filesort

  • 정렬이 필요한 데이터를 메모리에 올리고 정렬 작업을 수행한다는 의미함

  • 정렬을 위해서 메모리 영역에 데이터를 올리기 때문에 튜닝 검토 대상이 될 수 있음

Using join buffer

  • 조인을 수행하기 위해 중간 데이터 결과를 저장하는 조인 버퍼를 사용한다는 의미

  • 드라이빙 테이블의 데이터에 먼저 접근한 결과를 조인 버퍼에 담고, 조인 버퍼와 드린 테이블 간에 서로 일치하는 조인 키값을 찾는 과정을 수행하는 경우

Using union/Using intersect/ Using sort_union

  • type이 index_merge일 때, 인덱스를 어떻게 병합했는지 표시함

  • Using union의 경우 합집합, OR 구문

  • Using intersect의 경우 교집합, AND 구문

  • Using sort_union의 경우, WHERE 절의 OR 구문이 동등조건이 아닐 때

Using index condition

  • using where과 달리 필터 조건을 스토리지 엔진으로 전달하여, MySQL 엔진의 부하를 줄이는 방식

Using index condition(BKA)

  • Using index condition과 흡사하지만, 데이터 검색을 위해 배치 키 엑세스를 사용하는 방식

Using index for group-by

  • SQL 문에 GROUP BY 또는 DISTINCT 구문이 포함 될 때, 인덱스로 정렬 작업을 수행하여 최적화함

  • 인덱스 루스 스캔일 때, 표시함

Not exists

  • 하나의 일치하는 행을 찾으면 추가로 행을 더 검새학지 않아도 될 때 표시함

  • left outer join, right outer join의 경우 테이블에 존재하지 않는 데이터를 명시적으로 검색할 때 발생함

MySQL의 학장된 실행 계획 수행

EXPLAIN FORMAT = TRADITIONAL

  • 기본

EXPLAIN ANALYZE

  • 실제 측정한 실행 계획 정보를 출력

  • MySQL 8.0.18 이상 버전부터 SELECT 문 대상으로 수행 가능

PreviousdbNextk8s

Last updated