포트폴리오 수정 정리 - 커버링 인덱스를 통한 조회 성능 개선

• 어떤 부분에 인덱스를 걸면 빨라질 것이라고 생각했는지?
  
  > MySQL의 Leftmost Prefix Rule ( = 복합 인덱스에서 인덱스의 왼쪽 컬럼부터 순서대로 사용되어야 한다는 규칙 ) 에 기반하여 인덱스 효율성을 극대화하는 순서로 설계
  
  
• 복합인덱스를 걸면 순서가 중요한데 순서는 왜 이렇게 해주셨는지?
  
  복합 인덱스의 Leftmost Prefix Rule을 준수하여 설계했습니다. 동등 비교(=) 조건인 source를 선두에 배치해 데이터 탐색 범위를 먼저 최소화하고, 이후 정렬 및 범위 조건인 created_at을 배치하여 별도의 정렬 연산(Filesort) 없이 데이터를 순차적으로 읽을 수 있도록 최적화했습니다.
  
  선행 컬럼 (source): 카디널리티가 낮더라도 동등 비교(=) 조건을 사용하는 컬럼을 가장 먼저 배치하여, 인덱스 탐색의 시작점에서 불필요한 데이터 접근을 차단(Filtering)했습니다.
  
  후행 컬럼 (created_at): 1차 필터링 된 데이터 내에서 정렬(ORDER BY) 및 범위 검색(Range) 효율을 높이기 위해 배치했습니다. 이를 통해 DB가 별도의 정렬 프로세스를 수행하지 않고 인덱스 스캔만으로 데이터를 가져오도록 유도했습니다.
• 실행계획은 꼭 다 붙여줄 필요없고, 처음과 개선결과만 간략하게 보여주셔도 좋을 것 같고 없어도 괜찮을 것 같아요. 넣어주신다면 강조하고 싶은 부분을 표기해주면 좋을 것 같습니다. 단순히 이미지만 있으면 어딜봐야할지 알기 어려울 것 같아요.
  
  
• 커버링 인덱스로 조회성능 개선이면 실행계획을 봐도 좋을 것 같습니다.
  1. 쿼리 개선이기 때문에 API 수치보단 쿼리를 보면 더 정확할 것 같아요.
  2. 마찬가지로 인덱스를 건 기준설명하면 좋을 것 같습니다.

---

데이터가 쌓인 상태에서 조회를 해보면

인덱스를 추가하면 쓰기 시점에 b+tree 구조로 정렬된 상태의 데이터가 생성됨.

 

이미 인덱스로 지정된 컬럼에 대해 정렬된 상태를 가지고 있기 때문에 조회 시점에 전체 데이터를 정렬하고 필터링을 할 필요가 없다. 따라서, 조회 쿼리를 빠르게 수행할 수 있다.

 

인덱스는 순서가 중요.

---

인덱스를 생성할 때 아무것도 명시하지 않으면 기본값은 ASC(오름차순)이다.

 

즉, 아래 두 문장은 데이터베이스 입장에서 완전히 동일합니다.

-- 1. 명시 안 함 (기본값)
CREATE INDEX idx_test ON post (created_at);

-- 2. 명시 함
CREATE INDEX idx_test ON post (created_at ASC);

 

 

---

 

지금 포트폴리오에는 *HTTP/1.1 의 동시 연결제한으로 로딩 지연이 발생한것으로 적어놓았음*

 

그런데 알고 보니까 HTTP 의 동시 연결 제한 문제 때문에 웹 사이트에서 응답속도가 늦어진 것이 아니었음.

 

 

 

• 원인 분석: 근본 원인은 프런트에서 api를 호출하면 페이징 쿼리와 현재 페이지   COUNT 쿼리의 Full Table Scan으로 인해 트랜잭션 점유 시간이 길어지는 것임을 확인.
• 해결: COUNT 쿼리가 실제 테이블 접근 없이 인덱스만으로 처리되도록 **커버링 인덱스(Covering Index)**를 적용.
• 결과: 쿼리 실행 시간을 획기적으로 단축하여 전체 로딩 시간을 1초 → 0.1초 이내로 단축.

 

 

여기서 트랜잭션이란 PostService 클래스에 걸려있는 스프링의 @Transactional**을 의미.

---

1. 트랜잭션의 범위 (Scope)

