[MySQL] 1. 인덱스의 개념과 인덱스 스캔의 종류

인덱스란?

DBMS에서 인덱스란 데이터의 저장(INSERT, UPDATE, DELETE)의 성능을 희생하고 그 대신 데이터의 읽기 속도를 높이는 기능이다.
인덱스의 대표적인 예시로 언급되는 책의 맨 끝의 찾아보기(색인)으로 인덱스를 쉽게 이해할 수 있다.
책의 마지막에 있는 찾아보기가 인덱스라면, 책의 내용은 데이터파일, 찾아보기(색인)에 적혀있는 페이지 번호는 데이터 파일에 저장된 레코드의 주소값이라고 볼 수 있다.
인덱스를 이용해 빠르게 특정 데이터를 조회할 수 있는건 사실이지만, 첫 번째 문장에서 언급한 바와 같이 인덱스를 하나 더 추가할 지 말지는 데이터의 레코드 수, 저장속도(INSERT,UPDATE,DELETE)를 어느정도 까지 희생할 수 있는지, 읽기 속도를 얼마나 더 빠르게 만들어야 하는지 등 여러가지를 고려하여 결정해야한다.

B-Tree 인덱스 사용시 고려해야 하는 부분

글의 초입부에서 인덱스를 사용하는 경우 인덱스를 구성하는 칼럼의 크기, 레코드 건수 등을 고려해야 한다고 한 바 있습니다.

이에 대해 자세히 살펴보도록 하겠습니다.

1. 인덱스 키 값의 크기

InnoDB 스토리지 엔진은 디스크에 데이터를 저장하는 가장 기본 단위를 페이지(page) 또는 블록(block)이라고 한다.
페이지(or 블록)은 디스크의 모든 읽기 또는 쓰기의 최소 작업 단위이다.
index 페이지의 구조

전제 상황
- MySQL 5.7 부터는 페이지 크기를 innodb_page_size 시스템 변수를 이용해 4KB ~ 64KB 사이의 값으로 설정할 수 있지만 기본값은 16KB
- 자식 노드의 주소(복합 정보의 영역)페이지 종류별로 6 ~ 12바이트까지 다양한 값을 가질 수 있지만 예시로는 인덱스의 키 값은 16바이트, 자식 노드의 주소는 12바이트로 구성된다고 가정합니다.
예시를 통한 설명
- 그림과 같이 하나의 인덱스 페이지(16KB) 에서 인덱스의 키가 16바이트이고, 자식 노드의 주소가 12바이트라고 가정한다면 몇개의 인덱스(키)를 저장할 수 있을까?
  -> 답은 16*1024 / (16 + 12) = 585 (개)이다.
- 그렇다면 이 상황에서 인덱스의 키 값의 2배 늘어난 32 byte가 된다면 어떻게 될까?
  -> 16 * 1024 / (32 + 12) = 372 (개)로 하나의 인덱스 페이지에 저장할 수 있는 키의 값은 줄어들게 된다.

즉, 인덱스 키 값의 길이가 길어진다는 것은 전체적인 인덱스의 크기가 커진다는 것을 의미한다.
인덱스를 캐시해두는 InnoDB의 버퍼풀 등은 그 크기가 제한적이기 때문에 인덱스 키의 크기가 커지면 커질수록 메모리에 캐시해 둘 수 있는 레코드의 수는 줄어들게 된다.

인덱스 키 값의 크기는 가능하면 작게 만드는 것이 좋다. (요구사항에 따라 달라질 수 있지만)

2. B-Tree의 깊이

B-Tree 인덱스의 depth는 상당히 중요하지만 개발자가 직접 제어할 수 있는 방법은 없다.
B-Tree의 depth는 DBMS에서 몇 번이나 랜덤하게 디스크를 읽어야 하는지와 직접적으로 연관되는 문제로 1번에서 언급한 '인덱스 키 값의 크기'와 관련이 있다.
결론적으로, 키 값의 크기가 커지면 하나의 인덱스 페이지가 담을 수 있는 인덱스의 개수가 적어지고, 같은 레코드 수를 가지고 있다고 하더라도 B-Tree의 깊이가 깊어져 디스크 읽기가 더 많이 필요하다.

3. 선택도 (기수성)

인덱스에서 선택도(Selectivity)와 기수성(Cardinality)는 거의 같은 의미로 사용된다.
선택도란, 모든 인덱스 키 값 가운데 유니크한 값의 수를 의미한다.
중복된 값이 많아지면, 선택도가 떨어진다. 인덱스의 경우 선택도가 높을수록(유니크할 수록) 검색 대상이 줄어들기 때문에 그만큼 빨리 처리된다.
그렇다고 무조건적으로 선택도가 높은 칼럼에 대해서만 인덱스를 생성하는 것이 좋을까?
- ❌ 그렇지 않다. 선택도가 좋지 않다고 하더라도 정렬(order by)나 그룹핑(group by) 같은 작업에서는 인덱스를 만드는 것이 훨씬 나은 경우도 존재한다. (정렬과 그룹핑에서의 인덱스 사용 및 실행계획은 2편에서 설명한다.)
- 검색의 경우 선택도를 고려해야겠지만, 인덱스가 항상 검색에만 사용되는 것은 아니므로 기능을 고려하여 인덱스를 설계할 필요가 있다.

