Database

Overview

Database 원론 내용 정리

Transaction

데이터 베이스 내에서 한꺼번에 수행되어야 할 일련의 연산. 즉, 연산이 전부 동작되거나, 전부 안되거나해야 한다.

• 한꺼번에 완료가 된 경우에는 성공적인 종료 후, COMMIT : 이 경우, 모든 작업 결과는 데이터베이스에 반영되게 된다.
• 취소가 된 경우에는 비정상적인 종료 후, ROLLBACK : 이 경우, 모든 작업 결과는 취소되며, 데이터베이스에 반영되지 않는다.

Properties

• 원자성(Atomicity)

분리할 수 없는 하나의 단위로 작업은 모두 완료되거나 모두 취소 되어야 한다.

• 일관성(Consistency)

사용되는 모든 데이터는 일관되어야 한다.

• 격리성(Isolation)

접근하고 있는 데이터는 다른 트랜젝션으로부터 격리되어야 한다.

트랜젝션이 진행되기 전과 완료된 후에는 상태를 볼 수 있지만, 트랜젝션이 진행되는 중간의 데이터를 볼 수는 없다.

• 영속성(Durability)

트랜잭셕이 정상 종료되면 그 결과를 시스템에 영구적으로 적용되어야 한다.

• 순차성(Sequentiality)

데이터를 다시 로드하고 트랜젝션을 재생하여 원래 트랜젝션이 수행된 후의 상태로 데이터를 돌릴 수 있어야 한다.

다음의 예를 보자.

카드하나를 들고서 은행 인출기 앞으로 간다.

1. 카드를 넣는다.
2. 어떤거래를 할지 선택을하고
3. 비밀번호를 눌러 인증을 받고
4. 거래를 완료한다.

이 네가지 과정을 묶어서 트랜잭션이라고 한다.

거래까지 완료됬으면 COMMIT

중간에 비밀번호를 틀리거나, 인증을 받았는데 거래를 취소하거나 하는 일이 발생해서 처음으로 돌아갈경우에는 ROLLBACK 이라고 한다.

Index

SQL 서버에서 테이블을 만들고 데이터를 추가, 수정, 삭제할 때 데이터의 레코드는 내부적으로 아무런 순서 없이 저장된다. 이때 데이터 저장영역을 Heap 이라고 한다. Heap 에서는 인덱스가 없는 테이블의 데이터를 찾을 때 무조건 전체 데이터 페이지의 처음 레코드부터 끝 페이지의 마지막 레코드까지 다 읽어서 검색조건과 비교하게 된다. 이런 식의 데이터 검색방법을 테이블 스캔(table scan) 혹은 풀 스캔(full scan)이라고 한다. 이럴 경우 양이 많은 테이블에서 일부분의 데이터만 불러올 때 풀 스캔을 하면 처리 성능이 떨어진다. 즉, 인덱스는 데이터를 select 할 때 빨리 찾기 위해 사용된다.

Basic

인덱스를 생성 시에는 where 절과 join, order by 등과 관련된 칼럼 중 사용 빈도가 높고 값의 선별도가 좋은 칼럼을 사용해야 한다. 반대로 사용 빈도가 낮고 칼럼의 선별도가 나쁜, 예를 들어 한 칼럼의 값이 true/false, 성별(M/F) 등에는 인덱스를 사용하지 않는 것이 좋다. 또 테이블이 작거나 자주 갱신 될 때도 사용하지 않는 것이 좋다.

INDEX 사용시, WHERE 절에서 "!=" 이나, "<>"과 같은 구문은 사용하지 말아야 한다. 만약 "!=" 구문이나, "<>" 사용을 하게 되면, INDEX 를 참조하는 것이 아닌 Full Scan 을 하게 된다.

Create index

인덱스는 크게 clustered 와 non-clustered 인덱스로 나뉠 수 있다.

clustered 인덱스는 물리적 정렬로 DB에 데이터를 입력시 이것을 기준으로 입력이 된다. 따라서 한 테이블에 오직 하나만 존재 할 수 있으며 table 을 열었을 때 order by를 사용하지 않아도 데이터가 clustered 인덱스에 따라 정렬이 되어 있는 것을 확인할 수 있다. 물리적으로 정렬이 되어 있는 만큼 가장 빠른 처리를 한다.

non-clustered 인덱스는 clustered 인덱스와는 달리 중복을 값을 가지면서 한 테이블에 여러 개를 생성할 수 있다.

그리고 unique 라는 것도 있다. unique 는 말 그대로 중복을 허용하지 않는 값을 보호할 때 사용한다. 예를 들어 회원 관리 프로그램에서 아이디가 중복되는 것을 막고자 한다면 이 옵션을 사용하면 된다.

