3. 뷰

뷰(View) : 하나 이상의 테이블을 합하여 만든 가상의 테이블
읽기 전용이 기본, 일부 경우 업데이트 가능

CREATE VIEW 뷰이름 ( ... ) AS
SELECT ...

장점

• 복잡한 SQL 단순화
• 사용자별 맞춤 데이터 제공 : 특정 컬럼만 노출 가능
• 보안 및 관리에 용이

인라인 뷰 vs 뷰

구분	일반 뷰 (View)	인라인 뷰 (Inline View)
저장여부	DB 객체로 저장됨 (영구적)	SQL 문 실행 시에만 생성됨 (일회용)
재사용성	다른 쿼리에서도 이름으로 재사용 가능	해당 쿼리 내에서만 사용 가능
생성권한	CREATE VIEW 권한이 필요함	별도 권한 없이 SELECT 권한만 있으면 가능

뷰의 생성

CREATE VIEW 뷰이름 AS
SELECT ...
FROM ...
WHERE ...

장점

• 편리성 : 자주 사용되는 복잡한 질의를 뷰로 미리 정의해놓을 수 있음
• 보안성 : 사용자별로 필요한 데이터만 선별하여 보여줄 수 있음, 중요한 질의는 내용 암호화 가능
• 독립성 : 원본 테이블의 구조가 변해도 응용에 영향 주지 않을 수 있음

특징

• 원본 데이터 값에 따라 같이 변함
• 독립적인 인덱스 생성 어려움
• 삽입, 삭제 갱신 연산에 많은 제약 존재 (조회가 기본이기 때문)

예)
Q. 주소에 '대한민국'을 포함하는 고객들로 구성된 뷰를 만들고 조회하시오. 뷰의 이름은 vw_Customer로 설정하시오.

뷰의 수정

뷰 수정

CREATE OR REPLACE VIEW 뷰이름 AS
SELECT ...

뷰 삭제

DROP VIEW 뷰이름;

예)
Q. vw_Customer 뷰를 영국을 주소로 가진 고객으로 변경하시오. phone 속성은 필요 없으므로 포함하지 마시오

뷰의 활용

일반적으로 뷰는 읽기 전용!!

뷰의 활용 : INSERT, UPDATE, DELETE 문
뷰에 대한 삽입, 수정, 삭제 연산이 제한적으로 수행됨
주의 ) 실제로 기본 테이블에 수행되므로 결과적으로는 기본 테이블이 변경됨, 변경 불가능한 뷰도 존재

변경 불가능한 뷰

• 기본 테이블의 기본키를 구성하는 속성이 포함되어 있지 않은 뷰
• 기본 테이블에서 NOT NULL로 지정된 속성이 포함되어 있지 않은 뷰
• 기존 내용이 아니라 집계 함수로 새로 계산된 내용을 포함하는 뷰
• DISTINCT 또는 GROUP BY 이 포함된 뷰
• 여러 개의 테이블을 조인한 뷰(대다수)

실습

Q. 마당서점 데이터베이스를 이용하여 다음에 해당하는 뷰를 작성하시오
(1) 판매가격이 20,000원 이상인 도서의 도서번호, 도서이름, 고객이름, 출판사, 판매가격을 보여주는 highorders 뷰를 생성하시오

(2) 생성한 뷰를 이용하여 판매된 도서의 이름과 고객의 이름을 출력하는 SQL문을 작성하시오

(3) highorders 뷰를 변경하고자 한다. 판매가격 속성을 삭제하는 명령을 수행하시오. 삭제 후 (2)번 SQL문을 다시 수행하시오

4. 인덱스

데이터베이스의 물리적 저장

엑세스 시간

• 디스크의 입출력 시간

• 엑세스 시간 계산 = 탐색시간 + 회전 지연시간 + 데이터 전송시간
  탐색시간 : 엑세스 헤드를 트랙에 이동시키는 시간
  회전 지연시간 : 섹터가 엑세스 헤드에 접근하는 시간
  데이터 전송시간 : 데이터를 주기억장치로 읽어오는 시간

• DBMS가 하드 디스크에 데이터를 저장하고 읽어올 때, 속도 문제 발생
  컴퓨터 시스템 내의 연산 속도는 빠르지만, 디스크의 엑세스 속도는 상대적으로 느림

