인덱스(index)

인덱스(index)

  - 테이블에서 원하는 데이터를 쉽고 빠르게 찾기 위해 사용 > 검색이 자주 사용되는 테이블에서 사용

  - SQL 명령 처리 속도를 빠르게 하기 위해서 특정 컬럼에 대해 생성되는 검색 도구

  - b-tree 구조

 

 

인덱스의 사용

초기 데이터베이스는 정렬 개념이 적용되어있지 않기 때문에, 테이블내의 레코드 순서는 사용자가 필요로 하는 정렬 상태가 아니다. 어떤 데이터를 검색할 때 처음부터 끝까지 차례대로 찾아야하는 상황이면 테이블 전체를 스캔하고, 인덱스를 사용에 용이한 상황은 특정 컬럼을 선택해 별도의 테이블에 복사한 후 미리 정렬시켜놓을 때이다.

(원본 테이블 <- 참조 -> 인덱스)

 

  - 장점 : 처리 속도를 향상 시킨다.

  - 단점 :  인덱스 사용을 많이 하면 전체적인 DB의 성능을 저하시킨다.

 

 

인덱스를 사용해야하는 경우

  - 테이블에 데이터(행)가 너무 많을 때
  - where절에 사용되는 횟수가 많은 컬럼에 적용
  - join에 조건으로 사용되는 컬럼에 적용 PK-FK
  - 인덱스의 손익분기점 > 검색 결과가 10~15% 이하인 경우
  - null을 포함하는 경우 > null 인덱스에서 제외

 

* 그 반대의 경우

  - 테이블에 데이터(행)가 적은 경우엔 의미가 없다.
  - 인덱스의 손익분기점 > 검색 결과가 15% 이상인 경우
  - 해당 테이블의 삽입, 수정, 삭제가 빈번할 경우

 

 

인덱스 사용

--생성
CREATE INDEX TBLINSA
ON RESERVATION(NAME);

--추가
ALTER TABLE TBLRESERVATION
ADD INDEX NameIdx (Name);

--삭제
ALTER TABLE 테이블이름
DROP INDEX 인덱스이름

DROP INDEX 인덱스이름
ON 테이블이름

  - CREATE INDEX 인덱스 이름 ON 테이블 이름(필드명1, 필드명2..)

 

 

인덱스의 종류

 

• 고유 인덱스

create unique index idxName on tblIndex(name); --동명이인(X)

      색인의 값이 중복이 불가능하다. (PK, UNIQUE)

 

• 비고유 인덱스

create index idxBuseo on tblIndex(buseo);

      색인의 값이 중복이 가능하다. (일반 칼럼)

 

• 단일 인덱스

create index idxBuseo on tblIndex(buseo);
select count(*) from tblIndex where buseo = '기획부'; -- 인덱스 효과O
select count(*) from tblIndex where buseo = '기획부' and name = '홍길동'; -- 인덱스 효과X

      컬럼 1개를 대상으로 한다.

 

• 복합 인덱스

create index idxBuseoName on tblIndex(buseo, name);
select count(*) from tblIndex where buseo = '기획부' and name = '홍길동'; -- 인덱스 효과O
select count(*) from tblIndex where name = '홍길동' and buseo = '기획부';

      컬럼 N개를 대상으로 한다.

 

• 함수 기반 인덱스

create index idxSsn on tblIndex(ssn);
create index idxSsn2 on tblIndex(substr(ssn, 8, 1));
select count(*) from tblIndex where substr(ssn, 8, 1) = '1'; --5초