Laravel 미드레벨 데이터베이스 기술면접

복합 인덱스는 (user_id, action_type, status, created_at) 순서로 구성하는 것이 최적입니다. 등호 조건(=)으로 필터링되는 컬럼을 앞에 배치하고, 범위 조건(BETWEEN)은 마지막에 위치시켜야 인덱스를 최대한 활용할 수 있습니다. user_id는 카디널리티가 높고 가장 많은 데이터를 걸러내므로 첫 번째에 위치하며, action_type과 status는 등호 조건이므로 중간에, created_at은 범위 조건이므로 마지막에 배치합니다. Laravel 마이그레이션에서는 $table->index(['user_id', 'action_type', 'status', 'created_at'], 'idx_user_activity')로 생성할 수 있습니다. 인덱스 순서가 잘못되면 부분 인덱스 스캔만 가능하거나 인덱스를 전혀 사용하지 못할 수 있으므로, 쿼리 패턴에 맞는 정확한 순서 설계가 중요합니다.

데드락은 트랜잭션 A가 재고를 잠그고 포인트를 기다리는 동안, 트랜잭션 B가 포인트를 잠그고 재고를 기다리는 상황에서 발생합니다. 해결을 위해서는 첫째, 모든 트랜잭션에서 자원을 동일한 순서(예: 항상 재고 먼저, 포인트 나중)로 잠가야 합니다. 둘째, Laravel에서 DB::transaction() 내부에서 lockForUpdate()를 사용할 때 테이블 접근 순서를 일관되게 유지합니다. 셋째, config/database.php에서 'options' => [PDO::ATTR_TIMEOUT => 3]으로 락 타임아웃을 설정합니다. 넷째, 데드락 발생 시 자동 재시도를 위해 DB::transaction(function() {...}, 3)처럼 재시도 횟수를 지정하거나, catch (QueryException $e)로 데드락 에러(1213)를 감지하여 수동으로 재시도 로직을 구현합니다. 추가로 트랜잭션 범위를 최소화하고 실행 시간을 단축하여 락 경합을 줄이는 것도 중요합니다.

커버링 인덱스는 쿼리에서 필요로 하는 모든 컬럼을 인덱스 자체에 포함시켜, 인덱스만으로 쿼리를 완전히 처리할 수 있게 하는 기법입니다. 이 쿼리에는 (category_id, created_at, id, title, author_name) 순서로 복합 인덱스를 생성하면, WHERE 조건과 ORDER BY를 인덱스로 처리하고 SELECT 컬럼도 인덱스에서 직접 가져올 수 있어 테이블 접근(랜덤 I/O)이 완전히 제거됩니다. Laravel 마이그레이션에서 $table->index(['category_id', 'created_at', 'id', 'title', 'author_name'], 'idx_covering')으로 생성합니다. 성능 이점은 디스크 I/O가 크게 감소하여 응답 속도가 수배~수십 배 향상되지만, 인덱스 크기가 커져 INSERT/UPDATE 성능이 저하되고 메모리 사용량이 증가하는 단점이 있습니다. 따라서 조회가 압도적으로 많고 인덱스에 포함할 컬럼이 많지 않은 경우에 적합합니다.

조회와 수정 사이에 다른 트랜잭션이 잔액을 변경하면 Lost Update 문제가 발생하여 잔액이 부정확해질 수 있습니다. Pessimistic Lock은 SELECT ... FOR UPDATE로 조회 시점에 행을 잠가 다른 트랜잭션의 접근을 차단하며, 충돌이 자주 발생하는 상황에 적합합니다. Laravel에서 Wallet::where('user_id', $userId)->lockForUpdate()->first()로 구현합니다. Optimistic Lock은 version 컬럼을 추가하여 UPDATE 시점에 version을 확인하고, 변경되었으면 재시도하는 방식으로 충돌이 드문 상황에 적합합니다. Laravel에서는 WHERE user_id = ? AND version = ? 조건으로 UPDATE하고 affected rows가 0이면 재시도합니다. 결제 시스템처럼 충돌 가능성이 높고 데이터 정합성이 중요한 경우 Pessimistic Lock이 적합하며, 읽기가 많고 쓰기가 드문 경우 Optimistic Lock이 성능상 유리합니다. 추가로 트랜잭션 격리 수준을 REPEATABLE READ 이상으로 설정해야 합니다.

