MYSQL - 인덱스(INDEX)

인덱스

인덱스 개념

• 테이블 안의 데이터를 쉽고 빠르게 찾을 수 있도록 만든 데이터베이스 객체
• 책 페이지를 목차나 색인을 통해 쉽게 찾는 것과 같은 원리

인덱스의 필요성

• SQL 을 실행할 때 , 디스크 접근 횟수를 줄여 검색 속도를 높이기 위함

• 대부분 DBMS 는 B 트리 (Balanced tree) 구조의 인덱스를 지원
• 장점 : 루트 노드로부터 리프 노드까지의 탐색 길이가 같아서 모든 데이터에 대한 일정 수준의 검색 시간을 보장

• CREATE INDEX 명령문의 형식

CREATE [REVERSE] [UNIQUE] INDEX [인덱스 이름]
ON 테이블_이름 ({열_이름[ASC|DESC]},+);

• '수강' 테이블의 '학번', '과목번호', 열을 대상으로 인덱스 'idx_수강'을 생성하시오

create index idx_수강
on 수강(학번,과목번호);

show index form 수강;

• '과목' 테이블의 이름 열을 대상으로 유일한 값을 갖는 인덱스 'idx_과목'을 생성하시오

create unique index idx_과목
on 과목(이름,ASC);

• 인덱스 삭제를 위한 DROP INDEX 명령문의 형식

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

• 인덱스 'idx_수강' 을 삭제하고 '과목' 테이블의 인덱스 'idx_과목'을 삭제하시오

drop index idx_수강;
alter table 과목 drop index idx_과목;

인덱스 적용 고려사항

불필요한 인덱스는 오히려 성능을 떨어뜨리고 저장 공간만 낭비할 수 있음

• 인덱스는 꼭 필요한 만큼만 효과적으로 생성해야 함

【 인덱스 생성이 필요한 경우 】

• 기본키와 외래키의 경우 , 인덱스 생성이 바람직함 . 대부분의 DBMS 는 기본키에 대해서 자동으 로 인덱스를 생성함
• WHERE 절 조건식에 자주 사용되는 테이블 열의 경우 , 인덱스 생성이 바람직함
• 조인 조건식에 자주 사용되는 테이블 열도 인덱스 생성이 바람직함
• 하나의 테이블에 3~5 개 정도의 인덱스가 효과적임
• 가변길이 문자형이나 실수형 , 날짜형 열보다는 정수형 , 고정길이 문자형 열에 인덱스를 생성하 는 것이 바람직함
• ORDER BY 절이나 GROUP BY 절에 자주 사용되는 열의 경우 , 인덱스 생성을 고려할 수 있음

【 인덱스 생성이 불필요한 경우 】

• 갱신이 빈번한 테이블 열의 경우 , 인덱스가 바람직하지 않음
• 집계 함수 , 내장 함수를 적용하여 열 값을 변형하는 경우 , 인덱스가 효과적이지 않음
• 성별’ 같은 열처럼 도메인이 작아서 열의 선택도 ( 가 높을 경우 , 인덱스가 바람직하지 않음
• 범위 검색을 하는 경우 , 인덱스가 바람직하지 않음
• 테이블의 행 개수가 별로 없는 경우 , 인덱스가 바람직하지 않음