[mySQL] Explain이 무엇인가?

mysql 인덱스 정리하려다가 Explain보는 법? 좀 공부하려고 하다보니... 적어놔야할거같아서... 

 

---

Explain이 뭐냐??

 

mysql doc에 따르면 "select, delete, insert, replace, update 쿼리에 대해 Mysql이 어떻게 실행했는지 정보를 보여준다" 라고 되어있다. 즉, 데이터베이스가 데이터를 찾아가는 일련의 과정을 보여주는 것이다.

explain을 통해 기존의 쿼리 튜닝 뿐만 아니라 성능 분석, 인덱스 전략 수립등을 할 수 있다고 한다.

 

 

Explain 어떻게 사용하는데?

 

너무 간단하다. 사용하고자하는 쿼리 맨앞에 "Explain"만 붙이면 끝!

Explain select id from user where name = 'test'

 

그럼 테이블이 이렇게 나온다.(해당 쿼리는 제 프로젝트때 사용한 쿼리입니다)

 

 

저 테이블 칼럼이 무슨 의미인데??

 

사실 나도 잘 몰라서 이번 기회에 정리를 시작한게 된거다.

해당 설명은 mysql 공식 사이트와 여러블로그를 참고하였다

앞으로 설명하는 쿼리의 계산 값에 대해서는 자세하게 보지 마세요! 단순히 explain결과값이 어떻게 나오는지만 참고하시길 바랍니다.!

 

1.  id

select 쿼리별 부여되는 식별자이다. 다만 union으로 조회한 경우 null이 나올 수 있다

explain select * from users u join walker.customer_dog_info cdi on u.user_id = cdi.user_id

 

explain select * from users u where u.user_id = (select p.user_id from pay_history p);

 

 

2. select_type

각 단위의 select가 어떤 타입의 쿼리인지

 

<simple>

말그대로 simple "select * from user" 와 같이 단위 쿼리가 최대 하나만 존재할때

 

<primary>

union이나 서브쿼리를 가지는 select 쿼리의 실행계획에서 가장 바깥쪽에 있는 단위 쿼리

explain select * from users u where u.user_id = (select p.user_id from pay_history p);

 

 

<union>

union으로 결합한 단위 select 쿼리중 첫번째 쿼리 제외한 쿼리들

union의 첫번째 단위 select는 union되는 쿼리 결과들을 모아서 저장하는 임시테이블 -> derived라고 표시됨

EXPLAIN select * from (
    (select user_id from users u ) union all
    (select pay_status from pay_history p) union  ALL
    (select walker_adjust_id from walker_adjust w)
              ) tb;

 

 

보면 제일 외부 쿼리 Primary라 되어있고 첫번째 union에 derived라고 되어있는 것을 볼수 있다

 

<Dependent union>

Dependent는 union이나 union all로 결합된 단위 쿼리가 외부 쿼리에 영향을 받을때

아래 쿼리에서 in내부의 서브 쿼리를 먼저 처리하지 않고, 외부의 employees테이블을 먼저 읽은후 서브쿼리를 실행함

즉, 외부쿼리가 서브쿼리에게 영향을 주는 상황임

EXPLAIN select * from users w
where user_id in (
    (select user_id from users u ) union
    (select pay_status from pay_history p)
              ) ;

 

즉 위에서는 users w가 in절안에 있는 서브쿼리에게 영향을 줌

 

<Union Result>

union의 결과을 담아두는 테이블

즉 바로위 쿼리에서 2,3번의 쿼리 조회결과를 담은게 4번이다

 

<subquery>

from 절 이외에서 사용되는 서브쿼리

explain select * from users u where u.user_id = (select p.user_id from pay_history p);

 

 

이외에도 있는데 일단 주로 내가 보이는 것들만 정리해봤다

 

 

3. table

말그대로 접근하고 있는 테이블이다. 

 

4. partitions

테이블에 파티셔닝되어있는 경우 사용됨(파티셔닝: 동일한 테이블에 데이터를 분할하여 저장하는 방식)

파티셔닝 예)
CREATE TABLE trb1 (id INT, name VARCHAR(50), purchased DATE)
    PARTITION BY RANGE(id)
    (
        PARTITION p0 VALUES LESS THAN (3),
        PARTITION p1 VALUES LESS THAN (7),
        PARTITION p2 VALUES LESS THAN (9),
        PARTITION p3 VALUES LESS THAN (11)
    );

INSERT INTO trb1 VALUES
    (1, 'desk organiser', '2003-10-15'),
    (2, 'CD player', '1993-11-05'),
    (3, 'TV set', '1996-03-10'),
    (4, 'bookcase', '1982-01-10'),
    (5, 'exercise bike', '2004-05-09'),
    (6, 'sofa', '1987-06-05'),
    (7, 'popcorn maker', '2001-11-22'),
    (8, 'aquarium', '1992-08-04'),
    (9, 'study desk', '1984-09-16'),
    (10, 'lava lamp', '1998-12-25');


explain)
mysql> EXPLAIN SELECT * FROM trb1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: trb1
   partitions: p0,p1,p2,p3
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 10
        Extra: Using filesort

 

 

5. type

각 테이블의 레고드를 어떻게 읽는지 

방식에는 인덱스, 풀 테이블 스캔, 인덱스 풀 스캔 등등이 존재함

type에는 system, const, eq_ref, ref, fulltext, ref or null, index_merge, unique_subquery, index_subquery, range, index, All 이 있음

-> All 은 테이블 풀 스캔임 나머지는 인덱스를 사용해서 접근하는 방식임

-> All로 갈 수록 성능이 느려짐

 

 

프로젝트 때 사용한 type을 보면 range로 인덱스를 사용하여 하나의 값이 아니라 범위를 검색하고 있는것을 볼 수 있음

 

6. possible_keys

데이터를 찾는데 최적의 실행계획을 만들기 위해 후보로 선정했던 접근 방법에서 사용되는 인덱스의 목록

 

7. key

최종 사용된 인덱스

프로젝트 때 사용한 key를 보면 geo_index라고 제가 설정한 인덱스를 사용하는 것을 볼 수 있음

 

8. key_len

인덱스의 길이를 표시

 

9. ref

 

10.rows

실행 계획의 효율성 판단을 위해 예측했던 레코드 건수, 예측이기에 정확하지 않을 수 있음

 

11. filtered

필터링 되고 남은 레코드의 비율 

 

12.extra

동작한 방식에 대한 힌트를 알려줌

Using where	접근 방식을 설명한 것으로, 테이블에서 행을 가져온 후 추가적으로 검색조건을 적용해 행의 범위를 축소한 것을 표시한다.
Using index	테이블에는 접근하지 않고 인덱스에서만 접근해서 쿼티를 해결하는 것을 의미한다.
Using filesort	ORDER BY 인덱스로 해결하지 못하고, filesort(MySQL의 quick sort)로 행을 정렬한 것을 나타낸다.
Using temporary	암묵적으로 임시 테이블이 생성된 것을 표시한다.

 

 

---

explian보는 방법에 대해 간단하게 정리했다. extra에는 더 많은 힌트가 있지만 자주 보이는 것들만 빼왔다.

더자세하게 보고 싶다면 mysql docs을 참고!

 

 

https://dev.mysql.com/doc/refman/8.0/en/using-explain.html

https://zzang9ha.tistory.com/436