실행계획(explain) 사용

1. Mysql 실행계획(explain) 사용

- SQL문 앞에 explain을 붙여주면 된다

 

2. type 컬럼

1) ALL -- 테이블 풀스캔

--> 풀스캔이 무조건 나쁜 것은 아니다 where 조건절을 걸어도 옵티마이저가 검색row수가 적을 때는 풀스캔으로 처리할 경우도 많다.

 

2) index  -- 인덱스 스캔, extra 컬럼 using index 출력시 비용감소 확인

 

3) range -- 래인지 스캔은 제한된 형태의 인덱스 스캔이다. 레인지 스캔의 경우 인덱스의 특정부분에서 시작해서 특정 범위에 있는 값을 가지는 행을 반환한다. 전체 인덱스를 살펴봐야 할 필요는 없으므로 전체 인덱스 스캔보다 더 나은 성능을 보인다. where 절에 Between 이나 >가 있을 경우 범위 검색을 사용하게 되며, in() 이나 OR 리스트와 같이 어떤 값들의 리스트를 찾는 경우에도 인덱스를 사용할 수 있다. 이경우에도 레인지 스캔이라고 표시되지만 접근방법에는 큰자이가 있고, 성능상에도 꽤 차이가 난다.

 

4) ref -- 이방식은 어떤 값 하나에 대해 매치되는 행들을 반환해주는 인덱스 접근방식이다. 이때 같은 값을 가지는 여러 개의 행을 찾게 될 수도 있으므로 탐색과 스캔이 함께 사용되며, 고유하지 않은 값에 대한 인덱스나 고유하지 않은 프리픽스에 대한 인덱스에만 이런식으로 접근할 수 있다. 인덱스를 어떤 참조(reference) 값과 비교하기 때문에 ref라고 불리며, 참조 값은 상수여야하며, 다중-테이블 쿼리의 경우엔 이전 테이블에서 넘어온 값도 참조 값으로 사용될 수 있다.

접근 방식 ref_or_null은 ref방식의 변형으로 Mysql이 초기 탐색을 마친후 NULL 엔트리들을 찾기 위해 두 번째 탐색을 해야만 한다는 것을 의미한다.

ex) select * from ref_table where key_column ='상수' or key_column is null

 

5) eq_ref -- Mysql이 기껏해야 값 하나만을 반환해준다는 것을 알고 있을 때 이런 인덱스 탐색법이 사용되며, Mysql이 쿼리를 실행하기 위해 참조 값을 기본키 혹은 유니크 인덱스에 비교할 떄 이런 접근방법을 사용하는 것을 확인할 수 있다. Mysql에서 값을 하나라도 찾으면 더 이상 매치되는 행들ㄹ의 범위를 평가하거나 매치되는 행을 찾을 필요가 없으므로 이런 접근 방법은 아주 잘 최적화되었다고 볼 수 있다.

 

6) const, system -- 쿼리의 일부를 상수로 대체해서, 쿼리를 최적화할 수 있을 경우 Mysql에서는 이런 접근방법들을 사용하게 된다. where 절에 기본키를 이용해 검색하는 조건을 사용하는 경우 Mysql에서는 이 쿼리를 상수로 바꿔버린 뒤 조인에서 테이블을 사실상 제거해버린다.

 

7) NULL -- 이방법은 Mysql이 최적화 단계에서 쿼리를 처리할 수 있으므로 실행 단계에서 인덱스나 테이블에 접근조차하지 않았다는 것을 의미한다. 예를들어 인덱스가 되어 있는 컬럼에서 최솟값을 선택하려는 경우라면 인덱스만 찾아보면 되므로 실행단계에서 테이블에 접근할 필요가 없다.

 

3. rows 컬럼

이 컬럼은 원하는 행을 찾기 위해 얼마나 많은 행을 읽어야 할지에 대한 예측값을 의미한다. 이 숫자는 중첩루프(Nested-loop) 조인계획에서 루프 한 번당 몇 개의 레코드를 읽어야 할지에 대한 예측값이다. 다시말하면 이 값은 그저 Mysql이 테이블에서 읽어야할 거라고 생각하는 행 수를 의미하는 것만이 아니고, Mysql이 쿼리를 실행 관점에서 어떤 기준 이상으로 효율적으로 실행시킬 떄 읽어야하는 행 수의 평균을 의미한다.(기준은 조인 순서에서 앞쪽에서 나온 테이블에서 넘어온 칼럼뿐 아니라 SQL에서 주어진 상수들에 대한 것도 포함한다)

