1.CONCEPT OF INDEX
- 클러스터형 인덱스 : 기본 키(Primary key)로 지정하면 자동 생성되며 테이블 당 1개
- 보조 인덱스 : 고유 키(Unique)로 지정하면 자동 생성되며 여러 개 생성 가능
--예시1) 기본키, 고유키로 지정 create table table1 ( col1 int primary key, # 기본 키로 지정 col2 int unique # 고유 키로 지정 );--예시2) 존재하는 테이블에 키 지정 alter table member drop primary key; alter table member add constraint primary key(mem_id); # 고유 키도 같은 방법으로 가능
2. INTERNAL OPERATING OF INDEX
인덱스는 내부적으로 균형 트리의 자료구조로 구성되어 있다. 이러한 특성상 데이터를 검색하는 데 효율적인 형태다. 하지만 데이터 변경 작업(Insert, Update, Delete)시에 페이지(노드) 분할로 작업이 느릴 수 있다. 즉, 데이터 변경할 때 마다 적절한 균형 트리 구조로 재구성하는 것으로 볼 수 있는데, 이러한 작업이 빈번히 일어나면 오히려 성능에 부정적인 영향을 준다.
https://ko.wikipedia.org/wiki/트리_구조
클러스터형 인덱스와 보조 인덱스의 큰 차이점은 리프 페이지에 있다. 클러스터형 인덱스의 리프 페이지는 데이터 페이지와 동일해 찾고자 하는 데이터가 저장이 되어 있지만, 보조 인덱스의 리프 페이지는 찾고자 하는 데이터의 주소가 저장되어 있기 때문에, 그 주소의 데이터를 찾는 작업을 한번 더 수행하게 된다.
즉, 책의 비유를 빌리면 클러스터형 인덱스는 사전처럼 그 자체가 찾아보기(index)로 볼 수 있고, 보조 인덱스는 일반적인 책 뒷편의 찾아보기와 같아 데이터의 위치를 파악하고 한번 더 찾아봐야 한다.
3. USING INDEX
--예시1) 기본 키 생성과 삭제 alter table buy add primary key (num); # num은 buy테이블의 열 이름 alter table buy drop primary key;--예시2) 기본 키가 auto_incremet일 경우, 이를 없애준 후에 삭제가 가능함 alter table buy modify num int not null; alter table buy drop primary key--예시3) pk-fk 설정되어 있을 경우, 외래키 이름을 모를 경우 조회 후 삭제 가능함 (열이름 아님) select table_name, constraint_name from information_schema.referential_constraints where constraint_schema = 'market_db'; # buy와 member table은 market_db에 있음 alter table buy drop foreign key buy_ibfk_1; alter table member drop primary key;--예시4) 보조키 생성과 삭제, 확인 create index idx_mem_addr on member (addr); # member table의 특정 열 addr analyze table member; # 지금까지 만든 인덱스 모두 적용 show table status like 'member' # index_length가 보조 인덱스의 크기 show index from member;
인덱스는 데이터 탐색에 효율적이므로 찾고자 하는 데이터 혹은 where절의 조건에서 자주 사용되는 열에 인덱스를 사용하는 것이 효과적이다. 즉, 성능이 더 우수한 클러스터형 인덱스는 하나만 생성할 수 있으므로 조회할 때 가장 많이 사용되는 열에 지정하는 것을 권장한다. 반대로 성별, 연락처 국번 등 종류가 제한되는 인덱스는 효과가 떨어지며 사용되지 않는 인덱스는 제거하는 것이 바람직하다.
저자 우재남, 혼자 공부하는 SQL, 한빛미디어
