테이블 풀스캔이 인덱스 보다 성능이 더 좋다.

제목이 참 자극적이다. 사실 이 말은 절반만 맞다. 상황마다 다르기 때문이다. 대체적으로 인덱스가 성능이 좋지만, 절대적으로 테이블 풀 스캔이 마냥 나쁘다는 선입견을 없애고자 이렇게 지었다.

테이블 풀스캔이 무조건 나쁜 것으로 많이들 생각하지만 엄연히 말하면 나쁜 것은 아니다. 오히려 인덱스 보다 더 효율이 좋을 때가 많다. 인덱스를 깊게 공부를 해보면 인덱스가 생각보다 비효율적이란 걸 알 수 있는데, 이에 비해 풀스캔은 매우 효율적으로 동작한다.

예를 들어서 PK 가 단순 증가 정수(auto increment) 로 되어 있는 테이블에 아래 쿼리를 날렸다 보자.

select * from some_table where write_date between 오늘 and 다음주 limit 500,000

이 때 write_date 컬럼에 인덱스가 걸려 있는 상태여서 쿼리가 매우 빠르게 실행될거라 기대할 수 있다. 하지만 limit 절의 사이즈가 50만건이어서 대다수의 MYSQL 환경에서는 테이블 풀스캔으로 실행 되는 경우가 많다. 이유는 간단한데 인덱스가 동작하는 것이 비효율적이기 때문에 풀스캔을 돌린 후 레코드를 필터링하는 것이 더 좋겠다고 판단하는 것이다. 인덱스가 어떻게 동작하길래 풀스캔이 더 빠르다고 생각하는걸까?

인덱스가 동작하는 과정

나는 종종 근처 도서관에서 책을 빌려보곤 한다. 인덱스의 매카니즘은 책을 빌리는 과정과도 같다. 도서관에 들어서면 도서관의 도서 검색 시스템을 통해서 원하는 책의 위치를 알아낸 후에 책을 직접 가져온다. 이것을 여러번 반복한다. 빌려야할 책이 10권이라면, 검색 시스템에 다녀오고, 책 진열대를 찾아 책을 꺼내오는 과정을 10번을 한다. 조금 능숙해지면 미리 책 10권을 모두 검색해서 검색 시스템은 1번만 다녀오겠지만, 책 진열대를 찾아가는 것은 10번 해야 한다. 그런데 간혹 아까 전에 찾아갔던 책 진열대 근처의 진열대에 책이 위치에 있었다는 것을 알고는 '아 그럼 방금 여기 왔을 때, 한번에 다 가져올걸' 라며 아쉬움에 빠질 때가 있었다. 진열대 주소는 알았지만, 진열대가 물리적으로 서로 가까이 있는 줄은 몰랐기 때문이다. 이런 상황이 인덱스의 비효율성과 동일하다.

출처 Chat GPT

도서관의 상황처럼 인덱스는 인덱스 마다 바인딩 되어 있는 레코드의 하드디스크 위치를 찾아가는 과정이 있다. 이것은 건건이 발생한다. 앞선 내 경험 처럼 '한번에 다 가져올걸' 과 같은 것을 할 수가 없다. mysql 엔진은 인덱스에 바인딩된 레코드가 하드디스크의 어느 주소에 있다는 것만 알 뿐이지, 물리적으로 하드디스크의 어느 위치(섹터)에 있는 지는 알지 못한다. 그래서 앞선 예제의 쿼리 limit 500,000 절은 50만번이나 하드디스크를 탐방하는 것처럼 동작한다. 생각보다 비효율적이라는 느낌이 들까?

테이블 풀스캔 과정

그렇다면 테이블 풀스캔은 어떻길래 인덱스 보다 좋다고 주장하는 걸까? 앞선 도서관의 상황으로 비유하면 테이블 풀스캔은 책이 꽂혀있는 진열대의 책들을 통째로 가져오는 것과 같다. mysql 에서는 설정된 데이터페이지 사이즈 만큼 레코드를 읽어올 수 있다(정확히는 os 파일시스템의 블록 사이즈만큼 실행된다. os파일시스템의 블록사이즈 역시 실은 하드디스크의 헤더크기만큼 실행된다). 이를 택배로 치자면 트럭의 크기만큼 택배를 실어나를 수 있는 것과 같다. 여기에 최신 rdbms 들은 고급 기능 '사전읽기(read ahead)' 기능을 추가해서 풀스캔이 매우 빨라졌다. 사전읽기 기능은 풀스캔이 동작할 때에 다른 하드디스크 섹터도 읽을 것이라 예상하고 시키지 않았음에도 근처 하드디스크 섹터도 미리 읽어들이는 걸 말한다.

