MySQL 에서 EXPLAIN 사용하기

 EXPLAIN 은 어느정도 수준있는 DB를 다루는 개발자라면 꼭 알아야할 정도로 중요한 MySQL 의 쿼리문이다.

 실제로 많은 데이터베이스 관련 서적들에서도 최적화를 위한 선행 단계로 추천하고 있을 만큼 MySQL 의 강력하고 효과적인 쿼리문이다.

 EXPLAIN 키워드는 MySQL 에게 쿼리문의 실행 계획을 물어보는 키워드이다.

 Explain 구문을 이용해서 SQL Query를 수행하기 전에 데이터를 어떻게 가져올 건지에 대한 시스템의 실행계획을 받아볼 수 있다.

주로 쿼리 퍼포먼스 측정을 위해 Explain 을 많이 사용하지만 매 쿼리를 코드에 삽입할 때 테스트해보는 습관을 들이는 것이 좋다.

 

 SELECT 구문에서 explain 을 사용하는 방법은 단순히 키워드 앞에 붙여주기만 하면 된다. 단 SELECT 구문이 아닐 경우에는 INSERT, UPDATE, DELETE 등의 구문을 SELECT로 재구성시켜줘야 한다.

(ex) EXPLAIN select * from members

      UPDATE name from members where member_id = ‘1’

       EXPLAIN SELECT name from members (UPDATE 후)

 EXPLAIN 에서 각 칼럼은 다음과 같은 의미를 갖는다.

Select_type – 간단한 쿼리인지 복잡한 쿼리인지를 나타낸다.

Table – 어떤 테이블에 접근하는지를 나타낸다. 복잡한 쿼리문에서도 어떤 테이블에 실질적으로 접근하는지를 알 수 있다.

Type – 조인 방식이자, 테이블에서 해당 레코드를 어떻게 찾아가는지를 나타내며 퍼포먼스 측정에 있어서 중요한 지표이다. 

(ALL-풀스캔, INDEX-유사 풀스캔, RANGE-제한된 인덱스 스캔, REF-부분적인 값에 매칭되는 부분만 검사, EQ_REF-단 하나의 

값만 참조하는 경우, CONST-쿼리 일부를 상수로 대채시켜서 찾음. SYSTEM-무조건 하나의 열만을 갖는 테이블)

Possible Key- 해당 조회문에서 MySQL이 선택한 인덱스를 나타낸다.

Key – MySQL이 실제로 사용할 키를 나타낸다. 

Key len – 인덱스 필드가 가질 수 있는 최대길이

Ref – 키 칼럼의 인덱스를 찾기 위해 선행 테이블의 어떤 칼럼이 사용되었는지

Row – 원하는 행을 찾기 위해 읽어야 하는 예측 Row 카운트

Extra – 각종 조건문이 사용되는지 여부를 나타낸다.

 특히 많은 종류의 웹서비스가 SELECT 에 대한 처리를 주로 하는 경우가 많다.(SELECT 가 주가 아닌 데이터라면 MySQL 을 사용하지 않을것이다.) 

가령 Key 로 잡히지 않은 Column 을 조건으로 SELECT 쿼리를 수행하는 경우, 특히 유저 DB라면, FULL SCAN 이 발생해 막대한 비용이 들게 된다...

 이런 참사를 개발시에 눈치챌 수도 있지만, 본인이 DB 구조를 잘 모르거나 구조가 매우 복잡한 레거시 프로젝트에 투입된 상황이라면, EXPLAIN 의 생활화는 큰 도움이 될 것이다.