DBMS

• 데이터 검색 시 DBMS는 버퍼 풀에 저장된 데이터를 우선 읽어들인 후 작업을 진행
• DBMS는 데이터베이스별로 하나 이상의 데이터 파일을 생성함
• 테이블 생성 시 정의된 내용에 따라 구분되어 각각의 데이터 파일에 저장됨
• 데이터베이스가 저장된 위치는 SHOW VARIABLES LIKE 'datadir';로 확인 가능

인덱스와 B-tree

인덱스

인덱스 : 데이터를 쉽고 빠르게 찾을 수 있도록 만든 데이터 구조

• 기본키 -> 인덱스
• 빠른 검색, 효율적 레코드 접근 가능
• 테이블보다 작은 공간 차지함
• 데이터에서 수정, 삭제 등의 변경이 발생하면 인덱스를 재구성해야함
• 주로 B-tree 구조로 되어 있음(DBMS는 B+tree 사용)

B-tree

• 데이터 검색 시간 단축 위한 자료구조
• 각 노드는 키값과 포인터 가짐 (키와 데이터 함께 저장됨)
• 루트노드 / 내부노드 / 리프노드로 구성
• 리프노드에 해당 데이터의 저장 위치에 대응하는 정보가 있어 빠른 검색 가능
• 삽입, 삭제 시 항상 균형 유지 -> 성능 안정적
• 범위 검색 시 리프노드끼리 연결이 없어 효율 떨어짐

B-tree에서의 검색 : 루트에서 시작해 키를 비교하며 내려감
루트노드에서 값 비교 -> 내부노드에서 해당 노드 찾기 -> ... -> 리프노드에 도달
트리 구조 이용하기 때문에 한번 검색할 때마다 검색 대상이 1/n으로 줄어들어 접근 시간이 적게 걸림

B+tree

DBMS 인덱스에서 가장 많이 쓰이는 구조

내부노드 -> 키 저장 / 리프노드 -> 실제 데이터 저장
모든 리프노드가 연결 리스트로 이어져 있음 -> 연속된 데이터를 빠르게 조회 가능
특정 키 검색은 B-tree와 동일, O(log n)

MySQL 인덱스

클러스터 인덱스

• 테이블 자체가 인덱스 구조 -> 데이터가 인덱스 키 순서대로 물리적으로 저장됨
• 기본키 생성시 자동으로 생성됨
• 테이블당 하나만 존재
• 키값 정렬됨 -> 특정값, 범위 검색에 모두 유리
• 키 변경, 삽입 시 성능 부담

보조 인덱스

• 클러스터 인덱스가 아닌 모든 인덱스
• 데이터 자체를 정렬하지 않고 테이블당 여러 개 만들 수 있음
• 인덱스키와 해당 레코드의 기본키값 저장

클러스터 + 보조

두 가지 데이터 모두 빠른 검색을 필요로 하는 경우 유리
클러스터 인덱스로 저장된 데이터의 순서를 가능한 유지하면서
(클러스터 인덱스의 장점)
동시에 데이터 삽입, 삭제에 대한 인덱스 관리 비용을 줄일 수 있음
(보조 인덱스의 장점)

인덱스의 생성

CREATE INDEX 인덱스이름 ON 테이블이름;

인덱스를 생성하기 전 고려사항

• WHERE절에 자주 사용되는 속성인지
• 조인에 자주 사용되는 속성인지
• 테이블당 4~5개 권장
• 속성이 가공되는 경우 사용 X
• 속성의 선택도가 낮을 때(속성의 모든 값이 다른 경우) 유리

생성된 인덱스는 SHOW INDEX 명령어로 확인 가능

+) SQL문 앞에 EXPLAIN 키워드 붙이면 실행 계획 표시됨
-> id(실행순서) / select_type / table / type(조인방식) / possible_keys / key / rows / extra

인덱스의 재구성과 삭제

B-tree 인덱스는 데이터 수정, 삭제, 삽입이 잦으면 노드 갱신이 주기적으로 일어남
-> 단편화 현상 발생
=> ANALYZE 문법 통해 인덱스 다시 생성