PHP & MySQL

MYSQL EXPLAIN

관리자 | 2013.10.28 12:10 | 조회 6200

7.2.1. EXPLAIN을 사용해서 쿼리 최적화 하기

 

EXPLAIN tbl_name

 

또는:

EXPLAIN [EXTENDED | PARTITIONS] SELECT select_options

EXPLAIN 명령문은 DESCRIBE에 대한 동의어로 사용할 수 있거나 또는 MySQLSELECT 명령문을 실행하는 방법에 대한 정보를 얻기 위한 수단으로 사용할 수가 있다:

  • EXPLAIN tbl_nameDESCRIBE tbl_name 또는 SHOW COLUMNS FROM tbl_name과 동일한 의미이다.
  • SELECT 명령문을 EXPLAIN 앞에 두면, MySQL은 쿼리 실행 플랜 (query execution plan) 정보를 옵티마이저 (optimizer)에서 가져 와서 출력 한다. , MySQL은 테이블들이 어떤 순서로 조인 (join) 하는지에 대한 정보를 포함해서, SELECT를 처리하는 방법에 대해서 알려 준다.
  • EXPLAIN PARTITIONSMySQL 5.1.5부터 사용 가능하다. 이것은 파티션된 테이블을 포함하는 쿼리를 실행할 때에만 유용하다. 보다 자세한 내용은 Section 16.3.4, “파티션 관련 정보 얻기를 참조하도록 한다.

이 섹션에서는 쿼리 실행 플랜 정보를 얻기 위한 EXPLAIN사용법을 설명한다. DESCRIBE and SHOW COLUMNS 명령문에 대한 설명은 Section 13.3.1, “DESCRIBE 신텍스, and Section 13.5.4.4, “SHOW COLUMNS 신텍스를 참조하기 바란다.

EXPLAIN를 사용함으로써, 여러분은 테이블의 어느 곳에 인덱스를 추가해야만 열을 찾기 위한 SELECT가 보다 빠르게 되는지를 알 수 있게 된다. 또한, EXPLAIN를 사용하면 옵티마이저가 최적의 (optimal) 순서로 테이블을 조인 (join)할 수 있는지 여부도 검사할 수가 있다. SELECT 명령문에 명명되어 있는 테이블의 순서와 상응하게 조인 (join) 순서를 사용하도록 옵티마이저를 만들기 위해서는, 명령문에 SELECT만을 사용하는 대신에 SELECT STRAIGHT_JOIN을 사용해서 시작을 하도록 한다.

만일 여러분 생각에는 사용 되어야만 했을 인덱스가 사용되지 않은 상태로 문제를 일으키게 되면, 키의 기수 (cardinality)와 같은 테이블 상태를 업데이트 하기 위해 ANALYZE TABLE을 구동 시켜야 하는데, 이것은 옵티마이저의 선택에 영향을 미치게 된다. Section 13.5.2.1, “ANALYZE TABLE 신텍스를 참조할 것.

EXPLAINSELECT 명령문에서 사용된 각 테이블 정보 열을 리턴한다. MySQL이 쿼리를 처리하는 동안 읽을 수 있도록 테이블들은 결과물 안에 목록으로 정리가 된다. MySQL single-sweep multi-join 방법을 사용해서 모든 조인 (join)을 풀어 버린다 (reslove). 이것은 MySQL이 첫 번째 테이블에서 열을 읽고, 그 다음에 두 번째 테이블에서 매치 (match)가 되는 것을 찾으며, 세 번째, 네 번째 등으로 이동을 한다는 것을 의미하는 것이다. 모든 테이블을 처리한 후에, MySQL은 선택된 컬럼을 내 보내고 테이블에서 매치되는 다른 열이 있을 때까지 테이블을 역으로 검사한다. 그 테이블에서 그 다음 열을 읽고 다음 테이블로 이동을 해서 동일한 과정을 반복 진행한다.

 

