9.1. 개요
쿼리를 최적으로 실행하기 위해 각 테이블의 데이터가 어떤 분포로 저장되어 있는지 통계 정보를 참조하며,
그러한 기본 데이터를 비교해 최적의 실행 계획을 수립하는 작업이 필요하다.
대부분의 DBMS에서는 '옵티마이저'가 이러한 기능을 담당한다.
쿼리의 실행 계획을 수립하는 옵티마이저는 가장 복잡한 부분으로 알려져 있다.
9.1.1 쿼리 실행 절차
MySQL 서버에서 쿼리가 실행되는 과정은 크게 아래 세단계로 나눠진다.
1. 사용자로부터 요청된 SQL 문장을 잘게 쪼개서 MySQL 서버가 이해할 수 있는 수준으로 분리(파스 트리)한다.
2. SQL의 파싱정보(파스 트리)를 확인하면서 어떤 테이블을 읽고 어떤 인덱스를 이용해 테이블을 읽을지 선택한다.
3. 두번째 단계에서 결정된 테이블의 읽기 순서나 선택된 인덱스를 이용해 스토리지 엔진으로 부터 데이터를 가져온다.
9.1.2 옵티마이저의 종류
- 규칙 기반 최적화 : 대상 테이블의 레코드 건수나 선택도 등을 고려하지 않고, 우선순위에 따라 실행 계획을 수립하는방식 이미 오래전부터 많은 DBMS에서 거의 사용하지 않는다.
- 비용 기반 최적화 : 산출된 실행 방법별로 비용이 최소로 소요되는 처리 방식을 선택해 최종적으로 쿼리를 실행한다. 대부분의 RDBMS가 채택하고 있는 방식이며, MySQL 도 마찬가지다.
9.2. 기본 데이터 처리
9.2.1 풀 테이블 스캔과 풀 인덱스 스캔
풀 테이블 스캔은 인덱스를 사용하지 않고, 테이블의 데이터를 처음부터 끝까지 읽어서 요청된 작업을 처리하는 작업
- 테이블의 레코드 수가 너무 적을 때
- 인덱스를 이용할 수 있는 적절한 조건이 없는 경우
- 옵티마이저가 판단한 조건 일치 레코드 건수가 너무 많은 경우
9.2.2 병렬 처리
병렬처리 ? 하나의 쿼리를 여러 스레드가 작업을 나누어 동시에 처리한다는 것
MySQL 8.0 버전에서는 아무런 WHERE 조건 없이 단순히 테이블의 전체 건수를 가져오는 쿼리만 병렬 처리할 수 있다.
9.2.3 ORDER BY 처리(Using filesort)
대부분의 SELECT 쿼리에서는 정렬은 필수적으로 사용한다.
1) 인덱스 이용
- 장점 : 이미 인덱스가 정렬돼 있어서 매우 빠르다
- 단점 :
- INSERT, UPDATE, DELETE 작업 시 부가적인 인덱스 추가/삭제 작업이 필요하므로 느리다.
- 인덱스 때문에 디스크 공간이 더 많이 필요하다
2) Filesort 이용
- 장점 :
- 인덱스를 생성하지 않아도 됨
- 정렬해야 할 레코드가 많지 않으면 충분히 빠르다
- 단점 : 쿼리 실행시 정렬 작업이 실행되므로 건수가 많아질수롣 쿼리의 응답 속도가 느리다.
모든 정렬을 인덱스를 이용하도록 튜닝하기란 거의 불가능하다.
- 정렬 기준이 너무 많아서 요건별로 모두 인덱스를 생성하는 것이 불가능한 경우
- GROUP BY의 결과 또는 DISTINCT 같은 처리의 결과를 정렬해야하는 경우
- UNION의 결과와 같이 임시 테이블의 결과를 다시 정렬해야하는 경우
- 랜덤하게 결과 레코드를 가져와야 하는 경우
9.2.3.1 소트버퍼 (Sort buffer)
정렬을 수행하기 위해 별도의 메모리 공간을 할당받아서 사용하는데, 이 메모리 공간을 소트 버퍼라고 한다.
정렬해야 할 레코드의 건수가 소트 버퍼로 할당된 공간보다 크다면
MySQL은 정렬해야 할 레코드를 여러 조각으로 나눠서 처리하는데, 이 과정에서 임시 저장을 위해 디스크를 사용한다.
이 작업들이 모두 디스크의 쓰기와 읽기를 유발하며, 레코드 건수가 많을수록 이 반복 작업의 횟수가 많아진다.
sort_buffer_size 시스템 변수의 설정값이 무조건 크면 메모리에서 모두 처리되니 빨라질 것으로 예상하지만 실제 결과는 그렇지 않다.
큰 메모리 공간 할당 때문에 성능이 훨씬 떨어질 수 있다.
참고문헌
Real MySQL 8.0 - 백은빈, 이성욱