• Covering Index가 성능 향상을 가져오는 원리를 InnoDB의 인덱스 구조 관점에서 설명해주세요.
• Covering Index를 사용할 때 발생할 수 있는 단점은 무엇인가요?
• 어떤 경우에 Covering Index 사용을 고려해야 하나요?
• Index-Only Scan이 발생하는지 어떻게 확인할 수 있나요?

• 내 답변

정확히 잘 모르겠지만 추측을 해보자면, Convering Index를 생성함으로 인해서 Index 공간비용이 5~10배는 많아질 것 같습니다. 읽기에 대해서는 예를 들어서 게시판 조회 서비스가 있다고 가정하고 title과 thumbnail url을 반환해야 한다고 가정할 때 Index Scan만으로 데이터를 모두 조회할 수 있을 것 같습니다. 이 경우 terminal node에서 또 다른 random io 비용을 발생시키지 않을 수 있어서 대략 2배 가량의 성능 향상이 있을것 같습니다.

• 평가 결과

총점: 52/100점

Concept - 개념 정의 (10/20점)

• 정확성: Covering Index의 핵심 개념인 "Index-Only Scan"을 간접적으로 언급했으나 명확한 정의가 부족
• 강점: Index Scan만으로 데이터 조회 가능하다는 핵심 동작을 이해하고 있음
• 약점: "Covering Index란 무엇인가"에 대한 명확한 정의 문장이 없음
• 개선 방향: "쿼리에 필요한 모든 컬럼을 인덱스가 포함하여 테이블 접근 없이 인덱스만으로 쿼리를 처리하는 인덱스"라는 정의를 먼저 제시

Logic - 원리/동작 방식 (12/20점)

• 정확성: Random I/O 절감에 대해 언급했으나, InnoDB의 Secondary Index → Clustered Index 이중 조회 구조에 대한 설명이 없음
• 강점: Random I/O 비용 절감의 핵심 원리를 인지하고 있음
• 약점: 왜 Random I/O가 발생하는지(Secondary Index의 리프 노드에 PK만 저장됨)에 대한 원리 설명 부족
• 개선 방향: InnoDB의 Clustered Index 구조와 Secondary Index의 동작 방식을 설명하고, Covering Index가 이중 조회를 회피하는 원리를 설명

Example - 예시/비유 (12/20점)

• 강점: 게시판 조회 서비스라는 구체적인 예시를 제시함
• 약점: 실제 쿼리 예시나 EXPLAIN 결과, 구체적인 성능 수치가 없음
• 개선 방향: CREATE INDEX 문과 쿼리 예시, EXPLAIN 결과(Using index)를 포함하여 설명

Application - 활용/적용 (10/20점)

• 강점: 게시판 조회라는 실무 사례 언급
• 약점: 언제 사용하고 언제 사용하지 말아야 하는지, 어떤 상황에서 효과적인지에 대한 구체적 가이드 없음
• 개선 방향: 조회가 빈번한 컬럼, 좁은 컬럼, 집계 쿼리 등 구체적인 활용 시나리오 제시

Relation - 관련 개념/심화 (8/20점)

• 강점: 인덱스 공간 비용 트레이드오프 언급
• 약점: "5~10배"라는 수치에 대한 근거 없음, 쓰기 성능 저하, 인덱스 유지보수 비용 등 다른 트레이드오프 미언급
• 개선 방향: Clustered Index vs Secondary Index 비교, 인덱스 크기 계산 방법, 쓰기 성능 영향 등 관련 개념 연결

💬 면접 답변 (2-3분 분량)

Covering Index는 쿼리가 필요로 하는 모든 컬럼을 인덱스 자체가 포함하고 있어서, 테이블 데이터 페이지에 접근하지 않고 인덱스만으로 쿼리 결과를 반환할 수 있는 인덱스입니다.

InnoDB에서 Secondary Index를 통해 조회할 때는 일반적으로 두 번의 B+Tree 탐색이 필요합니다. Secondary Index의 리프 노드에는 Primary Key 값만 저장되어 있기 때문에, 먼저 Secondary Index에서 조건에 맞는 PK를 찾고, 그 PK를 가지고 다시 Clustered Index를 탐색해서 실제 데이터를 가져와야 합니다. 이때 Clustered Index 접근은 Random I/O가 발생하는데, 이것이 성능 저하의 주요 원인입니다.

Covering Index를 사용하면 이 두 번째 Clustered Index 탐색을 완전히 생략할 수 있습니다. 쿼리에 필요한 모든 컬럼이 이미 인덱스에 있으므로 인덱스 스캔만으로 결과를 반환합니다.

성능 차이는 상황에 따라 다르지만, 대량의 행을 조회하는 경우 수십 배까지 차이가 날 수 있습니다. 예를 들어 40,000개 행을 조회하는 집계 쿼리에서 일반 인덱스는 40,000번의 테이블 Random I/O가 필요하지만, Covering Index는 인덱스 순차 스캔만으로 완료됩니다. 다만 단일 행 조회에서는 1번의 테이블 접근만 절약하므로 효과가 미미합니다.

트레이드오프로는 인덱스 크기 증가로 인한 메모리 사용량 증가, INSERT/UPDATE 시 인덱스 유지보수 비용 증가가 있습니다. 따라서 읽기 위주 워크로드에서 자주 조회되는 패턴에 선별적으로 적용하는 것이 좋습니다.

📋 상세 분석 (암기/복습용)

• 핵심 포인트 💡:
  • Covering Index = Index-Only Scan을 가능하게 하는 인덱스
  • InnoDB Secondary Index 조회 시 Clustered Index 이중 조회 회피
  • 성능 향상폭은 조회 행 수와 Clustering Factor에 비례

Concept - 개념 정의

Covering Index는 쿼리의 SELECT, WHERE, ORDER BY, GROUP BY 절에서 참조하는 모든 컬럼을 포함하는 인덱스로, 테이블 데이터 페이지에 접근하지 않고 인덱스만으로 쿼리를 완전히 처리할 수 있는 인덱스입니다.

출처: Use The Index, Luke (opens in a new tab)

"The index has a copy of the column so the database can use the value stored in the index. Accessing the table is not required because the index has all of the information to satisfy the query."

Logic - 원리/동작 방식

• InnoDB Secondary Index 구조:

  • Secondary Index의 리프 노드에는 인덱스 컬럼 값 + Primary Key 값만 저장
  • 실제 데이터는 Clustered Index(Primary Key 인덱스)에만 존재

• 일반 Secondary Index 조회 과정:

  1. Secondary Index B+Tree 탐색 → 조건에 맞는 PK 획득
  2. Clustered Index B+Tree 탐색 → 실제 데이터 행 접근 (Random I/O 발생)

• Covering Index 조회 과정:

  1. Secondary Index B+Tree 탐색 → 필요한 모든 데이터 획득 (끝!)
  2. Clustered Index 접근 불필요

• 성능 향상 원리:

  • Random I/O 제거: 디스크 탐색 시간 절약 (SSD에서도 유효)
  • 캐시 효율성: 인덱스는 테이블보다 작아 버퍼 풀에 더 오래 유지
  • 순차 읽기: 인덱스 범위 스캔은 물리적으로 인접한 페이지 읽기

