[데이터베이스] 인덱스란? (실습 포함)

cs-study에서 스터디를 진행하고 있습니다.

디스크 읽기 방식

하드 디스크 드라이브 (HDD)와 솔리드 스테이트 드라이브 (SSD)

컴퓨터 대부분의 주요 장치 (CPU, RAM 등)은 대부분 디지털 방식이지만, HDD는 아날로그 방식의 장치이다. 디지털 방식으로 동작하는 주요 장치 간의 데이터 소통은 굉장히 빠른데, HDD만 그 페이스에 맞추지 못하는 상황이 발생하게 된다. 그래서 DB에서 디스크 장치는 항상 병목이 되어 느려지는 현상이 빈번하다. 이러한 기계식 디스크 드라이브를 대체하기 위해 나온 것이 SSD (Solid State Drive)이다.

 

 

순차 I/O의 경우 SSD와 HDD가 비슷한 성능을 보일 수 있지만, 랜덤 I/O의 경우 SSD가 훨씬 성능이 좋다. 데이터베이스 서버에서 순차 I/O 작업은 그다지 비중이 크지 않고 랜덤 I/O를 통해 작은 데이터를 읽고 쓰는 작업이 많아서 SSD가 일반적으로 DBMS에 적합하다고 볼 수 있다. (특히 웹 서비스 환경)

 

랜덤 I/O와 순차 I/O

기본적으로 디스크 드라이브는 데이터를 읽을 때 원판 플래터를 회전하며 데이터를 찾는다.

순차 I/O는 논리적/물리적 순서를 따라 차례대로 데이터를 읽어 나가는 방식이고, 랜덤 I/O는 논리적/물리적 순서를 따르지 않고 한 건의 데이터를 읽기 위해 한 블록씩 접근하는 방식이다.

 

 

위의 예제에서 5번만 순차 I/O고 나머지는 랜덤 I/O에 해당한다. 5번은 논리적/물리적으로 한 방향으로 연속하게 데이터를 읽어 들이지만, 나머지는 연속하지 않은 방향으로 한 블록씩 데이터를 읽어 들이고 있다.

이제, 구체적인 동작 과정을 살펴 보자.

 

 

순차 I/O는 3개의 페이지를 디스크에 기록하기 위해 한 번의 시스템 콜을 요청했지만, 랜덤 I/O는 3개의 페이지를 디스크에 기록하기 위해 세 번의 시스템 콜을 요청했다. 즉 순차 I/O는 디스크의 헤드를 1번 움직였지만, 랜덤 I/O는 디스크의 헤드를 3번 움직이게 된다.

디스크에 데이터를 읽고 쓰는데 걸리는 시간은 디스크 헤더를 움직여서 읽고 쓸 위치로 옮기는 단계에서 결정된다. 즉, 디스크의 성능은 디스크 헤더의 위치 이동 없이 얼마나 많은 데이터를 한 번에 기록하느냐에 의해 결정된다.

일반적으로 쿼리 튜닝은 쿼리를 처리하는 데 꼭 필요한 데이터만 읽도록 쿼리를 개선하여 랜덤 I/O 작업을 줄이는 것이 목적이다. 순차 I/O로 바꿔서 실행할 방법이 적기 때문이다.

참고로 인덱스 레인지 스캔은 랜덤 I/O를 사용하며, 풀 테이블 스캔은 순차 I/O를 사용한다. 그래서 매우 큰 테이블의 레코드를 읽을 때는 풀 테이블 스캔을 사용한다. (ex. 데이터 웨어 하우스, 통계 작업)

 

인덱스란?

인덱스는 DBMS의 저장 성능을 희생하고 검색 성능을 높이기 위해 만들어진 자료 구조이다.

 

비유

아주 두꺼운 책 한 권과 DBMS를 떠올려 보자.

 

• 책의 맨 처음이나 끝에 존재하는 색인 = DBMS의 인덱스
• 책의 내용 = DBMS의 데이터 파일
• 책의 색인을 통해 알 수 있는 페이지 번호 = DBMS의 데이터 파일에 저장된 레코드의 주소
• 책의 색인은 사전 순으로 정렬 = DBMS의 인덱스도 일정 기준으로 정렬 가능

 