--> rows 의  전체 행을 곱해주면 얼마나 많은 행을 읽을지 조사할 수 있지만

     실제 행수를 읽는 값과 정확히 일치하지 않는다는 말

 

ex) 테이블 4개 lefe outer join 되어있는 쿼리 실행계획

전체 테이블 row 수 -- 11899, 193, 83, 37606

 = 7721 * 1 * 1 * 195 = 1505595 (예상 rows수)

 

id	select_type	table	partition	type	possible_keys	key	key_len	ref	rows	filtered	extra
1	SIMPLE	NT		ALL	IDX_DV_DEVICE_COME_NOTI				7721	9.1	Using where
1	SIMPLE	DM		eq_ref	PRIMARY	PRIMARY	62	NEW.NT.MODEL_CD	1	10	Using where
1	SIMPLE	DC		eq_ref	PRIMARY	PRIMARY	4	NEW.NT.COLOR_SEQ	1	100
1	SIMPLE	DS		ref	PRIMARY	PRIMARY	66	NEW.NT.MODEL_CD, NEW.NT.COLOR_SEQ	195	100	Using where; Using index

 

 

4. filtered 컬럼

이 컬럼은 어떤 테이블에 대한 조인 조건이나 where 절에 있는 조건 등을 만족시키는 행이 어느 정도 될지에 대한 비관적 예측을 나타낸다. 이 퍼센트 값을 rows 컬럼에 곱해주면 쿼리 계획에서 이전 테이블과 조인할 때 얼마나 많은 행과 조인하게 될지에 대해 Mysql이 예측한 값을 보여준다.

 

ex) 테이블 4개 lefe outer join 되어있는 쿼리 실행계획

7721 에 필터 9.1 -> 7721 * 9.1 * 0.01 = 702.611 

where 절에 의해 거의 1/10으로 줄었구나..옵티마이져가 풀스캔으로 처리할만 하다.

 

5. Extra 컬럼

이 컬럼은 다른 컬럼에는 적합하지 않은 나머지 정보들을 나타낸다. Mysql 매뉴얼에서는 여기에 나올 수 있는 대부분의 값들이 설명되어 있다.

 

Using index -- 이는 Mysql이 테이블에 접근하지 않도록 커버링 인덱스를 사용한다는 것을 알려준다. 실행 계획상의 Type 컬럼이 index인 것과 혼동하지 않도록 주의하자

 

Using where -- 이 값은 Mysql 서버가 스토리지 엔진에서 값을 가져온 뒤 행을 필더링한다는 것을 의미한다. 인덱스에 포함된 컬럼들이 사용된 where 조건들은 대부분 스토리지 엔진에서 인덱스를 읽을 때 체크되기 때문에 where 절을 가진 쿼리가 모두 실행계획 상에 Using where를 표시하는 것은 아니다. 때로는 Using where가 있다는 사실이 다른 인덱스를 사용하면 쿼리를 더 효율적으로 만들 수 있다는 의미가 되기도 한다.

 

Using temporary -- 이는 Mysql이 쿼리 결과를 정렬하기 위해 임시 테이블을 사용한다는 것을 의미한다.

 

Using filesort -- 이는 Mysql이 결과의 순서를 맞추기 위해 인덱스 순서로 테이블을 읽는 것이 아니라 외부 정렬을 사용해야 한다는 것을 의마한다. Mysql은 두가지 파일 정렬 알고리즘들을 가지고 있는데, 각 방식은 메모리나 디스크에서 수행될 수 있으며 Explain으로는 어떤 파일 정렬이 사용될지나 정렬이 디스크에서 이루어질지 메모리에서 이루어질지 등을 알 수가 없다.

 

Range checked for each record(index map :N)

-- 이 값은 적합한 인덱스가 없으므로 각 레코드의 조인에서 각 인덱스들을 재평가한다는 것을 의미한다. N은 실행계획의 possible_keys 컬럼에 나타나 있는 인덱스들의 비트맵 값이다.