Example - 예시/비유

• 일상 비유:

  • 일반 인덱스: 도서관 색인에서 책 위치를 찾고 → 서가로 이동해서 책을 펼쳐봄 (2단계)
  • Covering Index: 색인 카드에 필요한 정보(저자, 출판년도)가 다 적혀있어서 서가에 갈 필요 없음 (1단계)

• 코드 예시:

  -- 게시판 테이블
  CREATE TABLE posts (
      id BIGINT PRIMARY KEY,
      title VARCHAR(255),
      thumbnail_url VARCHAR(500),
      content TEXT,
      created_at DATETIME,
      author_id BIGINT
  );
   
  -- 일반 인덱스
  CREATE INDEX idx_author ON posts(author_id);
   
  -- Covering Index
  CREATE INDEX idx_author_covering ON posts(author_id, title, thumbnail_url, created_at);
   
  -- 이 쿼리는 Covering Index로 Index-Only Scan 가능
  SELECT title, thumbnail_url, created_at 
  FROM posts 
  WHERE author_id = 123 
  ORDER BY created_at DESC 
  LIMIT 20;
   
  -- EXPLAIN 결과에서 Extra 컬럼에 "Using index" 표시됨

• 성능 차이 예시:

  • 40,000행 집계 쿼리 기준:
    • 일반 인덱스: 40,000번 Random I/O + 테이블 페이지 읽기
    • Covering Index: 인덱스 페이지만 순차 읽기
  • Clustering Factor가 나쁜 경우(데이터가 인덱스 순서와 다르게 흩어져 있는 경우) 차이가 극대화

Application - 활용/적용

• 사용하는 경우:

  • 특정 패턴의 조회 쿼리가 매우 빈번할 때
  • SELECT 절의 컬럼 수가 적을 때 (좁은 쿼리)
  • 집계 쿼리 (COUNT, SUM, AVG 등)
  • 정렬이 필요한 페이지네이션 쿼리
  • 읽기 위주 워크로드

• 사용하지 않는 경우:

  • SELECT * 쿼리 (모든 컬럼 포함 불가능)
  • TEXT, BLOB 등 대용량 컬럼이 필요한 경우
  • 쓰기가 빈번한 테이블 (인덱스 유지보수 비용 증가)
  • 가끔 실행되는 쿼리 (인덱스 공간 낭비)

Relation - 관련 개념/심화

• 관련 개념:

  • Clustered Index: InnoDB에서 테이블 데이터 자체가 PK 순서로 정렬된 B+Tree
  • Secondary Index: Clustered Index 외의 모든 인덱스, 리프에 PK 저장
  • Index Condition Pushdown (ICP): 인덱스 레벨에서 조건 필터링

• 비교:

  구분	일반 Secondary Index	Covering Index
  인덱스 크기	작음	큼 (추가 컬럼 포함)
  조회 성능	이중 조회 필요	Index-Only Scan
  쓰기 성능	상대적으로 빠름	약간 느림
  적합한 상황	범용적	특정 쿼리 패턴 최적화

• 트레이드오프:

  • 장점:
    • 조회 성능 극대화 (수십 배 가능)
    • 버퍼 풀 효율 향상
  • 단점:
    • 인덱스 크기 증가 → 메모리 사용량 증가
    • INSERT/UPDATE/DELETE 시 인덱스 유지보수 비용 증가
    • 컬럼 추가 시 기존 Covering Index 무효화 가능성

• MySQL 옵티마이저가 JOIN 순서를 결정할 때 사용하는 주요 통계 정보는 무엇인가요?
• 옵티마이저가 잘못된 JOIN 순서를 선택할 수 있는 상황은 어떤 것들이 있나요?
• JOIN 순서를 강제하고 싶을 때 사용할 수 있는 방법은?
• Nested Loop Join에서 드라이빙 테이블 선택이 왜 중요한가요?

• 내 답변

기본적으로는 옵티마이저는 히스토그램을 기준으로 판단하도록 되어있는데, 히스토그램은 매번 업데이트 되는것이 아닌 InnoDB 버퍼풀을 참조해서 주기적으로 업데이트 됩니다. 따라서 순간적으로 데이터가 크게 변화가 있거나 하는 상황등은 대응하지 못합니다. JOIN 순서는 히스토그램을 참조해서 임의로 변경하기도 하며, 기본적으로 개발자가 선택한 JOIN 순서를 따르도록 합니다.

• 평가 결과

총점: 38/100점

Concept - 개념 정의 (6/20점)

• 정확성: JOIN 순서가 성능에 미치는 영향에 대한 핵심 개념(Nested Loop Join의 드라이빙 테이블)이 누락됨
• 강점: 옵티마이저가 통계 기반으로 판단한다는 점 인지
• 약점: "개발자가 선택한 JOIN 순서를 따르도록 합니다"는 부정확한 설명. MySQL은 기본적으로 옵티마이저가 최적 순서를 재배치함
• 개선 방향: JOIN 순서가 왜 성능에 영향을 미치는지(Nested Loop Join에서 외부/내부 테이블의 역할 차이)부터 설명

Logic - 원리/동작 방식 (10/20점)

• 정확성: 히스토그램 업데이트에 대한 설명이 부분적으로 정확하나, 히스토그램만이 유일한 통계는 아님
• 강점: 통계 정보의 한계점(실시간 업데이트 안됨)을 인지
• 약점: 히스토그램은 MySQL 8.0에서 도입된 선택적 기능이며, 기본적으로는 인덱스 통계(cardinality)를 사용함. InnoDB 버퍼풀과 히스토그램 관계 설명이 부정확
• 개선 방향: 옵티마이저가 사용하는 통계 정보(인덱스 통계, 히스토그램)와 비용 모델 설명

Example - 예시/비유 (4/20점)

• 강점: 없음
• 약점: 구체적인 예시, 쿼리, EXPLAIN 결과 등이 전혀 없음
• 개선 방향: 작은 테이블과 큰 테이블의 JOIN 순서에 따른 실행 계획 차이 예시 제시

Application - 활용/적용 (8/20점)

• 강점: 통계가 실시간 업데이트되지 않는다는 실무적 한계 인지
• 약점: 언제 수동으로 JOIN 순서를 지정해야 하는지, 어떤 도구/힌트를 사용할 수 있는지 설명 없음
• 개선 방향: STRAIGHT_JOIN, optimizer_switch, FORCE INDEX 등 실무 도구 설명

Relation - 관련 개념/심화 (10/20점)

• 강점: 히스토그램과 옵티마이저 관계 언급
• 약점: Nested Loop Join, Hash Join, Sort Merge Join 등 다른 조인 알고리즘과의 관계, 실행 계획 확인 방법 등 미언급
• 개선 방향: 조인 알고리즘별 특성, optimizer_prune_level, optimizer_search_depth 등 관련 설정 연결

💬 면접 답변 (2-3분 분량)

JOIN 순서는 쿼리 성능에 매우 큰 영향을 미칩니다. MySQL의 기본 조인 알고리즘인 Nested Loop Join에서 JOIN 순서는 곧 어떤 테이블이 드라이빙 테이블(외부 루프)이 되고 어떤 테이블이 드리븐 테이블(내부 루프)이 되는지를 결정하기 때문입니다.

