Knex.js를 사용한 대량 업데이트 레코드에 대한 QL 접근 방식

웹 개발 세계에서는 특히 여러 레코드를 한 번에 업데이트하는 등 대량 작업을 처리할 때 데이터베이스를 효율적으로 사용하는 것이 중요합니다. 재고 관리, 사용자 데이터 처리, 거래 처리 등 무엇을 하든 효율적이고 안정적인 방식으로 대량 업데이트를 수행하는 능력은 필수적입니다.

이 가이드에서는 Node.js용 다목적 쿼리 빌더인 Knex.js를 사용하여 레코드를 대량 업데이트하는 세 가지 필수 SQL 기술을 분석합니다. 각 접근 방식은 다양한 시나리오에 맞게 조정되어 특정 사용 사례에 따라 뚜렷한 이점을 제공합니다. 우리가 다룰 내용은 다음과 같습니다.

1. 여러 조건을 사용한 단일 업데이트: 조건부 논리를 사용하여 특정 기준에 따라 다양한 업데이트를 적용하여 단일 쿼리로 여러 레코드를 업데이트할 수 있는 방법입니다.

2. 트랜잭션에서 개별 쿼리를 사용한 일괄 업데이트: 이 접근 방식은 트랜잭션을 활용하여 원자성을 보장하고 여러 업데이트 쿼리를 안전하고 효율적으로 실행합니다.

3. onContribute를 사용한 Upsert(삽입 또는 업데이트): 중복 데이터 위험 없이 새 레코드를 삽입하거나 기존 레코드를 업데이트해야 하는 시나리오에 적합합니다.

다음 섹션에서는 각 방법에 대해 자세히 알아보고 구현, 이점 및 최상의 사용 사례를 검토합니다. 이러한 접근 방식을 이해하면 특정 요구 사항에 가장 적합한 기술을 선택하여 애플리케이션의 성능과 데이터 무결성을 모두 최적화할 수 있습니다.

---

1. 여러 조건을 사용한 단일 업데이트

데이터베이스의 여러 레코드를 업데이트하는 경우 효율성이 중요합니다. 한 가지 강력한 기술은 여러 조건이 포함된 단일 UPDATE 쿼리를 사용하는 것입니다. 이 방법은 단일 SQL 문 내에서 특정 기준에 따라 다양한 레코드에 다양한 업데이트를 적용해야 할 때 특히 유용합니다.

콘셉트:

'여러 조건을 사용한 단일 업데이트' 접근 방식의 핵심 아이디어는 단일 UPDATE 쿼리를 사용하여 여러 행을 수정하고, 각 행은 잠재적으로 고유한 특성에 따라 서로 다른 값을 수신하는 것입니다. 이는 UPDATE 쿼리 내 CASE 문을 사용하여 업데이트해야 하는 각 필드에 대한 조건부 논리를 지정할 수 있도록 함으로써 달성됩니다.

이 접근 방식을 사용하는 이유:

• 효율성: 레코드 수가 중간 정도인 경우(예: 수십에서 수백 개) 여러 업데이트를 단일 쿼리로 통합하면 레코드 수가 줄어들어 성능이 크게 향상될 수 있습니다. 데이터베이스 왕복 횟수. 이는 빈도가 높은 업데이트를 처리할 때 특히 유용할 수 있습니다. 그러나 매우 큰 데이터 세트(수천 개 이상)의 경우 이 접근 방식이 효과적이지 않을 수 있습니다. 이 가이드의 뒷부분에서는 대규모 데이터 세트를 처리하는 대체 방법에 대해 논의합니다.

• 단순성: 단일 쿼리로 업데이트를 관리하는 것이 여러 개의 개별 쿼리를 실행하는 것보다 더 간단하고 유지 관리하기 쉬운 경우가 많습니다. 이 접근 방식을 사용하면 데이터베이스 상호 작용의 복잡성이 줄어들고 특히 적당한 수의 업데이트를 처리할 때 코드를 더 쉽게 이해할 수 있습니다.

