요약
- Extra 칼럼의 나머지 값들을 이해하게 됨.
- Start temporary, End temporary
- unique row not found
- 유니크 칼럼 아우터 조인 시, 아우터 테이블에 일치하는 레코드가 존재하지 않을 때
- Using filesort
- Using index(커버링 인덱스)
- 데이터 파일을 읽지않고 인덱스만으로 쿼리를 모두 처리할 수 있을 때
메모
Start temporary, End temporary
- 세미 조인 최적화 중, Duplicate Weed-out 최적화 전략이 사용되면, MySQL 옵티마이저는 실행 계획의 Extra 칼럼에 “Start temporary”와 “End temporary” 문구를 표시하게 됨.
- Duplicate Weed-out 최적화 전략은 불필요한 중복 건을 제거하기 위해서 내부 임시 테이블을 사용함.
- 이때 조인되어 내부 임시 테이블에 저장되는 테이블을 식별할 수 있게 조인의 첫 번째 테이블에 “Start temporary” 문구를 보여주고 조인이 끝나는 부분에 “End temporary” 문구를 표시해줌.
- 9.3.1.14절의 중복 제거를 참조하자.
unique row not found
- 두 개의 테이블이 각각 유니크(프라이머리 키 포함)칼럼으로 아우터 조인을 수행하는 쿼리에서, 아우터 테이블에 일치하는 레코드가 존재하지 않을 때 Extra 칼럼에 이 코멘트가 표시됨.
Using filesort
- ORDER BY를 처리하기 위해 인덱스를 이용할 수도 있지만, 적절한 인덱스를 사용하지 못할 때는 MySQL서버가 조회된 레코드를 다시 한번 정렬해야함.
- ORDER BY 처리가 인덱스를 사용하지 못할 때만 실행 계획의 Extra 칼럼에 “Using filesort” 코멘트가 표시됨.
- 이는 조회된 레코드를 정렬용 메모리 버퍼에 복사해 퀵 소트 또는 힙 소트 알고리즘을 이용해 정렬을 수행하게 된다는 의미임.
- “Using Filesort” 코멘트는 ORDER BY가 사용된 쿼리의 실행 계획에서만 나타날 수 있음.
- 이 코멘트가 출력되는 쿼리는 많은 부하를 일으키므로, 가능하면 쿼리를 튜닝하거나 인덱스를 생성하는 것이 좋음.
- “Using filesort” 는 중요한 부분이므로 11.4.9절 “ORDER BY”에서 다시 자세히 다룬다.
Using index(커버링 인덱스)
- 데이터 파일을 전혀 읽지 않고 인덱스만 읽어서 쿼리를 모두 처리할 수 있을 때 Extra 칼럼에 “Using index”가 표시됨.
- 인덱스를 이용해 처리하는 쿼리에서 가장 큰 부하를 차지하는 부분은 인덱스 검색에서 일치하는 키 값들의 레코드를 읽기 위해 데이터 파일을 검색하는 작업임.
- 최악의 경우, 인덱스를 통해 검색된 결과 레코드 한 건 한 건마다 디스크를 한 번씩 읽어야 할 수도 있음.
- 위 그림은 B-Tree 인덱스를 검색 후, 데이터 레코드를 읽는 모습임.
- 위 그림과 같이 employees 테이블에 데이터가 저장되어 있고, 아래와 같은 쿼리로 인덱스 레인지 스캔 접근 방법을 사용한다고 해보자.
mysql> EXPLAIN
SELECT first_name, birth_date
FROM employees
WHERE first_name BETWEEN 'Babette' AND 'Gad';
- 실제 실행 계획은 풀 테이블 스캔을 사용한다.
- 하지만 여기서 employees 테이블의 first_name 칼럼에 생성된 인덱스(ix_firstname)을 이용하면 일치하는 레코드 5만여 건을 검색하고 각 레코드의 birth_date 칼럼의 값을 읽기 위해 각 레코드가 저장된 데이터 페이지를 5만여 번 읽어야 함.
- 그래서 MySQL 옵티마이저가 만들어낸 실행 계획을 보면 인덱스를 사용하는 것 보다 풀 테이블 스캔으로 처리하는 편이 더 효율적이라고 판단함.
- 이번에는 birth_date 칼럼을 빼고 first_name 칼럼만 조회하는 쿼리를 생각해 보자.
mysql> EXPLAIN
SELECT first_name
FROM employees
WHERE first_name BETWEEN 'Babette' AND 'Gad';
- 풀 테이블 스캔이 아니라 인덱스 레인지 스캔으로 처리됨.
- 이 예제 쿼리는 employees 테이블의 여러 칼럼 중, first_name 칼럼만 사용함.
- 즉, employees 테이블의 first_name 칼럼만 있으면 이 쿼리를 완료할 수 있음.
- 그래서 이 쿼리는 인덱스를 통해 필요한 레코드를 검색하고 필요한 칼럼(first_name)까지 인덱스에서 가져올 수 있음.
- 필요한 칼럼이 모두 인덱스에 있으므로 데이터 파일을 읽어 올 필요가 없음.
- 이 쿼리는 30~40개의 페이지만 읽으면 되므로, 매우 빠른 속도로 처리됨.
- 인덱스만으로 쿼리를 수행할 수 있을 때, 실행 계획의 Extra 칼럼에는 “Using index”라는 메시지가 출력됨.
- 이렇게 인덱스만으로 처리되는 것을 “커버링 인덱스(Covering index)” 라고 함.
- 인덱스 레인지 스캔을 사용하지만 쿼리의 성능이 만족스럽지 못한 경우라면 인덱스에 있는 칼럼만 사용하도록 쿼리를 변경해 큰 성능 향상을 볼 수 있음.
- InnoDB의 모든 테이블은 클러스터링 인덱스로 구성돼 있음.
- 그리고 이 때문에 InnoDB 테이블의 모든 세컨더리 인덱스는 데이터 레코드의 주솟값으로 프라이머리 키 값을 가짐.
- 위 그림은 InnoDB 에서의 B-Tree 인덱스와 데이터 레코드를 표현한 것임.
- 인덱스의 “레코드 주소” 값에 employees 테이블의 프라이머리 키인 emp_no 값이 저장된 것을 볼 수 있음.
- 즉, InnoDB 테이블에서는 first_name 칼럼만으로 인덱스를 만들어도 결국 그 인덱스에 emp_no 칼럼이 같이 저장되는 효과를 냄.
- 이러한 클러스터링 인덱스 특성 때문에 쿼리가 “커버링 인덱스”로 처리될 가능성이 상당히 높음.
- 레코드 건수에 따라 차이가 있겠지만 쿼리를 커버링 인덱스로 처리할 수 있을 때와 그렇지 못할 때의 성능 차이는 수십에서 수백 배까지 날 수 있음.
- 하지만 무조건 커버링 인덱스로 처리하려고 인덱스에 많은 칼럼을 추가하면 더 위험한 상황이 초래될 수도 있음.
- 너무 과도하하게 인덱스의 칼럼이 많아지면 인덱스의 크기가 커져서 메모리 낭비가 심해지고 레코드를 저장하거나 변경하는 작업이 매우 느려질 수 있기 때문
- 접근 방법(실행 계획의 type 칼럼)이 eq_ref, ref, range, index_merge, index 등과 같이 인덱스를 사용하는 실행 계획에서는 모두 Extra 칼럼에 “Using index”가 표시될 수 있음.
- 인덱스 레인지 스캔(eq_ref, ref, index_merge 등의 접근방법)을 실행할 때만 커버링 인덱스로 처리되는 것은 아님.
- 인덱스 풀 스캔(index 접근 방법)을 실행할 때도 커버링 인덱스로 처리될 수 있음.
- 이때도 똑같은 인덱스 풀 스캔의 접근 방법이라면 커버링 인덱스가 아닌 경우보다 훨씬 빠르게 처리됨.