백엔드 스터디/데이터베이스

RDB INDEX & RDB VIEW

g*g 2024. 7. 10. 23:08

INDEX

index를 쓰는 이유

- 조건을 만족하는 튜플(들)을 빠르게 조회하기 위해

- 빠르게 정렬(order by)하거나 그룹핑(group by) 하기 위해

 

PLAYER

id name team_id backnumber
... ... ... ...

 

mysql> SELECT * FROM player WHERE name ='Sonny';

// index 적용
mysql> CREATE INDEX player_name_idx ON player(name);

 

mysql> SELECT * FROM player WHERE team_id = 105 and backnumber = 7;

// index 적용
mysql> CREATE UNIQUE INDEX team_id_backnumer_idx ON player(team_id, backnumer);

 

 

 

table생성시 index를 걸어주는 방법

mysql> CREATE TABLE player (
	-> id 			INT PRIMARY KEY,
    -> name 		VARCHAR(20) NOT NULL,
    -> taem_id		INT,
    -> backnumber 	INT,
    -> INDEX player_name_idx(name),
    -> UNIQUE INDEX team_id_backnumer_idx(team_id, backnumer);
);

- 대부분의 rdbms에서 primary key에는 index가 자동 생성된다 (index를 만들기위해 따로 명시하지 않아도 된다)

- 두 개이상의 애트리뷰트로 구성된 인덱스(team_id, backnumer) → multicolumn index, composite index라고도 한다

 

 

테이블의 인덱스 정보들을 알려주는 명령어 

mysql> SHOW INDEX FROM player;

 

 

사용되는 쿼리에 맞춰서 적절하게 인덱스를 걸어줘야 쿼리가 빠르게 처리 될 수 있다

 

index는 막 만들어도 괜찮을까?

- 테이블에 write(insert, update, delete)할 떄마다 인덱스도 변경 발생

- 추가적인 저장 공간 차지

- 불필요한 인덱스를 만들지 말자

 

 

VIEW(가상의 테이블)

뷰는 원칙적으로 하나 이상의 기본 테이블로부터 유도된 이름을 가진 가상 테이블을 말한다.

기본 테이블은 물리적으로 구현되어 데이터가 실제로 저장되지만, 뷰는 물리적으로 구현되어 있지 않다.

다만 뷰의 정의만 시스템 내에 저장하였다가 필요 시 실행 시간에 테이블을 구축하는 것이다. 데이터 검색에 있어서 시스템은 일반 사용자게에 뷰와 기본 테이블 사이에 차이가 없게 만들어 주지만 뷰에 대한 데이터 갱신 연산에는 약간의 제한이 있다. 뷰는 궁극적으로 기본 테이블로부터 유도되지만 일단 정의된 뷰가 또 다른 뷰를 만들 수 있다. 이러한 정의된 뷰도 기본테이블을 근거로 해야한다. 기본 테이블 없이는 뷰가 생성될 수 없다.

 

뷰 생성

뷰를 생성한다는 것은 실제 시스템이 직접 물리적으로 구현하는 것이 아니고 뷰의 정의만 시스템에 저장해 두는 것.

추후 사용자가 이 뷰를 접근하면 그때 시스템은 이 저장된 뷰의 정의에 따라 기본 테이블을 이용해서 마치 뷰가 기본 테이블인것 같이 만들어 제공한다. 뷰는 실행 시간에만 구체화되는 특수 테이블이다.

CREATE VIEW 뷰_이름
	AS SELECT 문
    [WITH CHECK OPTION];

 

 

학생 테이블의 컴퓨터과 학생들로 컴퓨터과 학생(CSTUDENT)이라는 뷰를 생성

CREATE VIEW CSTUDENT(NAME, YEAR)
	AS SELECT NAME, YEAR
    FROM STUDENT
    WHERE DEPT='컴퓨터'
    WITH CHECK OPTION;

 WITH CHECK OPTION; → 뷰에 대한 갱신이나 삽입 연산이 실행 될 때 뷰 정의조건(DEPT='컴퓨터')을 위배하면 실행을 거절시킨다는 것을 명세 하는 것

 

뷰 제거

DROP VIEW 뷰_이름{ RESTRICE | CASCADE }

 

DROP VIEW DEPTSIZE RESTRICE;

DEPTSIZE는 다른 곳에서 참조되고 있지 않은 한 데이터베이스에서 제거되어 없어진다.

실제로 뷰에 대한 시스템 테이블에서 DEPTSIZE에 대한 뷰 정의문을 제거한다.

 

DROP VIEW DEPTSIZE CASCADE;

CASCADE가 사용되면 해당 뷰 뿐만 아니라 이 뷰가 사용된 다른 모든 뷰나 제약 조건(CONSTRAINT)이 함께 제거된다.

DEPTSIZE뷰가 제거될 뿐만 아니라 이 뷰가 사용된 뷰도 자동적으로 제거된다.

 

뷰의 장단점

장점

  • 데이터의 논리적 독립성을 어느정도 제공한다. 뷰가 정의된 기본 테이블이 확장된다든지 뷰가 속해 있는 데이터베이스 테이블이 더 늘어난다고 하더라도 기본의 뷰를 사용하는 프로그램이나 사용자는 아무런 영향을 받지 않는다
  • 데이터의 접근을 제어하게 함으로써 보안을 제공한다. 뷰를 통해서만 데이터를 접근하게 되면 뷰에 나타나지 않은 데이터를 안전하게 보호하는 효율적인 기법으로 사용할 수 있다
  • 사용자의 데이터 관리를 간단하게 해준다. 필요한 데이터만 뷰로 정의해서 처리할 수 있기 때문에 관리가 용이하고 명령문이 간단해진다
  • 여러 사용자의 상이한 응용이나 요구를 지원해 준다. 하나의 테이블로 여러개의 상이한 뷰를 정의하여 사용자의 요구에 따라 활용할 수 있게 한다

단점

  • 뷰는 정의를 변경할 수 없다
  • 뷰는 삽입, 삭제, 갱신 연산에 많은 제한을 가지고 있다

 

 

 

출처

쉬운코드 DB인덱스 https://youtu.be/IMDH4iAQ6zM?si=37L4mMrDwSuf6VGX

데이터베이스 과목 강의 (SQL 뷰) https://youtu.be/fPXZgbMUam0?si=qIYZ9N6_z3WlpIZx