DB

mysql - explain, explain analyze 해석

blogger903 2024. 8. 19. 15:36
728x90

이번 포스트에서는 가끔 볼때마다 찾아보는 mysql explain, explain analyze에 대해서 정리했습니다

아는내용이 추가될수록 정리하는 내용은 계속 업데이트할 예정입니다

다루는 내용

  • 실행 계획 분석 ( explain analyze )
  • id 컬럼
  • type 컬럼
  • key 컬럼
  • key_len 컬럼
  • ref 컬럼
  • rows 컬럼
  • filtered 컬럼
  • Extra 컬럼
    • using filesort
    • using index
    • using temporary
    • using where
    • backward index scan

 

explain analyze 

예시 SQL

EXPLAIN ANALYZE
SELECT first_name, last_name, SUM(amount) AS total
FROM staff INNER JOIN payment
  ON staff.staff_id = payment.staff_id
     AND
     payment_date LIKE '2005-08%'
GROUP BY first_name, last_name;

-> Table scan on <temporary>  (actual time=0.001..0.001 rows=2 loops=1)
    -> Aggregate using temporary table  (actual time=58.104..58.104 rows=2 loops=1)
        -> Nested loop inner join  (cost=1757.30 rows=1787) (actual time=0.816..46.135 rows=5687 loops=1)
            -> Table scan on staff  (cost=3.20 rows=2) (actual time=0.047..0.051 rows=2 loops=1)
            -> Filter: (payment.payment_date like '2005-08%')  (cost=117.43 rows=894) (actual time=0.464..22.767 rows=2844 loops=2)
                -> Index lookup on payment using idx_fk_staff_id (staff_id=staff.staff_id)  (cost=117.43 rows=8043) (actual time=0.450..19.988 rows=8024 loops=2)

explain analyze 설명

staff 테이블 부터 스캔합니다

(cost, rows):

cost는 예측 비용이며 rows는 반환한 로우수 예측값입니다

(actual time, rows, loops): 

actual time는 millisecond 단위입니다

X..Y 형식이며 X는 첫번째 row를 읽은 시간이며, Y는 모든 row를 읽은 시간입니다
평균적으로 한번의 필터링에 Y만큼 소요되며 loops를 곱해주면 총 시간 은 Y*2 입니다

rows는 실제 반환한 로우수입니다

 

예시 분석:

Filter: (payment.payment_date like '2005-08%') (actual time=0.464..22.767 rows=2844 loops=2)
  • 필터링 작업: actual time=0.464..22.767 rows=2844 loops=2
    • 평균적으로 한 번의 필터링에 22.767ms 소요
    • 2번 반복되므로 실제 총 시간은 약 45.534ms
  • 중첩 루프 조인: actual time=0.816..46.135 rows=5687 loops=1
    • 총 시간 46.135ms는 필터링의 총 시간(45.534ms)과 거의 일치

id 컬럼

id컬럼은 단위 select 쿼리별로 부여되는 식별자 값입니다

join 되는 테이블의 개수만큼 실행 계획 레코드가 출력되지만 id는 같습니다

반면에 subQuery의 경우에는 id가 달라집니다. 실행 계획의 id 컬럼은 테이블 접근 순서를 의미하지 않습니다

테이블 접근 순서를 확인하실때에는 explain analyze 명령을 확인해봐야합니다

type 컬럼

각 테이블의 접근 방법으로 해석하면 됩니다

const

unique index scan

테이블의 레코드 건수와 관계없이 쿼리가 프라이머리 키나 유니크 키 칼럼을 이용하는 where 조건절을 가지고 있고, 반드시 1건을 반환하는 쿼리 방식

eq_ref

조인에서 두번째 이후에 읽는 테이블에서 반드시 1개만 존재한다는 보장이 있어야 사용할 수 있는 접근 방법

ref

인덱스 종류와 관계없이 동등 조건으로 검색할 때 ref 접근 방법
반환되는 레코드가 반드시 1건이라는 보장이 없으므로 const나 eq_ref보다 느립니다.
매우 빠른 레코드 조회 방법의 하나

range

인덱스 레인지 스캔 형태의 접근 방법
range는 인덱스를 하나의 값이 아니라 범위로 검색하는 경우를 의미하는데, 주로 "<, >, IS NULL, BETWEEN, IN, LIKE" 등의 연산자를 이용해 인덱스를 검색할 때 사용됩니다
range 접근 방법도 상당히 빠르며, 모든 쿼리가 이 접근 방법만 사용해도 최적의 성능이 보장된다고 볼수 있습니다

const, ref, range 이 세가지 방법을 묶어서 인덱스 레인지 스캔이라고 하고, "인덱스가 효율적으로 사용한다" 라고 부를수 있는 접근 방법입니다

