데이터베이스를 사랑하는 사람들의 모임 데이터베이스 사랑넷

# 이글은 mysql document 의 7.2.1 Explain Syntax 를 대~충 번역한 것입니다.

EXPLAIN 을 사용함으로써 인덱스가 적절히 사용되고 있는지 검토할 수 있다. 인덱스가 잘못 사용되고 있다면 ANALYZE TABLE 을 사용하여 테이블을 점검하라.
이것은 테이블의 상태를 갱신하며 옵티마이저의 동작에 영향을 준다.

옵티마이저가 SELECT 에 기록된 순서대로 조인을 행하게 강제하려면 SELECT 대신에 SELECT STRAIGHT_JOIN 을 사용하라.

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

MYSQL 은 모든 조인을 single-sweep multi-join 방식을 사용하여 해결한다. 이것은 MYSQL 이 첫번째 테이블에서 한행을 읽고, 두번째 테이블에서 매치되는 행을 찾고, 세번째 테이블에서 매치되는 행을 찾고.. 그러한 방식이다. 모든 테이블들이 처리된 후 추출된 컬럼을 출력하고 다시 처음 테이블로 돌아가서 조인을 계속한다. 이런식으로 첫번째 테이블에 더이상 남는행이 없을때까지 실행한다.

(어느것이 첫번째 테이블이 될지는 mysql 옵티마이저가 결정할 문제이다. STRAIGHT_JOIN 을 명시하지 않았다면 유저가 입력한 순서와는 관련이 없다.)

MYSQL 4.1 버전에서 EXPLAIN 의 출력포멧이 UNION 과 subquery, derived table 을 다루기에 더 효과적으로 변경되었다. 무엇보다 중요한 것은 id , select_type 의 두 컬럼이 추가된 것이다.

EXPLAIN 의 각 행은 하나의 테이블에 대한 정보를 보여주며 다음과 같은 컬럼들로 구성된다.

id

SELECT 번호, 쿼리내의 SELECT 의 구분번호이다.
select_type

SELECT 의 타입, 다음과 같다.
- SIMPLE
  
  단순 SELECT (UNION 이나 서브쿼리를 사용하지 않음)
- PRIMARY
  
  가장 외곽의 SELECT
- UNION
  
  UNION 에서의 두번째 혹은 나중에 따라오는 SELECT
- DEPENDENT UNION
  
  UNION 에서의 두번째 혹은 나중에 따라오는 SELECT, 외곽쿼리에 의존적이다.
- UNION RESULT
  
  UNION 의 결과물.
- SUBQUERY
  
  서브쿼리의 첫번째 SELECT
- DEPENDENT SUBQUERY
  
  서브쿼리의 첫번째 SELECT, 외곽쿼리에 의존적이다.
- DERIVED
  
  SELECT 로 추출된 테이블 (FROM 절 내부의 서브쿼리)
table

나타난 결과가 참조하는 테이블명.
type

조인타입, 아래와 같다. 우수한 순서대로 뒤로갈수록 나쁜 조인형태이다.
- system
  
  테이블에 단 하나의 행만 존재(시스템 테이블). const join 의 특수한 경우이다.
- const
  
  많아야 하나의 매치되는 행만 존재하는 경우. 하나의 행이기 때문에 각 컬럼값은 나머지 연산에서 상수로 간주되며, 처음 한번만 읽어들이면 되기 때문에 무지 빠르다.
  PRIMARY KEY 나 UNIQUE index 를 상수와 비교하는 경우.
  아래의 경우에서 tbl_name 은 const table 로 조인된다.
  
  SELECT * FROM tbl_name WHERE primary_key=1;
  SELECT * FROM tbl_name
  WHERE primary_key_part1=1 AND primary_key_part2=2;
