Sql

0. 공부하게 된 계기

sql에 대해서 많이 몰랐던 것 같다.
DBA가 석사생이 많은 이유가 이렇게 공부할 게 많아서였나 싶었다.
~지금은 얕게 공부했지만 차츰 하나씩 깊게 추가할 예정~

1. transaction

DBMS에서 상호작용 하는 단위로 DB에서 수행되는 여러 작업의 연속이다(내가 느낀 바로는 insert + update 등을 한 번에 처리한 일련의 개념이며 논리적 작업 단위를 구성하는 일련의 연산들의 집합이다. 사용자 입장에서는 블랙박스처럼 input / output만 알 수 있도록 구성된다).
DB에서 트랜잭션을 조작하는 기능은 DB의 무결성 유지를 보장한다.
DB에서 트랜잭션이 진행 중일 때 DB가 일시적으로 일관적이지 않을 수 있지만 트랜잭션이 커밋되거나 종료되면 변경 내용이 적용된다.

트랜잭션이 진행되는 동안 DB는 5가지 state를 지난다.

1. Active: 트랜잭션의 명령이 활성화된 상태를 의미한다.
2. Partially committed: change가 실행되지만 DB가 그 변화를 디스크에 반영하진 않은 상태이다.
3. Committed: 모든 트랜잭션이 DB에 영구적으로 반영된 상태이다. 이 상태에서는 트랜잭션을 롤백할 수 없다.
4. Failed: 트랜잭션이 실패하거나 active state나 partially committed에서 abort가 발생할 경우 진입하는 상태이다.
5. Terminated: committed 또는 aborted 상태 이후의 마지막 및 최종 트랜잭션 상태이다. 이로써 데이터베이스 트랜잭션 수명 주기가 종료된다.

ACID와 Sequentiality

ACID

• Atomicity(원자성)
  • 트랜잭션과 관련된 작업이 전부 반영되거나 전부 반영되지 않아야 한다는 의미이다.
  • Update 2개가 하나의 트랜잭션이라면 Update 1개만 DB에 반영되면 나머지는 반영이 안되는 상황이면 안 되고, 두 개 다 반영되거나 두 개 다 반영이 되지 않아야 한다.
• Consistency(일관성)
  • 트랜잭션 이전과 이후, 데이터베이스의 상태는 이전과 같아야 한다는 의미이다.
  • 성공적으로 수행된 트랜잭션은 정당한 데이터들만을 DB 반영해야 한다.
  • 트랜잭션 이전에 있던 PK, FK 등 명시적인 무결성 제약 조건들뿐만 아니라 비명시적인 조건들도 일관성이 유지되어야 한다.
• Isolation(독립성)
  • 모든 트랜잭션은 다른 트랜잭션으로부터 독립되어야 한다는 의미이다.
  • 독립성이 보장되지 않는다면 트랜잭션이 원래 상태로 돌아갈 수 없다.
  • 독립성을 보장할 수 있는 가장 쉬운 방법은 모든 트랜잭션이 순차적으로 수행되도록 하는 것이다.
• Durability(영구성)
  • 시스템이 고장나더라도 데이터가 손실될 우려가 없이 안전해야 한다는 의미이다.
  • 즉, 지금까지 반영된 트랜잭션 내역이 날라가면 안된다.

Sequentiality(UNDO와 REDO)

데이터를 다시 로드하고 트랜잭션을 재생하여 원래 트랜잭션이 수행된 후의 상태로 데이터를 되돌리는 것을 말한다.
UNDO와 REDO의 공통점은 복구를 한다는 것이지만 자세하게는 다른 점이 있다.
아래 예시(시간을 맘대로 조종할 수 있을 때, 해당 시점으로 상태를 변화시키기 위해서 알기 위한 정보는 무엇이 있을까?)는 나를 잘 이해시켜줘서 가져왔다.

여기서 ‘누가 무엇을 했다’는 REDO 로그이다.
과거로 돌아갈 수 있는 정보인 ‘누가 무엇을 했다’ + ‘과거가 어떤 상태였다’는 UNDO 정보이다.
이때 REDO 로그를 사용해서 과거의 데이터를 최신 데이터 쪽으로 흐르게 하는 것을 롤 포워드(roll-forward)라고 하며 반대로 undo 정보를 활용해 변경을 취소하는 것을 롤백(rollback)이라고 한다.

참고로 기존 트랜잭션이 update t1 set no = no + 1 이었다면 복구는 REDO 로그를 통해 update t1 set no = no + 1을 진행한 뒤, UNDO 정보를 통해 update ta set no = no - 1을 진행한다.
즉, 복구는 UNDO를 통해서 진행한다.

시스템 장애가 발생한다면 UNDO 데이터도 모두 날라가기 때문에 REDO 로그를 통해 마지막 check point부터 장애까지의 db buffer cache를 복구한다.
이게 완료가 되면 UNDO를 통해 commit되지 않은 모든 데이터를 rollback한다.

정리하자면 REDO가 UNDO를 복구하고 최종적으로 UNDO를 실행함으로써 복구한다.

2. Page

Page란 SQL server의 기본 저장 및 I/O 단위이다. MySQL의 경우 하나의 Page는 8KB의 크기를 가진다. 즉, 1MB에 128개의 Page를 저장할 수 있다는 의미이다.
이 중 헤더가 96KB를 차지한다.
이러한 Page가 8개 합쳐져서 나오는 개념이 Extent이다.

Oracle을 포함한 모든 DBMS에서 I/O는 블록(페이지) 단위로 이루어진다.
즉, 하나의 레코드를 읽더라도 레코드가 속한 블록 전체를 읽는다.
다만 이를 통해서 DB 전체를 읽을 필요가 없게 된다.

