[Node.js] Prisma 한방 쿼리로 성능 개선

현재 회사에서는 NestJS와 MySQL 그리고 Prisma라는 ORM의 조합으로 백엔드 개발을 하고 있습니다.

 

개발을 진행하면서 늘 불편하다고 느껴왔던 부분에 대한 개선이 필요하다고 판단하여 진행하게 되었습니다.

 

불편했던 점을 어느정도 개선할 수 있는 가이드라인이 되었으면 하는 바램에서 이 글을 작성해봅니다.

 

내가 마주했던 문제점

기능을 추가하게 되면서 table을 하나 더 Join하게 되었고 그 과정에서 기존에는 평균 100ms의 응답속도를 가지던 API가 최소 6배, 최대 10배 정도 느려지는 현상을 발견하였습니다. 다음 사진은 Thunder Client를 사용하여 테스트한 결과입니다.

 

보통 API의 응답 속도가 느려지는 원인은 여러가지가 있지만 이 경우는 한번에 가져올 수 있는 쿼리를 여러번에 걸쳐서 나눠서 가져오게 되면서 문제가 발생하였습니다. 다음 코드는 실제 로그에 기록되었던 쿼리입니다.

# 1번 쿼리
SELECT
	`earlivery_test`.`device`.`id`,
	`earlivery_test`.`device`.`name`,
	`earlivery_test`.`device`.`device_number`,
	`earlivery_test`.`device`.`description`,
	`earlivery_test`.`device`.`order_weight`,
	`earlivery_test`.`device`.`container_max_weight`,
	`earlivery_test`.`device`.`created_at`,
	`earlivery_test`.`device`.`deleted_at`,
	`earlivery_test`.`device`.`container_id`,
	`earlivery_test`.`device`.`item_id`,
	`earlivery_test`.`device`.`member_id`,
	`earlivery_test`.`device`.`location_id`,
	`earlivery_test`.`device`.`address_id`
FROM `earlivery_test`.`device`
LEFT JOIN `earlivery_test`.`location` AS `j1`
ON (`j1`.`id`) = (`earlivery_test`.`device`.`location_id`)
WHERE (
	`earlivery_test`.`device`.`deleted_at` IS NULL
	AND `earlivery_test`.`device`.`member_id` = 4
	AND (
		1=1 AND (
			`j1`.`id` IS NOT NULL
		)
	)
)
ORDER BY `earlivery_test`.`device`.`id` ASC
LIMIT 10 OFFSET 0;

# 2번 쿼리
SELECT
	`earlivery_test`.`device_data`.`id`,
	`earlivery_test`.`device_data`.`weight`,
	`earlivery_test`.`device_data`.`battery`,
	`earlivery_test`.`device_data`.`interval`,
	`earlivery_test`.`device_data`.`sn`,
	`earlivery_test`.`device_data`.`created_at`,
	`earlivery_test`.`device_data`.`device_number`
FROM `earlivery_test`.`device_data`
WHERE `earlivery_test`.`device_data`.`device_number` IN (999,56,77,1,2,3,4,5,6,7)
ORDER BY `earlivery_test`.`device_data`.`created_at` DESC;

# 3번 쿼리
SELECT
	`earlivery_test`.`device_item`.`id`,
	`earlivery_test`.`device_item`.`name`,
	`earlivery_test`.`device_item`.`code`,
	`earlivery_test`.`device_item`.`unit_weight`,
	`earlivery_test`.`device_item`.`alarm_weight`,
	`earlivery_test`.`device_item`.`max_weight`,
	`earlivery_test`.`device_item`.`order_weight`,
	`earlivery_test`.`device_item`.`image_url`,
	`earlivery_test`.`device_item`.`description`,
	`earlivery_test`.`device_item`.`category`,
	`earlivery_test`.`device_item`.`created_at`,
	`earlivery_test`.`device_item`.`updated_at`,
	`earlivery_test`.`device_item`.`deleted_at`,
	`earlivery_test`.`device_item`.`member_id`
