MySQL EXPLAIN 사용하여 Query 퍼포먼스 확인.

EXPLAIN 을 사용하여 인덱스가 적절히 사용되고 있는지, 결과 값을 가지고 오기 위해 어떤 작업을 수행하는지 대략적으로 알 수 있다.

EXPLAIN 은 SELECT문에 사용된 각 테이블당 하나의 행을 리턴한다. 나열된 순서는 MySQL이 쿼리 처리에 사용하는 순서대로 출력된다.

MySQL은 모든 조인을 single-sweep multi-join 방식을 사용하여 처리하는데, 이것은 MySQL이 첫번째 테이블에서 한 행을 읽고, 두번째 테이블에서 매치되는 행을 찾고, 세번째 테이블에서 매치되는 행을 찾는 방식이다.

모든 테이블들이 처리된 후 추출된 컬럼을 출력하고 다시 처음 테이블로 돌아가서 조인을 계속한다. 이런식으로 첫번째 테이블에 더이상 남는 행이 없을때까지 실행한다.

STARTGHT_JOIN을 명시하지 않았다면 유저가 입력한 순서와 관련없이, 어느것이 첫번째 테이블이 될지는 MySQL 옵티마이저가 결정한다.

옵티마이저가 SELECT에 기록된 순서대로 조인을 행하게 하려면 SELECT대신 SELECT STARTGHT_JOIN을 사용해야한다.

EXPLAIN을 사용하면 아래와 같은 컬럼들로 구성된다.

1. id: 쿼리내의 select 구분 번호
   
   
2. select_type: select의 타입
1. SIMPLE: 단순 select
2. PRIMARY: 가장 외곽의 select
3. UNION: union에서 두번째 혹은 나중에 따라오는 select
4. DEPENDENT UNION: union에서 두번째 혹은 나중에 따라오는 select, 외곽쿼리에 의존적
5. UNION RESULT: union의 결과물
6. SUBQUERY: 서브쿼리의 첫번째 select
7. DEPENDENT SUBQUERY: 서브쿼리의 첫번째 select, 외곽쿼리에 의존적이다.
8. DERIVED: select로 추출된 테이블 (from절 내부의 서브쿼리)
   
   
3. table: 나타난 결과가 참조하는 테이블
   
   
4. type: 조인 타입. 서로다은 타입의 조인이 아래에 있는데, 가장 좋은 것부터 나쁜 것의 순서로 표기
1. system
   테이블은 하나의 열만 가지고 있다(시스템 테이블). 이것은 const 조인 타입의 틀별한 경우
   
   
2. const
   테이블은 적어도 하나의 매칭 테이블을 가지고 있는데, 쿼리가 시작되는 시점에서 이 테이블을 읽게 된다. 여기에는 하나의 열만이 존재하기 때문에, 이 열에 있는 컬럼에서 얻는 값은 나머지 옵티마이저에 의해 상수(constant)로 인식될 수 있다. const 테이블은 한번 밖에 읽혀지지 않기 때문에 매우 빠르다.
   contst는 primary key또는 unique인덱스의 모든 부분을 상수 값(constant)과 비교할 때 사용된다.
   SELECT * FROM TABLE_NAME WHERE primary_key = 1 SELECT * FROM TABLE_NAME WHERE primary_key_part1 = 1 AND primary_key_part2 = 2
   
   
3. eq_ref
   이전 테이블로부터 각 열을 조합 (combination)하기 위해서 이 테이블의 열을 하나읽는다. system 및 const 타입과는 달리, 이것이 가장 최선의 가능 조인 (join) 타입이다. 이것은 조인 (join)에 의해 인덱스의 모든 부분이 사용될 때 쓰이게 되며, 이때 인덱스는 PRIMARY KEY 또는 UNIQUE 인덱스가 된다.
   eq_ref는 = 연산자를 사용해서 비교되는 인덱스된 컬럼용으로 사용될 수 있다. 비교 값은 이 테이블 전에 읽었던 테이블에서 컬럼을 사용한 수식 또는 상수 (constant)가 될 수 있다. 아래의 예제에서 보면, MySQL은 ref_table를 처리하기 위해서 eq_ref 조인 (join)을 사용하고 있다.
   SELECT * FROM ref_table,other_table WHERE ref_table.key_column = other_table.column; SELECT * FROM ref_table,other_table WHERE ref_table.key_column_part1 = other_table.column AND ref_table.key_column_part2=1;
   
   
