[기술면접] DataBase

Mijeong Ryu·2023년 7월 1일

기술면접_DB

Interview

목록 보기

4/5

N+1 문제

N+1 문제란?
N+1이란 1번의 쿼리를 날렸을 때 의도하지 않은 N번의 쿼리가 추가적으로 실행되는 것을 의미(1:N 또는 N:1 관계를 가진 엔티티 조회시 발생)
원인 및 해결 방안
Fetch Join 사용하여 해결
N+1문제가 발생하는 이유는 연관관계를 가진 엔티티를 조회할 때, 한쪽 테이블만 조회하고 연결된 다른 테이블은 따로 조회하기 때문인데 Fetch Join을 사용하면 미리 두 테이블을 조인(두 테이블을 조인하는 쿼리를 직접 작성)하여 한번에 모든 데이터를 가져오기 때문에 N+1 문제를 애초에 막을 수 있음

★★index

인덱스란? DB에서 select처리를 빠르게 하기 위하여 추가적인 디스크를 사용하여 색인을 하는것.
Full Scan:
Full Scan은 데이터베이스 테이블의 모든 레코드를 검사하는 방식입니다. 테이블에 인덱스가 없거나 쿼리 조건이 인덱스를 사용할 수 없는 경우에 일반적으로 Full Scan이 발생합니다. Full Scan은 대용량 테이블의 경우 많은 시스템 자원을 사용하고 성능이 저하될 수 있습니다.
Index Scan:
Index Scan은 테이블에 정의된 인덱스를 사용하여 특정 레코드를 검색하는 방식입니다. 인덱스는 테이블의 특정 컬럼 값과 해당 레코드의 위치 정보를 포함하는 자료 구조입니다. 인덱스를 사용하면 전체 레코드를 검색하지 않고도 원하는 레코드를 빠르게 찾을 수 있습니다. 인덱스는 데이터베이스 성능 향상에 중요한 역할을 합니다.

요약하자면, Full Scan은 테이블의 모든 레코드를 검색하는 반면, Index Scan은 인덱스를 사용하여 필요한 레코드만 검색하므로 일반적으로 성능이 더 좋습니다. 하지만 인덱스를 유지하기 위한 추가적인 저장 공간이 필요하고, 데이터 변경 시 인덱스 업데이트에 따른 오버헤드가 있습니다.
단점
인덱스는 데이터의 검색 속도를 향상시키지만, 다음과 같은 단점도 가지고 있습니다:

(1) 추가적인 저장 공간 필요 : 인덱스를 저장하기 위한 추가적인 디스크 공간이 필요합니다. 인덱스가 많거나 큰 테이블에 적용되면 이 공간이 크게 될 수 있습니다.
(2)변경에 따른 오버헤드 : 인덱스가 있는 테이블에 대한 데이터의 삽입, 삭제, 수정 작업이 느려질 수 있습니다. 이는 데이터 변경 시 인덱스를 업데이트해야 하기 때문입니다.
(3)인덱스 성능 저하 : 데이터가 변경됨에 따라 인덱스의 성능이 저하될 수 있습니다. 인덱스는 최적의 성능을 위해 데이터의 분포에 따라 구조화되는데, 데이터 변경으로 인해 이 분포가 바뀌면 인덱스의 성능이 저하될 수 있습니다. 이를 해결하기 위해 정기적인 인덱스 재구성이 필요할 수 있습니다.
이러한 단점들은 인덱스의 본질적인 특징과 구조적인 제약 때문에 발생합니다. 인덱스는 데이터의 일부를 복사하여 특정 조건에 따라 정렬하고, 이를 바탕으로 빠른 검색을 가능하게 합니다. 따라서 이 복사 및 정렬 과정이 추가적인 공간을 필요로하며, 원본 데이터의 변경에 따라 업데이트가 필요합니다.
성능 저하의 자세한 이유
: 인덱스의 DELETE 와 UPDATE 연산 때문입니다. 인덱스는 UPDATE와 DELETE를 수행할 때, 기존의 인덱스를 삭제하지 않고 '사용하지 않음' 처리를 해줍니다. 만약, 테이블에 UPDATE와 DELETE가 빈번하게 발생된다면 실제 데이터는 10만건이지만 인덱스는 100만 건이 넘어가게 되어, SQL문 처리 시 비대해진 인덱스에 의해 오히려 성능이 떨어질 수 있습니다.
인덱스의 구성
데이터베이스 인덱스는 대부분 B-tree 또는 B+tree라는 트리 구조를 사용합니다. 이 구조는 루트, 브랜치, 리프 노드로 구성됩니다.
(1) 루트 노드: 트리의 최상위에 위치하며, 브랜치 노드로의 포인터를 가집니다.
(2) 브랜치 노드: 루트 노드 또는 다른 브랜치 노드로부터의 포인터를 가지며, 그 자신이 가리키는 하위 브랜치 노드 또는 리프 노드로의 포인터를 가집니다.
(3) 리프 노드: 트리의 최하위에 위치하며, 실제 레코드에 대한 포인터를 가집니다.
이러한 트리 구조를 사용하면 원하는 레코드를 빠르게 찾아낼 수 있습니다.