FROM `earlivery_test`.`device_item`
WHERE `earlivery_test`.`device_item`.`id` IN (16,13,14,26,27,28,29,30,31,32);

# 4번 쿼리
SELECT
	`earlivery_test`.`device_address`.`id`,
	`earlivery_test`.`device_address`.`name`,
	`earlivery_test`.`device_address`.`detail_name`
FROM `earlivery_test`.`device_address`
WHERE `earlivery_test`.`device_address`.`id` IN (1);

 

기존과 비교하게 되었을 때 상당히 느려진 것을 확인할 수 있었고 다른 부분에서도 join이 많아지게 된다면 마찬가지로 문제가 발생할 수 있는 부분이였습니다. 그에 따라 최적화가 필요하다고 판단하여 최적화하는 방법을 찾게 되었습니다.

 

Prisma가 가지고 있던 문제점

Prisma에서는 Join을 지원하지 않았습니다... 따라서 Join이 여러번 발생하게 된다면 성능 상 이유로 Raw Query를 사용해야 했습니다.

 

이는 Prisma라는 ORM을 사용하면서 마주하는 굉장히 큰 약점이 된다고 생각하였습니다.

 

다른 곳에서도 마찬가지로 이 부분에 대한 아쉬움을 많이 토로하였습니다.

 

https://skkuding.dev/post/prisma-join/

The Curious Case of Prisma

 

https://github.com/prisma/prisma/issues/5184

Use `JOIN`s in more queries · Issue #5184 · prisma/prisma

 

따라서 이 부분을 순응하고 Raw Query로 기능을 구현하려고 하였습니다. 하지만 정말 이 방법뿐일까에 대한 고민이 계속 들었습니다.

 

답답한 마음에 공식문서를 뒤지다.

혹시나 하는 마음으로 공식문서를 뒤지게 되었습니다. (오늘 하루 최고의 선택!)

 

그 결과... 다음과 같은 부분을 마주하게 되었습니다.

 

https://www.prisma.io/docs/orm/prisma-client/queries/relation-queries#relation-load-strategies-preview

Relation queries (Concepts) | Prisma Documentation

 

네 놀랍게도 이 부분에 대한 문제가 해결되었다는 것을 확인할 수 있었습니다. 그 내용은 다음과 같았습니다.

 

버전 5.8.0부터 PostgreSQL 데이터베이스의 relationLoadStrategy 옵션을 통해 Prisma 클라이언트가 관계 쿼리를 실행하는 방법(즉, 어떤 로드 전략을 적용해야 하는지)을 쿼리 수준별로 결정할 수 있습니다.

 

버전 5.10.0 부터 이 기능을 MySQL에서도 사용할 수 있습니다.

 

relationLoadStrategy 옵션은 Prisma 스키마 파일에서 relationJoins 플래그를 통해 활성화해야 합니다.

generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["relationJoins"]
}

 

이 플래그를 추가한 후 Prisma Client를 다시 생성하려면 prisma generate를 다시 실행해야 합니다.

 

이 기능은 현재 PostgreSQL, CockroachDB 및 MySQL에서 사용할 수 있습니다.

 

프리즈마 클라이언트는 관계에 대한 두 가지 로드 전략을 지원합니다:

 

join(default): 데이터베이스 수준의 LATERAL JOIN(PostgreSQL) 또는 상관된 SubQuery(MySQL)를 사용하여 단일 쿼리로 모든 데이터를 데이터베이스로 가져옵니다.

query: 여러 쿼리를 데이터베이스에 전송하고(테이블당 하나씩) 애플리케이션 수준에서 조인합니다.

 

즉, Prisma Client가 반환한 JSON을 이미 데이터베이스에 생성하여 애플리케이션 수준에서 계산 리소스를 절약할 수 있습니다.

 

참고: relationLoadStrategy가 정식 버전으로 전환되면 모든 관계 쿼리에 대해 조인이 보편적으로 기본값이 됩니다.

 

