Computer Sciences/Database

15. Index and View

jeidiiy 2021. 5. 23. 11:00

Index

  • 추가적인 쓰기 작업과 저장 공간을 활용하여 데이터베이스 테이블의 검색 속도를 향상시키기 위한 자료구조

문법

CREATE INDEX YearIndex ON Movie(studioName, year);
DROP INDEX YearIndex;
  • 인덱스를 사용하지 않은 컬럼을 조회한다면 전체를 탐색해야 하기 때문에 처리 속도가 떨어짐

Selection of Indexes

  • 인덱스 선택은 데이터베이스 설계자에 의한 트레이드 오프가 요구됨
  • 중요한 사실
    • 인덱스의 존재는 쿼리 스피드를 매우 높여줌
    • 모든 인덱스는 삽입, 삭제, 갱신을 더 복잡하고 시간이 소요되게 함
    • 부가적인 공간을 차지함
  • 처음 설계할 때는 인덱스 없이 설계하고 이후에 자주 사용되는 쿼리에 대해 인덱스를 생성하는 방식이 좋음

Index 관리

DBMS는 index를 항상 최신의 정렬된 상태로 유지해야 원하는 값을 빠르게 탐색할 수 있음. 때문에 인덱스가 적용된 컬럼에 INSERT, UPDATE, DELETE가 수행된다면 각각 다음과 같은 연산을 추가적으로 해주어야 하며 그에 따른 오버헤드가 발생함.

  • INSERT: 새로운 데이터에 대한 인덱스를 추가함
  • DELETE: 삭제하는 데이터의 인덱스를 사용하지 않는다는 작업을 진행함
  • UPDATE: 기존의 인덱스를 사용하지 않음 처리하고, 갱신된 데이터에 대해 인덱스를 추가함

사용하지 않은 인덱스는 바로 제거해주어야 성능에 문제가 생기지 않음

Index 장단점

  • 장점
    • 테이블 조회 속도, 그에 따른 성능 향상
    • 시스템 부하 감소
  • 단점
    • 인덱스 관리를 위한 저장공간 필요
    • 인덱스 관리를 위한 추가 작업 필요
    • 잘못 사용할 경우 오히려 성능 저하
      • CREATE, DELETE, UPDATE가 빈번히 일어나는 속성에 인덱스를 사용하는 경우

인덱스 사용의 좋은 사례

  • 대규모 테이블
  • SELECT를 주로 사용하는 컬럼
  • JOIN, WHERE, ORDER BY에 자주 사용되는 컬럼
  • 데이터의 중복도가 낮은 컬럼

View

문법

CREATE VIEW <view-name> AS <view-definition>;
DROP VIEW <view-name>;

예제

// 뷰 생성
CREATE VIEW ParamountMovie AS
	SELECT title, year
	FROM Movie
	WHERE studioName = 'Paramount';
SELECT title
FROM ParamountMovie
WHERE year = 1997;

// 아래 질의와 위 질의는 같음

SELECT title
FROM Movie
WHERE year = 1997
AND studioName = 'Paramount';
// 기본적인 뷰 사용
CREATE VIEW MovieProd AS
SELECT title, name
FROM Movie JOIN MovieExec
	ON producerCNo = certNo;
    
SELECT name
FROM MovieProd
WHERE title = 'Gone With the Wind';
// 속성 이름 변경
CREATE VIEW MovieProd(movieTitle, prodName) AS
    SELECT title, name
    FROM Movie JOIN MovieExec
        ON producerCNo = certNo;
        
SELECT prodName
FROM MovieProd
WHERE movieTitle = 'Gone With the Wind';
// WITH Query
WITH MovieProd(movieTitle, prodName) AS (
    SELECT title, name
    FROM Movie JOIN MovieExec
        ON producerCNo = certNo
)
SELECT prodName
FROM MovieProd
WHERE movieTitle = 'Gone With the Wind';

뷰를 통한 수정

  • 변경이 불가능하지는 않으나 조건이 까다롭고 활용도가 낮음
    • 하나의 테이블로만 만들어진 뷰여야 함
    • SELECT 문을 통해 가져온 데이터가 키 값이어야 함

뷰를 사용하는 이유

  • 보안성, 편의성, 증가
  • 속도 및 성능 향상

WITH Query(Common Table Expression, CTE)

  • 바로 다음에 오는 쿼리에서 사용할 수 있도록 테이블을 만들 수 있는 쿼리
  • 많은 DBMS에서 지원함
WITH ParamountMovie AS (
    SELECT title, year
    FROM Movie
    WHERE studioName = 'Paramount'
)
SELECT title
FROM ParamountMovie
WHERE year = 1997;
// RETUING 문을 사용하면 처리되는 결과를 테이블로 리턴함
// 아래는 지워진 튜플들로 만들어진 테이블을 리턴하게 됨
WITH moved_rows AS (
    DELETE FROM products
    WHERE
        'date' >= '2010-10-01' AND
        'date' < '2010-11-01'
    RETURNING * 
)
INSERT INTO products_log SELECT * FROM moved_rows;
// WITH Query를 사용하면 재귀를 적용할 수 있음
WITH RECURSIVE t(n) AS (
    VALUES (1)
    UNION ALL
    SELECT n+1 FROM t WHERE n < 5
)
SELECT n FROM t;

Materialized View

문법

// 뷰 생성
// WITH DATA가 기본값이고 생략될 수 있음
// NO 옵션을 주면 데이터를 채우지 않음
CREATE MATERIALIZED VIEW <view-name> AS <view-definition> [WITH [NO] DATA];

// 데이터 로딩
// 기본적으로 MATERIALIZED VIEW는 데이터를 갱신하지 않음
// 수정될 경우 원본 테이블과 데이터가 일치하지 않게 됨
// REFRESH를 사용하면 최신 데이터를 로딩하여 적용함
REFRESH MATERIALIZED VIEW <view-name>;

// 뷰 삭제
DROP MATERIALIZED VIEW w<view-name>;

View vs Materialized View

  • View
    • 원본 테이블의 데이터를 표현하는 가상 테이블
    • 테이블에 접근할 때마다 쿼리가 실행됨
    • 항상 최신 데이터를 사용함
  • Materialized View
    • 복잡한 쿼리의 결과를 캐싱하고 주기적으로 이 결과를 REFRESH할 수 있음
    • 빠른 데이터 접근이 필요한 많은 경우에 유용함
      • 데이터 웨어하우스
      • 비즈니스 정보 애플리케이션
    • 최신 데이터가 필요하지 않은 데이터를 써도 되면서 성능을 필요로 할 때 유용함
    • 데이터는 View보다 오래될 수 있음

 

Uploaded by Notion2Tistory v1.1.0