MySQL 유의사항 — N+1·데드락·인덱스가 안 타는 함정

쿼리는 분명히 돈다. 그런데 어느 날부터 느려지고, 피크 타임에 가끔 터진다. 대부분은 새로운 버그가 아니라 처음부터 깔려 있던 함정이 데이터가 쌓이면서 드러난 것이다.

여기서는 자주 밟는 함정을 ‘왜 생기나 → 증상 → 어떻게 피하나’ 순서로 모았다. 깊은 InnoDB 튜닝과 인덱스 설계는 따로 다룬다.

→ 측정과 튜닝 방법은 MySQL 심화 튜닝에서 다룬다.

JPA 목록 조회 한 번에 쿼리가 수백 번 나간다. 왜?

왜 생기나. JPA의 LAZY 로딩 때문이다. Order를 조회할 때 연관된 OrderItem은 실제로 접근하기 전까지 로드하지 않는다. 반복문 안에서 연관 객체를 건드리면 루프 횟수만큼 쿼리가 추가로 나간다.

List<Order> orders = orderRepository.findAll(); // 쿼리 1번
for (Order order : orders) {
    order.getItems().size(); // 여기서 주문 수만큼 쿼리 N번
}
// 주문 100개 → 총 쿼리 101번

증상. 주문이 1,000개면 1,001번 쿼리가 나간다. 평소엔 모르다가 데이터가 쌓이면 특정 API가 느려지고, 피크 타임에 커넥션 풀이 순식간에 바닥난다.

어떻게 피하나. 페이징이 없으면 JOIN FETCH로 한 방에 가져온다. 컬렉션 페이징이 필요하면 default_batch_fetch_size로 IN 조회를 쓴다.

// 페이징 없을 때: fetch join (컬렉션이면 DISTINCT 필요)
@Query("SELECT DISTINCT o FROM Order o JOIN FETCH o.items WHERE o.userId = :userId")
List<Order> findByUserIdWithItems(@Param("userId") Long userId);

# 페이징과 함께 안전하게: 지연 로딩을 IN(...) 으로 묶음
spring:
  jpa:
    properties:
      hibernate:
        default_batch_fetch_size: 100

@OneToMany Fetch Join과 페이징을 같이 쓰면 Hibernate가 전체를 메모리에 올린 뒤 페이징하는 함정이 있다(HHH90003004 경고). 이 경우엔 batch size 방식을 쓴다.

인덱스를 걸었는데 EXPLAIN이 full scan이다. 왜?

왜 생기나. 인덱스는 “컬럼 값을 가공하지 않고 그대로” 쓸 때만 탄다. 컬럼에 함수나 연산을 씌우거나, 타입이 안 맞아 암묵적 형변환이 일어나면 인덱스가 죽는다.

증상. 인덱스를 만들었는데 EXPLAIN의 type: ALL, rows가 테이블 전체 수준이다.

어떻게 피하나. 함정별로 정리하면 이렇다.

-- ❌ 컬럼에 함수/연산 → 인덱스 못 탐
WHERE DATE(created_at) = '2026-06-29'
WHERE YEAR(created_at) = 2026
WHERE user_id + 0 = 123

-- ✅ 값을 변환, 컬럼은 그대로 → 인덱스 탐
WHERE created_at >= '2026-06-29 00:00:00'
  AND created_at <  '2026-06-30 00:00:00'

-- ❌ 암묵적 형변환: phone이 VARCHAR인데 숫자로 비교
--    MySQL이 컬럼 전체를 숫자로 변환 → 인덱스 무력화
WHERE phone = 01012345678

-- ✅ 컬럼 타입에 맞춰 문자열로 비교
WHERE phone = '01012345678'

-- ❌ 선두 와일드카드 LIKE: 앞이 % 면 B-tree 정렬을 못 씀
WHERE name LIKE '%kim'

-- ✅ 접두 검색은 인덱스를 탄다 (뒤쪽 % 만)
WHERE name LIKE 'kim%'

이 밖에도 OR로 묶인 조건은 한쪽만 인덱스가 있으면 옵티마이저가 full scan을 택하기 쉽다(UNION으로 분리하거나 양쪽 모두 인덱스). 그리고 status처럼 값 종류가 적은 낮은 카디널리티 컬럼은 단독 인덱스를 만들어도 옵티마이저가 안 쓰는 경우가 많다. 선택도가 높은 컬럼과 묶어서 복합 인덱스로 만드는 게 낫다.

