Chapter 5. SQL 활용하기 - Query 작성

📝 학습 목표

---

1. 예시를 기반으로 여러가지 요구 사항에 대한 SQL 쿼리를 고민한다.
2. paging을 고려하여 쿼리를 작성한다.

 

🤔 Join과 SubQeury!

---

SQL의 기본 문법인 JOIN과 SubQuery에 대해 알고 있다는 전제 하에 시작한다!

 

(타 블로그에 정리가 잘되어 있어서 소개해본다!)

- Table JOIN

[MYSQL] 📚 테이블 조인(JOIN) - 그림으로 알기 쉽게 정리

- SUBQUERY

[MYSQL] 📚 서브쿼리 개념 & 문법 💯 정리

---

 

 

✒️ 0. 들어가기 전

---

이번 포스팅의 목표는 몇 가지 요구 사항에 대해 어떻게 쿼리를 만들어야 하는지 고민해보는 것!

- MYSQL 기준으로 스터디는 진행됐다!

✒️ 1. Query 작성 고민

---

이전 포스팅에서 DB를 설계할 때 ERD를 그리는 방법에 대해서 배웠다.

 

Query 또한 DB 설계와 마찬가지로 정해진 답은 없다!

 

어떠한 상황에서 Join을 사용할지, SubQuery를 사용할지 선택해야 하는 상황도 있고,

무엇이 맞았다고 하기 어려운 경우도 있다!

또, 한 번에 거대한 쿼리를 보내기 부담스러운 경우,

역으로 두 개로 쪼개서 보내는 것이 효율적인 상황도 있다!

 

💡 요구사항

ERD 예시

자료형은 생략했다!

+ 프로그램 특성상 세로 관계가 저렇게 꺾여서 표기된다...ㅠㅠ

다음과 같은 임시 ERD를 작성했다!

 

PM이 이러한 요구사항을 제시했다고 가정해보자!

"책이 받은 좋아요 개수를 화면에 띄워주세요!"

 

만약에 책 테이블에 좋아요 개수를 필드로 둔다면 아래와 같은 Query로 충분하다.

SELECT likes FROM book;

 

아주 간단하고 기본적인 쿼리문이다.

 

그러나, 만약 like 칼럼없이 집계를 한다면 아래와 같은 Query를 날리면 될 것이다!

SELECT conunt(*) from book_likes where book_id = {대상 책 아이디};

사실 이러한 쿼리문도 간단히 생각해 낼 수 있는 쿼리문.

 

 

그러나 아래와 같은 요구사항이 추가로 생긴다면 어떻게 해야할까!?

"책 좋아요 개수를 계산하는 건 좋은데, 내가 차단한 member의 좋아요 수는 집계하지 말아주세요!"

 

 

우선 block 테이블을 만들자!

차단 테이블

만약 book_id가 3인 책을, 나의 member_id가 6이라고 가정해보자.

member_id => owner_id = 6이고, 해당 사용자가 차단한 사용자들은 target_id 에 들어갈 것이다.

 

문장 대로 쿼리를 쓰자면,

# book_id가 3인 책의 좋아요를 집계
SELECT COUNT(*) FROM book_like where book_id = 3
# 그러나, member_id가 6인 사람이 block한 사용자들 제외
 AND user_id NOT IN (SELECT target_id FROM block WHERE owner_id = 6);

 

-> 쿼리 의미 : 사용자 6가 차단한 사용자를 제외하고 책 ID가 3인 책의 좋아요 수를 세는 것!

 

위의 쿼리를 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 = 2
WHERE bl.book_id = 3;

 

혹은 아래와 같이 Left Join을 사용해도 된다.

select count(*)
from book_like as bl
left joinblock as b on bl.user_id = b.target_id and b.owner_id = 2
where bl.book_id = 3 and b.target_id is null;

 

 

