쇼핑물 사이트에 흔한 기능으로 낮은 금액순, 높은 금액순으로 정렬하는 기능이 있습니다. 금액 순으로 정렬하고 페이지를 선택해 원하는 데이터를 가져옵니다. 이 기능을 VIEW를 이용해 구현해보려 합니다.
VIEW 사용 이유
일정 범위의 데이터를 조회할 때 자주 사용하는 방식은 OFFSET, LIMIT을 사용하는 방식입니다. 이 방식은 처음부터 시작해 OFFSET을 찾고 그 다음부터 LIMIT까지 데이터를 가져옵니다. 즉, 가져올 데이터가 뒤에 있으면 거의 모든 테이블을 탐색합니다. 이 방식은 데이터셋이 커지면 성능이 저하됩니다.
OFFSET, LIMIT 대신 BETWEEN을 사용했습니다. PAGE를 입력 받으면 SIZE를 이용해 시작과 끝 범위를 구해 해당 범위에 데이터만 접근하는 방식입니다. 이 방식은 기본키로 정렬 된 테이블에는 효과적인 방법입니다. 하지만 기본키가 아닌 다른 열로 정렬하면 기본키가 흩어져 버려저 사용하기 어려워 집니다. 이로 인해 BETWEEN을 사용하려면 정렬을 했을 때 새로운 일련번호가 필요합니다.
일련번호를 부여할 방법으로 CTE(Common Table Expression)과 VIEW 방법을 생각했습니다. 그 중 VIEW를 최종 선택했습니다. 가장 큰 이유는 지속성이었습니다. CTE는 쿼리 실행 동안만 존재하는 임시적인 결과 집합으로 쿼리가 완료되면 사라집니다. 반면 VIEW는 DB 내에 저장되며 삭제되기 전까지 계속 존재합니다. 낮음 금액순 조회, 높은 금액순 조회로 자주 사용될 것을 생각하여 지속적으로 존재하는 VIEW를 사용하기로 결정했습니다.
인덱스 추가
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`)
)ITEM 테이블의 SQL 입니다. 여기서 item_price를 기준으로 정렬을 할겁니다. VIEW를 생성하기 전 item_price에 INDEX를 추가하려 합니다. INDEX를 추가하면 정렬 작업에 성능이 올라갑니다.
create index idx_item_price on item(item_price)가격은 중복을 허용하므로 UNIQUE INDEX가 아닌 INDEX로 만들었습니다.
show index from item인덱스가 잘 생성되었는지 확인 할 수 있습니다.
VIEW 생성
INDEX를 추가했으니 item_price, item_seq를 기준으로 정렬하는 view를 만들겠습니다. item_seq까지 정렬 조건에 포함한 것은 같은 값일 때 일련번호 순서대로 나오게 하기 위함입니다.
create view sorted_item_by_price as
select
row_number() over (order by i.item_price, i.item_seq) as row_num,
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
일련 번호를 부여하기 위해 row_number() 함수를 사용했습니다. item_price, item_seq 기준으로 정렬하면서 row_num이란 컬럼으로 일련번호가 생성됩니다.
drop view sorted_item_by_price만약 삭제하고 싶다면 drop 명령어를 사용해 삭제 할 수 있습니다.
Spring Boot에서 MyBatis로 VIEW 연결하기
테이블이랑 연결한 것과 마찬가지로 뷰를 연결해 사용할 수 있습니다.
ItemMapper 메소드 정의
// 상품들 조회 낮은 금액순
public List<ItemListDto> getItemsByLowPrice(int start, int end) throws SQLException;
ItemMapper.xml SQL 생성
<!-- 상품들 조회 낮은 금액순 -->
<select id="getItemsByLowPrice" resultType="ItemListDto">
select item_seq, brand_name, item_name, item_price
from sorted_item_by_price
where row_num between #{start} and #{end}
</select>
ItemService 메소드 생성
// 상품들 조회 낮은 금액순 페이지
public Page<ItemListDto> getItemPageByLowPrice(int page) throws SQLException {
int size = paginationConfig.getPageSize(); // 크기
int totalContents = itemMapper.countItem(); // 총 아이템 수
int totalPages = (totalContents + size - 1) / size; // 총 페이지 수
// 범위 밖 페이지를 입력했을 때 예외 발생
if (page < 1 || page > totalPages) {
throw new PageOutOfRangeException(page, totalPages);
}
int start = page * size + 1;
int end = start + size - 1;
List<ItemListDto> contents = itemMapper.getItemsByLowPrice(start, end);
return new Page<>(page, totalPages, totalContents, contents);
}보고 싶은 페이지 번호를 입력 값으로 받습니다. 일전에 직접 만든 Page 객체에 맞게 데이터들을 만들어 채워줍니다. Page 객체에는 현재 페이지, 총 페이지 갯수, 테이블 총 데이터 갯수, 페이지에 표시할 데이터가 들어가 있습니다.
Page 객체
package hyewadong.yogosaza.dto;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import java.util.List;
@Data
@Builder
@AllArgsConstructor
public class Page<T> {
private int currentPage;
private int totalPages;
private int totalContents;
private List<T> contents;
}
ItemListDto 객체
package hyewadong.yogosaza.dto.item;
import lombok.Builder;
import lombok.Data;
/*
아이템 리스트에서 사용하는 DTO
일련번호, 이름, 가격, 브랜드이름
*/
@Data
@Builder
public class ItemListDto {
private Integer itemSeq;
private String brandName;
private String itemName;
private Integer itemPrice;
}
Controller 메소드 생성
// 상품들 조회 낮은 금액순 페이지
@GetMapping("/page/lowPrice")
public ResponseEntity<?> getItemsPageByLowPrice(@RequestParam(required = true) int page) throws SQLException {
Page<ItemListDto> result = itemService.getItemPageByLowPrice(page);
return ResponseEntity.ok(result);
}조회이므로 Get을 사용하여 구현합니다.
결과
PostMan을 사용해 API 통신 결과 입니다.
주소
localhost:8080/item/page/lowPrice?page=1
데이터
{
"currentPage": 1,
"totalPages": 1000,
"totalContents": 10000,
"contents": [
{
"itemSeq": 1414,
"brandName": "그라미치",
"itemName": "itemName01414",
"itemPrice": 1000
},
{
"itemSeq": 1583,
"brandName": "뉴발란스",
"itemName": "itemName01583",
"itemPrice": 1000
},
{
"itemSeq": 1715,
"brandName": "컨버스",
"itemName": "itemName01715",
"itemPrice": 1000
},
{
"itemSeq": 1755,
"brandName": "나이키",
"itemName": "itemName01755",
"itemPrice": 1000
},
{
"itemSeq": 1821,
"brandName": "나이키",
"itemName": "itemName01821",
"itemPrice": 1000
},
{
"itemSeq": 1826,
"brandName": "아웃스탠딩",
"itemName": "itemName01826",
"itemPrice": 1000
},
{
"itemSeq": 1877,
"brandName": "에스피오나지",
"itemName": "itemName01877",
"itemPrice": 1000
},
{
"itemSeq": 1916,
"brandName": "반스",
"itemName": "itemName01916",
"itemPrice": 1000
},
{
"itemSeq": 1958,
"brandName": "그라미치",
"itemName": "itemName01958",
"itemPrice": 1000
},
{
"itemSeq": 2095,
"brandName": "나이키",
"itemName": "itemName02095",
"itemPrice": 1000
}
]
}
후기
이번에는 VIEW를 이용해 기본키가 아닌 열의 정렬을 하고 일련번호를 부여해 페이지 조회를 구현했습니다.
어떻게 구현할지 고민을 많이 했고 혹시 더 좋은 방법이 있다면 언제든 조언해주시면 감사하겠습니다.
