(CS) 데이터베이스 - 트랜잭션, 인덱스, 조인

트랜잭션과 무결성

트랜잭션

• DB에서 하나의 논리적 기능을 수행하기 위한 작업의 단위
• 여러개의 쿼리들을 하나로 묶는 단위 

원자성(atomicity)

• 트랜잭션과 관련된 일이 모두 수행 되거나, 모두 안되거나를 보장하는 특징
• 커밋과 롤백으로 인해, 데이터의 무결성 보장
• 커밋
  • 여러 쿼리가 성공적으로 처리되었다고 확정하는 명령어

커밋

• 롤백
  • 트랜잭션으로 처리한 하나의 묶음 과정을 일어나기 전으로 돌리는 일

롤백

일관성(consistency)

• 허용된 방식으로만 데이터를 변경해야하는 것

 

격리성(독립성, isolation)

• 트랜잭션 수행시, 트랜잭션 간에 독립적으로 수행이 가능하는 것
• 격리 수준에 따라 발생하는 현상

팬텀 리드	- 한 트랜잭션 내에서 동일한 쿼리를 보냈지만, 조회 결과가 다른 경우  ex) 키 170cm 이상 회원 조회 → 3명 키 175cm 회원 삽입 후 회원 조회 → 4명
반복 가능하지 않은 조회	- 한 트랜잭션 내의 같은 행에 두번 이상의 조회시, 값이 다른 경우 ex) n번째 행의 값 → 10 누가 n번째 행의 값 3으로 수정 후 read -> 3
더티 리드	- 어떤 트랜잭션에 의해 수정은 했지만, 커밋을 안한 상태에서, 그 행의 데이터를 읽을 수 있을때. ex) n번째 행의 값 → 10 누가 n번째 행의 값을 3으로 수정 후, not commit한 상태에서 read -> 3

• 격리 수준

Serializable	- 트랜잭션을 순차적으로 진행시키는 것 - 여러 트랜잭션이 동시에 같은 행 접근 불가 - 데드락(교착 상태) 확률이 높음
Repeatable_read	- 이전의 트랜잭션이 수정한 행을 다른 트랜잭션이 수정할 수 없음 - 새로운 행을 추가하는 것은 괜찮음
Read_commited	- 다른 트랜잭션이 커밋하지 않은 정보 읽을 수 없음 - 다른 트랜잭션이 수정할 수 있음.
Read_uncommited	- 하나의 트랜잭션이 커밋되기 전에 다른 트랜잭션에 노출되지만, 가장 빠름 - 어림잡아 집계하는 곳에서만 사용하기.

 

지속성(durability)

• 성공적으로 수행된 트랜잭션은 영원히 반영되어야함
• 체크섬, 저널링, 롤백 기능을 제공함
• 체크섬 : 오류 정정을 통해 송신된 자료의 무결성을 보호하는 방법
• 저널링 : 변경사항을 commit 하기전에 로깅하는 것으로, 변경 사항에 대한 로그를 남기는 것.

 

무결성

• 데이터의 정확성, 일관성, 유효성을 유지하는 것
• 종류

이름	설명
개체 무결성	- 기본키로 선택된 필드는 not null이어야 한다.
참조 무결성	- 서로 참조 관계에 있는 두 테이블의 데이터는 항상 일관된 값 유지해야 한다.
고유 무결성	- 특정 속성에 대해 고유한 값을 가지도록 조건이 주어진 경우, 각 속성 값은 모두 고유값을 가진다.
NULL 무결성	- null이 올수 없다는 조건이 주어진 경우, 그 속성 값은 null이 될수 없다는 제약 조건

 

인덱스

인덱스

• 데이터를 빠르게 찾을 수 있는 하나의 장치

 

인덱스 최적화 방법

1. 두번 탐색하도록 강요해라.
   • 인덱스 리스트, 컬렉션 순으로 탐색함 → 읽기 비용 발생
   • 컬렉션 수정 → 인덱스 수정 →B-tree 높이 조절 비용 발생 및 효율적 조회 분산 비용 발생
   • 만약, 컬렉션에서 가져와야 하는 양이 많으면 인덱스를 사용하는 것은 비효율적임.
2. 테스트를 꼭 하라
   • explain 함수를 통해 인덱스를 만든뒤, 쿼리를 보내고, 테스팅을 해서 time을 최소화 해야함
3. 복합 인덱스는 같음, 정렬, 다중 값 ,카디널리티(유니크한 값의 정도) 순으로 생성해라.
   1. == 나, equal이라는 쿼리가 있다면 제일 먼저 인덱스로 설정한다.
   2. 정렬에 쓰는 필드라면, 다음 인덱스로 설정한다.
   3. 다중 값을 출력해야 하는 필드 (> , < 등)는 나중에 인덱스를 설정한다
   4. 유니한 값의 정도 (ex. 성별, 나이가 있을 경우, 성별)에 따라 인덱스를 먼저 생성해야한다.

 

조인의 종류

• 내부 조인 (inner join)
  • 두 테이블간의 교집합
• 왼쪽 조인(left join)
  • 왼쪽 테이블에 있는 집합
• 오른쪽 조인 (right join)
  • 오른쪽 테이블에 있는 집합
• 합집합 조인 (full outer join)
  • 양쪽 테이블의 합집합

// inner join
select * from TableA as a
inner join TableB as b on
a.key = b.key


// left join
select * from TableA as a
left join TableB as b on
a.key = b.key


// right join
select * from TableA as a
right join TableB as b on
a.key = b.key


// full outer join
select * from TableA as a
full outer join TableB as b on
a.key = b.key