드라이빙 테이블의 각 행마다 드리븐 테이블을 탐색해야 하므로, 드라이빙 테이블 행 수가 조인 연산 횟수를 결정합니다. 따라서 일반적으로 결과 행 수가 적은 테이블을 드라이빙 테이블로 선택하는 것이 유리합니다. 예를 들어 1000행 × 100만행 조인에서 1000행 테이블을 먼저 스캔하면 1000번의 루프지만, 반대로 하면 100만 번의 루프가 됩니다.

MySQL 옵티마이저는 기본적으로 쿼리에 작성된 테이블 순서와 관계없이 최적의 JOIN 순서를 결정합니다. 이를 위해 인덱스 통계(cardinality), MySQL 8.0부터 지원하는 히스토그램, 그리고 비용 모델을 사용합니다.

하지만 옵티마이저가 항상 최적의 순서를 선택하지는 않습니다. 통계 정보가 오래되어 실제 데이터 분포와 다른 경우, 바인드 변수 사용으로 조건절의 선택도를 정확히 예측하지 못하는 경우, 복잡한 조인에서 모든 순서를 탐색하지 않고 휴리스틱으로 결정하는 경우 등에서 차선의 선택을 할 수 있습니다.

이런 경우 STRAIGHT_JOIN 힌트로 작성 순서대로 조인을 강제하거나, JOIN_ORDER 힌트로 특정 순서를 지정할 수 있습니다. ANALYZE TABLE로 통계를 갱신하거나 히스토그램을 생성하는 것도 옵티마이저 판단을 개선하는 방법입니다.

📋 상세 분석 (암기/복습용)

• 핵심 포인트 💡:
  • JOIN 순서 = Nested Loop Join의 드라이빙/드리븐 테이블 결정
  • 드라이빙 테이블 행 수가 적을수록 유리
  • 옵티마이저는 통계 기반으로 재배치하지만 항상 최적은 아님

Concept - 개념 정의

JOIN 순서는 다중 테이블 조인 시 어떤 테이블을 먼저 읽고(드라이빙/외부 테이블), 어떤 테이블을 나중에 읽을지(드리븐/내부 테이블)를 결정하는 것입니다. Nested Loop Join에서 이 순서는 전체 조인 연산의 횟수와 인덱스 활용 여부를 결정하므로 성능에 직접적인 영향을 미칩니다.

Logic - 원리/동작 방식

• Nested Loop Join 동작:

  for each row in 드라이빙_테이블:    -- 외부 루프
      for each matching row in 드리븐_테이블:  -- 내부 루프
          output combined row

• 성능 영향 원리:

  • 드라이빙 테이블 행 수 = 내부 루프 실행 횟수
  • 드리븐 테이블은 인덱스를 통해 접근해야 효율적
  • 최적 순서: 필터 조건 적용 후 결과 행이 적은 테이블을 먼저

• 옵티마이저의 JOIN 순서 결정:

  1. 가능한 모든 순서 조합 탐색 (N! 개)
  2. 각 순서별 비용(Cost) 계산
  3. 최소 비용 순서 선택

• 비용 계산에 사용되는 정보:

  • 인덱스 통계 (SHOW INDEX의 Cardinality)
  • 테이블 통계 (information_schema.TABLES의 행 수)
  • 히스토그램 (MySQL 8.0+, ANALYZE TABLE ... UPDATE HISTOGRAM)
  • 비용 모델 상수 (mysql.server_cost, mysql.engine_cost)

• 옵티마이저 탐색 전략:

  • optimizer_search_depth: 탐색 깊이 제한 (기본값 62)
    • 옵티마이저가 미완성 실행 계획을 얼마나 깊게 탐색할지 결정
    • 값이 낮으면 컴파일 시간 단축, 0으로 설정 시 MySQL이 자동 결정
  • optimizer_prune_level: 휴리스틱 가지치기 활성화 (기본값 1)
  • 테이블이 많으면 모든 순서를 탐색하지 않고 그리디 알고리즘 사용

• 휴리스틱 가지치기 (Heuristic Pruning)란?

  • N개 테이블 조인 시 가능한 순서: N! (팩토리얼) → 10개 테이블 = 3,628,800가지
  • 모든 경우를 탐색하면 컴파일 시간이 기하급수적으로 증가
  • 가지치기 전략:
    1. 부분 실행 계획의 예상 비용 계산
    2. 현재까지 발견된 최선의 계획보다 비용이 높으면 해당 경로 탐색 중단
    3. "더 나빠질 게 뻔한 경로는 끝까지 가보지 않는다"
  • optimizer_prune_level=1 (기본값): 가지치기 활성화로 컴파일 시간 단축
  • optimizer_prune_level=0: 모든 경로 탐색 → 더 좋은 계획 발견 가능하나 시간 소요 증가
  • 실무에서는 기본값 유지 권장 (성능과 최적화 품질의 균형)

Example - 예시/비유

• 일상 비유:

  • 드라이빙 테이블 선택 = 출발점 선택
  • 서울에서 부산 가는 친구 1명 찾기 vs 부산에서 서울 가는 친구 100명 찾기
  • 1명부터 시작하면 1번만 매칭, 100명부터 시작하면 100번 매칭 시도

• 코드 예시:

  -- 주문 테이블 (1억 행)과 사용자 테이블 (10만 행)
  -- 특정 사용자의 주문 조회
   
  -- 쿼리 1: 옵티마이저가 알아서 최적화
  SELECT o.order_id, o.amount, u.name
  FROM orders o
  JOIN users u ON o.user_id = u.id
  WHERE u.status = 'VIP';  -- VIP 사용자: 1000명
   
  -- 옵티마이저 예상: users 먼저 (1000행) → orders 조회
   
  -- EXPLAIN 결과:
  -- | id | table | type  | rows   | Extra |
  -- |----|-------|-------|--------|-------|
  -- | 1  | u     | ref   | 1000   | Using where |
  -- | 1  | o     | ref   | 100    | Using index condition |
   
  -- 쿼리 2: STRAIGHT_JOIN으로 작성 순서 강제
  SELECT /*+ STRAIGHT_JOIN */ o.order_id, o.amount, u.name
  FROM orders o
  JOIN users u ON o.user_id = u.id
  WHERE u.status = 'VIP';
   
  -- 비효율: orders 먼저 (1억행) → users 조회

Application - 활용/적용

• 사용하는 경우 (수동 순서 지정):

  • 옵티마이저가 명백히 잘못된 순서 선택 시
  • 통계 정보가 실제와 크게 다를 때
  • 임시 테이블이나 서브쿼리 결과가 포함된 복잡한 쿼리

• JOIN 순서 제어 방법:

  • STRAIGHT_JOIN: 작성 순서대로 강제
  • /*+ JOIN_ORDER(t1, t2, t3) */: 특정 순서 지정 (MySQL 8.0+)
  • /*+ JOIN_PREFIX(t1) */: 첫 번째 테이블 지정
  • /*+ JOIN_SUFFIX(t3) */: 마지막 테이블 지정

• 통계 갱신 방법:

  • ANALYZE TABLE table_name;: 인덱스 통계 갱신
  • ANALYZE TABLE table_name UPDATE HISTOGRAM ON column;: 히스토그램 생성

