[MySQL 쿼리 및 인덱스의 이해]Explain 정보에 대한 이해

Explain은 어떤 경우 사용하는가?
 - 하나 또는 그 이상의 테이블에 대해서 MySQL이 쿼리를 어떻게 처리하는지 이해하기 위해 사용
 - 5.0.1이후부터는 Last query cost항목이 추가되어 비교가 편리
 
Explain을 통한 Select문의 최적화
 - 구문
   mysql>EXPLAIN SELECT ...... \G
  
 - 세가지 질문에 대한 답(중요!)
   어떠한 순서로 테이블을 읽는가?
   어떠한 방법으로 읽어오나?
   얼마나 많은 Row를 읽어오나?

 

 - 최적화 방법
   쿼리재작성(서브쿼리는 조인으로)
   가능한 많은 조건절
   테이블 읽어오는 순서 조절(작은 테이블 우선)
   인덱스를 사용하도록 조정(use index, ignore index)
   필드에 새로운 인덱스를 추가

  
Explain 결과필드

 

 - 결과필드 설명
  Table : explain에서 읽은 테이블
  Id : 처리될 순서
  Select_Type : 처리될 select의 타임(simple,union..)
  Type : 조인을 수행하는 타임
  Possible_Key : 사용할 수 있는 키
  Key : 옵티마이저가 사용할 인텍스 수
  Key_length : 사용할 인덱스 길이
  Ref : 선택된 인덱스를 통해 가져오게될 컬럼
  Rows : 해당 쿼리가 테이블에서 검색하게될 Row수
  Extra : 옵티마이저가 제공하는 추가적인 정보
 

Explain에서의 Type

 - Const : 최상의 경우, 결과값을 찾기 위해서 단 하나의 값만 찾으면 되는 경우(예:Unique한 데이터 검색)
 - Eq_ref : 조인시 최상의 경우, 이전 테이블에서 찾은 값중 단 하나의 값만 해당 테이블에 존재하는 경우(예:둘다 Unique)
 - Ref/ref_of_null : 인덱스에 매치되는 값이 많지 않은 경우 나쁘지 않음, 이전 테이블에서 찾은 값중 몇 개의 값을 찾는 경우
 - Index_merge : 두개이상의 인덱스를 사용할 경우, 복합인덱스는 Merge가 되지 않는다
 - Unique_subquery : 서브쿼리에서 나온 값이 유니크한 키값일 경우
 - Index_subquery : 서브쿼리에서 인덱스는 사용하나 유니크는 아님
 - Range : 주어진 범위의 값을 사용, 검색의 범위와 인덱스의 커디널리티에 영향을 받음
 - Index : 모든 인덱스를 스캔하는 경우, 인덱스 스캔한다는 것을 제외하면 All과 같음
 - All : 전체테이블 스캔
 

Extra 필드에서 보이면 좋은것
 - UsingIndex : 매우 좋은 경우, 실제테이블을 읽지 않고 인덱스에만 처리
 - Usingwhere : 만약 where가 없었다면 전체테이블 스캔
 - Distinct : 이전 테이블과 조합되는 경우가 하나씩 밖에 없는 경우
 - Not exists : left join최적화를 사용할 수 있으면 많은 row는 남겨 놓을 수 있는 경우

 

 

Extra 필드에서 보이면 좋지 않은것
 - Usingfilesort : 추가적인 sort작업이 필요
 - Usingtemporary : temp테이블이 필요, OrderBy와 GroupBy에 해당하는 컬럼이 다를 경우
 - Range check for echo record : range쿼리가 nonselective인덱스(순차적이지않은)와 조합되는 경우