@Transactional이 붙어있기 때문에, readRecentPostPaginated 메소드가 실행될 때 아래와 같은 일이 일어납니다.

1. 시작 (Begin): 메소드가 시작되면서 DB 커넥션을 하나 빌려오고 트랜잭션을 엽니다.
2. 작업 1 (Select List): 게시글 목록 20개를 가져옵니다. (findRecentPagePostsPaginated)
3. 작업 2 (Select Count): 문제의 COUNT 쿼리를 실행합니다. (postRepository.count)
4. 종료 (Commit & Release): 모든 작업이 끝나면 트랜잭션을 닫고 DB 커넥션을 반납합니다.

2. "트랜잭션 점유 시간이 길어졌다"는 뜻

인덱스가 없을 때, **작업 2 (COUNT)**가 Full Table Scan으로 인해 예를 들어 1초가 걸렸다고 칩시다.

• 트랜잭션 유지 시간: 작업 1(0.1초) + 작업 2(1초) = 총 1.1초
• 이 1.1초 동안은 이 요청이 DB 커넥션 하나를 꽉 붙잡고(점유하고) 안 놔줍니다.

만약 COUNT 쿼리가 빨라져서 0.01초 만에 끝난다면?

• 트랜잭션 유지 시간: 0.1초 + 0.01초 = 총 0.11초
• 커넥션을 0.11초 만에 쓰고 바로 돌려주게 됩니다.

 

3. 결론

즉, 포트폴리오에서 말하는 트랜잭션은 **"서비스 계층(PostService)의 메소드 실행 단위"**이자, 물리적으로는 **"DB 커넥션을 빌려서 반납하기까지의 시간"**을 말합니다.

COUNT 쿼리 하나가 느려졌을 뿐인데, 그게 포함된 전체 트랜잭션 시간이 늘어나고, 결과적으로 **귀한 커넥션(Connection)**을 오랫동안 독점하게 되어 다른 요청들이 줄을 서게 만든 것입니다.

---

 

어찌됐든 DB로의 트랜잭션 점유 시간이 길어졌기에 사이트에서 조회하는 속도가 느려졌던 것이다.


이 문제를 해결하기 위해 커버링 인덱스를 생성하고 해당 인덱스를 통하여 COUNT 쿼리는 물론이고


OFFSET이 커질수록 조회속도가 느려지는 문제 또한 해결할 것이다.

 

인덱스 설정 순서 공식

1단계: 등호(=) 조건 (무조건 맨 앞!)

• WHERE source = 'fmkorea' 처럼 정확히 하나를 콕 집는 컬럼을 가장 먼저 둡니다.
• 데이터의 범위를 획기적으로 줄여주기 때문입니다.
• 예: source, category, status 등

2단계: 범위(>, <) 또는 정렬(ORDER BY) (그다음 배치)

• WHERE created_at >= ... 처럼 범위를 검색하거나, ORDER BY에 쓰이는 컬럼을 그 뒤에 둡니다.
• 주의: 이 단계가 나오는 순간, 그 뒤에 있는 컬럼들은 검색 효율이 뚝 떨어지거나 필터링 용도로만 쓰입니다. (인덱스 매칭이 여기서 끊긴다고 보시면 됩니다.)
• 예: created_at, recommendation_count, view_count

3단계: 나머지 SELECT 절에 있는 컬럼 (맨 뒤)

• SELECT 절에만 나오고 검색 조건에는 없는 컬럼들을 마지막에 붙여줍니다.
• 이건 검색 속도를 빠르게 하는 게 아니라, 테이블을 안 열어보게 만드는 (커버링 인덱스) 용도입니다.
• 예: id, source_id 등

✅ 질문자님 케이스 적용 (정답)

질문자님의 쿼리는 아주 교과서적인 "1단계(=) + 2단계(범위/정렬)" 조합입니다.

WHERE source = :source           -- 1단계: 등호(=)
  AND created_at >= :startOfDay  -- 2단계: 범위(Range)
ORDER BY created_at DESC         -- 2단계: 정렬(Sort) - 위와 같은 컬럼이라 이어짐

그래서 만드신 인덱스 (source, created_at, ...)는 100점짜리 정답입니다.

1. source (=): 먼저 딱 필요한 그룹만 골라냄 (1단계 성공)
2. created_at (>=): 그 안에서 날짜순으로 정렬된 데이터를 쭉 긁어옴 (2단계 성공)
3. 나머지: id 등은 그냥 덤으로 가져옴 (3단계 성공)