- eq_ref
  
  조인수행을 위해 각 테이블에서 하나씩의 행만이 읽혀지는 형태. const 타입이외에 가장 훌륭한 조인타입니다.
  조인연산에 PRIMARY KEY 나 UNIQUE index 인덱스가 사용되는 경우.
  인덱스된 컬럼이 = 연산에 사용되는 경우. 비교되는 값은 상수이거나 이전조인결과의 컬럼값일수 있다.
  다음 예에서 MySQL 은 ref_table 을 처리하는데 eq_ref 조인을 사용한다.
  
  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;
- ref
  
  이전 테이블과의 조인에 사용될 매치되는 인덱스의 모든행이 이 테이블에서 읽혀진다. leftmost prefix 키만을 사용하거나 사용된 키가 PRIMARY KEY 나 UNIQUE 가 아닐때(즉 키값으로 단일행을 추출할수 없을때) 사용되는 조인.
  만약 사용된 키가 적은수의 행과 매치될때 이것은 적절한 조인 타입니다.
  ref 는 인덱스된 컬럼과 = 연산에서 사용된다.
  아래 예에서 MySQL 은 ref_table 처리에 ref 조인 타입을 사용한다.
  
  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;
- ref_or_null
  
  ref 와 같지만 NULL 값을 포함하는 행에대한 검색이 수반된다.
  4.1.1 에서 새롭게 도입된 조인타입이며 서브쿼리 처리에서 대개 사용된다.
  아래 예에서 MySQL 은 ref_table 처리에 ref_or_null 조인타입을 사용한다.
```
SELECT * FROM ref_table
WHERE key_column=expr OR key_column IS NULL; 
```
  See Section 7.2.7, “How MySQL Optimizes IS NULL”.
- index_merge
  
  인덱스 병합 최적화가 적용되는 조인 타입.
  이 경우, key 컬럼은 사용된 인덱스의 리스트를 나타내며 key_len 컬럼은 사용된 인덱스중 가장 긴 key 명을 나타낸다.
  For more information, see Section 7.2.6, “Index Merge Optimization”.
- unique_subquery
  
  이것은 아래와 같은 몇몇 IN 서브쿼리 처리에서 ref 타입대신 사용된다.
  value IN (SELECT primary_key FROM single_table WHERE some_expr)
  
  unique_subquery 는 성능향상을 위해 서브쿼리를 단순 index 검색 함수로 대체한다.
- index_subquery
  
  unique_subquery 와 마찬가지로 IN 서브쿼리를 대체한다. 그러나 이것은 아래와 같이 서브쿼리에서 non-unique 인덱스가 사용될때 동작한다.
  value IN (SELECT key_column FROM single_table WHERE some_expr)
- range
  
  인덱스를 사용하여 주어진 범위 내의 행들만 추출된다. key 컬럼은 사용된 인덱스를 나타내고 key_len 는 사용된 가장 긴 key 부분을 나타낸다.
  ref 컬럼은 이 타입의 조인에서 NULL 이다.
  range 타입은 키 컬럼이 상수와 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN 또는 IN 연산에 사용될때 적용된다.
```
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);
```
- index
  
  이 타입은 인덱스가 스캔된다는걸 제외하면 ALL 과 같다. 일반적으로 인덱스 파일이 데이타파일보다 작기 때문에 ALL 보다는 빠르다.
  MySQL 은 쿼리에서 단일 인덱스의 일부분인 컬럼을 사용할때 이 조인타입을 적용한다.
- ALL
  
  이전 테이블과의 조인을 위해 풀스캔이 된다. 만약 (조인에 쓰인) 첫번째 테이블이 고정이 아니라면 비효율적이다, 그리고 대부분의 경우에 아주 느린 성능을 보인다. 보통 상수값이나 상수인 컬럼값으로 row를 추출하도록 인덱스를 추가함으로써 ALL 타입을 피할 수 있다.

