[DB] SQL 고급2

이현경·2026년 4월 10일
db

Database

목록 보기
8/13

3. 뷰

뷰(View)

  • 하나 이상의 테이블을 합하여 만든 가상의 테이블
  • 실제 데이터를 저장하지 않고, SELECT 쿼리 결과를 이름 붙여 재사용하는 객체
  • 문법
    CREATE VIEW 뷰이름 [(열이름 [,...n])]
AS SELECT 문
  • 특징
    • 데이터 보안: 특정 컬럼만 노출 가능
    • 재사용성: 복잡한 쿼리를 단순화
    • 독립성: 기본 테이블 구조 변경 시에도 뷰를 통해 동일한 인터페이스 제공
    • 읽기 전용이 기본: 일부 경우 업데이트 가능
  • 장점
    • 복잡한 SQL 단순화
    • 사용자별 맞춤 데이터 제공
    • 보안 및 관리 용이

3-1. 뷰의 생성

예1)

--  Book 테이블에서 ‘축구’라는 문구가 포함된 자료만 보여주는 뷰 만들기
CREATE VIEW vw_book
AS SELECT *
FROM book
WHERE bookname LIKE '%축구%';

-- 뷰를 사용한 SELECT 문
SELECT * FROM vw_book;

예2) 마당서점의 프로그래머가 보고서를 만들기 위해 Orders 테이블과 Customer 테이블, Book 테이블을 조인하거나 부속질의를 한다고 가정했을 때 Orders 테이블에 고객 이름과 도서 이름을 추가하는 방법 두 가지

① 실제 물리적인 테이블에 열을 추가하여 데이터를 넣는다

  • 사용 중인 프로그램을 수정해야 하며, 저장 용량도 증가한다.
  • 특정 속성을 수정할 경우 다른 테이블까지 연쇄적으로 영향을 미친다.
-- Orders 테이블에 고객 이름, 도서 이름을 담을 빈칸 추가
ALTER TABLE Orders ADD customer_name VARCHAR(50);
ALTER TABLE Orders ADD book_name VARCHAR(50);

-- Customer 테이블에서 이름을 퍼와서 Orders 테이블에 업데이트
UPDATE Orders O
SET customer_name = (SELECT name FROM Customer C WHERE C.custid = O.custid);

-- Book 테이블에서 책 이름을 퍼와서 Orders 테이블에 업데이트
UPDATE Orders O
SET book_name = (SELECT bookname FROM Book B WHERE B.bookid = O.bookid);

② Orders 테이블, Customer 테이블, Book 테이블을 조인한 후 가상의 테이블인 뷰 Vorders를 생성한다.

  • 실제 데이터를 디스크에 저장하지 않고, 뷰를 생성할 때 사용한 “SELECT 쿼리”만 DBMS에 정의
  • 뷰를 조회할 때마다 DBMS가 테이블에서 데이터를 읽어와 결과를 보여준다.
-- ver1: 암시적 조인
CREATE VIEW Vorders
AS SELECT O.orderid, O.custid, C.name, O.bookid, B.bookname, O.saleprice, O.orderdate
FROM customer C, orders O, book B
WHERE C.custid=O.custid AND B.bookid=O.bookid;

-- ver2: 명시적 조인
CREATE VIEW Vorders2 AS
SELECT O.orderid,
	O.custid,
	C.name,
	O.bookid,
	B.bookname,
	O.saleprice,
	O.orderdate
FROM orders O
JOIN customer C ON C.custid = O.custid
JOIN book B ON B.bookid = O.bookid;

예3)

-- 주소에 '대한민국'을 포함하는 고객들로 구성된 뷰를 만들고 조회
CREATE VIEW vw_customer
AS SELECT *
	 FROM customer
	 WHERE address LIKE '%대한민국%';

뷰의 장점

  • 편리성(및 재사용성)
    • 자주 사용되는 복잡한 질의를 뷰로 미리 정의해 놓을 수 있음
    • 복잡한 질의를 간단히 작성
  • 보안성
    • 사용자별로 필요한 데이터만 선별하여 보여줄 수 있고, 중요한 질의의 경우 질의 내용을 암호화할 수 있음
    • 개인정보(주민번호)나 급여, 건강 같은 민감한 정보를 제외한 테이블을 만들어 사용
  • 독립성
    • 원본 테이블의 구조가 변해도 응용에 영향을 주지 않도록 함
    • 논리적 데이터 독립성 제공 방법

