[풀스택과정] 데이터베이스 12. 인덱스 관리

* 이 글은 제가 공부하기 위해 최대한 사실에 입각해 내용을 적으려고 하지만 일부 내용들이 정확하지 않을 수 있습니다.

   혹시나 잘못된 부분이 있으면 너그럽게 이해해 주시고 피드백 부탁드려요!

---

1. 인덱스 개념

인덱스의 개념

• 인덱스란?

  – 인덱스는 SQL 명령문의 처리 속도를 향상하기 위해 칼럼에 대해 생성하는 객체
  – 인덱스는 포인트를 이용하여 테이블에 저장된 데이터를 랜덤 액세스하기 위한 목적으로 사용

• 인덱스의 종류

  – 고유 인덱스
  – 비고유 인덱스
  – 단일 인덱스
  – 결합 인덱스
  – DESCENDING INDEX
  – 함수 기반 인덱스

 

2. 인덱스 구조

인덱스의 구조

• 인덱스의 내부 구조는 B*-트리 형식으로 구성

 

3. 인덱스의 효율적인 사용 방법

인덱스의 효율적인 사용 방법

• 인덱스가 효율적인 경우

  – WHERE 절이나 조인 조건절에서 자주 사용되는 칼럼
  – 전체 데이터중에서 10~15% 이내의 데이터를 검색하는 경우
  – 두 개 이상의 칼럼이 WHERE절이나 조인 조건에서 자주 사용되는 경우
  – 테이블에 저장된 데이터의 변경이 드문 경우
  – 열에 널 값이 많이 포함된 경우, 열에 광범위한 값이 포함된 경우

• 인덱스 생성

☞ 사용법

• UNIQUE : 고유인덱스 시정
• ASC|DESC : 인덱스 키를 오름차순 또는 내림차순으로 정렬

 

4. 인덱스의 종류 및 생성 방법

고유 인덱스(unique index)

  – 고유 인덱스는 유일한 값을 가지는 칼럼에 대해 생성하는 인덱스로 모든 인덱스 키는 테이블의 하나의 행과 연결

☞ 사 용 예

  부서 테이블에서 name 칼럼을 고유 인덱스로 생성하여라. 단, 고유 인덱스의 이름을 idx_dept_name으로 정의한다

비고유 인덱스(non unique index)

  – 비고유 인덱스는 중복된 값을 가지는 칼럼에 대해 생성하는 인덱스로 하나의 인덱스 키는 테이블의 여러 행과

    연결될 수 있다. 

☞ 사 용 예

학생 테이블의 birthdate 칼럼을 비고유 인덱스로 생성하여라. 비고유 인덱스의 이름은 idx_stud_birthdate로 정의한다.

 

단일 인덱스, 결합 인덱스

• 단일 인덱스

  – 단일 인덱스는 하나의 칼럼으로만 구성된 인덱스이다. 

• 결합 인덱스

  – 결합 인덱스는 두 개 이상의 칼럼을 결합하여 생성하는 인덱스이다.

☞ 사용 예

학생 테이블의 deptno, grade 칼럼을 결합 인덱스로 생성하여라. 결합 인덱스의 이름은 idx_stud_dno_grade로 정의한다

 

DESCENDING INDEX

• DESCENDING INDEX

  – DESCENDING INDEX는 칼럼별로 정렬 순서를 별도로 지정하여 결합 인덱스를 생성하기 위한 방법이다. 

 

☞ 사용 예

학생 테이블의 deptno와 name 칼럼으로 결합 인덱스를 생성하여라. 단, deptno 칼럼을 내림차순으로 name 칼럼은

오름차순으로 생성하여라

함수 기반 인덱스(function based index)

• 함수 기반 인덱스

  – 함수 기반 인덱스는 오라클 8i 버전부터 지원하는 새로운 형태의 인덱스로 칼럼에 대한 연산이나 함수의 계산 결과를

    인덱스로 생성 가능
  – UPPER(column_name) 또는 LOWER(column_name) 키워드로 정의된 함수 기반 인덱스를 사용하면 대소문자

    구분 없이 검색할 수 있다.
  – 함수 기반 인덱스는 INSERT, UPDATE시에는 새로운 값을 인덱스에 추가

☞ 사 용 예

학생 테이블에서 학생들의 비만도 측정을 위해 표준 체중을 구하고자 한다. 표준 체중에 대한 함수 기반 인덱스를 생성하여라.
표준 체중을 구하는 공식은 ‘신장-100(cm)*0.9’이다.

☞ FUNCTION BASED INDEX를 생성하기 위한 필요조건

 

5. 인덱스 실행 경로 확인

인덱스 실행 경로 확인

• 실행 경로

  – SQL 명령문이 내부적으로 처리되는 경로

• SQL 명령문 실행 시

  – 검색 결과만 출력하므로 실행 경로가 인덱스를 이용하여 랜덤 액세스를 했는지 아니면 전체 테이블을 검색을 했는지

    프로그래머가 확인하기 어려움

인덱스 실행 경로 확인

☞ 사용 예

학과 테이블에서 학과 이름이 ‘정보미디어학부’인 학과 번호를 검색한 결과에 대한 실행 경로를 분석하여라. 

dname 칼럼에 고유 인덱스가 생성되어 있다.

인덱스 실행 경로 확인 2

☞ 사 용 예

학생 테이블에서 생일이 ’79/04/02’인 학생 이름을 검색한 결과에 대한 실행 경로를 분석하여라. 

birthdate 칼럼에 대해 비고유 인덱스가 생성되어 있다.

6. 인덱스 관리

인덱스 정보 조회

• USER_INDEXES 

  – 인덱스 이름과 유일성 여부 등을 확인

 

☞ 사 용 예

학생 테이블에 생성된 인덱스를 조회하여라

• USER_IND_COLUMNS

  – 인덱스 이름, 인덱스가 생성된 테이블 이름과 칼럼 이름 등을 확인

인덱스 삭제

• 인덱스 삭제
  – DROP INDEX문 사용

☞ 사 용 법

☞ 사 용 예

학생 테이블에 생성한 fidx_stud_no_name 인덱스를 삭제하여라

 

 

인덱스 재구성

• 인덱스 재구성

  – 인덱스 재구성은 인덱스를 정의한 테이블의 칼럼 값에 대해 변경 작업이 자주 발생하여, 불필요하게 생성된 인덱스 

    내부 노드를 정리하는 작업

☞ 사 용 법

☞ 사 용 예

학생 테이블에 생성된 stud_no_pk 인덱스를 재구성하여라