05. 실전 SQL - 어떤 Query를 작성해야 할까?
💡 학습 목표
1️⃣ 4주차 ERD에 대한 SQL 쿼리 작성
2️⃣ paging을 고려한 쿼리 작성
이번 챕터에서는 4주차에서 설계한 ERD를 보고 어떤 SQL 쿼리를 작성하는 것이 좋을지 학습해 보려고 한다!
💭 요구사항 & ERD
지난 주차에서는 위의 요구사항을 바탕으로 데이터베이스를 설계하는 방법을 학습했다.
4주차 실습에서 완성한 ERD는 다음과 같다!
SQL Query 작성
1️⃣ 요구사항 1
책이 받은 좋아요 개수를 보여준다.
위와 같은 요구사항이 있을 때, book 테이블에 likes 속성이 있다면 아래와 같은 쿼리로 데이터를 줄 수 있다.
select likes from book;하지만, 4주차 ERD에서는 book_likes 테이블에 두어 집계에 대한 조건이 생겼을 때 대응하기 쉽도록 구현했다.
(💭 예를 들어, 사용자 간 차단 기능이 생겨 차단한 사용자가 누른 좋아요는 집계를 하지 않는다는 조건이 생길 수 있었다.)
따라서 아래와 같은 쿼리를 적용해야 좋아요 개수를 집계할 수 있다!
select count(*) from book_likes where book_id = {대상 책 id};이번엔 위의 요구 사항에 집계에 대한 조건을 추가해 보자.
책의 좋아요 갯수를 계산하는데, 내가 차단한 사용자의 좋아요는 집계를 하지 않는다.
차단 정보를 저장하는 block 테이블이 위처럼 생겼다고 가정하면, 아래와 같은 쿼리가 필요하다.
# subquery 사용
select count(*) from book_like where book_id = {대상 책 id}
and user_id not in (select target_id from block where owner_id = {내 id});위 쿼리는 조인을 이용해서도 표현할 수 있다!
# inner join 사용
select count(*)
from book_like as bl
inner join block as b on bl.user_id = b.target_id and b.owner_id = {내 id}
where bl.book_id = {대상 책 id};# left join 사용
select count(*)
from book_like as bl
left joinblock as b on bl.user_id = b.target_id and b.owner_id = {내 id}
where bl.book_id = {대상 책 id} and b.target_id is null;이렇게 같은 요구사항에 대해 여러 쿼리를 작성할 수 있는데, 이는 각자 원하는 스타일로 선택하면 된다.
subquery 를 사용하는 것이 join 연산을 사용하는 것보다 가독성이 좋다고 느껴질 수 있다! 🤗
2️⃣ 요구사항 2
해시태그를 통해 책을 검색할 수 있다.
지난 실습에서 책과 해시태그의 관계는 N : M 관계 이기 때문에 두 테이블 사이에 book_hash_tag 매핑 테이블을 두어 설계했다.
이런 경우에는 한 줄로 끝나는 쿼리로는 조회할 수 없고, subquery 혹은 join 연산을 이용해야지만 조회가 가능하다.
UMC 라는 해시태그가 붙은 책을 찾는다고 가정하면 아래와 같이 쿼리를 작성할 수 있다.
# subquery 사용
select * from book where id in
(select book_id from book_hash_tag
where hash_tag_id = (select id from hash_tag where name = 'UMC' ));# join 사용
select b.*
from book as b
inner join book_hash_tag as bht on b.id = bht.book_id
inner join hash_tag as ht on bht.hash_tag_id = ht.id
where ht.name = 'UMC';3️⃣ 요구사항 3
책들의 목록을 최신 순으로 조회할 수 있다.
이번에는 책들의 목록을 최신 순으로 조회하는 쿼리를 작성해 보자.
간단하게 ORDER BY 절에 book 테이블의 created_at 속성을 사용하여 검색 결과를 정렬할 수 있다.
select * from book order by created_at desc;만약 목록을 조회하는 조건을 조금 수정하여 아래와 같은 요구사항이 있다고 가정하면,
책들의 목록을 좋아요 개수 순으로 조회할 수 있다.
book 테이블에 likes 속성이 없기 때문에 subquery를 사용해야 한다.
select * from book as b
join (select count(*) as like_count
from book_likes
group by book_id) as likes on b.id = likes.book_id
order by likes.like_count desc;이 기능은 인기 순 정렬이라고도 표현할 수 있는데, 목록 조회를 할 때 위와 같은 쿼리를 사용한다면 전체 책에 대해 조회할 때 시간이 엄청 오래 걸릴 수 있다.
따라서 페이징을 사용하는 것이 좋다!
페이징(Paging)
페이징(Paging) 은 Database에서 데이터를 끊어서 가져오는 것을 의미한다.
페이징에는 Offset based paging 과 Cursor based pasing 두 가지 형태가 있다.
Offset based 페이징
Offset based paging 은 위 이미지처럼 직접 페이지 번호를 찾아서 이동하는 페이징이다.
쿼리는 다음과 같이 작성할 수 있다.
select *
from book
order by likes desc
limit 10 offset 0;LIMIT 절 을 통해서 한 페이지에서 보여줄 데이터의 개수를 정하고, offset 으로 몇 개를 건너뛸지를 정할 수 있다.
만약 페이지 x번에 대하여 한 페이지에 y개의 데이터를 보여준다면 아래처럼 쿼리를 작성하면 된다.
select *
from book
order by likes desc
limit y offset (x - 1) * y;여기서 (x - 1) 인 이유는 보통 1페이지가 첫 페이지이기 때문이다! 🙃
목록 조회 offset paging
이번에는 책들 목록을 최신 순으로 조회하는 쿼리를 offset paging 을 사용하여 바꿔보자.
select * from book order by created_at desc;⬇️
# offset paging 적용
select * from book
order by created_at desc
limit 15 offset (n - 1) * 15;페이지 n번에 대하여 한 번에 15개의 데이터씩 보여준다고 가정했다.
(💡 실제 MySQL 쿼리에서는 (n - 1) * 15 가 아닌 계산된 값을 넣어야 한다.)
인기 순 offset paging
책들의 목록을 좋아요 개수 순으로 조회한 인기 순 정렬도 offset paging 을 이용한 쿼리로 작성할 수 있다.
select * from book as b
join (select count(*) as like_count
from book_likes
group by book_id) as likes on b.id = likes.book_id
order by likes.like_count desc;⬇️
# offset paging 적용
select * from book as b
join (select count(*) as like_count
from book_likes
group by book_id) as likes on b.id = likes.book_id
order by likes.like_count desc
limit 15 offset (n - 1) * 15;그러나 offset paging은 몇 가지 단점이 존재한다.
- 페이지가 뒤로 갈수록 넘어가야 하는 데이터가 많아져 성능 상의 문제
- 페이지 이동하려는 찰나에 게시물이 추가되면 중복이 생김
이런 단점을 보완한 페이징 기법이 Cursor based paging 이다!
Cursor based 페이징
Cursor based paging 은 커서로 무언가를 가리켜 페이징을 하는 방법으로, 커서는 마지막으로 조회한 콘텐츠를 의미한다.
즉, 마지막으로 조회한 대상 그 다음부터 가져온다고 생각하면 된다.
예를 들어, 마지막으로 조회한 책의 좋아요 수가 20이라면 쿼리는 다음과 같이 작성할 수 있다.
select * from book where book.likes < 20 order by likes desc limit 15;실제로는 20이 아닌 마지막으로 조회한 책의 아이디를 가져와서 좋아요 수를 조회하는 형태로 쿼리를 작성해야 한다.
select * from book where book.likes <
(select likes from book where id = 4)
order by likes desc limit 15;목록 조회 cursor paging
책 목록 조회 쿼리를 cursor paging 을 이용한 쿼리로 작성하면 다음과 같다.
# cursor paging 적용
select * from book where created_at <
(select created_at from book where id = 3)
order by created_at desc limit 15;인기 순 cursor paging
인기 순 조회 쿼리를 cursor paging 을 이용한 쿼리로 작성하면 다음과 같다.
# cursor paging 적용
select * from book as b
join (select count(*) as like_count
from book_likes
group by book_id) as likes on b.id = likes.book_id
where likes.like_count < (select count(*) from book_likes where book_id = 3)
order by likes.like_count desc limit 15;하지만 위 쿼리는 제대로 동작하지 않는데, 그 이유는 인기 순 정렬 값이 같은 값이 생길 수도 있기 때문이다.
따라서 좋아요 수가 같을 경우 최신 순으로 정렬되도록 ORDER BY 절 에 b.created_at 을 추가한 쿼리는 다음과 같다.
select * from book as b
join (select count(*) as like_count
from book_likes
group by book_id) as likes on b.id = likes.book_id
where likes.like_count < (select count(*) from book_likes where book_id = 3)
order by likes.like_count desc, b.created_at desc limit 15;최용욱님의 [UMC Server Workbook]을 기반으로 작성했습니다.
