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