[SQL]데이터 무결성 & 트랜잭션 & 가상 테이블

여러사용자가 규칙 없이 아무 데이터나 입력하면 데이터베이스는 엉망이 되어 사용할 수가 없다. 이를 방지하기 위해 관계형 데이터베이스 시스템은 몇 가지 규칙을 정하였다. 그중 대표적인 것이 데이터 무결성과 제약 조건, 트랜잭션과 동시성 제어이다.

 

 

 

 

 

 

 

📌데이터 무결성

---

데이터베이스에 저장된 데이터 값과 사용자가 의도한 데이터 값을 일치해야 한다. 또한 데이터는 정확성, 유효성, 일관성, 신뢰성이 지켜져야 하며 이를 어기는 잘못된 입력,수정,삭제로부터 보호되어야 한다. 예를 들어 숫자가 들어와야하는 데이터 값에 숫자가 아닌 문자같은 데이터 타입이 들어 온다거나 기본 키(primary key) 값에 중복 값이나 null값이 입력된다면 데이터 식별에 큰 문제가 생길 수 있다.

 이를 위해 데이터베이스 시스템은 데이터에 접근하거나 데이터를 처리할 때마다 부적절한 데이터가 입력되는지 검사하여 데이터의 결점 없음, 즉 무결성을 유지해야한다. 이를 지키기 위한 기본 규칙을 데이터 무결성이라고 한다.

 

데이터 무결성 종류

유형	설명
개체 무결성	기본 키(primary key)로 선택된 열은 고유해야 하며 null값을 가질 수 없다.
참조 무결성	기본 키와 외래 키의 관계이다. 외래 키가 있는 테이블의 경우에는 기본 키와 외래 키간의 관계가 항상 유지됨을 보장한다. 참조하는 외래키가 존재하면 행은 삭제될 수 없고 기본 키도 변경될 수 없다.
영역 무결성	데이터 형태, 범위, 기본값, 유일성에 관한 제한이다. 주어진 속성 값은 그 속성이 정의된 도메인에 속한 값이어야 한다.
비즈니스 무결성	사용자의 업무 규칙에 따른 비즈니스적인 제약 조건이다.

 

 

 

 

 

 

 

 

 

 

 

 

📌제약 조건

---

제약 조건(constraint)이란 정해 놓은 규칙에 맞는 데이터만 입력받고 규칙에 어긋나는 데이터는 거부하여 데이터 무결성을 지키는 방법이다.

 

1.기본 키 제약 조건

UNIQUE + NOT NULL 을 만족해야한다. 또한 테이블을 대표하여 각 행을 유일하게 식별하는 값이어야 한다.

 

 

2.외래 키 제약 조건

열 값이 부모테이블의 참조 열의 값을 반드시 참조해야 한다. 참조되는 열은 UNIQUE하거나 기본 키이다.

 

•  외래키란?(자세히)
  외래 키는 하나의 테이블을 이용해서 데이터를 분석하는 과정에서는 의미가 없다. 하지만, 여러개의 테이블을 조합해서 데이터분석을 진행한다면 반드시 이해하여야 한다. 왜냐 외래키는 한 테이블의 필드 중 다른 테이블의 행을 식별할 수 있는 키이기 때문이다. 쉽게 설명하면 외래키는 두개의 테이블을 연결해주는 연결 다리 역할을 한다. 이런 관계는 부모와 자식 관계와 같다하여 참조되는 테이블을 부모 테이블과 부모 키, 참조하는 테이블을 자식 테이블과 자식 키(외래 키)라고 부른다.

 

3.유일 키

중복된 값을 허용하지 않는다. 유일한 값으로 존재해야 한다. (null 값 허용 가능하다.)

 

 

4.NOT NULL

null값을 허용하지 않는다. 값을 반드시 입력해야 한다.

 

 

5.CHECK

범위나 조건 등 지정된 값만 허용한다.

 

 

 

 

 

 

 

 

 

 

 

📌트랜잭션 : 데이터 처리의 기본 작업 단위

---

트랜잭션(Transaction)이란 데이터베이스의 DML, 즉 삽입, 갱신, 삭제와 관련된 논리적인 작업을 말한다.

트랜잭션은 DML 실행과 동시성 제어를 위한 중요한 개념이다.

