[MySQL] 풀 테이블 스캔

 

이번에는 MySQL의 풀 테이블 스캔에 대해 정리해보려고 한다. 

 

 

MySQL 서버에서 쿼리의 실행절차

쿼리가 실행되는 과정은 다음과 같다.

 

1. SQL 파싱
2. 최적화 및 실행계획 수립
3. 결정된 실행계획을 이용해 스토리지 엔진으로부터 데이터를 가져옴

 

1. SQL 파싱

제일 처음에는 사용자로부터 받은 SQL 문장을 잘게 쪼개서 MySQL 서버가 이해할 수 있는 수준으로 분리한다(파스 트리를 만든다)

이 부분은 MySQL 서버의 SQL 파서 모듈로 처리한다. SQL문장이 문법적으로 잘못된 경우 이 단계에서 걸러진다. 

 

2. 최적화 및 실행계획 수립

SQL 파스 트리를 이용하여 어떤 테이블로부터 읽을지, 어떤 인덱스를 이용해 테이블을 읽을지 선택한다. 

이 부분은 MySQL 서버의 옵티마이저에서 처리하며, 이후 쿼리의 실행계획이 만들어진다.

 

3. 결정된 실행계획을 이용해 스토리지 엔진으로부터 데이터를 가져온다

MySQL 서버는 스토리지 엔진에게 데이터를 가져와달라고 요청하고,

스토리지 엔진으로부터 받을 레코드를 조인하거나 정렬한다.

 

 

 

풀 테이블 스캔

풀 테이블 스캔이란, 인덱스를 사용하지 않고 테이블의 데이터를 처음부터 끝까지 읽어서 요청된 작업을 처리하는 방법이다.

MySQL 옵티마이저가 풀테이블 스캔을 선택하는 조건은 다음과 같다

1. 테이블의 레코드 건수가 너무 작아서 인덱스를 통해 읽는 것보다 풀 테이블 스캔이 더 빠를 때
2. WHERE 절이나 ON절에 인덱스를 이용할 수 있는 적절한 조건이 없는 경우
3. 인덱스 레인지 스캔을 사용할 수 있어도, 조건 일치 레코드 건수가 너무 많은 경우

 

풀 테이블 스캔의 성능을 올리는 방법 - 리드 어헤드 작업

테이블 전체 크기는 인덱스보다 훨씬 크기 때문에 테이블 풀 스캔 작업은 상당히 많은 디스크 읽기를 필요로 한다.

그래서 대부분의 DBMS는 풀테이블 스캔을 실행 시 한꺼번에 여러 개의 블록이나 페이지를 읽어오는 기능을 내장하고 있다. MySQL도 마찬가지다. 

 

 

InnoDB 스토리지 엔진의 경우 특정 테이블의 연속된 데이터 페이지가 읽히면 백그라운드 스레드에 의해 리드 어헤드 작업이 자동으로 시작된다. 

• 리드 어헤드: 어떤 영역의 데이터가 앞으로 필요해지리라는걸 예측하여 요청이 오기 전 미리 디스크에서 읽어 InnoDB 버퍼 풀에 가져다 두는 것
• 처음 몇 개의 페이지는 포그라운드 스레드가 페이지 읽기를 실행하다, 특정 시점부터는 읽기 작업을 백그라운드 스레드로 넘긴다

 

백그라운드 스레드가 읽기를 넘겨받는 시점부터는 한 번에 4개 또는 8개씩 페이지를 읽으면서 계속 그 수를 증가시킨다

(한 번에 최대 64개 데이터 페이지까지 읽어 버퍼풀에 저장함) 그러면 포그라운드 스레드의 경우 미리 버퍼 풀에 준비된 데이터를 사용하기만 하면 된다.

 

innodb_read_ahead_threshold 시스템 변수를 조정하여 InnoDB 스토리지 엔진이 언제 리드 어헤드를 시작할 지 임계값을 설정할 수 있다. 

리드 어헤드 작업의 경우 풀 인덱스 스캔에서도 동일하게 사용된다. 

 

 

 

 

이런 요소 때문에 인덱스 조건에 일치하는 레코드 건수가 너무 많은 경우 풀테이블 스캔을 하는 게 더 효율적이다.