mysql - 行作为带有查找表的列

Question

我有两个表格，如下所示:

table 汽车

+--------+-----------+-------+---------+
| car_id | attribute | value |  brand  |
+--------+-----------+-------+---------+
|      1 | colore    | rosso | Ferrari |
|      1 | prezzo    | 100   | Ferrari |
|      2 | couleur   | bleu  | Renault |
|      2 | prix      | 50    | Renault |
|      3 | colore    | blu   | Ferrari |
|      3 | prezzo    | 100   | Ferrari |
+--------+-----------+-------+---------+

表格翻译

+--------------------+----------------+---------+----------------------+------------------+
| original_attribute | original_value |  brand  | translated_attribute | translated_value |
+--------------------+----------------+---------+----------------------+------------------+
| colore             | rosso          | Ferrari | color                | red              |
| prezzo             | 100            | Ferrari | price                | 100              |
| colore             | blu            | Ferrari | color                | blue             |
| couleur            | bleu           | Renault | color                | blue             |
| prix               | 50             | Renault | price                | 50               |
+--------------------+----------------+---------+----------------------+------------------+

我正试图找到一张看起来像这样的 table :

+-------------------+-------+-------------+--------------------+
| translated_car_id | color | price       | translated_brand   |
+-------------------+-------+-------------+--------------------+
|                 1 | red   |         100 | Ferrari            |
|                 2 | blue  |          50 | Renault            |
|                 3 | blue  |         100 | Ferrari            |
+-------------------+-------+-------------+--------------------+

目前，我正在使用以下代码。它有效，但速度极慢。

SELECT
    car_id translated_car_id,
    MAX(CASE
        WHEN translations.translated_attribute = 'color' THEN translations.translated_value
    END) color,
    MAX(CASE
        WHEN translations.translated_attribute = 'price' THEN translations.translated_value
    END) price,
    brand translated_brand
FROM
    cars c
        INNER JOIN
    translations ON (c.attribute = translations.original_attribute
        AND c.brand = translations.brand
        AND c.value = relations.original_value)
GROUP BY c.car_id

有人知道如何使查询或结构更高效吗？非常感谢。

提前致谢

Answer 1

据我所知，MySql 没有散列匹配，这在这里很有用。所以我假设一切都是作为嵌套循环连接完成的。

我担心的是，因为没有关于翻译的索引，所以对于汽车中的每一行，它都必须扫描翻译表以找到匹配的行。

我会推荐一个关于翻译的聚集索引(original_value、brand、original_attribute)。 (ms-sql 在最具体的情况下表现最好，首先其他一切都相同，不确定 MySql)这样它就可以直接转到需要匹配的行。这应该允许快速完成对一辆车的查询。

如果您可以减少从制造商到语言的翻译，这肯定有助于缩小翻译表的大小，但您必须确保它适用于您的数据集，因为它确实会降低一定程度的灵 active 。

我认为 MySql 将能够使用 cars 上的索引来有效地处理 GROUP BY，但我会提出一个不需要 group by 的更规范化的模式。

car   
-------------
car_id
brand

car_attribute
------------
car_id
attribute
value
clustered_index(car_id, attribute)

brand_attribute
------------
brand_attribute_id
brand
attribute
translated_attribute
clustered_index(brand, translated_attribute)

brand_attribute_value
------------
brand_attribute_id
value
translated_value
clustered_index(brand_attribute_id, value)

这是执行此操作的查询。

SELECT
    car.car_id,
    color_brand_value.translated_value AS color,
    price_brand_value.translated_value AS price,
    car.brand
FROM
    car

    INNER JOIN brand_attribute AS color_brand
        ON color_brand.brand = car.brand
            AND color_brand.translated_attribute = 'color'
    INNER JOIN car_attribute AS color_attribute
        ON color_attribute.car_id = car.car_id
            AND color_attribute.attribute = brand.attribute
    INNER JOIN brand_attribute_value AS color_brand_value
        ON color_brand_value.brand_attribute_id = color_brand.brand_attribute_id
            AND color_brand_value.value = color_attribute.value

    INNER JOIN brand_attribute AS price_brand
        ON price_brand.brand = car.brand
            AND price_brand.translated_attribute = 'price'
    INNER JOIN car_attribute AS price_attribute
        ON price_attribute.car_id = car.car_id
            AND price_attribute.attribute = brand.attribute
    INNER JOIN brand_attribute_value AS price_brand_value
        ON price_brand_value.brand_attribute_id = price_brand.brand_attribute_id
            AND price_brand_value.value = price_attribute.value

这样做肯定比较复杂。对于您的情况，我不确定它是否更好，但如果第一个选项不够好，则需要考虑一些事情。

我的背景是 mssql，因此可能存在我不知道的差异。如果我遗漏或有任何错误，请发表评论。