Real MySQL 8장 인덱스

민이(MInE) 2025. 1. 20.

데이터베이스의 디스크 읽기 방식과 인덱스 기초

데이터베이스 성능에서 가장 중요한 것은 디스크 I/O를 어떻게 최소화하느냐입니다.

1. 디스크 저장 매체의 특성

HDD vs SSD

하드 디스크 드라이브(HDD)

기계식 저장 장치
데이터베이스 서버의 주요 병목 지점
디스크 헤더의 물리적 이동 필요
랜덤 I/O 성능이 상대적으로 낮음

솔리드 스테이트 드라이브(SSD)

전자식 저장 장치
HDD와 동일한 인터페이스 지원
HDD 대비 약 1000배 빠른 속도
랜덤 I/O 성능이 월등히 우수
초당 트랜잭션 처리량: HDD(60개) vs SSD(436개)

성능 비교

구분       HDD          SSD
순차 I/O   우수         매우 우수
랜덤 I/O   매우 느림    우수
내구성     우수         제한적
가격       저렴         고가

2. 랜덤 I/O vs 순차 I/O

특징 비교

순차 I/O

3개 페이지 기록 시 1번의 시스템 콜
디스크 헤더 이동 최소화
큰 데이터 처리에 효율적
높은 데이터 처리량

랜덤 I/O

3개 페이지 기록 시 3번의 시스템 콜
잦은 디스크 헤더 이동
작은 데이터의 잦은 처리
상대적으로 낮은 처리량

성능 최적화

MySQL의 내장 최적화 기능
- 그룹 커밋
- 바이너리 로그 버퍼
- InnoDB 로그 버퍼

중요: SSD에서도 랜덤 I/O는 순차 I/O보다 처리량이 낮습니다.

3. 인덱스의 기본 개념

인덱스란?

책의 찾아보기(색인)에 비유할 수 있는 데이터베이스의 핵심 기능

책 내용 = 데이터 파일
찾아보기 = 인덱스
페이지 번호 = 레코드 주소

자료구조 비교

SortedList (인덱스와 유사)

항상 정렬된 상태 유지
데이터 저장은 느림
데이터 검색은 빠름
INSERT/UPDATE/DELETE 성능 ↓
SELECT 성능 ↑

ArrayList (데이터 파일과 유사)

저장 순서 그대로 유지
데이터 저장은 빠름
데이터 검색은 느림

인덱스 분류

역할별 분류

프라이머리 키 (Primary Key)
- 레코드 식별자
- NULL 값 불허
- 중복 값 불허
- 테이블당 1개만 존재
세컨더리 인덱스 (Secondary Index)
- 프라이머리 키 외의 모든 인덥스
- 유니크 인덱스 포함
- 중복 값 허용 가능

알고리즘별 분류

B-Tree 인덱스
Hash 인덱스

중복 허용 여부별 분류

유니크 인덱스
유니크하지 않은 인덱스

B-Tree 인덱스란?

B-Tree는 데이터베이스의 가장 오래되고 일반적인 인덱스 알고리즘입니다. 여기서 'B'는 많은 분들이 오해하시는 Binary(이진)가 아닌 Balanced(균형)를 의미합니다. B-Tree의 변형된 형태로는 B+Tree, B*Tree 등이 있으며, 이들도 실제 데이터베이스 시스템에서 널리 사용되고 있습니다.

B-Tree의 구조와 특성

기본 구조

루트 노드: 트리의 최상위에 위치한 노드
브랜치 노드: 루트와 리프 노드 사이에 위치한 중간 노드
리프 노드: 실제 데이터 레코드의 주소값을 가지고 있는 최하위 노드

주요 특성

모든 키 값은 정렬된 상태를 유지합니다.
리프 노드는 실제 데이터를 찾아가기 위한 주소값을 보유합니다.
데이터 파일의 레코드는 정렬되지 않은 상태입니다 (InnoDB 제외).
InnoDB의 경우 데이터가 기본적으로 PK 기준으로 정렬되어 저장됩니다.

B-Tree 인덱스의 작동 방식

1. 인덱스 키 추가

새로운 키 값이 들어오면 적절한 위치를 검색하여 리프 노드에 저장
리프 노드가 가득 찬 경우 노드 분리 작업 발생
일반적인 레코드 작업 대비 1.5배의 오버헤드 예상
InnoDB의 경우 체인지 버퍼를 통해 작업을 지연시켜 최적화 가능
- 단, PK, 유니크 인덱스, 내림차순 인덱스는 즉시 처리 필요