VIEW

뷰(VIEW)는 관계 데이터베이스의 데이터베이스 언어 SQL 에서 하나 이상의 테이블(또는 다른 뷰) 에서 원하는 모든 데이터를 선택하여, 그들을 사용자 정의하여 나타낸 것이다. 관계 데이터베이스의 관계 모델의 관계의 일종인 도출 관계에 해당한다. 여러 테이블(기본 관계) 또는 뷰의 데이터를 연결하여 조합할 수 있다. 보기에 표시되는 데이터의 선택 기준을 지정할 수도 있다.

뷰는 기본 테이블(table)과 같이 행(row)로 구성되지만, 다른 테이블에 있는 데이터를 보여줄 뿐이며, 실제 테이블과는 달리 데이터 자체를 포함하고 있는 것은 아니다. 뷰를 사용하면 여러 테이블이나 뷰를 하나의 테이블인 것처럼 볼 수 있다.

표준 SQL 규격으로는 SQL89에서 사용 가능해 졌다. SQL89에서는 뷰를 만들 수 있지만, DROP 문이 없기 때문에 삭제할 수 없었다. 하지만 SQL92에서는 CHECK OPTION, LOCAL, CASCADE 기능 확장이 이루어지고 있다. SQL99 에서는 더욱 기능 강화되었으며, 특정 조건 하에서 뷰에서 기본 테이블의 데이터 업데이트가 가능해졌다.

정의된 정렬 순서가 모자란 기본적인 테이블의 열처럼, 뷰를 통해 생성된 열도 특정 순서로 정렬되어 나타나지 않는다. 뷰는 관계 테이블이며, 관계 모델은 테이블을 일련의 열로 정의를 한다. 그러한 세트는(정의를 내림으로써) 정렬되지 않기 때문에, 뷰의 테이블도 마찬가지다. 따라서 ORDER BY 구문은 뷰의 정의에서 무의미하며, 표준 SQL2003 에서는 ORDER BY 구문의 CREATE VIEW 명령의 옵션에서 허용하지 않았으며, 마찬가지로 CREATE TABKE 구문에서도 거부된 것이다. 그러나 정렬된 데이터는 다른 테이블로서 동일한 방법으로 뷰를 통해 획득할 수 있다. 그럼에도 불구하고 오라클 데이터베이스와 같은 일부 DBMS 에서는 이러한 SQL 표준 제한을 따르지 않고, 허용하고 있다.

CREATE

뷰 생성 시에 다음과 같이 SQL 문을 작성한다. 기존에 있던 테이블에 있는 컬럼에서 원하는 자료만 조회하는 것이기 때문에 만들 때도 SELECT 문을 통해 생성한다. <source lang=sql> CREATE VEIW VIEW_NAME AS SELECT STATEMENT; </source> 뷰 생성시에, 뷰를 읽기전용(Read-only)과 업데이트 가능(Updatable) 상태로 정의할 수 있다. 만약 데이터베이스가 뷰의 스키마에서 내장된 기본 테이블의 스키마로 역 매핑을 결정할 수 있다면, 뷰는 업데이트 가능하다. INSERT, UPDATE, DELETE 동작은 업데이트 가능 뷰에서 실행될 수 있다. 원 테이블에 변경을 매핑하지 않기 때문에 읽기 전용에서는 그러한 동작을 지원하지 않는다. 뷰 업데이트는 키 보전에 의해 실행된다.

일부 시스템은 뷰에서 INSTEAD OF TRIGGER 를 지원한다. 이런 기술은 뷰에서 실행되는 INSERT, UPDATE, DELETE 의 위치에서 실행되는 다른 로직 정의를 가능하게 한다. 그리하여 데이터베이스 시스템은 뷰의 읽기 전용에 기반한 데이터 수정 작업을 실행할 수 있다. 그러나 INSTEAD OF TRIGGER 는 뷰 자체의 읽기 전용이나 업데이트 가능 속성을 변경하지는 못한다.

DROP

뷰 자체를 삭제하는 SQL 문이다. <source lang=sql> DROP VIEW VIEW_NAME; </source>

See also

• http://121202.tistory.com/17 - [DB용어] 트랜잭션( transaction ) 이란 ?
• http://www.dbguide.net/db.db?cmd=view&boardUid=12908&boardConfigUid=9&categoryUid=216&boardIdx=49&boardStep=1 - 노-아카이브와 아카이브 모드
• http://www.gurubee.net/lecture/1873 - 아카이브 로그 모드(Archive Log Mode)
• http://ohgyun.com/56 - 인덱스의 활용