트랜잭션은 데이터베이스의 데이터 무결성이 보장되는 상태에서 DML작업을 완수하기 위한 기본 작업 단위이다.
이는 관계형 데이터베이스 시스템은 데이터를 처리할 때 트랜잭션을 통해 정상 종료나 사용자 프로세스 실패나 시스템 실패와 같은 비정상 종료에 대해 데이터의 신뢰성과 일관성을 보장한다.

실무에서는 데이터베이스에서 SELECT문으로 데이터를 조회하고 DML을 실행하여 종료하는 과정까지는 트랜잭션이라고 하기도 한다.

 

트랜잭션은 DML문에만 적용이 가능하고  DDL문(CREATE, DROP, ALTER, RENAME, TRUNCATE)은 적용 대상이 아님을 주의하자!

 

 

예시

예를 들어 '승철'이가 '은행'에 돈을 입금했다고 가정해보자. 이 때 '승철'이의 계좌에서는 돈이 차감 되었지만 '은행'에 돈이 추가되지 않았다면 이는 문제가 될 것이다. 즉, 입금 이라는 행위는 '승철'이의 계좌에서 돈이 차감되고 '은행'에는 돈이 추가 되는 두 가지로 이루어져있다. 예시를 든 것 처럼 두 가지 중 하나라도 시행되지 않는 다면 입금이란 신뢰성과 일관성을 잃게 될 것이다.  따라서 이 과정을 하나로 묶는 방법이 필요하고 이것이 바로 
트랜잭션(Transaction)이다.

 

따라서 트랜잭션은 DB와 어플리케이션 간 상호작용에 있어서 안정성을 확보하기 위한 방법이 될 수 있다. 

트랜잭션을 확정하는 방법으로 두 가지가 있는데 바로 Commit과 Rollback이다. 이에 대한 설명은 아래에 작성해놓았다!

 

 

트랜잭션 과정

 

[트랜잭션의 특징(ACID)]

개념	내용
원자성	트랜잭션의 처리가 완전히 끝나지 않았을 경우에는 전혀 이루어지지 않은 것과 같아야 한다. (all or nothing  , 완전히 처리되거나 전혀 처리되지않거나 해야 한다.)
일관성	트랜잭션의 실행이 성공적으로 완료되면 데이터베이스는 모순 없이 일관성이 보존된 상태여야 한다.
고립성	어떤 트랜잭션도 다른 트랜잭션의 부분적 실행 결과를 볼 수 없다.
지속성	트랜잭션이 성공하면 트랜잭션의 결과를 영구적으로 보장해야 한다.

 

 

 

 

 

 

💡트랜잭션의 상태 변화와 트랜잭션 제어어

트랜잭션의 실행 과정에서 데이터의 상태 변화 과정을 살펴보면 다음과 같다.

 

[트랜잭션의 수행 단계]

상태	설명
1.실행	트랜잭션을 실행 중
2.부분 완료	DML등 트랜잭션의 명령을 실행한 후의 상태
3.완료(commited = 커밋)	트랜잭션이 성공적으로 완료
4.실패(failed)	더 이상 정상적으로 실행될 수 없음을 발견
5.철회(aborted = 롤백)	트랜잭셔닝 복원되어 트랜잭션 수행 이전 상태로 돌아간다.

EX) DML명령어 중 UPDATE 명령어를 사용하여 STU_INFO 테이블에서 KIM의 e-mail정보를 KIMSCLUB에서 KIMs_club으로 수정하려한다.

1. UPDATE 명령어를 이용해서 데이터를 변경할 명령어를 실행하면 트랜잭션이 시작된다.

2. UPDATE 명령어를 사용했기 때문에 데이터가 KIMs_club으로 변경된다. 다만 현재 상태는 데이터베이스에 영구적으로 반영된 상태가 아니므로 다른 사용자가 해당 e-mail데이터를 조회했을 때는 기존 데이터인 KIMSCLUB으로 출력된다.

3.데이터가 문제없이 정확하게 갱신되었다면 커밋(commit)을 하고

4.데이터는 정상적으로 데이터베이스에 영구적으로 반영된다. 다만 2 과정에서 제약 조건 위반 등 어떤 사유에 의해 UPDATE 명령어 실행이 실패되면

