인덱스

인덱스 - 테이블의 컬럼을 떼서 정렬을 하고 따로 저장을 해두는 것

인덱스 사용 이유

• WHERE 구문과 일치하는 열을 빨리 찾기 위해
• 특정 열을 고려 대상에서 빨리 없애 버리기 위해
• 조인(JOIN)을 실행할 때 다른 테이블에서 열을 추출하기 위해
• 특정하게 인덱스된 컬럼을 위한 MIN() 또는 MAX()값을 찾기 위해
• 사용할 수 있는 키의 최 자측 접두사(leafmost prefix)를 가지고 정렬 및 그룹화를 하기 위해 (??)
• 데이터 열을 참조하지 않은 상태로 값을 추출하기 위해서 쿼리를 최적화 하는 경우

 

인덱스 구조

• 논리적/물리적으로 테이블과 독립적
• 테이블은 컬럼에 데이터가 정렬되지 않고 입력된 순서대로 들어가지만,
  인덱스는 KEY컬럼과 ROWID컬럼 두개로 이루어져 있고 오름차순, 내림차순으로 정렬이 가능하다.

--문법
CREATE INDEX [인덱스명] ON [테이블명](컬럼1, 컬럼2, 컬럼3 ......)

--예제
CREATE INDEX EX_INDEX ON CUSTOMERS(NAME, ADDRESS)

--컬럼값 중복 불가 UNIQUE
CREATE[UNIQUE] INDEX EX_INDEX ON CUSTOMERS(NAME, ADDRESS)

--인덱스 조회
SELECT * FROM EX_INDEX WHERE TABLE_BNAME = 'CUTOMERS'

--인덱스 삭제
DROP INDEX [EX_INDEX]

--인덱스 수정
DROP INDEX [기존 인덱스 명] TO [바뀔 인덱스 명]

--예제
ALTER TABLE EX_INDEX RENAME TO EX_INDEX_NEW

--인덱스 리빌드
ALTER INDEX [인덱스명] REBUILD;

인덱스 파일은 생성 후 INSERT, UPDATE, DELETE등을 반복하다 보면 성능이 저하된다. 
생성된 인덱스는 트리 구조를 가지고, 점점 깊어지는데 이로 인해 인덱스의 검색 속도가 떨어지므로 주기적으로 리빌딩 하는 작업이 필요하다.

 

 

1. 다음 중 관계형 데이터베이스의 인덱스(index)에 대한 설명으로 가장 적절한 것은?

1) 기본 인덱스(primary key index)에 중복된 키 값들이 나타날 수 있다.
2) 기본 인덱스에 널 값(null value)들이 나타날 수 없다.
3) 보조 인덱스(secondary index)에는 고유한 키 값들만 나타날 수 있다. ▶︎ unique 값 설정 안하면 중복된 값이 나타날 수 있다.
4) 자주 변경되는 속성은 인덱스를 정의할 좋은 후보이다.

 

2. 다음 중 관계형 데이터베이스 인덱스(index)에 대한 설명으로 가장 부적절한 것을 2개 고르시오.

1) 테이블의 전체 데이터를 읽는 경우는 인덱스가 거의 불필요하다.
2) 인덱스는 조회, 삽입, 삭제, 갱신, 연산 속도를 향상시킨다.
3) B 트리는 관계형 데이터베이스의 주요 인덱스 구조이다.
4) 대량의 데이터를 삽입할 때는 모든 인덱스를 생성하고 데이터를 입력하는 것이 좋다.

 

3.

<B-TREE> 인덱스는 브랜치 블록과 리프 블록으로 구성되며, 브랜치 블록은 분기를 목적으로 하고 리프블록은 인덱스를 구성하는 컬럼의 값으로 정렬된다. 일반덕으로 OLTP 시스템 환경에서 가장 많이 사용된다.

<CLUSTERED> 인덱스는 인덱스의 리프 페이지가 곧 데이터 페이지이며, 리프페이지의 모든 데이터는 인덱스 키 컬럼 순으로 물리적으로 정렬되어 저장된다.

<BITMAP> 인덱스는 시스템에서 사용될 질의를 시스템 구현 시에 모두 알 수 없는 경우인 DW 및 AD-HOC 질의 환경을 위해서 설계되었으며, 하나의 인덱스 키 엔트리가 많은 행에 대한 포인터를 저장하고 있는 구조이다.

 

4. 다음 중 인덱스에 대한 설명으로 가장 적절한 것을 2개 고르시오.

1) 인덱스는 인덱스 구성 컬럼으로 항상 오름차순으로 정렬된다. ▶︎ 내림차순도 가능
2) 비용기반 옵티마이저는 인덱스 스캔이 항상 유리하다고 판단한다.
3) 규칙기반 옵티마이저는 적절한 인덱스가 존재하면 항상 인덱스를 사용 하려고 한다.
4) 인덱스 범위 스캔은 결과가 없으면 한 건도 반환하지 않을 수도 있다. 

 

