[MariaDB] OFFSET 페이징 & JOIN이 느릴 때 MariaDB 인덱스 최적화 경험

 

목차

1. 들어가며: 왜 인덱스가 있어도 쿼리가 느릴까?
2. 문제 상황: JOIN + ORDER BY + LIMIT 조합이 느려진 이유
3. 실제 병목 분석: LEFT JOIN의 rows 56,805 / 15,190 문제
4. 해결 전략 1: ORDER BY에 인덱스가 안 타는 구조 바꾸기 (서브쿼리 활용)
5. 해결 전략 2: JOIN 테이블 인덱스 강제 사용 (user_info / profile_info)
6. 해결 전략 3: 프로필 테이블 복합 인덱스 최적화 (userID, set_default)
7. Before, After
8. 정리: 쿼리 최적화의 핵심 원칙 5가지
9. 마치며: 실제 서비스에서 쿼리 튜닝할 때 기억해야 할 점

---

 1. 들어가며: 왜 인덱스가 있어도 쿼리가 느릴까?

     마리아 디비를 쓰면서, 알고 있던 점은 테이블을 생성시 자동으로 인덱스를 생성해준다는 것이였다.

     그래서 자동으로 인덱스가 생성되니 빠르겠지.. 라고 막연하게 생각하고 있었다.

 

2. 문제 상황: JOIN + ORDER BY + LIMIT 조합이 느려진 이유

     그러다, 메세지 리스트 불러오기 페이지가 느려서 개선해달라는 요청이 들어왔다.

     확인해보니, 

     불러오는 시간이 18.31초

     확실히 불러오는 것이 느리는 게 체감 되었다.

 

사용했던 쿼리를 EXPLAIN 을 넣어 조회 해보니 아래와 같이 인덱스가 등록은 되어 있으나 사용되고 있지 않았다.

 - ui_sender, ui_receiver, ui_manager 가 전부 type=ALL, rows=56805 → 해당 테이블(ex.Users)을 매번 풀스캔

찾아보니,

1. JOIN + ORDER BY + LIMIT 조합 때문에 정렬 인덱스가 무시된 상태

2. JOIN 하는 user_info 가 5만 건씩 계속 풀스캔하는 상태

 

-> 이 2가지가 느리게 하는 원인으로 생각되었다.

 

3. 해결 전략 1: ORDER BY에 인덱스가 안 타는 구조 바꾸기 (서브쿼리 활용)

JOIN + ORDER BY 조합은 정렬 인덱스를 무시하는 경향이 있다고 들어서 서브쿼리로 바꾸도록 하였다.

원래 아래와 같았다면,

FROM 
 CastingInfo AS ci

 

FROM 자리에 서브쿼리를 둬서 전체를 돌리는 게 아닌, 내가 필요한 부분만 돌린 후에 그 안에서 다시 조회 하도록 아래와 같이 변경하였다.

FROM (
  SELECT ...
  FROM CastingInfo
  ORDER BY reg_date DESC
  LIMIT 5 OFFSET 0
) AS ci2

 

이러면 JOIN 시에는 단 5개의 row만 붙여서 매우 빨라졌다.

 

4. 해결 전략 2: JOIN 테이블 인덱스 강제 사용 (user_info / profile_info)

    사실, 3번만으로도 꽤 빨라졌지만, EXPLAIN을 해봐도 type은 여전히 ALL이였다.

    아래를 보면, 서브쿼리를 통해, rows 는 132->5 로 줄어들었지만, JOIN은 여전히 풀스캔하는 것을 볼 수 있었다.

     하여 아래처럼 쿼리에 FORCE INDEX 를 넣었다

LEFT JOIN user_info FORCE INDEX (userID)
       ON user_info.userID = ci2.sender_id;

 

     결론 적으로는 FORCE INDEX 가 아니라, 각각 조인되는 컬럼의 type을 먼저 맞춰줘야 했다.

 

5.  해결 전략 3: 프로필 테이블 복합 인덱스 최적화 (userID, set_default)

 

join조건에 아래와 같이 있었다면

ON pi.userID = ci2.receiver_id
AND pi.set_default = 'Y'

 

아래와 같이 복합 인덱스를 생성한다

ALTER TABLE profile_info
  ADD INDEX idx_profile_user_default (userID, set_default);

 

그리고 각각 조인되는 컬럼의 type을 맞춰주기

일단, 테이블의 컬럼을 조회한다.

SHOW FULL COLUMNS FROM profile_info LIKE 'userID';

 

그러면 아래처럼 타입이 나오는 데, 이 타입이 다르다면 맞춰줘야 인덱스를 활용할 수 있게 된다

 

조인하는 테이블 모두 이렇게 복합 인덱스를 생성하고, 타입을 확인하면 아래와 같이 조인했던 테이블의 rows 가 줄어든다.

또한, type 도 eq_ref형식으로 ALL에서 변경됨.

 

6. Before , After

최적화 전

최적화 후 1

 

최적화 후 2

7. 정리: 쿼리 최적화의 핵심 원칙 5가지

1. 정렬은 반드시 서브쿼리로 먼저 LIMIT 한다
2. JOIN 대상 컬럼에는 lookup 가능한 인덱스가 필수
3. 복합 인덱스는 검색 조건 순서로 만든다
4. FORCE INDEX는 실무에서 매우 유용하다
5. EXPLAIN 은 항상 “rows 줄이기” 를 중심으로 해석한다

 

8. 마치며

이번 최적화는 실제 서비스에서 체감 성능을 극적으로 개선하였다.

또한 인덱스가 보인다고 해서 쓰이는 게 아니라 반드시 explain을 해서 확인해봐야 한다는 것을 알았다.

타입 확인도 꼭 해야 하고!

 

 

참고 블로그:

https://nooblette.tistory.com/entry/MySQL-Multi-Column-Index

[MySQL] Multi Column Index(복합 인덱스)