sequelize에 있는 subquery 옵션에 대한 글입니다.
Subquery
model.d.ts 파일에 있는 내용
Use sub queries. This should only be used if you know for sure the query does not result in a cartesian product.
이 말의 의미는 서브쿼리를 사용할 때는 해당 쿼리가 카르테시안 곱을 발생시키지 않을 것이라는 것을 확실하게 알고 있어야 한다. 라고 할 수 있다.
서브쿼리(Sub-queries)
서브쿼리란 SQL 쿼리 안에 또 다른 쿼리를 중첩하는 것을 의미한다. 이 방식은 복잡한 데이터 추출이 필요할 때 유용하지만, 무분별하게 사용하면 성능 문제를 야기할 수 있다.
서브쿼리 예시
서브쿼리는 WHERE, FROM 및 SELECT 절을 포함하여 SQL 문의 다양한 부분에서 사용될 수 있다. 다음은 'WHERE' 절에 하위 쿼리를 사용하여 평균 급여보다 많은 급여를 받는 직원을 찾는 예
SELECT first_name, salary
FROM employees
WHERE salary > (
SELECT AVG(salary)
FROM employees
);카르테시안 곱(Cartesian Product)
카르테시안 곱은 두 개 이상의 테이블에서 가능한 모든 행의 조합을 생성한다. 카르테시안 곱이 발생하면, 반환되는 결과 세트의 크기가 상당히 커질 수 있으며 이는 성능에 큰 영향을 미칠 수 있다.
카르테시안 곱 예시
조건 없이 두 테이블을 조인하면 데카르트 곱이 생성된다. 예를 들어 제품과 고객이라는 두 개의 테이블이 있다고 가정한다. 이와 같은 조인 조건이 없는 쿼리는 데카르트 곱을 생성한다.
SELECT *
FROM products, customers;여기서 products 테이블의 각 행은 customers 테이블의 각 행과 결합된다. products에 10개의 행이 있고 customers에 20개의 행이 있는 경우 결과 집합에는 10 x 20 = 200개의 행이 포함된다.
Sequelize에서의 subQuery 옵션
subQuery 예시
subQuery: true(default)
SELECT ... FROM (
SELECT ... FROM "mainTable"
JOIN "associatedTable" ON ...
WHERE ...
GROUP BY ...
ORDER BY ...
) AS "sub"
LIMIT ...subQuery: false
SELECT ... FROM "mainTable"
JOIN "associatedTable" ON ...
WHERE ...
GROUP BY ...
ORDER BY ...
LIMIT ...subQuery: false 옵션은 쿼리를 효과적으로 평면화하고 특정 사례, 데이터베이스 및 실제 쿼리 복잡성에 따라 더 높은 성능의 쿼리를 생성할 수 있다.
그러나 subQuery: false를 사용하면 특히 관련 테이블의 열을 기준으로 정렬하거나 필터링하는 경우 부작용이 발생할 수 있다. 집계를 사용할 때 결과에도 영향을 미칠 수 있다. 이는 생성된 특정 SQL과 쿼리 논리에 미치는 영향을 이해하면서 주의해서 사용해야 하는 옵션이다.
subQuery - limit
subQuery 옵션과 limit를 같이 쓰면 어떠한 일이 발생할까?
subQuery: true(default)
코드 예시
const testOne = await TestOne.findAll({
attributes: ['id', 'name'],
include: [
{
model: TestTwo,
as: 'two',
attributes: ['id', 'name'],
include: [
{
model: TestThree,
as: 'three',
attributes: ['id', 'name'],
},
],
},
],
limit: 1,
});위에 코드를 sql로 확인해보면 다음과 같다.
SELECT "TestOne".*,
"two"."id" AS "two.id",
"two"."name" AS "two.name",
"two->three"."id" AS "two.three.id",
"two->three"."name" AS "two.three.name"
FROM (SELECT "TestOne"."id",
"TestOne"."name"
FROM "TestOnes" AS "TestOne" LIMIT 1
) AS "TestOne"
LEFT OUTER JOIN "TestTwos" AS "two" ON "TestOne"."id" = "two"."oneId"
LEFT OUTER JOIN "TestThrees" AS "two->three" ON "two"."id" = "two->three"."twoId";결과는 다음과 같다.
[
{
"id": 1,
"name": "1번 테이블 이름 1",
"two": [
{
"id": 1,
"name": "2번 테이블 이름 1",
"three": [
{
"id": 1,
"name": "3번 테이블 이름 1"
},
{
"id": 2,
"name": "3번 테이블 이름 2"
}
]
},
{
"id": 2,
"name": "2번 테이블 이름 2",
"three": [
{
"id": 3,
"name": "3번 테이블 이름 3"
},
{
"id": 4,
"name": "3번 테이블 이름 4"
}
]
}
]
}
]subQuery: false
코드 예시
const testOne = await TestOne.findAll({
attributes: ['id', 'name'],
include: [
{
model: TestTwo,
as: 'two',
attributes: ['id', 'name'],
include: [
{
model: TestThree,
as: 'three',
attributes: ['id', 'name'],
},
],
},
],
limit: 1,
subQuery: false, // 추가
});위에 코드를 sql로 확인해보면 다음과 같다.
SELECT "TestOne"."id",
"TestOne"."name",
"two"."id" AS "two.id",
"two"."name" AS "two.name",
"two->three"."id" AS "two.three.id",
"two->three"."name" AS "two.three.name"
FROM "TestOnes" AS "TestOne"
LEFT OUTER JOIN "TestTwos" AS "two" ON "TestOne"."id" = "two"."oneId"
LEFT OUTER JOIN "TestThrees" AS "two->three" ON "two"."id" = "two->three"."twoId"
LIMIT 1;결과는 다음과 같다.
[
{
"id": 1,
"name": "1번 테이블 이름 1",
"two": [
{
"id": 1,
"name": "2번 테이블 이름 1",
"three": [
{
"id": 1,
"name": "3번 테이블 이름 1"
}
]
}
]
}
]비교
subQuery 옵션이 true 일 때랑 false 일 때 sql만 다시 보자
true
SELECT "TestOne".*,
"two"."id" AS "two.id",
"two"."name" AS "two.name",
"two->three"."id" AS "two.three.id",
"two->three"."name" AS "two.three.name"
FROM (SELECT "TestOne"."id",
"TestOne"."name"
FROM "TestOnes" AS "TestOne" LIMIT 1
) AS "TestOne"
LEFT OUTER JOIN "TestTwos" AS "two" ON "TestOne"."id" = "two"."oneId"
LEFT OUTER JOIN "TestThrees" AS "two->three" ON "two"."id" = "two->three"."twoId";위에 쿼리는 먼저 "TestOne"에서 하나의 행을 선택한 다음 "TestTwo" 및 "TestThree"와 조인을 수행한다. 이를 통해 "TestTwo" 및 "TestThree"의 여러 행을 "TestOne"의 단일 행과 결합할 수 있으므로 보다 "확장된" 결과 집합을 얻을 수 있다.
false
SELECT "TestOne"."id",
"TestOne"."name",
"two"."id" AS "two.id",
"two"."name" AS "two.name",
"two->three"."id" AS "two.three.id",
"two->three"."name" AS "two.three.name"
FROM "TestOnes" AS "TestOne"
LEFT OUTER JOIN "TestTwos" AS "two" ON "TestOne"."id" = "two"."oneId"
LEFT OUTER JOIN "TestThrees" AS "two->three" ON "two"."id" = "two->three"."twoId"
LIMIT 1;위에 쿼리는 모든 조인을 먼저 수행한 다음 전체 결과 집합을 단 하나의 행으로 제한한다. 이렇게 하면 자연스럽게 "TestTwo" 및 "TestThree"의 관련 행이 제한되어 결과 집합이 더욱 "압축"된다.
첫 번째 쿼리의 'LIMIT'는 "TestOnes" 테이블에만 영향을 미친다. 두 번째 쿼리에서는 'LIMIT'가 조인된 결과 전체에 적용된다.
separate 옵션
Sequelize에서 separate 옵션은 hasMany 또는 belongsToMany 연관이 있고 하나의 큰 조인을 수행하는 대신 연관된 각 모델에 대해 별도의 SQL 쿼리를 실행하려는 경우에 특히 유용하다.
이는 종종 성능상의 이유로 유용하며 여러 일대다 관계 조인에서 발생할 수 있는 데카르트 곱 문제를 방지하는 데 도움이 될 수 있다.separate: true를 설정하면 Sequelize는 기본 모델과 각 관련 모델에 대해 별도의 SQL 쿼리를 실행한다.
코드 예시
const testOne = await TestOne.findAll({
attributes: ['id', 'name'],
include: [
{
model: TestTwo,
as: 'two',
attributes: ['id', 'name'],
include: [
{
model: TestThree,
as: 'three',
attributes: ['id', 'name'],
},
],
separate: true, // 추가
},
],
limit: 1,
subQuery: false,
});위에 코드를 sql로 확인해보면 다음과 같다.
SELECT "TestOne"."id",
"TestOne"."name"
FROM "TestOnes" AS "TestOne"
LIMIT 1;
SELECT "TestTwo"."id",
"TestTwo"."name",
"TestTwo"."oneId",
"three"."id" AS "three.id",
"three"."name" AS "three.name"
FROM "TestTwos" AS "TestTwo"
LEFT OUTER JOIN "TestThrees" AS "three" ON "TestTwo"."id" = "three"."twoId"
WHERE "TestTwo"."oneId" IN (1);결과는 다음과 같다.
[
{
"id": 1,
"name": "1번 테이블 이름 1",
"two": [
{
"id": 1,
"name": "2번 테이블 이름 1",
"three": [
{
"id": 1,
"name": "3번 테이블 이름 1"
},
{
"id": 2,
"name": "3번 테이블 이름 2"
}
]
},
{
"id": 2,
"name": "2번 테이블 이름 2",
"three": [
{
"id": 3,
"name": "3번 테이블 이름 3"
},
{
"id": 4,
"name": "3번 테이블 이름 4"
}
]
}
]
}
]결과가 아래와 동일함을 알 수 있다.
const testOne = await TestOne.findAll({
attributes: ['id', 'name'],
include: [
{
model: TestTwo,
as: 'two',
attributes: ['id', 'name'],
include: [
{
model: TestThree,
as: 'three',
attributes: ['id', 'name'],
},
],
},
],
limit: 1,
});Sequelize에서 separate: true를 사용하면 관련 테이블에서 데이터를 가져오기 위해 별도의 쿼리를 실행하도록 Sequelize에 효과적으로 지시할 수 있다. 먼저 기본 테이블(이 경우 TestOnes)에 대한 쿼리를 실행한 다음 관련 테이블(TestTwos 및 TestThrees)에 대한 추가 쿼리를 실행하고 외래 키(oneId 및 twoId)를 기반으로 기본 테이블에 다시 연결한다.
