MySQL 서버로 요청된 쿼리는 결과는 동일하지만 내부적으로 그 결과를 만들어내는 방법은 매우 다양하며, 어떤 방법이 최적이고 최소의 비용이 소모될지 결정해야 한다. MySQL 옵티마이저는 쿼리를 최적으로 실행하기 위해 각 테이블의 데이터가 어떤 분포로 저장돼 있는지 통계 정보를 참조하여 기본 데이터를 비교해 최적의 실행 계획을 수립한다.
개요
어떤 DBMS든지 쿼리의 실행 계획을 수립하는 옵티마이저는 가장 복잡한 부분으로 알려져 있으며, 옵티마이저가 만들어 내는 실행 계획을 이해하는 것 또한 상당히 어려운 부분이다. 하지만 실행 계획을 이해할 수 있어야만 실행 계획의 불합리한 부분을 찾아내고, 더 최적화된 방법으로 실행 계획을 수립하도록 유도할 수 있다.
실행 절차
MySQL 서버에서 쿼리가 실행되는 과정은 크게 세 단계로 나눌 수 있다.
- SQL 파싱: 사용자로부터 요청된 SQL 문장을 잘게 쪼개어 MySQL 서버가 이해할 수 있는 수준으로 분리(파스 트리)한다.
- SQL 파서라는 모듈로 처리한다. 이때 문법적으로 잘못된다면 걸러진다. 이 단계에서 SQL 파스 트리가 만들어지고, MySQL 서버는 SQL 문장 자체가 아닌 파스 트리를 이용해 쿼리를 실행한다.
- 최적화 및 실행 계획 수립: MySQL 옵티마이저에 의해 SQL의 파싱 정보(파스 트리)를 확인하며 어떤 테이블 부터 읽고 어떤 인덱스를 이용해 테이블을 읽을지 선택한다.
- 불필요한 조건 제거 및 복잡한 연산 단순화
- 여러 테이블의 조인이 있는 경우 어떤 순서로 테이블을 읽을지 결정
- 각 테이블에 사용된 조건과 인덱스 통계 정보를 이용해 사용할 인덱스를 결정
- 가져온 레코드들을 임시 테이블에 넣고 다시 한번 가공해야 하는지 결정
- 두 번째 단계에서 결정된 테이블의 일기 순서나 선택된 인덱스를 이용해 스토리지 엔진으로부터 데이터를 가져온다.
두 번째 단계가 완료되면 쿼리의 실행 계획이 만들어지며, 세 번째 단계에서 수립된 실행 계획대로 스토리지 엔진에 레코드를 읽어오도록 요청하고, MySQL 엔진에서는 스토리지 엔진으로부터 받은 레코드를 조인하거나 정렬하는 작업을 수행한다.
옵티마이저의 종류
옵티마이저는 데이터베이스 서버에서 두뇌와 같은 역할을 담당한다. 옵티마이저는 현재 대부분의 DBMS가 선택하고 있는 비용 기반 최적화(Cost-based optimizer, CBO) 방법과 예전 초기 버전의 오라클 DMBS에서 많이 사용했던 규칙 기반 최적화 방법(Rule-based optimizer, RBO)로 나눌 수 있다.
- RBO: 기본적으로 대상 테이블의 레코드 건수나 선택도 등을 고려하지 않고 옵티마이저에 내장된 우선순위에 따라 실행 계획을 수립하는 방식을 의미한다.
- 통계 정보(테이블의 레코드 건수나 컬럼 값의 분포도)를 조사하지 않고 실행 계획이 수립되기 때문에 같은 쿼리에 대해서는 거의 항상 같은 실행 방법을 만들어 낸다.
- 사용자의 데이터는 분포도가 매우 다양하기 때문에 규칙 기반의 최적화는 이미 오래전부터 사용되지 않는다.
- CBO: 쿼리를 처리하기 위한 여러 가지 가능한 방법을 만들고, 각 단위 작업의 비용(부하) 정보와 대상 테이블의 예측된 통계 정보를 이용해 실행 계획별 비용을 산출한다.
- 산출된 실행 방법별로 비용이 최소로 소요되는 처리 방식을 선택해 최종적으로 쿼리를 실행한다.
규칙 기반 최적화는 각 테이블이나 인덱스의 통계 정보가 거의 없고 상대적으로 느린 CPU 연산 탓에 비용 계산 과정이 부담스럽다는 이유로 사용되는 최적화 방법이다. 현재는 MySQL을 포함한 대부분의 RDBMS가 비용 기반의 옵티마이저를 채택하고 있다.
MySQL 서버를 포함한 모든 RDBMS는 데이터를 정렬하거나 그루핑하는 등 기본 데이터 가공 기능을 가지고 있다. 하지만 결과물은 동일하더라도 RDBMS 별로 그 결과를 만들어 내는 과정은 천차만별이다.
풀 테이블 스캔과 풀 인덱스 스캔
- 풀 테이블 스캔: 인덱스를 사용하지 않고 테이블의 데이터를 처음부터 끝까지 읽어 요청된 작업을 처리하는 작업을 의미한다.
- 테이블의 레코드 건수가 너무 작아서 인덱스를 통해 읽는 것 보다 풀 테이블 스캔을 하는 편이 더 빠른경우(테이블이 페이지 1개로 구성된 경우)
WHERE절이나ON절에 인덱스를 이용할 수 있는 적절한 조건이 없는 경우- 인덱스 레인지 스캔을 사용할 수 있는 쿼리라고 하더라도 옵티마이저가 판단한 조건 일치 레코드 건수가 너무 많은 경우(인덱스 B-Tree를 샘플링해서 조사한 통계 정보 기준)
일반적으로 테이블의 전체 크기는 인덱스보다 훨씬 크기 때문에 테이블을 처음부터 끝까지 읽는 작업은 상당히 많은 디스크 읽기가 필요하다. 그래서 대부분 DBMS는 풀 테이블 스캔을 실행할 때 한꺼번에 여러개의 블록이나 페이지를 읽어오는 기능을 내장하고 있다.
하지만 MySQL에는 풀 테이블 스캔을 실행할 때 한꺼번에 몇 개씩 테이블을 읽어올지 설정하는 시스템 변수는 없다. 그래서 많은 사람들이 MySQL은 풀 테이블 스캔을 실행할 때 디스크로부터 페이지를 하나씩 읽어 오는 것으로 생각하지만 InnoDB 스토리지 엔진은 특정 테이블의 연속된 데이터 페이지가 읽히면 백그라운드 스레드에 의해 리드 어헤드(Read ahead) 작업이 자동으로 시작되며, 이를 통해 요청이 오기 전에 미리 디스크에서 필요할 데이터를 예측하여 읽어 버퍼풀에 가저다둔다.
즉, 풀 테이블 스캔이 실행되면 처음 몇개의 데이터 페이지는 포그라운드 스레드(Foreground thread, 클라이언트 스레드)가 페이지 읽기를 실행하지만 특정 시점부터는 읽기 작업을 백그라운드 스레드로 넘긴다.
백그라운드 스레드가 읽기를 넘겨받는 시점부터는 한번에 4개 또는 8개씩 페이지를 읽으면서 계속 그 수를 증가시키고, 한 번에 최대 64개의 데이터 페이지까지 읽어 버퍼풀에 저장해둔다. 포그라운드 스레드는 미리 버퍼풀에 준비된 데이터를 가져다 사용하기만 하면 되므로 쿼리가 빠르게 처리된다.
MySQL 서버에서는 innodb_read_ahead_threshold 시스템 변수를 이용해 InnoDB 스토리지 엔진이 언제 리드 어헤드를 시작할지 임계값을 설정할 수 있다. 포그라운드 스레드에 의해 시스템 변수에 설정된 개수만큼의 연속된 데이터 페이지가 읽히면 InnoDB 스토리지 엔진은 백그라운드 스레드를 이용해 대량으로 그 다음 페이지들을 읽어서 버퍼풀로 적재한다.
일반적으로 기본 설정으로 충분하지만 데이터 웨어하우스용으로 MySQL을 사용한다면 이 옵션을 더 낮은 값으로 설정하여 더 빨리 리드 어헤드가 시작되게 유도하는 것도 성능을 향상시킬 수 있다.
리드 어헤드는 풀 인덱스 스캔에서도 동일하게 사용된다.
병렬 처리
MySQL 8.0 버전부터는 용도가 한정돼 있긴 하지만 쿼리 병렬 처리가 가능해졌다.
여기서 말하는 병렬 처리는 하나의 쿼리를 여러 스레드가 나누어 동시에 처리한다는 것을 의미한다.
innodb_parallel_read_threads 시스템 변수를 이용해 하나의 쿼리를 최대 몇개의 스레드를 이용해서 처리할지를 변경할 수 있다. 아직 MySQL 서버에서는 쿼리를 여러 개의 스레드를 이용해 병렬로 처리하게 하는 힌트나 옵션은 없으며, 아무런 WHERE조건 없이 단순히 테이블의 전체 건수를 가져오는 쿼리만 병렬로 처리할 수 있다.
| |
병렬 처리용 스레드 개수가 늘어날수록 쿼리 처리에 걸리는 시간이 대체로 줄어들지만, 서버에 장착된 CPU의 코어 개수를 넘어서는 경우에는 오히려 성능이 떨어질 수도 있으니 주의가 필요하다.