읽어들인 섹터의 데이터, 즉 레코드 페이지는 mysql 의 캐싱이라 할수 있는 버퍼풀에 모두 올려둔다. 만약 풀스캔에서 where 절과 같은 필터링이 있다면 레코드가 캐싱(버퍼룰) 되자마자 필터조건으로 부합하는 레코드를 찾아서 select 결과로 바로 flush 한다. 그래서 테이블 풀스캔 쿼리가 하드디스크 내에서 where 필터링이 돌거라 생각하지만, 기본적으로 mysql 에서는 모든 where 필터링은 메모리 내에서 이루어지기 때문에 느리지 않다. 하드디스크를 최대한 효율적으로 읽을수 있는 극대화 방식이 테이블 풀스캔이다. 그렇다면 테이블 풀스캔이 나쁘다는 이야기는 왜 나오는걸까? 이것의 문제는 우리가 사용하는 메모리는 제한적이라는 점 때문에 생긴다.

테이블 풀스캔이 나쁜 상황은 캐시 오염과 정렬이 발생할 때다.

1) 캐시 오염 (버퍼풀 오염)

테이블 풀스캔이 하드디스크를 아무리 효율적으로 읽더라도 결국 읽어들인 레코드는 mysql 캐시(버퍼룰)에 올라가게 된다. 아무리 효율적으로 읽더라도 캐시 사이즈가 적다면 무용지물이다. 풀스캔이 나쁜 영향을 끼치는 것은 하드디스크를 통째로 메모리에 올리는 것과 같은 상황을 초래할 수 있기 때문이다.

mysql 은 여러 사람(세션)이 함께 사용하는 시스템이다. 10명의 사람이 mysql 을 쓰고 있는 상황에서 어떤 1사람이 어떠한 테이블을 전체를 읽어들이려고 한다면, 이 1 사람으로 인해 하드디스크의 내용물을 모두 캐시에 올리려고 드는 것이 되면서 나머지 9명이 캐시를 사용하지 못하게 된다. 이렇게 되면 9명의 사람의 쿼리는 캐시되지 못하고 매번 하드디스크에서 읽어들이는 것과 같이 될 수가 있다.

구체적인 상황을 보자면 게임서비스에서 user 라는 개념 모델의 레코드는 대체적으로 항상 캐시되어 있을 확률이 매우 높다. 사용빈도가 높기 때문일테니깐. 그런데 어떠한 세션이 게임 플레이 로그 테이블을 풀스캔하게 된다면, 기존에 캐시에 적재되어 있던 user 레코드는 캐시에서 없어지게 될 수 있다. (사실 LRU( least recently used) 알고리즘에 의해 캐시 히트율이 높은 user 레코드 캐시는 왠만해서는 삭제되지 않을 수도 있다.)

이야기가 조금 세는 감이 있지만, 캐시 히트율(버퍼풀 히트율) 에 대해서 짤막하게 얘기하고 넘어가보겠다. mysql 8 에서 아래 쿼리를 실행하면 캐시 히트율을 알아볼수있다.

SHOW GLOBAL STATUS LIKE 'innodb_buffer_pool_read%';

Variable_name	Value
Innodb_buffer_pool_read_ahead_rnd	0
Innodb_buffer_pool_read_ahead	7170364
Innodb_buffer_pool_read_ahead_evicted	551375
Innodb_buffer_pool_read_requests	37644238992
Innodb_buffer_pool_reads	15429586

여기서 2개의 속성 Innodb_buffer_pool_reads/Innodb_buffer_pool_read_requests 을 계산하면 캐시 히트율을 알 수 있다. 15429586 / 37644238992 수치를 계산하면 약 4%의 히트율을 보인다고 할수 있다. 이 mysql 을 사용하는 세션들의 조회 쿼리들은 생각보다 효율이 좋지 못하다는 것을 알 수 있다.

풀스캔 빈도가 높아지면 이 캐시히트율 수치가 낮아지게 된다. 캐시히트율이 낮다는 것은, 사실상 쿼리를 효율적으로 쓰고 있지 않다가 됨으로 CPU는 놀고 있고, 하드디스크 사용률은 100% 에 도달하는 기 현상을 맛볼수 있다.

RDBMS 에서는 DB를 사용하는 관점에 따라 OLTP / OLAP 으로 RDBMS 사용 구성을 구분할 수있다. 우리가 만드는 대부분의 시스템은 OLTP 에 해당하는데, 이 경우에는 캐시히트율이 높아야 한다. 반대로 OLAP 에서는 캐시히트율이 낮아도 무관하다. OLTP 에서는 작은 량의 데이터를 실시간으로 자주 질의하는 경우가 많고, OLAP 에서는 대용량의 데이터를 드물게 질의하는 경우가 많은 시스템의 특성이 있기 때문이다.

2) order 정렬

mysql 에서 정렬만큼 무서운 쿼리도 없다. 공부를 하면 할수록 느끼는 거지만 학부생 때 왜 그렇게 강의 커리큘럼이 정렬 알고리즘을 거의 세뇌에 가깝게 학습시키는 지 납득이 된다. 이 챕터의 내용은 정렬 알고리즘과 관련이 있기 때문이다.