4. ref
   이전 테이블에서 읽어온 각각의 열을 조합하기 위해 이 테이블에서 매칭 (matching)되는 인덱스 값을 가진 모든 열을 읽어온다. 만일 조인 (join)이 키의 좌측 끝(leftmost) 접두사 만을 사용하거나 또는 키 값이 PRIMARY KEY 또는 UNIQUE 인덱스가 아니라면 (달리 말하면, 만일 조인 (join)이 키 값을 기반으로 한 단일 (single) 열을 선택하지 않는다면), ref가 사용된다. 만일 사용된 키가 적은 수의 열에 대해서만 매치가 된다면, 그것은 좋은 조인 (join) 타입인 것이다.
   ref는 = 또는 <=> 연산자를 사용해서 비교되는 인덱스된 컬럼에 대해 사용될 수 있다. 아래의 예제에서 본다면, MySQL은 ref_table 처리 과정에서 ref 조인 (join)을 사용한다.
   SELECT * FROM ref_table WHERE key_column = expr; SELECT * FROM ref_table,other_table WHERE ref_table.key_column = other_table.column; SELECT * FROM ref_table,other_table WHERE ref_table.key_column_part1 = other_table.column AND ref_table.key_column_part2 = 1;
   
   
5. ref_or_null
   이 조인 (join) 타입은 ref과 유사하지만, MySQL이 NULL 값을 가지고 있는 열에 대해서도 검색을 한다는 점에서 차이가 있다. 이 조인 (join) 타입 최적화는 서브 쿼리(subqueries)를 해석할 때 자주 사용된다. 아래의 예제에서 보면, MySQL은 ref_table처리 과정에서 ref_or_null 조인 (join)을 사용하고 있다.
   SELECT * FROM ref_table WHERE key_column = expr OR key_column IS NULL;
   
   
6. index_merge
   이 조인 (join) 타입은 인덱스 병합 최적화가 사용되었음을 나타낸다. 이 경우에, 결과 열에 있는 key 컬럼은 사용된 인덱스 리스트를 가지고 있고, key_len는 사용된 인덱스에 대해서 가장 긴 키 부분의 리스트를 가지고 있다.
   
   
7. unique_subquery
   이 타입은 아래 형태의 IN 서브 쿼리 (subqueries)에 대해서 ref를 대체한다:
   value IN (SELECT primary_key FROM single_table WHERE some_expr) unique_subquery는 효율성을 위해서 서브 쿼리 (subquery)를 대체하는 인덱스 룩업(lookup) 함수이다.
   
   
8. index_subquery
   이것은 unique_subquery와 유사한 조인 (join) 타입이다. 이것은 IN 서브 쿼리(subqueries)를 대체하지만, 아래 형태의 서브 쿼리 (subquery)에 있는 논-유니크(non-unique)인덱스에 대해서도 동작을 한다:
   value IN (SELECT key_column FROM single_table WHERE some_expr)
   
9. range
   주어진 범위에 들어 있는 열만을 추출하며, 열 선택은 인덱스를 사용한다. 결과 열에 있는 key 컬럼은 어떤 인덱스가 사용되었는지를 가리킨다. key_len은 사용된 키에서 가장 긴 부분을 가진다. ref 컬럼은 이 타입에 대해서는 NULL 값이 된다.
   range는 키 컬럼이 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, 또는 IN 연산자를 사용하는 상수 (constant)와 비교할 때 사용될 수 있다:
   SELECT * FROM tbl_name WHERE key_column = 10; SELECT * FROM tbl_name WHERE key_column BETWEEN 10 and 20; SELECT * FROM tbl_name WHERE key_column IN (10,20,30); SELECT * FROM tbl_name WHERE key_part1 = 10 AND key_part2 IN (10,20,30);
   
   
10. index
    이 조인 (join) 타입은 ALL과 동일하지만, 인덱스 트리 (index tree)만을 스캔한다는 점에서 다르다. 일반적으로, 보통의 인덱스 파일이 데이터 파일보다 작기 때문에, 이것은  ALL 보다는 빠르게 동작한다.
    MySQL은 쿼리가 단일 인덱스의 일부분인 컬럼만을 사용할 때 이 조인 (join) 타입을 사용한다.
    
    
