3. 인덱스

인덱스란?

- 데이터베이스 객체 중 하나로 테이블에 인덱스를 작성하며 검색속도를 향상시킬 수 있다.

- 테이블과 별개로 독립적 객체로 작성되나, 테이블에 의존적이다.

- 기본키와 같은 제약조건이 지정되면 따로 인덱스 생성을 안하더라도 해당 키에 자동으로 인덱스가 생성된다.

 

 

인덱스의 필요성

1. 구별될 값이 많은 컬럼

2. WHERE 조건절에서 자주 조회되는 컬럼

3. 큰 테이블에서 적은 데이터가 필요할 때 (검색 결과가 전체 데이터의 아주 소수일 때)

4. JOIN에 자주 사용되는 컬럼

5. NULL을 포함하는 행이 많은 컬럼

 

 

인덱스가 불필요한 경우

1. 데이터가 적음

2. 조회보다 삽입, 수정, 삭제 처리가 많은 테이블

3. 조회 결과가 전체 행의 15% 이상으로 조회될 것 같은 경우

 

 

인덱스 생성

CREATE INDEX 인덱스이름 ON 테이블이름(컬럼이름);

 

​인덱스 가이드라인

1. WHERE절에 JOIN 조건을 기술한다.

2. 테이블을 JOIN하는 select문장을 작성할 경우 명확성을 위하여 또는 데이터베이스의 perfomance향상을 위하여

열 이름 앞에 테이블 명을 붙인다.

3. 똑같은 열 이름이 존재하는 테이블이 있을 경우, 반드시 열 이름 앞에 테이블 명을 붙인다.

4. n개의 테이블을 JOIN하려면 최소한 n-1번의 JOIN 조건문이 필요하다.

 

 

인덱스의 활용

1. 고유 인덱스(Unique Index)

: 중복되지 않는 데이터를 인덱스의 키 값으로 지정, 성능이 좋음, 중복되는 값을 삽입하려 하면 에러 발생

CREATE UNIQUE INDEX 인덱스명 ON 테이블명(컬럼명);

2. 비고유 인덱스(Non-Unique Index)

: 중복되는 데이터를 인덱스의 키 값으로 지정​

CREATE INDEX 인덱스명 ON 테이블명(컬럼명);

3. 함수 기반 인덱스

: 인덱스를 사용하려면 WHERE절의 조건을 다른 형태로 가공하면 안됨.

  만약 WHERE 조건절로 '컬럼+x'와 같이 가공된 형태를 사용하여 인덱스를 생성하고 싶을 때, 함수 기반 인덱스를 생성.

  인덱스 생성으로 검색 속도는 향상되나, 쿼리 조건이 바뀌면 인덱스를 다시 생성해야 하며, 성능향상을 위한 근본적인 방법 아님.

-- 형식: CREATE INDEX 인덱스명 ON 테이블명(컬럼명  가공형태);
CREATE INDEX idx_price_add_bonus ON newbook(price + 100);

4. 결합 인덱스(Composite Index)

: 두 개 이상의 컬럼을 합쳐 인덱스 생성

  WHERE 조건 컬럼이 2개 이상 AND로 연결되어 사용되는 경우 많이 사용

CREATE INDEX 인덱스명 ON 테이블명(컬럼명1, 컬럼명2....);

5. BITMAP 인덱스

: 데이터 값의 종류가 적고 많을 경우 사용(ex: 성별)

  데이터가 어디에 존재하는지 정보를 BIT으로 표시

  BITMAP INDEX 생성 후 컬럼 값이 하나 더 생긴다면 인덱스를 전부 수정해야 함

CREATE BITMAP INDEX 인덱스명 ON 테이블명(컬럼명);

6. 인덱스 제거

DROP INDEX 인덱스명;

​

7. 인덱스 재생성

: 대량의 DML 작업을 수행하면 인덱스 성능이 저하되므로 재설정(꾸준한 관리 필요)

ALTER INDEX 인덱스명 REBUILD;

​

8. 인덱스 조회

: user_indexes 뷰를 이용하여 인덱스 정보 조회

SELECT 테이블명, 인덱스명, 인덱스타입
FROM user_indexes
WHERE 테이블명 = '테이블명'