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 문 대상으로 수행 가능