include 또는 select를 지원하는 모든 쿼리에서 최상위 레벨의 relationLoadStrategy 옵션을 사용할 수 있습니다.

const users = await prisma.user.findMany({
  relationLoadStrategy: 'join', // or 'query'
  include: {
    posts: true,
  },
});

// OR

const users = await prisma.user.findMany({
  relationLoadStrategy: 'join', // or 'query'
  select: {
    posts: true,
  },
})

 

대부분의 시나리오에서는 join strategy(default)가 더 효과적입니다.

 

PostgreSQL에서는 결과 집합의 중복을 줄이고 쿼리 결과를 데이터베이스 서버에서 예상되는 JSON 구조로 변환하는 작업을 위임하기 위해 LATERAL JOIN과 JSON 집계를 조합하여 사용합니다.

 

MySQL에서는 상관관계가 있는 SubQuery를 사용하여 단일 쿼리로 결과를 가져옵니다.

 

데이터 세트와 쿼리의 특성에 따라 query의 성능이 더 우수할 수 있는 edge 케이스가 있을 수 있습니다.

 

이러한 상황을 파악하기 위해 데이터베이스 쿼리를 프로파일링하는 것이 좋습니다.

 

데이터베이스 서버의 리소스를 절약하고 확장하기 쉬운 애플리케이션 서버에서 데이터 병합 및 변환과 같은 무거운 작업을 수행하려면 query를 사용하세요.

 

더 많은 내용의 5.10.0 패치 내용을 확인하려면 다음 링크를 확인하세요.

 

https://github.com/prisma/prisma/releases/tag/5.10.0

Release 5.10.0 · prisma/prisma

 

직접 개선해보기

이렇게 해당 기능에 대한 개선점을 찾을 수 있었습니다. 따라서 이 방법에 맞춰서 기능을 개선하려고 했습니다.

 

공식문서대로 Prisma Schema 파일을 다음과 같이 수정하였습니다.

generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["relationJoins"]
}

 

Prisma Client를 다시 생성하기 위해 prisma generate를 실행하였습니다.

prisma generate

 

그 다음 문제가 발생했던 부분에서 다음 옵션을 추가하였습니다.

relationLoadStrategy: "join",

 

이렇게 되면 전부 개선을 완료하였습니다. 성능을 비교해보도록 하겠습니다.

 

성능 비교

개선을 하고 난 후 최소 82%, 최대 90%의 성능이 향상되었습니다. 다음 사진은 Thunder Client를 사용하여 테스트한 결과입니다.

 

다음 코드는 실제 로그에 기록되었던 쿼리입니다. 쿼리가 굉장히 긴 모습입니다.

