Index
- 책의 찾아보기(index, 색인)와 같이 원하는 내용을 바로 찾을 수 있도록 지원.
- 테이블의 데이터 조회 시 동작속도를 높여주는 자료구조.
- 데이터의 위치를 빠르게 찾아주는 역할.
- 컬럼의 값과 레코드가 저장된 주소를 키와 값의 쌍으로 인덱스를 만들어 둠.
- MYI(MySQL Index) 파일에 인덱스 저장
Index의 문제점
- 책의 모든 페이지에 나오는 단어를 찾아보기에 표시하게 되면 찾아보기의 분량이 엄청나게 많아져 오히려 본문보다 두꺼워지는 상황이 발생.
- 필요 없는 index를 만들면 데이터베이스가 차지하는 공간만 늘어나고, index를 이용하여 데이터를 찾는 것이 전체 테이블을 찾는 것보다 느려짐.
- 데이터베이스의 공간을 차지하므로 추가적인 공간 필요(DB크기의 10% 정도의 추가 공간 필요)
- 처음 index를 생성하는데 많은 시간이 소요.
- 데이터의 변경 작업(insert, update, delete)이 자주 일어나는 경우 오히려 성능 저하가 일어날 수 있음.
Index의 종류
- 클러스터형 인덱스 (clustered index)
- 특정 나열된 데이터들을 일정 기준으로 정렬해 주는 인덱스
- 클러스터형 인덱스 생성 시 데이터 페이지 전체가 다시 정렬 >> 이미 대용량의 데이터가 입력된 상태라면 클러스터형 인덱스 생성시 심각한 부하가 발생.
- 테이블당 하나만 생성 가능. 어느 열에 클러스터형 인덱스를 생성하는지에 따라 시스템의 성능이 달라짐
- 보조 인덱스보다 검색 속도는 빠르다. 단, 입력/수정/삭제는 더 느림
- MySQL의 경우 Primary Key가 있다면 Primary Key를 클러스터형 인덱스로, 없다면 unique 하면서 Not Null인 컬럼을, 그것도 없으면 임의로 보이지 않는 컬럼을 만들어 클러스터형 인덱스로 지정
- 보조 인덱스 (secondary index)
- 개념적으로 후보키에만 부여 가능한 index. (후보키 : 주민번호와 같이 각 데이터를 인식할 수 있는 최소한의 고유 식별 속성 집합)
- 보조 인덱스 생성시 데이터 페이지는 그냥 둔 상태에서 별도의 페이지에 인덱스를 구성. (자동 정렬되지 않음)
- 데이터가 위치하는 주소값(RID)
- 클러스터형 인덱스보다 검색 속도는 느리지만 데이터의 입력/수정/삭제 시 성능 부하가 적음.
- 보조 인덱스는 테이블당 여러 개 생성 가능. (너무 많이 생성시 오히려 성능 저하)
Index 생성 전략
- 인덱스는 열 단위에 생성
- where절에서 사용되는 열에 생성
- where절에 사용되는 열이라도 자주 사용해야 가치가 있음.
- 데이터 중복도가 높은 열에는 인덱스를 만들어도 효과가 없음. (중복도가 낮은 열에 생성)
- 외래키를 설정한 열에는 자동으로 외래키 인덱스가 생성됨.
- 조인에 자주 사용되는 열에는 인덱스를 생성하는 것이 좋음.
- 데이터 변경(삽입, 수정, 삭제) 작업이 얼마나 자주 일어나는지를 고려해야 함.
- 클러스터형 인덱스는 테이블당 하나만 생성할 수 있음.
- 사용하지 않는 인덱스는 제거.
자동으로 생성되는 Index
- 클러스터형 인덱스.
- Primary Key를 설정하면 자동으로 해당 열에 클러스터형 인덱스가 생성
- 해당 Primary Key 기준으로 정렬
Index 생성
- create index 문으로 인덱스를 만들면 보조 인덱스가 생성.
- create index 문으로는 클러스터형 인덱스를 만들 수 없으며, 클러스터형 인덱스를 만들려면 alter table을 사용해야 함.
- create index 문의 unique 옵션은 고유한 인덱스를 만들 때 사용.
- ASC, DESC로 정렬 방식 지정.
- index_type은 생략 가능하며, 생략할 경우 기본 값인 B-Tree 형식 사용.
- address 테이블의 index 설정 확인
show index from address;- 주소지역(address_area) 열에 단순 보조 인덱스 생성.
create index address_area_idx
on address(address_area);- 고유 보조 인덱스 생성 : 겹치는 값이 있으면 안됨
create unique index user_name_idx
on address(user_name);- 조합해서 인덱스 생성
create index user_name_birth_year_idx
on address(user_name,birth_year);Index 삭제
- 인덱스를 모두 삭제할 때는 보조 인덱스부터 삭제.
- 인덱스를 많이 생성해 놓은 테이블의 경우 각 인덱스의 용도를 확인한 후 활용도가 떨어지는 인덱스를 삭제
drop index 인덱스이름 on 테이블이름;- 자동으로 생성된 클러스터형 인덱스 삭제.
alter table 테이블이름 drop primary key;
야호