전체 글

[MSSQL] INCLUDE 컬럼 설계로 Key Lookup 병목을 제거하는 판단 기준

INCLUDE 컬럼: 실무적 정의와 운영상 중요성MSSQL에서 Key Lookup은 비클러스터형 인덱스(Nonclustered Index)에서 검색 조건을 만족하는 행을 찾은 뒤, SELECT에 필요한 나머지 컬럼을 가져오기 위해 클러스터형 인덱스에 다시 접근하는 연산이다. Heap 테이블에서는 같은 목적의 RID Lookup이 발생한다. 검색 행마다 반복되는 임의 페이지 접근이므로 반환 행이 증가할수록 논리 읽기와 CPU 사용량이 함께 증가한다.INCLUDE 컬럼은 비클러스터형 인덱스의 리프 레벨에 조회용 컬럼을 저장한다. 검색 키 순서에는 참여하지 않지만 쿼리에 필요한 컬럼을 인덱스 내부에서 모두 반환할 수 있게 한다. 이 상태를 커버링 인덱스(Covering Index)라고 하며, 적절히 설계하면 ..

[MySQL] 커버링 인덱스 설계: 테이블 접근 제거 조건과 과도한 인덱스 비용

커버링 인덱스: 실무적 정의와 운영상 중요성MySQL에서 커버링 인덱스(Covering Index)는 쿼리가 요구하는 컬럼을 하나의 인덱스에서 모두 읽을 수 있도록 설계한 인덱스이다. 검색 조건만 인덱스에 존재하는 것으로는 충분하지 않다. SELECT, WHERE, JOIN, ORDER BY, GROUP BY 처리에 필요한 컬럼이 인덱스만으로 해결되어야 테이블 레코드 접근을 제거할 수 있다.InnoDB의 보조 인덱스 리프에는 보조 인덱스 키와 기본 키(Primary Key)가 저장된다. 따라서 기본 키 컬럼은 보조 인덱스 정의에 직접 나열하지 않아도 커버링에 이용될 수 있다. 반면 인덱스에 없는 일반 컬럼을 SELECT하면 보조 인덱스에서 기본 키를 찾은 뒤 클러스터드 인덱스를 다시 탐색해야 한다.커버링..

[MSSQL] 복합 인덱스 선행 컬럼 누락이 Index Scan을 만드는 이유와 키 순서 설계

복합 인덱스 키 순서: 실무적 정의와 운영상 중요성MSSQL의 복합 인덱스(Composite Index)는 둘 이상의 컬럼을 하나의 키로 구성한 B-Tree 인덱스이다. INDEX (CustomerID, OrderDate)는 CustomerID를 먼저 정렬하고, CustomerID가 같은 행 내부에서 OrderDate를 정렬한다.따라서 전체 인덱스가 OrderDate 순서로 정렬된 것은 아니다. 첫 번째 키인 CustomerID가 조건절에서 빠지면 SQL Server는 특정 OrderDate가 존재할 연속 범위의 시작점과 종료점을 계산할 수 없다. 이 경우 옵티마이저는 후행 키 조건을 만족하는 행을 찾기 위해 인덱스 리프 페이지를 넓게 읽는 Index Scan을 선택할 수 있다.핵심은 WHERE 절에 조..

[MySQL] 복합 인덱스 컬럼 순서: 등가 조건을 앞에, 범위 조건을 뒤에 배치하는 원칙

복합 인덱스 컬럼 순서: 실무적 정의와 운영상 중요성MySQL에서 복합 인덱스(Composite Index)는 둘 이상의 컬럼 값을 지정된 순서로 정렬해 저장하는 B-Tree 인덱스이다. WHERE 절에 등가 조건과 범위 조건이 함께 있다면 일반적인 배치 원칙은 등가 조건 컬럼을 앞에 두고, 실제 탐색 범위를 결정할 범위 조건 컬럼을 그 뒤에 두는 것이다.예를 들어 tenant_id = ?, status = ?, created_at >= ?가 함께 사용된다면 기본 후보는 (tenant_id, status, created_at)이다. 선행 등가 조건으로 탐색할 인덱스 구간을 고정한 다음, 해당 구간 안에서 날짜 범위를 연속적으로 읽을 수 있기 때문이다.다만 이를 모든 쿼리에 적용되는 절대 규칙으로 이해하면..

[MSSQL] Actual Execution Plan의 Estimated Rows 오차 원인과 카디널리티 교정 절차

Actual Execution Plan: 실무적 정의와 운영상 중요성MSSQL의 Actual Execution Plan은 옵티마이저가 컴파일 시점에 선택한 계획과 실행 중 수집된 행 수 정보를 함께 보여 주는 진단 자료이다. Estimated Number of Rows는 통계와 카디널리티 추정 모델을 이용한 예상값이며, Actual Number of Rows는 해당 실행에서 연산자가 반환한 행 수이다.두 값의 차이는 단순한 표시 오차가 아니다. 옵티마이저는 예상 행 수를 기준으로 인덱스 접근 방식, 조인 순서, Nested Loops·Hash Match·Merge Join 선택, 병렬 처리 여부와 메모리 그랜트(Memory Grant)를 결정한다. 따라서 하위 연산자의 작은 추정 오류가 상위 조인과 정렬..

[MySQL] EXPLAIN ANALYZE 카디널리티 오차 진단과 실행 계획 튜닝 순서