필자의 경우, join 연산보다는 sub query가 더 가독성이 좋다고 생각해 sub query를 자주 사용한다!
물론, 조인 연산을 잘 몰라서 안 쓰는 것은 비밀.
원하는 스타일의 query를 선택하여 사용하시면 될 것 같다.
허나, 둘 중에 확실히 특정 연산이 더욱 효율적이고 직관적인 상황이 있을 수 있다.

https://velog.io/@syh0397/SUBQUERY-%EC%99%80-JOIN-%EC%9D%98-%EC%B0%A8%EC%9D%B4
해당 블로그 글을 참고해보자!

 

- 그렇다면 왜 차단의 예시를 들었을까?

커뮤니티의 성격을 가진 APP의 경우, 런칭할 때 "사용자 신고/차단 기능이 없을 경우" REJECT 당할 가능성이 있기 때문에,
실제 커뮤니티 어플을 만들 때 꼭 마주쳐야 하는 상황이기 때문이다.

구체적으로 Apple의 App Store 지침에는
"불법적이거나 위험한 콘텐츠를 제거하고 사용자를 보호할 수 있는 적절한 보고 및 차단 메커니즘을 구현해야 한다"고 명시되어 있다.
Google Play 스토어도 유사한 정책을 가지고 있다.

 

💡 실제 접할 수 있는 요구사항 : 해시태그를 통한 책의 검색

쿼리를 작성할 때, N:M 관계 (다대다 관계) 로 인해 가운데 매핑 테이블이 추가된 경우

이전의 간단한 쿼리로 데이터를 가져오기 어렵다 ㅠㅠ

 

이를 테면...

개발 공부가 하고 싶어서, "JAVA"라는 해시테그가 달린 책을 찾는 상황을 가정해보자. 

 

문장 그대로 직관적으로 쿼리문을 짜보자!

내가 서브쿼리를 자주 쓰는 이유이기도 하다...

select * from book where id in 
	(select book_id from book_hash_tag 
			where hash_tag_id  = (select id from hash_tag where name = 'JAVA' ));

차근차근 실행되는 아래에서 부터 한 줄씩 읽어보면, 

• "JAVA"라는 이름의 해시태그 ID를 찾는다.
• 이전 서브쿼리에서 찾은 "JAVA" 해시태그 ID를 가지고,
  book_hash_tag 테이블에서 해당 해시태그가 달린 책의 ID(book_id)를 찾는다.
• 이전 서브쿼리에서 찾은 책 ID 목록을 사용하여, book 테이블에서 해당 ID의 책 전체 정보(*)를 선택한다.

물론, 아래처럼 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 = 'JAVA';

 

 

이번에는 책들의 목록을 최신순으로 조회하는 쿼리를 만들어보자!

이러한 기능을 위해 우리는 모든 테이블에 공통적으로 "created_at"을 놓았다!

select * from book order by created_at desc;

 

 

그럼, 조금 어렵게 좋아요 개수순으로 목록 조회를 한다고 가정한다면!?

book 테이블에는 likes 칼럼이 없기 때문에,

또, 관계를 통해 book_like 테이블을 뒤져야 한다.

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;

- 위에서 부터 코드를 읽어보면,

• book 테이블에서 모든 열(*)을 선택하고, 테이블에 'b'라는 별칭을 부여한다.
• 괄호 안의 서브쿼리 :
  book_likes 테이블에서 book_id별 좋아요 수를 계산 -> 이 결과에 'likes'라는 별칭을 부여.
• 이 서브쿼리 결과를 book 테이블과 조인한다.
  (이 때, book 테이블의 id와 서브쿼리의 book_id가 일치하는 행들을 결합)
• 마무리 :
  조인 결과를 likes 별칭의 like_count 열 기준으로 내림차순 정렬한다.

>> 인기순 정렬까지 쿼리로 작성해 보았다!

 

✒️ 2. 페이징 (Paging)

---

💡 목록 조회, 이게 최선일까!?