그렇다면 DBMS는 Index를 어떻게 관리하고 있나요? (Index 자료구조)
B+Tree 인덱스 자료구조
자식 노드가 2개 이상인 B-Tree를 개선시킨 자료구조이며,
BTree 리프노드들을 LinkedList로 연결하여 순차 검색을 용이하게 합니다. 해시 테이블보다 나쁜 O(log2N)의 시간복잡도를 갖지만 일반적으로 사용되는 자료구조입니다.
데이터를 리프 노드에만 저장하고, 리프 노드의 데이터들은 링크드 리스트로 연결되어 있습니다. 이로 인해 B+트리는 범위 검색에 효율적입니다. 또한 B+트리의 리프 노드들은 정렬된 순서로 연결되어 있어 범위 기반의 검색이 용이합니다.
B-Tree는 데이터를 모든 노드에 저장합니다.B-Tree의 경우 최상의 경우 특정 key를 root node에서 찾을 수 있지만, B+Tree의 경우 반드시 특정 key에 접근하기 위해서 leaf node까지 가야 하는 단점이 있습니다.
해시 테이블
컬럼의 값으로 생성된 해시를 기반으로 인덱스를 구현합니다. 컬럼값과 데이터의 위치를 KEY와 VALUE로 표현합니다. 시간복잡도가 O(1)이라 검색이 매우 빠릅니다.
부등호(<,>)와 같은 연속적인 데이터를 위한 순차 검색이 불가능하기 때문에 사용에 적합하지 않습니다.

복합 인덱스의 순서
복합 인덱스는 여러 개의 컬럼을 하나의 인덱스로 만드는 것을 말합니다. 이때 인덱스를 구성하는 컬럼의 순서는 매우 중요합니다. 반적으로는 WHERE절에서 가장 많이 사용되는 컬럼을 먼저 인덱스에 포함시키는 것이 좋습니다.
만약, 구조와 날짜 중에서 어떤 것을 먼저 넣을지 결정해야 한다면, 조회 쿼리를 분석해보고 어느 컬럼이 더 자주 WHERE 절에서 사용되는지, 또 어느 컬럼이 더 많은 고유한 값(distinct value)을 가지는지 확인해보면 좋습니다.
일반적으로, 값의 분산도가 높고 (즉, 고유한 값이 많고), 자주 조회에 사용되는 컬럼을 복합 인덱스의 앞에 두는 것이 좋습니다. 지만 모든 상황이 다르므로, 가능한 다양한 조합으로 인덱스를 생성해보고 실행 계획과 성능을 체크해보는 것이 중요합니다. 또한, 인덱스를 변경할 때마다 어플리케이션의 전반적인 성능에 어떤 영향을 미치는지도 확인해야 합니다.
인덱스가 필요한 경우 / 필요 없는 경우
(1) 인덱스가 필요없는 경우
데이터가 적은 경우(수천건 미만) 인 경우에는 인덱스가 없는 것이 성능이 좋습니다.
조회보다 삽입, 수정, 삭제 처리가 많은 테이블인 경우
조회결과가 전체행의 15% 이상 읽어들일 것으로 예상되는 경우
(2) 인덱스가 필요한 경우
데이터가 많이 쌓일거라고 예상되거나 많은 경우, 조회속도가 너무 느린 경우
데이터의 중복도가 낮은 컬럼
조회결과가 전체 데이터 수의 3~5% 이상쯤되면 인덱스 스캔이 효율적이고 적은 비용으로 찾을 수 있습니다.
Access 대상 범위가 전체 범위의 3~5% 이상 쯤 되면 인덱스 스캔보다 풀 스캔이 훨씬 유리합니다.
주의사항

갑자기 인덱스를 추가하면 기존에 잘 돌아가고 잇던 쿼리에 옵티마이저가 실행계획을 바꾸는 경우가 생겨, 갑자기 느려질 수 있다
(기존 테이블에 인덱스를 추가한느 경우, 기존에 있던 sql 문장들까지 전부 고려해야 한다)

