[DB] Index

Page란?

 디스크와 메모리에 I/O가 이루어지는 데이터 Read/Write의 최소 작업 단위이다.

 하나의 컬럼만 읽고 싶어도 레코드가 속한 페이지 전체를 읽어야 한다. 만약 레코드를 찾는데 1개의 페이지만으로 처리가 안된다면 추가 페이지를 읽어야 하는데, 그러한 과정은 디스크 I/O 작업로 인한 성능 저하 및 메모리 효율 저하를 불러온다. 또한, 페이지에 저장되는 데이터의 크기가 크면 메모리에 캐싱해둘 수 있는 페이지 수도 줄어들게 된다.

 따라서 페이지에 저장되는 개별 데이터의 크기를 최대한 작게 하여 1개의 페이지에 많은 데이터가 담기도록 하는 것, 그리하여 액세스하는 Page 수를 최대한 줄이는 것이 DB 성능 개선에 매우 큰 영향을 준다.

Extent란?

 연속된 Page(=Block)를 정해진 수로 묶어 둔 논리적 단위이다.

 테이블이나 인덱스에 데이터를 입력하다가 공간이 부족해지면 정해진 Extent 크기의 연속된 정해진 수의 Page를 할당 받는다. Extent 내의 Page끼리는 인접하고, Extent끼리는 인접하지 않는다. 

Full Scan이란?

 다중 블록단위 I/O를 수행하는 검색 방식으로, 테이블에 존재하는 모든 데이터를 읽어 가며 조건에 맞는 데이터를 결과로 추출하고 조건에 맞지 않으면 버리는 방식이다.

 한 번의 I/O에 여러 블록을 읽어오기 때문에 다량의 Row를 읽어야 할 때는 I/O Call이 적어 소요되는 입출력 비용은 적은 편이나, 적은 양의 결과만 필요할 경우 불필요한 메모리 사용과 검색 시간을 소요할 수 있다.

Index란?

 데이터 테이블에 대한 검색 성능의 속도를 높여주기 위해 사용하는 자료구조이다. 특정 컬럼에 Index를 생성하면 데이터를 정렬한 후, 지정한 컬럼의 값과 데이터의 물리적 주소를 별도의 추가적인 메모리에 저장하여 지정한 컬럼 값을 주소값 검색에 사용하는 key값으로 사용하는 형태이다.

 Full Scan 방식과 비교했을 단일 블록단위 I/O를 사용하기 때문에 I/O 호출은 더 많다고 볼 수 있으나, 필요한 페이지에만 접근할  수 있어 적은 양의 결과를 찾을 때 훨씬 빠른 검색 성능을 얻을 수 있다. 또한 ORDER BY 문이나 MIN/MAX 같은 값도 이미 정렬된 형태를 가지고 있기 때문에 수행이 빠르다. 

 주로 B-tree의 형식으로 저장되는데, InnoDB에서는 B+tree로 이루어져있다고 한다. 자세한 내용은 차후 다른 글로 작성하겠다.

Index의 종류

• 클러스터형 인덱스(Clustered Index)
  • 테이블당 한 개만 생성 가능
  • 특정 열 또는 여러 개의 열들을 기준으로 데이터가 정렬 가능하게 하는 자료구조
  • 데이터가 많고, 변경이 많지 않으며, Select 문 수행이 많은 경우 효과적
  • 값의 분산도(Cardinality)가 높은 열에 만드는 것이 효율적
  • 테이블 생성시 PK(Primary Key)를 사용하면 고유(Unique) 인덱스가 클리스터형 인덱스로 자동 생성됨
• 비클러스터형 인덱스(Nonclustered Index)
  • Leaf 페이지가 데이터 페이지가 아니므로, 실제 데이터 정렬 상태와는 별도
  • 데이터 행으로부터 독립적
  • 한 테이블에 여러 개의 비클러스터형 인덱스 설정이 가능
  • 클리스터형 인덱스보다 Select 속도는 느리지만, 비교적 데이터 변경 면에서는 빠름
  • 클러스터형 인덱스가 있는 경우 Leaf block으로 이동 후 클러스터형 인덱스를 이용해서 데이터 접근
  • 클러스터형 인덱스가 없는 경우 Leaf block으로 이동 후 RID(Row의 주소)를 이용해서 데이터 접근

Index의 단점

 Index는 잘못 사용하면 오히려 성능이 저하될 수 있다는 단점이 존재한다.

 우선 인덱스 관리를 위한 추가 작업이 필요한 문제가 존재한다.

 삽입(INSERT) 시 새로운 데이터에 대한 인덱스 추가 처리, 삭제(DELETE) 시 삭제하는 데이터의 인덱스를 사용하지 않는다는 추가 처리, 수정(UPDATE) 시 기존의 인덱스를 사용하지 않는다는 추가 처리 및 갱신된 데이터에 대한 인덱스 추가 등의 작업이 필요하다. 때문에 삽입/삭제/수정이 빈번하게 발생하는 테이블에서 사용하면 성능 저하 및 메모리 낭비가 발생한다. DELETE나 UPDATE의 경우 인덱스는 삭제하는 것이 아니라 미사용 처리를 하는 것이기 때문에 자칫 잘못하면 실제 데이터보다 인덱스가 커지는 문제가 생길 수 있다.

 그리고 인덱스 역시 Page로써 존재하므로 추가적인 메모리 공간을 차지한다. 실제 데이터의 10% 정도의 크기를 차지하게 되는데, 인덱스를 제대로 사용하지 못할 경우 메모리 낭비로 이어질 수 있다.

 또한 인덱스로 지정한 컬럼이 과하게 길어지거나, 전체 데이터의 많은 비중의 데이터를 처리해야할 경우 페이지 I/O가 다량 발생하게 되므로 오히려 성능이 낮아질 수 있다.

Index의 사용?

 Index는 단점이 명확히 존재하긴 하지만, 많은 경우에 (특히 검색이 잦은 데이터 테이블의 경우) DB 쿼리 성능 향상에 큰 영향을 미칠 수 있다. 잘 사용한다면 확연한 성능 차이를 볼 수도 있는 부분으로, Index의 구조적 특징을 염두에 둔 DB 설계를 통해 성능 측면에서 유의미한 결과를 얻을 수 있으므로 DB 사용시 유념하도록 하자.