[cs 스터디] DB Part.1

MIIN·2023년 2월 22일

cs

목록 보기

1/3

데이터베이스

인덱스

인덱스란?

데이터베이스 테이블에 대한 검색 성능의 속도를 높여주는 자료구조
특정 칼럼에 인덱스를 생성하면 해당 칼럼의 데이터들을 정렬한 후 별도의 메모리 공간에 칼럼의 값과 물리적 주소를 함께 저장

장단점

장점

테이블 검색 속도 및 성능 향상에 따른 전반적인 시스템 부하 감소
데이터들이 정렬된 형태를 갖게 됨에 따라 'Full Table Scan' 작업 없이 조건에 맞는 데이터를 빠르게 찾을 수 있음
ORDER BY, MIN/MAX와 같은 경우도 마찬가지로 빠르게 수행 가능

Where 절의 효율성
Index를 사용하지 않는 경우

Where 절을 이용할 때 레코드의 처음부터 끝까지 다 읽어서 검색 조건과 맞는지 비교해야 한다.(Full Table Scan)
Index를 사용하는 경우
테이블이 정렬되어 있기 때문에 해당 조건에 맞는 데이터를 빠르게 찾을 수 있다.

Order by 절의 효율성
Index를 사용하는 경우 Order by에 의한 Sort 과정을 피할 수 있다.
Order by는 정렬과 동시에 메모리에서 정렬이 이루어지고 메모리보다 더 큰 작업이 필요하다면 디스크 I/O도 추가적으로 발생되는 등 부하가 괴장히 많이 걸리는 작업이다.
하지만 인덱스를 사용하면 이미 정렬이 되어있기 때문에 이런 전반적인 자원의 소모가 필요하지 않다.

MIN, MAX의 효율적인 처리
시작값과 끝값만 가져오면 되기 때문에 훨씬 효율적이다.

단점

인덱스를 관리하기 위해 DB의 약 10%에 해당하는 저장공간이 필요하다.
정렬된 상태를 계속 유지시켜줘야 한다.
- = 데이터 값이 바뀌는 부분에 악영향을 미친다.
- 데이터가 추가되거나 값이 바뀌면 INDEX 테이블 내에 있는 값들을 재정렬해야 한다.
잘못 사용할 경우 오히려 성능이 저하될 수 있다.
- 테이블의 전체 데이터 중 10~15% 이하의 데이터를 처리하는 경우에만 효율적이다.
- 값의 범위(range)가 적은 칼럼의 경우, 인덱스를 읽은 후 다시 많은 데이터를 조회해야 하기 때문에 비효율적

해시 테이블

칼럼 값과 물리적 주소를 (key, value)의 한 쌍으로 저장
하지만 해시 테이블은 등호(=) 연산에 최적화되어 있는 반면 DB는 부등호(<. >) 연산이 더 자주 사용됨
해시 테이블 내 데이터들은 정렬되어 있지 않아 특정 기준보다 크거나 작은 값을 빠르게 찾을 수 없어 실제 인덱스에서 잘 사용하지 않음

B-Tree

출처: http://www.btechsmartclass.com/data_structures/b-trees.html

여러 유형 중 가장 많이 사용되는 구조
Binary search tree와 유사하지만 한 노드 당 자식 노드가 2개 이상 가능하다.
균형이진트리의 연속이기에 균형을 유지하며, 따라서 아무리 최악의 경우라도 O(logN)의 검색 성능을 보여준다.
그러나 처음 생성 당시는 균형 트리지만 테이블 갱신의 반복을 통해 서서히 균형이 무너지고, 성능이 악화된다.
어느 정도 균형을 자동으로 회복하는 기능이 있지만, 갱신 빈도가 높은 테이블에 작성되는 인덱스 같은 경우 인덱스 재구성을 통해 트리의 균형을 되찾는 작업이 필요하다.

이 중에서도 B*Tree와 B+Tree가 가장 많이 사용된다.

B*Tree

대부분의 DBMS, 특히 오라클에서 중점적으로 사용하고 있는 보편적인 인덱스
Root(기준) / Branch(중간) / Leaf(말단) Node로 구성되며 계층적 구조를 갖는다.
특정 칼럼에 인덱스를 생성하는 순간 칼럼의 값들을 정렬한다.
오라클 서버에서 풀 스캔보다 인덱스 스캔이 유리하다고 판단했을 때, 생성된 인덱스가 정렬한 순서가 중간쯤 되는 데이터를 root 블록으로 지정하고 root 블록을 기준으로 가지가 되는 branch 블록을 지정하며 마지막으로 잎에 해당하는 leaf 블록에 인덱스의 키가 되는 데이터와 데이터의 물리적 주소 정보인 rowid를 저장한다.

