커서 페이징이 남기는 filesort

대량 주문 조회가 뒤 페이지로 갈수록 느려진다는 이야기가 있었고 전형적인 깊은 오프셋 문제로 보였다. 커서 방식으로 바꾸면 될 것이라 생각했는데 바꾼 뒤에도 깊은 페이지가 여전히 느렸다.

Open Table of contents

커서가 없애는 비용과 남기는 비용

실행 계획을 보니 파일 정렬이 그대로 남아 있었고 거기서 무엇을 잘못 알았는지가 드러났다. 깊은 오프셋의 비용은 앞의 행을 읽고 버리는 비용과 정렬 비용 둘로 나뉜다.

커서는 앞을 건너뛰는 비용만 없애고 정렬 비용은 그대로 둔다. 정렬이 인덱스로 해결되지 않으면 페이지마다 다시 정렬하므로 뒤로 갈수록 느린 성질이 유지된다.

정렬 대상은 상세 테이블의 기본키인데 옵티마이저는 날짜 범위로 좁혀지는 주문 테이블부터 읽고 있었다. 그 결과로 상세를 조인하고 나면 결과가 인덱스 순서가 아니므로 정렬이 필요해진다.

정렬키가 있는 테이블과 필터키가 있는 테이블이 다르면 이 상황이 생긴다. 옵티마이저는 날짜 범위가 좁다는 선택도를 크게 보고 정렬 비용을 과소평가한다.

상세 테이블부터 읽도록 조인 순서를 강제하니 실행 계획이 기본키 역방향 스캔으로 바뀌면서 파일 정렬이 사라졌다. 정렬이 공짜가 되고 주문 테이블은 기본키 동등 조회로 붙는다.

다만 이 강제는 조인이 있을 때만 필요했다. 주문 테이블 하나만 조회하는 엔드포인트는 정렬키와 필터키가 같은 테이블이라 범위 술어가 기본키 선택을 자연히 유도했다.

강제한 뒤 첫 페이지가 아예 돌아오지 않았는데 커서가 없으니 전역 최대 식별자부터 역방향으로 걸어가기 때문이었다. 날짜 범위의 최댓값까지 도달하는 동안 조건에 맞는 행을 하나도 못 만난다.

최적화가 다른 경로를 망가뜨린 것이므로 그 경로를 따로 처리해야 했다. 날짜 범위 안의 최대 식별자를 먼저 구해 커서처럼 넣으니 시작점이 데이터 근처로 당겨져 역주행이 사라졌다.

응답에 총 건수를 넣으려 했는데 같은 조건의 집계가 다시 전체 스캔과 정렬을 유발했다. 본 쿼리를 최적화한 의미가 없어지므로 다음 커서와 더 있는지 여부만 응답에 담았다.

한 건을 더 읽어 보면 다음이 있는지는 알 수 있고 그것으로 화면에 필요한 정보는 충족된다. 총 건수가 정말 필요하면 매 페이지가 아니라 별도 엔드포인트로 분리하는 편이 낫다.