요약: "WHERE에 나온다고 무조건 앞이 아니라, =(는) 조건을 맨 앞에 두고, >(범위) 조건은 그 뒤에 딱 하나만 둔다"라고 기억하시면 완벽합니다.

 

인덱스에서 순서의 중요성

인덱스 순서는 엄청나게 중요합니다. 순서가 틀리면 인덱스가 아예 작동하지 않거나, 성능이 100배 이상 차이 날 수도 있습니다.

쉽게 이해하실 수 있도록 **"전화번호부"**에 비유해서 설명해 드릴게요.

---

1. 왜 순서가 중요한가요? (전화번호부 원리)

전화번호부는 [성(Last Name) → 이름(First Name)] 순서로 정렬되어 있습니다.

• 인덱스: (성, 이름)
• 찾는 사람: "김철수"

1. 정상 순서 검색:
   • 성='김'을 먼저 찾습니다. (가나다순이니까 금방 찾음)
   • 그 안에서 이름='철수'를 찾습니다.
   • 👉 아주 빠름
2. 반대 순서 검색 (문제 상황):
   • 질문: "이름이 '철수'인 사람 다 찾아줘." (성은 모름)
   • 전화번호부는 '성' 기준으로 정렬되어 있지, '이름' 기준으로는 뒤죽박죽입니다.
   • 결국 전화번호부 첫 페이지부터 끝 페이지까지 다 뒤져야(Full Scan) 합니다.
   • 👉 인덱스(정렬) 효과 0

DB 인덱스도 똑같습니다. 이것을 **Leftmost Prefix Rule(가장 왼쪽부터 매칭)** 이라고 부릅니다.

---

한 줄 요약 (핵심 문장)

" '동등 조건(=)'을 선두에 배치하여 범위를 최소화하고, '정렬/범위 조건'을 후속 배치하여 Filesort를 제거했으며, 최종적으로 필요한 컬럼을 모두 포함해 커버링 인덱스를 유도함으로써 쿼리 실행 계획을 최적화했습니다."

 

📌 적용 인덱스: idx_optimized_pagination (source, created_at, source_id, id)

1. 첫 번째 컬럼: source (Equality, 카디널리티 확보)

• 이유: WHERE source = ?와 같이 동등 비교(=) 조건으로 사용되는 컬럼입니다.
• 효과: 인덱스의 첫 번째 관문에서 특정 커뮤니티(예: 'fmkorea') 데이터만 물리적으로 모여 있는 구간으로 바로 접근(Random Access)하게 하여, **탐색 범위를 획기적으로 좁히는 역할(Filtering)**을 수행합니다.

2. created_at DESC (Primary Sort Key)

• 역할: Filesort 제거 (핵심 성능 요소)
• 설명: 최신순 정렬(ORDER BY created_at DESC) 요구사항과 동일하게 **내림차순(DESC)**으로 인덱스를 구성했습니다. 이로 인해 DB는 데이터를 읽는 방향을 뒤집거나(Reverse Scan) 별도의 정렬 과정을 거칠 필요 없이, **인덱스를 정방향으로 스캔(Forward Scan)**하는 것만으로 정렬된 결과를 얻을 수 있습니다.

3. source_id DESC (Secondary Sort Key)

• 역할: 정렬의 유니크성(Uniqueness) 보장 및 안정성 확보
• 설명: 게시글 작성 시간(created_at)이 동일한 경우, 원본 글 번호(source_id)를 기준으로 내림차순 정렬하여 순서를 확정합니다. 쿼리의 정렬 조건(ORDER BY created_at DESC, source_id DESC)과 인덱스 구성을 완벽히 일치시켜 추가적인 정렬 부하를 0으로 만들었습니다.

4. id DESC (Covering & Tie-Breaker)

• 역할: 커버링 인덱스 완성
• 설명: COUNT 쿼리나 페이징 조회(SELECT id ...) 시 필요한 PK(id)까지 인덱스에 포함했습니다. InnoDB의 Clustered Index 특성상 PK는 항상 포함되지만, 명시적으로 선언함으로써 실행 계획(Explain)에서 Using index (커버링 인덱스) 사용을 확실하게 유도하고 물리적 디스크 I/O를 제거했습니다.