본 게시물의 내용과 이미지는 도서 Real MySQL의 내용을 재구성하여 작성되었습니다. 저자, 출판사에 의해 저작권 문제 발생시 게시물이 비공개 될 수 있음을 알립니다.
이전 포스팅 ( Real MySQL [3] 실행계획 - type 칼럼 ) 에서 계속...
possible_keys
옵티마이저는 쿼리를 처리하기위해 여러가지 처리 방법을 고려해 비용이 가장 낮을 것으로 예상되는 실행 계획을 선택해 쿼리를 실행한다. possible_keys 칼럼에 내용은 옵티마이저가 최적의 실행계획을 만들기 위해 후보로 선정했던 인덱스 목록이다. 이 컬럼은 무시해도 좋다.
key
Key 칼럼에 표시되는 인덱스는 최종 선택되어 실행 계획에 사용된 인덱스를 의미한다. 쿼리 튜닝시 Key 칼럼에 의도했던 인덱스가 표시되는지 확인해야 한다. Key 칼럼이 PRIMARY인 경우 프라이머리 키를 사용한다는 의미이며, 그 이외의 값은 모두 테이블이나 인덱스 생성시 부여한 고유 이름이다.
type 칼럼이 index_merge가 아닌 경우 반드시 테이블 하나당 하나의 인덱스만 이용한다. index_merge 의 경우 2개 이상 인덱스가 사용되는데, Key 칼럼에 여러 개의 인덱스가 , 로 구분되어 표시된다.
그리고 실행계획의 type 이 ALL 로 인덱스를 사용하지 못하면 Key 칼럼의 값은 NULL로 표시된다.
key_len
key_len 칼럼의 값은 쿼리를 처리하기 위해 다중 칼럼으로 구성된 인덱스에서 몇개의 칼럼까지 사용했는지 알려준다. 정확하게는 인덱스의 각 레코드에서 몇 바이트까지 사용했는지 알려준다.
EXPLAIN
SELECT * FROM dept_emp WHERE dept_no='d005';
위 예제는 dept_no + emp_no 로 두 개의 칼럼으로 만들어진 프라이머리 키를 포함한 dept_emp 테이블을 조회하는 쿼리다. 이 쿼리는 dept_emp 테이블의 프라이머리 키중 dept_no만 비교하는데 사용하고 있다. 그래서 key_len 칼럼의 값이 12로 표시된 것이다. 즉, dept_no 칼럼의 타입이 CHAR(4) 이기 때문에 프라이머리 키 앞쪽에서 12바이트만 유효하게 사용했다는 의미다. UTF8 문자셋을 사용해 MySQL 이 메모리 공간 할당하는 경우 문자에 관계없이 고정적으로 3바이트로 계산한다 위의 실행 계획에서 key_len 칼럼의 값은 12바이트 (4*3 바이트) 가 표시된다.
EXPLAIN
SELECT * FROM dept_emp WHERE dept_no='d005' AND emp_no=10001;
dept_emp의 emp_no 칼럼은 INTEGER 이며, INTEGER 타입은 4바이트를 차지한다. 그래서 key_len은 dept_no(12 바이트) + emp_no( 4바이트) 칼럼의 길이의 합인 16으로 표시된다.
ref
접근 방법이 ref 방식이면 참조 조건(Equal 비교 조건)으로 어떤 값이 제공되었는지 보여준다. 상수값을 지정하면 ref는 const, 다른 테이블의 칼럼이면 칼럼명이 표시된다. 이 칼럼에 출력되는 내용은 크게 신경쓰지 않아도 되지만 아래의 케이스는 주의해서 볼 필요가 있다.
ref 칼럼에서 "func" 라고 표시될 때가 있다. 이는 "Function"의 줄임말로 콜레이션 변환값이나 값 자체의 연산을 거쳐서 참조했다는 것을 의미한다.
EXPLAIN
SELECT *
FROM employees e, dept_emp de
WHERE e.emp_no=de.emp_no
이 쿼리는 조인 조건에 사용된 emp_no 칼럼에 대해 변환, 가공을 수행하지 않아 ref 칼럼에 조인 되어 칼럼의 이름이 그대로 표시된다.
아래는 간단한 산술연산이 포함된 쿼리이다.
EXPLAIN
SELECT *
FROM employees e, dept_emp de
WHERE e.emp_no = (de.emp_no-1);
이 쿼리는 -1 연산이 포함되어 있다.
사용자가 명시적으로 값을 변환할 때 뿐만아니라 MySQL 서버가 내부적으로 값을 변환해야 할 경우도 ref 칼럼에 func가 출력된다. 가능하다면 MySQL 이 이런 변환을 하지 않도록 되도록 조인 칼럼의 타입은 일치시키는 편이 좋다.
rows
실행 계획의 효율성을 판단하기 위해 예측했던 레코드 건수를 보여준다. 스토리지 엔진별로 가지고 있는 통계정보를 참조한 예상 값이기 때문에 정확하지는 않다.
dept_emp 테이블에서 from_date 가 1985-01-01 보다 크거나 같은 레코드를 조회하는 쿼리이다. 풀 테이블 스캔으로 접근되며 rows 칼럼의 값을 보면 쿼리 처리를 위해 334,868 건의 레코드를 읽어야 할 것이라고 예측했다.
범위를 줄인 쿼리를 비교해보면 range로 인덱스 레인지 스캔을 사용하는 경우이다.
날짜는 DATE 타입이므로 3바이트로 표시됐고 range 방식(인덱스 레인지 스캔)으로 처리되었다.
Extra
쿼리 실행 계획에서 성능에 관련된 중요한 내용이 Extra 칼럼에 표시된다. Extra 칼럼에 고정된 몇 개의 문장이 표시되는데, 일반적으로 2~3개씩 같이 표시된다. Extra 칼럼에 표시될 수 있는 문장에 대해 알아보자
const row not found
const 접근 방식으로 테이블을 읽었지만 해당 테이블에 레코드가 1건도 존재 하지 않는 경우
Distinct
Extra에 Distinct가 표시되는 쿼리 예제를 살펴보자
EXPLAIN
SELECT DISTINCT d.dept_no
FROM departments d, dept_emp de WHERE de.dept_no=d.dept_no;
실제 조회하려는 값은 dept_no 인데 중복 없이 유니크하게 가져오기 위한 쿼리다. 두 테이블의 조인 결과를 다시 DISTINCT 처리를 넣은 것이다.
Full scan on NULL key
"col1 IN (SELECT col2 FROM ...) 과 같은 조건이 포함된 쿼리에서 자주 발생할 수 있다. col1의 값이 NULL 이되면 조건은 NULL IN (SELECT col2 FROM ...) 으로 바뀐다. SQL 표준에서 NULL은 알수 없는 값으로 정의하고 있다. 정의대로 연산을 수행하기 위해 이 조건은 다음과 같이 비교되어야 한다.
- 서브 쿼리가 1건이라도 결과 케코드를 가진다면 최종 비교 결과는 NULL
- 서브 쿼리가 1건도 결과 레코드를 가지지 않는다면 비교 결과는 FALSE
이 비교과정에서 col1이 NULL이면 풀 테이블 스캔을 해야만 결과를 알 수 있다. Full scan on NULL Key는 쿼리 실행중 col1 이 NULL을 만나면 예비책으로 풀 테이블 스캔을 사용할 것이라는 것을 알려주는 키워드다.
Extra에 표시될수 있는 값들 중 중요한 몇가지만 나열해보고자 한다. 자세한 내용을 확인하고자 한다면 책을 구입하시길.
Using index
커버링 인덱스를 사용한다는 것을 알려줌.
Using where
MySQL 서버가 스토리지 엔진에서 값을 가져온 뒤 행을 필터링 한다는 것을 의미
Using temporary
MySQL이 쿼리 결과를 정렬하기 위해 임시 테이블을 사용
Using filesort
MySQL이 결과의 순서를 맞추기 위해 인덱스 순서로 테이블을 읽는 것이아니라 외부 정렬을 사용해야 한다는 것을 의미
Range checked for each record(index map:N)
적합한 인덱스가 없으므로 각 레코드의 조인에서 각 인덱스를 재평가 한다는 의미
다음 포스팅에서 계속.