EXTENDED 키워드가 사용되면, EXPLAINEXPLAIN 명령문 다음에 SHOW WARNINGS 명령문을 입력해서 볼 수 있는 기타 정보를 리턴한다. 이 정보는 옵티마이저가 SELECT 명령문에 있는 컬럼 이름과 테이블을 얼마나 많이 검증을 하였는지를 보여주며, SELECT는 최적화 과정에 관한 어플리케이션 재 작성과 최적화 규칙, 그리고 다른 가능한 노트 (notes)를 보여준다. MySQL 5.1.12 이후부터는, EXPLAIN EXTENDED 또한 filtered 컬럼을 출력한다.

 

Note: 동일한 EXPLAIN 명령문에서는 EXTENDED and PARTITIONS 키워드를 함께 사용할 수가 없다.

 

EXPLAIN를 통해서 나오는 각각의 결과 열은 하나의 테이블에 대한 정보이며, 각 열은 아래의 컬럼을 가지고 있다:

  • id

SELECT 아이덴티파이어 (identifier). 이것은 쿼리 안에 있는 SELECT의 순차적인 번호(sequential number)이다.

  • select_type

SELECT에 대한 타입이며, 아래의 테이블에 있는 것 중에 하나가 된다:

 

SIMPLE

Simple SELECT (not using UNION or subqueries)

PRIMARY

Outermost SELECT

UNION

Second or later SELECT statement in a UNION

DEPENDENT UNION

Second or later SELECT statement in a UNION, dependent on outer query

UNION RESULT

Result of a UNION.

SUBQUERY

First SELECT in subquery

DEPENDENT SUBQUERY

First SELECT in subquery, dependent on outer query

DERIVED

Derived table SELECT (subquery in FROM clause)

UNCACHEABLE SUBQUERY

A subquery for which the result cannot be cached and must be re-evaluated for each row of the outer query

UNCACHEABLE UNION

The second or later select in a UNION that belongs to an uncachable subquery (see UNCACHEABLE SUBQUERY)

 

DEPENDENT는 전형적으로는 상호 연관된 (correlated) 서브 쿼리(subquery)의 사용을 의미한다.

 

“DEPENDENT SUBQUERY” 값 평가 (evaluation) UNCACHEABLE SUBQUERY값 평가는 서로 틀린 것이다. “DEPENDENT SUBQUERY”의 경우, 서브쿼리는 자신의 외부 컨텍스와는 다른 값을 가진 변수에 대해서만 한 번 재 평가를 한다. UNCACHEABLE SUBQUERY의 경우에는, 외부 컨텍스트의 각 열에 대해서 모두 재 평가를 한다.

  • table

결과 열이 참조하는 테이블.

  • type

조인 (join) 타입. 서로 다른 타입의 조인 (join)이 아래에 있는데, 가장 좋은 것부터 가장 나쁜 것의 순서로 되어 있다:

    • system

테이블은 하나의 열만을 가지고 있다 (= 시스템 테이블). 이것은 const 조인 (join) 타입의 특별한 경우이다.

    • const

테이블은 적어도 하나의 매칭 (matching) 테이블을 가지고 있는데, 쿼리가 시작되는 시점에서 이 테이블을 읽게 된다. 여기에는 하나의 열만이 존재하기 때문에, 이 열에 있는 컬럼에서 얻는 값은 나머지 옵티마이저에 의해 상수 (constant)로 인식될 수 있다. const 테이블은 한번 밖에 읽혀지지 않기 때문에 매우 빠르다.

 

constPRIMARY KEY 또는 UNIQUE 인덱스의 모든 부분을 상수 값 (constant value)과 비교를 할 때 사용된다. 아래의 쿼리에서 보면, tbl_nameconst 테이블 형태로 사용되고 있다:

 

SELECT * FROM tbl_name WHERE primery_key=1;

 

SELECT * FROM tbl_name

  WHERE primery_key_part1=1 AND primery_key_part2=2;

    • eq_ref

이전 테이블로부터 각 열을 조합 (combination)하기 위해서 이 테이블의 열을 하나읽는다. system and 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;

    • ref