5.트랜잭션은 롤백(Rollback)되어 철회된다.

 

 

💡트랜잭션을 확정하는 SQL 개념인 commit & rollback

커밋과 롤백은 트랜잭션 제어어(TCL, Transaction Control Language)라고도 하는데, 
커밋은 우리가 워드프로세서에서 문서 작업을 마무리한 다음 파일로 저장하기 위해 ‘저장’ 버튼을 누르는 것과 유사하고, 
롤백은 문장이 틀렸을 때 되돌리기를 해서 방금 전 상태로 되돌아가는 것과 유사하다.

 차이점은 데이터베이스에서 커밋은 데이터베이스에 영구적으로 반영되기 때문에 커밋 이전으로 되돌리기가 쉽지 않다는 점입니다.

 

개념	설명
커밋(commit)	트랜잭션의 모든 미결정 데이터를 영구적으로 반영함으로써 트랜잭션을 종료한다 (저장과 유사)
롤백(rollback)	트랜잭션의 모든 미결정 데이터 변경을 포기함으로써 트랜잭션을 종료한다.(되돌리기와 유사)

 

 

트랜잭션 제어어는 상황에 따라 수동 혹은 자동으로 실행된다.

일반적으로 DML 명령어를 실행할 때는 수동으로 커밋해야 하고 DDL 명령어를 실행할 때는 자동으로 커밋된다.

 

 

 

 

 

 

 

📌동시성 제어

---

동시성 제어는 동시에 실행되는 여러 개의 트랜잭션이 작업을 성공적으로 마칠 수 있도록 지원한다. 은행이나 증권사 등 다중 사용자(multi-user) 환경으로 구성된 기업의 데이터베이스 시스템에서는 반드시 필요한 작업이다. 지금까지 알아본 트랜잭션은 동시성 제어와 매우 밀접하게 관련되어 있다. 다음은 다중 사용자 환경에서 발생하는 예이다.

 다중 사용자 환경은 여러 명의 사용자가 동시에 데이터베이스에 접근하여 데이터를 조회하거나 조작하는 환경을 말한다. 여러 명의 사용자가 존재하기 때문에 데이터베이스 안에 있는 데이터에 대한 접근 제어를 정확하게 하지 않으면 부정확한 데이터가 되고 만다. 이러한 동시성 제어 실패로 인한 대표적인 오류 현상에는 갱신 손실, 불일치 현상, 연쇄 복귀 등이 있다.

 

동시성 제어 기법

제어 기법	설명
락킹(locking)	트랜잭션이 데이터에 잠금(lock)을 설정하면 다른 트랜잭션은 해당 데이터에 대해 잠금이 해제(unlock)될 때까지 접근/수정/삭제가 불가하다.
타임스탬프(timestamp)	시스템에서 생성하는 고유 번호인 타임스탬프를 트랜잭션에 부여함으로써 트랜잭션 간의 접근 순서를 미리 정한다.
적합성(validation) 검증	먼저 트랜잭션을 수행하고 트랜잭션을 종료할 때 적합성을 검증하여 데이터베이스에 최종 반영한다.

 

 

데이터베이스 시스템은 다음과 같이 트랜잭션 직렬화를 보장하는 동시성 제어를 통해 트랜잭션을 정상적으로 수행한다.

 

 

데이터 처리 작업을 할 때는 트랜잭션과 동시성 제어의 개념을 인지하고 DML 명령어를 실행하여 신중하게 작업해야 한다.

사실 동시성 제어는 데이터베이스 시스템이 자동으로 수행하므로 사용자가 동시성 제어에 직접 관여할 일은 없다.

동시성 제어는 다중 사용자와 트랜잭션을 제어하기 위한 관계형 데이터베이스의 기본 원리라고 알아만 두면 된다.

 

 

 

 

 

 

 

 

 

 

 

📌뷰 : 가상의 테이블

---

뷰(view)는 테이블과 유사하지만 실제 데이터가 없는 테이블을 바라보는 매개체이자 ‘거울’과 같은 개념이다.

뷰는 직접 테이블에 접근하는 것이 아니라 테이블에서 사용자가 필요로 하는 부분만 선택하여 만들어 놓은 데이터의 집합이다. 즉, 가상의 테이블이라 할 수 있다.

