기본 데이터 처리

• MySQL 서버를 포함한 모든 RDBMS는 데이터를 정렬하거나 그루핑하는 등의 기본 데이터 가공 기능을 가지고 있다.
  • 결과물은 동일하더라도 RDBMS 별로 그 결과를 만들어 내는 과정은 다르다.
• MySQL 서버가 이러한 기본적인 가공을 위해 어떤 알고리즘을 사용하는지 살펴본다.

풀 테이블 스캔과 풀 인덱스 스캔

풀 테이블 스캔

• 인덱스를 사용하지 않고 테이블의 데이터를 처음부터 끝까지 읽어서 요청된 작업을 처리하는 작업을 의미한다.
• MySQL 옵티마이저는 다음과 같은 조건이 일치할 때 주로 풀 테이블 스캔을 선택한다.
  • 테이블의 레코드 건수가 너무 작아서 인덱스를 통해 읽는 것보다 풀 테이블 스캔을 하는 편이 더 빠른 경우
    • 일반적으로 테이블이 페이지 1개로 구성된 경우
  • WHERE 절이나 ON 절에 인덱스를 이용할 수 있는 적절한 조건이 없는 경우
  • 인덱스 레인지 스캔을 사용할 수 있는 쿼리라고 하더라도 옵티마이저가 판단한 조건 일치 레코드 건수가 너무 많은 경우
    • 인덱스의 B-Tree를 샘플링해서 조사한 통계 정보 기준
• 일반적으로 테이블의 전체 크기는 인덱스보다 훨씬 크기 때문에 테이블을 처음부터 끝까지 읽는 작업은 상당히 많은 디스크가 필요하다.
• 대부분 DBMS는 풀 테이블 스캔을 실행할 때 한꺼번에 여러 개의 블록이나 페이지를 읽어오는 기능을 내장하고 있다.
• MySQL에는 풀 테이블 스캔을 실행할 때 한꺼번에 몇 개씩 페이지를 읽어올지 설정하는 시스템 변수는 없다.
  • MyISAM 스토리지 엔진은 디스크로부터 페이지를 하나씩 읽어 온다.
  • InnoDB 스토리지 엔진은 특정 테이블의 연속된 데이터 페이지가 읽히면 백그라운드 스레드에 의해 리드 어헤드 작업이 자동으로 시작된다.

💡 리드 어헤드 (Read ahead)
어떤 영역의 데이터가 앞으로 필요해지리라는 것을 예측해서 요청이 오기 전에 미리 디스크에서 읽어 InnoDB의 버퍼 풀에 가져다 두는 것을 의미한다.

즉, 풀 테이블 스캔이 실행되면 처음 몇 개의 데이터 페이지는 포그라운드 스레드(Foreground thread)가 페이지 읽기를 실행하지만 특정 시점부터는 읽기 작업을 백그라운드 스레드로 넘긴다.

백그라운드 스레드가 읽기를 넘겨받은 시점부터는 한 번에 4개 또는 8개씩의 페이지를 읽으면서 계속 그 수를 증가시킨다. 이때, 한 번에 최대 64개의 데이터 페이지까지 읽어서 버퍼 풀에 저장해 둔다.

포그라운드 스레드는 미리 버퍼 풀에 준비된 데이터를 가져다 사용하기만 하면 되므로 쿼리가 상당히 빨리 처리된다.

• MySQL 서버에서는 innodb_read_ahead_threadhold 시스템 변수를 이용해 InnoDB 스토리지 엔진이 언제 리드 어헤드를 시작할지 임계값을 설정할 수 있다.
  • 포그라운드 스레드에 의해 해당 시스템 변수에 설정된 개수만큼의 연속된 데이터 페이지가 읽히면, InnoDB 스토리지 엔진은 백그라운드 스레드를 이용해 대량으로 그 다음 페이지들을 읽어서 버퍼 풀로 적재한다.
  • 일반적으로 디폴트 설정으로도 충분하다.
  • MySQL을 데이터 웨어하우스용으로 사용한다면 이 옵션을 더 낮은 값으로 설정해서 더 빨리 리드 어헤드가 시작되게 유도하는 것도 좋은 방법이다.

풀 인덱스 스캔

• 자세한 내용은 앞서 ‘인덱스’에서 다룬 내용 참고
• 풀 인덱스 스캔에서도 리드 어헤드를 사용한다.
  • 인덱스를 처음부터 끝까지 스캔하는 것을 의미
• 인덱스는 테이블의 2~3개 칼럼만으로 구성되기 때문에 테이블 자체보다는 용량이 작아서 훨씬 빠른 처리가 가능하다.

예제

SELECT COUNT(*) FROM employees;

• 실제 실행 계획은 풀 인덱스 스캔을 하게 될 가능성이 높다.
• MySQL 서버는 단순히 레코드의 건수만 필요로 하는 쿼리라면 용량이 작은 인덱스를 선택하는 것이 디스크 읽기 횟수를 줄일 수 있기 때문이다.

SELECT * FROM employees;

• 레코드에만 있는 칼럼이 필요한 쿼리의 경우, 풀 테이블 스캔을 한다.

Reference

참고 서적

📔 Real MySQL 8.0