explain을 사용한 index 확인

mysql – explain을 사용한 index 확인#

EXPLAIN 옵션#

EXPLAIN [EXTENDED] SELECT select_options

EXPLAIN tbl_name =  DESCRIBE tbl_name = SHOW COLUMNS FROM tbl_name

EXPLAIN SELECT ~ : 쿼리 실행 플랜 정보를 옵티마이저로부터 호출 후 출력. 테이블 조인 순서 정보 및 SELECT 처리 정보 출력한다
SELECT : 사용된 각 테이블 정보 열을 리턴한다
MySQL이 쿼리를 처리하는 동안 읽을 수 있도록 테이블들은 결과물 안에 목록 정리
MySQL은 *single-sweep multi-join*을 통해서 모든 join을 reslove한다
MySQL이 첫 번째 테이블에서 열을 읽고, 그 다음에 두 번째 테이블에서 match된 데이터를 찾고, 이후 세 번째, 네 번째 등으로 이동을 의미한다
모든 테이블을 처리한 후 선택된 컬럼을 내보내고 테이블에서 매칭되는 다른 열이 있을 때까지 테이블을 역으로 검사한다
해당 테이블에서 그 다음 열을 읽고 다음 테이블로 이동을 해서 동일한 과정을 반복 진행한다
EXTENDED 키워드가 사용되면, EXPLAIN은 EXPLAIN 명령문 다음에 SHOW WARNINGS 명령문을 입력해서 볼 수 있는 기타 정보를 리턴한다
옵티마이저가 SELECT 명령문에 있는 컬럼 이름과 테이블 검증 과정 보여준다

EXPLAIN 출력 상세 정보#

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t_order	ref	idx_t_order_table_list,idx_t_order_no...	idx_t_order_no	8	const	34

id ( sequential number )
select_type
- SIMPLE : Simple SELECT (not using UNION or subqueries)
- PRIMARY : Outermost SELECT
- UNION : Second or later SELECT statement in a UNION
- DEPENDENTUNION :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)
- DEPENDEN : 전형적으로는 상호 연관된 (correlated) 서브 쿼리(subquery)의 사용을 의미한다
table : 결과 열이 참조하는 테이블
type ( 오름차순 ASC )
- system : 테이블은 하나의 열만을 가지고 있다 (= 시스템 테이블). 이것은 const조인 타입의 특별한 경우이다
- const
  - 테이블은 적어도 하나의 매칭 (matching) 테이블을 가지고 있는데, 쿼리가 시작되는 시점에서 이 테이블을 읽게 된다. 여기에는 하나의 열만이 존재하기 때문에, 이 열에 있는 컬럼에서 얻는 값은 나머지 옵티마이저에 의해 상수 (constant)로 인식될 수 있다. const 테이블은 한번 밖에 읽혀지지 않기 때문에 매우 빠르다.
  - PRIMARY KEY 또는 UNIQUE 인덱스의 모든 부분을 상수 값(constant value)과 비교를 할 때 사용된다. 아래의 쿼리에서 보면,tbl_name은 const 테이블 형태로 사용되고 있다