뷰의 특징

  • 원본 데이터 값에 따라 같이 변함
  • 독립적인 인덱스 생성이 어려움
  • 삽입, 삭제, 갱신 연산에 많은 제약이 따름



3-2. 뷰의 수정

뷰를 수정하는 문장

  • CREATE VIEW 문에 OR REPLACE 명령을 더하여 작성
CREATE OR REPLACE VIEW 뷰이름 [(열이름 [,...n])]
AS SELECT 문

예1)

-- vw_customer 뷰를 영국을 주소로 가진 고객으로 변경
-- phone 속성은 포함x
CREATE OR REPLACE VIEW vw_customer(custid, NAME, address)
AS SELECT custid, NAME, address
	 FROM customer
	 WHERE address LIKE '%영국%';

  • 뷰가 필요 없어졌다면 DROP 문을 사용하여 뷰를 삭제함
DROP VIEW 뷰이름 [,...n];

예2)

-- vw_customer 뷰 삭제
DROP VIEW vw_customer;



3-3. 뷰의 활용

일반적으로 뷰는 읽기 전용이지만 DISTINCT, GROUP BY 등의 제약이 없으면 변경 가능하다. 다만 실제 수정사항은 기본 테이블에 수행된다.

변경 불가능한 뷰의 특징

이 뷰를 통해서 원본 테이블의 어느 행, 어느 칸을 정확히 고쳐야 할지 100% 추적할 수 없을 때

  • 기본 테이블의 기본키가 포함되지 않은 뷰
  • 기본 테이블의 NOT NULL 속성이 포함되지 않은 뷰
  • 집계 함수로 계산된 내용을 포함하는 뷰
  • DISTINCT 키워드를 포함하여 정의한 뷰
  • GROUP BY 절을 포함하여 정의한 뷰
  • 여러 개의 테이블을 조인하여 정의한 뷰




4. 인덱스

4-1. 데이터베이스의 물리적 저장

테이블을 생성하고 테이블에 데이터를 저장할 때 DBMS는 데이터를 어디에 어떻게 저장할까? → DBMS만의 고유한 방식으로 저장하여 관리한다.

  • 액세스 시간 = 디스크의 입출력 시간
    • 액세스 시간은 데이터의 저장 및 읽기에 많은 영향을 끼침
    • 액세스 시간의 계산
      • 탐색 시간(액세스 헤드를 트랙에 이동시키는 시간)
      • 회전 지연 시간(섹터가 액세스 헤드에 접근하는 시간)
      • 데이터 전송 시간(데이터를 주기억장치로 읽어오는 시간)
    • DBMS가 하드디스크에 데이터를 저장하고 읽어올 때, 속도 문제가 발생함
      • 컴퓨터 시스템에서 처리되는 연산 속도는 빠르지만, 디스크의 액세스 속도는 상대적으로 느리기 때문
      • DBMS는 주기억장치에 사용하는 공간 중 일부를 버퍼 풀로 만들어 사용
  • 데이터 검색 시 DBMS는 버퍼 풀에 저장된 데이터를 우선 읽어들인 후 작업을 진행
    • DBMS는 데이터베이스별로 하나 이상의 데이터 파일을 생성함
    • 테이블은 생성 시 정의된 내용에 따라 논리적으로 구분 지어져 각각의 데이터 파일에 저장됨
    • MySQL의 저장 장치 엔진(Engine)은 플러그인 방식으로 선택할 수 있으며 InnoDB 엔진이 기본으로 설치되어 있음
    • 데이터베이스가 저장된 위치 알아보기
      SHOW VARIABLES LIKE 'datadir';