5. 다음 중 아래의 내용에 대한 설명으로 가장 적절한 것 2개를 고르시오.

[INDEX 생성]
CREATE INDEX IDX_EMP_01 ON EMP (REGIST_DATE, DEPTNO);

[SQL  실행]
SELECT * 
FROM EMP
WHERE DEPTNO = 47
AND
REGIST_DATE BTWEEN '2015.02.01' AND '2015.02.28';

1) 실행된 SQL에 대해서 인덱스 비효율이 있어 인덱스 컬럼을 DEPTNO+ REGIST_DATE 순으로 변경할 필요가 있다.
2) IDX_EMP_01 인덱스를 이용하여 DEPTNO = 47 조건을 효율적으로 탐색할 수 있다.
3) REGIST_DATE 컬럼에 대한 조건을 범위 검색이 아닌 동등 검색 조건으로 변경하면 IDX_EMP_01 인덱스를 효율적으로 활용할 수 있다.
4) IDX_EMP_01 인덱스는 테이블 내의 대량 데이터를 탐색할 때 매우 유용하게 활용될 수 있는 인덱스 형식이다.

 

6. 다음 중 인덱스에 대한 설명으로 가장 부적절한 것은?

1) 인덱스의 목적은 조회 성능을 최적화하는 것이다.
2) INSERT, UPDATE, DELETE 등의 DML 처리 성능을 저하시킬 수도 있다.
3) B-트리 인덱스는 일치 및 범위 검색에 적절한 구조이다. (수직 → 수평 탐색 구조)
4) 인덱스 엑세스는 테이블 전체 스캔보다 항상 유리하다.

 

7. 다음 중 인덱스에 대한 설명으로 가장 부적절한 것은?

1) 인덱스는 데이터 조회 목적에는 효과적이지만, INSERT, UPDATE, DELETE 작업에는 오히려 많은 부하를 줄 수도 있다.
2) 인덱스를 이용한 데이터 조회는 항상 테이블 전체 스캔보다 빠른것을 보장할 수 있다.
3) SQL Server의 클러스터형 인덱스는 ORACLE IOT와 매우 유사하다
4) 인덱스는 INSERT와 DELETE 작업과는 다르게 UPDATE 작업에는 부하가 없을 수도 있다.
5) 인덱스를 활용하여 데이터를 조회할 때 인덱스를 구성하는 컬럼들의 순서는 SQL 실행 성능과 관계가 없다.

SQL Server 클러스터형 인덱스

- 클러스터형 인덱스는 해당 키 값을 기반으로 테이블이나 뷰의 데이터 행을 정렬하고 저장한다.
- 클러스터형 인덱스는 테이블 당 하나만 생성할 수 있다. 
- 자주 사용하는 쿼리에 사용되는 칼럼, 고유한 값을 많이 가지고 있는 칼럼에 생성하는 것이 유리하다.
- 범위 값을 반환하는 쿼리에 클러스터형 인덱스를 사용하는 것이 좋다.
- ORDER BY  혹은 GROUP BY에 사용하는 경우 클러스터형 인덱스 키로 사용하는것이 좋다. 
- 자주 변경되는 칼럼은 클러스터형 인덱스 키로 사용하지 않는 것이 좋다.
- 최대한 적은 수의 칼럼을 클러스터형 인덱스 키 칼럼으로 지정해서 사용한다.  
- 이미 대용량의 데이터가 입력된 상태라면, 업무시간에 클러스터형 인덱스를 생성하는 것은 심각한 시스템 부하를 줄 수 있으므로 신중해야 한다. 
- 비클러스터형 인덱스보다 검색 속도는 빠르지만, 데이터 입력/수정/삭제는 느리다. 

오라클 IOT, Index-Oraganized Table 

- table random access 가 발생하지 않도록 처음부터 인덱스 구조로 생성된 테이블
- index leaf block = data block (모든 행 데이터를 리프블록에 저장)
- 정렬상태를 유지하며 데이터를 삽입 (pk 컬럼 순)

CREATE TABLE INDEX_ORG_T (
A NUMBER PRIMARY KEY, 
B VARCHAR(10)
)
ORGANIZATION INDEX;

- 일반적으로 사용되는 테이블은 힙 구조 테이블

장점
- RANDOM이 아닌 Sequential Access 방식이므로 넓은 범위 access시 유리 

단점
- dml 시 인덱스 분할로 인한 부하 발생 (pk 이외의 컬럼 수가 많을수록 성능 저하)

활용
- 크기가 작고 NL JOIN으로 반복 LOOKUP하는 테이블
- 폭이 좁고 긴 테이블
- 넓은 범위를 주로 검색하는 테이블 (Between, Like 같은 조건으로 검색, 통계성 테이블)
- 데이터 입려과 조회 패턴이 서로 다른 테이블 (입력은 일자별 조회는 사원별)