이전 테이블에서 읽어온 각각의 열을 조합하기 위해 이 테이블에서 매칭 (matching)되는 인덱스 값을 가진 모든 열을 읽어온다. 만일 조인 (join)이 키의 좌측 끝(leftmost) 접두사 만을 사용하거나 또는 키 값이 PRIMARY KEY 또는 UNIQUE 인덱스가 아니라면 (달리 말하면, 만일 조인 (join)이 키 값을 기반으로 한 단일 (single) 열을 선택하지 않는다면), ref가 사용된다. 만일 사용된 키가 적은 수의 열에 대해서만 매치가 된다면, 그것은 좋은 조인 (join) 타입인 것이다.

 

ref= 또는 <=> 연산자를 사용해서 비교되는 인덱스된 컬럼에 대해 사용될 수 있다. 아래의 예제에서 본다면, MySQLref_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;

    • ref_or_null

이 조인 (join) 타입은 ref과 유사하지만, MySQLNULL 값을 가지고 있는 열에 대해서도 검색을 한다는 점에서 차이가 있다. 이 조인 (join) 타입 최적화는 서브 쿼리(subqueries)를 해석할 때 자주 사용된다. 아래의 예제에서 보면, MySQLref_table처리 과정에서 ref_or_null 조인 (join)을 사용하고 있다:

 

SELECT * FROM ref_table

  WHERE key_column=expr OR key_column IS NULL;

    • index_merge

이 조인 (join) 타입은 인덱스 병합 최적화가 사용되었음을 나타낸다. 이 경우에, 결과 열에 있는 key 컬럼은 사용된 인덱스 리스트를 가지고 있고, key_len는 사용된 인덱스에 대해서 가장 긴 키 부분의 리스트를 가지고 있다.

    • unique_subquery

이 타입은 아래 형태의 IN 서브 쿼리 (subqueries)에 대해서 ref를 대체한다:

 

value IN (SELECT prime_key FROM single_table WHERE some_expr)

 

unique_subquery는 효율성을 위해서 서브 쿼리 (subquery)를 대체하는 인덱스 룩업(lookup) 함수이다.

    • index_subquery

이것은 unique_subquery와 유사한 조인 (join) 타입이다. 이것은 IN 서브 쿼리 (subqueries)를 대체하지만, 아래 형태의 서브 쿼리 (subquery)에 있는 논-유니크 (non-unique)인덱스에 대해서도 동작을 한다:

 

value IN (SELECT key_column FROM single_table WHERE some_expr)

    • 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);

    • index

이 조인 (join) 타입은 ALL과 동일하지만, 인덱스 트리 (index tree)만을 스캔한다는 점에서 다르다. 일반적으로, 보통의 인덱스 파일이 데이터 파일보다 작기 때문에, 이것은  ALL 보다는 빠르게 동작한다.

 

MySQL은 쿼리가 단일 인덱스의 일부분인 컬럼만을 사용할 때 이 조인 (join) 타입을 사용한다.

    • ALL

이전 테이블에서 읽어온 각각의 열을 조합하기 위해 전체 테이블 스캔을 실행한다. 테이블이 const가 표시되지 않은 첫 번째 테이블이고, 다른 모든 경우에 있어서 매우 좋지 않은 경우라면, 이것은 그리 좋은 경우가 아니다. 일반적인 경우에는, 이전 테이블에서 가져온 상수(constant) 값 또는 컬럼 값을 사용해서 테이블 열을 추출하는 인덱스를 추가하면 ALL을 피할 수가 있다.

  • possible_keys

possible_keys 컬럼은 이 테이블에서 열을 찾기 위해 MySQL이 선택한 인덱스를 가리킨다. 이 컬럼은 EXPLAIN 결과에서 나타나는 테이블 순서와는 전적으로 별개의 순서가 된다. 이것은, possible_keys에 있는 키 중에 어떤 것들은 테이블 순서를 만드는 과정에서는 사용되지 않을 수도 있음을 의미하는 것이다.

 

