웹 보안 신입 데이터베이스 기술면접
새 면접Q. 사용자 인증 시스템을 설계할 때 사용자 테이블(users)과 로그인 이력 테이블(login_history)을 분리하는 이유는 무엇인가요? 두 테이블은 어떤 관계(1:1, 1:N, N:M)로 설계되어야 하며, 보안 관점에서 로그인 이력 테이블에 반드시 포함되어야 할 컬럼들은 무엇인가요?
한 사용자가 여러 번 로그인할 수 있다는 점과, 보안 감사(audit)를 위해 필요한 정보들을 생각해보세요.
사용자 테이블과 로그인 이력 테이블은 1:N 관계로 설계되어야 합니다. 한 사용자가 여러 번 로그인할 수 있기 때문입니다. 테이블을 분리하는 이유는 사용자 기본 정보와 이력 데이터의 성격이 다르고, 이력 데이터는 계속 증가하므로 성능과 관리 측면에서 분리가 필요하기 때문입니다. 로그인 이력 테이블에는 user_id(외래키), login_time(로그인 시각), ip_address(접속 IP), user_agent(브라우저 정보), login_status(성공/실패), logout_time 등이 포함되어야 합니다. 이러한 정보는 비정상적인 접근 패턴 탐지, 계정 도용 추적, 보안 사고 조사 등에 활용됩니다.
- • 1:N 관계로 설계 (한 사용자가 여러 로그인 이력 보유)
- • 이력 데이터의 지속적 증가로 인한 성능 고려
- • 보안 감사를 위한 필수 컬럼: IP 주소, 시각, 상태, User-Agent
실제 서비스에서 비정상적인 로그인 시도나 계정 도용을 탐지하고, 보안 사고 발생 시 원인을 추적하는 데 사용됩니다.
로그인 이력 테이블의 데이터가 수백만 건으로 증가했을 때, 특정 사용자의 최근 로그인 이력을 빠르게 조회하려면 어떤 인덱스를 설계해야 할까요?
Q. 대용량 게시판 서비스에서 게시글 테이블(posts)에 title, content, author_id, created_at, is_deleted 컬럼이 있습니다. 삭제되지 않은 게시글을 최신순으로 조회하는 쿼리가 자주 실행되는데, 어떤 인덱스를 생성해야 효율적일까요? 단일 컬럼 인덱스와 복합 인덱스 중 어떤 것이 적합하며, 인덱스 컬럼의 순서는 어떻게 결정해야 하나요?
WHERE 절의 조건과 ORDER BY 절을 모두 고려하고, 카디널리티(고유값의 비율)를 생각해보세요.
is_deleted와 created_at을 포함하는 복합 인덱스를 생성해야 합니다. 인덱스는 INDEX(is_deleted, created_at DESC) 형태로 만드는 것이 효율적입니다. is_deleted를 먼저 두는 이유는 WHERE 절의 필터링 조건이기 때문이며, created_at을 다음에 두어 정렬까지 인덱스로 처리할 수 있게 합니다. 단일 컬럼 인덱스를 각각 만들면 옵티마이저가 하나만 선택하거나 인덱스 머지를 사용하게 되어 비효율적입니다. 다만 is_deleted는 boolean 타입으로 카디널리티가 낮지만, 대부분의 데이터가 false이므로 선택도(selectivity)가 높아 인덱스 효과가 있습니다.
- • 복합 인덱스 사용: (is_deleted, created_at DESC)
- • WHERE 조건 컬럼을 먼저, ORDER BY 컬럼을 나중에 배치
- • 카디널리티가 낮아도 선택도가 높으면 인덱스 효과 있음
게시판, SNS 피드, 공지사항 등에서 삭제되지 않은 최신 글을 빠르게 조회할 때 필수적으로 사용되는 인덱스 설계 패턴입니다.
만약 작성자별로 게시글을 조회하는 쿼리도 자주 실행된다면, 인덱스 전략을 어떻게 수정하시겠습니까?
Q. 은행 계좌 이체 기능을 구현할 때, A 계좌에서 B 계좌로 10만원을 이체하는 도중 시스템 오류가 발생하면 어떤 문제가 생길 수 있나요? 이를 방지하기 위해 트랜잭션을 어떻게 구성해야 하며, 트랜잭션 격리 수준(Isolation Level) 중 어떤 수준이 적합한가요?
돈이 중복으로 빠지거나, 한쪽만 반영되는 상황을 생각해보고, ACID 속성 중 원자성과 격리성을 고려하세요.
트랜잭션 없이 구현하면 A 계좌에서는 돈이 빠졌는데 B 계좌에는 입금되지 않는 불일치 문제가 발생할 수 있습니다. 이를 방지하려면 출금과 입금을 하나의 트랜잭션으로 묶어 원자성을 보장해야 합니다. BEGIN TRANSACTION으로 시작해서 A 계좌 잔액 확인 및 차감, B 계좌 잔액 증가를 수행하고, 모두 성공하면 COMMIT, 하나라도 실패하면 ROLLBACK 해야 합니다. 격리 수준은 READ COMMITTED 이상이 필요하며, 동시에 여러 이체가 발생할 때 잔액 부족 문제를 정확히 처리하려면 REPEATABLE READ나 SERIALIZABLE 수준이 적합합니다. 또한 A 계좌 조회 시 SELECT FOR UPDATE를 사용해 락을 걸어 동시성 문제를 방지해야 합니다.
- • 출금과 입금을 하나의 트랜잭션으로 묶어 원자성 보장
- • 격리 수준은 최소 READ COMMITTED, 동시성 제어가 중요하면 REPEATABLE READ
- • SELECT FOR UPDATE로 계좌 락을 걸어 동시 이체 문제 방지
금융 서비스, 포인트 시스템, 재고 관리 등 정확한 수량 관리가 필요한 모든 시스템에서 필수적으로 사용됩니다.
만약 두 사용자가 동시에 같은 계좌에서 이체를 시도할 때, 잔액이 부족한 상황이 발생하지 않으려면 어떤 락 전략을 사용해야 할까요?
Q. 쇼핑몰에서 두 사용자가 동시에 주문을 하는데, 사용자 A는 상품1→상품2 순서로, 사용자 B는 상품2→상품1 순서로 재고를 차감하는 트랜잭션을 실행하고 있습니다. 갑자기 시스템이 멈추고 데이터베이스 로그에 'Deadlock detected'라는 메시지가 나타났습니다. 데드락이 무엇이며, 왜 발생했고, 어떻게 방지할 수 있나요?
두 트랜잭션이 서로가 가진 자원을 기다리는 상황을 생각해보고, 락을 획득하는 순서를 고려하세요.
데드락은 두 개 이상의 트랜잭션이 서로가 보유한 락을 기다리면서 무한 대기 상태에 빠지는 현상입니다. 이 경우 트랜잭션 A가 상품1에 락을 걸고 상품2를 기다리는 동안, 트랜잭션 B는 상품2에 락을 걸고 상품1을 기다리면서 데드락이 발생했습니다. 데이터베이스는 이를 감지하면 한 트랜잭션을 강제로 롤백시켜 해결합니다. 데드락을 방지하려면 모든 트랜잭션에서 자원(상품)에 접근하는 순서를 동일하게 통일해야 합니다. 예를 들어 상품 ID 오름차순으로 정렬해서 락을 획득하도록 구현하면 됩니다. 추가로 트랜잭션 시간을 최소화하고, 락 타임아웃을 설정하는 것도 도움이 됩니다.
- • 데드락은 트랜잭션들이 서로의 락을 기다리며 무한 대기하는 상태
- • 자원 접근 순서를 통일하여 방지 (예: ID 오름차순)
- • 트랜잭션 시간 최소화 및 락 타임아웃 설정
쇼핑몰 주문 처리, 예약 시스템, 재고 관리 등 여러 자원을 동시에 잠그는 트랜잭션에서 자주 발생하는 문제입니다.
데드락이 자주 발생하는 시스템에서 모니터링과 알림을 어떻게 구성하시겠습니까?
Q. 회원 테이블(users)에 100만 건의 데이터가 있고, 특정 이메일로 사용자를 찾는 로그인 쿼리가 5초 이상 걸립니다. EXPLAIN 명령어로 실행 계획을 확인했더니 'type: ALL'이라고 표시되었습니다. 이것이 의미하는 바는 무엇이며, 쿼리 성능을 개선하기 위해 어떤 조치를 취해야 하나요?
type: ALL은 전체 테이블을 스캔한다는 의미이며, 이메일로 빠르게 검색하려면 어떤 데이터베이스 구조가 필요한지 생각해보세요.
EXPLAIN에서 'type: ALL'은 풀 테이블 스캔(Full Table Scan)을 의미하며, 인덱스를 사용하지 않고 테이블의 모든 행을 순차적으로 검색한다는 뜻입니다. 100만 건을 모두 읽기 때문에 성능이 매우 느립니다. 이를 개선하려면 email 컬럼에 인덱스를 생성해야 합니다. CREATE INDEX idx_email ON users(email)을 실행하면 B-Tree 인덱스가 생성되어 O(log N) 시간에 검색이 가능해집니다. 인덱스 생성 후 EXPLAIN을 다시 실행하면 'type: ref' 또는 'type: const'로 바뀌고, rows 값이 크게 줄어든 것을 확인할 수 있습니다. 이메일은 UNIQUE 속성이 있으므로 UNIQUE INDEX로 생성하면 더욱 효율적이며, 중복 데이터도 방지할 수 있습니다.
- • type: ALL은 풀 테이블 스캔으로 모든 행을 검색하여 느림
- • email 컬럼에 인덱스 생성으로 검색 성능 대폭 개선
- • UNIQUE INDEX 사용으로 중복 방지 및 성능 최적화
로그인, 회원 조회, 이메일 인증 등 특정 값으로 사용자를 찾는 모든 기능에서 인덱스는 필수적입니다.
인덱스를 생성한 후에도 여전히 느리다면, 어떤 추가 원인들을 확인해봐야 할까요?
아직 댓글이 없습니다. 첫 번째 댓글을 남겨보세요!