- Published on
[ASAC 스터디] Database
- Authors
- Name
- Chan Sol OH
목차
- 데이터베이스에서 인덱스를 사용하는 이유 및 장단점에 대해 설명해주세요.
- B-tree의 동작 방식
- ACID에 대해서 설명해주세요.
- Transaction에 대해서 설명해주세요.
- 데이터베이스 정규화에 대해서 설명해주세요.
- 데이터베이스 join에 대해서 설명해주세요.
- Mysql(InnoDB) 공유 락과 배타적 락, 갭 락 그리고 레코드 락을 설명해주세요
- Mysql의 비관적 락과 낙관적 락에 대해서 설명해주세요.
- Mysql을 사용하는 분산 서버는 어떻게 동시성 관리를 할 수 있을까요?
- RDBMS vs NOSQL을 알려주세요.
ASAC 데이터베이스 면접 준비 스터디를 하면서 정리하는 글입니다.
데이터베이스에서 인덱스를 사용하는 이유 및 장단점에 대해 설명해주세요.
인덱스란 DB 내에서 존재하는 테이블의 부분 집합에 연결된 복사본입니다. 인덱스는 하나 이상의 컬럼으로 구성되어 있고, 테이블의 로우에 연결됩니다.
만약 인덱스가 없다면 쿼리를 수행하기 위해 DB는 테이블 전체를 돌면서 조건에 맞는 값을 찾아야합니다. 대신 인덱스가 있다면, 전체 테이블 대신 특정 열만 읽고 결과를 반환합니다.
하지만, 인덱스는 디스크에 공간을 차지하고, 테이블이 생성, 업데이트, 삭제가 일어나는 등 변화가 있을 때마다 동기화를 해야한다. 그렇기 때문에 업데이트가 빈법하게 일어나는 컬럼으로 인덱스를 만들면 안된다.
보통은 검색 조건이나 정렬 조건으로 사용되는 컬럼을 사용하고, 대부분의 RDBMS에서 기본키에는 인덱스가 자동 생성된다. 또한 외래 키 관계에서 참조되는 열은 다른 테이블의 열을 참조하며 자주 조회되는 대상이므로 인덱싱해야 합니다.
인덱스 테이블에는 설정한 속성 값과 그에 맞는 실제 테이블의 위치를 가리키는 포인터가 있다. 그리 인덱스를 생성할 때, 두개 이상 속성을 사용하면, 정렬할 때 왼쪽의 속성이 우선순위를 가진다. 그렇기에 멀티 컬럼 인덱스를 쓴다면 인덱스의 순서가 중요하다.
// player_name_idx라는 인덱스를 생성
// player를 테이블의 name 속성 이용
CREATE INDEX player_name_idx ON player (name);
// team_id_backnumber_idx라는 인덱스를 생성
// player를 테이블의 team_id, backnumber 속성을 이용함
CREATE UNIQUE INDEX team_id_backnumber_idx
ON player (team_id, backnumber);
위의 경우 아래 같이 쿼리를 작성하면 정렬되지 않은 순서대로 검색하기 때문에 성능이 좋지 않다.
SELECT * FROM player WHERE backnumber=10;
SELECT * FROM player WHERE team_id=7 OR backnumber=10;
그 이유는 인덱스는 먼저 team_id를 기준으로 정렬하고 그 다음 backnumber로 정렬했기 때문에 backnumber만 놓고 보면 정렬되지 않고 뒤죽박죽인 상태다. 따라서 예제처럼 쿼리를 작성하려면 backnumber로 시작하는 인덱스를 생성할 필요가 있다. 그렇다면 어떤 쿼리를 보냈을 때, DB가 어떤 인덱스를 쓰는지 어떻게 알까? 쿼리를 보내기 전에 EXPLAIN을 붙여주면 되고, optimizer가 알아서 적절한 인덱스를 선택한다. 내가 선택하고 싶다면 아래처럼 쿼리를 작성하면 된다.
SELECT \* FROM player USE INDEX (team_id_backnumber_idx) WHERE teamn_id=10;
만약 특정 인덱스를 제외하고 싶다면 USE 대신 IGNORE를 사용하면된다.
B-tree의 동작 방식
이진탐색트리는 부모 노드는 최대 두 자녀 노드를 가지는데 왼쪽 자녀 노드는 부모 노드 보다 작고, 오른쪽 자녀 노드는 부모 노드 보다 크다. 만약 자녀 노드를 3개로 하고 싶다면? 부모 노드의 크기가 2가 되어서 자녀 노드의 값 범위를 지정할 수 있다. 예를 들어 부모 노드 값 p1, p2가 있을 때, 왼쪽 자녀 노드는 p1 보다 작고, 가운데 자녀 노드는 p1과 p2 사이 값이고, 오른쪽 자녀 노드는 p2 보다 크게된다. 이렇게 자녀 노드의 개수를 2개 이상으로 늘릴 수 있는 트리를 B-tree라고 한다. 즉 B-tree는 이진탐색트리를 일반화한 것이라고 할 수 있다.
B-tree에서 중요한 파라미터인 최대 자녀 수를 M이라하고 보통 M차 B-tree라고 부른다. 최대 자녀 수만 정해지면 부모 노드의 최대 키 수는 M-1로 정해진다. 각 노드의 최소 자녀 수는 ROUNDUP(M/2)가 되지만, root 노드와 leaf 노드는 제외합니다.
B-tree에 데이터를 삽입하는 방식은 간단합니다. leaf 노드에 데이터를 계속 추가하다가 최대 키 수인 M-1을 넘어가면 중앙 값을 부모 노드로 승격시키고, 좌우의 값들을 새로운 leaf 노드로 내려버립니다.
// 최대 자식 노드 개수 = 3
// 현재 상태
{"1", "2"}
// 15 추가 -> 중앙값 2를 부모노드로 승격, 나머지를 leaf 노드로 옮기기
{"2" , ""}
{"1", ""}, {"15" , ""}, {"" , ""}
// 5 추가 -> leaf노드로 추가됨.
{"2" , ""}
{"1", ""}, {"5" , "15"}, {"" , ""}
// 30 추가 -> {"5" , "15"} 로 30이 들어가는데 2를 초과하게 됨
// 중앙값인 15가 부모노드로 승격되고 30은 새로운 leaf로 들어감 -> 왜? 15보다 크기 때문이다.
{"2" , "15"}
{"1", ""}, {"5" , ""}, {"30" , ""}
// 60과 90이 추가되면 오른쪽 leaf 노드가 M-1을 초과하게 되어 중앙값인 60이 부모노드로 추가
{"2" , "15", "60"}
{"1", ""}, {"5" , ""}, {"30" , ""}, {"90" , ""}
// 그러나 부모 노드의 키 수도 M-1을 넘어갈 수 없기 때문에 다시 부모 노드의 중앙값인 15를 부모 노드로 승격시키고
// 나머지 2와 60을 분할한다.
{"15", ""}
{"2", ""}, {"60", ""}
{"1", ""}, {"5", ""} {"30", ""} , {"90", "}
위와 같은 방식으로 노드를 추가하면서 B-tree는 한쪽 트리만 깊어지는 이진트리의 불균형 문제를 해결합니다. 또한, 이런 방식으로 키 값을 추가하면 모든 leaf 노드는 같은 레벨에 있게됩니다.
ACID에 대해서 설명해주세요.
트랜젝션은 여러 DB 작업들을 하나로 묶음 작업이며, All or Noting으로 결과가 나옵니다. 즉, 트랜젝션은 DB 데이터를 원하는 대로 업데이트되도록, 그렇지 않으면 업데이트 하지 않도록 해서 정합성을 유지하기 위해 사용됩니다. ACID는 데이터 정합성을 유지하기 위한 트랜젝션의 특징입니다.
- Atomicity (원자성)
모든 작업이 All or Noting을 지키도록하는 원칙입니다.
- Consistency (정합성)
데이터가 미리 지정한 규칙으로만 수정할 수 있도록하는 원칙입니다. 또한 특정 row의 변화가 일어나면 그 row를 포함하는 모든 table에 그 변화가 적용되어야합니다. 그렇기 때문에 여러 DB로 테이블 복사본이 있을 때 테이블을 동기화할 수 있다면, Eventual Consistency라고 합니다.
- Isolation (고립성)
서로 다른 Transaction끼리 영향을 주면 안되는 것을 의미합니다. Transaction의 동작과 Transaction의 결과를 더럽히거나 무효화하게 된다면 격리되지 않는 것이라고 볼 수 있습니다.
- Durability (영구성)
DB system(ex pawer)에 문제가 생겨도 commit된 Transaction은 DB에 영구적으로 저장한다. 즉, 비휘발성 메모리(HDD, SSD)에 저장함을 의미한다. 기본적으로 Transaction의 Durabilitysms DBMS가 보장한다.
Transaction에 대해서 설명해주세요.
Transaction은 논리적인 작업 단위로 여러 sql 명령어들을 묶어서 나뉠 수 없게 만든것입니다. Transaction 일부 sql 문만 성공하는 일은 없어야합니다. 하지만 Transaction끼리 격리되지 않아 발생하는 여러 문제가 있습니다. DB 테이블이라는 공유 데이터를 여러 Transaction이 동시에 접근해서 사용하게되면 동시성 문제가 발생할 수 있고, 이를 lock, mvcc 등 다양한 방법으로 격리하게됩니다.
- Dirty Read
Transaction이 데이터를 읽을 때 다른 Transaction이 commit 하지 않은 데이터까지 읽어서 발생하는 문제입니다.
- Non-repeatable read
Transaction이 commit되기만하면 읽을 수 있는 상태에서 발생합니다. 한 Transaction이 끝나기도 전에 다른 Transaction이 값을 수정해서 commit한 경우 아직 끝나지 않은 Transaction은 똑같은 row를 접근할 때 다른 값을 얻게됩니다.
- Phantom read
Transaction이 접근하는 row들에 대해 lock을 보유하더라도 다른 Transaction에 의해 새로운 row가 추가되면 기존 Transaction은 그 row에 lock을 걸지 않습니다. 그렇기 때문에 기존 Transaction이 똑같은 쿼리를 다시 요청하면 새로운 값까지 나타나게 되는 문제입니다.
위와 같이 3가지 문제를 단계적으로 해결한게 아래 4가지 Transaction 격리 수준입니다.
- Read Uncommited
- Read Commited
- repeatable Read
- Serializable
데이터베이스 정규화에 대해서 설명해주세요.
정규화는 관계형 데이터베이스에서 데이터의 중복을 최소화하게 데이터를 구조화하는 것입니다. 크고 조직되지 않은 테이블들을 작고 잘게 조직된 테이블과 관계로 나누는 것을 의미합니다. 정규화 단계는 많지만, 보통 3단계까지 정규화를 하면 정규화를 했다고 얘기를 합니다.
- 제 1 정규화 : 모든 속성은 원자값을 가져야합니다. 개별 테이블에서 반복되는 필드를 제거합니다. 기본 키로 각 데이터 집합을 식별합니다.
- 제 2 정규화 : 여러 레코드에 적용되는 값 집합에 대해 별도의 테이블을 만듭니다. 이러한 테이블을 외래키로 연관시킵니다.
- 제 3 정규화 : 기본 키에 의존하지 않는 필드를 제거합니다. 이행종속성을 제거하도록 테이블을 쪼개야합니다.
데이터베이스 join에 대해서 설명해주세요.
DB 정규화를 하면 테이블들을 분리시키고 데이터 중복을 최소화할 수 있습니다. 만약, 필요한 데이터가 서로 다른 테이블에 있다면 join을 통해서 각 테이블 데이터를 모아볼 수 있어야합니다. 서로 다른 테이블을 합치기 때문에 여러 방법이 있습니다.
employees 테이블과 department 테이블의 join 기준이 ON employees_department_id = department_id 인 상태를 가정하겠습니다.
- Inner join : 두 테이블의 교집합만 합치는 방법입니다. 둘 다 같은 id인 row가 결과로 나오게됩니다.
- Outter join : 두 테이블의 교집합이 아닌 값도 출력하는 방법입니다. Left outter join은
employees_id가 가질 수 있는 모든 경우에 대해서 결과로 나오게됩니다. 만약 오른쪽 테이블에서 공통된 값이 없다면 NULL을 반환하게됩니다.
Full Outter join과 Union의 차이는 Full Outter join은 두 테이블이 일치하지 않는 값도 결과에 포함시키지만, Union은 일치하는 값만 결과에 포함시킵니다.
Mysql(InnoDB) 공유 락과 배타적 락, 갭 락 그리고 레코드 락을 설명해주세요
Mysql은 db 엔진으로 InnoDB를 사용합니다.
InnoDB는 row-lock을 위해 공유락과 배타적락을 사용합니다. 공유락은 Transaction이 row를 읽을 수 있도록 lock을 잡다둔겁니다. 베타락은 row를 업데이트하거나 제거하는데 사용합니다. row에 대해서 락을 걸기 때문에 row-level lock이라고도 부릅니다.
레코드 락 레코드 락은 인덱스의 레코드에 락을 겁니다. 레코드 락에 걸린 인덱스에는 데이터 생성, 업데이트, 제거가 불가능해집니다.
갭 락 레코드락은 하나의 인덱스 레코드에 락을 걸지만, 갭락은 인덱스 레코드에 범위로 락을 겁니다. 인덱스는 보통 기준 컬럼으로 정렬되어 있기 때문에 유효합니다.
Mysql의 비관적 락과 낙관적 락에 대해서 설명해주세요.
비관적 락 비관적 락이 걸린 경우 data table이나 row에 다른 Transaction이 작업을 할 수 없도록 막습니다. 이는 낙관적 락과 달리 DBMS에서 제공하는 기술로 DBMS가 우리에게 특정 데이터에 대해서 독점적인 접근 권한을 보장합니다.
데드락 문제 발생 비관적 락을 사용하는 상황에서 만약 서로 다른 row에 대해 lock을 가진 Transaction이 상대 row가 필요한 상황이 발생한다면, 서로가 Lock을 풀 때까지 무한 대기하는 데드락 문제가 발생합니다.
낙관적 락 낙관적 락은 MySQL의 특징은 아닙니다. 단지 레코드의 버전 숫자를 통해 레코드가 변화했는지 안했는지 판단하는 전략입니다. 보통 version이라는 추가적인 컬럼을 놓고 update 전에 버전과 update 할 때 버전이 같은지 확인하고 같다면 update를 진행합니다. 이를 통해 초기 레코드를 얻고나서 다른 스레드나 Transaction에서 데이터를 업데이트했는지 확인할 수 있습니다.
어떤 락을 사용할까? 비관적 락에서는 여러 consumer가 있는 batch 프로세싱에 적절합니다. 한 consumer가 특정 row에 lock을 걸고 작업할 때 다른 consumer는 lock이 걸린 row를 건너뛰고 다음 작업을 할 수 있게됩니다. 이 방식은 데이터 무결성을 유지하고 batch 프로세싱 시나리오에서 충돌을 방지합니다. 낙관적 락에서는 특정 작업을 반복해도 괜찮을 때 사용해야합니다. 왜냐하면 비관적 락과 달리 낙관적 락은 update 전까지 다른 Transaction이 레코드를 업데이트했는지 안했는지 알 수 없기 때문에 update 전에 존재하는 작업들을 반복해서 할 수 있습니다. 만약 그런 작업들이
side-effect를 발생시키는 작업이라면 낙관적 락을 사용하면 안됩니다.결론 낙관적 락은 충돌이 빈번하게 일어나지 않고 작업 속도가 빨라야할 때 사용하고, 비관적 락은 데이터의 무결성을 보장하지만 동시 접속이 많은 환경에서는 성능이 낮아질 수 있습니다.
Mysql을 사용하는 분산 서버는 어떻게 동시성 관리를 할 수 있을까요?
어떨 때 분산락이 필요하나면, 여러 프로세스에서 데이터를 공유할 때 오직 한 프로세스에서 자원을 사용할 수 있게할 수 있습니다. 보통 분산락은 redis나 ZooKeeper를 이용한 예제가 많지만, MySQL을 이용해서 인프라 비용을 아낄 수 있고, MySQL에서 제공하는 USER-Level-lock은 lock에 이름을 지정할 수 있으며 이 이름을 통해 애플리케이션에서 Lock을 제어할 수 있습니다.
MySQL의 User-level-lock은 아래와 같이 있습니다.
- GET_LOCK(str, timeout) : str에 락의 이름을 지정할 수 있고, timeout으로 락을 얻을 때까지 대기하는 시간을 지정할 수 있습니다. 한 세션에서 락을 잡고 있다면 다른 세션에서는 락을 잡지 못합니다. 무서운 점은
GET_LOCK으로 얻은 락은 Transaction이 commit되거나 rollback되어도 해제되지 않습니다. - RELEASE_LOCK(str) : str이라는 락을 해제합니다. return 값이 1이면 lock 해제를 성공한 것이고, 0이면 세션이 가진 락이 아닐 때 반환합니다. 그리고 Null을 반환하면 락이 존재하지 않을 때 반환합니다.
락을 얻는 부분과 로직의 부분을 분리하기 위해 비즈니스 로직을 Supplier 인터페이스를 통해 수행되도록 할 수 있고, 앞 뒤로 GET_LOCK과 RELEASE_LOCK을 수행시킬 수 있습니다. JDBC를 이용해서 datasource를 주입해서 Connection을 직접 관리할 수 있어서 GET_LOCK() 과 REALSE_LOCK()의 실행을 모두 동일한 Connection에서 할 수 있습니다.
RDBMS vs NOSQL을 알려주세요.
RDBMS는 스키마를 정확하게 지정하고 데이터의 무결성을 보장합니다. 하지만 유연성이 떨어져 스키마의 수정이 어렵습니다. 그리고 여러 테이블이 관계를 맺고 있어서 join문이 많은 복잡한 쿼리가 발생할 수 있습니다. 관계가 있기 때문에 테이블을 분리하기 어려워서 수직적 확장을 할 수 있습니다.
NoSQL은 비관계형 데이터베이스로 유연한 스키마를 가지고 관계형 테이블과는 다른 방식으로 데이터를 저장합니다. 문서, 키-값, 와이드 칼럼, 그래프 등이 있습니다. 또한 테이블끼리 관계가 없기 때문에 수평적 확장이 가능해서 대량의 부하에서도 손쉽게 확장할 수 있습니다. 스키마가 정확하게 지정되지 않기 때문에 중복과 같은 데이터 무결성을 체크해줘야합니다.
ACID 성질을 준수해야하는 경우 변경될 여지가 없고 명확한 스키마가 사용자와 데이터가 중요한 경우 RDBMS를 사용합니다. 그리고 정확한 데이터의 구조를 알 수 없거나 자주 변경되는 경우, 읽기는 자주해도 업데이트는 많이 안하는 경우 그리고 막대한 양의 데이터를 사용하기 때문에 수평적 확장이 필요한 경우 NoSQL을 사용합니다.