2. 인덱스 키 삭제

해당 키가 있는 리프 노드를 찾아 삭제 마크만 진행
삭제된 공간은 재활용 가능
체인지 버퍼를 통한 최적화 가능

3. 인덱스 키 변경

실제로는 삭제 후 새로운 키 추가의 형태로 동작
체인지 버퍼를 통한 최적화 가능

4. 인덱스 키 검색

루트 노드부터 시작하여 리프 노드까지 트리 탐색 수행
SELECT 뿐만 아니라 UPDATE, DELETE 작업 시에도 사용
검색 조건의 제약:
- 완전 일치 검색 가능 (100% 일치)
- 전방 일치 검색 가능 (glen%)
- 후방 일치 검색 불가 (%glen)
- 키 값 변형 후 비교 불가

InnoDB에서의 특별한 고려사항

InnoDB 스토리지 엔진에서 인덱스는 단순한 검색 성능 향상 이상의 의미를 가집니다

레코드 잠금이 인덱스를 통해 이루어집니다.
넥스트 키 락이 인덱스를 기반으로 동작합니다.
인덱스가 없으면 불필요한 테이블 잠금이 발생할 수 있습니다.

따라서 InnoDB를 사용할 때는 인덱스 설계가 더욱 중요하며, 전반적인 데이터베이스 성능에 큰 영향을 미칩니다.

B-Tree 인덱스 성능에 영향을 미치는 핵심 요소들

B-Tree 인덱스의 성능은 여러 요소들에 의해 영향을 받습니다. 이러한 요소들을 이해하고 적절히 관리하는 것은 데이터베이스 최적화에 매우 중요합니다.

1. 인덱스 키 값의 크기

페이지 단위 저장 구조

InnoDB 스토리지 엔진은 데이터를 '페이지' 또는 '블록'이라는 기본 단위로 저장
모든 읽기/쓰기 작업의 최소 단위
버퍼 풀의 데이터 버퍼링 기본 단위
인덱스의 모든 노드(루트, 브랜치, 리프)가 페이지 단위로 관리

키 크기가 성능에 미치는 영향

예를 들어, 16KB(16,384바이트) 크기의 페이지에서

인덱스 키 16바이트 + 자식 노드 주소 12바이트 = 약 585개 저장 가능
인덱스 키 32바이트 + 자식 노드 주소 12바이트 = 약 372개 저장 가능

성능 영향

디스크 읽기 횟수 증가
- 더 큰 키 값 = 더 많은 페이지 접근 필요
- 예: 500개 레코드 읽기
  - 16바이트 키: 1번의 페이지 읽기
  - 32바이트 키: 2번의 페이지 읽기
버퍼 풀 효율성 감소
- 큰 키 값 = 버퍼 풀에 캐시할 수 있는 데이터 감소
- 캐시 효율 저하로 인한 전반적인 성능 감소

2. B-Tree 깊이

특징

직접적인 제어 방법은 없음
시간 복잡도: O(logN)
일반적으로 5단계 이상 깊어지는 경우는 드묾

최적화 방안

인덱스 키의 크기를 최소화하여 간접적으로 깊이 영향 줄이기
불필요한 컬럼을 인덱스에서 제거

3. 선택도(카디널리티)

개념

카디널리티: 전체 인덱스 키 값 중 유니크한 값의 수
예: 전체 100개 중 유니크한 값이 10개 = 카디널리티 10

성능 영향

높은 카디널리티 = 더 나은 검색 성능
낮은 카디널리티 = 검색 성능 저하

복합 인덱스 설계 시 고려사항

기본적으로 카디널리티가 높은 순서대로 설정
예외: 첫 번째 컬럼이 단독으로 자주 사용되는 경우, 카디널리티를 무시하고 해당 컬럼을 첫 번째로 설정 가능

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

인덱스 사용 비용

인덱스를 통한 레코드 읽기는 직접 읽기보다 4~5배 높은 비용
전체 레코드 중 20~25% 이상을 읽어야 한다면 풀 테이블 스캔이 더 효율적

최적화 판단 예시

100건의 데이터가 있는 경우

50건 필요 시: 풀 테이블 스캔이 더 효율적
10건 필요 시: 인덱스 사용이 더 효율적

B-Tree 인덱스의 데이터 읽기