EXPLAIN ANALYZE: 실무적 정의와 운영상 중요성MySQL에서 EXPLAIN은 옵티마이저가 쿼리를 실행하기 전에 계산한 예상 실행 계획을 반환한다. 반면 EXPLAIN ANALYZE는 쿼리를 실제로 실행한 뒤 각 iterator의 소요 시간, 반환 행 수, 반복 횟수를 예상값과 함께 표시한다.추정 행 수와 실제 처리 행 수가 다를 때 가장 먼저 확인할 항목은 전체 실행 시간이 아니다. 실행 계획의 하위 노드부터 estimated rows와 actual rows가 처음 크게 벌어지는 지점을 찾아야 한다. 상위 조인 노드의 오차는 하위 테이블 접근 단계에서 발생한 카디널리티 오류가 누적된 결과일 가능성이 높다.카디널리티 추정이 틀리면 옵티마이저는 잘못된 인덱스, 조인 순서, Nested Loop 반..

[MySQL] 갭 락과 넥스트키 락 재현: 없는 값을 조회해도 INSERT가 막히는 이유

데이터베이스를 다루다 보면 분명히 존재하지 않는 값을 조회했을 뿐인데, 다른 세션에서 실행한 INSERT 문이 아무 이유 없이 멈춰버리는 상황을 마주하게 됩니다. 개발자 입장에서는 "그 값은 테이블에 없는데 왜 잠기지?"라는 의문이 들 수밖에 없습니다. 이때 원인은 대부분 InnoDB 스토리지 엔진의 갭 락(Gap Lock)과 넥스트키 락(Next-Key Lock)입니다. 이 락들은 존재하는 레코드가 아니라 인덱스 레코드와 레코드 사이의 빈 공간에 걸리기 때문에, 조회 결과가 0건이어도 다른 트랜잭션의 INSERT를 차단할 수 있습니다. 이번 글에서는 갭 락과 넥스트키 락의 동작 원리를 정리하고, 두 세션을 이용한 재현 SQL과 잠긴 구간을 실제로 확인하는 방법, 그리고 실무에서 이 락을 다룰 때의 주의..

[MySQL] 메타데이터 락 때문에 멈춘 DDL 진단과 안전한 해소 절차

데이터베이스를 다루다 보면 분명 무거운 쿼리도 없고 InnoDB 락 경합도 안 보이는데, ALTER TABLE 같은 DDL 문이 실행조차 되지 못하고 그대로 멈춰 있는 상황을 만나게 됩니다. SHOW PROCESSLIST를 봐도 딱히 원인이 될 만한 것이 없어 보이고, 담당자는 배포 창구 시간이 흘러가는데도 손을 못 대는 경우가 많습니다. 이때 가장 흔하게 의심해야 할 것이 바로 메타데이터 락(Metadata Lock, 이하 MDL)입니다. 이 글에서는 MDL이 무엇이고 왜 걸리는지부터, performance_schema를 이용해 원인이 되는 세션을 정확히 찾아내는 방법, 그리고 서비스에 영향을 최소화하면서 안전하게 대기를 해소하는 절차까지 실무 순서대로 깊이 있게 다루어 보겠습니다.1. 메타데이터 락(..

[MySQL] NOWAIT와 SKIP LOCKED 활용: 대기 없는 작업 큐 구현과 한계

데이터베이스를 다루다 보면 여러 개의 프로세스가 같은 작업 테이블을 두고 경쟁하는 상황을 자주 만나게 됩니다. 흔히 "작업 큐(Job Queue)"라고 부르는 구조인데, 배치 워커 여러 대가 동시에 떠서 대기 중인 작업(PENDING) 행을 하나씩 가져가 처리하는 패턴입니다. 이때 가장 먼저 떠올리는 방법이 SELECT ... FOR UPDATE로 행을 잠근 뒤 상태를 변경하는 것인데, 워커 수가 늘어나는 순간 문제가 생깁니다. 한 워커가 이미 잠근 행을 다른 워커가 FOR UPDATE로 또 조회하면, 뒤따라온 워커는 잠금이 풀릴 때까지 그대로 대기(Lock Wait)하게 됩니다. 워커가 많아질수록 대기 행렬이 길어지고, 결국 innodb_lock_wait_timeout에 걸려 타임아웃 에러를 뿜어내는 ..

[MySQL] SELECT FOR UPDATE와 FOR SHARE 비교: 예약·재고 처리 잠금 설계

데이터베이스를 다루다 보면 재고 차감이나 좌석 예약처럼 '먼저 값을 읽고, 조건을 확인한 뒤, 다시 값을 갱신하는' 로직을 자주 작성하게 됩니다. 문제는 이 읽기와 쓰기 사이에 다른 트랜잭션이 끼어들면 초과 판매나 중복 예약 같은 정합성 문제가 발생한다는 점입니다. 이때 가장 흔하게 사용되는 것이 바로 비관적 잠금(Pessimistic Locking)이며, MySQL의 InnoDB 스토리지 엔진은 이를 위해 SELECT ... FOR UPDATE와 SELECT ... FOR SHARE라는 두 가지 잠금 읽기(Locking Read) 구문을 제공합니다. 이 글에서는 두 구문의 기본 개념과 잠금 종류부터 격리 수준별 동작 차이, 재고 차감 로직에 어떤 것을 선택해야 하는지, 그리고 데드락을 피하기 위한 NO..