4. 읽어야하는 레코드의 건수

어찌보면 인덱스는 테이블의 레코드를 바로 읽기 전에 하나의 과정이 더 추가되는 높은 비용이 드는 작업이라고 볼 수 있다.
이때 인덱스를 이용한 읽기의 손익분기점이 얼마인지 판단할 필요가 있다.

일반적으로는 인덱스를 통해 읽어야할 레코드의 건수가 전체 테이블의 20~25%를 넘어서면 인덱스를 이용하지 않고 테이블을 모두 직접 읽어서 필요한 레코드만 가려내는(필터링) 방식으로 처리하는것이 효율적이다.

인덱스의 종류

클러스터링이란? 여러 개를 하나로 묶는다는 의미이다

MySQL 역시, 클러스터링을 비슷한 의미로 사용한다.
MySQL 서버에서 클러스터링은 테이블의 레코드를 비슷한 것(PK를 기준으로)들끼리 묶어서 저장하는 형태로 구현하는데 이는 주로 비슷한 값들을 조회하는 경우가 많다는 점에서 착안했다.

MySQL 기준으로 클러스터링 인덱스는 InnoDB 스토리지 엔진에서만 지원하며 ISAM등 다른 스토리지 엔진에서는 클러스터링 인덱스를 지원하지 않는다.

클러스터링 인덱스

클러스터링 인덱스는 프라이머리 키 값이 비슷한 레코드끼리 묶어서 저장하는 것을 의미한다.
중요한 것은 PK 값에 의해 레코드의 저장 위치가 결정된다는 것이고, PK로 클러스터링 된 테이블은 프라이머리 키 값 자체에 대한 의존도가 상당히 크기 때문에 신중하게 PK를 결정해야 한다.
- PK에 의해 레코드의 저장 위치가 결정되므로 인덱스 알고리즘이라기 보다 테이블 레코드의 저장 방식으로 볼 수도 있다.
- (클러스터링 인덱스와 클러스터링 테이블은 동의어로 사용되기도 한다!)
InnoDB에서 클러스터링 인덱스는 테이블 당 딱 하나만 가질 수 있다.
InnoDB에서 테이블에 PK가 선언되어 있거나 특정 컬럼이 (UNIQUE + NOTNULL)로 생성되어 있다면 클러스터링 인덱스는 항상 하나가 생성된다.
InnoDB 처럼 항상 클러스터링 인덱스로 저장되는 테이블은 PK 기반의 검색이 빠르지만 레코드의 저장이나 PK의 변경이 느리다.(사실 PK를 변경할 일은 많이 없긴 하다.)

클러스터링 인덱스의 구조

BTree 인덱스와 달리 클러스터링 인덱스의 리프노드에는 레코드의 모든 칼럼이 같이 저장되어 있다.
즉, 클러스터링 테이블(클러스터링 인덱스)는 하나의 거대한 인덱스 구조로 관리되고 있다고 볼 수 있다.
루트노드는 PK와 자식노드의 페이지 번호를 가지고 있다.
리프노드의 경우 각 페이지에 PK와 모든 칼럼을 가지고 있다.

클러스터링 인덱스를 사용하는 테이블의 레코드에서 PK 값을 변경한다면?
-> 클러스터링 테이블도 모두 변경된다.
PK를 변경할 일이 많이 없을테지만, PK의 중요성을 확인했으니 테이블 설계시 PK를 더 신중히 고려하자.

인덱스 스캔의 종류

1. 인덱스 레인지 스캔

인덱스 레인지 스캔은 인덱스의 접근 방법 중 가장 대표적인 접근 방식이다.
검색해야 할 인덱스의 범위가 결정되었을 때 사용하는 방식으로 검색하려는 값의 수나 검색 결과 레코드 건수와 관계 없이 레인지 스캔이라고 표현한다.
인덱스에서 루트와 브랜치 노드를 이용해 스캔 시작 위치를 검색하고, 그 지점부터 필요한 방향(오름차순 or 내림차순)으로 인덱스를 읽어나가는 과정이 존재한다.

