Index - 1. 데이터 저장과 인덱스

Kim, Beomgoo·2022년 9월 19일

Index 랜덤I/O 순차I/O 인덱스

데이터베이스

목록 보기

2/2

보조기억장치

DB의 실제 데이터는 HDD, SSD 같은 보조기억장치에 저장된다.
CPU나 DRAM 등에 비해 처리 속도가 매우 느리다.
DB에서 순차I/O는 그렇게 많지 않고, 대신 랜덤I/O 작업이 많으므로 HDD보다 SSD가 DBMS에는 좀 더 적합하다.

순차 I/O와 랜덤 I/O

여러 개의 Page를 디스크에 저장한다고 할 때,

HDD의 경우
- 순차 I/O - 처음 Page를 기록할 위치까지 플래터와 헤드를 움직인 뒤 순차적으로 Page를 기록한다.
- 랜덤 I/O - 각 Page를 기록할 때마다 매번 기록할 위치까지 플래터와 헤드를 움직여야 한다.
  따라서 순차 I/O가 랜덤 I/O보다 훨씬 속도가 빠르다.
SSD의 경우도 순차 I/O가 랜덤 I/O보다 속도가 빠르다.
따라서 DB에서 쿼리를 튜닝하는 것은 랜덤 I/O를 줄이는 것이 목적이다.

DB에서 데이터를 조회하는 여러 방법 중에 Index Range Scan과 Table Full Scan이 있다. Index Range Scan은 Index에 기록된 데이터의 저장 위치를 랜덤하게 액세스하는 것이고, Table Full Scan은 연속적으로 기록된 데이터들을 순차적으로 읽어들이는 것이다.

따라서 DBMS는 테이블의 규모, 인덱스의 구성, 조회할 레코드의 규모 등을 종합적으로 고려하여 어떤 방식으로 쿼리를 실행할지 실행 계획을 결정한다.

Index

데이터베이스의 테이블에 있는 모든 데이터를 다 뒤져가며 원하는 결과를 골라 가져온다면 시간이 오래 걸릴 수 있다. 따라서 두꺼운 전공 서적을 찾아볼 때, 원하는 키워드의 내용이 몇 페이지에 있는지 색인(Index)을 뒤져보는 것처럼, 데이터베이스에서도 인덱스를 이용한다.

인덱스는 칼럼 또는 칼럼들의 값과, 해당 레코드가 저장된 주소를 key-value의 쌍으로 만들어 사용한다.

책의 색인이 뒤죽박죽 무질서하게 섞여 있다면 원하는 키워드를 찾아보기 힘들 것이다. 그래서 색인은 A,B,C 그리고 가,나,다의 사전 순으로 정렬하여 만들어 둔다. 데이터베이스의 인덱스도 마찬가지로 인덱스 자체를 검색하기 쉽게 하기 위해 인덱스 칼럼(key)의 값에 따라 정렬하여 저장한다.

레코드를 저장하거나 수정하면, 인덱스에 새로 정렬하여 저장하거나, 정렬 상태를 수정해야 하므로, INSERT, UPDATE, DELETE 성능을 저하시킬 수 있다. 하지만 데이터 레코드를 조회하는 SELECT 시에는 일반적으로 성능이 좋아진다. 따라서 무작정 인덱스를 많이 만들어 두는 것이 오히려 쿼리 성능을 저하시킬 수도 있으므로 적절히 조율하여 인덱스를 설계해야 한다.

인덱스의 장점

인덱스의 정렬되어 있다는 특징이 ‘조건’을 이용한 검색에서 장점이 된다.

WHERE절의 조건에 사용될 때

일반적으로 데이터 레코드를 저장할 때는 INSERT 순서대로 저장이 되지만, 삭제 등으로 인해 생긴 빈 공간을 우선적으로 사용하므로 데이터 레코드는 정해진 순서가 없이 뒤죽박죽 저장된 상태이다.

만약 WHERE 절에 특정 조건을 지정하여 검색을 하는데, 인덱스가 없다면 레코드를 처음부터 끝까지 다 읽으며 검색 조건에 맞는지 확인을 해야 한다. 하지만 검색 조건에 쓰인 칼럼으로 인덱스가 만들어져 있다면, 인덱스는 항상 정렬되어 있기 때문에 빠르게 검색을 처리할 수 있다.
ORDER BY의 조건에 사용될 때

ORDER BY는 조건에 쓰인 컬럼에 따라 오름차순 또는 내림차순 정렬을 하는 쿼리이다. 만약 인덱스가 없다면, 메모리 또는 디스크 I/O 등을 동원하여 추가적인 정렬 작업이 필요하다.

하지만 인덱스가 있다면 정렬된 상태 또는 역순 그대로 데이터들을 가져오기만 하면 된다.

인덱스의 단점

DML(INSERT, UPDATE, DELETE)에 취약하다.

인덱스는 항상 정렬되어 있어야 한다는 특징은 장점이 되기도 하지만 단점이 되기도 한다.

데이터가 추가되거나 변경된다면 인덱스의 상태도 변경하고 재정렬해야 한다.
인덱스 남용은 오히려 독이 될 수 있다.

인덱스 자체가 데이터이므로, 인덱스 저장을 위한 저장공간이 추가로 필요하다.

또한 1.에서의 이유와 일맥상통하게, 인덱스가 많아지면, 테이블의 데이터를 삽입/수정/삭제 시 재정렬 해야 하는 인덱스가 많아지므로 성능이 매우 떨어질 수 있다.
SELECT시 인덱스 사용이 더 느릴 수 있다.

일반적으로 읽으려는 레코드가 테이블 전체 레코드 대비 약 20% 정도가 넘어가면, DBMS는 인덱스 스캔이 아닌 Table Full Scan을 이용하도록 실행 계획을 생성한다.

중간에 인덱스 페이지를 거쳐야 하는 오버헤드가 발생하며, 인덱스를 통해 레코드들에 접근하려면 랜덤 I/O가 많이 발생하여 Table Full Scan을 하는 것보다 더 느릴 수 있기 때문이다.