[데이터베이스] 랜덤 I/O와 순차 I/O 그리고 인덱스

랜덤 I/O와 순차 I/O

• 랜덤 I/O: 데이터가 디스크의 물리적인 위치에서 분산되어 있거나 데이터 접근이 예측 할 수 없는 순서로 이루어지는 입출력 작업을 말합니다.
• 순차 I/O: 데이터가 디스크의 연속된 물리적인 위치에 저장되어 있거나, 순서대로 접근되는 입출력 작업을 말합니다.

HDD와 SSD의 랜덤I/O와 순차 I/O

• 순차 I/O: 데이터가 연속적으로 저장되어 있기 때문에 HDD와 SSD에서 모두 순차 I/O의 성능 차이가 크지 않습니다.
• 랜덤 I/O: HDD의 경우, 분산된 데이터 하나씩 읽을 때마다 디스크 헤드를 물리적으로 움직여야 하므로 성능이 떨어집니다. SSD는 기계적인 움직임이 없기 때문에 랜덤 I/O에서 상대적으로 높은 성능을 유지할 수 있습니다.

인덱스

HDD나 SSD 저장장치는 순차 I/O가 랜덤 I/O보다 빠르다. I/O 성능을 향상 시키려면 랜덤 I/O 횟수를 줄이는 방식을 사용할 수 있다. 이때 인덱스를 이용한 쿼리 튜닝을 해 랜덤 I/O를 줄이는 방식으로 성능을 향상 시킬수 있습니다.

• 인덱스: 데이터베이스 테이블에서 특정 열(column)의 값을 빠르게 검색할 수 있도록 도와주는 데이터 구조입니다. 책의 목차처럼 데이터의 위치를 신속하게 찾아주는 역할을 합니다.

인덱스의 종류

• B-Tree 인덱스
• B+-Tree 인덱스
• Hash 인덱스
• 클러스터링 인텍스

커버링 인덱스

조회하는데 필요한 모든 열들을 포함하는 인덱스를 말합니다. 데이터에 접근할때 실제 데이터에 접근하지 않고 인덱스의 데이터에 접근하기 때문에 속도가 빠릅니다.

다중 컬럼 인덱스

컬럼 두개 이상을 묶어 만든 인덱스를 말합니다. 이를 통해 두개 이상의 컬럼을 조회할때 다중 컬럼인덱스를 활용해 데이터 접근 속도를 높일수 있습니다.

B-Tree 인덱스

Balance Tree 구조의 m-원 탐색트리 입니다. 인덱스의 키 값이 정렬된 채로 유지되어 조회가 빠릅니다. 하지만 삽입이나 삭제, 수정 연산이 발생하면 트리의 Depth를 맞추기 위한 연산이 추가적으로 발생합니다. 인덱스와 레코드 주소가 키 값 형태로 리프노드와 internal 노드에 저장되어 있습니다.

B+-Tree 인덱스

B-Tree의 변형된 트리 자료구조인 B+트리를 사용해 인덱스를 구성하는 방식입니다. B-Tree는 리프노드와 Internal 노드에 인덱스와 레코드 주소가 저장되어 있지만 B+트리 같은 경우 리프노드에만 레코드 주소가 저장됩니다. 이를 통해 노드에 더 많은 인덱스를 추가해 트리의 높이를 줄여 cache hit를 높일 수 있습니다. 또한 리프노드의 맨 끝은 다음 노드를 연결하는 연결 리스트로 되어있어 데이터를 선형적으로 탐색하기 용이합니다.

Hash 인덱스

Hash인덱스는 키값을 해시함수를 통해 해싱하여 데이터를 찾아가는 자료구조입니다. 데이터 접근하는데 해싱만 하면 되기 때문에 접근 속도가 매우 빠릅니다. 하지만 정렬된 저장구조가 아니기 때문에 범위 기반 탐색에는 적합하지 않습니다.

클러스터링 인덱스

클러스터링 인덱스는 테이블의 프라이머리 키 값이 비슷한 레코드끼리 묶어서 저장하는 것을 말합니다. MySQL에서는 InnoDB에서 스토리지 엔진에서만 지원합니다. 프라이머리 키를 기준으로 물리적인 저장 위치가 바뀌기 때문에 프라이머리 키 값에 대한 의존도가 큽니다.

쿼리 실행 계획

쿼리 실행 계획(Query Execution Plan)은 데이터베이스가 특정 SQL 쿼리를 실행할 때 취하는 단계별 절차를 나타낸 것입니다. 이를 통해 쿼리의 성능을 분석하고 최적화할 수 있습니다.

주요 구성 요소

1. 순차 스캔(Sequential Scan):
   • 테이블의 모든 행을 하나씩 읽어오는 방법
   • 인덱스를 사용하지 않을 때 발생
2. 인덱스 스캔(Index Scan):
   • 인덱스를 이용해 데이터를 검색하는 방법
   • B-Tree, Hash 인덱스 등이 사용됨
3. 조인 방법:
   • Nested Loop Join, Merge Join, Hash Join 등
   • 테이블 간의 데이터를 결합하는 방식
4. 정렬(Sort):
   • ORDER BY 절에 의해 데이터 정렬이 필요한 경우
5. 필터링(Filter):
   • WHERE 절 조건에 맞는 데이터만 선택

실행 계획을 확인해보려 `EXPLAIN` 명령어를 사용해 확인할 수 있습니다.

EXPLAIN SELECT * FROM employees WHERE last_name = 'Kim';

힌트

힌트(Hints)는 데이터베이스 쿼리 최적화에 사용자가 의도적으로 특정 실행 계획을 선택하도록 지시하는 기능입니다. 이를 통해 데이터베이스의 쿼리 옵티마이저가 선택하는 기본 실행 계획을 변경할 수 있습니다.

주요 힌트 종류

1. 인덱스 힌트(Index Hint):

• 특정 인덱스를 사용하도록 지시

SELECT * FROM employees USE INDEX (idx_last_name) WHERE last_name = 'Kim';

2. 옵티마이저 힌트(Optimizer Hint):

• 전체 쿼리의 최적화 방식을 제어

SELECT * FROM employees WITH (FORCESEEK) WHERE last_name = 'Kim';