11. ALL
    이전 테이블에서 읽어온 각각의 열을 조합하기 위해 전체 테이블 스캔을 실행한다. 테이블이 const가 표시되지 않은 첫 번째 테이블이고, 다른 모든 경우에 있어서 매우 좋지 않은 경우라면, 이것은 그리 좋은 경우가 아니다. 일반적인 경우에는, 이전 테이블에서 가져온 상수(constant) 값 또는 컬럼 값을 사용해서 테이블 열을 추출하는 인덱스를 추가하면 ALL을 피할 수가 있다.
    
    
    
5. possible_keys
   possible_keys 컬럼은 이 테이블에서 열을 찾기 위해 MySQL이 선택한 인덱스를 가리킨다. 이 컬럼은 EXPLAIN 결과에서 나타나는 테이블 순서와는 전적으로 별개의 순서가 된다. 이것은, possible_keys에 있는 키 중에 어떤 것들은 테이블 순서를 만드는 과정에서는 사용되지 않을 수도 있음을 의미하는 것이다.
   만일 이 컬럼 값이 NULL이라면, 연관된 인덱스가 존재하지 않게 된다. 이와 같은 경우, 여러분은 WHERE 구문을 검사해서, 이 구문이 인덱스 하기에 적당한 컬럼을 참조하고 있는지 여부를 알아 봄으로써 쿼리 속도를 개선 시킬 수가 있게 된다. 그러한 경우라면, 적절한 인덱스를 하나 생성한 후에, EXPLAIN을 다시 사용해서 쿼리를 검사한다.
   테이블이 어떤 인덱스를 가지고 있는지를 보기 위해서는, SHOW INDEX FROM tbl_name를 사용한다.
   
   
6. key
   key 컬럼은 MySQL이 실제로 사용할 예정인 키 (인덱스)를 가리킨다. 만일 아무런 인덱스도 선택되지 않았다면, 그 키는 NULL이 된다. MySQL로 하여금 possible_keys 컬럼에 있는 인덱스를 사용하거나 또는 무시하도록 만들기 위해서, FORCE INDEX, USE INDEX, 또는 IGNORE INDEX를 쿼리에서 사용하도록 한다.
   MyISAM 및 BDB 테이블의 경우에는, ANALYZE TABLE를 구동시키면 옵티마이저가 보다 좋은 인덱스를 선택하도록 도움을 줄 수가 있다. MyISAM 테이블의 경우에는, myisamchk --analyze 가 동일한 역할을 한다.
   
   
7. key_len
   key_len 컬럼은 MySQL이 사용하기로 결정한 키의 길이를 나타낸다. 만일 key 컬럼이 NULL이라면, 이 값도 NULL이 된다. key_len 값은 다중-부분 (multiple-part) 키 중에 얼마나 많은 부분을 MySQL이 실제로 사용하는지를 여러분이 알 수 있도록 해 준다.
   
   
8. ref
   ref 컬럼은 테이블에서 열을 선택하기 위해 key 컬럼 안에 명명되어 있는 인덱스를 어떤 컬럼 또는 상수(constant)와 비교하는지를 보여준다.
   
   
9. rows
   rows 컬럼은 MySQL이 쿼리를 실행하기 위해 조사해야 하는 열의 숫자를 가리킨다.
   
   
10. Extra
    이 컬럼은 MySQL이 쿼리를 어떻게 해석하는지에 관한 추가적인 정보를 제공한다. 이 컬럼이 가질 수 있는 값은 다음과 같다.
1. Distinct
   MySQL은 명확한 값 (distinct value)를 찾게 되며, 따라서 MySQL이 매칭되는 열을 찾게 되면 더 이상의 열에 대해서는 검색을 중단한다.
   
   
2. Not exists
   MySQL은 쿼리상에서 LEFT JOIN 최적화를 실행 했으며, 이 최적화와 매치되는 열을 찾은 후에는 더 이상 이 테이블에서 이전 열 조합 검색을 하지 않게 된다. 이러한 방식으로 최적화가 되는 쿼리 타입의 예는 다음과 같다.
   SELECT * FROM t1 LEFT JOIN t2 ON t1.id = t2.id WHERE t2.id IS NULL; t2.id를 NOT NULL로 정의했다고 가정하자. 이와 같은 경우, MySQL은 t1을 스캔하고 t1.id 값을 사용해서 t2에 있는 열을 검색한다. 만일 MySQL이 t2에서 매칭되는 열을 발견하면, MySQL은 t2.id 가 결코 NULL이 아님을 알게 되며, 따라서 동일한 id 값을 가지고 있는 t2에서는 더 이상 열을 스캔하지 않게 된다. 달리 표현하면, t1에 있는 각 열에 대해서, MySQL은 t2에서는 단일 검색 (lookup)만을 하게 되며, t2에서 실제로 얼마나 많은 열이 매치가 되는지는 상관이 없게 된다.
   
   
