7.2.1. Optimizing Queries with EXPLAIN

영문 메뉴얼을 보고있자니..
속이 울렁울렁~ 졸음이 오는것같기도 하는 기분;;
url   http://dev.mysql.com/doc/refman/5.0/en/using-explain.html

내가 알지 못하는 훌륭하신분이 해석해주셨다
오오~ 위대하다~ ^0^
url   http://database.sarang.net/?inc=read&aid=24199&criteria=mysql

해석한 내용

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 타입을 피할 수 있다.

 

• possible_keys

  이 컬럼값은 MySQL 이 해당 테이블의 검색에 사용할수 있는 인덱스들을 나타낸다.
  주의할것은 explain 결과에서 나타난 테이블들의 순서와는 무관하다는 것이다.
  이것은 possible_keys 에 나타난 인덱스들이 결과에 나타난 테이블 순서에서 실제 사용할 수 없을수도 있다는 것을 의미한다.
  이값이 NULL 이라면 사용가능한 인덱스가 없다는 것이다. 이러한 경우에는 인덱스를 where 절을 고려하여 사용됨직한 적절한 컬럼에 인덱스를 추가함으로써 성능을 개선할 수 있다. 인덱스를 수정하였다면 다시한번 EXPLAIN 을 실행하여 체크하라.
  See Section 13.2.2, “ALTER TABLE Syntax”.

  현재 테이블의 인덱스를 보기 위해서는 SHOW INDEX FROM tbl_name.을 사용하라.

• key

  이 컬럼은 MySQL 이 실제 사용한 key(index) 를 나타낸다.
  만약 사용한 인덱스가 없다면 NULL 값일 것이다. MySQL 이 possible_keys 에 나타난 인덱스를 사용하거나 사용하지 않도록 강제하려면 FORCE INDEX, USE INDEX, 혹은 IGNORE INDEX 를 함께 사용하라.
  See Section 13.1.7, “SELECT Syntax”.

  MyISAM 과 BDB 테이블에서는 ANALYZE TABLE 이 옵티마이저가 더나은 인덱스를 선택할 수 있도록 테이블의 정보를 갱신한다.
  MyISAM 에서는 myisamchk --analyze 가 같은 기능을 한다.
  See Section 13.5.2.1, “ANALYZE TABLE Syntax” and Section 5.7.2, “Table Maintenance and Crash Recovery”.

• key_len

  이 컬럼은 MySQL 이 사용한 인덱스의 길이를 나타낸다. key 컬럼값이 NULL 이면 이값도 NULL 이다.
  key_len 값으로 MySQL 이 실제 복수컬럼 키중 얼마나 많은 부분을 사용할 것인지 알 수 있다.
  

• ref

  이 컬럼은 행을 추출하는데 키와 함께 사용된 컬럼이나 상수값을 나타낸다.
  

   

• rows

  이 값은 쿼리 수행에서 MySQL 이 예상하는 검색해야할 행수를 나타낸다.
  