데이터 파일이란?

DB의 데이터를 담는 파일로, 모든 DB는 하나 이상의 데이터 파일을 갖는다.

 

인덱스의 필요성을 이해할 수 있는 예시

어떤 Java 덕후가 온갖 책이 들어 있는 데이터베이스에서 Java 책 정보를 찾고 싶어한다. 인덱스가 없다면, 처음부터 끝까지 full scan을 수행하면서 Java 책을 찾아야 하는데, 이것은 비효율적이다. 그래서 다음과 같이 Row에서 category와 row_id만 빼 와서 인덱스를 만들고, category를 기준으로 정렬하였다.

 

 

이렇게 세팅을 하면, Java 책이 처음으로 등장하는 시점에서 더 이상 나오지 않는 시점까지만 탐색을 수행하면 된다. 전체 Java 책 정보를 알고 싶을 때는 여기서 탐색한 row_id를 가지고 실제 쿼리를 날리면 된다. 심지어 탐색 과정에서 B-Tree 자료 구조의 이점 덕분에 탐색 속도도 빠르다.

 

SELECT    name, location
FROM    book_store
WHERE    id IN (1, 4, 4222, 9999)

 

row_id는 사실 데이터를 삽입할 때 DB 내부에서 자동으로 생성하는 값으로, 해당 row의 고유한 주소 값을 가리킨다. 그래서 DB에서 가장 빠르게 데이터를 찾아내는 방법이라고 할 수 있다.

 

인덱스의 관리

DBMS는 인덱스를 항상 최신의 정렬된 상태로 유지해야 원하는 값을 빠르게 탐색할 수 있다. 따라서 인덱스가 적용된 컬럼에 INSERT, UPDATE, DELETE가 수행된다면 각각 다음과 같은 연산을 추가로 해 주어야 하며 그에 따른 오버헤드가 발생한다.

 

• INSERT: 새로운 데이터에 대한 인덱스를 추가함
• DELETE: 삭제하는 데이터의 인덱스를 사용하지 않는다는 작업을 진행함
• UPDATE: 기존의 인덱스를 사용하지 않음 처리하고, 갱신된 데이터에 대해 인덱스를 추가함

 

인덱스의 장점과 단점

장점

• 테이블을 조회하는 속도와 그에 따른 성능을 향상할 수 있다.
• 전반적인 시스템의 부하를 줄일 수 있다.

단점

• 인덱스를 관리하기 위해 DB의 약 10%에 해당하는 저장 공간이 필요하다.
• 인덱스를 관리하기 위해 추가 작업이 필요하다.
• 인덱스를 잘못 사용할 경우 오히려 성능이 저하되는 역효과가 발생할 수 있다.
  • 만약 INSER, DELETE, UPDATE가 빈번한 속성에 인덱스를 걸게 되면 인덱스의 크기가 비대해져서 성능이 떨어진다.
  • 이러한 이유 중 하나는 DELETE와 UPDATE 연산 때문이다.
  • UPDATE와 DELETE는 기존 인덱스를 삭제하지 않고, '사용하지 않음' 처리를 한다.
  • 만약 어떤 테이블에 UPDATE와 DELETE가 빈번하게 발생한다면 실제 데이터는 10만 건이지만, 인덱스는 100만 건이 넘어가게 되어 SQL문 처리 시 비대해진 인덱스에 의해 오히려 성능이 떨어지게 되는 것이다.

 

언제 인덱스를 사용해야 할까?

• 규모가 작지 않은 테이블
• INSERT, UPDATE, DELETE가 자주 발생하지 않는 컬럼
• JOIN이나 WHERE 또는 ORDER BY에 자주 사용되는 컬럼
• 데이터의 중복도가 낮은 컬럼 (카디널리티가 높은 컬럼)
  • 예를 들어 성별이라는 컬럼이 있다고 하자. (성별은 남자와 여자만 있다고 가정)
  • 이때 성별에 인덱스를 걸어 봤자 탐색할 수 있는 값이 2개 밖에 없으므로 하나의 성별이 붙은 데이터를 검색하는 데 재수 없으면 Full Scan을 할 수도 있다.
  • 또한, 인덱스는 내부적으로 Key, Value의 트리 형태로 데이터를 저장하는데, Key가 중복되어 여러 개 존재하면 검색할 대상이 증가한다.
  • 이러한 이유로 데이터의 중복도가 낮아서 분포도가 높은 컬럼에 대해 인덱스를 사용해야 한다.

 

