[DB] 4. SQL 고급(2) - 뷰, 인덱스

1) 뷰(VIEW)

뷰

: 하나 이상의 테이블을 합하여 만든 가상 테이블

 

• 뷰 특징
  • 실제 데이터 저장 x SELECT 쿼리 결과를 이름 붙여 재사용하는 객체다.
  • 기본적으로 읽기 전용
  • 데이터보안(특정 컬럼만 노출 가능), 재사용성(복잡 쿼리를 단순화),
  • 독립성(기본 테이블 구조 변경 시에도 뷰를 통해 동일한 인터페이스를 제공)

 

뷰 생성

 

사용법)

CREATE VIEW 뷰이름 [(열이름 [, ... n])]
AS SELECT 문

 

예시 코드 1)

 

- CREATE VIEW로 뷰를 만들고 FROM으로 불러 사용할 수 있다.

Book 테이블에서 bookname에 '축구'가 들어가는 값을 vw_Book 뷰로 설정한다.

[ ! ] 뷰 문에서 AS는 생략 불가능하다

 

-  뷰 생성 확인 

뷰를 생성하면 왼쪽 리스트의 해당 테이블 안에 표시된다.

 

예시 코드 2)

order 테이블에서 customer 테이블과 custid가 일치하고 book테이블 에서 bookid가 같은 값을 Vorders2 뷰에 해당하는 인자로 보여라.

 

코드의 낭독성을 위해 SELECT의 인자가 많다면 위와 같이 작성하는 것을 권장한다.

 

- 뷰 생성 확인

 

뷰 수정

 

사용법)

CREATE OR REPLACE VIEW 뷰이름 [(열이름 [, ... n])]
AS SELECT 문

 

예시 코드 1)

영국을 주소로 가지는 고객을 보여주는 뷰로 변경하여라.

 

뷰 삭제

 

사용법)

DROP VIEW 뷰이름 [, ... n];

 

예시 코드 1)

- 뷰를 삭제해서 확인할 수 없다.

vw_cutomer 뷰를 삭제하라.

 

뷰 활용

 

일반적으로는 읽기 전용이며

뷰에는 삽입, 수정, 삭제 연산이 제한적으로 수행된다.

활용 ➔ INSERT / UPDATE / DELETE

 

• 변경 불가능한 뷰의 예시
  • 기본 테이블의 기본키를 구성하는 속성이 포함되어 있지 않은 뷰
  • NOT NULL로 지정된 속성 포함x 
  • 집계 함수로 새로 계산된 내용 포함
  • DISTINCT/GROUP BY 절 포함

INSERT 활용

INSERT INTO 제품1 VALUES ('p58', 1000, '신선식품');
SELECT * FROM 제품1;

-- 'p58'(제품번호) 같은 기본키를 반드시 포함한다.

 

UPDATE 활용

UPDATE 제품1
SET 재고량 = 1500
WHERE 제품번호 = 'p08';

'제품1'은 '제품'테이블의 뷰이다. = '제품1'을 수정할 때, '제품' 테이블 값도 바뀐다.

[ ! ] 뷰를 수정하면 원본 테이블도 같이 수정된다.

 

DELETE 활용

DELETE FROM 제품1
WHERE 제품번호 = 'p08';

 

[ ! ] 뷰에서 데이터를 삭제하면 원본 테이블의 행도 같이 삭제된다.

 

실습 문제 풀이(Practice)

Q) 마당서점 데이터베이스를 이용하여 다음에 해당하는 뷰를 작성하시오.

1. 판매가격이 20,000원 이상인 도서의 도서번호, 도서이름, 고객이름, 출판사, 판매가격을 보여주는 highorders 뷰를 생성하시오.
2. 생성한 뷰를 이용하여 판매된 도서의 이름과 고객의 이름을 출력하는 SQL 문을 작성하시오.
3. highorders 뷰를 변경하고자 한다. 판매가격 속성을 삭제하는 명령을 수행하시오. 삭제 후 (2)번 SQL문을 다시 수행하시오.

 

1. 뷰 생성

POINT!
(1) CREATE VIEW의 AS는 생략 불가
(2) SELECT 인자가 많을 때는 수직적으로 나열한다.
(3) 작성순서에 유의하자. (SELECT ➔ FROM ➔ WHERE ➔ GROUP BY ➔ HAVING ➔ ORDER BY)

 

2. 뷰 사용

POINT!
VIEW를 사용할 땐 FROM으로 불러온다.

 

3. 뷰 수정

 

- 수정 전 highorders 데이터

 

- 수정 코드

POINT!
속성을 삭제하는 것 = 해당 속성을 제외하고 재정의한다.

 

- 수정 후 highorders 데이터

saleprice 속성이 삭제되었다.

 

- 수정 후 (2)의 SQL문 실행

---

2) 인덱스(index)

인덱스

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

일반적으로 RDBMS의 인덱스는 대부분 B-tree구조로 되어 있지만,

DBMS는 B+tree 기반이다.

B-tree vs. B+tree

B-tree: 각 노드에 키와 데이터 함께 저장 (= Balanced Tree)
B+tree: 내부 노드에는 키만, 실제 데이터는 리프 노드에만 저장 ➔ 데이터 조회속도가 빠르다.

 

 

+ 데이터베이스가 저장된 위치 알아보기

 

MYSQL의 인덱스 종류 (Oracle과는 다르다.)

클러스터 인덱스: 기본 키 생성시 자동으로 생성되고, 테이블당 하나만 존재한다. 데이터가 정렬되어 있다.
➔ 변경/삭제가 어렵다.
보조 인덱스: 클러스터 인덱스 외의 모든 인덱스를 말한다. 데이터를 정렬하지 않고, 테이블당 여러 개 만들 수 있다.
➔ 중간 삽입/삭제가 비교적 용이하다. empty값도 가능하다.

 

인덱스 생성 + SHOW 인덱스

 

SHOW INDEX 값

- SHOW 일부 속성 정리

1 Table	2 Non_unique	3 Key_name	4 Seq_in_index	5 Column_name	6 Collation	7 Cardinality	10 Null	11 Index_type
인덱스가 포함된 테이블의 이름	중복 허용 여부 (0: 불가,1: 가능)	인덱스의 이름	인덱스 내 컬럼 순서	인덱스가 설정된 컬럼 이름.	정렬 방식 (A: 오름차순, D: 내림차순)	고유 값의 개수 (추정치)	NULL 값 허용 여부	인덱스 데이터 구조 방식

 

인덱스 최적화

ANALYZE TABLE Book;

인덱스 삭제

DROP INDEX ix_Book ON Book;

 

EXPLAIN 인덱스

 

 

 

 

-- 모든 실습은 MariaDB, HeidiSQL을 사용해 이루어졌습니다.

 

 

[목차여기]