• 오버헤드 감소: 쿼리 수가 적다는 것은 데이터베이스에 대한 오버헤드가 줄어든다는 의미이며, 이는 전반적인 성능을 향상시킬 수 있습니다. 이는 네트워크 대기 시간이나 데이터베이스 로드가 작업 속도에 영향을 미칠 수 있는 시나리오에서 특히 중요합니다.
  매우 많은 수의 레코드에 대해 이 가이드에서는 잠재적인 오버헤드를 보다 효과적으로 관리하기 위한 다른 전략을 살펴봅니다.

구현 예:

다음은 인기 있는 Node.js용 SQL 쿼리 빌더인 Knex.js를 사용하여 이 접근 방식을 구현하는 방법에 대한 실제 예입니다. 이 예에서는 조건부 논리를 사용하여 레코드 ID에 따라 다양한 업데이트를 적용하여 여러 레코드에 대한 여러 필드를 한 번에 업데이트하는 방법을 보여줍니다.

const queryHeaderProductUpdate = 'UPDATE products SET '; // Start of the SQL UPDATE query
const updatesProductUpdate = []; // Array to hold the individual update statements
const parametersProductUpdate = []; // Array to hold the parameters for the query

const updateProducts = [
  { product_id: 1, name: 'New Name 1', price: 100, status: 'Active' },
  { product_id: 2, name: 'New Name 2', price: 150, status: 'Inactive' },
  { product_id: 3, name: 'New Name 3', price: 200, status: 'Active' }
];

// Extract the product IDs to use in the WHERE clause
const productIds = updateProducts.map(p => p.product_id);

// Build the update statements for each field
updateProducts.forEach((item) => {
  // Add conditional logic for updating the 'name' field
  updatesProductUpdate.push('name = CASE WHEN product_id = ? THEN ? ELSE name END');
  parametersProductUpdate.push(item.product_id, item.name);

  // Add conditional logic for updating the 'price' field
  updatesProductUpdate.push('price = CASE WHEN product_id = ? THEN ? ELSE price END');
  parametersProductUpdate.push(item.product_id, item.price);

  // Add conditional logic for updating the 'status' field
  updatesProductUpdate.push('status = CASE WHEN product_id = ? THEN ? ELSE status END');
  parametersProductUpdate.push(item.product_id, item.status);

  // Add 'updated_at' field with the current timestamp
  updatesProductUpdate.push('updated_at = ?');
  parametersProductUpdate.push(knex.fn.now());

  // Add 'updated_by' field with the user ID
  updatesProductUpdate.push('updated_by = ?');
  parametersProductUpdate.push(req.user.userId);
});

// Construct the full query by joining the individual update statements and adding the WHERE clause
const queryProductUpdate = `${queryHeaderProductUpdate + updatesProductUpdate.join(', ')} WHERE product_id IN (${productIds.join(', ')})`;

// Execute the update query
await db.raw(queryProductUpdate, parametersProductUpdate);

이 코드의 역할:

1. 쿼리 헤더 구성: 제품 테이블에 대한 UPDATE 문을 시작합니다.

2. 조건부 업데이트 빌드: CASE 문을 사용하여 product_id를 기반으로 각 필드에 다른 업데이트를 지정합니다.

3. 전체 쿼리 생성: 업데이트 문과 WHERE 절을 결합합니다.

4. 쿼리 실행: 생성된 쿼리를 실행하여 지정된 레코드에 업데이트를 적용합니다.

이 기술을 구현하면 조건부 논리를 사용하여 대량 업데이트를 효율적으로 처리할 수 있어 데이터베이스 작업을 더욱 간소화하고 효과적으로 만들 수 있습니다.

Note: In the provided example, we did not use a transaction because the operation involves a single SQL query. Since a single query inherently maintains data integrity and consistency, there's no need for an additional transaction. Adding a transaction would only increase overhead without providing additional benefits in this context.

Having explored the "Single Update with Multiple Conditions" approach, which works well for a moderate number of records and provides simplicity and efficiency, we now turn our attention to a different scenario. As datasets grow larger or when atomicity across multiple operations becomes crucial, managing updates effectively requires a more robust approach.

Batch Updates with Individual Queries in a Transaction is a method designed to address these needs. This approach involves executing multiple update queries within a single transaction, ensuring that all updates are applied atomically. Let's dive into how this method works and its advantages.

