[MySQL] 풀 테이블 스캔
이번에는 MySQL의 풀 테이블 스캔에 대해 정리해보려고 한다.
MySQL 서버에서 쿼리의 실행절차
쿼리가 실행되는 과정은 다음과 같다.
- SQL 파싱
- 최적화 및 실행계획 수립
- 결정된 실행계획을 이용해 스토리지 엔진으로부터 데이터를 가져옴
1. SQL 파싱
제일 처음에는 사용자로부터 받은 SQL 문장을 잘게 쪼개서 MySQL 서버가 이해할 수 있는 수준으로 분리한다(파스 트리를 만든다)
이 부분은 MySQL 서버의 SQL 파서 모듈로 처리한다. SQL문장이 문법적으로 잘못된 경우 이 단계에서 걸러진다.
2. 최적화 및 실행계획 수립
SQL 파스 트리를 이용하여 어떤 테이블로부터 읽을지, 어떤 인덱스를 이용해 테이블을 읽을지 선택한다.
이 부분은 MySQL 서버의 옵티마이저에서 처리하며, 이후 쿼리의 실행계획이 만들어진다.
3. 결정된 실행계획을 이용해 스토리지 엔진으로부터 데이터를 가져온다
MySQL 서버는 스토리지 엔진에게 데이터를 가져와달라고 요청하고,
스토리지 엔진으로부터 받을 레코드를 조인하거나 정렬한다.
풀 테이블 스캔
풀 테이블 스캔이란, 인덱스를 사용하지 않고 테이블의 데이터를 처음부터 끝까지 읽어서 요청된 작업을 처리하는 방법이다.
MySQL 옵티마이저가 풀테이블 스캔을 선택하는 조건은 다음과 같다
- 테이블의 레코드 건수가 너무 작아서 인덱스를 통해 읽는 것보다 풀 테이블 스캔이 더 빠를 때
- WHERE 절이나 ON절에 인덱스를 이용할 수 있는 적절한 조건이 없는 경우
- 인덱스 레인지 스캔을 사용할 수 있어도, 조건 일치 레코드 건수가 너무 많은 경우
풀 테이블 스캔의 성능을 올리는 방법 - 리드 어헤드 작업
테이블 전체 크기는 인덱스보다 훨씬 크기 때문에 테이블 풀 스캔 작업은 상당히 많은 디스크 읽기를 필요로 한다.
그래서 대부분의 DBMS는 풀테이블 스캔을 실행 시 한꺼번에 여러 개의 블록이나 페이지를 읽어오는 기능을 내장하고 있다. MySQL도 마찬가지다.
InnoDB 스토리지 엔진의 경우 특정 테이블의 연속된 데이터 페이지가 읽히면 백그라운드 스레드에 의해 리드 어헤드 작업이 자동으로 시작된다.
- 리드 어헤드: 어떤 영역의 데이터가 앞으로 필요해지리라는걸 예측하여 요청이 오기 전 미리 디스크에서 읽어 InnoDB 버퍼 풀에 가져다 두는 것
- 처음 몇 개의 페이지는 포그라운드 스레드가 페이지 읽기를 실행하다, 특정 시점부터는 읽기 작업을 백그라운드 스레드로 넘긴다
백그라운드 스레드가 읽기를 넘겨받는 시점부터는 한 번에 4개 또는 8개씩 페이지를 읽으면서 계속 그 수를 증가시킨다
(한 번에 최대 64개 데이터 페이지까지 읽어 버퍼풀에 저장함) 그러면 포그라운드 스레드의 경우 미리 버퍼 풀에 준비된 데이터를 사용하기만 하면 된다.
innodb_read_ahead_threshold 시스템 변수를 조정하여 InnoDB 스토리지 엔진이 언제 리드 어헤드를 시작할 지 임계값을 설정할 수 있다.
리드 어헤드 작업의 경우 풀 인덱스 스캔에서도 동일하게 사용된다.
이런 요소 때문에 인덱스 조건에 일치하는 레코드 건수가 너무 많은 경우 풀테이블 스캔을 하는 게 더 효율적이다.