SQL의 offset
페이지 단위로 데이터를 가져올 때 offset과 limit 방식을 사용했습니다. offset으로 시작 위치를 정하고 limit 만큼 데이터를 가져오는 방식입니다. 하지만 offset에는 단점이 있습니다.
바로 데이터셋이 커지면 성능 저하 문제를 일으킬 수 있다는 것 입니다. 그 이유는 offsest 방식이 데이터의 처음부터 주어진 offset까지 모든 행을 읽고 그 다음부터 limit 만큼의 데이터를 반환하기 때문입니다. 이로 인해 처리해야 할 데이터가 많아질수록 효율이 떨어집니다.
이 부분을 해결하기 위해 offset 대신 where에 > 범위 검색과 between을 도입하 성능을 비교해보겠습니다.
Item, Brand 테이블
이번에 사용할 item과 brand 테이블 입니다.
CREATE TABLE `item` (
`item_seq` BIGINT NOT NULL AUTO_INCREMENT,
`brand_seq` BIGINT NOT NULL,
`category_id` BIGINT NOT NULL,
`item_code` VARCHAR(20) NOT NULL ,
`item_name` VARCHAR(20) NOT NULL ,
`item_price` BIGINT NOT NULL,
`item_color` VARCHAR(10) NOT NULL,
`item_size` VARCHAR(10) NOT NULL,
`item_create_date` DATETIME,
primary key(`item_seq`)
)
CREATE TABLE `brand` (
`brand_seq` BIGINT NOT NULL AUTO_INCREMENT,
`brand_name` VARCHAR(20) NOT NULL,
primary key(`brand_seq`)
)Item 테이블에는 10_000개의 데이터가 들어있습니다.
기존 방식 : offset
SQL
explain
SELECT i.item_seq, b.brand_name, i.item_name, i.item_price
FROM item AS i
JOIN brand AS b
ON i.brand_seq = b.brand_seq
LIMIT 10
OFFSET 5000
결과
explain 키워드를 통해 실행 계획을 확인했습니다.
type의 ALL 풀 테이블 스캔을 의미합니다. rows 10,134를 확인할 수 있습니다. rows는 예상 검색 갯수를 나타내는 열로 예상이기에 실제 값과 미묘한 차이가 있을 수 있습니다. 하지만 10_000개의 거의 근접한 모습으로 비효율적인 모습을 확인할 수 있습니다.
개선 방안 1 : where 절에 primary key 범위 검색
offset이 아닌 where 절에서 기본키인 item_seq를 범위 검색하는 방법입니다.
explain
SELECT i.item_seq, b.brand_name, i.item_name, i.item_price
FROM item AS i
JOIN brand AS b
ON i.brand_seq = b.brand_seq
where item_seq > 5000
LIMIT 10
결과
type은 ALL에서 range 범위 검색으로 바뀌었습니다. 또한 rows 5000대로 나오면서 성능이 좋아진 것을 확인할 수 있습니다. 이 방법은 5000보다 큰 모든 아이템을 검색 한 후 상위 10개를 제한합니다.
개선 방안 2 : between 사용
offset, limit 대신 between을 사용하는 방법입니다.
explain
SELECT i.item_seq, b.brand_name, i.item_name, i.item_price
FROM item AS i
JOIN brand AS b
ON i.brand_seq = b.brand_seq
WHERE i.item_seq BETWEEN 5001 AND 5010
결과
type은 range이며 rows가 10개로 줄어들었습니다.
between을 사용하면 지정한 범위만 정확하게 검색하므로 더 효율적입니다.
제 프로젝트에서 page에 필요한 데이터 수를 between으로 가져오기로 결정했습니다.
후기
이렇게 측정을 통해 근거를 확보하고 선택하여 보람찬 시간이었습니다. 만약 더 좋은 방법이 있다면 알려주시면 감사하겠습니다.
