옵티마이저란?
DBMS에서 쿼리를 실행해서 결과를 얻게 되는데 이때 같은 결과를 얻더라도 내부적으로 다양한 방법을 통해 결과를 얻을 수 있다. 이러한 방법 중 옵티마이저는 테이블의 데이터가 어떤 분포로 저장돼 있는지 통계 정보를 참고하여 가장 효율적인 방법을 찾는 역할을 한다.
이러한 옵티마이저가 동작하는 방식을 알아야 쿼리가 원하는 방식으로 동작하도록 튜닝할 수 있기 때문에 해당 부분은 중요하니 자세히 살펴보자!
쿼리 실행 절차
- 사용자로부터 요청된 SQL 문장을 쪼개서 MySQL 서버가 이해할 수 있는 수준으로 분리(파스 트리)한다.
- 파스 트리를 확인하면서 어떤 테이블부터 어떤 인덱스를 사용해서 데이터를 읽을지 선택한다.
- 결정된 선택에 따라 테이블의 읽기 순서, 선택된 인덱스를 이용해 스토리지 엔진으로부터 데이터를 가져온다.
여기서 우리가 알아본 단계는 옵티마이저가 사용되는 2번째 단계이며 결과물로 실행 계획이 만들어진다.
옵티마이저 종류
| 종류 | 특징 |
|---|---|
| 규칙 기반 최적화 | 우선순위에 따라 실행 계획을 수립하는 방식으로 실제 데이터를 고려하지 않고 항상 같은 실행 방법을 생성 |
| 비용 기반 최적화 | 단위 작업의 비용 정보와 대상 테이블의 통계 정보를 이용해 실용적인 실행 계획을 수립하는 방식 |
위와 같은 특징으로 현재는 비용 기반 최적화 방식을 많이 채택하고 잇다.
옵티마이저 상세동작
정렬 처리(Order By)
다수의 레코드를 가져오는 쿼리는 보통 정렬처리를 같이 사용한다. 이러한 정렬처리는 인덱스를 이용할 경우 조회시는 빠르게 처리될 것이다. 다만 아래와 같은 이유때문에 모든 정렬에서 인덱스를 사용할 수 없다.
- 정렬 기준이 많아서 모든 컬럼에 대해 인덱스를 생성할 수 없는 경우
- Group By 또는 Distinct 같은 처리결과에 정렬을 해야하는 경우
- Union 또는 임시 테이블 결과를 다시 정렬해야하는 경우
- 랜덤하게 결과 레코드를 가져와야하는 경우
이러한 경우 옵티마이저는 Filesort라는 정렬방식을 사용한다.
소트 버퍼
MySQL은 정렬을 할때 소트버퍼라는 메모리 공간을 할당받아 정렬을 한다.
이 공간은 데이터의 크기에 따라 가변적인 세션단위 메모리 공간이다. 최대 사용 공간은 sort_buffer_size라는 시스템 변수로 지정이 가능하다.최대 사용 공간 넘을 경우 디스크와 혼합하여 사용하여 디스크I/O가 발생한다. 이러한 최대 사용 공간은 무한정 늘린다고 성능향상을 보장하지 않으며 56KB~1MB가 적절하다는 의견이 있다.
정렬 알고리즘
MySQL은 정렬을 할때 두가지 방식을 사용하고 있다. 소트 버퍼에 모든 컬럼을 담는 방식 또는 정렬에 필요한 컬럼만 담는 방식이 있다.
| 구분 | 특징 |
|---|---|
| 모든 컬럼 담는 방식 | 정렬 후 사용자에게 그대로 반환 컬럼 데이터의 크기가 작아 소트 버퍼에 무리가 없을 경우 사용 |
| 필요 컬럼 담는 방식 | 정렬 후 인덱스 기준으로 필요한 컬럼들을 추가적으로 가져와 반환 컬럼 데이터의 크기가 커 소트 버퍼에 무리가 갈 경우 사용 |
위와 같은 특징 때문에 상황에 따라 유리한 방식이 다르기 때문에 최신 버전에 MySQL버전은 두가지 방식을 혼용하여 사용한다.
중요한 점
정렬시에 정렬할때 사용하지 않지만 조회되는 컬럼은 처음부터 가져오든 나중에 가져오든 많은 비용을 발생시킨다. 따라서 모든 조회쿼리도 마찬가지이지만 정렬이 들어간 조회쿼리는 되도록이면 select * from table과 같은 사용하지 않는 조회 컬럼이 포함되지 않도록 주의해야한다.
정렬 처리 속도
정렬 처리속도는 아래와 같은 순서로 빠르다.
1. 인덱스를 사용한 정렬
2. 두개 테이블을 조인하지만 드라이빙 테이블에 컬럼으로만 정렬
3. 두개 테이블을 조인하고 드리븐 테이블 컬럼으로 정렬
여기서 드라이빙은 from절에 오는 원천 테이블을 말하며 드리븐은 조인이 되는 테이블을 말한다.
또한 3번째 방식은 임시 테이블을 만들고 정렬한다.
추가로 limit를 사용하면 성능이 훨씬 빨라질거라 생각하지만 정렬 또는 그룹핑을 하는 경우는 전체 데이터를 가져온 다음 처리하기에 기대만큼에 성능을 보장하지 않는다.(정렬 후 가져오는 갯수에 데이터를 다 찾으면 더 이상 전체 데이터를 가져오지 않기에 약간의 성능 향상은 있다.)
정렬은 인덱스를 최대한 활용하고 인덱스로 안될 경우 정렬이 많이 사용되는 테이블을 드라이빙 테이블로 사용하도록 쿼리를 작성하자
그룹핑 처리(Group by)
group by절은 order by절과 마찬가지로 스트리밍 처리를 할 수 없는 쿼리이며 인덱스를 사용하는 경우와 사용 못하는 경우로 나눌 수 있다.
인덱스 스캔을 사용하는 경우
드라이빙 테이블에 인덱스인 컬럼을 사용하는 경우이며 실행계획을 확인해보면 Extra 컬럼에 Using index for group-by 또는 별도의 코멘트가 표시되지 않는다.
루스 인덱스 스캔을 사용하는 경우
루스 인덱스 스캔방식은 인덱스의 레코드를 필요한 부분만 건너뛰면서 가져오는 것을 말한다. 실행계획에 Using index for group-by가 표시된다.
해당 경우에 대해서는 추후 실행계획 부분에서 자세히 알아보자.
임시 테이블을 사용하는 경우
인덱스를 전혀 사용하지 않는 경우이며 실행계획을 확인해보면 Using temporary가 표시된다. 이는 임시 테이블을 만들고 해당 결과를 반환한다.
또한 5.7버전까지는 group by를 사용한 경우 지정하지 않아도 order by를 수행했다. 따라서 정렬이 필요하지 않은 경우 명시적으로 order by null 이라고 작성해야했지만 8.0 버전부터는 정렬을 지정하지 않으면 동작하지 않기에 굳이 추가하지 않아도 된다.
