如何优化CSV导入后的去重插入查询，避免执行超时？

Alright, let's break down how to fix this efficiently. The key here is to minimize PHP's involvement in data handling (since loops over large datasets are slow and prone to timeouts) and leverage the database's native bulk processing capabilities. Here are actionable steps:

1. Let the Database Handle Deduplication & Direct Inserts #

Instead of pulling 2000-row chunks into PHP with GROUP BY, use a single INSERT ... SELECT statement to push deduplicated data straight into the products table. This cuts out the PHP middleman entirely, which is way faster for large datasets.

Example SQL:

INSERT INTO products (sku, name, price, category_id, created_at)
SELECT DISTINCT sku, name, price, category_id, NOW()
FROM temp_product_table
-- Add a unique constraint on sku (or your unique identifier) to handle duplicates
ON DUPLICATE KEY UPDATE updated_at = NOW(), price = VALUES(price);

Pro tip: Make sure you have a unique index on the column(s) that define a duplicate product (like sku)—this makes the DISTINCT check and ON DUPLICATE KEY logic efficient.

2. Bulk Insert Child Tables with Joins #

Once the products table is populated, don't loop through each product in PHP to insert into product_images or product_description. Use JOIN operations to map temp table data to the new product IDs in one go:

For product_images:

INSERT INTO product_images (product_id, image_url, is_main)
SELECT p.id, t.image_url, t.is_main
FROM temp_product_table t
INNER JOIN products p ON t.sku = p.sku
ON DUPLICATE KEY UPDATE image_url = VALUES(image_url);

For product_description:

INSERT INTO product_description (product_id, description, language_code)
SELECT p.id, t.description, t.language_code
FROM temp_product_table t
INNER JOIN products p ON t.sku = p.sku
ON DUPLICATE KEY UPDATE description = VALUES(description);

This way, all child table inserts are handled in bulk by the database, no PHP loops required.

3. If You Must Use PHP: Optimize Chunking & Pagination #

If you need PHP for custom data transformation (e.g., formatting descriptions, resizing image URLs), avoid slow OFFSET pagination and use key-based pagination instead. This prevents the database from scanning thousands of rows just to skip to the next chunk.

Example PHP logic:

// Initialize with the smallest possible sku (or your unique key)
$lastProcessedSku = '';
$chunkSize = 10000; // Adjust based on your server's memory

while (true) {
    // Fetch next chunk using key-based pagination (fast, even for large datasets)
    $data = $this->db->query("
        SELECT DISTINCT * FROM temp_product_table
        WHERE sku > ?
        ORDER BY sku
        LIMIT ?
    ", [$lastProcessedSku, $chunkSize])->result_array();

    if (empty($data)) break;

    // Wrap each chunk in a transaction to reduce commit overhead
    $this->db->trans_start();

    $productIds = [];
    $imageBatch = [];
    $descBatch = [];

    foreach ($data as $item) {
        // Insert product (or use insert_batch for even more speed)
        $productId = $this->db->insert('products', [
            'sku' => $item['sku'],
            'name' => $item['name'],
            // ... other fields
        ]);
        $productIds[$item['sku']] = $productId;

        // Collect child data for bulk insertion
        $imageBatch[] = [
            'product_id' => $productId,
            'image_url' => $item['image_url'],
            'is_main' => $item['is_main']
        ];

        $descBatch[] = [
            'product_id' => $productId,
            'description' => $item['description']
        ];
    }

    // Bulk insert child tables (way faster than individual inserts)
    $this->db->insert_batch('product_images', $imageBatch);
    $this->db->insert_batch('product_description', $descBatch);

    $this->db->trans_complete();

    // Update last processed sku for next iteration
    $lastProcessedSku = end($data)['sku'];
}

Key improvements: Key-based pagination (WHERE sku > ?), larger chunk sizes, transaction wrapping, and bulk inserts for child tables.

4. Optimize the Temp Table First #

Before processing, add indexes to your temp table to speed up DISTINCT, JOIN, and WHERE operations:

ALTER TABLE temp_product_table ADD INDEX idx_sku (sku);
-- Add indexes on other columns used in joins or filters, e.g., category_id

If you're using InnoDB, you can also tweak server settings like innodb_buffer_pool_size (if you have access) to give the database more memory for bulk operations.

5. Last Resort: Adjust PHP Execution Time #

If you've optimized everything else and still hit timeouts, you can temporarily increase the execution time (but this is a band-aid, not a fix):

// Set to 5 minutes (300 seconds) - adjust as needed
ini_set('max_execution_time', 300);
// For CLI scripts, use set_time_limit instead
set_time_limit(300);

内容的提问来源于stack exchange，提问作者Rojan