만일 이 컬럼 값이 NULL이라면, 연관된 인덱스가 존재하지 않게 된다. 이와 같은 경우, 여러분은 WHERE 구문을 검사해서, 이 구문이 인덱스 하기에 적당한 컬럼을 참조하고 있는지 여부를 알아 봄으로써 쿼리 속도를 개선 시킬 수가 있게 된다. 그러한 경우라면, 적절한 인덱스를 하나 생성한 후에, EXPLAIN을 다시 사용해서 쿼리를 검사한다.

 

테이블이 어떤 인덱스를 가지고 있는지를 보기 위해서는, SHOW INDEX FROM tbl_name를 사용한다.

  • key

key 컬럼은 MySQL이 실제로 사용할 예정인 키 (인덱스)를 가리킨다. 만일 MySQLpossible_keys 인덱스 중의 하나를 사용해서 열을 검사할 예정이라면, 그 인덱스는 키 값 형태로 리스트된다.

 

key 이름을 possible_keys 값에는 존재하지 않는 인덱스로 지정하는 것은 가능하다. 이러한 경우는 possible_keys 인덱스 중의 어떠한 것도 열을 검사하기에는 적당하지 않지만, 쿼리가 선택한 모든 컬럼이 다른 인덱스 컬럼인 경우에 나타난다. , 네임드 인덱스가 선택된 컬럼을 커버하기 때문에, 비록 어떤 열을 추출할지 결정할 때 그것이 사용되지는 않더라도, 인덱스 스캔이 데이터 열 스캔보다 효율적인 방법이 된다.

 

InnoDB의 경우에는, InnoDB가 각각의 세컨더리 인덱스를 사용해서 프라이머리 키 값을 저장하기 때문에 쿼리 역시 프라이머리 키를 선택하더라도, 세컨더리 인덱스는 선택된 컬럼을 커버할 수도 있다. 만일 keyNULL이라면, MySQL은 쿼리를 보다 효율적으로 실행하기 위한 인덱스를 찾지 못하게 된다.

 

MySQL로 하여금 possible_keys 컬럼에 있는 인덱스를 사용하거나 또는 무시하도록 만들기 위해서, FORCE INDEX, USE INDEX, 또는 IGNORE INDEX를 쿼리에서 사용하도록 한다.

 

MyISAM and BDB 테이블의 경우에는, ANALYZE TABLE를 구동시키면 옵티마이저가 보다 좋은 인덱스를 선택하도록 도움을 줄 수가 있다. MyISAM 테이블의 경우에는, myisamchk --analyze 가 동일한 역할을 한다.

  • key_len

key_len 컬럼은 MySQL이 사용하기로 결정한 키의 길이를 나타낸다. 만일 key 컬럼이 NULL이라면, 이 값도 NULL이 된다. key_len 값은 다중-부분 (multiple-part) 키 중에 얼마나 많은 부분을 MySQL이 실제로 사용하는지를 여러분이 알 수 있도록 해 준다.

  • ref

ref 컬럼은 테이블에서 열을 선택하기 위해 key 컬럼 안에 명명되어 있는 인덱스를 어떤 컬럼 또는 상수(constant)와 비교하는지를 보여준다.

  • rows

rows 컬럼은 MySQL이 쿼리를 실행하기 위해 조사해야 하는 열의 숫자를 가리킨다.

  • filtered

filtered 컬럼은 테이블 정의문이 필터링하는 테이블 열을 추정한 비율이다. , rows는 조사된 열의 추정 숫자이며, rows × filtered / 100는 이전 테이블과 조인이 될 예정인 열의 숫자가 된다. 여러분이 EXPLAIN EXTENDED를 사용하게 되면, 이 컬럼이 출력된다 (MySQL 5.1.12에서 추가됨).

  • Extra