인덱스 레인지 스캔의 과정
1. 인덱스에서 조건을 만족하는 값이 저장된 위치를 찾는다. (인덱스 탐색 과정)
2. 1번에서 탐색된 위치부터 필요한 만큼 인덱스를 차례대로 쭉 읽는다. (인덱스 스캔 과정)
3. 2번에서 읽어 들인 인덱스의 키와 레코드 주소를 이용해 레코드가 저장된 페이지를 가져오고 최종 레코드를 읽어온다.
  (만약, select 하려는 칼럼들이 인덱스로 구성되어있는 커버링 인덱스라면 3번 과정은 필요치 않아질 수 있다.)

2. 인덱스 풀 스캔

인덱스 레인지 스캔과는 달리 인덱스의 처음부터 끝까지 모두 읽는 방식을 인덱스 풀 스캔이라고 한다.

대표적으로 쿼리의 조건절에 사용된 칼럼이 인덱스의 첫 번째 컬럼이 아닌 경우 인덱스 풀 스캔이 사용된다.
- ex) 인덱스는 (A,B,C) 순서로 만들어져 있지만, 쿼리의 조건절은 B 컬럼이나 C컬럼으로 검색하는 경우
일반적으로 테이블을 처음부터 끝까지 읽는 것 보다는 인덱스만 읽는 것이 효과적이기 때문에 쿼리가 인덱스에 명시된 칼럼만으로 조건을 처리할 수 있는 경우에 주로 이 방식이 사용된다.
- 즉, 인덱스 레인지 스캔보다는 빠르지 않지만 테이블 풀 스캔보다는 효율적
- 테이블 풀 스캔보다 효과적이긴 하지만, 인덱스 풀 스캔을 사용하는 것은 '인덱스 레인지 스캔'이나 '루스 인덱스 스캔'보다 효율적인 방식은 아니며, 일반적으로 인덱스를 생성하는 목적이 아니다.
- 테이블 전체를 읽거나 인덱스 풀 스캔을 사용하는 경우는 '인덱스를 효율적으로 사용하지 못한다'는 관점에 더 가깝다.

3. 루스 인덱스 스캔

오라클의 '인덱스 스킵 스캔'과 작동방식,기능이 비슷하다.
루스 인덱스 스캔은 인덱스 레인지 스캔과 비슷하게 동작하지만, 중간에 필요치 않은 인덱스 키 값은 무시(SKIP)하고 다음으로 넘어가는 형태로 처리된다.
일반적으로 GROUP BY나 집합함수(MAX, MIN)에 대해 최적화를 하는 경우에 사용한다.

상황 가정 및 결론
- 테이블에서 (dept_no, emp_no) 조합으로 인덱스가 생성 및 정렬되어있는 상황
- 첫번째 값(dept_no)만 읽으면 된다는 것을 옵티마이저는 알고 있기 때문에 조건에 만족하지 않는 레코드는 무시하고 다음 페이지로 넘어간다.

SELECT dept_no, MIN(emp_no)
FROM dept_emp
WHERE dept_no BETWEEN 'd002' AND 'd004'
GROUP BY dept_no;

4. 인덱스 스킵 스캔

인덱스를 사용하는데에 있어서 인덱스를 구성하는 칼럼의 순서는 매우 중요하다.
테이블에 인덱스가 (gender, birth_date)로 2개의 칼럼에 대해 걸려있을 때, MySQL 8.0 이전의 경우 인덱스를 사용하기 위해서는 앞에 있는 인덱스인 gender 칼럼에 대한 비교 조건이 필수적이다.

//인덱스를 사용하지 못하는 쿼리
SELECT * FROM employees WHERE birth_date >= '1999-12-06'

//인덱스를 사용하는 쿼리
SELECT * FROM employees WHERE gender = 'M' AND birth_date >= '1999-12-06'

MySQL 8.0 이후
- 8.0부터 gender 칼럼을 뛰어넘어서 뒤에 있는 칼럼 만으로 인덱스 검색을 가능하게 해주는 '인덱스 스킵 스캔 최적화 기능'이 도입되었다.
- WHERE 절에서 뒤에 있는 칼럼으로 검색가능하도록 개선된 것
- 실행계획에는 "Using index for skip scan"이라는 문구로 표시된다.
인덱스 스킵 스캔의 단점
- WHERE 조건절에 조건이 없는 인덱스의 선행 칼럼의 유니크한 값의 개수가 적어야 한다.
  - 아까는 선택도(기수성)이 높아야, 즉 유니크한 칼럼이 좋은 인덱스라더니 이건 무슨말인가 하면 🧐 이 부분은, 쿼리 실행 계획 비용과 관련된 내용이다.
  - 만약 유니크한 값의 개수가 매우 많다면 옵티마이저는 스캔을 시작할 시작지점을 검색하는 작업이 많아져 오히려 느려질 수 있다.
  - 인덱스 스킵 스캔의 특이점이라고 생각하고, 인덱스 스킵 스캔은 인덱스의 선행 칼럼이 가진 유니크한 값의 개수가 소량일 때만 적용 가능한 최적화라는 것을 기억해두자.
