[데이터베이스] 인덱스 Index, B tree

Index(인덱스)

• 추가적인 쓰기 작업과 저장 공간을 활용하여 데이터베이스 테이블의 검색 속도를 향상시키기 위한 자료구조
• 테이블의 칼럼과 해당 레코드가 저장된 주소를 key-value 쌍으로 인덱스를 만들어 둔다.
• DBMS 인덱스는 값 탐색(SELECT)은 빠르지만, 값의 삭제, 추가, 수정(Update, Delete, Insert)의 경우에는 쿼리문 실행 속도가 느려진다.

 

Index 자료구조

• B+-Tree 인덱스 알고리즘
  • 일반적으로 사용되는 인덱스 알고리즘
  • 칼럼의 값을 변형하지 않고, 원래의 값을 이용해 인덱스하는 알고리즘
• Hash 인덱스 알고리즘
  • 칼럼의 값으로 해시 값을 계산해서 인덱싱하는 알고리즘
  • 주로 메모리 기반의 데이터 베이스에서 사용
• B+-Tree 를 사용하는 이유는?
  • SELECT query에 부등호(<>) 연산도 포함되기 때문에 hash table에서 사용하기 적합하지 않다.

 

Primary index(기본 인덱스), Secondary index(보조 인덱스)

• 클러스터(Cluster) : 여러 개를 하나로 묶는 의미
• 클러스터드 인덱스 
  • 인덱스에서 비슷한 것을 묶어서 저장하는 형태
  • 클러스터드 인덱스는 테이블의 primary key에만 적용
  • primary key 값이 비슷한 레코드끼리 묶어서 저장하는 것
  • 테이블 당 한 개만 생성 가능하다. <-> 논 클러스터드 인덱스는 테이블 당 여러개 생성 가능

 

 

테이블 생성 시 파일 구성

• FRM : 테이블 구조 저장 파일
• MYD : 실제 데이터 파일
• MYI : Index 정보 파일(Index 사용시 생성)
  • 쿼리로 index를 사용하는 칼럼을 검색하면, MYI 파일의 내용을 활용

 

인덱스 단점

• index를 생성하면, .mdb 파일 크기가 증가한다.
• 한 페이지를 동시에 수정할 수 있는 병행성 감소
• 데이터 변경이 자주 일어날 경우 index의 재작성으로 성능 저하

 

 

인덱스 활용 예시

• 사용하면 좋은 경우
  • where 절에서 자주 사용되는 경우
  • 외래키가 사용되는 경우
  • join 에 자주 사용되는 경우
• Index 사용하면 안좋은 경우
  • Data 중복도가 높을 경우
  • DML이 자주 일어나는 경우(Insert, Delete, Update)

 

DML 요청 시 상황

• INSERT
  • 기존 block에 여유가 없으면 새로운 data 입력
  • 새로운 block을 할당 받고, Key를 옮긴다.
  • index split 작업 동안, 해당 block의 key 값에 대해 DML 블로킹
• DELETE
  • table에서 data 삭제 : data는 지워지고, 다른 Data가 공간 사용 가능
  • index에서 data 삭제 : data가 지워지지 않고, 사용 안됨으로 표시된다.
• UPDATE
  • table에서 update : index는 update 불가능
  • index에서 update : delete 발생 후, 새로운 작업 insert (table보다 2배 작업)