3. range checked for each record (index map: N)
   MySQL은 사용하기에 좋은 인덱스를 찾지 못했으나, 이전 테이블에서 컬럼 값을 찾고 난 후에는 사용할 수도 있을 법한 인덱스는 알아냈다. 이전 테이블에 있는 각 열 조합에 대해서는, MySQL은 그 조합이 열을 추출하기 위해서 range 또는 index_merge 접근 방식을 사용할 수 있는지를 검사한다. 이 방법은 그리 빠른 방법은 아니지만, 인덱스를 전혀 사용하지 않는 것 보다는 빠르게 진행한다.
   
   
4. Using filesort
   MySQL은 저장된 순서에 따라서 열을 추출하는 방법을 찾기 위해 기타 과정을 진행한다. 정렬 (sort)은 조인 (join) 타입과 정렬 키 및 WHERE 구문과 매치가 되는 모든 열에 대한 열 포인터 (pointer)를 사용해서 모든 열에 걸쳐 진행 된다. 그런 다음에 그 키는 저장이 되고 열은 저장 순서에 따라서 추출된다.
   
   
5. Using index
   인덱스 트리에 있는 정보만을 가지고 테이블에서 컬럼 정보를 추출한다. 쿼리가 단일 인덱스의 일부 컬럼만을 사용하는 경우에, 이러한 전략을 사용할 수가 있다.
   
   
6. Using  temporary
   쿼리를 해석하기 위해서는, 결과를 저장할 임시 테이블을 하나 생성해야 한다. 만일 쿼리가 컬럼을 서로 다르게 목록화 하는 GROUP BY 및 ORDER BY 구문을 가지고 있는 경우에 이런 것이 일어나게 된다.
   
   
7. Using where 
   WHERE 구문은 다음 테이블에 대한 열 매치 (match) 또는 클라이언트에 보내지는 열을 제한하기 위해 사용된다. 테이블에서 모든 열을 조사하거나 불러올 의도가 특별히 없다면, Extra 값이 Using where 가 아니고, 테이블 조인 (join) 타입이 ALL 또는 index일 경우에는 쿼리에 문제가 생길 수도 있다.
   만일 여러분이 가능한 한 빠른 쿼리를 만들고 싶다면, Extra Using filesort 및 Using temporary 값을 조사하도록 한다.
   
   
8. Using sort_union(...), Using union(...), Using intersect(...)
   이것들은 인덱스 스캔이 어떻게 index_merge 조인 타입과 병합 (merge)이 되는지를 나타낸다. Section 7.2.6, “인덱스 병합 최적화”를 참조할 것.
   
   
9. Using index for group-by
   테이블 접근에 대한 Using index 방식과 유사한 Using index for group-by 방식은MySQL이 실제 테이블을 추가적으로 검색을 하지 않고서도, GROUP BY 또는 DISTINCT 쿼리의 모든 컬럼을 추출 (retrieve)하기 위해 사용될 수 있는 인덱스를 찾았음을 가리킨다. 또한, 그 인덱스는 각 그룹에 대해 가장 효과적인 방식으로 사용되기 때문에, 적은 수의 인덱스 엔트리만이 읽혀지게 된다.
   
   
10. Using where with pushed condition
    이 아이템은 NDB Cluster 테이블에만 적용된다. 이것은 MySQL 클러스터가 인덱스가 되지 않은 컬럼 (non-indexed column)과 상수 (constant) 간의 직접 비교 (direct comparision (=))의 효율성을 개선하기 위해서 조건문을 푸시 다운 (condition pushdown) 하는 중이라는 의미를 갖는다. 이와 같은 경우, 조건문은 동시에 값이 검사되는 클러스터의 모든 데이터 노드로 “푸시 다운 (pushed down)” 된다. 이것은 매치되지 않는 열을 네트워크 전체에 보낼 필요성을 없애 주며, 조건문 푸시 다운을 하지 않는 경우에 비해서 5 ~ 10배의 속도 향상을 얻을 수가 있다.