B+Tree
B-Tree의 확장 개념으로, internal 혹은 branch node에 key와 data를 담을 수 있는 B-Tree와 달리 브랜치 노드에 key만 담아두고 data는 담지 않는다.
오직 리프 노드에만 key와 data를 저장하고, 리프 노드끼리 Linked list로 연결돼 있다.
MySQL의 DB engine이 B+Tree

장점

리프 노드를 제외하고 데이터를 담아두지 않기 때문에 메모리를 더 확보함으로써 더 많은 key들을 수용할 수 있다. 하나의 노드에 더 많은 key들을 담을 수 있기 때문에 트리의 높이는 더 낮아진다.(cache hit을 높일 수 있다.)
풀 스캔 시, B+Tree는 리프 노드에 데이터가 모두 있기 때문에 한 번에 선형탐색만 하면 된다. -> B-Tree에 비해 빠르다.

참고:
https://zorba91.tistory.com/293
https://choicode.tistory.com/27

더 자세한 사항 알아보기
https://ssocoit.tistory.com/217
https://3catpapa.tistory.com/201

정규화(Normalization)

이상현상이 있는 릴레이션을 분해하여 이상현상을 없애는 과정

장점

DB 변경 시 이상 현상(Anomaly)을 제거할 수 있다.
정규화된 DB 구조에서는 새로운 데이터 형의 추가로 인한 확장 시, 그 구조를 변경하지 않아도 되거나 일부만 변경해도 된다.
DB와 연동된 응용 프로그램에 최소한의 영향만을 미치게 되어 응용 프로그램의 생명을 연장시킨다.

단점

릴레이션의 분해로 인해 릴레이션 간의 JOIN 연산이 많아진다.
질의에 의한 응답 시간이 느려질수도 있다. 데이터의 중복 속성을 제거하고 결정자에 의해 동일한 의미의 일반 속성이 하나의 테이블로 집약되므로 한 테이블의 데이터 용량이 최소화되는 효과가 있다.
따라서 데이터를 처리할 때 속도가 빨라질 수도, 느려질 수도 있다.
조인이 많이 발생해 성능 저하가 나타나면 반정규화(De-normalization)를 적용할 수도 있다.

제1 정규화

테이블의 칼럼이 원자값(Atomic Value)을 갖도록 테이블 분해

제2 정규화

제1 정규화를 진행한 테이블에 대해 완전 함수 종속을 만족하도록 테이블을 분해하는 것
완전 함수 종속: 어떤 속성이 기본키에 대해 완전히 종속인 경우로, 기본키의 부분집합이 결정자가 되어서는 안된다.
부분 함수 종속: 어떤 속성이 기본키가 아닌 다른 속성에 종속되거나, 기본키가 여러 속성으로 구성되어 있을 경우 기본키를 구성하는 속성 중 일부만 종속될 때

제3 정규화

제2 정규화를 진행한 테이블에 대해 이행적 종속을 없애도록 테이블을 분해하는 것
이행적 종속: A->B, B-C가 성립할 때 A->C가 성립되는 것

BCNF 정규화

제3 정규화를 진행한 테이블에 대해 모든 결정자가 후보키가 되도록 테이블을 분해하는 것

제4 정규화

BCNF를 진행한 테이블에 대해서 함수 종속이 아닌 다치 종속(MVD: Multi Valued Dependency)을 제거
다치 종속: 하나의 릴레이션에서 두 개의 속성이 2:N 대응되는 경우

제5 정규화

제4 정규화를 만족한 테이블에 대해서 후보키를 통하지 않는 조인 종속(JD: Join Dependency)를 제거
조인 종속이 존재하는 릴레이션이 사용하기 편하므로, 이는 지나치게 이상적인 정규형이다.
조인 종속: 테이블을 분해한 결과를 다시 조인했을 때 원래의 테이블과 동일하게 복원되는 제약 조건으로, 다치 종속의 개념을 더 일반화한 것

일반적으로는 제3정규형이나 BCNF에 속하도록 릴레이션을 분해한다.

트랜잭션

트랜잭션이란?

데이터베이스의 상태를 변화시키기 위해 수행하는 작업 단위
한꺼번에 모두 수행되어야 할 일련의 연산들

트랜잭션 특징

데이터베이스 시스템에서 병행 제어 및 회복 작업 시 처리되는 작업의 논리적 단위다.
사용자가 시스템에 대한 서비스 요구 시 시스템이 응답하기 위한 상태 변환 과정의 작업단위이다.
하나의 트랜잭션은 Commit되거나 Rollback된다.