이 컬럼은 MySQL이 쿼리를 어떻게 해석하는지에 관한 추가적인 정보를 제공한다. 이 컬럼이 가질 수 있는 값은 다음과 같다. 쿼리를 가능한 한 빠르게 실행하고자 한다면, Using filesort and Using temporaryExtra 값을 분석해 보도록 한다.

    • Distinct

MySQL은 명확한 값 (distinct value)를 찾게 되며, 따라서 MySQL이 매칭되는 열을 찾게 되면 더 이상의 열에 대해서는 검색을 중단한다.

    • Full scan on NULL key

옵티마이저가 인덱스-룩업 (index-lookup) 접속 방식을 사용할 수 없을 때 펄백 (fallback) 방식으로 서브 쿼리 최적화를 할 때 이 값이 나온다.

    • Impossible WHERE noticed after reading const tables

MySQL이 모든  const (system) 테이블 값을 읽었으며, WHERE 구문이 항상 거짓 (false)이라는 것을 알고 있다.

    • No tables

쿼리에 FROM 구문이 없거나, 또는 FROM DUAL 구문을 하나 가지고 있다.

    • Not exists

MySQL은 쿼리상에서 LEFT JOIN 최적화를 실행 했으며, 이 최적화와 매치되는 열을 찾은 후에는 더 이상 이 테이블에서 이전 열 조합 검색을 하지 않게 된다. 이러한 방식으로 최적화가 되는 쿼리 타입의 예는 다음과 같다:

 

SELECT * FROM t1 LEFT JOIN t2 ON t1.id=t2.id

  WHERE t2.id IS NULL;

 

t2.idNOT NULL로 정의했다고 가정하자. 이와 같은 경우, MySQLt1을 스캔하고 t1.id 값을 사용해서 t2에 있는 열을 검색한다. 만일 MySQLt2에서 매칭되는 열을 발견하면, MySQLt2.id 가 결코 NULL이 아님을 알게 되며, 따라서 동일한 id 값을 가지고 있는 t2에서는 더 이상 열을 스캔하지 않게 된다. 달리 표현하면, t1에 있는 각 열에 대해서, MySQLt2에서는 단일 검색 (lookup)만을 하게 되며, t2에서 실제로 얼마나 많은 열이 매치가 되는지는 상관이 없게 된다.

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

MySQL은 사용하기에 좋은 인덱스를 찾지 못했으나, 이전 테이블에서 컬럼 값을 찾고 난 후에는 사용할 수도 있을 법한 인덱스는 알아냈다. 이전 테이블에 있는 각 열 조합에 대해서는, MySQL은 그 조합이 열을 추출하기 위해서 range 또는 index_merge 접근 방식을 사용할 수 있는지를 검사한다. 이 방법은 그리 빠른 방법은 아니지만, 인덱스를 전혀 사용하지 않는 것 보다는 빠르게 진행한다.

    • Select tables optimized away

쿼리는 인덱스, 또는 MyISAMCOUNT(*)을 사용하되 GROUP BY 구문은 사용하지 않은 채로 모두 처리된 집단 함수 (MIN(), MAX())만을 가지고 있다. 옵티마이저는 오직 하나의 열만을 리턴한다.

    • Using filesort

MySQL은 저장된 순서에 따라서 열을 추출하는 방법을 찾기 위해 기타 과정을 진행한다. 정렬 (sort)은 조인 (join) 타입과 정렬 키 and WHERE 구문과 매치가 되는 모든 열에 대한 열 포인터 (pointer)를 사용해서 모든 열에 걸쳐 진행 된다. 그런 다음에 그 키는 저장이 되고 열은 저장 순서에 따라서 추출된다. Section 7.2.11, “ORDER BY 최적화 하기를 참조할 것.

    • Using index

인덱스 트리에 있는 정보만을 가지고 테이블에서 컬럼 정보를 추출한다. 쿼리가 단일 인덱스의 일부 컬럼만을 사용하는 경우에, 이러한 전략을 사용할 수가 있다.

    • Using join cache