쿼리별 동작방식(https://land-turtler.tistory.com/120 참고)
1) 테이블 생성 시, 3가지 파일이 생성

FRM( 테이블 구조 저장 파일 )
MYD( 실제 데이터 파일 )
MYI ( Index 정보 파일 )
2) SELECT 문 수행 시 ( name = 'turtle'인 대상 탐색 )
서버 프로세스가 DB Buffer Cache에 name이 'turtle'인 정보가 있는지 확인
Buffer에 없다면, 하드디스크에서 turtle 정보를 가진 블록을 복 Buffer Cache로 복사한 후, 특정 데이터 출력
===> Index가 없는 경우, 데이터 파일 블록 전체를 Cache로 복사 후 찾음
===> Index가 있는 경우, where 절에 Index 컬럼이 있는지 확인 후, 인덱스에서 turtle이 어떤 'ROW ID'를 가진지 찾아서 해당 블록만 Cache에 복사
3) Insert 문 수행 시
기존 Block에 여유가 없을 때, 새로운 Block을 할당 받은 후, Key를 옮긴다.
이 때 해당 key값들은 DML이 블로킹되고, lock이 걸린다.
4) Delete문 수행 시
Data는 삭제되어서 그 공간에 다른 Data가 사용 가능하지만, Inde는 Data는 지워지지 않고 "사용 안됨"표시만 생성
5) Update 수행 시
Index는 Delete -> Insert 작업으로 수행한다( index에선 update가 없다. 기존 인덱스를 "사용하지 않음" 표시 하고 Insert 수행)

클러스터형 인덱스 VS 논클러스터형 인덱스
클러스터형 인덱스는 테이블을 생성시 기본으로 생성되는 인덱스이고, 테이블당 1개만 가질 수 있습니다.
논클러스터형 인덱스는 테이블 생성시 기본으로 생성되지 않으며 테이블당 약 최대 240개 정도를 가질 수 있습니다.
클러스터형 인덱스는 테이블 수정,삽입,삭제시 항상 정렬을 유지하고 논클러스터형 인덱스는 테이블 자체는 정렬되지 않고, 인덱스 페이지에만 정렬됩니다.
인덱스가 있음에도 타지 않는 경우
(1) 인덱스 컬럼 변형

where 절에 사용할 인덱스에 함수를 적용한다던가 ( lower(name) == 'word', to_char(hire_date)) ,연산을 하는 등 ( where idx - 1 == 5 ) 의 인덱스에 변형을 가한 경우

(2) 내부적 데이터 변환
where 절에 인덱스에 딱 맞지 않는 데이터 타입을 주는 경우
ex) 문자열 데이터 타입 칼럼에 '값'이 아닌 값 을 적는 경우
where hiredate = to_date('21-01-01', 'YY/DD/DD')는 가능하지만 hire_date = '21-01-01' 은 사용 불가

(3) NULL 조건 혹은 NOT NULL을 사용하는 경우
대부분 풀 스캔이 일어나므로, '' 혹은 0값을 직접 사용하는 것이 낫다

(4) NOT 또는 IN 연산자 사용
NOT, IN은 일반적으로 안타는 경우가 많다. NOT에 사용된 값이 아닌 데이터 비율이 높거나, IN에 포함된 데이터 비율이 높다면 FULL SCAN이 더 낫다고 판단

(5) LIKE문장에서 범위를 전체 지정 시
%표시 가 앞쪽에 사용되면 정렬 순서를 사용할 수 없으므로 FULL SCAN이 이뤄진다.
where name like 'S%'는 인덱스 사용 가능

(6) 부정형 조건의 사용
where deptno != 30 -- 인덱스 사용 불가
where deptno < 30 or deptno > 30 -- 인덱스 사용 가능

(7) OR 조건 사용
where name = 'yaya' or name = 'ho' -- 인덱스 사용 불가능

VIEW

DB에서 View는 무엇인가?
허용된 데이터를 제한적으로 보여주기 위한 것으로, 하나 이상의 테이블에서 유도된 가상 테이블이다.
사용자가 view에 접근했을 때 해당하는 데이터를 원본에서 가져오며 view에 나타나지 않은 데이터를 간편히 보호할 수 있는 장점 존재한다.

★옵티마이저