나는 인덱스를 사용하는 쿼리에서 order by 정렬을 사용해왔기에 실제 정렬이 어떻게 수행 되는지에 대해서는 관심이 없었다. 인덱스 자체가 이미 정렬이 되어 저장이 되어있으니깐 말이다. 그런데 문득 인덱스를 사용하지 않는 테이블 풀스캔은 어떻게 정렬을 하는 걸까? 란 궁금증이 생겼다. 앞선 내용에 따르면 테이블풀스캔은 레코드를 캐시에 올리고 정렬을 해야한다는 소리가 되는데, 이는 말이 안될테니 너무나 궁금했다. 하드디스크의 사이즈만큼이나 거대한 메모리는 없기 때문에 정렬은 캐시의 사이즈만큼 일부분만 할수있을테니 말이다.

정렬은 전체 레코드셋을 알아야지만 수행할수있다. 그런데 메모리의 용량은 한계가 있고, 이는 전체 레코드셋을 캐시에 모두 올리지 못한다는 얘기가 됨으로 테이블 풀스캔의 정렬은 불가능한 영역처럼 보여진다. 하지만 우리의 선배 개발자들은 이러한 문제를 뛰어넘었으니, 폰노이만의 merge sort 알고리즘을 사용해서 해결했다.

Increase the sort_buffer_size variable value. Ideally, the value should be large enough for the entire result set to fit in the sort buffer (to avoid writes to disk and merge passes). https://dev.mysql.com/doc/refman/8.0/en/order-by-optimization.html

A filesort operation uses temporary disk files as necessary if the result set is too large to fit in memory. https://dev.mysql.com/doc/refman/8.0/en/internal-temporary-tables.html

 

테이블 풀스캔은 Mysql  InnoDb 스토리지 엔진이 페이지 단위(Mysql 기본 설정값 16KB)로 하드디스크에서 레코드 페이지를 읽어오며, 사전 읽기(read-ahead) 기능을 통해 연속된 레코드 페이지들을 버퍼풀에 미리 캐싱한다. MySQL SQL 쿼리 처리 계층은 버퍼풀에서 레코드를 하나씩 가져와, 정렬 기준 컬럼을 sort buffer라는 메모리 공간에 누적시킨다. 이 sort buffer가 가득 차면, 현재까지 누적된 레코드들을 메모리에서 정렬하고, 정렬된 결과를 블록(run)이란 단위로 디스크의 임시 테이블 파일(sorted run file)에 저장한다. 이 과정을 반복하면 전체 테이블 레코드셋은 정렬된 여러 블록으로 구분되고 나뉘어 저장된다. 이 블록들을 merge sort 알고리즘과 함께 결합해서 select 쿼리 결과로 내보내는 것이 바로 테이블 풀스캔의 정렬 방식이다. 사실 정확히는 인덱스가 타지 않는 order by 에 대한 정렬(Using temporary, Using filesrot)이 모두 이런식으로 동작한다.

쉽게 인터넷에서 찾아 볼 수 있는 merge sort 알고리즘 도식도를 함께 살펴보면, mysql 정렬 과정을 상상해볼 수 있다. 아래 이미지에서 파란색 영역이 테이블 풀스캔 과정이라 볼수있고, 아래 노란색 영역은 임시 테이블에서 각 블록들을 merge sort 하여 결합 하는 과정과 동일하다 할 수 있다.

결론

“결국 테이블 풀스캔은 안 좋은 거 아닌가요?”라는 얘기를 하고 있는 게 아니냐며 핀잔을 줄 수 있다. 실제로 우리들이 개발하는 대부분의 시스템 OLTP 환경(예: 웹 서비스 트랜잭션 처리)에서는 테이블 풀스캔은 피해야 하는 접근이 맞다. 하지만 테이블 풀스캔을 단순히 "악의 축"처럼 여기는 생각은 잘 못된 생각이다. 이 글을 쓴 이유는 이러한 오해를 풀기 위함이다.

예를 들어, OLAP 환경에서는 대용량의 데이터를 집계·분석하는 쿼리를 사용함으로 테이블 풀스캔이 빈번하게 사용된다. 그리고 서비스 운영을 위한 백오피스나 CMS 같은 서비스 관리 시스템에서도 풀스캔이 자주 발생할 수 있다.

대부분의 MySQL 인프라 구성에서는 OLTP를 처리하는 Primary(MySQL Master) 서버와, 분석/조회 목적의 Replica(MySQL Slave) 서버로 분리되어 구성된다. 그래서 Primary 를 쓰는 시스템인지, Replicat 를 쓰는 시스템인지에 따라서 테이블 풀스캔을 바라보는 입장과 태도가 다르기도 하다. Primary 를 사용하는 OLTP 시스템에서는 테이블 풀스캔은 긴장의 대상이 될것이지만, OLAP 시스템에서는 친근할 것이다. 그럼으로 이러한 차이를 이해하고 단순히 "풀스캔은 느리다" 라는 선입견에 머무리지 말고, 상황에 다른 판단과 성능 분석 능력을 갖추는 것이 중요하다. 이 글이 선입견을 벗어내는 데 도움이 되었으면 좋겠다.