whereHas()는 EXISTS 서브쿼리를 사용하여 각 메인 테이블의 행마다 서브쿼리를 실행하므로, 데이터가 많을수록 성능이 급격히 저하됩니다. JOIN 방식은 Post::join('comments', 'posts.id', '=', 'comments.post_id')->where('comments.status', 'approved')->select('posts.*')->distinct()->get()처럼 작성하며, 한 번의 조인으로 처리되어 빠르지만 1:N 관계에서 중복 제거를 위해 DISTINCT가 필요합니다. whereIn 서브쿼리 방식은 $commentedPostIds = Comment::where('status', 'approved')->pluck('post_id'); Post::whereIn('id', $commentedPostIds)->get()처럼 두 번의 쿼리로 나누어 실행하며, 서브쿼리 결과가 작을 때 효율적이고 코드 가독성이 좋습니다. 관계 테이블의 데이터가 적으면 whereIn이, 많으면 JOIN이 유리하며, 복잡한 조건이 있으면 whereIn으로 명확하게 분리하는 것이 유지보수에 좋습니다. 실제 성능은 데이터 분포와 인덱스 상태에 따라 다르므로 EXPLAIN으로 확인해야 합니다.

Range Partitioning은 특정 컬럼(created_at)의 값 범위에 따라 데이터를 물리적으로 다른 파티션에 저장하는 기법입니다. 예를 들어 2024년 1월 데이터는 p202401 파티션에, 2월 데이터는 p202402 파티션에 저장됩니다. 파티션 프루닝은 쿼리의 WHERE 조건에 파티션 키가 포함되면 MySQL이 자동으로 해당 파티션만 스캔하여 성능을 크게 향상시키는 최적화 기법입니다. 예를 들어 WHERE created_at >= '2024-01-01' AND created_at < '2024-02-01' 조건이면 p202401 파티션만 접근합니다. 오래된 데이터 삭제는 ALTER TABLE logs DROP PARTITION p202301처럼 파티션 단위로 즉시 삭제 가능하며, DELETE 쿼리보다 수백 배 빠릅니다. Laravel에서는 마이그레이션에서 DB::statement()로 파티션 생성 DDL을 실행하고, 스케줄러로 매월 새 파티션 추가 및 오래된 파티션 삭제 작업을 자동화할 수 있습니다. 파티션 키는 반드시 WHERE 절에 자주 사용되는 컬럼이어야 하며, 프라이머리 키와 유니크 키에 파티션 키가 포함되어야 합니다.

EXPLAIN 결과에서 type은 접근 방식(const, ref, range, index, ALL 순으로 느려짐)을 나타내고, key는 실제 사용된 인덱스, rows는 예상 스캔 행 수, Extra는 추가 정보(Using index는 커버링 인덱스, Using filesort는 정렬 발생)를 보여줍니다. type이 ALL이거나 rows가 과도하게 크면 성능 문제가 있습니다. 옵티마이저가 통계 정보 부족이나 복잡한 조건으로 잘못된 인덱스를 선택하면, Laravel에서 DB::select(DB::raw('SELECT * FROM users USE INDEX (idx_name) WHERE ...'))->get()이나 Model::from(DB::raw('users FORCE INDEX (idx_name)'))->where(...)->get()처럼 힌트를 사용할 수 있습니다. USE INDEX는 제안, FORCE INDEX는 강제입니다. 장점은 즉각적인 성능 개선이지만, 단점은 데이터 분포 변경 시 오히려 성능이 악화될 수 있고 유지보수가 어려워집니다. 따라서 통계 정보 갱신(ANALYZE TABLE), 인덱스 재설계, 쿼리 구조 개선을 먼저 시도하고, 불가피한 경우에만 힌트를 사용하며 정기적으로 재검토해야 합니다.