[MYSQL] - SELECT 효율적으로 사용하기 - JOIN(1)

1. 쿼리의 속도를 측정하는 EXPLAIN

 

mysql 옵티마이저는 비용 기반으로 어떤 실행 계획으로 쿼리를 실행했을 때 비용이 얼마나 발생하는지를 계산하여 비용이 가장 적은 것을 택하게 된다.

어디까지나 추정 값이므로 정확한 비용은 실행 전까지 정확하게 알 수 없다..

EXPLAIN은 MySQL 서버가 어떠한 쿼리를 실행할 것인가, 즉 실행 계획이 무엇인지 알고 싶을 때 사용하는 기본적인 명령어이다.

 

EXPLAIN SELECT * FROM 테이블명 WHERE 조건

SELECT 명령문을 EXPLAIN 앞에 두면, MYSQL은 쿼리 실행 플랜 (query execution plan) 정보를 옵티마이저 (optimizer) 에서 가져와서 출력한다. 즉, MYSQL은 테이블들이 어떤 순서로 조인 (join) 하는지에 대한 정보를 포함해서, SELECT를 처리하는 방법에 대해서 알려준다.

table : 어떤 테이블에 대한 접근을 표시하고 있는지 table 필드에 표시되어 있다.

id : id는 SELECT에 붙은 번호를 말한다. MYSQL은 조인을 하나의 단위로 실행하기 떄문에 id는 그 쿼리에 실행 단위를 식별하는 것이다.

따라서 조인만 수행되는 쿼리에서는 id는 항상 1이 된다.

select_type : select_type은 항상 SIMPLE이 된다. 복잡한 조인을 해도 SIMPLE 이 된다. 서브쿼리나 UNION이 있으면 id와 select_type이 변한다. (DERIVED, PRIMARY, UNION ) (TODO)

partitions : partitions 는 파티셔닝이 되어 있는 경우에 사용되는 필드이다. null 이라면, 쿼리에서 사용된 테이블이 모두 파티셔닝이 되어 있지 않다는 의미. 파티셔닝 되어 있는 경우는 반드시 필드를 확인하자.

type : type은 접근 방식을 표시하는 필드이다. 어떻게 행 데이터를 가지고 올 것인지를 가리킨다. EXPLAIN 에는 ALL, eq_ref, ref가 있는데, ALL, eq_ref는 조인 시 기본키나 고유키를 사용하여 하나의 값으로 접근( 최대 1행만을 정확하게 패치), ref는 여러 개의 행을 패치할 가능성이 있는 접근을 의미한다.

접근 방식은 대상 테이블로의 접근이 효율적일지 여부를 판단하는데 아주 중요한 항목이다.

이들 접근 방식 가운데도 주의가 필요한 것은 ALL, index, ref_or_null 이다. ALL, index 두 가지는 테이블 또는 특정 인덱스가 전체 행에 접근하기 때문에 크기가 크면 효율이 떨어진다.

ref_or_null의 경우 NUL이 들어있는 행은 인덱스의 맨 앞에 모아서 저장하지만 그 건수가 크면 효율이 떨어진다. ref_or_null의 경우 NUL이 들어 있는 행은 인덱스의 맨 앞에 모아서 저장하지만 그 건수가 많으면 MYSQL 서버의 작업량이 방대해진다. 다시 말해 ALL이외의 접근 방식은 모두 인덱스를 사용한다.

 

성능이 좋은 것 부터 나열되어있음

possible_keys : possible_keys 필드는 이용 가능성있는 인덱스의 목록이다.

key : possible_keys는 이용 가능성 있는 인덱스의 목록 중에서 실제로 옵티마이저가 선택한 인덱스가 key가 된다. 위 EXPLAIN에서는 County 테이블 (첫 번째 행)의 Key는 NULL인데 이는 행 데이터를 가져오기 위해 인덱스를 사용할 수 없다는 의미이다.

key_len : key_len필드는 선택된 인덱스의 길이를 의미한다. 인덱스가 너무 긴것도 비효율적이므로 기억해 두기..!

rows : rows는 이 접근 방식을 이용해 몇 행을 가져왔는가를 표시한다. 최초에 접근하는 테이블에 대해서 쿼리 전체에 의해 접근하는 행 수, 그 이후에 테이블에 대해서는 1행의 조인으로 평균 몇 행에 접근했는가를 표시한다.

단, 어디까지나 통계 값으로 계산한 값이므로 실제 행 수와 반드시 일치하지 않는다.

filtered : filtered는 행 데이터를 가져와 거기에서 where구의 검색 조건이 적용되면 몇 행이 남는지를 표시한다. 이 값은 현실의 값과 반드시 일치하지는 않는다.

extra : extra필드는 옵티마이저가 동작하는데 대해서 우리에게 알려주는 힌트다. 이 필드는 EXPLAIN을 사용해 옵티마이저의 행동을 파악할 때 아주 중요하다.

 

mysql 문서 참고

http://www.mysqlkorea.com/sub.html?mcode=manual&scode=01&m_no=21444&cat1=7&cat2=217&cat3=227&lang=k

:::MySQL Korea:::

https://cheese10yun.github.io/mysql-explian/

Mysql Explain - Yun Blog | 기술 블로그

 

 

 

https://sewonzzang.tistory.com/11

[MYSQL] - SELECT 효율적으로 사용하기 - JOIN(2)