4-2. 인덱스와 B-tree

  • 인덱스(index) : 데이터를 쉽고 빠르게 찾을 수 있도록 만든 데이터 구조
  • 일반적인 RDBMS의 인덱스는 대부분 B-tree 구조로 되어 있음
  • B-tree (Balanced Tree)
    • 데이터의 검색 시간을 단축하기 위한 자료구조
    • B-tree의 각 노드는 키 값과 포인터를 가짐
    • 루트 노드, 내부 노드, 리프 노드로 구성
    • 리프 노드가 같은 레벨에 존재하는 균형 트리
    • 리프 노드에 해당 데이터의 저장 위치에 대응하는 정보가 있어 빠른 검색 가능
      • rowid (RID, Row, Identify, 테이블의 행에 대한 논리적 위치)

  • 최대 3개의 자식노드를 갖는 B-tree에서 3 찾기
    • B-tree에서 검색은 루트 노드에서부터 값을 비교하여 중간 단계인 내부 노드에서 해당 노드를 찾고, 이런 단계를 거쳐 최종적으로 마지막 레벨인 리프 노드에 도달함
      • 루트 노드 4와 검색할 값 3 비교 → 왼쪽 포인터로 이동 → 2와 3 비교 → 오른쪽 포인터로 이동 → 성공(검색 중지)
    • B-tree는 데이터를 검색할 때 특유의 트리 구조를 이용하기 때문에 한 번 검색할 때마다 검색 대상이 1/m (m은 자식의 개수)로 줄어 접근 시간이 적게 걸림
    • 100만 개의 투플을 가진 데이터도 디스크 블록을 서너 번 읽으면 찾을 수 있음

  • 인덱스의 특징
    • 인덱스는 테이블에서 한 개 이상의 속성을 이용하여 생성함
    • 빠른 검색과 함께 효율적인 레코드 접근이 가능함
    • 순서대로 정렬된 속성과 데이터의 위치만 보유하므로 테이블보다 작은 공간을 차지함
    • 저장된 값들은 테이블의 부분집합이 됨
    • 일반적으로 B-tree 형태 기반의 최적화 버전을 사용 → DBMS는 B+tree
    • 데이터에서 수정, 삭제 등의 변경이 발생하면 인덱스를 재구성해야 함

B-tree와 B+tree

  • B-tree
    • 구조: 각 노드에 키와 데이터가 함께 저장됨
    • 검색: 루트에서 시작해 키를 비교하며 내려감
    • 리프 노드: 리프에도 키와 데이터가 들어있음
    • 특징: 삽입·삭제 시 항상 균형을 유지→ 성능 안정적
    • 단점: 범위 검색 시 리프 노드끼리 연결이 없어 효율이 떨어짐
  • B+tree
    • DBMS 인덱스에서 가장 많이 쓰이는 구조.
    • 구조: 내부 노드에는 키만 저장, 실제 데이터는 리프 노드에만 저장
    • 리프 노드: 모든 리프 노드가 연결 리스트로 이어져 있음
    • 검색: 특정 키 검색은 B-Tree와 동일하게 O(log n)
    • 특징: 리프 노드 연결 덕분에 연속된 데이터를 빠르게 조회 가능



4-3. MySQL 인덱스

  • MySQL의 인덱스는 클러스터 인덱스와 보조 인덱스로 나뉨
  • 클러스터 인덱스
    • 테이블 자체가 인덱스 구조로 되어 있어 데이터가 인덱스 키 순서대로 물리적으로 저장됨
    • 기본키 생성 시 자동으로 생성됨
    • 특징
      • 테이블당 하나만 존재
      • 키 값이 정렬되어 특정값, 범위 검색에 모두 유리
      • 기본키 기반 검색이 매우 빠름
      • 검색 성능은 뛰어나지만, 키 변경·삽입 시 성능 부담
  • 보조 인덱스 (Secondary Index)
    • 클러스터 인덱스가 아닌 모든 인덱스
    • InnoDB에서는 PK가 클러스터 인덱스이고, 나머지 인덱스는 모두 보조 인덱스
    • 특징
      • 데이터 자체를 정렬하지 않고 테이블당 여러 개 만들 수 있다.
      • 인덱스 키와 해당 레코드의 기본키 값을 저장
        • 인덱스의 리프 노드는 테이블상의 데이터 위치를 지정하는 rowid 저장
        • rowid <block번호-block 내 row가 위치한 순번>
  • 클러스터 인덱스와 보조 인덱스를 동시에 사용하는 검색
    • Book 테이블에서 bookid를 클러스터 인덱스로, bookname을 보조 인덱스로 사용
      • bookid와 bookname 모두 빠른 검색을 필요로 하는 경우 유리
      • 예) SELECT * FROM Book WHERE bookname = '축구의 역사';
        • 보조 인덱스 탐색: '축구의 역사'를 탐색
        • PK 획득: '축구의 역사' 옆에 적힌 기본키 정보(bookid = 3)를 획득
        • 클러스터 인덱스 탐색: 얻어낸 bookid = 3을 가지고 클러스터 인덱스 탐색
        • 최종 데이터 획득: 3번 페이지에 있는 가격, 출판사 등 모든 데이터를 획득
    • 클러스터 인덱스로 저장된 데이터의 순서를 가능한 유지하면서, 데이터 삽입과 삭제에 대한 인덱스 관리 비용을 줄일 수 있다