Relation - 관련 개념/심화

• 관련 개념:

  • Nested Loop Join: MySQL의 기본 조인 알고리즘
  • Hash Join: MySQL 8.0.18+, 동등 조인에서 인덱스 없이 효율적
  • Block Nested Loop (BNL): 조인 버퍼 사용, 8.0.20 이후 Hash Join으로 대체
  • Multi-Range Read (MRR): 드리븐 테이블 접근 최적화

• 비교:

  구분	Nested Loop Join	Hash Join
  인덱스 필요	드리븐 테이블에 필수	불필요
  메모리 사용	적음	join_buffer_size만큼
  JOIN 순서 영향	크게 영향	상대적으로 적음
  적합한 상황	인덱스 있고 선택도 높을 때	인덱스 없거나 대량 조인

• 트레이드오프:

  • 장점 (수동 순서 지정): 옵티마이저 오판 방지, 예측 가능한 성능
  • 단점: 데이터 분포 변경 시 수동 수정 필요, 옵티마이저 발전 반영 안됨

• 옵티마이저가 잘못 선택하는 상황:

  • 통계 정보 오래됨 (innodb_stats_persistent = ON 시 자동 갱신 안됨)
  • 바인드 변수로 인한 선택도 오판
  • 상관 서브쿼리의 복잡한 비용 추정
  • 테이블 수가 많아 모든 순서 탐색 불가 (휴리스틱 사용)

• 바인드 변수(Bind Variable)로 인한 선택도 오판이란?

  • Prepared Statement 사용 시 ? 자리에 실제 값이 아닌 파라미터 마커 사용
  • 옵티마이저는 실행 계획을 한 번만 생성하고 재사용 (Plan Caching)
  • 문제 상황:

    -- status = 'VIP'는 1,000건, status = 'NORMAL'은 9,999,000건
    PREPARE stmt FROM 'SELECT * FROM users WHERE status = ?';
    SET @s = 'VIP';
    EXECUTE stmt USING @s;  -- 최초 실행: VIP 기준으로 실행 계획 수립 (Index Scan)
    SET @s = 'NORMAL';
    EXECUTE stmt USING @s;  -- 재실행: 같은 계획 재사용 (Index Scan) → 비효율!
  • 원인: 첫 실행 시점의 파라미터 값으로 선택도(Selectivity) 추정
  • 해결 방법:
    • MySQL 8.0+: 히스토그램 활용으로 일부 완화
    • 극단적 경우: 동적 SQL 사용 (매번 새로운 실행 계획)
    • Oracle의 경우: Adaptive Cursor Sharing으로 자동 대응

• 상관 서브쿼리(Correlated Subquery)란?

  • 외부 쿼리의 컬럼을 내부 서브쿼리에서 참조하는 서브쿼리
  • 외부 쿼리의 각 행마다 서브쿼리가 반복 실행됨

    -- 상관 서브쿼리 예시: 각 사용자의 최신 주문 조회
    SELECT * FROM users u
    WHERE EXISTS (
      SELECT 1 FROM orders o 
      WHERE o.user_id = u.id  -- 외부 쿼리(u.id)를 참조
        AND o.created_at = (
          SELECT MAX(created_at) FROM orders WHERE user_id = u.id
        )
    );
  • 비용 추정이 어려운 이유:
    1. 외부 행 수 × 서브쿼리 비용 = 전체 비용이지만
    2. 서브쿼리 비용이 외부 값에 따라 달라질 수 있음 (데이터 분포)
    3. 옵티마이저가 상관 관계를 정확히 모델링하기 어려움
  • MySQL 최적화 전략:
    • EXISTS 전략: 조건 만족 시 즉시 TRUE 반환
    • 세미조인 변환: 상관 서브쿼리를 조인으로 변환 (optimizer_switch의 semijoin 옵션)
    • 서브쿼리 Materialization: 결과를 임시 테이블에 저장 후 재사용

• 페이지 분할(Page Split)이란?

  • B-Tree 인덱스에서 새 데이터 삽입 시 페이지가 가득 찼을 때 발생하는 현상
  • InnoDB의 기본 페이지 크기는 16KB
  • 분할 과정:
    1. 삽입할 위치의 페이지가 꽉 참
    2. 새 페이지를 할당하고 기존 페이지의 절반을 이동
    3. 두 페이지를 B-Tree 구조에 연결
  • 랜덤 삽입 vs 순차 삽입:

    순차 삽입 (AUTO_INCREMENT):
    [1,2,3,4,5,6,7,8] → [1,2,3,4,5,6,7,8] [9,10...] (끝에만 추가)
    
    랜덤 삽입 (UUID):
    [1,3,5,7,9,11,13,15] → 8 삽입 시 분할 발생
    [1,3,5,7] [8,9,11,13,15] → 중간 분할로 공간 낭비
  • 성능 영향:
    • 페이지 분할 시 I/O 증가 (새 페이지 할당, 데이터 이동)
    • 랜덤 삽입은 페이지 단편화 유발 (50% 채워진 페이지들)
    • Buffer Pool 효율성 저하
  • 완화 방법:
    • innodb_fill_factor 설정 (기본값 ~93%): 페이지를 미리 여유있게 채움

      SET GLOBAL innodb_fill_factor = 80;  -- 20% 여유 공간 확보
    • 순차적 PK 사용 (AUTO_INCREMENT 권장)
    • OPTIMIZE TABLE로 주기적 재구성
  • 모니터링:

    -- 페이지 분할/병합 횟수 확인
    SELECT name, count FROM information_schema.innodb_metrics 
    WHERE name LIKE 'index_page_splits' OR name LIKE 'index_page_merge%';
  • 페이지 병합(Page Merge): 반대로 삭제로 페이지 사용률이 MERGE_THRESHOLD(기본 50%) 미만이면 인접 페이지와 병합
  • 참고: MySQL 8.0 Reference Manual - Sorted Index Builds (opens in a new tab), Percona - InnoDB Page Merging and Page Splitting (opens in a new tab)

