Database
29 Aug 2020

DB인덱스(index)

인덱스란?

인덱스는 지정한 컬럼들을 기준으로 메모리 영역에 일종의 목차를 생성하는 것이다.(인덱스를 메모리에 저장한다.)

RDBMS에서 검색속도를 높이기위해 사용하는 기술이다.

index는 색인으로 해당 테이블의 컬럼을 색인화(따로 파일로 저장)하여 검색시 해당 테이블의

레코드를 full scan하는게 아니라 색인화 되있는 index파일을 검색하여 검색속도를 빠르게한다.

인덱스는 insert,update,delete의 성능을 희생하고 select의 성능을 향상 시킨다.

여기서 주의점은 update,delete의 행위가 느린것이지, update,delete를 하기위해 해당 데이터를 조회하는것은 인덱스가 있으면 빠르게 조회가 된다.


인덱스이미지


B-tree 인덱스 구조

  • 인덱스 탐색은 Root->Branch->leaf-> 디스크 저장소 순으로 진행된다.

    • Branch(페이지번호 2)는 dept_no가 d001이면서 emp_no가 10017~10024까지인 leaf의 부모로 있다.

    • 즉, dept_no=d001 and emp_no=10018로 조회하면 페이지 번호 4인 Leaf를 찾아 데이터 파일의 주소를 불러와 반환하는 과정을 하게 됩니다.

  • 디스크에서 읽는 것은 메모리에서 읽는것보다 성능이 훨씬 떨어진다.

    • 결국 인덱스 성능을 향상시킨다는 것은 디스크 저장소에 얼마나 덜 접근하게 만드느냐, 인덱스 Root에서 Leaf까지 오고가는 횟수를 얼마나 줄이느냐에 달려있다.

  • 인덱스의 갯수는 3~4개 정도가 적당하다.

    • 너무 많은 인덱스는 새로운 Row를 등록할때마다 인덱스를 추가해야하고, 수정/삭제시마다 인덱스 수정이 필요하여 성능상 이슈가 있다.

    • 인덱스 또한 공간을 차지 한다. 많은 인덱스들은 그만큼 많은 공간을 차지한다.

    • 특히 많은 인덱스들로 인해 옵티마이저가 잘못된 인덱스를 선택할 확률이 높다.

  • 인덱스의 n+1번째 컬럼은 n번째 컬럼에 의존해서 정렬 되있다.


인덱스 키 값의 크기

InnoDB(Mysql)은 디스크에 데이터를 저장하는 가장 기본 단위를 페이지라고 하며, 인덱스 역시 페이지 단위로 관리 된다.

페이지는 16KB로 크기가 고정되어 있다.

만약 본인이 설정한 인덱스 키의 크기가 16Byte라고 하고, 자식 노드(Branch,Leaf)의 주소가 담긴 크기가 12Byte정도로 잡으면 16*1024/(16+12)=585로 인해 하나의 페이지에 585개가 저장될 수 있다.

여기서 인덱스 키가 32Byte로 커지면 16*1024/(32+12)=372가 되어 372개만 페이지에 저장할 수 있게 된다.


조회 결과로 500개의 row를 읽을때 16Byte일때는 1개의 페이지에서 다 조회가 되지만, 32Byte일때는 2개의 페이지를 읽어야 하므로 이는 성능 저하가 발생하게 된다.

인덱스의 키는 길면 길수록 성능상 이슈가 있다.


인덱스 컬럼 기준

1개의 컬럼만 인덱스를 걸어야 한다면, 해당 컬럼은 카디널리티(Cardinality)가 가장 높은 것을 잡아야 한다는 점이다.

카디널리티란 해당 컬럼의 중복된 수치를 나타낸다. 예를 들어 성별, 학년 등은 카디널리티가 낮다고 말한다. 반대로 주민번호,계좌번호 등은 카디널리티가 높다고 말한다.

인덱스로 최대한 효율을 뽑으려면 해당 인덱스로 많은 부분을 걸러내야 하기 때문이다.

만약 성별을 인데스로 잡는다면 남/녀 중 하나를 선택하기 때문에 인덱스를 통해 50%밖에 걸러내지 못한다.

하지만 주민번호나 계좌번호같은 경우 인덱스를 통해 데이터의 대부분을 걸러내기 때문에 빠르게 조회가 가능하다.


여러 컬럼으로 인덱스 구성시 기준

카디널리티가 높은 순에서 낮은 순으로 (중복도가 낮은 순에서 높은순으로) 인덱스를 잡는 것이 성능이 훨씬 뛰어나다


Clustered Index vs Non-Clustered Index

클러스터드 인덱스

  • 인덱스 키의 순서에 따라 데이터가 정렬되어 저장되는 방식이다.

  • 실제 데이터가 순서대로 저장되어 있어 인덱스를 검색하지 않아도 데이터를 빠르게 조회 가능

  • 데이터 삽입,삭제 발생시 순서 유지를 위해 데이터 재정렬을 하기때문에 삽입,삭제가 느리다.

  • 한 개의 테이블에 하나의 인덱스만 생성할 수 있다.


넌클러스터드 인덱스

  • 인덱스의 키값만 정렬 되있을뿐 실제 데이터는 정렬되지 않는 방식이다.

  • 데이터를 검색하기 위해서 먼저 인덱스를 검색하여 실제 데이터의 위치를 확인해야 하므로

    검색속도가 떨어진다.

  • 한 개의 테이블에 여러개 생성 가능

    클러스터드 인덱스에 비해 검색 속도가 떨어진다.

Tags:
0 comments