옵티마이저 :
옵티마이저는 SQL을 가장 빠르고 효율적으로 수행할 최적의 처리 경로를 생성해주는 DBMS 내부의 핵심 엔진입니다.
컴퓨터의 두뇌가 CPU인 것처럼 DBMS의 두뇌는 옵티마이저라고 할 수 있습니다. 개발자가 SQL을 작성하고 실행하면 즉시 실행되는 것이 아니라 옵티마이저라는 곳에서 “이 쿼리문을 어떻게 실행시키겠다!”라는 여러가지 실행 계획을 세우고, 최고의 효율을 갖는 실행계획을 판별한 후 그 실행계획에 따라 쿼리를 수행하게 되는 것입니다.
비용기반 vs 규칙기반 : 규칙기반옵티마이저는, 실행 계획을 수립할 때 15개의 우선순위를 기준으로 실행계획을 수립합니다. 최신 오라클 버전은 규칙기반 옵티마이저보다 비용기반 옵티마이저를 기본으로 사용하는 것 으로 알고 있습니다. (/rule/사용) 반면에 비용기반 옵티마이저는 처리하는 비용이 가장 적은 실행계획을 선택하는 방식입니다. 비용을 예측하기 위해선 규칙기반 옵티마이저가 사용하지 않는 여러 통계정보등을 이용합니다.
실행계획 읽는 법
: 첫번째로 들여쓰기를 기준으로 안쪽부터 읽어주고, 들여쓰기 기준 동일한 뎁스면 위에서 아래로 내려오면서 읽습니다.

비용 기반 옵티마이저(CBO)는 다양한 쿼리 실행 계획을 생성하고 이들 중에서 데이터를 가져오는 데 필요한 리소스 비용이 가장 적은 계획을 선택합니다. 반면, 규칙 기반 옵티마이저(RBO)는 미리 정의된 규칙 세트를 사용하여 쿼리를 실행하는 방식을 결정합니다.
(참고: 통계정보 = 테이블 행의 수, 컬럼의 분포 등.. 최신으로 UPDATE해주는게 좋다
실행계획: 어떤 순서로 테이블을 스캔할지, 조인은 어떤 방식으로 할지, 인덱스는 뭐탈지)

★트랜잭션

트랜잭션
여러개의 작업을 하나로 묶은 단위입니다.
트랜잭션은 작업의 완전성을 보장해줍니다.
즉, 작업들을 모두 처리하거나 처리하지 못할 경우 이전 상태로 복구하여 작업의 일부만 적용되는 현상이 발생하지 않게 만들어주는 기능입니다.
하나의 트랜잭션은 Commit(작업완료)되거나 Rollback(취소)됩니다.
트랜잭션의 특성(ACID)
(1) 원자성(Atomicity) 작업이 모두 반영되던지 아니면 전혀 반영되지 않아야 한다.
(2) 일관성(Consistency) 실행이 완료되면 언제나 일관성 있는 상태를 유지해야 한다.
(3) 독립성(Isolation) 둘 이상 트랜잭션이 동시에 실행될 경우 서로의 연산에 끼어들 수 없다.
(4) 영속성(Durability) 완료된 결과는 영구적으로 반영되어야 한다.

DB connectionPool

DB와의 커넥션 객체를 미리 할당을 해서 Pool에 넣어두고, 이후 DB연결 요청이 들어왔을때 Pool의 있는 Connection을 사용하고, 작업이 끝나면 반납하는방식을 의미합니다. Connection을 잇는 작업을 중복으로 처리되지 않도록해서 성능을 올리는 방법입니다.
커넥션 풀의 주요 장점은 다음과 같습니다:

(1)효율성: 데이터베이스 연결은 생성하고 끊는데 비용이 드는 작업이며, 시간도 많이 소요됩니다. 커넥션 풀을 이용하면 이미 생성된 연결을 재사용할 수 있으므로 이러한 비용과 시간을 크게 절약할 수 있습니다.

(2)성능 개선: 애플리케이션이 요구하는 동시 데이터베이스 연결 수를 미리 생성해두었다가 필요할 때 즉시 제공할 수 있으므로 애플리케이션의 응답 시간을 단축시키고 전반적인 성능을 향상시킵니다.

(3)유연성: 풀의 크기는 일반적으로 구성 가능하므로, 애플리케이션의 요구사항에 따라 커넥션 수를 증가시키거나 감소시킬 수 있습니다.

하지만 커넥션 풀의 크기를 적절히 관리하는 것이 중요합니다. 커넥션 풀이 너무 작으면 요청이 많을 때 커넥션의 부족으로 성능 문제가 발생할 수 있고, 반대로 너무 크면 불필요한 자원을 소비하게 됩니다. 따라서 커넥션 풀의 크기는 애플리케이션의 동시 사용자 수, 하드웨어 용량, 데이터베이스 서버의 설정 등을 고려하여 적절히 조정해야 합니다.