조인 캐시 버퍼는 테이블을 부분적으로 읽어온 후에, 읽어 온 열을 사용해서 조인을 실행한다.

    • Using temporary

쿼리를 해석하기 위해서는, 결과를 저장할 임시 테이블을 하나 생성해야 한다. 만일 쿼리가 컬럼을 서로 다르게 목록화 하는 GROUP BY and ORDER BY 구문을 가지고 있는 경우에 이런 것이 일어나게 된다.

    • Using where

WHERE 구문은 다음 테이블에 대한 열 매치 (match) 또는 클라이언트에 보내지는 열을 제한하기 위해 사용된다. 테이블에서 모든 열을 조사하거나 불러올 의도가 특별히 없다면, Extra 값이 Using where 가 아니고, 테이블 조인 (join) 타입이 ALL 또는 index일 경우에는 쿼리에 문제가 생길 수도 있다.

    • Using sort_union(...), Using union(...), Using intersect(...)

이것들은 인덱스 스캔이 어떻게 index_merge 조인 타입과 병합 (merge)이 되는지를 나타낸다.

    • Using index for group-by

Using index의 테이블 접속 방식과 유사하게, Using index for group-by는 디스크에 있는 실제 테이블을 추가적으로 접속하지 않은 채로 GROUP BY 또는 DISTINCT 쿼리의 모든 컬럼을 추출할 때 사용할 수 있는 인덱스를 가리킨다.

    • Using where with pushed condition

테이블 접근에 대한 Using index 방식과 유사한 Using index for group-by 방식은MySQL이 실제 테이블을 추가적으로 검색을 하지 않고서도, GROUP BY 또는 DISTINCT 쿼리의 모든 컬럼을 추출 (retrieve)하기 위해 사용될 수 있는 인덱스를 찾았음을 가리킨다. 또한, 그 인덱스는 각 그룹에 대해 가장 효과적인 방식으로 사용되기 때문에, 적은 수의 인덱스 엔트리만이 읽혀지게 된다.

    • Using where with pushed condition

이 아이템은 NDB Cluster 테이블에만 적용된다. 이것은 MySQL 클러스터가 인덱스가 되지 않은 컬럼 (non-indexed column)과 상수 (constant) 간의 직접 비교 (direct comparision (=))의 효율성을 개선하기 위해서 조건문을 푸시 다운 (condition pushdown) 하는 중이라는 의미를 갖는다. 이와 같은 경우, 조건문은 동시에 값이 검사되는 클러스터의 모든 데이터 노드로푸시 다운 (pushed down)된다. 이것은 매치되지 않는 열을 네트워크 전체에 보낼 필요성을 없애 주며, 조건문 푸시 다운을 하지 않는 경우에 비해서 5 ~ 10배의 속도 향상을 얻을 수가 있다.

 

여러분이 아래와 같은 클러스터 테이블을 가지고 있다고 가정하자:

 

CREATE TABLE t1 (

    a INT,

    b INT,

    KEY(a)

) ENGINE=NDBCLUSTER;

 

이와 같은 경우, 조건문 푸시 다운은 아래와 같은 쿼리와 함께 사용될 수 있다:

 

SELECT a,b FROM t1 WHERE b = 10;

 

이것은 EXPLAIN SELECT 결과를 통해서 볼 수가 있는데, 그 결과는 다음과 같다:

 

mysql> EXPLAIN SELECT a,b FROM t1 WHERE b = 10\G

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE

        table: t1

         type: ALL

possible_keys: NULL

          key: NULL

      key_len: NULL

          ref: NULL

         rows: 10

        Extra: Using where with pushed condition

 

조건문 푸시 다운은 다음의 쿼리와 함께 사용할 수 없다:

 

SELECT a,b FROM t1 WHERE a = 10;

SELECT a,b FROM t1 WHERE b + 1 = 10;

 

