반응형
1. 문장 끝에 ; 대신 \G 를 사용하면 결과를 세로로 볼 수 있다.
2. select 문장 앞에 explain 키워드를 사용하면 플랜을 볼 수 있다.
I. plan 결과 설명
※ http://dev.mysql.com/doc/refman/5.5/en/using-explain.html
1. select_type 값
- SIMPLE
. 평범한 SELECT 문장
- PRIMARY
. 하위 쿼리나 UNION을 사용할 때 외부(첫 번째) 쿼리
- UNION
. UNION 두 번째나 다음 번 쿼리
- DEPENDENT UNION
. UNION에서 첫 번째 쿼리에 따른 두 번째나 다음 번 쿼리
- UNION RESULT
. UNION 겨로가
- SUBQUERY
. 내부 하위 쿼리
- DEPENDENT SUBQUERY
. 첫 번째 쿼리에 따른 내부 하위 쿼리(즉, 상관 하위 쿼리)
- DERIVED
. FROM 절에서 사용한 하위 쿼리
- UNCACHEABLE SUBQUERY
. 그 결과가 캐쉬될 수 없으며 각 행마다 다시 계산되어야 하는 하위 쿼리
- UNCACHEABLE UNION
. UNCACHEABLE SUBQUERY에 속한 UNION에서 두 번째나 그 이후의 SELECT
2. table
- 쿼리에 응답하는 테이블 명
3. type
- 쿼리에서 테이블이 어떻게 조인되는지 설명
- const 또는 system
. 테이블이 한 번만 읽어온다. 테이블에 row가 단 하나만 있을 때 일어날 수 있다.
- eq_ref
. 조인하는 테이블에서 row 집합을 읽어올 때마다 이 테이블에서 한 row씩 읽는다.
테이블의 인덱스를 사용할 때 조인이 사용되며 인덱스는 UNIQUE하거나 기본 키이다.
- fulltext
. fulltext 인덱스를 사용하여 조인한다.
- ref
. 조인하는 테이블에서 row 집합을 읽어올 때마다 이 테이블에서 대응되는 row 집합을 읽어온다.
조인 조건에 따라서는 한 행만 선택할 수 없을 때, 조인에서 키의 일부분만이 사용되었을 때,
혹은 키가 UNIQUE 하지 않거나 기본 키가 아닐 때
- ref_or_null
. ref 쿼리와 비슷하지만 NULL 인 row 도 찾아본다. (하위 쿼리에서 가장 많이 사용된다)
- index_merge
. Index Merge 라는 특별한 최적화 기법이 사용되었다.
- unique_subquery
. 유일한 한 줄이 리턴될 때 IN 하위 쿼리에서 ref를 대신하여 사용할 수 있다.
- index_subquery
. unique_subquery 와 비숫하지만 인덱스된 유일하지 않은 하위 쿼리에서 사용한다.
- range
. 조인하는 테이블에서 row 집합마다 이 테이블 중 특정 범위에 들어가는 row들을 읽어온다.
- index
. 모든 인덱스를 읽는다.
- ALL
. 이 테이블의 모든 행을 읽는다.
4. rows
- 조인을 실행하기 위해 테이블마다 읽어야 하는 row의 수를 대충 계산
쿼리가 사용하는 총 row의 수를 얻기 위해서는 값들을 곱해야 한다.
5. possible_keys
- 테이블을 조인하기 위해 사용할 수 있는 키
- PRIMARY 는 테이블마다 일반적으로 있기 때문에 대부분 나온다.
6. key
- row 을 조인할 때 실제로 사용하고 있는 키
- NULL 은 키를 사용하고 있지 않다는 뜻이다.
7. key_len
- 사용된 키의 길이를 나타낸다.
8. ref
- 테이블에서 row를 선택할 때 사용한 키를 나타낸다.
9. Extra
- 어떻게 조인을 실행하는가에 대한 정보
- Distinct
. 처음으로 일치하는 줄이 발견되면 끝난다.
- Not exists
. LEFT JOIN 을 사용하여 쿼리가 최적화되어 있다.
- Range checked for each record
. 조인하는 테이블에서 row 집합을 읽어올 때마다 사용할 수 있는 인덱스를 찾는다.
- Using filesort
. 테이터를 정렬하려면 두 번 읽어야 한다. (따라서 시간도 두 배 걸린다)
- Using index
. 테이블에 있는 모든 정보를 인덱스에서 얻어왔다. 따라서 실제 row는 읽지 않았다.
- Using join buffer
. 조인 버퍼를 사용하여 테이블을 부분적으로 읽어 들인다. 그 후 쿼리를 수행할 때 버퍼에서 각 행을 추출한다.
- Using temporary
. 이 쿼리를 실행하는 동안 임시 테이블을 사용하였다.
- Using where
. 행을 선택하는 데 where 절을 사용하였다.
II. sql 쿼리 튜닝
1. 조인 최적화를 위해 키 분배를 검사
- myisamchk 유틸리티 사용
#>myisamchk --analyze <pathtomysqldatabase/table명>
#>myisamchk --analyze <pathtomysqldatabase/*.MYI>
#>myisamchk --analyze <pathtomysqldatadirectory/*/*.MYI>
- Analyze Table 문을 사용
sql>analyze table customers, orders, order_items, books;
2. 테이블 최적화
- 테이블의 단편화를 없앤다.
sql>optimize table <table명>;
#>myisamchk -r table
- 테이블의 인덱스를 정렬하고 인덱스에 맞게 데이터를 재배치 한다.
#>myisamchk --sort-index --sort-records=1 <pathtomysqldatadirectory/*/*.MYI>
2. select 문장 앞에 explain 키워드를 사용하면 플랜을 볼 수 있다.
explain
select * from User \G
select * from User \G
I. plan 결과 설명
※ http://dev.mysql.com/doc/refman/5.5/en/using-explain.html
1. select_type 값
- SIMPLE
. 평범한 SELECT 문장
- PRIMARY
. 하위 쿼리나 UNION을 사용할 때 외부(첫 번째) 쿼리
- UNION
. UNION 두 번째나 다음 번 쿼리
- DEPENDENT UNION
. UNION에서 첫 번째 쿼리에 따른 두 번째나 다음 번 쿼리
- UNION RESULT
. UNION 겨로가
- SUBQUERY
. 내부 하위 쿼리
- DEPENDENT SUBQUERY
. 첫 번째 쿼리에 따른 내부 하위 쿼리(즉, 상관 하위 쿼리)
- DERIVED
. FROM 절에서 사용한 하위 쿼리
- UNCACHEABLE SUBQUERY
. 그 결과가 캐쉬될 수 없으며 각 행마다 다시 계산되어야 하는 하위 쿼리
- UNCACHEABLE UNION
. UNCACHEABLE SUBQUERY에 속한 UNION에서 두 번째나 그 이후의 SELECT
2. table
- 쿼리에 응답하는 테이블 명
3. type
- 쿼리에서 테이블이 어떻게 조인되는지 설명
- const 또는 system
. 테이블이 한 번만 읽어온다. 테이블에 row가 단 하나만 있을 때 일어날 수 있다.
- eq_ref
. 조인하는 테이블에서 row 집합을 읽어올 때마다 이 테이블에서 한 row씩 읽는다.
테이블의 인덱스를 사용할 때 조인이 사용되며 인덱스는 UNIQUE하거나 기본 키이다.
- fulltext
. fulltext 인덱스를 사용하여 조인한다.
- ref
. 조인하는 테이블에서 row 집합을 읽어올 때마다 이 테이블에서 대응되는 row 집합을 읽어온다.
조인 조건에 따라서는 한 행만 선택할 수 없을 때, 조인에서 키의 일부분만이 사용되었을 때,
혹은 키가 UNIQUE 하지 않거나 기본 키가 아닐 때
- ref_or_null
. ref 쿼리와 비슷하지만 NULL 인 row 도 찾아본다. (하위 쿼리에서 가장 많이 사용된다)
- index_merge
. Index Merge 라는 특별한 최적화 기법이 사용되었다.
- unique_subquery
. 유일한 한 줄이 리턴될 때 IN 하위 쿼리에서 ref를 대신하여 사용할 수 있다.
- index_subquery
. unique_subquery 와 비숫하지만 인덱스된 유일하지 않은 하위 쿼리에서 사용한다.
- range
. 조인하는 테이블에서 row 집합마다 이 테이블 중 특정 범위에 들어가는 row들을 읽어온다.
- index
. 모든 인덱스를 읽는다.
- ALL
. 이 테이블의 모든 행을 읽는다.
4. rows
- 조인을 실행하기 위해 테이블마다 읽어야 하는 row의 수를 대충 계산
쿼리가 사용하는 총 row의 수를 얻기 위해서는 값들을 곱해야 한다.
5. possible_keys
- 테이블을 조인하기 위해 사용할 수 있는 키
- PRIMARY 는 테이블마다 일반적으로 있기 때문에 대부분 나온다.
6. key
- row 을 조인할 때 실제로 사용하고 있는 키
- NULL 은 키를 사용하고 있지 않다는 뜻이다.
7. key_len
- 사용된 키의 길이를 나타낸다.
8. ref
- 테이블에서 row를 선택할 때 사용한 키를 나타낸다.
9. Extra
- 어떻게 조인을 실행하는가에 대한 정보
- Distinct
. 처음으로 일치하는 줄이 발견되면 끝난다.
- Not exists
. LEFT JOIN 을 사용하여 쿼리가 최적화되어 있다.
- Range checked for each record
. 조인하는 테이블에서 row 집합을 읽어올 때마다 사용할 수 있는 인덱스를 찾는다.
- Using filesort
. 테이터를 정렬하려면 두 번 읽어야 한다. (따라서 시간도 두 배 걸린다)
- Using index
. 테이블에 있는 모든 정보를 인덱스에서 얻어왔다. 따라서 실제 row는 읽지 않았다.
- Using join buffer
. 조인 버퍼를 사용하여 테이블을 부분적으로 읽어 들인다. 그 후 쿼리를 수행할 때 버퍼에서 각 행을 추출한다.
- Using temporary
. 이 쿼리를 실행하는 동안 임시 테이블을 사용하였다.
- Using where
. 행을 선택하는 데 where 절을 사용하였다.
II. sql 쿼리 튜닝
1. 조인 최적화를 위해 키 분배를 검사
- myisamchk 유틸리티 사용
#>myisamchk --analyze <pathtomysqldatabase/table명>
#>myisamchk --analyze <pathtomysqldatabase/*.MYI>
#>myisamchk --analyze <pathtomysqldatadirectory/*/*.MYI>
- Analyze Table 문을 사용
sql>analyze table customers, orders, order_items, books;
2. 테이블 최적화
- 테이블의 단편화를 없앤다.
sql>optimize table <table명>;
#>myisamchk -r table
- 테이블의 인덱스를 정렬하고 인덱스에 맞게 데이터를 재배치 한다.
#>myisamchk --sort-index --sort-records=1 <pathtomysqldatadirectory/*/*.MYI>
반응형