• Change Buffer란?

  • 세컨더리 인덱스 변경(INSERT, UPDATE, DELETE)을 버퍼링하여 Random I/O를 줄이는 InnoDB 최적화 기법
  • 원래 Insert Buffer였으나, DELETE와 UPDATE(Purge 포함)도 지원하면서 Change Buffer로 명칭 변경
  • 동작 원리:

    [일반적인 세컨더리 인덱스 갱신]
    INSERT → 세컨더리 인덱스 페이지를 디스크에서 읽음 → 수정 → 다시 씀 (Random I/O)
    
    [Change Buffer 사용 시]
    INSERT → 인덱스 페이지가 Buffer Pool에 없으면?
           → 변경 내용을 Change Buffer에 기록 (Sequential I/O)
           → 나중에 해당 페이지 읽을 때 변경사항 병합(Merge)
  • 읽기 시 병합(Merge) 동작:
    • 세컨더리 인덱스 페이지가 Buffer Pool로 읽혀질 때 Change Buffer의 대기 중인 변경사항을 적용
    • ROLLBACK은 Change Buffer를 사용하지 않음 (트랜잭션 중 버퍼링된 변경을 강제 병합)
    • 서버 종료 시 innodb_fast_shutdown=0으로 설정하면 모든 버퍼링된 변경사항을 병합
  • 제한사항:
    • UNIQUE 인덱스에는 적용 불가: 중복 체크를 위해 인덱스 페이지를 반드시 읽어야 함
    • 세컨더리 인덱스의 리프 페이지에만 적용
    • unique_checks=0으로 임시 비활성화하면 UNIQUE 인덱스도 버퍼링 가능 (벌크 로드 시 활용)
  • 설정:

    -- 버퍼링 유형 설정 (none, inserts, deletes, changes, purges, all)
    SET GLOBAL innodb_change_buffering = 'all';  -- 기본값
     
    -- 최대 크기 (Buffer Pool의 백분율, 기본값 25%)
    SET GLOBAL innodb_change_buffer_max_size = 25;
  • 모니터링:

    -- Change Buffer 메트릭 조회
    SELECT NAME, COMMENT FROM INFORMATION_SCHEMA.INNODB_METRICS 
    WHERE NAME LIKE '%ibuf%'\G
     
    -- SHOW ENGINE INNODB STATUS의 INSERT BUFFER AND ADAPTIVE HASH INDEX 섹션
    SHOW ENGINE INNODB STATUS\G
  • ⚠️ Deprecated/Removed 상태:
    • MariaDB: 10.5.15/10.6.7부터 기본값 none으로 변경, 11.0에서 완전 제거
    • 제거 이유: 재현 어려운 손상 버그의 주요 원인, 벤치마크에서 성능 향상 미미 (수 % 수준)
    • SSD/NVMe 환경에서는 Random I/O 비용이 크게 감소하여 Change Buffer의 이점 감소
    • MySQL 8.4/9.0에서는 아직 유지되나, 향후 deprecated 가능성 존재
  • 참고: MySQL 8.0 Reference Manual - Change Buffer (opens in a new tab), MariaDB - InnoDB Change Buffering (opens in a new tab)

2번 상황이 더 적절하다. lock을 얻기 전에 transaction을 시작해버리면 connection pool이 낭비될 수 있기 때문이다.

• https://dev.mysql.com/doc/refman/8.4/en/bit-functions.html (opens in a new tab)

행기반 데이터베이스는 데이터를 메모리에서 행 단위로 연속적으로 저장해, 빠른 읽기/쓰기가 필요한 트랜잭션 처리(OLTP)에 유리합니다. 반면 열기반 데이터베이스는 데이터를 열 단위로 저장해, 분석 쿼리(OLAP)에서 필요한 열만 빠르게 읽을 수 있고, 압축 효율성이 좋습니다. 열기반은 대용량 분석에 적합하지만, 행기반은 트랜잭션 처리에 더 효과적입니다.

1. 행(Row)기반 데이터베이스

• 특징

  • 저장 구조: 데이터의 각 행(row)을 단위로 연속된 공간에 저장합니다. 즉, 한 행에 포함된 모든 컬럼 값들이 함께 저장됩니다.
  • 사용 패턴: 한 번에 전체 행의 데이터를 읽거나 쓰는 경우가 많으며, OLTP(Online Transaction Processing)와 같이 빈번한 쓰기 작업, 레코드 단위의 조작이 중요한 환경에서 주로 사용됩니다.

• 장점

  • 빠른 트랜잭션 처리: 한 레코드에 대한 읽기 및 쓰기 작업이 한 번에 이루어지므로, INSERT, UPDATE, DELETE와 같은 트랜잭션 처리에서 빠른 성능을 보입니다.
  • 단순한 데이터 접근: 레코드 단위로 데이터를 다루므로, 응용 프로그램에서 데이터를 가져오거나 수정할 때 직관적입니다.
  • 복잡한 트랜잭션 지원: ACID 특성을 잘 지원하여 복잡한 트랜잭션 환경에서 안정적인 성능을 보장합니다.

• 단점

  • 분석 쿼리 비효율: 특정 컬럼에 대한 집계나 분석을 수행할 때, 필요한 컬럼만 읽어오는 것이 어렵고 전체 행을 읽어야 하므로 I/O 비용이 증가합니다.
  • 캐시 활용의 한계: 필요한 데이터만 추출하기 어려워, 메모리 캐시에 불필요한 데이터까지 적재되는 경우가 발생할 수 있습니다.

2. 열(Column)기반 데이터베이스

• 특징

  • 저장 구조: 데이터의 각 컬럼(column)을 단위로 연속된 공간에 저장합니다. 즉, 동일 컬럼의 값들이 연속해서 저장됩니다.
  • 사용 패턴: 대규모 데이터 분석, 집계, 리포팅 등 OLAP(Online Analytical Processing) 환경에서 주로 사용되며, 특정 컬럼만 선택하여 빠르게 읽어들일 수 있습니다.

• 장점

  • 빠른 분석 쿼리 성능: 필요한 컬럼만 읽어오기 때문에 I/O 오버헤드가 줄어들고, 대량의 데이터를 빠르게 처리할 수 있습니다.
  • 효율적인 압축: 동일 데이터 타입의 값들이 연속되어 저장되므로, 압축률이 높아 저장 공간을 절약할 수 있습니다.
  • 벡터화 처리 지원: 대량의 데이터를 한 번에 처리하는 벡터화 연산에 유리하여, 병렬 처리 및 최적화된 분석 쿼리를 구현할 수 있습니다.

• 단점

  • 트랜잭션 처리에 부적합: 레코드 단위의 빠른 쓰기 작업이 필요한 OLTP 환경에서는 오히려 성능이 저하될 수 있습니다. 개별 행을 재구성해야 하는 작업이 비효율적입니다.
  • 데이터 업데이트 비용: 행 전체를 업데이트하는 대신 여러 컬럼에 분산되어 저장되어 있기 때문에, 한 행에 대한 업데이트 시 여러 영역을 수정해야 하여 비용이 발생할 수 있습니다.
  • 구조 설계 복잡성: 데이터가 컬럼 단위로 분리되어 있으므로, 행 간 연관성을 고려한 쿼리를 작성하거나 조인 등의 작업 시 복잡도가 증가할 수 있습니다.

• 사딩은 크게 수직 분할과 수평 분할로 나눔

1. 수직 분할 한 스키마에 저장되어 있는 데이터를 특정 칼럼 단위로 잘라내어 분할 저장합니다. 수직 파티셔닝은 각 스키마를 나누고 데이터가 따라 옮겨갑니다.

결국 논리적 엔티티들을 다른 물리 엔티티들로 나누는 것을 의미합니다.

2. 수평 분할 한 스키마에 저장되어 있는 데이터를 특정 알고리즘을 통해 행 단위로 잘라내어 분할 저장합니다. 수평 분할은 하나로 구성된 스키마를 동일한 구성의 여러 개의 스키마로 분리한 후, 각 스키마에 어떤 데이터가 저장될지를 샤드키를 기준으로 분리합니다.

• 샤딩 알고리즘

수평 분할을 통한 샤딩은 샤딩 알고리즘이 필요합니다. 기준이 되는 샤드키를 통해 어느 스키마에 접근하여 데이터를 핸들링할 것인지 정해야 하기 때문입니다. 한마디로 라우팅을 위한 알고리즘입니다.