Single Block I/O는 하나의 I/O Call에서 하나의 데이터 블록만 읽어 메모리에 적재하는 방식이다.
반면 Multi Block I/O는 Call이 필요한 시점에 인접한 블록을 모두(한 extent 내에 있는 블록들) 가져오는 방식이다.
index 스캔 시에는 Single Block이 유리하지만 Table Full Scan처럼 물리적으로 저장된 순서에 따라 읽을 때는 Multi Block 방식이 유리하다.

3. MVCC

기존에 Lock 기능을 이용해서 동시성을 제어한다면 읽기 작업과 쓰기 작업이 서로 방해를 일으키는 문제가 발생한다.
이러한 문제를 해결하기 위해 나온 개념이 MVCC이다.
Multi Version Concurrency Content의 약자로 하나의 레코드에 여러 버전이 관리된다는 의미이다.

MVCC를 사용한다면 일반적인 lock 방식보다 DBMS가 훨씬 빠르게 작동한다.
또한 데이터를 읽기 시작할 때, 다른 사람이 그 데이터를 삭제하거나 수정하더라도 영향을 받지 않고 데이터를 사용할 수 있다.
대신 사용하지 않는 데이터가 계속 쌓이게 되므로 데이터를 정리하는 시스템이 필요하다.
또한 UNDO 블록 I/O, CR Copy 생성 등 부가적인 작업의 오버헤드가 발생한다.

MVCC를 구현하는 방식에는 두 가지가 있다.
첫 번째 방법은 PostgreSQL, SQL Server 등에서 사용하는 방식으로, 데이터 베이스 내에 다중 버전을 저장한다.

두 번째 방식은 Oracle이나 MySQL에서 사용하는 방식이다.
최신 버전의 데이터만 DB에 저장하고 트랜잭션이 발생하면 이전의 데이터를 덮어씌우지 않고 새로운 버전의 데이터를 UNDO 영역에 생성한다.
이때 당시의 System Commit Number도 같이 저장되어, 하나의 데이터에 대해 여러 버전의 데이터가 존재함에도 데이터 조회 시 새로운 최신의 데이터를 읽을 수 있도록 한다.

4. Join의 원리

아래 설명을 위해 사용될 sql문 예시이다.

select e.ename, d.dname
from emp e, dept d
where e.deptno = d.deptno

DBMS는 평소에 데이터를 하드 디스크의 데이터 파일에 저장해 두었다가 필요한 시점에 메모리로 복사한다. 이때 메모리가 데이터베이스의 버퍼 캐시이다.
여기서 중요한 것은 100개의 컬럼을 가진 테이블에 Select를 통해 1개의 컬럼을 수행할 경우에도 100개의 컬럼에 모두 접근하게 된다는 점이다.

Nested Loop Join

프로그램에서 중첩된 반복문을 도는 것과 유사한 방법이라고 생각하면 된다. 다음과 같은 순서로 이루어진다.

1. emp, dept 테이블을 메모리에 복사한다.
2. emp 테이블에서 ename을 꺼내서 임시 작업 공간으로 복사한다.
3. 선행 테이블(emp)에서 주어진 조건을 만족하며(여기서는 별다른 조건이 없음) 후행 테이블(dept)에서 dname을 where 조건에 맞는 데이터를 찾아서 가져온다.
4. 한 행의 작업이 끝나면 다시 처음 테이블로 돌아가서 다음 행에 대해서 이 작업을 반복한다.

이때 먼저 읽은 테이블의 행의 수가 Join의 성능을 결정하기 때문에 선행 테이블로 무엇을 설정할지가 Join의 성능에 영향을 미친다.

Sort-Merge Join

정렬을 한 뒤 각각의 pointer를 이용하여 순차적으로 확인하는, 1번 방법보다 빠른 원리를 이용한다. 다음과 같은 순서로 이루어진다.

1. 선행 테이블(emp)에서 주어진 조건을 만족하는 행을 찾는다(여기서는 별다른 조건이 없음).
2. 선행 테이블(emp)의 where 절에 있는 조인 키를 기준으로 정렬 작업을 수행한다.
3. 후행 테이블도 마찬가지 작업을 수행한다.
4. 정렬된 결과를 통해 조인을 진행한다. emp 테이블의 ename을 가져오고 이를 통해 dept 테이블의 dname을 가져온다.

이 방법은 별다른 index가 없다면 sort하는데 오랜 시간이 걸린다.

Hash Join

2번보다 약 2배 가량 빠른 방법이라고 한다. 다음과 같은 순서로 이루어진다.

1. 조인하는 두 테이블 중 범위가 좁은 테이블을 메모리에 가져온다.
2. Join 조건 컬럼(table.deptno)의 데이터를 Hash 함수에 넣어서 나온 Hash Value 값으로 Hash Table을 생성한다.
3. 후행 테이블의 Join 조건 컬럼(table.deptno)를 Hash 함수에 넣고 값을 비교하여 매칭한다.

참고 및 사진 출처

https://ko.wikipedia.org/wiki/%EB%8D%B0%EC%9D%B4%ED%84%B0%EB%B2%A0%EC%9D%B4%EC%8A%A4_%ED%8A%B8%EB%9E%9C%EC%9E%AD%EC%85%98
https://fauna.com/blog/database-transaction
https://www.geeksforgeeks.org/transaction-states-in-dbms/
https://d2.naver.com/helloworld/407507
https://brownbears.tistory.com/181
https://loosie.tistory.com/527