1. 개요
옵티마이저는 DBMS 에서 가장 복잡한 부분입니다.
그래서 옵티마이저가 만들어 내는 실행 계획을 이해하는 것 또한 상당히 어렵습니다.
하지만, 실행 계획을 이해할 수 있어야 불합리한 부분을 찾아 최적화할 수 있습니다.
먼저, 아래에서 실행 계획을 이해하기 위해 필요한 내용들을 짚고 넘어가겠습니다.
1.1 쿼리 실행 절차
1. SQL 파서 가 SQL 문장을 분석하여, 파스 트리 를 만들어 낸다.
1번 작업은 MySQL 의 SQL 파서(쿼리 파서) 에서 실행됩니다.
2. 파스 트리 를 확인하며 테이블을 읽고, 어떤 인덱스를 이용할지 선택한다.
2번 작업은 옵티마이저 가 실행하고, 이때 결정된 사항들이 실행 계획입니다.
3. 2번째 단계에서 결정된 테이블 읽기 순서나, 선택된 인덱스를 통해 스토리지 엔진으로부터 데이터를 가져온다.
3번 작업은 스토리지 엔진이 2번 작업을 통해 만들어진 실행 계획을 이용하여 데이터를 가져옵니다.
1.2 옵티마이저의 종류
옵티마이저는 비용 기반 최적화 방법과 규칙 기반 최적화 방법으로 나눌 수 있습니다
- 비용 기반 최적화 : 현재 대부분의 DBMS 에서 사용하고 있음
- 특징 : 레코드 건수, 선택도 등을 교려하지 않고, 옵티마이저에 내장된 우선순위에 따라 실행 계획을 수립하는 방식
- 장점 : 같은 쿼리에 대해서 항상 거의 같은 실행 계획을 만들어냄
- 단점 : 데이터의 분포도는 예측할 수 없이 다양하기 때문에, 모든 상황에 효율적인 실행 계획은 아님
- 규칙 기반 최적화 : 옛날 Oracle 에서 많이 사용했음
- 특징 : 각 단위 작업의 비용(부하)정보와 대상 테이블의 통계 정보를 이용해 실행 계획별 비용을 산출하여, 최소 비용 계획을 실행함
- 장점 : 데이터의 분포도에 따라 발생하는 부하 정도를 추측하여, 최적의 실행 계획을 만들어냄
- 단점 : 같은 쿼리라도 데이터양, 인덱스 설정 여부 등 외부 요인에 따라 다른 실행 계획을 만들어냄
2. 기본 데이터 처리
2.1 풀 테이블 스캔과 풀 인덱스 스캔
MySQL 은 다음과 같은 조건을 만족할 때, 풀 테이블 스캔 을 선택한다
- 테이블의 레코드 건수가 너무 작아서 인덱스를 통해 읽는 것 보다, 테이블 풀 스캔을 하는 편이 더 빠른 경우
(일반적으로 테이블 페이지 1개로 구성된 경우) WHERE절이나ON절에 인덱스를 이용할 수 있는 적절한 조건이 없는 경우- 인덱스 레인지 스캔을 사용할 수 있더라도, 옵티마이저가 판단한 조건에 맞느 레코드 건수가 너무 많은 경우
풀 인덱스 스캔 은 마찬가지로 인덱스 전체를 읽는것을 의미합니다.
SELECT count(*) FROM tbl;이 쿼리는 아무 조건 없이, 레코드 건수를 조회합니다.
이 경우 당연히 풀 테이블 스캔을 할 것 처럼 보이지만, 레코드 건수만 필요하다면 용량이 작은 인덱스를 선택하는 것이 디스크 읽기 횟수를 줄일 수 있습니다.
2.2 병렬 처리
아직 MySQL 에서는 쿼리를 병렬로 처리하게 하는 힌트나 옵션은 없습니다.
하지만 Oracle 에서는 병렬 처리 힌트를 지원한다고 하네요.
(참고 : Oracle Parallel hint, Oracle Database Tutorial - Parallel hint)
MySQL 에서는 다음과 같이
> SET SESSION innodb_parallel_read_threads=1;
> SELECT COUNT(*) FROM salaries;
1 row in set (0.32 sec)
> SET SESSION innodb_parallel_read_threads=2;
> SELECT COUNT(*) FROM salaries;
1 row in set (0.20 sec)
> SET SESSION innodb_parallel_read_threads=4;
> SELECT COUNT(*) FROM salaries;
1 row in set (0.18 sec)
> SET SESSION innodb_parallel_read_threads=8;
> SELECT COUNT(*) FROM salaries;
1 row in set (0.13 sec)2.3 ORDER BY 처리(Using filesort)
정렬을 처리하는 방법은 인덱스 를 이용하는 방법과, Filesort 라는 별도의 처리 방법을 이용하는 방법이 있습니다.
| 장점 | 단점 | |
|---|---|---|
| 인덱스 이용 | 인덱스는 이미 정렬되어 있어서, 순서대로만 읽으면 되기 때문에 매우 빠름 | - INSERT, UPDATE, DELETE 작업 시 부가적인 인덱스 추가/삭제 작업이 필요함 - 인덱스 때문에 디스크 공간이 더 필요함 - 인덱스 개수가 늘어날수록 InnoDB 버퍼 풀을 위한 메모리가 많이 필요함 |
| Filesort 이용 | - 인덱스를 생성하지 않아도 되서, 인덱스 사용시의 단점이 장점이 됨. - 정렬해야 할 코드가 많지 않으면 메모리에서 Filesort가 처리되므로 충분히 빠름 | 정렬 작업이 쿼리 실행 시 처리되므로, 대상 레코드가 많을수록 쿼리 응답 속도가 느리다 |
이미 정렬된 순서대로 읽기만 하면 되는 인덱스 이용 방법이 더 효율적일거라 생각됩니다.
하지만, 다음과 같은 경우에는 Filesort 를 이용하는것도 좋습니다.
- 정렬 기준이 너무 많아서, 모두 인덱스를 생성하는것이 불가능한 경우
- GROUP BY 의 결과, 또는 DISTINCT 같은 처리 결과를 정렬해야 하는 경우
- UNION 의 결과와 같이 임시 테이블의 결과를 다시 정렬해야 하는 경우
- 랜덤하게 결과 레코드를 가져와야 하는 경우