EXPLAIN 의 출력내용중 rows 컬럼값들을 곱해봄으로써 얼마나 효과적인 join 을 실행하고 있는지 알 수 있다. 이 값은 MySQL 이 쿼리수행중 검사해야할 행수를 대략적으로 알려준다. 만약 max_join_size 시스템 변수값을 설정하였다면 이 값은 또한 여러테이블을 사용하는 select 중 어느것을 먼저 실행할지 판단하는데 사용된다.
See Section 7.5.2, “Tuning Server Parameters”.

다음 예는 다중테이블 조인이 EXPLAIN 정보를 통해 점차적으로 개선되는 과정을 보여준다. 만약 아래와 같은 select 문을 EXPLAIN 으로 개선한다면 :

The columns being compared have been declared as follows:

Table	Column	Column Type
`tt`	`ActualPC`	`CHAR(10)`
`tt`	`AssignedPC`	`CHAR(10)`
`tt`	`ClientID`	`CHAR(10)`
`et`	`EMPLOYID`	`CHAR(15)`
`do`	`CUSTNMBR`	`CHAR(15)`

The tables have the following indexes:

Table	Index
`tt`	`ActualPC`
`tt`	`AssignedPC`
`tt`	`ClientID`
`et`	`EMPLOYID` (primary key)
`do`	`CUSTNMBR` (primary key)

The tt.ActualPC values are not evenly distributed.

먼저, 개선되기 전의 EXPLAIN 은 다음과 같은 정보를 보여준다.:

각 테이블의 type 이 ALL 을 나타내므로, MySQL 이 모든 테이블의 카티션곱(Cartesian product) 를 생성한다는 것을 나타낸다.
각 테이블의 행의 조합이 모두 검사되어야 하기 때문에 이것은 아주 오랜 시간이 소요될 것이다.

실제로 이 결과는 74 * 2135 * 74 * 3872 = 45,268,558,720 행에 달한다.
만약 테이블이 더 크다면 얼마나 소요될지 상상할 수도 없을 것이다.
여기서 우선적인 문제는 MySQL 은 같은 타입으로 선언된 컬럼의 인덱스를 더 효과적으로 사용할 수 있다는 것이다. (ISAM 테이블에서는 같은 타입으로 선언되지 않은 인덱스는 사용할 수 없다.) 여기에서 VARCHAR 과 CHAR 은 길이가 다르지 않다면 같은 타입이다.
tt.ActualPC 는 CHAR(10) 이고 et.EMPLOYID 는 CHAR(15) 로 선언되어 있으므로 길이의 불일치가 발생한다.

이러한 컬럼 길이의 불일치 문제의 해결을 위해 ALTER TABLE 을 사용하여 ActualPC 컬럼을 10 글자에서 15 글자로 변경하자 (길이를 늘리는것은 데이타 손실이 없다.)

mysql> ALTER TABLE tt MODIFY ActualPC VARCHAR(15);

이제 tt.ActualPC 와 et.EMPLYID 는 모두 VARCHAR(15) 이다. 다시 EXPLAIN 을 실행해보면 다음 결과와 같다.

훨씬 좋아졌지만 아직 완벽하지 않다. 행의 곱은 이제 74 만큼 줄었다.

두번째 작업은 tt.AssignedPC = et_1.EMPLYID 와 tt.ClientID = do.CUSTNMBR 에서의 컬럼길이의 불일치를 수정하는 것이다.

남아있는 문제는 MySQL 이 기본으로 tt.ActualPC 컬럼의 값이 균등하게 분포되어 있다고 가정한다는 것이다. 하지만 tt 테이블은 실제로 그렇지 않다.

다행히도 MySQL 이 키 분포를 검사하도록 하는것은 매우 쉽다.

EXPLAIN 결과의 rows 컬럼값이 나타내는 MySQL 최적화에 의해 예측된 행수에 주목하라.

나타난 숫자가 실제 행수에 근접한지 체크해야 한다. 그렇지 않다면 STRAIGHT_JOIN 를 사용고 FROM 절에서 테이블의 순서를 변경함으로써 더 나은 성능을 얻을 수 있다.