첫 번째 쿼리의 경우에는 조건문 푸시 다운을 적용할 수가 없는데, 그 이유는 인덱스가 컬럼 a에 존재하기 때문이다. 두 번째 경우에 대해서 조건문 푸시 다운을 사용할 수 없는 이유는, 인덱스가 되지 않은 컬럼 b를 포함하는 비교문이 간접적이기 때문이다. (하지만, WHERE 구문에서 b + 1 = 10 b = 9 로 줄여서 비교를 한다면 푸시 다운을 사용할 수가 있다.)

 

하지만, > 또는 < 연산자를 사용해서 인덱스된 컬럼을 상수(constant)와 비교를 하는 경우에는 조건문 푸시 다운을 사용할 수가 있다:

 

mysql> EXPLAIN SELECT a,b FROM t1 WHERE a<2\G

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE

        table: t1

         type: range

possible_keys: a

          key: a

      key_len: 5

          ref: NULL

         rows: 2

        Extra: Using where with pushed condition

 

조건문 푸시 다운에 관련해서는 아래의 사항을 기억하기 바란다:

      • 조건문 푸시 다운은 MySQL 클러스터에만 관련이 있으며, 다른 스토리지 엔진을 사용하고 있는 테이블에 대해서 쿼리를 실행할 경우에는 발생하지 않는다.
      • 조건문 푸시 다운 기능은 디폴트로 사용되지 않는다. 이 기능을 활성화 시키기 위해서는, mysqld--engine-condition-pushdown 옵션과 함께 시작하거나, 또는 아래의 명령문을 실행한다:

SET engine_condition_pushdown=On;

 

Note: BLOB 또는 TEXT 타입의 컬럼에 대해서는 조건문 푸시 다운이 지원되지 않는다.

여러분은 EXPLAIN 결과에 있는 rows 컬럼 값을 사용하면 좋은 조인 (join)을 얻는 방법을 알아 낼 수가 있다. 이것은 MySQL이 쿼리를 실행하기 위해서 조사해야 하는 열의 수가 얼마나 되는지 대략적으로 알려준다. 만일 여러분이 max_join_size 시스템 변수를 사용해서 쿼리를 제한하면, 이러한 열은 다중-테이블 SELECT 명령문을 실행해야 하는지 아니면 무시해야 하는지를 결정할 때 이 열 값을 사용할 수도 있다.

아래의 예제는 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;

위 예제에 대해서, 다음과 같은 가정을 하도록 하자:

  • 비교가 되는 컬럼은 아래와 같이 선언 되었다:

Table

Column

Data Type

Tt

ActualPC

CHAR(10)

tt

AssignedPC

CHAR(10)

tt

ClientID

CHAR(10)

et

EMPLOYID

CHAR(15)

do

CUSTNMBR

CHAR(15)

  • 테이블은 아래와 같은 인덱스를 가지고 있다:

Table

Index

tt

ActualPC

tt

AssignedPC

tt

ClientID

et

EMPLOYID (primary key)

do

CUSTNMBR (primary key)

  • tt.ActualPC 값은 골고루 분산되지 않는다.

최적화가 실행되지 전에, 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) 값을 생성하는 중이라는 것을 가리킨다; , 열에 대한 모든 조합. 이 과정은 각각의 테이블에 있는 열의 숫자를 조사해야 하기 때문에 시간이 오래 걸리게 된다. 위의 경우에는, 74 × 2135 × 74 × 3872 = 45,268,558,720 열이 된다.

만일 컬럼 상의 인덱스들을 동일한 타입과 크기로 선언할 경우에는, MySQL이 이를 효과적으로 처리하는데 한가지 문제가 생긴다. 위의 문장에서 보면, VARCHAR and CHAR의 크기를 동일하게 선언하면, 이것들은 동일한 것으로 간주가 된다. tt.ActualPCCHAR(10)로 선언 되었고 et.EMPLOYIDCHAR(15)로 선언 되었기 때문에, 길이가 맞지 않게 된다.

 

컬럼 길이 간의 이러한 문제를 해결 하기 위해서는, ALTER TABLE를 사용해서 ActualPC의 길이를 10 개 문자에서 15 문자로 늘리도록 한다:

 

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

 