★★파티셔닝

파티셔닝 : table을 ‘파티션(partition)’이라는 작은 단위로 나누어 관리하는 기법입니다. (참고: 수평 파티셔닝 = 샤딩)
장점 : 필요한 부분만 탐색해서 성능이 증가합니다. 즉, 풀스캔에서 데이터 액세스의 범위를 줄여서 성능 향상을 가져옵니다. (필요한 데이터만 빠르게 조회) 그리고 파티션별로 조회, 백업 및 복구가 가능합니다.
단점 : 테이블간 JOIN에 대한 비용이 증가하고, 테이블과 인덱스를 따로 파티셔닝 할 수 없습니다. (같이 파티션 해야함)

대용량 데이터 INSERT 방법

작업량 적은 시간대에 진행
Index 임시로 제거할 수 있으면 제거한 후, insert 작업 끝나면 재구성
분산 처리나 클러스터링을 통해 서버의 처리능력을 확장

대용량 데이터 SELECT 방법

Index 생성
파티셔닝으로 테이블 분리
분산 처리나 클러스터링을 통해 서버의 처리능력을 확장

데이터 관련 언어

DDL (정의어 : Data Definition Language) : 데이터베이스 구조를 정의, 수정, 삭제하는 언어 ( alter, create, drop )
DML (조작어 : Data Manipulation Language) : 데이터베이스내의 자료 검색, 삽입, 갱신, 삭제를 위한 언어 ( select, insert, update, delete )
DCL (제어어 : Data Control Language) : 데이터에 대해 무결성 유지, 병행 수행 제어, 보호와 관리를 위한 언어 (revoke,grant)
TCL 트랜잭션 제어 언어 (commit, rollback)

기타

1) 트리거 : 트리거는 특정 테이블에 대한 이벤트에 반응해 INSERT, DELETE, UPDATE 같은 DML 문이 수행되었을 때, 데이터베이스에서 자동으로 동작하도록 작성된 프로그램입니다.
사용자가 직접 호출하는 것이 아닌, 데이터베이스에서 자동적으로 호출한다는 것이 가장 큰 특징입니다.
2) 공유락 VS 베타락 :
B Lock은 트랜잭션 처리의 동시성을 제어하기 위한 메커니즘입니다.
공유락(LS, Shared Lock) Read Lock라고도 하는 공유락은 트랜잭션이 읽기를 할 때 사용하는 락이며, 데이터를 읽기만하기 때문에 같은 공유락 끼리는 동시에 접근이 가능합니다. 공유락이 획득되어 있는 동안 다른 트랜잭션은 동일한 데이터를 읽을 수 있지만, 베타락을 획득하여 데이터를 수정하는 것은 불가능합니다.
베타락(LX, Exclusive Lock) Write Lock라고도 하는 베타락은 데이터를 변경할 때 사용하는 락입니다. 트랜잭션이 완료될 때까지 유지되며, 베타락이 끝나기 전까지 어떠한 접근도 허용하지 않습니다. 데이터의 동시 수정을 방지하기 위해 사용됩니다. 베타락을 획득한 트랜잭션은 해당 데이터에 대한 읽기 및 쓰기 작업을 수행할 수 있습니다. 베타락을 획득한 트랜잭션이 데이터를 수정하는 동안 다른 트랜잭션은 해당 데이터에 대한 읽기 및 쓰기 작업을 수행할 수 없습니다.
3) 클러스터링 VS 리플리케이션
클러스터링이란 여러 개의 DB를 수평적인 구조로 구축하는 방식입니다. 동기 방식으로 사용합니다.
DB 간의 데이터를 동기화하여 항상 일관성있는 데이터를 얻을 수 있다.
1개의 DB가 죽어도 다른 DB가 살아 있어 시스템을 장애없이 운영할 수 있다.
저장소 하나를 공유하면 병목현상이 발생할 수 있다. 비용이 높다.

리플리케이션이란 여러개의 DB를 권한에 따라 수직적인 구조로 구축하는 방식입니다. 비동기 방식으로 사용합니다.
비동기 방식으로 운영되어 지연 시간이 거의 없다.
노드들 간 데이터 동기화가 보장되지 않아 일관성있는 데이터를 얻지 못할 수 있다.
Master DB가 다운되면 복구 및 대처가 까다롭다.
4) 정규화란?
중복을 최대한 줄여 데이터를 구조화하고, 불필요한 데이터를 제거해 데이터를 논리적으로 저장하는 것.이상현상이 일어나지 않도록 정규화 시킨다!