4-4. 인덱스의 생성

  • 의미 없이 인덱스를 생성하면 검색 속도가 더 느려지고 저장공간만 낭비한다.
  • 인덱스를 생성하기 전 고려사항

    • 인덱스는 WHERE 절, 조인에 자주 사용되는 속성이어야 함

    • 속성의 선택도가 낮을 때 유리함 (속성의 모든 값이 다른 경우)

    • 단일 테이블에 인덱스가 많으면 속도가 느려질 수 있음 (테이블당 4~5개 정도 권장)

    • 속성이 가공되는 경우에는 사용하지 않음

      CREATE [UNIQUE] INDEX [인덱스이름]
ON 테이블이름 (컬럼 [ASC | DESC] [{,컬럼 [ASC | DESC]} ...]);

    • [UNIQUE]는 테이블의 속성값에 대하여 중복이 없는 유일한 인덱스를 생성하는 것을 말함

    • [ASC | DESC]는 컬럼 값의 정렬 방식을 의미함

-- Book 테이블의 bookname 열을 대상으로 인덱스 ix_book을 생성
CREATE INDEX ix_book ON book(bookname);

-- Book 테이블의 publisher, price 열을 대상으로 인덱스 ix_book2를 생성
CREATE INDEX ix_book2 ON book(publisher, price);
  • 생성된 인덱스는 SHOW INDEX 명령어로 확인 가능

  • SQL문 앞에 EXPLAIN 키워드를 붙이면 실행 계획이 표시됨

  • 실행 계획에서 확인할 수 있는 내용
    • id: 실행 순서 단계
    • select_type: SELECT 유형 (단순, 서브 쿼리 등)
    • table: 접근하는 테이블 이름
    • type: 조인 방식
      • ALL: full table scan이라는 뜻 → 최악
      • index: full index scan이라는 뜻 → 낫 굿..
      • range: 인덱스를 타서 특정 범위만 빠르게 훑었다는 뜻 → 굿
      • ref, eq_ref: 인덱스를 타서 정확한 값 하나만 짚어냈다는 뜻 → 완벽
    • possible_keys: 사용할 수 있는 인덱스 목록
    • key: 실제로 사용된 인덱스
    • rows: 예상 읽을 행(row) 수
    • Extra: 추가 정보 (Using index, Using where 등)



4-5. 인덱스 재구성과 삭제

  • B-tree 인덱스는 데이터의 수정·삭제·삽입이 잦으면 노드의 갱신이 주기적으로 일어나 단편화 현상이 나타남
    • 단편화 현상: 데이터의 잦은 변경으로 인해 인덱스 내부에 쓰지 않는 빈 공간이 생기고 데이터가 흩어져 검색 성능이 저하되는 현상
    • 이럴 경우 ANALYZE TABLE 명령을 통해 인덱스를 재구성
  • MySQL에서는 주로 OPTIMIZE TABLE 테이블명; 으로 최적화
-- Book 테이블의 인덱스를 최적화
ANALYZE TABLE book;

  • 하나의 테이블에 인덱스가 많으면 데이터베이스 성능에 좋지 않은 영향을 미침
  • 사용하지 않는 인덱스는 삭제해야 함
  • 인덱스의 삭제는 DROP INDEX 명령을 사용
-- 인덱스 ix_book 삭제
DROP INDEX ix_book ON book;

profile
이현경
커피 한 잔의 여유를 아는 품격있는 여자
이전 포스트

[프로그래머스] SQL 문제 풀이

다음 포스트

[DB] 데이터베이스 프로그래밍

0개의 댓글