슬로우 쿼리를 잡자! EXPLAIN, EXPLAIN ANALYZE

📑 EXPLAIN 

1. explain의 정의

explain Plan란  SQL을 수행하기전 데이터를 어떻게 가져올 건지에 대한 실행계획을 의미하며

데이터 performance를 확인 하고자 할때 explain Plan 명령어를 사용한다.

 

2. 사용방법

 

2.1 )  SELECT 에서 explain 사용하기

select explain을 사용하려면 SELECT 키워드 앞에 explain을 붙여주면된다. 간단하게 user라는 테이블과 author이라는 테이블이 있다라고 할때 두 테이블을 조인한 테이블에 대하여 explain을 주었다.

 

3. 사용 예시

• 사용한 SQL문

SELECT
    recruitment.id AS recruitment_id,
    company.name AS company_name,
    recruitment.pay AS train_pay,
    recruitment.military_support,
    company.company_logo_url,
    GROUP_CONCAT(acode.keyword) AS recruit_jobs,
    CASE WHEN bookmark.recruitment_id IS NOT NULL THEN TRUE ELSE FALSE END AS is_bookmarked,
    MAX(recruitment.created_at) AS latest_created_at
FROM
    tbl_recruitment recruitment
    LEFT JOIN tbl_bookmark bookmark ON recruitment.id = bookmark.recruitment_id
    JOIN tbl_company company ON recruitment.company_id = company.company_id
    JOIN tbl_recruit_area recruitment_area ON recruitment_area.recruitment_id = recruitment.id
    JOIN tbl_recruit_area_code recruitment_area_code ON recruitment_area_code.recruit_area_id = recruitment_area.id AND recruitment_area_code.type = 'JOB'
    JOIN tbl_code acode ON recruitment_area_code.code_id = acode.parent_code_id
WHERE
    recruitment.status = 'RECRUITING'
    AND company.name = '기업 이름'
GROUP BY
    recruitment.id
ORDER BY
    latest_created_at DESC;

 

📑 결과

이게 뭔지 천천히 알아보자

• id
  • 각 쿼리 단계의 순서. 쿼리가 복잡해질수록 여러 단계로 나뉘며, 값이 클수록 먼저 실행된다.
• select_type
  • 쿼리의 유형을 나타냄.
    • SIMPLE: 조인이나 서브쿼리가 없는 단순 SELECT 문.
    • PRIMARY: 가장 바깥쪽의 쿼리.
    • SUBQUERY: 서브쿼리에서 사용되는 SELECT.
    • DERIVED: 파생된 테이블, 즉 서브쿼리에서 가져온 결과를 임시 테이블로 사용할 때.
• table
  • 쿼리에 사용되는 테이블의 이름
• type
  • 데이터 검색 유형으로, 데이터베이스가 테이블의 데이터를 어떻게 검색하는지를 나타냅니다. 일반적으로 이 값이 ALL에서 const, eq_ref 등의 값으로 갈수록 성능이 좋다고 볼 수 있다.
    • ALL: 전체 테이블 스캔.
    • index: 인덱스 스캔.
    • range: 범위 조건을 이용한 인덱스 스캔.
    • ref: 인덱스를 통한 조회지만 전체 테이블에 대한 스캔.
    • const: 특정 값으로 인덱스를 사용한 조회.
• possible_keys
  • 쿼리에서 사용할 수 있는 인덱스들을 보여준다. 이 값이 NULL이면 인덱스를 사용하지 않는다는 의미입니다.
• key
  • 실제로 사용된 인덱스를 나타낸다. NULL이면 인덱스를 사용하지 않았다는 뜻
    • key가 primary인 경우 기본키가 인덱스로 사용되고 있다는것.
    • key가 FK_TBL_RECRUIT_AREA_ON_RECRUITMENT 인 경우 fk가 인덱스로 사용되고 있는것
• key_len
  • 실제 사용된 인덱스의 길이를 나타낸다. 인덱스의 길이가 길다면 조회 성능에 영향을 줄 수 있다.
• ref
  • 조인 조건에서 비교된 열이나 상수를 나타낸다.
• rows
  • MySQL이 쿼리를 수행하기 위해 검사할 것으로 예상되는 행 수. 이 값이 클수록 쿼리 성능에 영향을 미칠 가능성이 높다.
• filtered
  • 조건에 의해 필터링된 행 비율을 나타낸다. 예를 들어 filtered 값이 50이면 데이터의 50%가 조건을 만족한다는 의미이다.
• Extra
  • 추가적인 정보로, Using where, Using index, Using temporary, Using filesort 등의 값이 나올 수 있다.
  • Using where: WHERE 조건을 사용하여 필터링.
  • Using index: 인덱스만 사용하여 데이터를 조회.
  • Using temporary: 임시 테이블을 사용하여 쿼리를 처리.
  • Using filesort: 정렬을 위해 별도의 정렬 알고리즘을 사용.

---

EXPLAIN을 통해 쿼리 성능을 최적화하기 위해 어느 부분에서 병목이 발생하는지, 어떤 인덱스를 추가하거나 수정해야 하는지 등을 파악할 수 있다.

 

🤔 하지만?

Explain은 그저 SQL을 수행하기전 데이터를 어떻게 가져올 건지에 대한 실행계획 일 뿐이다.