---

2. Batch Updates with Individual Queries in a Transaction

When dealing with bulk updates, especially for a large dataset, managing each update individually within a transaction can be a robust and reliable approach. This method ensures that all updates are applied atomically and can handle errors gracefully.

Why Use This Approach:

• Scalability: For larger datasets where Single Update with Multiple Conditions might become inefficient, batch updates with transactions offer better control. Each query is executed separately, and a transaction ensures that all changes are committed together, reducing the risk of partial updates.

• Error Handling: Transactions provide a safety net by ensuring that either all updates succeed or none do. This atomicity guarantees data integrity, making it ideal for scenarios where you need to perform multiple related updates.

• Concurrency Control: Using transactions can help manage concurrent modifications to the same records, preventing conflicts and ensuring consistency.

Code Example

Here’s how you can implement batch updates with individual queries inside a transaction using Knex.js:

const updateRecordsInBatch = async () => {
    // Example data to update
    const dataToUpdate = [
        { id: 1, name: 'Updated Name 1', price: 100 },
        { id: 2, name: 'Updated Name 2', price: 200 },
        { id: 3, name: 'Updated Name 3', price: 300 }
    ];

    // Start a transaction
    const trx = await db.transaction();
    const promises = [];

    try {
        // Iterate over the data and push update queries to the promises array
        dataToUpdate.forEach(record => {
            promises.push(
                trx('products')
                    .update({
                        name: record.name,
                        price: record.price,
                        updated_at: trx.fn.now()
                    })
                    .where('id', record.id)
            );
        });

        // Execute all queries concurrently
        await Promise.all(promises);

        // Commit the transaction
        await trx.commit();
        console.log('All records updated successfully.');
    } catch (error) {
        // Rollback the transaction in case of error
        await trx.rollback();
        console.error('Update failed:', error);
    }
};

Explanation

1. Transaction Initialization: The transaction is started using db.transaction(), which ensures that all subsequent queries are executed within this transaction.

2. Batch Updates: Each update query is constructed and added to an array of promises. This method allows for multiple updates to be performed concurrently.

3. Executing Queries: Promise.all(promises) is used to execute all update queries concurrently. This approach ensures that all updates are sent to the database in parallel.

4. Committing or Rolling Back: If all queries succeed, the transaction is committed with trx.commit(). If any query fails, the transaction is rolled back with trx.rollback(), ensuring that no partial updates are applied.

Using batch updates with individual queries inside a transaction provides a reliable way to manage large datasets. It ensures data integrity through atomic transactions and offers better control over concurrent operations. This method is especially useful when Single Update with Multiple Conditions may not be efficient for very large datasets.

---

3. Upsert (Insert or Update) Using onConflict

When you're working with data that might need to be inserted or updated depending on its existence in the database, an "upsert" operation is the ideal solution. This approach allows you to handle both scenarios—insert new records or update existing ones—in a single, streamlined operation. It's particularly useful when you want to maintain data consistency without having to write separate logic for checking whether a record exists.

Why Use This Approach:

• Simplicity: An upsert enables you to combine the insert and update operations into a single query, simplifying your code and reducing the need for additional checks.

• Efficiency: This method is more efficient than performing separate insert and update operations, as it minimizes database round-trips and handles conflicts automatically.

• Conflict Handling: The onConflict clause lets you specify how to handle conflicts, such as when records with unique constraints already exist, by updating the relevant fields.
  

const productData = [
  {
    product_id: 1,
    store_id: 101,
    product_name: 'Product A',
    price: 10.99,
    category: 'Electronics',
  },
  {
    product_id: 2,
    store_id: 102,
    product_name: 'Product B',
    price: 12.99,
    category: 'Books',
  },
  {
    product_id: 3,
    store_id: 103,
    product_name: 'Product C',
    price: 9.99,
    category: 'Home',
  },
  {
    product_id: 4,
    store_id: 104,
    product_name: 'Product D',
    price: 15.49,
    category: 'Garden',
  },
];

