#03. 뷰와 인덱스 (정의, 사용법)

[[_TOC_]]

1. VIEW (뷰)

VIEW(뷰)란?

• SELECT문 사용 시에 "자주" 사용되거나, "복잡한" 조인 조건인 경우,
• 사용의 편의를 위해 생성하는
• 가상의 테이블

1-1. 뷰의 정의

테이블과 뷰의 차이

• 테이블 : 전체 조직의 관점에서 최적의 저장 단위로 생성
• 뷰 : 개인의 관점에서 사용자의 편의를 위해 자유롭게 생성

특징

• 실제 데이터를 저장하지 않는 "가상 테이블"
• 실제 테이블처럼 사용하도록 만든 "데이터베이스 객체"
• 뷰는 데이터를 저장하지 않음
• 주로 기반 테이블로부터 정의되지만, 또 다른 뷰를 기반으로 정의될 수 있음
• 뷰 검색은 실제로는 뷰의 기반 테이블에 대한 검색으로 변환되어 수행된다.
  • => 생성된 뷰의 데이터는 snapshot처럼 소정된 값이 아닌,
  • 기반 테이블의 변경되는 정보를 실시간으로 반영하여 변경된다.
• 뷰 검색은 제한이 없지만, 뷰 변경은 조건에 의해 특정한 경우로 한정된다.

장점

1. 편의성
   • 뷰를 미리 생성해두면 복잡한 쿼리를 다시 작성할 필요가 없다
2. 보안성
   • 물리적 테이블에는 민감한 정보가 존재할 수 있기 때문에,
   • 뷰를 통해 사용자에 따른 데이터 접근 제한을 둘 수 있음

3- 재사용성

• 작성 빈도가 높거나 빈번하게 참조되는 검색 결과를 미리 뷰로 정의

4- 독립성

• 스키마 변경(기반 테이블 구조의 변경)되어도, 뷰를 정의하는 쿼리만 수정하면 되기 때문에
• 응용 프로그램에 영향을 주지 않으면서 유연하게 기반 릴레이션의 스키마 변경이 가능

1-2. 사용법

생성

CREATE VIEW 뷰이름(열리스트)
AS SELECT {SELECT문}

ex)

• 특정 열만 생성 시

CREATE VIEW V1_ACTIVE_USERS(USER_ID, STATUS, USER_NAME)
AS SELECT (USER_ID, STATUS, USER_NAME) FROM USERS WHERE STATUS='A'

• 모든 열 생성 시

CREATE VIEW V1_ACTIVE_USERS
AS SELECT * FROM USERS WHERE STATUS='A'

삭제

DROP VIEW 뷰이름

ex)

DROP VIEW V1_ACTIVE_USERS

2. INDEX (인덱스)

Index(인덱스)란?

• 테이블 안의 필요한 데이터를 쉽고 빠르게 찾을 수 있도록 만든
• 데이터베이스 객체

2-1. 인덱스의 필요성

• 테이블의 데이터는 디스크(보조기억장치)에 저장됨
• SQL 실행 시, 디스크로부터 필요한 데이터를 탐색
• 필요한 블록(페이지)을 반복해서 주기억장치로 읽어옴
  • 인덱스가 없는 경우 디스크에 저장된 데이터의 모든 행을 순차적으로 주기억장치로 읽어와서, 비교를 통해 데이터를 검색
  • 디스크 접근 속도는 매우 느리기 때문에, 데이터의 크기가 클수록
    • 데이터 I/O가 많아지며,
    • 메모리에 캐싱할 수 있는 페이지(블록)의 수가 줄어들 수 있다.
  • 인덱스를 생성하면,
    • 인덱스를 통해 PK를 찾는다.
    • 이 PK를 통해 레코드를 찾기 때문에 필요한 일부 행만 주기억장치로 읽어 들여 비교하기 때문에 매우 속도가 빨라진다.

2-2. 사용법

생성

CREATE [REVERSE] [UNIQUE] INDEX 인덱스이름
ON 테이블이름(열이름)

• REVERSE : 인덱스를 역순으로 생성
• UNIQUE : 테이블의 컬럼값에 중복이 없는 유일한 인덱스를 생성 => 인덱스가 설정되는 컬럼값은 중복값을 허용하지 않음

'USERS'테이블의 'USER_ID' 컬럼을 대상으로 인덱스 'idx_USERS' 생성

CREATE INDEX idx_USERS
ON USERS(USER_ID)

삭제

DROP INDEX 인덱스이름

ex)

DROP INDEX idx_USERS

2-3. 인덱스 생성 필요 확인!

인덱스 생성이 필요한 경우

• 기본키와 외래키의 경우 => 대부분의 DBMS는 기본키에 대해 자동으로 인덱스 생성을 하고있다
• WHERE 조건식에 자주 사용되는 컬럼인 경우
• JOIN 조건식에 자주 사용되는 컬럼인 경우
• ORDER BY 절이나 GROUP BY 절에 자주 사용되는 컬럼인 경우
• (bad) - 가변길이 문자형, 실수형, 날짜형 컬럼 < (good) - 정수형, 고정길이 문자형 컬럼에 인덱스 생성
• 하나의 테이블 당 3-5개 정도의 인덱스가 적합

인덱스 생성이 불필요한 경우

• 갱신이 빈번한 컬럼의 경우
• 집계 함수, 내장 함수를 적용하여 컬럼 값을 변형하는 경우
• 도메인이 작아 선택도가 높은 경우 -> 인덱스의 key의 범위가 큰 경우
• 범위 검색인 경우
• 테이블의 행 개수가 별로 없는 경우