2024. 2. 2. 11:32ㆍDB
일반적으로 DB를 사용할 때 초보 입장에서는 문법에 맞게 sql문만 사용하면 된다고 생각한다.
그러나 DB에 대한 기초이론도 탄탄해야한다.
따라서 이번 글에서는 DB에 SQL문을 실행할 때 어떤 과정으로 진행되는지, db 내부적으로 시스템이 어떻게 가동되는지에 대해 정리해보고자 한다.
* 해당 글에는 정확하지 않은 정보가 있을 수 있으므로 더 자세한 정보는 공식문서나 해외 포럼을 참고하시고 잘못된 부분을 댓글로 지적해주시면 수정하겠습니다.
목차는 다음과 같다.
1. SQL 파서의 구문 분석
2. 옵티마이저의 쿼리 최적화
3. 로그 관리(redo 로그와 undo 로그)
4. 트랜잭션에 관해
4-1. 정합성과 무결성
4-2. 동시성 제어 (동시성 제어, 고립성 레벨, 데드락)
4-3. 성능 최적화
1. SQL 파서의 구문 분석
우리가 일반적으로 sql문을 실행할 때의 순서에 대해 알고 있는 사람은 많지 않을 것이다.
SQL문은 RDBMS에서 일반적으로 다음과 같은 과정을 통해 실행된다.
1. Query Parser의 쿼리구문 분석(구문분석, 의미분석)
파서의 분석은 2가지 과정을 거친다.
- 1. 구문분석
- 2. 의미분석
1번의 구문분석에서 마주하는 것이 우리가 일반적으로 query문을 사용했을 때 마주치는 문법 에러이다. 쿼리문의 형식이 해당 DB가 요구하는 형식이 아닐 경우 parser가 error를 발생시킨다. 이걸 보고 구문분석 이라고 한다.
구문분석이 맞다고 해도 해당 쿼리문이 실제 있는 데이터(존재하지 않은 table이나 column을 참조하려고 하는지)를 참조하는지에 대한 것도 검사하는 의미분석도 실행된다.
존재하지 않는 테이블을 조회하려는 경우 에러가 발생한다. 좀 전의 구문에러(Syntax error)가 발생하던 때와는 에러 로그가 다른 것을 알 수 있다.
2. 옵티마이저의 쿼리 최적화
옵티 마이저는 파서가 분석한 구문을 어떻게 하면 효과적으로 실행할 수 있을지 고민하고 검증한다. 인덱스 사용, 조인 방법, 데이터 접근 순서등 구문을 효율적으로 실행하기 위한 계획을 세운다.
옵티마이저의 실행계획이 어떻게 될지 알아보기 위해서는 쿼리문 앞에 EXPLAIN 키워드를 붙이면 된다.
각 컬럼이 뭘 뜻하는지 상세히 설명할 수는 없지만 중복된 데이터가 엄청 많고 JOIN을 여러번 해야하는 상황에서 옵티마이저의 실행계획을 확인하고 쿼리문을 바꾸는 전략을 취할수도 있기 때문에 유용하게 쓰일 수 있다.
3. 로그 관리(redo로그와 undo로그)
옵티마이저에 의해 쿼리문이 실행됐든 취소가 됐든 동작이 수행되고 나면 로그로써 관리가 돼야 한다.
DB에는 쿼리 실행 로그를 관리하기 위해 redo로그와 undo로그로 데이터를 관리한다.
편의상 리두로그와 언두로그로 칭하겠다.
-리두로그
리두로그는 모든 데이터의 변경사항을 기록한다. 따라서 시스템이 예기치않게 실패하는 경우에도 리두로그를 참조해서 데이터를 복구한다.
즉 가장 최근의 커밋 내역이 리두로그에 있으므로 그 커밋내역을 기준으로 복구도 수행할 수 있는 것이다.
아래와 같은 테이블이 있다고 가정하자. (테이블명 : sample)
| id | balance |
| 1 | 100 |
그리고 아래의 쿼리문을 실행했다고 가정 해보자.
UPDATE sample
set balance = 200
where id = 1
이 때 리두로그는 id = 1 -> balance = 200이라는 로그를 기록 한다.
즉 기존의 데이터의 값이 몇인지는 기록하지 않고 변경사항에 대해서만 기록하는 것이다.
-언두로그
언두로그는 트랜잭션이 변경하기 전의 데이터 상태를 기록한다.
따라서 트랜잭션이 실패하거나 사용자가 명시적으로 롤백을 요청했을 경우 언두 로그를 사용해서 데이터를 이전 상태로 되돌린다.
위와 마찬가지로 동일한 UPDATE문을 실행했을 때 이 부분이 트랜잭션으로 처리됐다면 언두로그는 다음과 같은 형식으로 기록된다.
id = 1 -> balance = 100 -> 200이라는 로그를 기록 한다.
즉 트랜잭션 이전의 데이터 상태와 트랜잭션이 commit되고 나서 변경될 데이터 상태를 함께 가지고 있는 것이다.
정리해서 구분하자면 리두로그와 언두로그의 차이는 어떤 복구에 쓰이느냐 이다.
리두로그는 시스템 장애로 인해 문제가 생겼을 때 가장 최근에 commit한 변경사항을 기준으로 데이터를 복구한다.
언두로그는 트랜잭션 처리중 rollback 요청, 혹은 트랜잭션이 실패했을 경우에 트랜잭션 요청 이전의 데이터 상태를 기억하고 있다가 해당 값으로 들고 있는다.
여기서 한가지 의문점이 든 것은 트랜잭션의 복구에도 리두로그의 가장 최근 커밋 내역을 참고하면 되지 않을까 했는데 ..
자세히는 모르겠지만 아무래도 데이터의 신빙성이 훨씬 높은 쪽이 트랜잭션전에 갖고 있던 언두로그이지 않을까 생각돼서 별도로 관리하는 것으로 추측했다.
하지만 기본적으로 언두로그는 로그를 적재하다가 덮어쓰는 형식이기 때문에 트랜잭션을 열어놓은 상태에서 commit을 오래동안 하지 않은 고 rollback을 하게 되면 이전의 데이터를 되살릴 수 없는 문제가 발생할 수 있다(아주 예외적이지만 이론적으로 그렇다는 것).
트랜잭션에서 commit을 하게 되면 변경사항을 db에 기록 -> 리두로그가 파일에 쓰여졌다는 것의 기술적 의미를 가진다.
트랜잭션에서 rollback을 하게되면 변경사항 취소 -> 언두로그를 보고 이전 데이터를 참조해서 복구하는 것을 뜻한다.
데이터베이스는 필요한 데이터를 버퍼 캐시를 이용해 메모리로 읽어 들인 후에 실행 계획에 맞게 연산을 수행한다. 이 때 실행 결과를 저장하거나 혹은 데이터를 롤백하기 위해서 undo로그나 redo로그에도 변경사항이 생긴다.
이 과정에서 대부분 버퍼캐시를 이용해 로그의 기록을 메모리에 데이터를 서빙해서 관리한다.
4. 트랜잭션에 관해
4-1. 정합성과 무결성
정합성 : 전체 데이터를 일관되게 유지하는 것 -> 트랜잭션으로 관리
예를 들어 a가 b에게 송금하다가 a에 대한 데이터만 수정되고 b에 대한 데이터가 수정되지 않았을 때 이를 보고 데이터의 정합성이 깨졌다고 한다.
무결성 : 데이터 자체의 신뢰성을 보장하는 것 -> constraint 키워드로 관리
예를 들어 컬럼하나에 대해서 특정 컬럼이 유지해야하는 값의 범위라던지 어떤 값이 있을 때 데이터의 값이 그 범위내에서 유지되는 것을 무결성이라고 한다.
4-2. 동시성 제어 (동시성 제어, 고립성 레벨, 데드락)
여러유저가 데이터베이스에 동시에 같은 요청을 보냈을 때 발생하는 특성에 관한 것.
동시성으로 인해 발생할 수 있는 문제를 제어하기 위해서 db에서는 고립성 레벨(isolation level)을 단계별로 구분해 설정할 수 있다.
다음 명령어를 통해 내 db의 고립성 레벨을 확인해보자.
-- 현재 고립성 레벨 확인
SHOW VARIABLES LIKE 'transaction_isolation';
1. READ UNCOMMITTED (읽기 미확정):
가장 낮은 고립성 수준이다. 다른 트랜잭션에서 커밋되지 않은 데이터를 읽을 수 있기 때문에 데이터의 정확성을 보장할 수 없다.
2. READ COMMITTED (읽기 확정):
커밋된 데이터만 읽을 수 있으며 다른 트랜잭션에서 커밋된 변경사항만 볼 수 있다.
Oracle의 기본 고립성 레벨은 이 단계와 유사하게 설정돼있다.
3. REPEATABLE READ (반복 가능 읽기):
트랜잭션 안에서 일관된 읽기를 수행한다. 즉 다른쪽에서 updte가 끝나고 commit을 했다고 하더라도 내쪽에서 commit이 끝나지 않으면 반대쪽 상황이 적용되지 않는다. 이것이 가능한 이유는 MVCC라는 개념 덕분인데 트랜잭션을 시작할 때 스냅샷을 떠놓고 해당 스냅샷만을 참조하기 때문에 다른쪽의 commit 사항에 영향을 받지 않게 만드는 것이다.
2단계의 고립성 레벨과 다른 점은 2단계의 경우 내가 트랜잭션이 종료되지 않았더라도 다른 쪽이 종료됐으면 해당 데이터의 정합성은 있는 것으로 보기 때문에 참조가 가능하지만 3단계의 경우 내쪽의 트랜잭션이 끝나야지만 다른 쪽의 데이터도 정합성 있는 것으로 보는 것이다.
그러나 반대쪽에서 insert 한 구문에 대해서는 보일 수 있다. 이를 PHANROM READ라고 한다.
유령 레코드가 보이는 것이다.
이를 쉽게 설명하자면 다음과 같은 상황이다.
1. 트랜잭션 A가 테이블 조회
2. 트랜잭션 B가 테이블에 데이터 삽입(INSERT) 이후 COMMIT
3. 트랜잭션 A가 다시 테이블 조회
이 과정을 수행하게 되면 트랜잭션 A에서는 갑자기 B에서 커밋한 데이터를 읽을 수 있게 되는 것이다.
MySQL에서는 REPETABLE READ 수준의 고립성 레벨이 기본적으로 설정돼있다.
그러나 MySQL에서는 PHANTOM READ를 방지한다. 이 말이 뜻하는 것은 db의 고립성 레벨 구현이 조금씩 차이가 있기 때문에 어디서부터 어디까지 고립성을 보장하는지는 직접 확인해봐야 알 수 있다.
4. SERIALIZABLE (직렬화 가능):
가장 높은 고립성 수준이며 방금 언급한 PHANTOM READ와 같은 데이터의 모든 격리 문제가 방지된다.
그러나 동시성에 제한을 많이 뒀기 때문에 성능에 제한이 생길 수 있다.
데드락이란?
데드락을 알기 위해서 우리는 우선 db에서 락(rock)이라는 개념에 대해 먼저 알아야 한다.
쉽게 예시를 들어보겠다.
update문을 실행할 때 양쪽에서 같은 데이터에 대해 update를 실시할때 먼저 실시된 update문이 있으면 그 데이터의 대한 row에는 lock이 걸리게 돼서 이후에 실행되는 update문에는 rock이 걸려있어서 실행되지 못한다.
따라서 update문이 먼저 실행된 쪽에서 commit 되고 난 후에 두번째로 요청한 update문이 실행된다.
중요한 것은 이 rock의 단위는 row(행)로 간다.
따라서 아래와 같은 예제를 들으면 이해하기 쉬울 것이다.
1. 트랜잭션 A가 테이블 A의 id = 1인 레코드를 수정 -> 해당 레코드에 Rock이 걸림
2. 트랜잭션 B가 테이블 A의 id = 2인 레코드를 수정 -> 해당 레코드에 Rock이 걸림
3. 트랜잭션 A가 id = 2인 레코드를 수정 요청 -> 이미 트랜잭션 B가 먼저 수정했기 때문에 대기 상태
4. 트랜잭션 B가 id = 1인 레코드를 수정 요청 -> 이미 트랜잭션 A가 먼저 수정했기 때문에 대기 상태
이렇게 되면 트랜잭션A와 B 모두 pending(대기)상태에 빠지게 되므로 데드락이 발생하게 된다.
데드락 뿐만 아니라 발생할 수 있는 또 다른 문제는 고립성 레벨 3단계 이상에서는 데이터를 참조하는 타이밍에 따라서 데이터의 정합성이 깨질 수 있고 데이터 복사가 일어날 수도 있다. 이 부분에 대해서는 추후에 기회가 되면 자세히 공부하고 포스팅해볼 예정이다.
4-3. 성능 최적화
엄청 큰 테이블에서 데이터를 조회할 일이 있다고 한다면 index 설정을 해놓는다.
이 index는 아마 culumn마다 생성되는 것 같고 이는 힙 구조로 돼있어서 탐색 알고리즘이기 때문에 시간복잡도 면에서 굉장히 효율적일 것.
db콜 횟수를 줄이는 것이 중요하다. 예를 들어 100번 insert해야하는 경우가 있을 때 db로 접근하는 횟수가 100번이고 이는 곧 네트워크 접근횟수와 같다. 네트워크 속도는 물리적으로 내가 조종할 수 있는 것이 아니기 때문에 최대한 줄이는 것이 좋다. (해외 사용자를 고려한다면 해외 region에도 인스턴스를 생성하는 것도 하나의 방법)