```
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 및 const 타입과는 달리, 이것이 가장 최선의 가능 조인 (join) 타입이다. 이것은 조인 (join)에 의해 인덱스의 모든 부분이 사용될 때 쓰이게 되며, 이때 인덱스는 PRIMARY KEY 또는 UNIQUE 인덱스가 된다
  - '=' 연산자를 사용해서 비교되는 인덱스된 컬럼용으로 사용될 수 있다. 비교 값은 이 테이블 전에 읽었던 테이블에서 컬럼을 사용한 수식 또는 상수 (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
  - 이전 테이블에서 읽어온 각각의 열을 조합하기 위해 이 테이블에서 매칭되는 인덱스 값을 가진 모든 열을 읽어온다. 만일 조인 (join)이 키의 좌측 끝(leftmost) 접두사 만을 사용하거나 또는 키 값이 PRIMARY KEY 또는 UNIQUE 인덱스가 아니라면 (만일 조인이 키 값을 기반으로 한 단일 (single) 열을 선택하지 않는다면) ref가 사용된다. 사용된 키가 적은 수의 열에 대해서만 매칭 된다면, 그것은 좋은 조인 (join) 타입이다
  - '=' 또는 '<=>' 연산자를 사용해서 비교되는 인덱스된 컬럼에 대해 사용될 수 있다. 아래처럼 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과 유사하지만, MySQL이 NULL 값을 가지고 있는 열에 대해서도 검색을 한다는 점에서 차이가 있다. 최적화 시 서브 쿼리를 해석할 때 자주 사용된다. 아래처럼 ref_table 처리 과정에서 ref_or_null 조인을 사용한다
```
SELECT * FROM ref_table WHERE key_column=expr OR key_column IS NULL;
```
- index_merge
  - 인덱스 병합 최적화 처리 시 표시다. 결과 열에 있는 key 컬럼은 사용된 인덱스 리스트를 가지고 있고, key_len는 사용된 인덱스에 대해서 가장 긴 키 부분의 리스트를 가지고 있다
- unique_subquery
  - 아래 형태의 IN 서브 쿼리에 대해서 ref를 대체한다
```
value IN (SELECT primary_key FROM single_table WHERE some_expr)
```
  - 효율성을 위해서 서브 쿼리를 대체하는 인덱스 lookup 함수이다
- index_subquery
  - unique_subquery와 유사한 조인타입으로 IN 서브 쿼리를 대체하지만, 아래 형태의 서브 쿼리에 있는 non-unique 인덱스에 대해서도 동작을 한다
```
value IN (SELECT key_column FROM single_table WHERE some_expr)
```
- range
  - 주어진 범위에 들어 있는 열만을 추출하며, 열 선택은 인덱스를 사용한다. 결과 열에 있는 key 컬럼은 어떤 인덱스가 사용되었는지를 가리킨다. key_len은 사용된 키에서 가장 긴 부분을 가진다. ref 컬럼은 이 타입에 대해서는 NULL 값이 된다.
  - 키 컬럼이 =, <>, >, >=, <, <=, 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
  - ALL과 동일하지만, index tree만을 스캔 한다는 점에서 다르다. 일반적으로, 보통의 인덱스 파일이 데이터 파일보다 작기 때문에, 이것은 ALL 보다는 빠르게 동작한다. 쿼리가 단일 인덱스의 일부분인 컬럼만을 사용할 때 사용한다
- ALL
  - 이전 테이블에서 읽어온 각각의 열을 조합하기 위해 전체 테이블 스캔을 실행한다. 테이블이 const가 표시되지 않은 첫 번째 테이블이고, 다른 모든 경우에 있어서 매우 좋지 않은 경우라면, 이것은 그리 좋은 경우가 아니다. 일반적인 경우에는, 이전 테이블에서 가져온 상수(constant) 값 또는 컬럼 값을 사용해서 테이블 열을 추출하는 인덱스를 추가하면 ALL을 피할 수가 있다
possible_keys
- 테이블에서 열을 찾기 위해 MySQL이 선택한 인덱스를 가리킨다. 이 컬럼은 EXPLAIN 결과에서 나타나는 테이블 순서와는 전적으로 별개의 순서가 된다. 이것은, possible_keys에 있는 키 중에 어떤 것들은 테이블 순서를 만드는 과정에서는 사용되지 않을 수도 있음을 의미하는 것이다
- 만일 이 컬럼 값이 NULL이라면, 연관된 인덱스가 존재하지 않게 된다. 이와 같은 경우, 여러분은 WHERE 구문을 검사해서, 이 구문이 인덱스 하기에 적당한 컬럼을 참조하고 있는지 여부를 알아 봄으로써 쿼리 속도를 개선 시킬 수가 있게 된다. 그러한 경우라면, 적절한 인덱스를 하나 생성한 후에, EXPLAIN을 다시 사용해서 쿼리를 검사한다
- 테이블이 어떤 인덱스를 가지고 있는지를 보기 위해서는, SHOW INDEX FROM tbl_name를 사용한다
key
- MySQL이 실제로 사용할 예정인 키 (인덱스)를 가리킨다. 만일 아무런 인덱스도 선택되지 않았다면, 그 키는 NULL이 된다. MySQL로 하여금 possible_keys 컬럼에 있는 인덱스를 사용하거나 또는 무시하도록 만들기 위해서,FORCE INDEX, USE INDEX, 또는 IGNORE INDEX를 쿼리에서 사용하도록 한다
- MyISAM 및 BDB 테이블의 경우에는, ANALYZE TABLE를 구동시키면 옵티마이저가 보다 좋은 인덱스를 선택하도록 도움을 줄 수가 있다. MyISAM 테이블의 경우에는, myisamchk --analyze 가 동일한 역할을 한다
key_len
- MySQL이 사용하기로 결정한 키의 길이를 나타낸다. 만일 key 컬럼이 NULL이라면, 이 값도 NULL이 된다. key_len 값은 multiple-part키 중에 얼마나 많은 부분을 MySQL이 실제로 사용하는지를 알 수 있도록 해준다
ref
- 테이블에서 열을 선택하기 위해 key 컬럼 안에 명명되어 있는 인덱스를 어떤 컬럼 또는 상수(constant)와 비교하는지를 보여준다
rows
- MySQL이 쿼리를 실행하기 위해 조사해야 하는 열의 숫자를 가리킨다
Extra
- distinct
  - 조건을 만족하는 레코드를 찾았을 때 같은 조건을 만족하는 또 다른 레코드가 있는지 검사하지 않는다
- not exist
  - left join 조건을 만족하는 하나의 레코드를 찾았을 때 다른 레코드의 조합은 더 이상 검사하지 않는다
- range checked for each record
  - 최적의 인덱스가 없는 차선의 인덱스를 사용을 뜻한다
- using filesort
  - mysql이 정렬을 빠르게 하기 위해 부가적인 일을 뜻한다.
- using index
  - select 할때 인덱스 파일만 사용을 뜻한다
- using temporary
  - 임시 테이블 사용시 order by 나 group by 할때 주로 사용한다
- where used
  - 조건을 사용한다는 의미한다

Last update: 2021년 2월 24일 02:10:46