B-Tree index를 통한 데이터 읽기
어떤 경우에 인덱스를 사용하도록 유도할지, 안 할지 판단하기 위해서 MySQL(스토리지 엔진)이 어떻게 인덱스를 이용(경융)해서 실제 레코드를 읽어 내는지 알 필요가 있는데, 여기서는 MySQL이 인덱스를 이용하는 대표적인 세 가지 방법을 학습하는 것을 목표로 한다.
1. 인덱스 레인지 스캔 (index range scan)
레인지 스캔은 인덱스 접근 방법 중 가장 대표적인 방법으로, 셋 중에서 가장 빠른 방법이다.
아래와 같은 쿼리가 어떻게 인덱스를 타고 데이터를 가져오는지 알아보자.
select * from employees where first_name between 'Ebbe' and 'Gad';
인덱스 레인지 스캔은 검색해야 할 인덱스의 범위가 결정됐을 때 사용하는 방식이다. 검색하려는 값의 수나 검색 결과 레코드 건수와 관계없이 레인지 스캔이라고 표현한다.
그림처럼 루트 노드에서 비교를 시작해 브랜치 노드를 거치고 리프 노드까지 가야지 필요한 레코드의 시작 지점을 알 수 있다.
시작해야 할 위치를 찾으면 그때부터는 리프 노드의 레코드만 순서대로 읽으면 된다. 이처럼 차례대로 쭉 읽는 것을 스캔이라 표현한다. 만약 스캔하다가 리프 노드의 끝까지 읽으면 리프 노드 간의 링크를 이용해서 다음 리프 노드에서 다시 스캔한다.
B-Tree index는 루트와 브랜치 노드를 통해 스캔 위치를 탐색하고 리프 노드에서 검색 조건에 일치하는 건들을 데이터 파일에서 레코드를 읽어온다. 그림과 같이 시작 지점이 정해지면 방향(오름차순, 내림차순)으로 인덱스를 읽어 나가는데, 이것은 병렬 과정이 수반되는 것이 아니라 인덱스 자체의 정렬 특성 때문에 자동으로 그렇게 된다.
또, 리프 노드에 저장된 레코드 주소로 데이터 파일을 읽어올 때, 한 건 단위로 랜덤 I/O가 한 번씩 발생한다. 3건의 레코드가 검색 조건에 일치한다면 랜덤 I/O가 최대 3번 필요한 것이다.
그래서 인덱스를 통한 조회는 비용이 많이 발생하고 20~25% 손익 분기점을 넘으면 테이블 데이터를 직접 읽는 것이 효율적이라고 한다.
인덱스 레인지 스캔을 정리해보면 다음과 같이 3단계를 거친다.
루트/브랜치 노드를 통해 리프 노드까지 탐색하고 조건에 해당하는 값이 저장하는 위치를 찾는다. 이를 인덱스 탐색(index seek)이라고 한다.
찾은 값의 위치부터 필요한 만큼 인덱스를 차례로 읽는다.
리프 노드는 양방향 Linked List로 연결되어 있어 순차적 접근 가능
where 절의 조건에 맞는 범위까지만 스캔
읽어들인 인덱스 키와 레코드 주소를 이용해 레코드가 저장된 페이지를 가져오고, 최종 레코드를 읽어온다.
쿼리 조건에 따라 3번 과정이 필요 없는 경우도 있는데, 이를 커버링 인덱스라고 한다.
커버링 인덱스로 처리되는 쿼리는 디스크의 레코드를 읽지 않아도 되기 때문에 랜덤 읽기가 줄어들고 성능이 빨라진다.
조회되는 컬럼들이 모두 인덱스의 리프 노드에 존재하기 때문에 데이터 페이지에 접근할 필요가 없어지기 때문이다.
실제로 MySQL에서 인덱스로 읽은 레코드 건수를 확인하고 싶으면 Handler 키워드로 알아보자.
2. 인덱스 풀 스캔 (index full scan)
인덱스의 처음부터 끝까지 모두 읽는 방식이다.
쿼리의 조건절에 사용되는 컬럼이 인덱스의 첫 번째가 아닌 경우 풀 스캔 방식이 사용된다.
일반적으로 인덱스의 크기는 테이블의 크기보다 작기 때문에 테이블을 처음부터 다 읽는 것보다는 인덱스만 일근 것이 효율적이다. 쿼리가 인덱스에 명시된 컬럼만으로 조건을 처리할 수 있는 경우 사용된다.
인덱스뿐만 아니라 데이터 레코드까지 모두 읽어야 한다면 절대 이 방식으로 처리되지 않는다.
인덱스 풀 스캔 과정을 살펴보면, 먼저 인덱스 리프 노드의 제일 앞 또는 뒤로 이동한 후, 인덱스의 리프 노드를 연결하는 링크그 리스트를 따라서 처음부터 끝까지 스캔한다.
인덱스 레인지 스캔보다는 느리지만 테이블 풀 스캔보다는 효율적이다. 다시 언급하지만, 인덱스에 포함된 컬럼만으로 쿼리를 처리할 수 있는 경우에 사용한다. 인덱스의 전체 크기는 테이블 자체의 크기보다는 훨씬 작으므로 적은 디스크 I/O로 쿼리를 처리할 수 있다.
3. 인덱스 스킵 스캔 (index skip scan)
DB 서버에서 인덱스의 핵심은 값이 정렬돼 있는 것이다. 따라서 인덱스를 구성하는 컬럼의 순서가 매우 중요하다.
예를 들어, 다음과 같이 인덱스를 생성해보자.
생성한 인덱스를 사용하려면 where 절에 gender 컬럼에 대한 비교가 필수이다. 비교 조건이 birth_date 부터 시작하는 경우에는 인덱스를 새로 생성해야 한다.
MySQL 8.0 이상부터는 옵티마이저가 gender 컬럼을 건너뛰고 birth_date 컬럼만으로도 인덱스 검색이 가능하게 해주는 인덱스 스킵 스캔 최적화 기능이 도입됐다.
이전 버전에서도 이와 비슷한 최적화를 수행하는 루즈 인덱스 스캔(Loose index scan) 기능이 있지만 루즈 인덱스 스캔은 group by 작업을 처리하기 위해 인덱스를 사용하는 경우에만 적용할 수 있다.
하지만 8.0 이상부터 도입된 인덱스 스킵 스캔은 where 조건절의 검색을 위해 사용 가능하도록 용도가 훨씬 넓어졌다.
gender 컬럼은 ‘M’, ‘F’ 값만 가지는 ENUM 타입이고, 위에서 birth_date 컬럼 인덱스만 사용해서 조회하는 쿼리에서, 인덱스 풀 스캔을 사용하면 idx_gender_birthdate 인덱스를 사용한다.
옵티마이저는 먼저 gender 컬럼에서 유니크한 값을 모두 조회해서 주어진 쿼리에 gender 컬럼의 조건을 추가한 후, 쿼리를 다시 실행하는 형태로 처리한다.
즉, 옵티마이저가 내부적으로 아래 2개의 쿼리를 먼저 실행하는 것과 비슷하게 최적화를 진행한다.
4. 루스 인덱스 스캔 (Loose index scan)
루스 인덱스 스캔은 오라클과 같은 DBMS의 ‘인덱스 스킵 스캔’이랑 기능과 작동 방식은 비슷하지만 MySQL에서는 이를 루스 인덱스 스캔이라고 한다.
앞에서 range, full scan과 상반된 의미에서 tight index scan으로 분류한다. 말 그대로 느슨하게 또는 듬성듬성하게 인덱스를 읽는 것을 의미한다.
루즈 인덱스 스캔은 range 스캔과 비슷하게 작동하지만 중간에 필요하지 않은 인덱스 키값은 무시(skip) 하고 다음으로 넘어가는 형태로 처리한다.
일반적으로 group by 또는 집합 함수 가운데 max(), min() 함수에 대해 최적화를 하는 경우에 사용된다.
쿼리에서 조회하려는 테이블은 dept_no, emp_no 라는 두 개의 컬럼으로 인덱스가 생성돼 있다. 그리고 (dept_no, emp_no) 조합으로 정렬까지 돼 있어서 그림과 같이 dept_no 그룹 별로 첫 번째 레코드의 emp_no 값만 읽으면 된다.
즉, 인덱스에서 where 조건을 만족하는 범위 전체를 다 스캔할 필요가 없다는 것을 옵티마이저는 알고 있기 때문에 조건에 만족하지 않는 레코드는 무시하고 다음 레코드로 이동한다.
그래서 그림에서도 인덱스 리프 노드를 스캔하면서 불필요한 부분은 그냥 건너뛰고 필요한 부분(회색 막대)만 읽는다.
Last updated