그렇기에 EXPLAIN은 실제 실행 결과를 다루지 않고, 실행 전 예측에 기반한다.

DB 옵티마이저에게 제공되는 통계가 정확하지 않다면 잘못 된 실행 계획이 나올 가능성이 높고, 항상 최적은 실행계획을 보장하지는 않는다.

그렇다면 실제로 실행된 결과를 분석하여 보려면 어떻게 해야할까?

 

📝 EXPLAIN ANALYZE

1. 실행 결과

Explain과 같은 쿼리를 사용하였습니다

 

실행시키자 가장 먼저 보이는 하나의 레코드?

왜 이렇게 적지 싶어서 눌러보니..

 

 

-> Sort: latest_created_at DESC  (actual time=1.03..1.03 rows=1 loops=1)
    -> Stream results  (cost=59 rows=60.3) (actual time=1.02..1.02 rows=1 loops=1)
        -> Group aggregate: group_concat(acode.keyword separator ','), max(recruitment.created_at)  (cost=59 rows=60.3) (actual time=1.01..1.01 rows=1 loops=1)
            -> Nested loop inner join  (cost=53 rows=60.3) (actual time=0.284..0.908 rows=255 loops=1)
                -> Nested loop left join  (cost=45.2 rows=2.31) (actual time=0.234..0.38 rows=10 loops=1)
                    -> Nested loop inner join  (cost=44.7 rows=1.23) (actual time=0.228..0.364 rows=2 loops=1)
                        -> Nested loop inner join  (cost=42.9 rows=2.24) (actual time=0.219..0.353 rows=1 loops=1)
                            -> Nested loop inner join  (cost=42.2 rows=1.89) (actual time=0.21..0.342 rows=1 loops=1)
                                -> Filter: (recruitment.`status` = 'RECRUITING')  (cost=23 rows=18.9) (actual time=0.14..0.226 rows=22 loops=1)
                                    -> Index scan on recruitment using PRIMARY  (cost=23 rows=189) (actual time=0.0342..0.197 rows=189 loops=1)
                                -> Filter: (company.`name` = '팬마음')  (cost=0.917 rows=0.1) (actual time=0.00509..0.0051 rows=0.0455 loops=22)
                                    -> Single-row index lookup on company using PRIMARY (company_id=recruitment.company_id)  (cost=0.917 rows=1) (actual time=0.00458..0.00462 rows=1 loops=22)
                            -> Covering index lookup on recruitment_area using FK_TBL_RECRUIT_AREA_ON_RECRUITMENT (recruitment_id=recruitment.id)  (cost=0.313 rows=1.18) (actual time=0.00847..0.00953 rows=1 loops=1)
                        -> Filter: (recruitment_area_code.`type` = 'JOB')  (cost=0.276 rows=0.548) (actual time=0.0086..0.0104 rows=2 loops=1)
                            -> Index lookup on recruitment_area_code using PRIMARY (recruit_area_id=recruitment_area.id)  (cost=0.276 rows=5.48) (actual time=0.00812..0.00946 rows=2 loops=1)
                    -> Covering index lookup on bookmark using PRIMARY (recruitment_id=recruitment.id)  (cost=0.404 rows=1.88) (actual time=0.0043..0.00747 rows=5 loops=2)
                -> Index lookup on acode using FK_TBL_CODE_ON_PARENT_CODE (parent_code_id=recruitment_area_code.code_id)  (cost=1.88 rows=26.1) (actual time=0.0455..0.0505 rows=25.5 loops=10)

 

생각보다 길다;;

 

2. 용어 정리

용어 의미

actual	실행 결과
time	실행 시간(ms)
time=a…b 중 a	첫번째 행을 읽어오는데 들었던 시간의 평균(ms)
time=a…b 중 b	모든 행을 읽어오는데 들었던 시간의 평균(ms)
rows	반환된 행수의 평균
loops	작업을 반복한 횟수
cost	CPU 사이클, I/O 접근 빈도 등 리소스 소모에 대한 추정치

 

 

트러블슈팅 시 접근법:

1. 쿼리 시간이 느린 경우
   • time(실행 시간)이 가장 큰 부분을 찾는다.
2. 실행 시 사용된 전략이 비합리적인 경우
   • rows(반환된 행수)가 실행 계획과 크게 차이나는 부분이 있는지 확인한다.
   • 실행 계획 상의 rows로 전략을 세우기 때문에, 차이가 크다면 전략이 비합리적인 원인이 될 수 있다.
   • 해결법으로는 연관 컬럼에 인덱스 추가 등을 고려할 수 있다.

 

🫠 차이점

1. actual  키워드
   1. actual 이 붙지 않았다면 실행 계획 (explain과 동일)
   2. actual 이 붙었다면 실제 실행 후 분석한 결과라는 뜻

• time
  • 첫번째 숫자(0.464): 첫 행을 읽어오는데 들었던 시간의 평균 (ms)
  • 두번째 숫자(22.767): 모든 행을 읽는데 들었던 시간의 평균 (ms)
• rows: 실제 반환된 행수의 평균
• loops: 해당 작업이 반복된 횟수

또한 Explain의 통계가 부정확 할 경우, Explain Analyze와 실행시간이나 조회 행등이 다를 수 있다.

 

참고 👇 👇

https://velog.io/@wisepine/MySQL-슬로우쿼리-잡는-명령어-EXPLAIN-ANALIZE-해석법