이제 tt.ActualPC and et.EMPLOYID는 모두 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

이 결과 값은 완벽하지는 않지만 많이 개선이 된 것이다: rows 값은 74 보다 작다. 이와 같은 실행은 수초 정도 걸린다.

 

두 번째 대안으로는, tt.AssignedPC = et_1.EMPLOYID 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

 

이렇게 하고 나면, 쿼리는 거의 최적화가 이루어진다. 이제 남아 있는 문제는, MySQLtt.ActualPC 컬럼에 있는 값들은 골고루 분포되어 있으나, tt 테이블에 대해서는 그렇지 않다고 가정하고 있다는 점이다. 다행스러운 것은, MySQL로 하여금 키 분포도를 분석하도록 만드는 것이 쉽다는 점이다:

 

mysql> ANALYZE TABLE tt;

 

추가적인 인덱스 정보를 사용하면, 조인 (join)은 완벽해 지고 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 조인 (join) 최적화를 통해서 개선된다는 점을 알아두자. rows 가 만들어 내는 값과 쿼리가 리턴하는 열의 실제 숫자를 비교함으로써 그 숫자가 거의 일치하는지를 검사해야 한다. 만일 그 숫자가 많이 차이가 난다면, SELECT 명령문에서 STRAIGHT_JOIN을 사용하고 FROM 구문에서 테이블 순서를 다르게 하면 보다 나은 성능을 얻을 수가 있을 것이다.

 

 

 

 

 

출처 : http://www.mysqlkorea.co.kr/sub.html?mcode=manual&scode=01_1&lang=k&cat1=827&cat2=963

 

튜닝공부~

twitter facebook me2day 요즘
38개(1/2페이지)
PHP & MySQL
번호 제목 글쓴이 조회 날짜
38 [PHP] 파파고 API 관리자 2172 2021.01.29 16:33
37 [AWS] 아마존 개인화 amazon personalize-dg 첨부파일 관리자 2247 2020.01.22 19:33
36 [PHP] 소녀나라 구인공고 사진 관리자 3669 2018.04.17 12:54
35 [MYSQL] 게시판 페이징(?) 속도 향상 [1] 관리자 11688 2014.08.13 02:18
34 [MYSQL] 페이징 쿼리, 페이징 기법 관리자 22156 2014.08.13 01:48
33 [MYSQL] 이메일 필터 정규식 관리자 8136 2014.07.25 11:32
32 [PHP] Zen HTML Selectors 관리자 5961 2014.03.12 17:16
31 [PHP] zen coding~! 젠코딩 Zen HTML Elements 관리자 8186 2014.03.12 17:16
30 [PHP] 에디트 플러스 zen coding CSS 관리자 7630 2014.03.12 17:16
29 [PHP] php + jquery ajax + json 관리자 5708 2014.03.04 15:30
28 [PHP] php 엑셀 파일 생성시 한글깨짐 관리자 8817 2014.02.21 16:31
27 [PHP] 날씨 API, 기상청 날씨 파싱 관리자 10962 2014.01.22 15:32
26 [PHP] 도로명주소 관리자 5802 2014.01.09 11:39
>> [MYSQL] MYSQL EXPLAIN 관리자 6201 2013.10.28 12:10
24 [PHP] PHP, AJAX, JSON 리턴 관리자 7458 2013.10.18 17:49
23 [MYSQL] mysql성능 향상 첨부파일 관리자 4493 2013.10.07 12:52
22 [MYSQL] mysql 최적화 관리자 5241 2013.10.07 12:35
21 [PHP] PHP 변수 초기화 및 조건부 할당 관리자 6537 2013.09.25 00:57
20 [PHP] 킴스큐 썸네일 사이즈 변경 관리자 4876 2013.07.16 14:14
19 [PHP] 간단하게 만드는 캐싱 사진 관리자 5712 2013.05.21 14:11
많이 본 글
댓글 많은 글