1. Modular Sharding

장점: Range 샤딩에 비해 데이터가 균일하게 분산됩니다. 단점: DB를 추가 증설하게 된다면 이미 적재된 데이터들의 재정렬이 필요합니다. (ex) (DB는 2개, 상품번호가 0~10까지 있을 경우) 상품번호 % 2 모듈러 연산 시 DB1에는 0,2,4,6,8,10 상품, DB2에는 1,3,5,7,9 상품이 들어가게 됩니다. Modular 샤딩은 데이터량이 일정 수준에서 유지될 것으로 예상되는 데이터 성격을 가진 곳에 적용할 때 어울리는 방식입니다.

실제적으로 상품 데이터를 적재하고 있으나, 해당 데이터는 장기적으로 미사용 할 경우 다른 디비에 옮겨 보관하기 때문에 적합합니다. 데이터가 꾸준히 늘어나더라도 적재속도가 그리 빠르지 않다면 문제없다고 합니다.

일단 데이터가 균일하게 분산된다는 점 자체가 트래픽을 안전하게 소화하면서도 DB 리소스를 최대한 활용할 수 있기 때문입니다.

2. Range Sharding

Range 샤딩은 샤드키의 범위를 기준으로 DB를 선택하는 방식입니다.

장점: Modular 샤딩에 비해 증설에 재정렬 비용이 들지 않습니다. 단점: 일부 DB에 데이터가 몰릴 수 있습니다. Range 샤딩은 증설작업에 드는 비용이 크지 않습니다. Modular의 경우 증설작업이 진행될 경우 기존 데이터들도 모두 재정렬을 해야 합니다. 이런 부분에서 편합니다.

하지만 많이 접근하는 데이터가 있는 DB 쪽으로 트래픽이나 데이터량이 몰릴 수 있습니다. 결국 샤딩을 했더라도 동일한 현상이 나타난다면 또 부하 분산을 통해 DB를 쪼개 재정렬하는 작업이 필요하고, 반대로 트래픽이 저조한 DB는 통합 작업을 통해 유지비용을 아끼도록 관리해야 합니다.

• Reference : Gmarket Tech 샤딩이란 무엇인가 (opens in a new tab)

• Named Lock과 다른 동시성 제어 방법들의 차이점은 무엇인가요?
• Named Lock을 사용할 때 주의해야 할 부하 관련 이슈는 무엇인가요?

✅ Named Lock은 이름 기반의 잠금 메커니즘으로, 특정 문자열을 키로 사용하여 동시성을 제어하는 방법입니다. MySQL의 GET_LOCK(), RELEASE_LOCK() 함수를 통해 사용할 수 있습니다.

• 사용법

  • 획득: SELECT GET_LOCK('lock_name', timeout)
  • 해제: SELECT RELEASE_LOCK('lock_name')
  • 확인: SELECT IS_FREE_LOCK('lock_name')
  • 예시 코드:

  -- Lock 획득 (10초 타임아웃)
  SELECT GET_LOCK('order_process_12345', 10);
   
  -- 비즈니스 로직 수행
  UPDATE orders SET status = 'processing' WHERE id = 12345;
   
  -- Lock 해제
  SELECT RELEASE_LOCK('order_process_12345');

• 장점

  • 🎯 유연한 잠금 범위: 테이블/행 단위가 아닌 비즈니스 로직 단위로 잠금 가능
  • ⚡ 빠른 성능: 메모리 기반으로 동작하여 디스크 I/O 없이 빠른 처리
  • 🔧 간단한 구현: 별도의 인프라 없이 MySQL 함수만으로 구현 가능
  • 🌐 분산 환경 지원: 여러 애플리케이션 서버에서 동일한 MySQL을 통해 동시성 제어

• 단점

  • ⚠️ 수동 관리 필요: 개발자가 직접 Lock 획득/해제를 관리해야 함
  • 🔗 Connection 종속성: Connection이 끊어지면 자동으로 Lock이 해제됨
  • 📊 제한된 모니터링: Lock 상태를 추적하고 디버깅하기 어려움
  • 💾 메모리 사용: 많은 Lock 사용 시 MySQL 메모리 사용량 증가

• 부하 관련 이슈

  • Connection Pool 고갈: Lock을 오래 보유하면 Connection이 부족해질 수 있음
  • Lock 대기 증가: 타임아웃 설정이 길면 대기 스레드가 늘어나 성능 저하
  • 메모리 압박: 동시에 많은 Named Lock 사용 시 MySQL 메모리 부담
  • 모니터링 어려움: 어떤 프로세스가 Lock을 보유 중인지 추적 곤란

• 대체 동시성 제어 장치

  • Pessimistic Lock (비관적 잠금)
    • DB 레벨에서 SELECT ... FOR UPDATE 사용
    • 트랜잭션 내에서만 유효
  • Optimistic Lock (낙관적 잠금)
    • Version 컬럼을 이용한 충돌 감지
    • 동시 수정이 적은 경우 효율적
  • Redis 분산 락
    • Redlock, Redisson 등 사용
    • 별도 인프라 필요하지만 더 강력한 기능
  • ZooKeeper
    • 강력한 분산 조정 서비스
    • 복잡하지만 안정적인 동시성 제어

• 꼬리질문: Named Lock과 다른 동시성 제어 방법들의 차이점은 무엇인가요?

  • Pessimistic Lock과의 차이
    • Named Lock: 애플리케이션 레벨, 이름 기반, Connection 종속
    • Pessimistic Lock: DB 레벨, 행/테이블 기반, 트랜잭션 종속
  • Optimistic Lock과의 차이
    • Named Lock: 사전 차단 방식, Lock 대기 발생
    • Optimistic Lock: 충돌 감지 방식, 재시도 로직 필요
  • Redis Lock과의 차이
    • Named Lock: MySQL 내장, 추가 인프라 불필요
    • Redis Lock: 별도 Redis 필요, TTL 지원, 더 많은 기능

• 꼬리질문: Named Lock을 사용할 때 주의해야 할 부하 관련 이슈는 무엇인가요?

  • Connection Pool 관리
    • Lock 보유 시간 최소화 필요
    • Pool 크기 적절히 설정 (Lock 대기 Connection 고려)
  • 타임아웃 설정
    • 너무 길면: 대기 스레드 증가로 시스템 부하
    • 너무 짧으면: Lock 획득 실패로 재시도 증가
    • 권장: 비즈니스 로직 수행 시간의 2-3배
  • Lock 이름 관리
    • 너무 세분화: 메모리 사용량 증가
    • 너무 광범위: Lock 경합 증가
    • 권장: 적절한 단위로 그룹핑 (예: order_lock_{user_id % 100})
  • 모니터링 구현
    • Lock 획득/해제 로깅
    • 장시간 Lock 보유 감지
    • Connection Pool 사용률 모니터링

• Union은 왜 중복을 제거하나요?
• Union All이 Union보다 빠른 이유는 무엇인가요?

✅ UNION은 두 개 이상의 SELECT 문의 결과를 결합하면서 중복된 행을 제거하는 반면, UNION ALL은 중복을 제거하지 않고 모든 결과를 그대로 반환합니다.

