DB: Index

인덱스

• index
  인덱스는 데이터베이스 분야에 있어서 테이블에 대한 동작의 속도를 높여주는 자료구조를 일컫는다. 인덱스는 테이블 내의 1개의 컬럼, 혹은 여러개의컬럼을 이용하여 생성될 수 있다. 고속의 검색 동작뿐만 아니라 레코드 접근과 관련 효율적인 순서 매김 동작에 대한 기초를 제공한다. 인덱스를 저장하는 데 필요한 디스크 공간은 보통 테이블을 저장하는 데 필요한 디스크 공간보다 작다. 왜냐하면 보통 인덱스는 키-필드만 갖고 있고, 테이블의 다른 세부 항목들은 갖고 있지 않기 때문이다. 관계형 데이터베이스에서는 인덱스는 테이블 부분에 대한 하나의 사본이다. 인덱스는 고유 제약 조건을 실현하기 위해서도 사용된다. 고유 인덱스는 중복된 항목이 등록되는 것을 금지하기 때문에 인덱스의 대상인 테이블에서 고유성이 보장된다.

  • 책의 찾아보기(index, 색인)와 같이 원하는 내용을 바로 찾을 수 있도록 지원.

  • 테이블의 데이터 조회 시 동작속도를 높여주는 자료구조.

  • 데이터의 위치를 빠르게 찾아주는 역할

  • 컬럼의 값과 레코드가 저장된 주소를 키와 값의 쌍으로 인덱스를 만들어 둠.

  • MYI(MySql Index) 파일에 인덱스 저장.

    

• index의 문제점

  • 책의 모든 페이지에 나오는 단어를 찾아보기에 표시하게 되면 찾아보기의 분량이 엄청나게 많아져 오히려 본문보다 두꺼워지는 상황이 발생.
  • 필요 없는 index를 만들면 데이터베이스가 차지하는 공간만 늘어나고, index를 이용하여 데이터를 찾는 것이 전체 테이블을 찾는 것 보다 느려짐.
  • 데이터베이스의 공간을 차지하므로 추가적인 공간 필요
  • 처음 index를 생성하는데 많은 시간이 소요
  • 데이터의 변경 작업 (insert, update, delete)이 자주 일어나는 경우 오히려 성능 저하가 일어날 수 있음.

• index의 종류

  • 클러스터형 인덱스
    • 특정 나열된 데이터들을 일정 기준으로 정렬해 주는 인덱스
    • 클러스터형 인덱스 생성 시 데이터 페이지 전체가 다시 정렬 >> 이미 대용량의 데이터가 입력된 상태라면 클러스터형 인덱스 생성시 심각한 부하가 발생
    • 테이블당 하나만 생성 가능, 어느 열에 클러스터형 인덱스를 생성하는지에 따라 시스템의 성능이 달라짐.
    • 보조 인덱스 보다 검색 속도는 더 빠르다. 단, 입력/수정/삭제는 더 느림
    • MySQL의 경우 pk가 있다면 pk를 클러스터형 인덱스로, 없다면 unique 하면서 Not Null인 컬럼을, 그것도 없으면 임의로 보이지 않는 컬럼을 만들어 클러스터형 인덱스로 지정.
  • 보조 인덱스
    • 개념적으로 후보키에만 부여 가능한 index (주민번호 처럼 각 데이터를 인식할 수 있는 최소한의 고유 식별 속성 집합)
    • 보조 인덱스 생성시 데이터 페이지는 그냥 두 상태에서 별도의 페이지에 인덱스를 구성. (자동 정렬되지 않음, ex: 책의 색인)
    • 데이터가 위치하는 주소값(RID)
    • 클러스터형 인덱스보다 검색 속도는 느리지만 데이터의 입력/수정/삭제 시 성능 부하가 적음
    • 보조 인덱스는 테이블당 여러 개 생성 가능 (너무 많이 생성시 오히려 성능 저하)

• 제약 조건에 따른 Index 결정
  

• Index 생성 전략
  

• 자동생성 index

  • 클러스터형 인덱스
    

• Index 생성

create [unique] index 인덱스이름
on 테이블이름 (열이름) [asc|desc]

• create index 문으로 인덱스를 만들면 보조 인덱스가 생성

• create index 문의 unique 옵션은 고유한 인덱스를 만들 때 사용

• asc, desc 로 정렬 방식 지정

• index_type은 생략 가능하며, 생략할 경우 기본 값이 B-tree형식 사용

• create index문으로는 클러스터형 인덱스를 만들 수 없으며, 클러스터형 인덱스를 만들려면 alter table을 사용해야 함
  ex)

  alter table ssafy modify column user_id char(4) primary key;

  • index를 활용한 select

    • full table scan
      

    • 클러스터형 인덱스 참조
      

• index 삭제

  • 인덱스를 모두 삭제할 때는 보조 인덱스부터 삭제
  • 인덱스를 많이 생성해 놓은 테이블의 경우 각 인덱스의 활용 용도를 확인한 후 활용도가 떨어지는 인덱스를 삭제

  drop index 인덱스이름 on 테이블이름;

  • 자동으로 생성된 클러스터형 인덱스 삭제

  alter table 테이블이름 drop primary key;