MySQL에서 B-Tree 인덱스를 통한 데이터 읽기는 여러 가지 방식으로 이루어집니다. 각각의 방식을 이해하는 것은 쿼리 최적화와 성능 향상에 매우 중요합니다.

1. 인덱스 레인지 스캔 (Index Range Scan)

개요

가장 대표적이고 일반적인 인덱스 접근 방식
검색해야 할 인덱스의 범위가 결정됐을 때 사용
MySQL의 실행 계획에서 const, ref, range 접근 방식을 포함

동작 방식

루트 노드에서 시작하여 브랜치 노드를 거쳐 리프 노드까지 탐색
시작 지점을 찾은 후 리프 노드를 순차적으로 스캔
리프 노드의 끝에 도달하면 노드 간 링크로 다음 리프 노드로 이동
스캔 중지 조건에 도달하면 결과 반환

성능 특성

인덱스 조회 후 실제 데이터 파일 접근 시 랜덤 I/O 발생
버퍼 풀이 있어 실제 랜덤 I/O는 감소될 수 있음
커버링 인덱스의 경우 데이터 파일 접근이 불필요

2. 인덱스 풀 스캔 (Index Full Scan)

개요

인덱스의 처음부터 끝까지 모두 읽는 방식
조건절의 컬럼이 인덱스의 첫 번째 컬럼이 아닐 때 사용

특징

풀 테이블 스캔보다는 적은 디스크 I/O 사용
인덱스 레인지 스캔보다는 비효율적
데이터 레코드까지 읽어야 하는 경우에는 사용되지 않음

3. 루스 인덱스 스캔 (Loose Index Scan)

개요

오라클의 인덱스 스킵 스캔과 유사한 방식
MySQL 8.0부터 기능이 크게 개선됨
인덱스를 듬성듬성하게 읽는 방식

사용 예시

SELECT member_id, MIN(money)
FROM purchase
WHERE member_id BETWEEN 5 AND 10
GROUP BY member_id

위와 같은 쿼리에서 (member_id, money) 복합 인덱스가 있을 경우
각 member_id별로 첫 번째 레코드만 읽고 나머지는 스킵

특징

인덱스 레인지 스캔과 인덱스 풀 스캔은 타이트 인덱스 스캔으로 분류
GROUP BY나 집계 함수와 함께 자주 사용

4. 인덱스 스킵 스캔 (Index Skip Scan)

개요

MySQL 8.0부터 도입된 최적화 기능
복합 인덱스에서 선행 컬럼이 조건절에 없어도 인덱스를 활용 가능

사용 조건

선행 컬럼의 유니크한 값이 적은 경우
커버링 인덱스인 경우

예시

복합 인덱스 (gender, birth_date)가 있을 때

WHERE birth_date >= '2077-06-30'

B-Tree의 다중 컬럼 인덱스와 정렬 방식

1. 다중 컬럼 인덱스의 이해

개념과 특징

다중 컬럼 인덱스(복합 컬럼 인덱스)는 둘 이상의 컬럼을 포함하는 인덱스
컬럼 순서에 따라 정렬 우선순위가 결정됨
첫 번째 컬럼 → 두 번째 컬럼 → ... 순으로 의존적 정렬 구조

중요한 특성

첫 번째 컬럼이 우선 정렬됨
두 번째 이후 컬럼들은 이전 컬럼에 의존해 정렬
첫 번째 컬럼 없이 후속 컬럼만으로는 인덱스 활용 불가

설계 시 고려사항

컬럼 순서가 검색 성능에 직접적 영향
자주 사용되는 검색 조건을 우선적으로 고려
선택도(카디널리티)도 중요한 고려 요소

2. B-Tree 인덱스의 정렬

기본 정렬 특성

인덱스 생성 시 각 컬럼별로 정렬 방향 지정 가능
MySQL 8.0부터 복합 인덱스에서 컬럼별 정렬 방향 혼합 가능
- 예: 첫 번째 컬럼 ASC, 두 번째 컬럼 DESC 등

MySQL 버전별 특징

MySQL 5.7 이전: 모든 컬럼이 동일한 정렬 방향만 가능
MySQL 8.0 이후: 컬럼별 다른 정렬 방향 지정 가능

3. 인덱스 스캔 방향

스캔 방향의 유연성

인덱스의 물리적 정렬 방향과 무관하게 양방향 스캔 가능
옵티마이저가 실행 시점에 최적의 스캔 방향 결정

예시

SELECT * FROM table ORDER BY name DESC LIMIT 1;