await knex('products')
  .insert(productData)
  .onConflict(['product_id', 'store_id'])
  .merge({
    product_name: knex.raw('EXCLUDED.product_name'),
    price: knex.raw('EXCLUDED.price'),
    category: knex.raw('EXCLUDED.category'),
  });

Explanation

1. 데이터 정의: 삽입하거나 업데이트하려는 제품 기록을 나타내는 개체 배열인 productData를 정의합니다. 각 개체에는 product_id, store_id, product_name, 가격 및 카테고리가 포함되어 있습니다.

2. 삽입 또는 업데이트: knex('products').insert(productData) 함수는 productData 배열의 각 레코드를 제품 테이블에 삽입하려고 시도합니다.

3. 충돌 처리: onCon conflict(['product_id', 'store_id']) 절은 product_id와 store_id의 조합에서 충돌이 발생하는 경우 다음 단계를 실행해야 함을 지정합니다.

4. 병합(충돌 시 업데이트): 충돌이 감지되면 merge({...}) 메서드는 productData의 새 product_name, 가격 및 카테고리 값으로 기존 레코드를 업데이트합니다. knex.raw('EXCLUDED.column_name') 구문은 삽입되었을 값을 참조하는 데 사용되며, 이를 통해 데이터베이스는 이러한 값으로 기존 레코드를 업데이트할 수 있습니다.

onConfluence 절이 upsert 작업에서 올바르게 작동하려면 관련 열이 고유 제약 조건의 일부여야 합니다. 작동 방식은 다음과 같습니다.

• 단일 고유 열: onConfluence 절에서 단일 열을 사용하는 경우 해당 열은 테이블 전체에서 고유해야 합니다. 이러한 고유성은 데이터베이스가 이 열을 기반으로 레코드가 이미 존재하는지 여부를 정확하게 감지할 수 있도록 보장합니다.
• 다중 열: onConfluence 절에 여러 열이 사용되는 경우 이러한 열의 조합은 고유해야 합니다. 이러한 고유성은 이러한 열의 결합된 값이 테이블 전체에서 고유하도록 보장하는 고유 인덱스 또는 제약 조건에 의해 적용됩니다.

색인 및 제약 조건:
인덱스: 하나 이상의 열에 대한 고유 인덱스를 사용하면 데이터베이스에서 값의 고유성을 효율적으로 확인할 수 있습니다. 고유 인덱스를 정의하면 데이터베이스는 이를 사용하여 지정된 열의 값이 이미 존재하는지 빠르게 확인합니다. 이를 통해 onConfluence 절이 충돌을 정확하게 감지하고 처리할 수 있습니다.

제약조건: 고유 제약조건은 하나 이상의 열에 있는 값이 고유해야 함을 보장합니다. 이 제약 조건은 중복 값을 방지하는 규칙을 적용하고 데이터베이스가 이러한 열을 기반으로 충돌을 감지할 수 있도록 허용하므로 onConfluence 절이 작동하는 데 매우 중요합니다.

여러 조건을 사용한 단일 업데이트 접근 방식과 유사하게 upsert 작업에는 트랜잭션이 필요하지 않습니다. 레코드를 삽입하거나 업데이트하는 단일 쿼리가 포함되므로 트랜잭션 관리에 따른 추가 오버헤드 없이 효율적으로 작동합니다.

---

결론

각 기술은 코드 단순화 및 데이터베이스 상호 작용 감소부터 데이터 무결성 보장 및 충돌 효율적 처리에 이르기까지 뚜렷한 이점을 제공합니다. 사용 사례에 가장 적합한 방법을 선택하면 애플리케이션에서 보다 효율적이고 안정적인 업데이트를 얻을 수 있습니다.

이러한 접근 방식을 이해하면 데이터베이스 운영을 특정 요구 사항에 맞게 조정하여 성능과 유지 관리성을 모두 향상시킬 수 있습니다. 대량 업데이트를 처리하든 복잡한 데이터 관리 작업을 처리하든, 워크플로를 최적화하고 개발 프로젝트에서 더 나은 결과를 얻으려면 올바른 전략을 선택하는 것이 중요합니다.

위 내용은 Knex.js를 사용한 대량 업데이트 레코드에 대한 QL 접근 방식의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!