인덱스 관련 질문 있습니다.

Question

안녕하세요. 인덱스를 활용한 조회 성능 개선을 공부하던 중 궁금한 점이 생겨 질문드립니다. 현재 저는 OFFSET 기반 pagination을 사용하는 서비스 를 개발하고 있으며, 다음과 같은 환경에서 성능 테스트를 진행했습니다. 데이터: 약 1,000만 건 서버: EC2 t3.small DB: RDS t4g.micro k6 vus100 1. 문제 상황 초기에는 OFFSET 제한 없이 마지막 페이지까지 이동 가능하도록 구현했습니다. 하지만 데이터가 1,000만 건 수준으로 증가하자, 깊은 페이지로 갈수록 조회 속도가 급격히 느려지는 문제 를 확인했습니다. 2. 고민 및 제약 일반적으로 이 문제는 Keyset Pagination(커서 기반) 으로 해결하라고 많이 알려져 있습니다. 하지만 제 서비스는 👉 네비게이션 바를 통한 페이지 직접 이동 (ex. 1, 10, 100 페이지 클릭) 이 필요하기 때문에 Keyset 방식만으로는 요구사항을 만족시키기 어렵다고 판단했습니다. 3. 적용한 개선 방법 다음과 같은 방식으로 성능 개선을 진행했습니다. OFFSET 최대 범위를 제한 (최대 10,000 페이지 / OFFSET 100,000) 커버링 인덱스 적용 조회 방식 개선 먼저 ID만 조회 → 이후 필요한 10건만 상세 조회 전체 게시글 수(count)는 캐싱 처리 4. 성능 개선 결과 [Page 10] avg: 1.4s → 700ms p95: 4.5s → 1.8s [Page 100] avg: 17s → 1.18s p95: 24s → 3.3s [Page 1000] avg: 32.1s → 1.7s p95: 59s → 4.27s 5. 추가 제약사항 로그인 사용자와 비로그인 사용자의 조회 결과가 다름 (사용자별 구독 게시글이 포함됨) 따라서 캐시는 비로그인 사용자에만 적용 위 성능 수치는 로그인 사용자 기준 6. 현재 고민 위와 같이 개선했지만, 👉 여전히 성능이 충분하지 않다고 느끼고 있습니다. 특히 궁금한 점은 다음과 같습니다. 7. 질문 OFFSET 기반 pagination을 유지하면서 👉 추가로 성능을 개선할 수 있는 방법이 있을까요? 다음과 같은 방법들을 고려했는데, 방향성이 맞는지 궁금합니다. RDS를 2개를 사용하여 조회 성능 데이터를 각각 2개의 db가 처리하도록 한다? Keyset + OFFSET 혼합 방식 ( 일반적인 페이지 이동은 Keyset Pagination을 사용하고, 사용자가 특정 페이지를 직접 입력하거나 점프하는 경우에만 제한적으로 OFFSET 기반 조회를 사용하는 혼합 방식) RDS 스펙 업그레이드 또한 에펨코리아( https://www.fmkorea.com/ )와 같은 대형 커뮤니티는 제가 원하는 페이지 네이션 방식을 사용하면서 깊은페이지(최대 1만)도 지원하고 동시접속자 수십만 페이지 수천~수만 대량 데이터 환경에서도 빠른 조회 성능을 유지하는데 👉 이러한 서비스들은 어떤 방식으로 pagination 및 조회 성능을 처리하는지 궁금합니다.

dingcodingco · Answer

영훈님 좋은 질문 해주셔서 감사합니다. 인덱스 타입 분석하시면서 OR 조건까지 깊게 고민해보신 흔적이 잘 보입니다!! OR 조건이 정말 인덱스를 못 타는가 결론부터 말씀드리면 절대적이지 않습니다. MySQL 8.0 이상에서는 옵티마이저가 Index Merge Optimization 을 통해 OR 조건의 각 컬럼에 인덱스가 있을 때 여러 인덱스를 동시에 사용해서 결과를 합치는 방식으로 처리하기도 합니다. EXPLAIN을 보시면 type이 index_merge로 나오고 Extra 컬럼에 Using union(idx_a, idx_b) 같은 표시가 뜨는 경우가 그것입니다. 다만 옵티마이저가 비용 계산을 해보고 "그냥 풀스캔이 더 싸다"고 판단하면 인덱스를 안 탑니다. 결국 OR이 무조건 나쁜 게 아니라, 데이터 분포와 카디널리티에 따라 옵티마이저의 선택이 달라지는 거라고 이해하시면 됩니다.   UNION ALL로 분리하는 패턴이 현업에서 쓰이는가 쓸 수 있습니다! 다만 모든 OR 쿼리에 무조건 적용하는 게 아니라, EXPLAIN으로 봤을 때 index_merge가 비효율적이거나 풀스캔이 발생하는 핵심 쿼리에 한해서 적용합니다. 트래픽이 거의 없는 관리자 페이지 쿼리에까지 UNION ALL을 쓰는 경우는 드뭅니다. UNION ALL 분리 방식의 단점도 분명히 있습니다. 가독성이 떨어지고, 컬럼 추가나 조건 변경 시 두 쿼리를 모두 수정해야 해서 유지보수 부담이 늘어납니다. 그래서 실무에서는 "이 쿼리가 정말 자주 호출되고, 성능 차이가 측정 가능한 수준일 때"에만 선택적으로 적용하는 경향이 있습니다. IS NOT NULL 조건도 비슷합니다. NULL 비율이 낮아서 선택도가 좋다면 인덱스를 탈 수 있지만, 대부분이 NOT NULL인 상황이라면 옵티마이저가 풀스캔으로 빠집니다. 이때는 컬럼 설계를 다시 보거나, 함수 기반 인덱스 또는 부분 인덱스를 고려하기도 합니다. 모든 조건을 다 고려하면서 쿼리를 짜는가 절대 처음부터 그렇게 짜지는 않습니다!! 부담가지지 마세요 모든 쿼리를 다 완벽하게 최적화하려고 하면 개발 속도가 너무 느려지고, 사실 대부분의 쿼리는 그 정도까지 안 가도 잘 돌아갑니다. 현실적인 접근은 이런 흐름입니다. 일단 평범하게 짜고, 슬로우 쿼리 로그나 APM(예: 핀포인트, 데이터독)으로 병목이 되는 쿼리를 식별하고, 그제서야 EXPLAIN으로 분석해서 개선합니다. 즉, 나열해주신 "인덱스를 타기 어렵게 만드는 조건들"은 쿼리를 처음 작성할 때의 체크리스트라기보다는, 느린 쿼리를 발견했을 때 원인을 찾는 진단 도구에 가깝다고 보시면 됩니다. (물론 DBA 분들이 회사에 계시면, 이를 미리 점검해주시거나 혹은 점검해오라고 요청하시기도 할거에요 ㅎㅎ) 질문하신 케이스처럼 이미 풀스캔을 발견하셨다면, 그 다음 단계는 EXPLAIN으로 옵티마이저가 왜 인덱스를 안 탔는지 확인하시는 거예요. possible_keys에 인덱스가 잡히는데 실제 key가 NULL이라면 옵티마이저의 선택 문제고, possible_keys 자체가 없으면 인덱스 설계 문제입니다. 이 둘은 해결 방법이 완전히 다릅니다. 지금 fullscan을 발견하신 그 쿼리, EXPLAIN 결과(type, key, rows, Extra 컬럼)와 실제 실행 시간을 측정해두시고, OR 분리 전후를 비교해서 수치를 만들어보세요. 너무 좋은 이력서 한 줄이 될 것 같습니다!! 오늘도 좋은 하루 보내세요

communityai8509 · Answer

안녕하세요, 인프런 AI 인턴입니다. 문영훈님. 인덱스 사용과 관련하여 복잡한 쿼리에서 성능 최적화에 대한 질문을 주셨네요. 현업에서 인덱스 최적화는 DB 성능을 극대화하는 중요한 작업 중 하나입니다. 주신 리스트와 같은 조건이 인덱스 효율성을 낮출 수 있고, 이를 고려하여 쿼리를 작성하는 것이 일반적입니다. 쿼리 최적화 방법: OR 조건의 활용 : 말씀하신 것처럼 OR 조건은 인덱스 효율성을 저하시킬 수 있습니다. 따라서, 가능하다면 쿼리를 재구성하여 UNION ALL 또는 다른 구조로 변경해 성능을 개선할 수 있습니다. 햄스터와 조합해 보기 : 데이터베이스 옵티마이저가 OR 조건을 잘 처리하지 못할 경우 UNION ALL 을 사용함으로써 개별 조건의 인덱스를 탈 수 있도록 해주는 것이 좋습니다. 함수와 연산자의 사용 : 인덱스가 적용되는 필드에 함수를 적용하면, 인덱스를 활용하지 못할 수 있습니다. 함수를 사용하지 않고 필드를 직접 비교하도록 쿼리를 수정하는 것이 좋습니다. LIKE와 와일드카드 사용 : LIKE 문법에서는 가장 앞에 % 를 붙이지 않는 것이 좋습니다. % 를 앞에 붙일 경우, 인덱스가 무용지물이 되기 쉽습니다. 널 비교 : NULL 값의 비교나 IS NOT NULL 조건을 자주 사용하는 경우에도 주의가 필요합니다. 옵티마이저가 인덱스를 사용하지 않을 가능성이 높습니다. 샘플 데이터 추출을 통한 성능 테스트 : 인덱스 설계를 변경하거나 쿼리를 재구성해 볼 수 있습니다. 이러한 조건을 모두 고려하면서 쿼리를 작성하는 것이 이상적이지만, 실제로는 데이터 구조와 쿼리 사용 내역을 기반으로 필요한 부분만 중점적으로 최적화합니다. 문영훈님께서 언급하신 문제에 대해 더 많은 예시나 이해를 돕고자 비슷한 문제를 다룬 게시글을 추천드립니다: - 효율적인 인덱스 사용과 쿼리 최적화 전략 저는 질문자님의 이해를 돕고자 지식공유자님의 기존 답변을 참고하여 유사한 답변 링크를 추천해 드리고 있습니다. 현재 베타 기능으로 답변이 만족스럽지 않을 수 있는 점 양해 부탁드립니다. 🙏 추가적으로 궁금한 점이 있으시면, 이어서 질문해 주세요. 곧 지식공유자께서 답변해 주실 것입니다.