SELECT
	`t1`.`id`,
	`t1`.`name`,
	`t1`.`device_number` AS `deviceNumber`,
	`t1`.`description`,
	`t1`.`order_weight` AS `orderWeight`,
	`t1`.`container_max_weight` AS `containerMaxWeight`,
	`t1`.`created_at` AS `createdAt`,
	`t1`.`deleted_at` AS `deletedAt`,
	`t1`.`container_id` AS `containerId`,
	`t1`.`item_id` AS `itemId`,
	`t1`.`member_id` AS `memberId`,
	`t1`.`location_id` AS `locationId`,
	`t1`.`address_id` AS `addressId`,
	(
		SELECT
			COALESCE(
				JSON_ARRAYAGG(`__prisma_data__`),
				CONVERT('[]', JSON)
			) AS `__prisma_data__`
		FROM (
			SELECT `t4`.`__prisma_data__`
			FROM (
				SELECT
					JSON_OBJECT(
						'id',
						`t3`.`id`,
						'weight',
						`t3`.`weight`,
						'battery',
						`t3`.`battery`,
						'interval',
						`t3`.`interval`,
						'sn',
						`t3`.`sn`,
						'createdAt',
						`t3`.`created_at`,
						'deviceNumber',
						`t3`.`device_number`
					) AS `__prisma_data__`,
					`t3`.`created_at`
				FROM (
					SELECT `t2`.*
					FROM `earlivery_test`.`device_data` AS `t2`
					WHERE `t1`.`device_number` = `t2`.`device_number` /* root select */
				) AS `t3` /* inner select */
			) AS `t4`
			ORDER BY `t4`.`created_at` DESC
			LIMIT 1 /* middle select */
		) AS `t5` /* outer select */
	) AS `data`,
	(
		SELECT
			JSON_OBJECT(
				'id',
				`t6`.`id`,
				'name',
				`t6`.`name`,
				'code',
				`t6`.`code`,
				'unitWeight',
				`t6`.`unit_weight`,
				'alarmWeight',
				`t6`.`alarm_weight`,
				'maxWeight',
				`t6`.`max_weight`,
				'orderWeight',
				`t6`.`order_weight`,
				'imageUrl',
				`t6`.`image_url`,
				'description',
				`t6`.`description`,
				'category',
				`t6`.`category`,
				'createdAt',
				`t6`.`created_at`,
				'updatedAt',
				`t6`.`updated_at`,
				'deletedAt',
				`t6`.`deleted_at`,
				'memberId',
				`t6`.`member_id`
			)
		FROM `earlivery_test`.`device_item` AS `t6`
		WHERE `t1`.`item_id` = `t6`.`id`
		LIMIT 1
	) AS `item`,
	(
		SELECT
			JSON_OBJECT(
				'id',
				`t7`.`id`,
				'name',
				`t7`.`name`,
				'detailName',
				`t7`.`detail_name`
			)
		FROM `earlivery_test`.`device_address` AS `t7`
		WHERE `t1`.`address_id` = `t7`.`id`
		LIMIT 1
	) AS `address`
FROM `earlivery_test`.`device` AS `t1`
LEFT JOIN `earlivery_test`.`location` AS `j1`
ON (`j1`.`id`) = (`t1`.`location_id`)
WHERE (
	`t1`.`deleted_at` IS NULL
	AND `t1`.`member_id` = 4
	AND (
		1=1
		AND (
			`j1`.`id` IS NOT NULL
		)
	)
)
ORDER BY `t1`.`id` ASC
LIMIT 10 OFFSET 0;

 

이 SQL을 해석하기 위해서는 다음 함수들의 뜻을 알아야 합니다. 설명드리도록 하겠습니다.

 

CONVERT(): 데이터를 다른 데이터 타입으로 변환 (여기서는 문자열 '[]'을 JSON 형식으로 변환)

 

JSON_OBJECT(): 키-값 쌍을 받아 JSON 객체를 생성 (여기서는 여러 컬럼 값을 각각의 키-값 쌍으로 변환하여 JSON 객체를 생성)

 

JSON_ARRAYAGG(): 여러 값을 JSON 배열로 변환 (여기서는 __prisma_data__ 컬럼의 여러 값을 JSON 배열로 변환)

 

COALESCE(): 주어진 인자들 중에서 첫 번째로 NULL이 아닌 값을 반환

(여기서는 JSON_ARRAYAGG('__prisma_data__')의 결과가 NULL인 경우 빈 배열 ([])을 반환합니다.)

 

상단에서 설명한 대로 JSON 형태로 Join해서 가져오는 모습으로 보입니다.

 

느낀 점

1. Prisma의 고질적인 문제였던 Join 문제를 어느정도 해결할 수 있었다.

 

2. RDB의 Join과는 조금 다른 느낌으로 Join이 이루어진다.

SubQuery를 통해 Join을 하지않고 JSON으로 변환해서 Join하는 거 처럼 보이게 한다.

JSON으로 변환하지 않았다면 더 빠를 것으로 보인다.

 

3. 문제라고 생각되는 부분을 끝까지 파고 들 수 있던 순간이였다.

 

4. ChatGPT에만 너무 의존하지 않고 공식 문서를 한번 더 확인해보는 좋은 기회였다.