기록
검색 쿼리 최적화
들어가며
"검색 결과가 너무 느리다는 문의가 있습니다."
카톡을 받고 운영 서버에서 확인해봤는데, 개발 서버나 로컬에서는 상품 데이터가 별로 없어서 못 느꼈지만 운영 서버에는 약 1,000건이 조금 넘는 데이터가 있었습니다.
단순 키워드 검색을 진행했더니 2~3초가 걸리는 매우 느린 성능이 나왔습니다.
사실 LIKE %keyword% 검색이 인덱스를 안 타고 풀스캔을 한다고 해도, 데이터가 약 1,000건 정도인 것을 감안하면 매우 느린 성능이었습니다.
문제 상황 (Problem)
상품 리스트 조회 API(POST /products/list)가 매우 느렸습니다.
병목 지점 분석
Sequelize의 logging: console.log 옵션을 활성화해서 실제 실행되는 SQL을 확인했습니다.
const list = await Product.findAll({
// ...
logging: console.log // SQL 로깅 활성화
});
엄청난 양의 쿼리가 나와서 당황했지만 눈에 보이는 몇 가지 문제가 있었습니다.
1. 다수의 서브쿼리
Product.findAll 하나에 likeYn, reviewAvg, reviewCnt, adYn, orderCnt 등 6개의 상관 서브쿼리가 포함되어 있었습니다.
40개씩 페이징이 걸려있는데, 결과가 40개 나오면 이 서브쿼리들이 각 행마다 실행되어 사실상 40 × 6 = 240번의 추가 스캔이 발생했습니다.
2. 같은 조건의 중복 쿼리
- 메인
Product.findAll(40개 조회) Product.count(전체 개수 조회)- 다시
Product.findAll(productIds 조회) Product.findOne(타입별/레벨별 카운트)
즉, 같은 조건으로 최소 3번 더 products 테이블을 읽고 있었습니다.
3. N+1 문제
// ex) 문제의 코드
for (let i = 0; i < list.length; i++) {
const result = list[i];
// 매 행마다 개별 쿼리 실행 (N+1)
let Images = await Images.findAll({
where: {
productId: result.id,
delYn: 'N'
}
});
}
40개의 상품이면 40번의 추가 SELECT가 순차적으로 실행됐습니다.
4. 통계 데이터 계산
주소별 카운트, 언어별 카운트를 계산하는 쿼리들도 서브쿼리 중첩이 심했습니다.
-- 주소별 통계
SELECT country, SUM((SELECT COUNT(*) FROM products ...))
-- 언어별 통계
SELECT language, SUM((SELECT COUNT(*) FROM products ...))
1차 최적화: N+1 문제 해결
해결 방법
모든 상품 ID를 미리 수집한 후, 한 번의 쿼리로 모든 이미지를 가져온 다음, productId별로 그룹핑해서 사용하도록 변경했습니다.
Before (문제)
// ex) 40개 상품이면 40번의 쿼리 실행
for (let i = 0; i < list.length; i++) {
const result = list[i];
let Images = await Images.findAll({
where: {
productId: result.id,
delYn: 'N'
}
});
}
After (개선)
// 1. 모든 상품 ID 수집
const Images = [
...new Set(list.map(item => item.id).filter(id => Number.isFinite(id)))
];
// 2. 한 번의 쿼리로 모든 상세 이미지 가져오기
let Images = {};
if (Images.length > 0) {
const detailImages = await Images.findAll({
where: {
productId: { [Op.in]: Images },
delYn: 'N',
},
});
// 3. productId별로 그룹핑
detailImages.forEach((image) => {
if (!detailImagesByProductId[image.productId]) {
ImagesByProductId[image.productId] = [];
}
ImagesByProductId[image.productId].push(image);
});
}
// 4. 반복문에서는 미리 가져온 데이터 사용
for (let i = 0; i < list.length; i++) {
const result = list[i];
const Images = ImagesByProductId[result.id] ?? [];
// ...
}
개선 효과
- 41번의 쿼리 → 2번의 쿼리로 감소 (95% 감소)
2차 최적화: 서브쿼리를 JOIN으로 변경
문제점
상품 검색 쿼리에서 **서브쿼리(Subquery)**를 사용하여 다음 정보를 조회하고 있었습니다:
- 좋아요 여부 (
likeYn) - 리뷰 평균 및 개수 (
reviewAvg,reviewCnt) - 광고 여부 (
adYn) - 주문 개수 (
orderCnt) - 정렬 기준 계산
문제의 코드 - 서브쿼리 사용
attributes: [
// ex) 좋아요 여부
[
sequelize.literal(`
IF((SELECT COUNT(*) FROM product_likes
WHERE product_id = Product.id AND member_id = ${memberId}) > 0,
'Y', 'N')
`),
'likeYn'
],
// 리뷰 평균
[
sequelize.literal(`
(IFNULL((SELECT AVG((pr.rc + pr.rr + pr.rq) / 3)
FROM product_reviews AS pr
WHERE pr.product_id = Product.id), 0))
`),
'reviewAvg'
],
// 리뷰 개수
[
sequelize.literal(`
(SELECT COUNT(*) FROM product_reviews AS pr
WHERE pr.product_id = Product.id)
`),
'reviewCnt'
],
// ... 정렬 기준에서도 동일한 서브쿼리 반복
]
각 상품마다 서브쿼리가 실행되어 DB의 부하가 크고, 쿼리 최적화도 어려웠습니다.
해결 방법
서브쿼리를 LEFT JOIN으로 변경하고, Sequelize의 include를 사용해 필요한 관계를 한 번에 가져오도록 개선했습니다.
1단계: 모델 관계 추가
// models/index.js
db.Product.hasMany(db.ProductReview, {
as: 'ProductReviews',
foreignKey: 'productId'
});
2단계: Include 구조 변경
const productInclude = [
{
model: Member,
as: "Member",
attributes: mA,
where: { ...memberWhere },
required: true, // INNER JOIN
},
{
model: ProductLike,
as: 'ProductLikes',
attributes: [], // 집계만 사용하므로 빈 배열
required: false, // LEFT JOIN
duplicating: false, // 중복 방지
where: { memberId: memberId }
},
{
model: ProductReview,
as: 'ProductReviews',
attributes: [],
required: false,
duplicating: false,
},
{
model: Order,
as: 'Order',
attributes: [],
required: false,
duplicating: false,
},
{
model: MA,
as: 'MA',
attributes: [],
required: false,
duplicating: false,
where: {
endDt: { [Op.gte]: nowIsoString }
}
},
];
3단계: 집계 함수로 변경
const productAttributes = [
'id', 'memberId', 'title', 'price', // ... 기본 속성들
// 좋아요 여부 - JOIN된 데이터로 계산
[
sequelize.literal(`IF(COUNT(DISTINCT ProductLikes.id) > 0, 'Y', 'N')`),
'likeYn'
],
// 리뷰 평균
[
sequelize.literal(`
IFNULL(AVG((ProductReviews.rc +
ProductReviews.rr +
ProductReviews.rq) / 3), 0)
`),
'reviewAvg'
],
// 리뷰 개수
[
sequelize.literal(`COUNT(DISTINCT ProductReviews.id)`),
'reviewCnt'
],
// 광고 여부
[
sequelize.literal(`IF(COUNT(DISTINCT MA.id) > 0, 'Y', 'N')`),
'adYn'
],
// 주문 개수
[
sequelize.literal('COUNT(DISTINCT `Order`.id)'),
'orderCnt'
],
];
4단계: 정렬 기준 단순화
order: [
['Rank1', 'ASC'],
['Rank2', 'ASC'],
['Priority', 'DESC'],
// JOIN된 데이터로 정렬 - 서브쿼리 제거
[
sequelize.literal(`
(COUNT(DISTINCT \\\\`Order\\\\`.id) +
IFNULL(AVG((ProductReviews.rc +
ProductReviews.rr +
ProductReviews.rq) / 3), 0))
`),
'DESC'
]
]
5단계: 중요한 옵션 추가
{
group: 'Product.id', // 집계를 위한 그룹핑
subQuery: false, // Sequelize가 불필요한 서브쿼리 생성 방지
}
최적화 전후 비교
Before - 서브쿼리 사용
SELECT
Product.id,
-- 각 상품마다 서브쿼리 실행
(SELECT COUNT(*) FROM product_likes WHERE product_id = Product.id) AS likeYn,
(SELECT AVG(...) FROM product_reviews WHERE product_id = Product.id) AS reviewAvg,
(SELECT COUNT(*) FROM product_reviews WHERE product_id = Product.id) AS reviewCnt,
(SELECT COUNT(*) FROM orders WHERE product_id = Product.id) AS orderCnt
FROM products AS Product
WHERE ...
ORDER BY
-- 정렬에서도 서브쿼리 반복
(SELECT COUNT(*) FROM orders WHERE product_id = Product.id) DESC
LIMIT 40;
After - JOIN 사용
SELECT
Product.id,
-- JOIN된 데이터를 집계
IF(COUNT(DISTINCT ProductLikes.id) > 0, 'Y', 'N') AS likeYn,
IFNULL(AVG((ProductReviews.rc + ...) / 3), 0) AS reviewAvg,
COUNT(DISTINCT ProductReviews.id) AS reviewCnt,
COUNT(DISTINCT `Order`.id) AS orderCnt
FROM products AS Product
INNER JOIN members AS Member ON Product.member_id = Member.id
LEFT JOIN product_likes AS ProductLikes ON Product.id = ProductLikes.product_id
LEFT JOIN product_reviews AS ProductReviews ON Product.id = ProductReviews.product_id
LEFT JOIN orders AS `Order` ON Product.id = `Order`.product_id
LEFT JOIN ad AS MA ON Product.id = ad.product_id
WHERE ...
GROUP BY Product.id
ORDER BY
(COUNT(DISTINCT `Order`.id) + IFNULL(AVG(...), 0)) DESC
LIMIT 40;
개선 효과
-
240개의 서브쿼리 → 5개의 LEFT JOIN으로 통합
-
DB 옵티마이저의 쿼리 최적화
핵심 포인트 정리
1. N+1 문제 해결 패턴
// - N+1 발생
for (const item of items) {
const related = await RelatedModel.findAll({
where: {
itemId: item.id
}
});
}
// - 한 번에 조회
const itemIds = items.map(item => item.id);
const relatedItems = await RelatedModel.findAll({
where: { itemId: { [Op.in]: itemIds } }
});
// productId별로 그룹핑
const relatedByItemId = {};
relatedItems.forEach(item => {
if (!relatedByItemId[item.itemId]) {
relatedByItemId[item.itemId] = [];
}
relatedByItemId[item.itemId].push(item);
});
2. 서브쿼리 vs JOIN 비교
| 구분 | 서브쿼리 | JOIN |
|---|---|---|
| 실행 방식 | 각 행마다 반복 실행 | 한 번에 처리 |
| 성능 | 느림 (O(N×M)) | 빠름 (O(N+M)) |
| 최적화 | 어려움 | 용이함 |
| 가독성 | 직관적 | 복잡할 수 있음 |
3. Sequelize 주요 옵션
required: false→ LEFT JOINrequired: true→ INNER JOINduplicating: false→ 중복 방지subQuery: false→ 불필요한 서브쿼리 생성 방지attributes: []→ 집계만 사용할 때logging: console.log→ SQL 로깅으로 디버깅
최종 결과
개선 후 - 평균 0.3초 소요
기존 2.5초가 넘던 조회 API의 속도를 0.3초로 약 88% 개선하는 데 성공했습니다.
배운 점
1. 실행되는 SQL을 확인
Sequelize 같은 ORM을 사용하면 실제 어떤 SQL이 실행되는지 모르고 넘어가기 쉽다.
logging: console.log 옵션으로 실제 SQL을 확인하여 쿼리를 점검하는 습관이 중요
2. N+1
학원 다니면서 지독하게 봤었는데 여전히 괴롭히는 N+1 문제
반복문 안에서 쿼리를 실행하는 코드를 발견하면 항상 의심하자
3. 서브쿼리보단 JOIN
서브쿼리는 각 행마다 반복 실행되지만, JOIN은 한 번에 처리된다.
특히 집계 함수를 사용할 때는 JOIN이 훨씬 효율적
4. GROUP BY와 집계 함수의 조합
LEFT JOIN 후 GROUP BY와 COUNT(DISTINCT ...), AVG(...) 같은 집계 함수를 적절히 사용하면 서브쿼리 없이도 원하는 결과를 얻을 수 있다.
5. 최적화는 단계적으로
한 번에 모든 것을 바꾸려 하지 말고, 가장 큰 병목부터 하나씩 해결하면서 측정하는 것이 중요하다.
- 1차: N+1 해결
- 2차: 서브쿼리 제거
6. 한 가지에 매몰되지 않기
이것 외에도 LIKE %keyword% 검색을 최적화해서 인덱스를 탈 수 있지 않을까? 해서 삽질을 오랫동안 했다.
쉬운 길은 돌아가지 말고 그냥 가자.
마무리
요즘 참 정신없이 사는 것 같습니다. 퇴근하고 집에 와서 공부하고 주말에 개인 프로젝트 하고... 바쁘게 살아가고 있습니다.
배우고 싶은 게 많은데 몸이 잘 따라주지 않네요. 무언가는 포기해야 하지만 욕심이 나는 건 어쩔 수 없는 것 같습니다.
사실 회사다니면서 이런 최적화도 해보고 싶었는데 드디어 해보게 됩니다.
그래도 바쁜 프로젝트가 끝나 비교적 여유로울 최적화를 해봐서 운이 좀 좋은 것 같네요.