→ 카디널리티·통계·EXPLAIN 읽는 법은 MySQL 심화 튜닝에서 자세히 다룬다.

복합 인덱스를 걸었는데도 안 탄다. 선두 컬럼 규칙이 뭔가?

왜 생기나. 복합 인덱스는 왼쪽 컬럼부터 순서대로 정렬된다. (a, b, c) 인덱스는 a를 안 쓰면 b, c만으로는 탐색을 시작할 수 없다. 또 범위 조건(>, <, BETWEEN, LIKE 'x%')이 나온 컬럼 뒤쪽 컬럼은 인덱스 탐색에 쓰이지 않는다.

증상. 인덱스가 있는데 EXPLAIN의 rows가 수백만이다.

어떻게 피하나. 등치 조건(=) 컬럼을 앞에, 범위 조건 컬럼을 맨 뒤에 둔다.

-- 자주 쓰는 쿼리
WHERE user_id = 123 AND status = 'PENDING' AND created_at >= '2026-01-01'

-- ❌ 범위 컬럼이 앞: created_at 뒤 user_id, status가 죽는다
CREATE INDEX idx_wrong ON orders (created_at, user_id, status);

-- ✅ 등치(user_id, status) 먼저 → 범위(created_at) 나중
CREATE INDEX idx_correct ON orders (user_id, status, created_at);

선두 컬럼을 조건에서 빼면(WHERE status = ?만) 이 인덱스는 아예 못 쓴다는 점도 같은 규칙이다.

→ B-tree 레벨 설명과 커버링 인덱스는 MySQL 심화 튜닝에 있다.

가끔 Deadlock found로 트랜잭션이 죽는다. 왜?

왜 생기나. 둘 이상의 트랜잭션이 같은 잠금들을 서로 다른 순서로 잡으려 할 때 발생한다. A는 row1 → row2 순으로, B는 row2 → row1 순으로 잡으면 서로 상대가 쥔 락을 기다리며 교착에 빠진다. InnoDB가 이를 감지하면 한쪽을 강제로 롤백시킨다.

T1: UPDATE accounts WHERE id=1;  -- row1 락 획득
T2: UPDATE accounts WHERE id=2;  -- row2 락 획득
T1: UPDATE accounts WHERE id=2;  -- row2 대기
T2: UPDATE accounts WHERE id=1;  -- row1 대기 → 데드락

증상. 평소엔 잘 되다가 동시성이 올라가면 간헐적으로 Deadlock found when trying to get lock 에러가 뜬다. 재현이 어렵다.

어떻게 피하나.

• 잠금 순서를 통일한다. 여러 행을 갱신할 때 항상 PK 오름차순처럼 정해진 순서로 접근하면 교착이 생기지 않는다.
• 트랜잭션을 짧게 유지한다. 트랜잭션 안에서 외부 API 호출이나 무거운 연산을 하지 않는다. 락을 잡고 있는 시간이 길수록 충돌 확률이 올라간다.
• 데드락은 완전히 없앨 수 없으므로, 애플리케이션에서 재시도 로직을 둔다.

REPEATABLE READ인데 가끔 의도치 않은 곳까지 락이 걸린다

왜 생기나. MySQL 기본 격리수준인 REPEATABLE READ에서 InnoDB는 팬텀 읽기를 막기 위해 존재하는 행뿐 아니라 행과 행 사이의 빈 구간(갭)에도 락을 건다. 이게 갭 락(Gap Lock), 그리고 행 락과 갭 락을 합친 넥스트키 락(Next-Key Lock)이다.

증상. 범위 조건으로 SELECT ... FOR UPDATE나 UPDATE를 했더니, 실제로 존재하지 않는 범위까지 잠겨서 다른 트랜잭션의 INSERT가 대기에 걸린다. 데드락의 숨은 원인이 되기도 한다.

-- id 사이에 갭 락이 걸려, 그 범위로의 INSERT가 막힐 수 있다
SELECT * FROM orders WHERE id BETWEEN 10 AND 20 FOR UPDATE;

어떻게 피하나.

• 락 범위를 좁힌다. 가능하면 PK 등치 조건으로 정확한 행만 잠근다.
• 불필요한 FOR UPDATE를 남발하지 않는다.
• 팬텀이 문제가 안 되는 워크로드라면 격리수준을 READ COMMITTED로 낮추는 선택지가 있다(갭 락이 거의 사라진다). 단, 반복 읽기 일관성이 깨지는 트레이드오프를 이해하고 바꿔야 한다.

