[SQL] 인덱스 - 생성 / 삭제 / 종류 ( 고유 / 비고유 / 단일 / 결합 / DESCENDING / 집중 / 비집중 )

인덱스

 

인덱스

검색 성능을 향상 시키기 위한 부가적인 자료 구조

-- 질의 명령문의 검색 속도를 향상시키기 위해 칼럼에 대해 생성하는 객체

-- 포인터를 이용하여 테이블에 저장된 데이터를 랜덤 액세스하기 위한 목적으로 사용함

 

인덱스가 효율적인 경우

-- WHERE 절이나 조인 조건절에서 자주 사용되는 칼럼의 경우

-- 전체 데이터 중에서 10~15% 이내의 데이터를 검색하는 경우

-- 두 개 이상의 칼럼이 WHERE 절이나 조인 조건에서 자주 사용되는 경우

-- 테이블에 저장된 데이터의 변경이 드문 경우

   색인은 부가적인 자료 구조임

    데이터 삽입 시 비효율적임

 

인덱스 생성 / 삭제 구문

-- 색인 생성

  CREATE INDEX 색인명

  ON 테이블명 ( 속성명 , 속성명,…)

-- 색인 삭제

  DROP INDEX 색인명

  ON 테이블명

 

---

인덱스의 종류

1. 고유 인덱스 vs 비고유 인덱스

고유 인덱스

create unique index 인덱스명 on 테이블명 (속성) ;

-- 유일 값을 가지는 속성에 대하여 생성하는 색인

-- 각 키 값은 테이블의 하나의 튜플과 연관됨

비고유 인덱스

create index 인덱스명 on 테이블명 (속성) ;

-- 중복된 값을 가지는 속성에 생성하는 인덱스

-- 키 값은 여러 개의 튜플들과 연관됨

기본키

-- 테이블이 기본키에 대해서는 자동으로 고유색인이 생성됨

  -> Primary Index

-- 기본키는 중복을 허용하지 않음

-- 새로운 튜플을 삽입 할 때마다 키값이 고유값인지 검사해야 함 -> 고유 색인을 이용함

-- 테이블에 속한 튜플들이 많다면 매우 느림

 

관계형 테이블의 검색

1. 테이블 검색 시 기본키만을 사용하지 않음

    예) 학생 테이블에서 학번이 100 번인 학생 검색하기

2. 실제로는 학생을 검색할 때는 학번보다 이름을 이용하는 경우가 더 많음

3. 검색을 빨리 하려면 조건에 많이 사용되는 컬럼에 대하여 색인을 생성함

-> Secondary Index

 

고유 인덱스 생성

-- 고유 인덱스를 생성할 때는 UNIQUE 키워드를 사용함
예) 부서 테이블에 부서 이름 에 대하여 고유 색인 생성 하기

create unique index idx_dname_unique  
on department(dname); 

 

비고유 인덱스 생성
-- unique없이 색인을 생성하면 비고유 색인이 됨
예) 부서 테이블에 부서 위치에 대하여 비고유 색인 생성하기

create index idx_loc_unique 
on department(loc);

 

 

2. 단일 인덱스 vs 결합 인덱스

단일 인덱스

create index 인덱스명 on 테이블명 ( 속성 ) ;
-- 하나의 속성만으로 구성된 색인
-- 앞에서 보인 예들은 단일 인덱스들임

결합 인덱스

create index 인덱스명 on 테이블명 ( 속성, 속성 ) ;
-- 두 개 이상의 속성들에 대하여 생성된 색인

 

결합 인덱스의 생성
예) 직원 테이블에서 부서번호와 급여에 대하여 결합 인덱스 생성하기 

create index idx_dnosalary on employee(dno,salary);

 

3. DESCENDING INDEX

create index 인덱스명 on 테이블명 ( 속성 asc or desc, 속성 asc or desc);

 

일반적인 색인들은 속성값에 대하여 오름차순으로 정렬되어 저장됨

DESCENDING INDEX : 특별히 속성별로 정렬 순서를 지정하여 결합 인덱스를 생성하는 방법

예) 사원에 대하여 부서 번호 는 오름차순 , 급여 는 내림차순 으로 하여 색인 생성하기

create index idx_dnosalary_desc on employee(dno asc, salary desc);

 

 

4. 집중 인덱스 vs 비집중 인덱스

집중 인덱스

-- 테이블의 튜플이 저장 된 물리적 순서 해당 색인의 키값 순서와 동일하게 유지되도록 구성된 색인

-- 기본키에 대하여 생성된 색인은 집중 인덱스임

-- 테이블의 튜플들이 기본키에 오름차순으로 정렬되어 저장되어 있고

   기본키 색인 또한 기본키에 따라서 오름차순으로 정렬되어 있음

-- 집중 인덱스는 하나의 테이블에 대하여 하나만 생성할 수 있음

비집중 인덱스

-- 집중 인덱스가 아닌 인덱스들

---

 

인덱스의 활용

질의 수행 시 인덱스를 사용하는지 확인하기

1. 질의 수행 시 인덱스를 강제로 사용하게 하기

2. employee 테이블의 ename 을 가지고 색인 emp_name_idx 을 만듦

3. FROM 절에 WITH(INDEX= INDEX_NAME) 을 추가하여 강제로 특정 색인을 사용하게 함

4. 색인을 사용하는지 질의수행 계획 확인