- 쿼리가 인덱스에 존재하는 칼럼만으로 처리 가능해야 한다. (커버링 인덱스여야 한다는 뜻이다.)
  - 인덱스에 포함된 gender, birth_date 칼럼 이외의 나머지 칼럼도 필요하다면 인덱스 스킵 스캔을 사용하지 못하고 풀 테이블 스캔으로 실행계획을 수립할 수 있다.
  - (이 부분은 추후에 버전이 올라간다면 개선될 여지가 있는 부분으로 보여 버전 별로 확인이 필요하다고 생각한다.)

5. 다중 칼럼 인덱스 (Multi-Column 인덱스)

2개 이상의 칼럼으로 구성된 인덱스를 다중 칼럼 인덱스 또는 복합 칼럼 인덱스라고 하며 2개 이상의 칼럼이 연결되었다고 해서 Concatenated Index라고도 한다.
중요한 점은 두 번째 칼럼은 첫 번째 칼럼에 의존하여 정렬되어 있다는 것이다. 즉 두 번째 칼럼의 정렬은 첫 번째 칼럼이 같은 레코드에서만 의미가 있다는 것이다.
- (dept_no, emp_no) 순서로 인덱스가 걸려 있을 때, emp_no의 정렬 순서가 빠르다고 하더라도 앞 칼럼인 dept_no의 정렬 순서가 늦다면 인덱스의 뒤쪽에 위치한다.

그렇다면, 어떤 상황에서 인덱스를 '잘' 사용할 수 있는가?

쿼리의 WHERE 조건이나 GROUP BY 또는 ORDER BY 절이 어떤 경우에 인덱스를 사용할 수 있는지 식별할 수 있어야 쿼리를 최적화하거나,쿼리에 맞는 인덱스를 생성할 수 있다.
용어 정리
- 작업 범위 결정 조건 : 작업의 범위를 결정하는 조건
- 필터링 조건(체크 조건) : 비교 작업의 범위를 줄이지 못하고 단순히 거름종이 역할의 필터링만 하는 조건

비교 조건(동등성 비교, 범위 비교)에서 인덱스의 사용
- 다중 칼럼 인덱스의 경우, 인덱스가 걸리는 칼럼의 순서가 중요하다.
- 작업 범위 결정 조건은 많으면 많을 수록 쿼리의 처리 성능은 높이지만 체크 조건은 많다고 해서 쿼리의 처리 성능을 높이지는 못한다. (쿼리의 실행을 느리게 할 때도 있음.)
인덱스를 사용할 수 없는 경우

B-Tree 인덱스 특성상 아래의 조건에서는 '작업 범위 결정 조건'으로 사용할 수 없다.

<단일 칼럼 인덱스의 경우>

NOT-EQUAL로 비교되는 경우
- <> , NOT IN, NOT BETWEEN, IS NOT NULL
- ex) "WHERE column <> 'N';
LIKE '%??' (앞부분이 정해지지 않은 경우)
- ex) "WHERE column LIKE '%가%'"
스토어드 함수나 다른 연산자로 인덱스 칼럼이 변형된 후 비교된 경우
- ex) "WHERE SUBSTRING(column,1,1) = 'X'";
데이터 타입이 서로 다른 비교(인덱스 칼럼의 타입을 변환해야 비교가 가능한 경우)
- ex) "WHERE char_column = 10";
문자열 데이터 타입의 콜레이션이 다른 경우

<다중 칼럼 인덱스의 경우>

idx_test 라는 이름으로 (column_1,column_2, .. column_n)이 걸려있다고 가정하자.

작업 범위 결정 조건으로 인덱스를 사용하지 못하는 경우
- 맨 앞 칼럼인 column_1에 대한 조건이 없는 경우
- 맨 앞 칼럼인 column_1의 비교 조건이 위에 나와있는 단일 인덱스의 사용 불가 경우 중 하나에 해당하는 경우
작업 범위 결정 조건으로 인덱스를 사용할 수 있는 경우(i < 2 < N, 아래 2가지 조건을 모두 만족하는 경우 1~i까지는 작업 범위 결정 조건으로 사용되고, i+1부터 n까지는 체크 조건으로 사용된다. )
1. column_1 ~ column_(i-1) 까지 동등 비교(" = " or "IN")
2. column_i 칼럼에 대해서는 아래 중 하나의 연산자로 비교
  1. 동등 비교 ("=" or "IN")
  2. 크기 비교 (">" or "<")
  3. 좌측이 고정된 LIKE 비교 (LIKE '가나다%')

저작자표시 (새창열림)

'Database' 카테고리의 다른 글

Lock - 1. Lock의 개념과 종류 (0)	2023.08.27