[Database] 인덱스(Index)란?

DBMS는 Database에서 필요에 따라 데이터를 검색한 결과를 사용자에게 반환해준다. 그렇다면 Database에는 실제로 데이터가 어떻게 저장되는 걸까?

 

Database의 물리적 저장

워드에서 작성한 문서는 파일의 형태로 저장되듯이, DBMS도 마찬가지로 DBMS만의 고유한 방식으로 데이터를 저장하고 관리한다. 

 

MySQL의 경우, 사용자가 MySQL Workbench와 같은 SQL 작업 도구 Tool을 통해 SQL문을 작성해서 실행하면, DBMS로 요청이 간다. DBMS도 운영체제에서 실행되는 응용 프로그램이기 때문에, 저장 작업도 마찬가지로 운영체제를 통해 각 장치에 명령이 내려져서 데이터를 운영체제의 파일 시스템에 종속적인 데이터베이스 파일로 저장이 된다. 

 

이때, 실제로 데이터가 저장되는 곳은 보조기억 장치이다. 보조기억장치는 여러가지가 있을 수 있는데, 일반적으로 사용되는 곳은 하드디스크이다. 

하드디스크

 

하드디스크는 원형 플레이트로 구성되어서, 트랙과 섹터로 나누어져 있다. 두번째 그림과 같이 Arm이 가리키는 공간에 저장된 값을 읽어오거나 저장하는 방식으로 작동한다. 따라서 하드디스크의 성능은 Arm이 얼마나 빠르게 디스크에서 해당 위치를 얼마나 빠르게 가리키고 찾는 지에 따라 달라진다. 이는 모터에 의해서 디스크가 회전하는 속도, Arm이 이동하는 속도, 시간 등이 작용한다.

 

이런 디스크 입출력 시간을 액세스 시간이라고 하는데, 하드디스크는 주기억장치보다 1000배 이상 느리기 때문에 DBMS가 하드디스크에서 데이터를 저장/읽어올 때 속도 문제가 발생하게 된다. 

 

따라서, DBMS는 이런 문제를 해결 하기 위해 버퍼 풀(Buffer Pool Memory)를 만들어서 자주 접근하는 데이터를 버퍼에다 따로 저장한다. 따라서 데이터를 검색하게 되면 DBMS는 버퍼 풀에 저장된 데이터를 우선 읽어들이고, 있으면 거기서 꺼내가고 없으면 디스크를 조회하는 방식으로 작업을 처리하게 된다. 

 

MySQL InnoDB 엔진 저장 방식

DBMS는 데이터베이스 별로 하나 이상의 데이터 파일을 생성해서 저장한다. DBMS 별로 고유한 파일 구조와 처리 방법을 가지고 있는데, MySQL의 저장 방식에 대해 살펴보자.

 

MySQL은 저장장치 엔진을 InnoDB 엔진을 기본적으로 사용한다. 

사용하는 데이터베이스 별로 별도의 파일을 생성하는데, 다음과 같다. 

 

파일	설명
데이터 파일 (ibdata)	- 사용자 데이터와 개체를 저장 - 테이블과 인덱스가 저장됨 - 확장자는 *.ibd
폼파일(frm file)	- 테이블에 대한 각종 정보와 테이블을 구성하는 필드, 데이터 타입에 대한 정보 저장 - 데이터베이스 구조의 변경사항이 있을 때 자동으로 업데이트 됨

 

---

💡인덱스

그렇다면 테이블과 함께 저장되는 인덱스는 무엇일까?

 

인덱스란 데이터를 빠르게 찾을 수 있도록 만든, 책의 목차나 색인과 같은 역할을 하는 데이터 구조를 뜻한다. 

데이터베이스에서 인덱스는 원하는 데이터를 빨리 찾기 위해서 투플의 키 값에 대한 물리적 위치를 기록해둔다. 

 

데이터를 저장하는 방식인 자료구조 중에 B-Tree는 특수한 트리 구조를 가지기 때문에 검색할 때 매우 빠른 속도를 자랑한다. 인덱스는 이러한 특징 때문에 일반적으로 B-Tree를 사용해서 저장된다. 

 

하지만 데이터의 변경/삽입이 많이 일어날 경우 B-Tree 특성상 트리 구조를 유지하기 위해서 노드의 분할/이동이 많이 일어나야하기 때문에 성능이 저하되는 문제점이 있다. 

 

인덱스의 특징들을 살펴보면 다음과 같다. 

• 테이블의 한 개 이상의 속성을 이용해서 생성
• 빠른 검색, 효율적인 레코드 접근이 가능
• 순서대로 정렬된 속성과 데이터 위치만 보유
• 저장된 값들은 테이블의 부분집합이 됨
• 보통 B-Tree의 형태로 저장
• 데이터 수정/삭제 등의 변경 시 인덱스 재구성이 필요

 

 

MySQL 인덱스 

MySQL는 클러스터 인덱스와 보조 인덱스를 가지고 있고, B-Tree 형태로 저장한다. 

 

클러스터 인덱스

클러스터 인덱스는 연속된 키 값의 레코드를 묶어서 같은 블록에 저장한다. 따라서 테이블 하나당 한 개만 생성하고, B-Tree 인덱스의 리프 노드에서 테이블의 열 자체가 저장되는 형태이다.

 

그림에서의 왼쪽 표가 루트 노드이고, 오른쪽이 리프 노드이다. 

https://sasca37.tistory.com/215

 

클러스터 인덱스는 테이블 생성시 기본키를 생성하면 자동으로 생성되어 테이블 검색 속도를 향상 시킨다. 

 

보조 인덱스

데이터베이스를 실제 업무에서 사용하다보면, 순서가 다르게 무작위로 저장이 되면서 검색 속도를 저하시킨다. 이때 보조 인덱스를 통해 실제 테이블의 자료가 무작위로 저장되어 있더라도 쉽게 찾을 수 있게 한다. 

 

보조 인덱스는 속성 값으로 B-Tree 인덱스를 구성하고, 리프노드의 각 행은 해당 페이지의 주소 값을 저장한다.

 

아래 그림에서의 왼쪽 표가 리프노드인데, 인덱스의 리프 노드는 실제 데이터 값이 아니라, 테이블상의 데이터 위치를 지정하는 rowid를 저장한다. 

https://sasca37.tistory.com/215

 

 

이렇게 두가지 인덱스, 클러스터와 보조 인덱스를 보통 함께 사용해서 데이터를 찾아 나간다. 

 

---

인덱스 생성 방법

 

인덱스는 데이터의 검색 속도를 빨리하기 위해 사용되지만, 인덱스를 무작정 생성한다고 빨라지는 것이 아니고 오히려 느려질 수 도 있다. 의미 없이 인덱스를 생성하면 검색이 더 느려지고 저장 공간만 낭비할 수 가 있다. 

 

따라서 다음과 같은 경우에 인덱스를 생성해준다.

 

• 조인에 자주 사용되는 속성
• WHERE 절에 자주 사용되는 속성
• 테이블 당 4~5개 정도를 권장

 

인덱스 생성

CREATE [UNIQUE] INDEX [인덱스 이름]
ON 테이블이름 (컬럼 [ASC | DESC] [{, ZJFFJA [ASC | DESC]} ...]};

CREATE INDEX ix_Book ON Book(bookname);

 

인덱스 확인

SHOW INDEX FROM [테이블명];

 

 

인덱스의 재구성

ANALYZE TABLE [테이블이름];

---

Reference 

https://sasca37.tistory.com/215

[Database] 인덱스 정리 (클러스터, 보조 인덱스, B+ 트리)