all

풀 테이블 스캔 접근 방법입니다
테이블을 처음부터 끝까지 전부 읽어서 불필요한 레코드를 제거하고 (체크 조건이 존재할 때) 반환합니다

ref 컬럼

접근 방법이 ref면 참조 조건(Equal 비교 조건)으로 어떤 값이 제공됐는지 보여준다.
상숫값이면 const로 표시되고, 다른 테이블 컬럼 값이면 그 테이블명과 컬럼명이 표시됩니다.
func는 참조용으로 사용되는 값이 콜레이션 변환이나 값 자체의 연산을 거쳐 참조됐다는 것을 의미합니다.

filtered 컬럼

옵티마이저는 각 테이블에서 일치하는 레코드 개수를 가능하면 정확하게 파악해야 효율적인 실행계획을 수립할 수 잇습니다
실행계획에서 rows 컬럼 값은 인덱스를 사용하는 조건에만 일치하는 레코드 건수를 예측한것인데, 대부분 쿼리에서 where 절에 사용되는 조건이 모두 인덱스를 사용할 수 있는 것은 아닙니다. 특히 조인이 사용되는 경우에는 where 절에서 인ㄷ게스를 사용할 수 있는 조건이 중요하지만 인덱스를 사용하지 못하는 조건에 일치하는 레코드 건수가 적은 테이블이 드라이빙 테이블로 선정될 가능성이 높습니다

filtered 값은 필터링되어 버려지는 레코드의 비율이 아니라 필터링되고 남은 레코드의 비율을 의미합니다
rows * filtered(백분율)로 필터된 레코드 건수를 계산할 수 있습니다
옵티마이저는 레코드건수 뿐만 아니라 다른 요소들도 충분히 감안해서 실행계획을 수립하겠지만, 조인의 횟수를 줄이고 그 과정에서 읽어온 데이터를 저장해둘 메모리 사용량을 낮추기 위해 대상 건수가 적은 테이블을 선행 테이블로 선택할 가능성이 높습니다

Extra 컬럼

성능 관련해서 중요한 내용이 표시되기 때문에 Extra가 아닙니다

Using filesort

order by를 처리 하기 위해 인덱스를 이용할 수 있지만 적절한 인덱스를 사용하지 못할 때는 MySQL 서버가 조회된 레코드를 다시 한번 정렬해야 합니다
조회된 레코드를 정렬용 메모리 버퍼에 복사해 퀵 소트 또는 힙 소트 알고리즘을 이용해 정렬을 수행하게 된다는 의미입니다. order by 가 사용되는 쿼리의 실행계획에서만 나타날 수 있습니다

Using index (커버링 인덱스)

데이터 파일을 전혀 읽지 않고 인덱스만 읽어서 쿼리를 모두 처리할 수 있을 때
인덱스를 이용해 처리하는 쿼리에서 가장 큰 부하를 차지하는 부분은 인덱스 검색에서 일치하는 키 값들의 레코드를 읽기 위해 데이터 파일을 검색하는 작업입니다. 최악의 경우 인덱스를 통해 검색된 결과 레코드 한 건 마다 디스크를 한 번씩 읽어야 할 수도 있습니다

Using temporary

MySQL 서버에서 쿼리를 처리하는 동안 중간 결과를 담아 두기 위해 임시 테이블을 사용합니다. 임시 테이블은 메모리상에 생성될수도, 디스크에 생성될 수도 있습니다. 또한 Using temporary 표시가 안되어도 내부적으로 임시 테이블을 사용하는 경우가 있습니다.

Using where

mysql 서버는 내부적으로 크게 mysql 엔진과 스토리지 엔진이라는 두 개의 레이어로 나눠 볼 수 있습니다.
각 스토리지 엔진은 디스크나 메모리 상에서 필요한 레코드를 읽거나 저장하는 역할, mysql 엔진은 스토리지 엔진으로 부터 받은 레코드를 가공 또는 연산하는 작업을 수행합니다
mysql 엔진 레이어에서 별도의 가공을 해서 필터링 작업을 처리한 경우에만 Extra 컬럼에 "Using where" 코멘트가 표시됩니다

Backward index scan

일반적으로 인덱스는 오름차순으로 정렬되어 있는데, 이를 내림차순으로 읽는 것입니다.
일부 상황에서는 정방향 스캔보다 더 효율적일 수 있습니다 (예: 최근 데이터를 우선적으로 가져와야 할 때).

 

 

 

참고: Real MySQL 8.0 part1

 

'DB' 카테고리의 다른 글

mysql - sending to client  (0) 2024.08.19
MySQL PK 전략  (0) 2024.07.29

'DB'의 다른글

  • 현재글mysql - explain, explain analyze 해석

관련글

티스토리툴바