• Extra

  이 컬럼은 MySQL 이 쿼리를 해석한 추가적인 정보를 나타낸다.
  아래와 같은 값들이 나타날 수 있다.
  

  • Distinct

    MySQL 이 매치되는 첫행을 찾는 즉시 검색을 중단할 것이다.
    

  • 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 에서 매치되는 행을 찾으면 t2.id 는 NULL 이 될 수 없으므로 더이상 진행하지 않는다. 즉, t1 의 각 행에 대해 t2 에서 매치되는 행이 몇개가 있던지 한개만 찾으면 된다.

     

  • range checked for each record (index map: #)

    MySQL 이 사용할 좋은 인덱스가 없다. 그러나 선행된 테이블의 컬럼값에 따라 몇몇 인덱스를 사용할 수 있다. 선행된 테이블의 개개 행에 대해 MySQL 이 range 나 index_merge 접근법을 사용할 수 있는지 체크할 것이다.
    적용가능성의 핵심은 Section 7.2.5, “Range Optimization” and Section 7.2.6, “Index Merge Optimization” 에 모든 선행된 테이블의 값이 명확하거나 상수인 때를 예외로 하여 기술되어 있다.
    이것은 그리 빠르진 않으나 인덱스가 없는 조인의 경우보다는 빠르다.

     

  • Using filesort

    MySQL 이 정렬을 위해 추가적인 과정을 필요로한다. 정렬과정은 조인타입에 따라 모든 행을 검색하고 WHERE 절에 의해 매치된 모든 행들의 키값을 저장한다. 그리고 저장된 키값을 정렬하여 재정렬된 순서로 행들을 추출한다.
    See Section 7.2.10, “How MySQL Optimizes ORDER BY”.

   

  • Using index

    컬럼정보가 실제 테이블이 아닌 인덱스트리에서 추출된다. 쿼리에서 단일 인덱스된 컬럼들만을 사용하는 경우이다.

   

  • Using temporary
MySQL 이 결과의 재사용을 위해 임시테이블을 사용한다. 쿼리 내에 GROUP BY 와 ORDER BY 절이 각기 다른 컬럼을 사용할때 발생한다.

   

  • Using where
WHERE 절이 다음 조인에 사용될 행이나 클라이언트에게 돌려질 행을 제한하는 경우이다. 테이블의 모든 행을 검사할 의도가 아니라면 Extra 값이 Using where 가 아니고 조인타입이 ALL 이나 index 라면 쿼리사용이 잘못되었다.
    

  • 쿼리를 가능한 한 빠르게 하려면, Extra 값의 Using filesort 나 Using temporary 에 주의해야 한다.
    

  • Using sort_union(...) , Using union(...) , Using intersect(...)
이들은 인덱스 병합 조인타입에서 인덱스 스캔이 병합되는 형태를 말한다.


    See Section 7.2.6, “Index Merge Optimization” for more information.

   

  • Using index for group-by

    테이블 접근방식은 Using index 와 같다. MySQL 이 실제 테이블에 대한 어떠한 추가적인 디스크 접근 없이 GROUP BY 나 DICTINCT 쿼리에 사용된 모든 컬럼에 대한 인덱스를 찾았음을 말한다. 추가적으로 각각의 group 에 단지 몇개의 인덱스 항목만이 읽혀지도록 가장 효율적인 방식으로 인덱스가 사용될 것이다.
    For details, see Section 7.2.11, “How MySQL Optimizes GROUP BY”.

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

 

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

EXPLAIN SELECT tt.TicketNumber, tt.TimeIn,
            tt.ProjectReference, tt.EstimatedShipDate,
            tt.ActualShipDate, tt.ClientID,
            tt.ServiceCodes, tt.RepetitiveID,
            tt.CurrentProcess, tt.CurrentDPPerson,
            tt.RecordVolume, tt.DPPrinted, et.COUNTRY,
            et_1.COUNTRY, do.CUSTNAME
        FROM tt, et, et AS et_1, do
        WHERE tt.SubmitTime IS NULL
            AND tt.ActualPC = et.EMPLOYID
            AND tt.AssignedPC = et_1.EMPLOYID
            AND tt.ClientID = do.CUSTNMBR;

이 예에서 아래와 같은 가정이 사용되었다.:

• 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 은 다음과 같은 정보를 보여준다.:

table type possible_keys key  key_len ref  rows  Extra
et    ALL  PRIMARY       NULL NULL    NULL 74
do    ALL  PRIMARY       NULL NULL    NULL 2135
et_1  ALL  PRIMARY       NULL NULL    NULL 74
tt    ALL  AssignedPC,   NULL NULL    NULL 3872
           ClientID,
           ActualPC
      range checked for each record (key map: 35)

각 테이블의 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 을 실행해보면 다음 결과와 같다.

 

table type   possible_keys key     key_len ref         rows    Extra
tt    ALL    AssignedPC,   NULL    NULL    NULL        3872    Using
             ClientID,                                         where
             ActualPC
do    ALL    PRIMARY       NULL    NULL    NULL        2135
      range checked for each record (key map: 1)
et_1  ALL    PRIMARY       NULL    NULL    NULL        74
      range checked for each record (key map: 1)
et    eq_ref PRIMARY       PRIMARY 15      tt.ActualPC 1

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

이 쿼리는 이제 몇초만에 실행될 것이다.

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

 

mysql> ALTER TABLE tt MODIFY AssignedPC VARCHAR(15),
    ->                MODIFY ClientID   VARCHAR(15);

이제 EXPLAIN 은 다음과 같은 결과를 보여준다.

table type   possible_keys key      key_len ref           rows Extra
et    ALL    PRIMARY       NULL     NULL    NULL          74
tt    ref    AssignedPC,   ActualPC 15      et.EMPLOYID   52   Using
             ClientID,                                         where
             ActualPC
et_1  eq_ref PRIMARY       PRIMARY  15      tt.AssignedPC 1
do    eq_ref PRIMARY       PRIMARY  15      tt.ClientID   1

이것은 이제 거의 최적의 결과가 된 것 같다.

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

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

mysql> ANALYZE TABLE tt;

이제 완벽한 조인이 되었다. EXPLAIN 결과는 다음과 같다.

table type   possible_keys key     key_len ref           rows Extra
tt    ALL    AssignedPC    NULL    NULL    NULL          3872 Using
             ClientID,                                        where
             ActualPC
et    eq_ref PRIMARY       PRIMARY 15      tt.ActualPC   1
et_1  eq_ref PRIMARY       PRIMARY 15      tt.AssignedPC 1
do    eq_ref PRIMARY       PRIMARY 15      tt.ClientID   1

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

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