• UNION

  • 📜 기능: 여러 SELECT 문의 결과를 합칩니다.
  • 🗑️ 중복 제거: 결과 집합에서 중복된 행을 자동으로 제거합니다.
  • 🐢 성능: 중복을 찾기 위해 내부적으로 정렬(Sort) 또는 해시(Hash) 작업을 수행하므로 UNION ALL보다 느립니다.
  • 🤔 언제 사용?: 순수한 합집합(Set Union)이 필요할 때, 즉 중복이 없는 유니크한 결과만 원할 때 사용합니다.

• UNION ALL

  • 📜 기능: 여러 SELECT 문의 결과를 합칩니다.
  • ✨ 중복 허용: 중복된 행을 포함하여 모든 결과를 그대로 반환합니다.
  • 🚀 성능: 중복 제거 과정이 없으므로 UNION보다 훨씬 빠릅니다.
  • 🤔 언제 사용?: 중복된 결과가 상관없거나, 중복이 발생하지 않을 것이 확실할 때 성능상의 이점을 위해 사용합니다.

• 예시 코드

  -- table_a: (1, 'A'), (2, 'B'), (3, 'C')
  -- table_b: (3, 'C'), (4, 'D'), (5, 'E')
   
  -- UNION (중복 '3, C'가 제거됨)
  SELECT * FROM table_a
  UNION
  SELECT * FROM table_b;
  -- 결과: (1, 'A'), (2, 'B'), (3, 'C'), (4, 'D'), (5, 'E')
   
  -- UNION ALL (중복 '3, C'가 포함됨)
  SELECT * FROM table_a
  UNION ALL
  SELECT * FROM table_b;
  -- 결과: (1, 'A'), (2, 'B'), (3, 'C'), (3, 'C'), (4, 'D'), (5, 'E')

• 꼬리질문: Union은 왜 중복을 제거하나요?

  • UNION은 관계대수(Relational Algebra)의 합집합(Set Union) 연산에 기반을 두고 있기 때문입니다. 집합 이론에서 합집합은 중복된 원소를 포함하지 않는 것이 기본 원칙입니다. 따라서 SQL의 UNION도 이 원칙을 따라 중복을 제거합니다.

• 꼬리질문: Union All이 Union보다 빠른 이유는 무엇인가요?

  • UNION ALL은 단순히 두 결과 집합을 위아래로 붙이기만 하는 반면, UNION은 중복을 제거하기 위해 추가적인 작업을 수행해야 합니다.
  • 이 추가 작업은 보통 결과 집합을 정렬(Sorting)하거나 해시 테이블(Hash Table)을 만들어 중복 여부를 검사하는 과정이며, 이로 인해 상당한 오버헤드가 발생합니다. 따라서 UNION ALL이 훨씬 빠릅니다.

• Hash Join 선택 조건은 무엇인가요?
• Merge Join 선택 조건은 무엇인가요?
• Nested Loop Join 선택 조건은 무엇인가요?
• 각 조인 알고리즘의 동작 방식은 어떻게 되나요?
• 시간 복잡도는 어떻게 다른가요?

1. 🔎 각 Join 알고리즘의 상세 동작 방식

◆ Hash Join 🔨

Hash Join은 두 단계로 구성되어 동작합니다:

● Build Phase (구축 단계)

• 작은 테이블(Build Input)을 선택하여 메모리에 해시 테이블을 구축
• 조인 키를 해시 함수에 적용하여 버킷(bucket)에 분배
• 해시 테이블에는 해시값과 실제 행에 대한 포인터가 저장됨
• 메모리가 부족하면 일부 파티션을 디스크(tempdb)로 스필(spill)

● Probe Phase (탐색 단계)

• 큰 테이블(Probe Input)을 스캔하면서 각 행에 대해 동일한 해시 함수 적용
• 해시 테이블에서 매칭되는 버킷을 찾아 조인 수행
• 해시 충돌이 발생할 수 있으므로 실제 값 비교 필요
• 디스크로 스필된 파티션은 재귀적으로 처리

● Grace Hash Join 변형

• 메모리가 부족한 경우 사용되는 변형 알고리즘
• 양쪽 테이블을 모두 파티셔닝하여 디스크에 저장
• 각 파티션 쌍을 메모리에 로드하여 조인 수행

◆ Merge Join 🔄

Merge Join은 정렬된 입력을 효율적으로 병합합니다:

● 정렬 단계

• 양쪽 입력이 조인 키로 정렬되어 있지 않으면 먼저 정렬 수행
• 인덱스가 있으면 명시적 정렬 불필요 (Interesting Order)
• External Sort 알고리즘 사용 가능

● 병합 단계

• 두 정렬된 입력을 동시에 스캔
• 포인터를 사용하여 각 테이블의 현재 위치 추적
• 조인 키 값을 비교하여: • 같으면: 조인 결과 생성 • 왼쪽이 작으면: 왼쪽 포인터 전진 • 오른쪽이 작으면: 오른쪽 포인터 전진
• 양쪽 입력을 한 번씩만 스캔 (Single Pass)

◆ Nested Loop Join 🔁

Nested Loop Join은 가장 단순한 조인 알고리즘입니다:

● 기본 동작

• 외부 테이블(Outer Table)의 각 행에 대해
• 내부 테이블(Inner Table)을 전체 스캔하여 매칭 확인
• 이중 반복문(Nested Loop) 구조

● Index Nested Loop Join

• 내부 테이블에 인덱스가 있는 경우
• 전체 스캔 대신 인덱스 시크(Seek) 수행
• Correlated Parameter를 사용한 효율적 검색
• "Index Join"이라고도 불림

● Block Nested Loop Join (BNL)

• 메모리 버퍼를 활용한 최적화
• 외부 테이블의 여러 행을 버퍼에 저장
• 내부 테이블 스캔 시 버퍼의 모든 행과 비교
• MySQL 8.0.20부터는 Hash Join으로 대체

2. ✅ Hash Join 선택 조건 🔨

▪ Equality 조인만 필요한 경우 • Equi-join (=) 연산에 최적화 • 불평등 조인(>, <, !=)에는 부적합

▪ 정렬되지 않은 대용량 데이터 • 양쪽 테이블이 모두 큰 경우 효과적 • 정렬 비용을 피할 수 있음

▪ 충분한 메모리 확보 가능 • work_mem * hash_mem_multiplier 고려 • 메모리 부족 시 디스크 스필 발생으로 성능 저하

▪ 인덱스가 없거나 전체 스캔 필요시 • 인덱스 사용이 불가능한 상황 • Full Table Scan이 불가피한 경우

▪ 추가 선택 조건: • 조인 결과가 입력 크기에 비례할 때 • OLAP 쿼리나 배치 처리에 적합 • 병렬 처리 가능한 환경

3. ✅ Merge Join 선택 조건 🔄

▪ 범위 조건이 포함된 조인 • 부등호 조인 (>=, <=) 처리 가능 • 정렬된 데이터에서 범위 검색 효율적

▪ 양쪽 입력이 이미 정렬된 경우 • 인덱스로 인한 사전 정렬 (Interesting Order) • 추가 정렬 비용 없음

▪ Non-blocking 연산 필요시 • 첫 결과를 빠르게 반환 가능 • Pipelined 처리 가능

