개요
인덱스는 데이터베이스 성능 최적화의 출발점임을 부정하기 어려움 MySQL은 스토리지 엔진에 따라 인덱싱과 검색 방식이 달라질 수 있어 기본 개념을 정확히 이해하는 것이 쿼리 튜닝의 기반이 됨 또한 디스크 접근 특성인 랜덤 I/O와 순차 I/O의 차이는 실무 성능에 직접적인 영향을 주므로 반드시 짚고 가야 함
디스크 I/O가 성능을 좌우하는 이유
CPU와 메모리 성능은 가파르게 개선됐지만 디스크 장치는 물리적 한계로 개선 속도가 상대적으로 느림 DB 성능 튜닝의 많은 부분이 디스크 I/O를 얼마나 줄이느냐로 귀결됨
I/O는 컴퓨터와 주변 장치 간 데이터 전송 전반을 의미하며 파일 시스템 접근, 디스크 읽기·쓰기, 컨트롤러 캐시 플러시 등 시스템 전반의 입출력 행위를 포함함
저장 매체 개요
서버 환경에서 흔히 쓰는 저장 매체 구분
- 내장 디스크 내부 장착형 디스크 드라이브
- DAS Direct Attached Storage, 본체에 직접 연결하는 디스크 확장 장치
- NAS Network Attached Storage, 네트워크를 통해 공유되는 파일 스토리지
- SAN Storage Area Network, 전용 네트워크로 연결되는 블록 스토리지
내장 디스크는 서버 섀시 공간 제약으로 장착 수량과 용량 확장이 제한됨 DAS는 본체에 직결되어 내장 디스크와 유사한 방식으로 동작하며 대용량 확장에 유리하나 하나의 호스트에 귀속되므로 다중 서버 공유 불가 NAS는 TCP/IP 기반 공유 스토리지로 여러 서버에서 동시에 접근 가능하나 직접 연결 대비 지연과 처리량 손실 발생 SAN은 광케이블 등 전용 인터커넥트를 사용해 대용량과 높은 성능, 안정성을 제공하나 구축 비용이 큼
일반적인 성능과 비용 특성은 내장 디스크 → DAS → SAN 순으로 고성능·고비용 경향을 가짐 모든 매체 내부에는 하나 이상의 디스크 드라이브가 존재한다는 공통점이 있음
HDD와 SSD의 차이
전통적인 HDD는 플래터와 디스크 헤드를 움직여 데이터를 읽고 쓰는 기계식 구조를 가짐 SSD는 플래시 메모리를 사용해 기계적 움직임 없이 접근하므로 지연이 낮고 랜덤 I/O가 월등히 빠름 인터페이스는 SATA, SAS 등 기존 채널을 그대로 활용 가능하며 서버 스토리지 구성에 바로 적용 가능함
왜 HDD는 SSD보다 느린가
- HDD는 플래터로 물리적 위치를 탐색해야 해 접근 지연이 필연적으로 큼
- SSD는 셀에 전자적으로 접근하므로 위치 탐색 지연이 현저히 낮음
순차 I/O에서는 SSD가 HDD와 유사하거나 다소 빠른 수준을 보이기도 하나 데이터베이스는 작은 블록의 랜덤 접근 비중이 높아 SSD의 이점이 크게 드러남
랜덤 I/O와 순차 I/O
두 방식 모두 결국 디스크 헤드를 적절한 위치로 이동해 데이터를 읽고 쓰는 동작을 수반함 차이는 접근 패턴에 있음
- 순차 I/O 연속된 페이지를 한 번의 흐름으로 접근
- 랜덤 I/O 불연속 페이지를 여러 번 나눠 접근
예시로 3개의 페이지를 기록하는 경우
- 순차 I/O 시스템 콜 1회, 헤드 이동 1회로 연속 기록
- 랜덤 I/O 시스템 콜 3회, 헤드 이동 3회로 분산 기록
디스크 접근 시간은 헤드 이동과 회전 지연에서 대부분 결정됨 따라서 같은 양의 데이터를 다룰 때 순차 I/O가 랜덤 I/O보다 유리함 DBMS에서는 작은 단위의 랜덤 I/O가 빈번하므로 이를 완화하기 위해 그룹 커밋, 바이너리 로그 버퍼, InnoDB 로그 버퍼 등 완충 메커니즘을 제공함
쓰기 작업은 fsync와 같은 동기화가 필요하며 순차 I/O라 해도 동기화 호출이 잦으면 랜덤 I/O처럼 비효율이 발생 가능함 RAID 컨트롤러의 캐시 메모리는 빈번한 동기화 호출을 병합해 순차 쓰기 형태로 변환하는 데 기여함
실무 쿼리 튜닝에서 랜덤 I/O를 순차 I/O로 전환하는 전략은 제한적임 목표는 랜덤 I/O 자체를 줄이는 것, 즉 쿼리가 필요한 데이터만 읽도록 만드는 것에 초점
인덱스 레인지 스캔은 주로 랜덤 I/O를 유발하고 풀 테이블 스캔은 순차 I/O를 활용함 큰 테이블에서 대부분의 레코드를 읽는 경우에는 인덱스를 배제하고 풀 스캔을 유도하는 편이 더 빠를 수 있음 이는 OLAP나 대용량 통계 처리에서 종종 채택되는 전략이며 OLTP 특성의 워크로드에서는 신중한 적용이 필요함
OLTP On-Line Transaction Processing, 데이터 갱신 중심의 다중 동시 트랜잭션 처리 방식 OLAP On-Line Analytic Processing, 대화형 분석 질의와 집계 중심의 정보 처리 방식
인덱스의 기본 개념
인덱스는 컬럼 값과 해당 레코드의 저장 위치를 키-값 쌍으로 보관하는 구조를 가짐 책의 찾아보기 색인처럼 정렬된 상태로 유지되어 원하는 값을 빠르게 찾을 수 있음
자료구조 비유
- SortedList 정렬 유지, 조회 빠름, 삽입·갱신 비용 큼 → DB 인덱스에 대응
- ArrayList 저장 순서 유지, 별도 정렬 없음 → 데이터 파일에 대응
인덱스는 읽기 성능을 높이는 대신 쓰기 비용을 증가시킴 INSERT, UPDATE, DELETE 시 인덱스 구조를 유지하기 위한 추가 작업이 발생함 따라서 WHERE 절에 등장한다고 모두 인덱스로 만드는 것은 역효과를 초래할 수 있음 인덱스 추가 여부는 쓰기 성능 희생 가능한 범위와 읽기 가속 기대치를 기준으로 판단해야 함
역할 관점 분류
- Primary Key 테이블의 대표 키, 고유성과 식별성 보장
- Secondary Key 보조 인덱스, 검색 가속 목적
알고리즘 관점 분류
- B-Tree 가장 범용적, 원본 값을 정렬해 보관
- Hash 해시 값으로 인덱싱, 동등 비교에 매우 빠름, 전방 일치 등 범위 검색 부적합, 주로 메모리 기반 엔진에서 활용
- Fractal-Tree B-Tree의 쓰기 비용을 낮추기 위한 변형, 범용 사용 가능성을 지향하나 성숙도는 상대적으로 낮음
고유성 관점 분류
- Unique 같은 키 값 1개만 허용
- Non-Unique 같은 키 값 여러 개 허용
유니크 여부는 옵티마이저의 실행 계획 선택에 중요한 신호로 작용함
B-Tree 인덱스 이해
B-Tree는 DB 인덱싱에서 가장 널리 쓰이는 기본 알고리즘이며 B+-Tree 또는 B*-Tree 형태가 일반적 여기서 B는 Binary가 아닌 Balanced를 의미함
B-Tree는 컬럼의 원래 값을 기반으로 정렬 상태를 유지함 일부 엔진은 프리픽스 길이로 앞부분만 보관해 공간을 절약하기도 함 특수 요구사항을 제외하면 범용 검색에 가장 적합함
구조
- 루트 노드 트리 최상단, 탐색 시작점
- 브랜치 노드 중간 계층, 하위 노드로 경로 분기
- 리프 노드 최하단, 실제 인덱스 키와 레코드 주소 보관
인덱스 키는 정렬돼 있으나 데이터 파일의 레코드는 일반적으로 임의 순서로 저장됨 삭제 공간 재활용과 페이지 분할 등으로 물리 저장 순서는 삽입 순서와 달라질 수 있음
예외적으로 InnoDB는 클러스터형 저장 방식을 사용함
- 클러스터링 기본 프라이머리 키 순서로 레코드가 물리적으로 모여 저장됨
- 오라클의 IOT, MS SQL의 클러스터 테이블과 유사 개념
- InnoDB는 기본값으로 클러스터링 테이블을 생성함
리프 노드의 레코드 주소 의미
- 일부 시스템은 물리적 주소를 직접 가리킴
- MyISAM은 내부 레코드 ID 형태로 참조
- InnoDB는 프라이머리 키가 클러스터 키이므로 프라이머리 키 자체가 주소 역할을 수행 결과적으로 인덱스 레코드는 인덱스 키와 주소 성분의 조합으로 이뤄짐
인덱스 설계와 I/O 관점 팁
- 큰 범위를 읽는 질의는 인덱스 레인지 스캔보다 풀 스캔이 더 빠를 수 있음 순차 I/O 활용
- 선택도가 높은 조건을 선행 컬럼으로 배치해 랜덤 I/O 횟수 최소화
- 커버링 인덱스 활용으로 테이블 접근을 회피하면 랜덤 I/O를 크게 절감 가능
- 불필요한 보조 인덱스는 쓰기 비용과 공간만 증가시키므로 주기적 점검 필요
- 유니크 인덱스는 탐색을 빠르게 하고 불필요한 추가 탐색을 줄여 옵티마이저 결정에 긍정적 신호 제공
간단 예시로 보는 선택 전략
- 조건이 전체의 80% 이상을 포함하는 집계 질의 → 인덱스보다 풀 스캔이 유리할 가능성 큼
- 조건이 1% 미만으로 매우 선별적이고 필요한 컬럼이 인덱스에 모두 포함됨 → 커버링 인덱스로 랜덤 I/O 최소화
- 조인에서 드라이빙 테이블을 고선택도 조건으로 줄인 뒤 조인 실행 → 조인 전체 랜덤 I/O 감소
마무리
인덱스는 읽기 효율을 위해 쓰기 비용을 감수하는 장치라는 본질을 잊지 말아야 함 MySQL에서는 스토리지 엔진 특성과 I/O 패턴 이해가 실행 계획과 성능 결과를 가르는 핵심 요소가 됨 튜닝의 목표는 랜덤 I/O를 순차 I/O로 바꾸는 것이 아니라 불필요한 랜덤 I/O 자체를 줄이는 것임 B-Tree의 구조와 InnoDB의 클러스터링 특성을 이해하고, 선택도와 커버링 여부를 기준으로 인덱스를 설계하면 실무에서 체감 성능 향상을 얻을 수 있음