테이블이 아닌 뷰를 사용하는 이유는 대체로 사용자의 편의와 데이터베이스의 보안 때문이다. 원본 테이블에 직접 접근하지 않아도 사용자가 임의의 뷰를 구성하여 별도의 이름을 붙이거나 접근 가능한 사람을 지정할 수 있다. 이렇게 하면 데이터베이스의 데이터를 안전하게 유지하면서 필요한 사용자에게 적절한 데이터를 제공할 수 있다. 또한 임의의 뷰 구성을 통해 자주 사용되는 복잡한 SQL 문을 매번 작성하지 않도록 할 수 있다. 이처럼 테이블을 대체하여 편리하게 사용하기 위한 것이 뷰입니다.

 

 

[뷰 특징]

-뷰는 데이터 가공을 통해 마치 테이블처럼 내용을 보여 줄 수 있다.

-자주 쓰거나 복잡한 SQL 문의 결과를 미리 만들어 놓을 수 있다.

-여러 테이블을 조인하여 하나의 뷰로 생성할 수 있다. 예로 테이블 세 개를 조인하여 결과를 하나의 뷰로 만들 수 있다.

-사용자별로 접근 권한을 다르게 할 수 있다.

-각기 다른 데이터베이스 시스템에서 각각의 데이터를 전달해야 하는 경우에도 유용하다.

 

 

[뷰의 종류]

종류	설명	비고
심플 뷰(simple view)	하나의 테이블에서 데이터를 생성한다.	CREATE VIEW 명령어로 생성한다.
컴플렉스 뷰(complex view)	여러 개의 테이블을 조인하여 데이터를 생성한다.	CREATE VIEW 명령어로 생성한다.
인라인 뷰(inline view)	SELECT 문의 FROM 절에 기술한 SELECT 문	1회용 뷰로 권한을 제어할 수 없다.

 

 

 

 

 

 

 

 

 

 

 

 

 

📌옵티마이저 : 성능 최적화 관리하기

---

옵티마이저(optimizer)란 실행 계획에 의한 정해진 우선순위 또는 통계 정보를 이용하여 SELECT 문의 질의 성능이 최적화될 수 있도록 실행 계획을 수립하는 데이터베이스 시스템 요소이다. 옵티마이저는 사용자가 요청한 SELECT 문의 질의에 대해 실행 계획을 세우고 비용(필요한 자원)을 계산하여 최적의 성능을 낼 수 있도록 도와준다.

옵티마이저 작동 원리

옵티마이저는 RBO(Rule Based Optimizer)와 CBO(Cost Based Optimizer) 방식이 있는데 실무에서는 주로 CBO 방식을 이용한다.

구분	RBO	CBO
개념	사전에 정의된 규칙 기반 계획	최소 비용 계산, 실행 계획 수립
기준	실행 우선순위	엑세스 비용
성능	사용자의 SQL 작성 숙련도	옵티마이저 예측 성능
특징	실행 계획의 예측이 용이함	저장된 통계 정보의 활용
고려 사항	저효율, 사용자의 규칙 이해도	예측 복잡, 비용 산출 공식 정확성

옵티마이저는 최적화된 SQL 실행 순서, 실행 방법, 비용 등을 찾아주므로 사용자는 실행 계획을 확인하면서 SQL 문을 효율적으로 작성할 수 있다.

 

 

 

 

 

 

 

 

 

 

📌인덱스 : 빠른 검색을 위한 데이터 주소록

---

우리가 지금까지 실습한 인사(employees) 데이터베이스는 데이터가 매우 적지만, 규모 있는 기업에서 운영하는 데이터베이스의 데이터는 용량이 수천만 건에서 수 테라바이트(TB)인 경우가 많다. 이런 경우에는 SELECT 문의 조회 논리가 효율적이지 않으면 결과가 바로 출력되지 않을 수도 있다(엄밀하게 말하면 출력되지 않는 것이 아니라 결과가 매우 느리게 반환되는 것으로, 이것은 실제 운영하는 데이터베이스 시스템에 악영향을 줄 수 있다).

 

인덱스(index)는 데이터를 찾기 위한 ‘색인’으로 데이터의 주소록이라고 부를 수 있다. 데이터를 빠르고 효율적으로 조회하기 위해 사용하는 것으로, 데이터베이스 시스템에 의해 자동으로 생성되기도 하고 사용자가 수동으로 생성하기도 한다.

 