트랜잭션의 성질(ACID)

원자성(Atomicity)

트랜잭션의 연산은 DB에 모두 반영되거나 아예 반영되지 않아야 한다.
트랜잭션 내의 모든 명령은 반드시 완벽히 수행되어야 하며, 모두가 완벽히 수행되지 않고 어느 하나라도 오류가 발생하면 트랜잭션 전부가 취소되어야 한다.

일관성(Consistency)

트랜잭션이 그 실행을 성공적으로 완료하면 언제나 일관성 있는 DB 상태로 변환한다.
시스템이 가지고 있는 고정요소는 트랜잭션 수행 전과 트랜잭션 수행 완료 후의 상태가 같아야 한다.

독립성, 격리성(Isolation)

둘 이상의 트랜잭션이 동시에 병행 실행되는 경우 어느 하나의 트랜잭션 실행 중에 다른 트랜잭션의 연산이 끼어들 수 없다.
수행 중인 트랜잭션은 완전히 완료될 때까지 다른 트랜잭션에서 수행 결과를 참조할 수 없다.

영속성, 지속성(Durability)

성공적으로 완료된 트랜잭션의 결과는 시스템이 고장나더라도 영구적으로 반영되어야 한다.

트랜잭션 격리 수준

MySQL의 격리 수준

READ UNCOMMIT(커밋되지 않은 읽기) = 레벨 0
- SELECT 문장이 수행되는 동안 해당 데이터에 Shared Lock이 걸리지 않는 계층
- 트랜잭션에 처리 중이거나, 아직 Commit되지 않은 데이터를 다른 트랜잭션이 읽는 걸 허용함
- DB의 일관성을 유지하는 것이 불가능
- Dirty Read 발생
READ COMMITTED(커밋된 읽기) = 레벨 1
- SELECT 문장이 수행되는 동안 다른 트랜잭션이 접근할 수 없어 대기
- Commit이 이루어진 트랜잭션만 조회 가능
- 기본적으로 사용하는 Isolation Level
- Non-Repeatable Read 발생
REPEATABLE READ(반복 가능한 읽기) = 레벨 2
- 트랜잭션이 완료될 때까지 SELECT 문장이 사용되는 모든 데이터에 Shared Lock이 걸리는 계층
- 트랜잭션이 범위 내에서 조회한 데이터 내용이 항상 동일함을 보장
- 다른 사용자는 트랜잭션 영역에 해당되는 데이터에 대해 수정 불가능
- MySQL DBMS에서 기본으로 사용
- Non-Repeatable Read 발생 X
- 즉, 자신의 트랜잭션 번호보다 낮은 트랜잭션 번호에서 변경된 것만 보게 된다.
- Phantom Read 발생
SERIALIZABLE(직렬화 가능) = 레벨 3
- 트랜잭션이 완료될 때까지 SELECT 문장이 사용되는 모든 데이터에 Shared Lock이 걸리는 계층
- 가장 엄격한 격리 수준으로 완벽한 읽기 일관성 모드
- 다른 사용자는 트랜잭션 영역에 해당되는 데이터에 대해 수정 및 입력 불가능

Dirty Read

어떤 트랜잭션에서 아직 실행이 끝나지 않은 다른 트랜잭션에 의한 변경사항을 보게 되는 경우
커밋되지 않은 수정 중인 데이터를 다른 트랜잭션에서 읽을 수 있도록 허용할 때 발생하는 현상

Non-Repeatable Read

한 트랜잭션에서 같은 쿼리를 두 번 수행할 때 그 사이에 다른 트랜잭션 값을 수정 또는 삭제하면서 두 쿼리의 결과가 상이하게 나타나는 일관성이 깨진 현상
한 트랜잭션에서 똑같은 SELECT를 수행했을 때 항상 같은 결과를 반환해야 한다는 Repeatable Read 정합성에 어긋남

Phantom Read

한 트랜잭션 안에서 일정 범위의 레코드를 두 번 이상 읽었을 때, 첫 번째 쿼리에서 없던 레코드가 두 번째 쿼리에서 나타나는 현상
트랜잭션 도중 새로운 레코드 삽입을 허용하기 때문에 나타남

격리 수준	DIRTY READ	NON-REPEATABLE READ	PHANTOM READ
READ UNCOMMITTED	O	O	O
READ COMMITTED		O	O
REPEATABLE READ			O(InnoDB는 발생 X)
SERIALIZATION

Ref:
https://coding-factory.tistory.com/226
https://joont92.github.io/db/트랜잭션-격리-수준-isolation-level/
https://zzang9ha.tistory.com/381
https://dar0m.tistory.com/225

MIIN

다음 포스트