오름차순 인덱스여도 역방향 스캔으로 최적화
전체 데이터 스캔 없이 마지막 레코드만 읽음

4. 내림차순 인덱스 활용

내림차순 인덱스의 필요성

복합 인덱스에서 혼합된 정렬 조건 처리
대부분의 쿼리가 역순 정렬을 요구하는 경우

성능상의 이점

InnoDB 스토리지 엔진의 Forward Index Scan 최적화
페이지 내 인덱스 레코드의 단방향 연결 구조 활용

선택 기준

단일 컬럼 인덱스의 경우
- 대부분 역순 정렬이 필요하다면 내림차순 인덱스 권장
- 정렬 방향이 혼재된 경우 오름차순 인덱스로 충분
복합 인덱스의 경우
- 정렬 조건이 혼합된 경우 각 컬럼의 정렬 방향을 명시적으로 지정

B-Tree 인덱스의 가용성과 효율성

1. B-Tree 인덱스의 조건별 효율성

비교 조건의 종류와 효율성

작업 범위 결정 조건 vs 필터링 조건

SELECT * FROM dept_emp
WHERE dept_no = 'd002' AND emp_no >= 10114

인덱스 (dept_no, emp_no)의 경우
- 작업 범위 결정 조건으로 동작
- dept_no='d002' 찾고 emp_no >= 10114부터 순차적 스캔
- 매우 효율적
인덱스 (emp_no, dept_no)의 경우
- 필터링 조건으로 동작
- 각 레코드마다 dept_no 조건 체크 필요
- 상대적으로 비효율적

B-Tree 인덱스의 가용성

인덱스 사용이 불가능한 경우

왼쪽 값 기준 정렬 특성으로 인한 제약
-- 인덱스 (name) WHERE name LIKE '%glen' -- 사용 불가 -- 인덱스 (dept_no, emp_no) WHERE emp_no >= 4885 -- 첫 컬럼 없이 사용 불가
작업 범위 결정 조건으로 사용 불가능한 경우
- NOT-EQUAL 비교
- WHERE column <> 'N' WHERE column NOT IN (10, 11, 12) WHERE column IS NOT NULL
- 후방 일치 LIKE
- WHERE column LIKE '%glen' WHERE column LIKE '_glen' WHERE column LIKE '%glen%'
- 함수 기반 비교
- WHERE SUBSTRING(column, 1, 1) = 'X' WHERE DAYOFMONTH(column) = 1
- 데이터 타입 불일치
- WHERE char_column = 10

MySQL의 특별한 경우

NULL 값도 인덱스에 저장
WHERE column IS NULL 조건도 인덱스 사용 가능

다중 컬럼 인덱스의 가용성

인덱스 구성: (col_1, col_2, col_3, ..., col_n)

작업 범위 결정 조건으로 사용 가능한 경우

동등 비교 형태
WHERE col_1 = 'A' WHERE col_1 IN ('A', 'B')
두 번째 이후 컬럼에 대한 조건
- 동등 비교 (=, IN)
- 크다 작다 비교 (>, <)
- 좌측 일치 패턴 (LIKE 'glen%')

사용 불가능한 경우

첫 번째 컬럼 조건이 없는 경우
첫 번째 컬럼이 앞서 설명한 사용 불가 조건인 경우

2. R-Tree 인덱스

개요

2차원 데이터 인덱싱을 위한 공간 인덱스
B-Tree와 유사하나 2차원 벡터 값 처리
위치 기반 서비스 구현에 적합

MySQL 공간 확장 기능

공간 데이터 타입
- POINT
- LINE
- POLYGON
- GEOMETRY (슈퍼 타입)
MBR (Minimum Bounding Rectangle) 개념
- 도형을 감싸는 최소 크기의 사각형
- 이 사각형들의 포함 관계로 인덱스 구성

R-Tree 인덱스의 활용

주요 용도

GPS 좌표 (위도/경도) 저장
CAD/CAM 소프트웨어
회로 디자인

인덱스 사용 가능한 함수

ST_Contains()
ST_Within()
포함 관계를 비교하는 함수에서만 인덱스 활용 가능

효율적인 거리 기반 검색

-- 비효율적인 방식
SELECT * FROM places
WHERE ST_Distance(point, target_point) <= 10;

-- 효율적인 방식
SELECT * FROM places
WHERE ST_Contains(
  ST_Buffer(target_point, 10),
  point
);

저작자표시 비영리 변경금지