페이징이 뒤로 갈수록 느려진다. LIMIT 1000000, 20이 문제인가?

왜 생기나. 큰 OFFSET은 버리는 행을 모두 읽는다. LIMIT 1000000, 20은 1,000,020행을 읽고 앞 100만 행을 버린 뒤 20행만 반환한다. 페이지가 뒤로 갈수록 선형으로 느려진다.

증상. 1페이지는 빠른데 뒤쪽 페이지가 점점 느려지다 타임아웃이 난다.

어떻게 피하나. 마지막으로 본 행을 커서로 삼는 키셋(커서) 페이징을 쓴다.

-- 다음 페이지: 직전 페이지 마지막 행의 (created_at, id)를 커서로
SELECT id, created_at FROM orders
WHERE (created_at <  '2026-06-15 10:30:00')
   OR (created_at =  '2026-06-15 10:30:00' AND id < 49823)
ORDER BY created_at DESC, id DESC
LIMIT 20;

(created_at, id) 인덱스가 있으면 커서 위치에서 20개만 읽고 멈춘다. 100만 페이지여도 속도가 같다. 단점은 특정 페이지로 직접 점프가 안 된다는 것 — 무한 스크롤·“더 보기”엔 잘 맞고, “3페이지로 이동” UX엔 OFFSET이 필요하다.

→ Slice 활용과 응답 비교는 MySQL 심화 튜닝에 있다.

DB가 새로 배포한 것도 없는데 하루 종일 서서히 느려진다

왜 생기나. 긴 트랜잭션이 범인인 경우가 많다. 트랜잭션을 열어둔 채 오래 두면 그 동안 Undo Log를 지울 수 없어 계속 쌓인다(MVCC). 모든 쿼리가 길어진 Undo 체인을 타고 올라가야 해서 점진적으로 느려진다. 동시에 그 트랜잭션이 쥔 락이 다른 트랜잭션을 계속 대기시킨다.

증상. 급격히 터지는 게 아니라 하루에 걸쳐 서서히 느려지다 어느 순간 무너진다. 원인을 찾기 어렵다.

어떻게 피하나.

• 트랜잭션을 짧게 유지한다. 배치에서 @Transactional로 수십만 행을 한 트랜잭션에 묶지 말고 청크로 끊는다.
• 트랜잭션 안에서 외부 API 호출·사용자 입력 대기를 하지 않는다(개발자 PC에서 트랜잭션 열고 퇴근하는 패턴이 대표적).
• 장기 미커밋 트랜잭션을 모니터링한다.

-- 오래 살아있는 트랜잭션 찾기
SELECT trx_id, trx_started, trx_mysql_thread_id, trx_query
FROM information_schema.innodb_trx
ORDER BY trx_started ASC;

트래픽이 조금 늘었을 뿐인데 커넥션이 거부된다

왜 생기나. 커넥션 풀 고갈이다. HikariCP 기본 maximumPoolSize=10인데, 느린 쿼리나 N+1로 커넥션을 오래 쥐면 대기가 쌓이고 타임아웃이 난다. 반대로 풀을 무작정 키우면 MySQL max_connections를 초과해 접속 자체가 거부된다.

증상. Connection is not available, request timed out 또는 MySQL의 Too many connections.

어떻게 피하나. 인스턴스 수까지 고려해 양쪽을 함께 설계한다.

DB max_connections >= (인스턴스 수 × maximumPoolSize) + 여유분

근본 원인은 대부분 커넥션을 오래 쥐는 느린 쿼리·N+1·긴 트랜잭션이다. 풀 크기를 키우기 전에 그쪽을 먼저 줄이는 게 맞다.

FK 락 경합도 같이 본다. 자식 테이블에 INSERT/UPDATE가 몰리면 부모 행에 대한 외래키 잠금 경합이 생겨 대기가 쌓일 수 있다. 핫한 부모 행을 여러 자식이 동시에 참조하는 구조라면 의심해볼 지점이다.

정리

함정 대부분의 공통 해법은 트랜잭션을 짧게, 쿼리는 인덱스를 타게다.

→ 측정·EXPLAIN·InnoDB 내부는 MySQL 심화 튜닝을 참고.