如何优化相似产品关联数据的数据库存储方式？

First off, let's start with the big question: should you merge all records for the same product_id into a single row with comma-separated values? The short answer is almost always no—and here's why:

Why Comma-Separated Fields Are a Bad Idea #

Storing multiple values in a single field breaks database normalization (specifically 3rd Normal Form), and creates headaches for every operation you need to perform:

• Slow, clunky queries: To find which product_id is linked to a specific similar_product_id, you'd have to use functions like FIND_IN_SET() which can't use indexes. This gets painfully slow as your table grows.
• Error-prone updates/deletes: If you need to remove one similar product from a product_id, you'd have to split the string, remove the value, re-concatenate, and update the row. No atomicity here—easy to mess up and create invalid data.
• No data integrity: You can't set up foreign key constraints to ensure similar_product_id actually exists in your products or similar_products_product tables. This opens the door to orphaned or invalid entries.

Better Solutions for Your Use Case #

Your current similar_products_view table structure is actually correct for a relational database. The problem is likely the inefficient batch insertion code, not the table design itself. Here are the best fixes:

1. Optimize Batch Inserts (Huge Performance Boost) #

Instead of looping through each entry and running a separate INSERT query, bundle all your inserts into a single query. This cuts down on database round-trips drastically.

Here's how to adjust your PHP code:

$valuePairs = [];
foreach ($data['similar_products'] as $cat_id => $similarIds) {
    foreach ($similarIds as $similar_product_id) {
        // Sanitize values and format as a value tuple
        $valuePairs[] = sprintf(
            "(%d, %d, %d)",
            (int)$product_id,
            (int)$similar_product_id,
            (int)$cat_id
        );
    }
}

if (!empty($valuePairs)) {
    $sql = sprintf(
        "INSERT INTO %ssimilar_products_view (product_id, similar_product_id, category_id) VALUES %s",
        DB_PREFIX,
        implode(', ', $valuePairs)
    );
    $this->db->query($sql);
}

This will run one insert query instead of dozens/hundreds, which is way faster.

2. Get "Merged" Results Without Changing Storage #

If you need to retrieve all similar products for a product_id as a single row (for display or API responses), create a database view instead of modifying your table. Views let you present merged data while keeping the underlying normalized structure intact.

Create the view with this SQL:

CREATE VIEW similar_products_combined AS
SELECT
    product_id,
    GROUP_CONCAT(similar_product_id SEPARATOR ',') AS similar_product_ids,
    GROUP_CONCAT(category_id SEPARATOR ',') AS category_ids
FROM `prefix_similar_products_view`
GROUP BY product_id;

Now you can query similar_products_combined to get merged rows, but all DELETE, UPDATE, and targeted SELECT operations still run on the original similar_products_view table—keeping everything efficient and maintainable.

3. JSON Fields (For Specific Scenarios Only) #

If your database supports JSON (MySQL 5.7+, PostgreSQL, etc.), you could store all similar items for a product_id in a single JSON column. But this is only a good idea if your primary use case is fetching all similar items for a product_id (and rarely querying by individual similar_product_id or category_id).

Example table structure:

CREATE TABLE similar_products_json (
    product_id INT PRIMARY KEY,
    similar_items JSON NOT NULL
);

Insert example:

INSERT INTO similar_products_json (product_id, similar_items)
VALUES (10, '[
    {"similar_product_id": 2, "category_id": 7},
    {"similar_product_id": 4, "category_id": 7},
    {"similar_product_id": 15, "category_id": 6}
]');

The downside here is that querying for a specific similar_product_id requires parsing JSON, which is slower than querying a normalized table. You also lose foreign key constraints, so you'd have to handle data validity in your application code.

Final Recommendation #

Stick with your normalized similar_products_view table—optimize the batch inserts as shown above. Use a view if you need merged results for display. Only consider JSON fields if you have a very specific use case where normalized queries aren't needed.

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