인덱스의 분류

역할별

• 클러스터 인덱스
  • 테이블의 기본 키 (PK)에 대해 적용되는 인덱스
• 비클러스터 인덱스
  • 테이블의 기본 키 외에 다른 컬럼에 적용된 인덱스
• 유니크 인덱스
  • 테이블의 기본 키는 아니지만, 중복을 허용하지 않는 Unique 속성이 들어간 컬럼에 적용된 인덱스
  • 참고로 클러스터 인덱스는 유니크 인덱스 + Not Null 속성을 띄고 있다.
  • 유니크 인덱스도 한꺼번에 비클러스터 인덱스로 보기도 한다. (어쨌든 기본 키는 아니므로)

 

데이터 저장 방식(알고리즘)별

• B-Tree 인덱스
• R-Tree 인덱스
• Hash 인덱스
• Fractal-Tree 인덱스
• Merge-Tree 인덱스

 

주로, B-Tree, R-Tree, Hash 인덱스를 사용한다.

 

데이터 중복 허용 여부별

• 유니크(Unique) 인덱스
• 논유니크(Non-Unique) 인덱스

 

기능별

• 전문 검색용 인덱스
• 공간 검색용 인덱스

 

인덱스 실습

GitHub - wikibook/realmysql80: 《Real MySQL 8.0》 예제 파일

 

위 저장소에서 실습 파일을 다운 받고, 테이블에 데이터를 적재한다. 이번 실습은 employees 테이블에서 인덱스를 사용하지 않고 검색했을 때와 인덱스를 사용하고 검색했을 때를 비교하고자 한다.

실습 소프트웨어는 Dbeaver를 사용하였다.

 

 

employees 테이블의 인덱스에서 ix_first_name 인덱스를 지워준다. first_name을 기준으로 조회를 해 볼 것인데, 인덱스가 없을 때를 살펴 보려고 한다. 그리고 아래 SQL을 실행한다.

 

select * from employees where first_name = "Mona" limit 0, 300000

 

 

위와 같이 소요 시간이 104ms임을 알 수 있다. Limit을 하지 않으면 디폴트가 200이라서 많은 값에 대해 조회하기 위해서 설정해 주었다.

이제 인덱스를 설정해 보자.

 

create index ix_first_name on employees (first_name)

 

그리고 다시 Mona를 조회하면 아래와 같은 결과가 나온다.

 

 

104ms에서 7ms까지 소요 시간이 대폭 감소한 것을 알 수 있다.

 

출처

• http://oniondev.egloos.com/9682288
• https://devlog-wjdrbs96.tistory.com/341
• https://joont92.github.io/db/mysql-index/
• http://www.gurubee.net/lecture/2394
• Real MySQL

 

예상 면접 질문 및 답변

인덱스란?

인덱스는 DBMS의 저장 성능을 희생하고 검색 성능을 높이기 위해 만들어진 자료 구조이다.

 

인덱스의 장점과 단점에 대해 설명하라.

장점

• 테이블을 조회하는 속도와 그에 따른 성능을 향상할 수 있다.
• 전반적인 시스템의 부하를 줄일 수 있다.

단점

• 인덱스를 관리하기 위해 DB의 약 10%에 해당하는 저장 공간이 필요하다.
• 인덱스를 관리하기 위해 추가 작업이 필요하다.
• 인덱스를 잘못 사용할 경우 오히려 성능이 저하되는 역효과가 발생할 수 있다.

 

인덱스는 언제 사용하는가?

• 규모가 작지 않은 테이블
• INSERT, UPDATE, DELETE가 자주 발생하지 않는 컬럼
• JOIN이나 WHERE 또는 ORDER BY에 자주 사용되는 컬럼
• 데이터의 중복도가 낮은 컬럼 (카디널리티가 높은 컬럼)