대용량 쿼리 벌크 업데이트

그동안 잘못 알고 있었던 사실.

DB에 최소한으로 접근하는 것이 항상 좋다.

-> DB에 연결된 트랜잭션의 시간이 길어진다면, 전체적인 데이터베이스의 접근하는 속도가 저하된다.

 

사실 위 의미대로라면 분할로 업데이트하는 청크는 설명될 수 없다.

---

NestJS, MySQL, Typeorm의 예를 들어보겠다.

 

1. 업데이트에 필요한 행만큼 쿼리문 실행

async bulkMap(dto: BulkDto): Promise<{ updatedCount: number }> {
	let updatedCount: number = 0;
    
    await this.entityManager.transaction(async (em) => {
    	for(const item of dto.items){
        	const result = await em..createQueryBuilder()
          		.update(User)
                ...
                .excute();
                
               updatedCount += result.affected || 0;
        }
    });
    
    return { updatedCount };
}

코드는 간단하지만 행이 많아질수록 트랜잭션 시간이 길어져 응답속도가 느려진다.

 

2. DB 한번 연결 후 한방에 실행

1번의 소스코드에서 반복문에 조건절을 배열로 쌓고 최종적으로 DB에 한번에 업데이트하는 쿼리를 상상해보자.

 

• 행이 많아지면 많아질수록 트랜잭션의 시간은 길어진다.
• 락이 걸려있다면 해당 DB에는 다른 커넥션이 접근이 불가능하다.
• SQL 패킷 길이 제한에 걸릴 수 있다.

 

3. 청크로 분할 업데이트

async bulkMap(dto: BulkDto): Promise<{ updatedCount: number }> {
	let updatedCount: number = 0;
    
    await this.entityManager.transaction(async (em) => {
		// 데이터를 보관할 임시 테이블 생성
		await em.query(`
        	CREATE TEMPORARY TABLE tmp_user (
                user_id BIGINT NOT NULL PRIMARY KEY,
                json_obj JSON NOT NULL
            )
      	`);
        
        const chunkSize = 1000;
        for (let i = 0; i < dto.items.length; i += chunkSize) {
            const chunk = dto.items.slice(i, i + chunkSize);
            const values: string[] = [];
            const params: any[] = [];

            for (const item of chunk) {
          		const data = dataMap.get(item.userId);
          		values.push('(?, ?)');
          		params.push(item.userId, JSON.stringify([data]));
            }
      
      		// 임시테이블에 청크로 변경할 데이터 삽입
            await em.query(
                `INSERT INTO tmp_user (user_id, json_obj) VALUES ${values.join(', ')}`,
                params
            );
    	}  
        
        // 유저테이블 업데이트
		const userResult = await em.query(`
            UPDATE user u
            JOIN tmp_user t ON t.user_id = b.id
            SET u.json_obj = t.json_obj
      	`);
        
        // 주문테이블 업데이트
        const purchaseOrderUserResult = await em.query(`
            UPDATE purchase_order_user pou
            JOIN tmp_user t ON t.user_id = pou.user_id
            SET pou.json_obj = t.json_obj
  		`);
        
        // 수행 쿼리수 기록
  		updatedCount += (userResult?.affectedRows || 0) + (purchaseOrderUserResult?.affectedRows || 0);
        
        // 임시 테이블 삭제
  		await em.query('DROP TEMPORARY TABLE IF EXISTS tmp_buyer_sales_manager');
	});
    
    return { updatedCount };
}

• 임시테이블을 만든다
• 변경할 데이터를 임시테이블에 청크 단위로 삽입한다
• 변경할 테이블과 임시테이블을 조인하여 한번에 업데이트한다.
• 임시테이블을 삭제한다.

 

이 방법으로 10만건의 데이터를 업데이트하는데 12초밖에 걸리지 않았다.

 

핵심은 쿼리 수행수를 줄이는 것이었다.

DB접근을 줄이는 것도 좋지만, 수행해야 할 양이 많다면 청크로 진행하되 쿼리 숫자를 줄여보자.