스프링부트로 직접 만들면서 배우는 대규모 시스템 설계 - 게시판 강의를 보고 작성한 글입니다.
-
사용DB : MySQL 8.0 Innodb
페이징 쿼리
페이징은 offset과 limit을 사용해 구현할 수 있다.
- 아래 예시의 id가 autoincrement라고 가정
select * from article
where board_id = 1
order by id desc
limit 30 offset 90;N번 페이지에서 M개의 게시글을 조회할 때 고려해볼 수 있는 것들
페이징 쿼리에 인덱스 적용
대부분의 웹 페이지는 글을 최신순으로 정렬하여 제공한다.
그리고 이 때, 최신순 정렬을 위하여 인덱스를 활용할 것이다.
예시에서 사용되는 상황은 게시판이 여러개 있고, 각 게시판마다 다수의 게시글을 적을 수 있는 서비스이다.
이 경우, 게시글을 조회할 때 특정 게시판에 접근하면 해당 게시판과 연결된 게시글이 좌르륵 나올 것이다.
때문에 서비스에서 필요한 것은
- 특정 게시판을 빠르게 탐색
- 특정 게시판에 포함된 게시글들을 빠르게 탐색하는 것이다.
이 요구사항을 위해 다음과 같은 인덱스가 필요하다.(게시판은 순서대로 보여준다고 하자)
create index idx_board_id_article_id on article(board_id asc, article_id desc);인덱스는 순서가 중요하다. 순서에 지정한 대로 데이터가 저장되기 때문이다. where board_id =? 를 할 경우, 인덱스 앞단의 정렬조건이므로 인덱스를 탈 수 있으나, board_id 조건 없이 where article_id = ? 조건만 있을 경우 인덱스 뒷단의 정렬조건이라 정렬에 인덱스를 쓰기 어렵다.
문제1: offset이 굉장히 큰 페이징 쿼리가 실행되었을 경우.
앞의 내용대로 페이징 쿼리에 인덱스를 적용했을 경우, 쿼리가 인덱스를 타며 빠르게 동작하는 것을 볼 수 있다. 하지만 Offset이 매우 큰 경우에도 빠른 실행속도를 자랑할까?
select * from article
where board_id = 1
order by article_id desc
limit 30 offset 1499970;테스트용으로 약 천 만건의 article데이터를 생성한 상태이다. 위 쿼리 실행시 약 3초의 시간이 소요되었다.
인덱스가 사용됐는데 왜 느리죠?
Innodb는 자동으로 Clustered Index를 생성한다.
우리가 생성한 인덱스는 세컨더리 인덱스(pk가 아닌 임의로 생성한 인덱스)라 row 데이터가 실제 데이터가 아니라 pk(클러스터링 인덱스)를 가리키고 있다.
즉, 인덱스 트리를 두 번 타게 된다. (세컨더리 인덱스 트리 -> 클러스터링 인덱스 트리)
여기까지는 그렇다 쳐! 그래서 그게 offset긴거랑 무슨상관? offset에 해당하는 데이터만 읽어오면 되잖아! 라고 생각했는데 그게 아니었음. 놀랍게도 offset범위에 도달하기 전에 거치는 모든 데이터를 클러스터링 인덱스 트리까지 전부 탐색하고 온다고 한다.
저 2번 때문에 시간이 오래 걸리는 거다! 놀랍게도 MySQL은 offset범위에 포함되지 않는 데이터도 끄으으읕까지 클러스터링 인덱스까지 찾아가 데이터를 읽는다!
(대체 왜...? 그냥 mysql 동작 방식인듯싶다. 최적화하기엔 너무 어려운 고충이 있지않았을까...)
문제1 - 해결! : 커버링 인덱스
해결방법이 있다! 바로 커버링 인덱스를 사용하는 것이다.
클러스터링 인덱스 트리까지 전부 탐색하는 게 문제다! 그렇다면 세컨더리 인덱스의 leaf노드에 해당하는 board_id와 article_id까지만 추출해서, 그에 해당하는 데이터들만 클러스터링 인덱스를 통해 탐색하게 할 수 있다.
이렇게 select대상에 인덱스에 쓰인 컬럼만 존재한다면 커버링 인덱스를 활용할 수 있었다.(세컨더리 인덱스 트리까지만 탐색하고, 클러스터링 인덱스 트리는 조회하지 않음)
select board_id, article_id from article
where board_id = 1
order by article_id desc
limit 30 offset 1499970;커버링 인덱스를 활용해 board_id와 article_id만 먼저 추출하고 그에 해당하는 애들만 별도의 select쿼리로 모든 데이터들을 조회해온다.
select * from (
select article_id from article
where board_id = 1
order by article_id desc
limit 30 offset 1499970
) t left join article on t.article_id = article.article_id;
이렇게 되면 offset에 해당하는 애들만, 클러스터링 인덱스에 접근하기 때문에 소요시간을 많이 줄일 수 있다.
적용 결과 3초가 소요되었던 쿼리가 약 0.2초로 줄어들었다.
UsingIndex : 커버링 인덱스 사용
문제2: 더욱 극단적인 데이터 조회! 300_000번 페이지 조회
select * from (
select article_id from article
where board_id = 1
order by article_id desc
limit 30 offset 8999970
) t left join article on t.article_id = article.article_id;
약 1.5초 정도로 다시 느려졌다. 아니, 커버링 인덱스까지 적용했는데 또 왜 느려져?
board_id와 article_id를 커버링 인덱스로 가져오려면 어쨌든 세컨더리 인덱스 트리까지는 필수적으로 탐색해야한다. 세컨더리 인덱스만 탄다고 하더라도 offset만큼 인덱스 스캔이 필요하기에 극단적으로 뒤에 있는 페이지를 조회하면 어쩔 수 없이 소요시간이 늘어나게 된다.
문제2 - 해결1 : 테이블 분리
- 1년단위로 테이블 분리
- offset을 인덱스 페이지 단위로 스킵하는게아닐 1년동안 작성된 게시글 수 단위로 즉시 시킵
- 조회하고자 하는 오프셋이 1년동안 작성된 게시글 수보다 크다면 해당 개수만큼 즉시 스킵
- 애플리케이션에서 처리 코드 추가 필요
- 애당초 300_000번 페이지를 조회하는 게 정상적인 사용자일지 여부도 중요함! 데이터 수집을 목적으로 하는 비정상적인 사용자일수도 있음. 이 경우 정책으로 풀어낼 수도 있다. "게시글 목록 조회는 10,000번까지만 해!" 또는 "10,000번 이후 게시글부터는 시간 범위 또는 텍스트 검색 기능만 제공해서 탐색할 수 있게 한다. : 더 작은 데이터 집합내에서 페이징을 수행하는 게 가능해짐"(물론 검색......으로 가면 또 고려해야할 게 많겠지만)
문제2 - 해결2 : 무한스크롤
무한스크롤에서는 아무리 뒷 페이지로 가더라도 균등한 조회 속도를 가진다. (이후 상세하게 살펴봄)
뒷 페이지가 잘 조회되는 서비스라면 페이징 대신 무한스크롤을 고려해보는 것도 좋아!
게시글의 개수를 조회할 때 고려해볼 수 있는 것들
문제: 게시글이 많으면 count쿼리 느려짐
페이징시, 페이지 개수를 구해야한다. -> 즉, 게시글의 개수를 카운트해야한다.
하지만 게시글의 개수가 매우 많으면 count쿼리의 동작이 느리다.
해결 : 한 번에 볼 수 있는 페이지 번호 개수를 제한
완전 기술이 아닌 정책적인 부분으로 해결해볼 수 있다!!!
일단, 정말 모든 게시글의 개수가 필요한 지를 생각해보자.
- 대부분의 사용자는 앞단의 게시글에 자주 접근하고, 오래된 게시글에는 잘 접근하지 않을거다.
- 즉, 전체 게시글 수가 크리티컬하게 필요한 것이 아니다.
- 사용자가 이동 가능한 페이지 번호 활성화가 중점이다.
때문에 구글같은 대다수의 웹 페이지에서는 페이지 당 30개의 게시글이 노출되고, 10 페이지씩 이동이 가능과 같이, 전체 페이지 개수를 노출시키지 않고 사용자가 뒷 페이지로 넘어갈때 마다 해당 페이지를 기준으로 앞뒤 10페이지씩만 노출시키는 등의 방법을 사용하고 있다.
이 경우 사용자가 현재 이용 중인 페이지 기준에 따라서, 전체 게시글 개수가 아닌 일부 게시글 개수만 확인하면 된다.
쿼리에서 범위에 해당하는 일부의 게시글 개수만 구함.
select count(*)
from (
select article_id from article where board_id = {board_id} limit {limit}
) t;사용자가 10,001~10,010번 페이지에 있다고 가정하면, 300,301개까지만 카운트 하면 된다. -> 약 0.07초 소요됨.