과연 책들을 찾아가지고, 목록 조회를 할 때 저런 쿼리를 날리면 끝일까??.....???????

 

전자 도서관의 경우를 가정해보자...

책이 100만권 있고 사용자가 10만명 있다고 가정했을 때,

저 쿼리를 그대로 사용하면 바로 서버가 터짐과 동시에 직장을 짤릴 수도 있다.

 

그렇다면, 가장 쉽게 떠올릴 수 있는 방법은 "DB 자체를 끊어서 가져오는 것"

 

그것을 바로 Paging 이라고 한다!

 

그렇다면, 페이징의 2가지 형태에 대해 알아보자.

💡 Offset based Paging

우리가 흔히 보았던 "페이징"은 offset과 limit을 이용한 페이징 일 것이다.

우리가 흔히보는 "페이지" 형식의 게시판 사이트이다.

이런 쿼리는 어떻게 만들까!?

SQL로 Pagination을 구현하기 위해 구글을 찾다보면 아래와 같은 결과를 자주 볼 수 있다.

SELECT *
FROM table
{condition // 조건 }
LIMIT {contents 개수} OFFSET {page number}

 

우리의 사례로 변경해보자면.

select *
from book
order by likes desc
limit 10 offset 0;

limit을 통해 한 페이지에 보여줄 데이터의 개수를 정하고,

offset으로 몇 개를 건너뛸지 정하는 것이다.

 

offset은 0부터 시작하므로,

우리 요구사항의 경우 다음과 같은 쿼리로 정형화 할 수 있다.

select * from book
order by created_at desc
limit 15 offset (n - 1) * 15;

한 페이지에 15개 씩 보여준다는 뜻이다.

(물론, (n-1)*15 로 쿼리를 날리면 안되고, 계산해서 숫자로 보내야한다...)

 

인기순 정렬의 쿼리는??

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 based paging의 단점

Offset Paging은 직접 Offset을 하나 씩 옮겨가며,

Target에 도착하기 전까지 여러개의 데이터를 넘어가서 가져온다는 느낌이다.

예를 들어, 1000페이지를 가져오려면, 9999*15 개의 행을 건너뛰어야 한다........

Records가 커질 때, Offset Paging의 성능문제

 

또한 가장 큰 단점은 "추가/삭제"할 때 매우 치명적인 문제가 생긴다는 것이다.

 

특히, 새로운 게시글이 자주 올라오는 SNS를 가정해보자.

 

쉽게 인스타그램이 "OFFSET 페이징" 형식으로 아래에 페이지 번호가 있는 UI라고 생각해보자.

그렇다면 이러한 상황이 발생할 수 있다.

" 사용자가 1 페이지에서 게시글 a, b, c를 즐기고, 2페이지로 넘어갔다.
그러나 또 게시글 a, b, c가 화면에 표시되었다. "

어떻게 된 영문일까..?

 

그 사이에 게시글 3개가 새로 추가된 것이다.

즉, 새로운 데이터가 추가되거나 기존 데이터가 삭제되면 Offset 값이 변경되어 다른 결과를 반환하게 될 수 있다는 것이다.

 

💡 Cursor based Paging

Cursor paging은 이름에서 유추할 수 있듯이

"커서로 가르켜 페이징하는 방식" 이라고 생각하면 된다.

여기서 Cursor = 마지막으로 조회한 컨텐츠

 

쉽게 생각해서 이렇게 생각하면 편하다.

"방금 마지막으로 조회한 컨텐츠, 그 다음꺼부터 가져와"

 

DB의 입장에서 설명하면, 이 정도로 설명하면 될 것이다.

특정 SQL 문장을 처리한 결과를 담고 있는 영역을 가리키는 일종의 포인터!

 

실시간 데이터와 대량의 데이터(페이스북, 슬랙 , 트위터 등)을 다루는 웹사이트에서 쓰이는 페이징 방법으로써,

프론트에서 무한 스크롤(인스타 그램, 페이스북처럼 하단으로 계속 스크롤 되는 페이징 방식)을 사용하여 구현한다!

쭉쭉 컨텐츠 가져와~

그럼 다 Cursor Paging 쓰지..?

-> 단점으로는 데이터에 순서가 있어야 하며, 구현이 조금 까다롭다는 점이 있다..

 

이제 복~잡한 Cursor Paging의 쿼리의 예시를 보자.

 

(이번 예시에서는 book에 좋아요 칼럼이 있다고 가정해보자 (너무 길어진다..))

마지막으로 조회한 책의 좋아요가 20이라면

select * from book where book.likes < 20 order by likes desc limit 15;

 

이 정도의 느낌으로 보면 된다!

실제로는 마지막으로 조회한 책의 id를 가져와서 다음과 같은 형태의 쿼리를 보낼 것이다.

select * from book where book.likes < 
	(select likes from book where id = 4)
		order by likes desc limit 15;

 

- 책 목록 조회 쿼리를 커서 페이징으로 바꿔보겠다!

select * from book where created_at < 
	(select created_at from book where id = 3)
		order by created_at desc limit 15;

 

- 인기순 조회를 커서 페이징으로 바꾼다면

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;

 

- 그렇다면, 이 인기순 조회 (커서 페이징)은 잘 작동할까?

답은 "그럴 가능성이 크지만, 아닐 수 있다."이다.

개발자로써 알고리즘을 짤 때의 신조는

"우리는 아닐 가능성이 1이라도 있는 알고리즘은 사용할 수 없다." 라고 생각한다.

 

좋아요 수가 0인 데이터가 압도적으로 많다고 가정해보자.

이러한 경우 Cursor 기반 페이징이 제대로 동작하지 않을 수 있다.

 

잘 생각해보자..

마지막으로 가져온 책의 좋아요 수가 0이고, 아직 조회하지 않은 다른 책들의 좋아요 수도 0이라면?

이 경우 WHERE b.id > [마지막으로_가져온_id] 조건으로는 다음 데이터를 구분할 수 없기 때문이다.

좋아요 수가 0이라는 동일한 조건에 해당하는 데이터들이 많기 때문에 어디서부터 가져와야 할지 알 수 없다.

따라서 이런 상황에서는 Cursor 기반 페이징 방식이 제대로 작동하지 않을 수 있다.

대신에 좋아요 수가 아닌 다른 고유한 순서 필드(예: 생성 시간 등)를 활용해야 한다.

(created_at은 밀리초 6자리까지 두었기 때문에, 겹칠 일이 거의 없다고 보면된다...)

 

-> 최신순까지 적용

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;

데이터 분포에 따라 Cursor 기반 방식이 제한적일 수 있다는 점!

 

 

매우 어려운 쿼리를 한 번 짜보며 마무리 해보자.....

 

- 책 목록 조회 시 좋아요 순으로 커서 페이징을 하면서 동시에 차단한 유저의 좋아요는 집계하지 않는 쿼리

SELECT b.*
FROM book AS b
JOIN (
    SELECT bl.book_id, COUNT(*) AS like_count
    FROM book_likes AS bl
    WHERE NOT EXISTS (
        SELECT target_id
        FROM block AS bc
        WHERE bc.target_id = bl.user_id AND bc.owner_id = 3
    )
    GROUP BY bl.book_id
) AS likes ON b.id = likes.book_id
ORDER BY likes.like_count DESC, b.created_at DESC
LIMIT 15;

---

우리는 새로운 기술이 나오면, 적용하고 마냥 해맑은 아이처럼 "좋다 ㅎㅎ" 하고 쓰지말고,

해당 기술을 "왜" 쓰는지, "원리는 어떻게 되는지", "예외처리 등 빈틈을 처리해야 할 것은 없는지"
등을 사고하는 개발자가 되어야 한다고 생각한다!

무조건 적으로 장점만 100이고 단점이 0 인 기술은 없다는 점.