▪ ORDER BY 절이 있는 경우 • 조인 결과가 이미 정렬되어 있음 • 추가 정렬 단계 불필요

▪ 추가 선택 조건: • 양쪽 입력 크기가 비슷한 경우 • 메모리 사용량이 제한적인 환경 • 안정적이고 예측 가능한 성능 필요

4. ✅ Nested Loop Join 선택 조건 🔁

▪ 한쪽 테이블이 매우 작은 경우 • 외부 테이블의 행 수가 적을 때 • OLTP 쿼리에서 흔한 패턴

▪ 선택도 높은 인덱스 존재 • 내부 테이블에 효율적인 인덱스 • Index Seek 비용이 낮은 경우

▪ 첫 결과를 빠르게 반환 필요 Sort Merge Join이 더 느린 이유는 양쪽 스트림 동시 스캔이 필요하기 때문입니다. • 양쪽 테이블을 동시에 읽기 시작해야 함 • 첫 매칭 지점까지 양쪽을 진행시켜야 함

Customers 테이블 (id로 정렬됨):
id: [1, 3, 5, 10, 15]
 
Orders 테이블 (customer_id로 정렬됨):
order_id | customer_id
---------|------------
   101   |     1
   102   |     1
   103   |     3
   104   |     5
   105   |     5
   106   |     5
   107   |    10
   108   |    15
 
쿼리:
SELECT * FROM Customers c
JOIN Orders o ON c.id = o.customer_id

초기 상태:
Customers 포인터 → id=1
Orders 포인터    → customer_id=1 (order_id=101)

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

단계 1: 비교 (1 == 1)
✓ 매칭! → 결과: (Customer id=1, Order 101)
Orders 포인터 이동 → customer_id=1 (order_id=102)

단계 2: 비교 (1 == 1)
✓ 매칭! → 결과: (Customer id=1, Order 102)
Orders 포인터 이동 → customer_id=3 (order_id=103)

단계 3: 비교 (1 < 3)
→ Customers 포인터 이동 → id=3

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

단계 4: 비교 (3 == 3)
✓ 매칭! → 결과: (Customer id=3, Order 103)
Orders 포인터 이동 → customer_id=5

단계 5: 비교 (3 < 5)
→ Customers 포인터 이동 → id=5

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

단계 6: 비교 (5 == 5)
✓ 매칭! → 결과: (Customer id=5, Order 104)
Orders 포인터 이동 → customer_id=5 (order_id=105)

단계 7: 비교 (5 == 5)
✓ 매칭! → 결과: (Customer id=5, Order 105)
Orders 포인터 이동 → customer_id=5 (order_id=106)

단계 8: 비교 (5 == 5)
✓ 매칭! → 결과: (Customer id=5, Order 106)
Orders 포인터 이동 → customer_id=10

단계 9: 비교 (5 < 10)
→ Customers 포인터 이동 → id=10

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

... 계속 진행 ...

결과: 총 8개의 조인 결과 (모든 매칭 찾음) ✓

잘못된 시도:
Customers 포인터 → id=5 (Index Seek으로 직접 점프!)
Orders 포인터    → customer_id=1 (처음부터 시작)

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

단계 1: 비교 (5 > 1)
→ Orders 포인터 이동 → customer_id=1 (order_id=102)

단계 2: 비교 (5 > 1)
→ Orders 포인터 이동 → customer_id=3 (order_id=103)

단계 3: 비교 (5 > 3)
→ Orders 포인터 이동 → customer_id=5 (order_id=104)

단계 4: 비교 (5 == 5)
✓ 매칭! → 결과: (Customer id=5, Order 104)

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

문제 발생! ⚠️

놓친 결과들:
❌ (Customer id=1, Order 101) - 놓침!
❌ (Customer id=1, Order 102) - 놓침!
❌ (Customer id=3, Order 103) - 놓침!

이유: Customers에서 id=5로 점프했기 때문에
      id=1, 3의 매칭을 찾을 수 없음!

또 다른 잘못된 시도:
Customers 포인터 → id=1 (처음부터)
Orders 포인터    → customer_id=5 (Index Seek으로 점프!)

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

단계 1: 비교 (1 < 5)
→ Customers 포인터 이동 → id=3

단계 2: 비교 (3 < 5)
→ Customers 포인터 이동 → id=5

단계 3: 비교 (5 == 5)
✓ 매칭! → 결과: (Customer id=5, Order 104, 105, 106)

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

문제 발생! ⚠️

놓친 결과들:
❌ (Customer id=1, Order 101, 102) - 놓침!
❌ (Customer id=3, Order 103) - 놓침!

이유: Orders에서 customer_id=5로 점프했기 때문에
      customer_id=1, 3의 Order들을 찾을 수 없음!

정상적인 동기화:
Customers: [1] → [3] → [5] → [10] → [15]
            ↓     ↓     ↓      ↓      ↓
Orders:    [1,1] [3] [5,5,5] [10]  [15]

양쪽이 동기화되어 진행하므로 모든 매칭 발견 ✓

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

Index Seek 사용 시 동기화 깨짐:
Customers:  1   3  [5] ← 여기로 점프!
                    ↓
Orders:    [1,1,3,5,5,5,10,15] ← 처음부터 읽어야 함

동기화가 깨져서 이전 매칭(1, 3)을 놓침 ❌

• 전체 결과를 기다리지 않음 • 온라인 쿼리에 적합

▪ LIMIT 절과 함께 사용시 • 일부 결과만 필요한 경우 • Early termination 가능

▪ 추가 선택 조건: • 조인 조건이 복잡한 경우 • 불평등 조인이 유일한 옵션일 때 • Correlated Subquery 처리

5. 📊 시간 복잡도 비교

6. 🎯 실무 선택 가이드

◆ Query Optimizer의 자동 선택 • 통계 정보 기반 비용 추정 • Cardinality 예측 • Available Index 확인 • Memory Grant 계산

◆ 수동 힌트 사용 시 주의사항

-- SQL Server 예시
SELECT * FROM Table1
INNER LOOP JOIN Table2 ON ...  -- Nested Loop 강제
INNER HASH JOIN Table3 ON ...  -- Hash Join 강제
INNER MERGE JOIN Table4 ON ... -- Merge Join 강제
OPTION (FORCE ORDER)

⚠️ 주의: 데이터 분포가 변하면 성능 저하 가능

◆ 모니터링 포인트 • Hash Join: Memory Grant, Spill to TempDB • Merge Join: Sort 비용, Residual Predicate • Nested Loop: Inner Table Scan 횟수, Index Seek 비용

7. 🔄 Adaptive Join (SQL Server 2017+) • Runtime에 Hash Join과 Nested Loop 중 선택 • Batch Mode 처리 필요 • Columnstore Index와 함께 사용 • 실제 행 수에 따라 동적 전환

8. 💡 성능 최적화 팁

▪ 통계 정보 최신 유지 • UPDATE STATISTICS 정기 실행 • Auto Update Statistics 활성화

▪ 적절한 인덱스 설계 • 조인 키에 인덱스 생성 • Covering Index 고려

▪ 메모리 설정 최적화 • work_mem (PostgreSQL) • max_server_memory (SQL Server)

▪ 파티셔닝 고려 • 대용량 테이블 파티션 • Partition-wise Join 활용