๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค

N+1 ๋ฌธ์ œ์™€ ํ•ด๊ฒฐ๋ฐฉ๋ฒ•

์„œ์•„๋ž‘๐Ÿ˜ 2024. 12. 11. 23:55

 

 

N+1 ๋ฌธ์ œ๋ž€?

N+1 ๋ฌธ์ œ๋Š” ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค์™€ ๊ด€๋ จ๋œ ์„ฑ๋Šฅ ๋ฌธ์ œ๋กœ, ์ฃผ๋กœ ORMs(Object-Relational Mapping)์—์„œ ๋ฐœ์ƒํ•ฉ๋‹ˆ๋‹ค.
์ด๋Š” ํ•œ ๋ฒˆ์˜ ์ฟผ๋ฆฌ๋กœ ์ถฉ๋ถ„ํžˆ ๋ฐ์ดํ„ฐ๋ฅผ ๊ฐ€์ ธ์˜ฌ ์ˆ˜ ์žˆ์Œ์—๋„ ๋ถˆ๊ตฌํ•˜๊ณ , ์ถ”๊ฐ€์ ์ธ ๋ฐ์ดํ„ฐ๋ฅผ ๊ฐ€์ ธ์˜ค๊ธฐ ์œ„ํ•ด N๊ฐœ์˜ ์ถ”๊ฐ€ ์ฟผ๋ฆฌ๊ฐ€ ์‹คํ–‰๋˜๋Š” ์ƒํ™ฉ์„ ๋งํ•ฉ๋‹ˆ๋‹ค.
์ด๋กœ ์ธํ•ด ์„ฑ๋Šฅ์ด ์ €ํ•˜๋˜๊ณ , ํŠนํžˆ ๋ฐ์ดํ„ฐ๊ฐ€ ๋งŽ์•„์งˆ์ˆ˜๋ก ์‹ฌ๊ฐํ•œ ๋ณ‘๋ชฉํ˜„์ƒ์„ ์ผ์œผํ‚ฌ ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค.

 


 

N+1 ๋ฌธ์ œ์˜ ์˜ˆ์‹œ

์ƒํ™ฉ

๋‹ค์Œ๊ณผ ๊ฐ™์€ ๋‘ ๊ฐœ์˜ ํ…Œ์ด๋ธ”์ด ์žˆ๋‹ค๊ณ  ๊ฐ€์ •ํ•ฉ๋‹ˆ๋‹ค.

  1. Author ํ…Œ์ด๋ธ”
    id name
    1 John Doe
    2 Jane Smith
  2. Book ํ…Œ์ด๋ธ”
    id title author_id
    1 Book A 1
    2 Book B 1
    3 Book C 2

 

์ฝ”๋“œ ์˜ˆ์‹œ (Python + SQLAlchemy)

from sqlalchemy.orm import joinedload
from models import Author, Book

# ์ž˜๋ชป๋œ ๋ฐฉ์‹: N+1 ๋ฌธ์ œ ๋ฐœ์ƒ
authors = session.query(Author).all()
for author in authors:
    print(author.name, [book.title for book in author.books])

 

๋ฐœ์ƒํ•œ ๋ฌธ์ œ

  1. session.query(Author).all()๋กœ Author ํ…Œ์ด๋ธ”์˜ ๋ชจ๋“  ๋ ˆ์ฝ”๋“œ๋ฅผ ๊ฐ€์ ธ์˜ต๋‹ˆ๋‹ค. (1๊ฐœ์˜ ์ฟผ๋ฆฌ)
  2. ๊ฐ Author์— ์—ฐ๊ฒฐ๋œ Book ๋ฐ์ดํ„ฐ๋ฅผ ๊ฐ€์ ธ์˜ค๊ธฐ ์œ„ํ•ด N๊ฐœ์˜ ์ถ”๊ฐ€ ์ฟผ๋ฆฌ๊ฐ€ ์‹คํ–‰๋ฉ๋‹ˆ๋‹ค.

์˜ˆ:

SELECT * FROM Author; -- 1๊ฐœ์˜ ์ฟผ๋ฆฌ
SELECT * FROM Book WHERE author_id = 1; -- N๊ฐœ์˜ ์ฟผ๋ฆฌ ์ค‘ ์ฒซ ๋ฒˆ์งธ
SELECT * FROM Book WHERE author_id = 2; -- N๊ฐœ์˜ ์ฟผ๋ฆฌ ์ค‘ ๋‘ ๋ฒˆ์งธ
...

์ฆ‰, ์ด 1 + N๊ฐœ์˜ ์ฟผ๋ฆฌ๊ฐ€ ์‹คํ–‰๋ฉ๋‹ˆ๋‹ค.

 


 

ํ•ด๊ฒฐ ๋ฐฉ๋ฒ•

1. Eager Loading (๋ฏธ๋ฆฌ ๋กœ๋”ฉ)