[인덱스의 특징]

인덱스는 테이블의 데이터 값에 빠르게 액세스하도록 하는 데이터베이스 객체이다.

데이터를 빠르게 찾을 수 있기 때문에 디스크 액세스 횟수를 줄일 수 있다. 

데이터베이스 시스템이 인덱스를 자동으로 사용하고 유지 보수하므로 사용자는 인덱스를 직접 조작할 필요가 없다

(수동으로도 생성 가능).

언제든지 생성하거나 삭제할 수 있으며 테이블이나 다른 인덱스에 영향을 주지 않는다.

 

 

 

▷데이터를 조회하는 원리

우리가 수행한 SELECT 문을 떠올려 보면 우리는 Oracle SQL Developer에서 SELECT 문을 작성하여 실행한 것뿐이었지만 오라클 데이터베이스 시스템은 다음과 같은 과정을 거친다.

 

1. 사용자는 employee_id가 100인 직원의 정보 조회를 오라클 데이터베이스 시스템에 요청한다. 

 

2. 오라클 데이터베이스 시스템은 사용자에게 SELECT 문의 질의를 받으면 데이터베이스 메모리 안의 버퍼 캐시를 살펴본다. 버퍼 캐시는 자주 사용되는 테이블의 데이터 정보가 저장되어 있는 일종의 ‘가속 장치’이므로 검색 데이터가 버퍼 캐시 안에 있다면 데이터는 빠르게 조회되어 출력된다. 

 

3. 하지만 버퍼 캐시에 조회하는 데이터가 없다면 데이터베이스 시스템은 디스크에 저장되어 있는 데이터를 찾아내어

 

4. 버퍼 캐시로 복사한 후

 

5. 결괏값을 사용자에게 반환합니다.

 

위처럼 버퍼 캐시에 조회하는 데이터가 없어서 디스크에 저장되어 있는 데이터 파일을 조회하는 경우에는 과정이 더 복잡하고 수행 속도가 느리기 때문에 결괏값을 반환하는데 시간이 더 걸린다. 그렇다고 모든 데이터를 버퍼 캐시에 저장해 놓으면 좋겠지만 관리 효율성과 자원 비용이 비싸서 그럴 수가 없다.

 하지만 어떤 데이터가 어디에 존재하는지 알려주는 색인이 있다면 훨씬 효과적으로 데이터를 검색하여 반환할 수 있다는 아이디어가 나왔고, 이를 위해 데이터가 존재하는 색인 값만 별도로 만들어서 관리하는 기법인 인덱스를 생각하게 되었다.

 

인덱스는 데이터의 검색 속도를 개선하기 위해 테이블의 행을 효율적으로 식별 가능하도록 한다.. 색인이 접근하는 행을 대표하는 주소 아이디를 로우 아이디(row id)라고 부른다. 로우 아이디는 데이터베이스 시스템에 의해 관리되는 주소 값이다. 

row id

인덱스와 로우 아이디를 이용한 데이터 조회

대표적인 인덱스의 종류에는 B 트리(B-tree) 인덱스와 비트맵(bitmap) 인덱스가 있다.

B 트리 인덱스는 데이터의 종류가 많고 동일한 데이터가 적은 경우에 주로 사용한다. 주로 일반적인 기업의 운영 데이터베이스 시스템에서 많이 사용된다. 

[B트리 인덱스의 종류]

종류	설명	사용 예
unique index	중복 데이터가 없는 경우(unique)에 사용한다.	기본 키, 유일 키 데이터
non-unique index	중복 데이터가 있는 경우에 빠른 검색 결과를 보장한다.	인덱스가 필요한 일반적인 데이터
descending index	내림차순 데이터 값으로 인덱스를 생성한다.	매출, 최근 일자 등
composite index	여러 열을 합쳐서 하나의 인덱스를 생성한다.	여러 조건이 필요한 경우 ex) 고객번호 and 성별

B 트리 인덱스는 루트 블록부터 리프 블록까지 사용자가 원하는 값을 스무고개하듯이 순차적으로 찾아가는 구조이다. 이러한 이유 때문에 B 트리 인덱스를 트리 기반 인덱스라고도 부른다.

B트리 인덱스의 구조