ORM์—์„œ ๊ด€๊ณ„๋ฅผ ๋ฏธ๋ฆฌ ๋กœ๋”ฉํ•˜์—ฌ ํ•„์š”ํ•œ ๋ฐ์ดํ„ฐ๋ฅผ ํ•œ ๋ฒˆ์˜ ์ฟผ๋ฆฌ๋กœ ๊ฐ€์ ธ์˜ฌ ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค.

authors = session.query(Author).options(joinedload(Author.books)).all()
for author in authors:
    print(author.name, [book.title for book in author.books])

์‹คํ–‰๋˜๋Š” SQL:

SELECT * FROM Author LEFT OUTER JOIN Book ON Author.id = Book.author_id;

 

2. Raw SQL ํ™œ์šฉ

ํ•„์š”ํ•œ ๋ฐ์ดํ„ฐ๋ฅผ ์ง์ ‘ SQL๋กœ ๊ฐ€์ ธ์˜ต๋‹ˆ๋‹ค.

query = """
SELECT Author.name, Book.title 
FROM Author 
LEFT JOIN Book ON Author.id = Book.author_id;
"""
result = session.execute(query).fetchall()

for row in result:
    print(row.name, row.title)

 

3. Batch Loading (Batch Query)

๋ฐ์ดํ„ฐ๋ฅผ ๊ทธ๋ฃนํ™”ํ•˜์—ฌ ๊ฐ€์ ธ์˜ต๋‹ˆ๋‹ค.

authors = session.query(Author).all()
author_ids = [author.id for author in authors]
books = session.query(Book).filter(Book.author_id.in_(author_ids)).all()

# Mapping
books_by_author = {}
for book in books:
    if book.author_id not in books_by_author:
        books_by_author[book.author_id] = []
    books_by_author[book.author_id].append(book)

for author in authors:
    print(author.name, books_by_author.get(author.id, []))

 


 

N+1 ๋ฌธ์ œ๋ฅผ ์˜ˆ๋ฐฉํ•˜๋Š” ๋ฐฉ๋ฒ•

  1. ORM์˜ Lazy Loading ๊ธฐ๋ณธ ์„ค์ • ํ™•์ธ
    • ๊ด€๊ณ„ ํ•„๋“œ์˜ ๊ธฐ๋ณธ ๋กœ๋”ฉ ์ „๋žต์„ Lazy๋กœ ์„ค์ •ํ•˜๋ฉด N+1 ๋ฌธ์ œ๊ฐ€ ๋ฐœ์ƒํ•˜๊ธฐ ์‰ฝ์Šต๋‹ˆ๋‹ค.
  2. Eager Loading ํ™œ์šฉ
    • joinedload, selectinload์™€ ๊ฐ™์€ ๋ฏธ๋ฆฌ ๋กœ๋”ฉ ์˜ต์…˜์„ ์‚ฌ์šฉํ•ฉ๋‹ˆ๋‹ค.
  3. ์ฟผ๋ฆฌ ์ตœ์ ํ™”, fetch join์‚ฌ์šฉ
    • ํ•„์š”ํ•˜์ง€ ์•Š์€ ๋ฐ์ดํ„ฐ๋ฅผ ๊ฐ€์ ธ์˜ค์ง€ ์•Š๋„๋ก ์ฟผ๋ฆฌ๋ฅผ ์ตœ์†Œํ™”ํ•ฉ๋‹ˆ๋‹ค.
    • ์ง์ ‘์ ์ธ join์„ ์‚ฌ์šฉํ•˜๋„๋ก ํ”„๋ ˆ์ž„์›Œํฌ์—์„œ ์ง€์›ํ•˜๋Š” ์˜ต์…˜์„ ์‚ฌ์šฉํ•˜์—ฌ ์ฟผ๋ฆฌ๋ฅผ ์ตœ์†Œํ™” ํ•ฉ๋‹ˆ๋‹ค. JPA์—์„œ๋Š” @Query annotation์„ ์‚ฌ์šฉํ•ด์„œ fetch joinํ•˜๋„๋ก ํ•ฉ๋‹ˆ๋‹ค.
  4. ํ”„๋กœํŒŒ์ผ๋ง ๋„๊ตฌ ์‚ฌ์šฉ
    • SQLAlchemy์˜ echo=True ์˜ต์…˜์ด๋‚˜ Django์˜ django-silk๋ฅผ ์‚ฌ์šฉํ•ด ์‹คํ–‰๋˜๋Š” SQL ์ฟผ๋ฆฌ๋ฅผ ํ™•์ธํ•ฉ๋‹ˆ๋‹ค.
  5. ๋ฐ์ดํ„ฐ ์ ‘๊ทผ ํŒจํ„ด ๋ถ„์„
    • N+1 ๋ฌธ์ œ๋Š” ๋ฐ์ดํ„ฐ์— ์ ‘๊ทผํ•˜๋Š” ๋ฐฉ์‹์—์„œ ๋น„๋กฏ๋˜๋ฏ€๋กœ, ํŒจํ„ด์„ ๋ถ„์„ํ•˜๊ณ  ์ ์ ˆํžˆ ์กฐ์ •ํ•ฉ๋‹ˆ๋‹ค.