mysql - 联合作为子查询 MySQL

Question

我想使用联合作为子查询来优化查询。我不太确定如何构建查询。我正在使用 MYSQL 5

这是原始查询:

SELECT  Parts.id 
FROM    Parts_Category, Parts 
    LEFT JOIN Image ON Parts.image_id = Image.id 
WHERE 
( 
    (
        Parts_Category.category_id = '508' OR 
        Parts_Category.main_category_id ='508'
    ) AND 
    Parts.id = Parts_Category.Parts_id 
) AND 
Parts.status = 'A' 
GROUP BY 
    Parts.id 

我想做的是用下面的联合替换这个 ( (Parts_Category.category_id = '508' OR Parts_Category.main_category_id ='508' ) 部分。这样我就可以删除 GROUP BY子句并使用应提高性能的直接 col 索引。零件和零件类别表每个包含 50 万条记录，因此任何 yield 都会很大。

(
    SELECT * FROM
    (
        (SELECT Parts_id FROM Parts_Category WHERE category_id = '508') 
        UNION 
        (SELECT Parts_id FROM Parts_Category WHERE main_category_id = '508')
    )
    as Parts_id
)

谁能告诉我如何重写它？我已经尝试了几个小时，但无法获得它，因为我只是 MySQL 的新手。

Answer 1

SELECT  Parts.id
FROM    (
        SELECT  parts_id
        FROM    Parts_Category
        WHERE   Parts_Category.category_id = '508'
        UNION
        SELECT  parts_id
        FROM    Parts_Category
        WHERE   Parts_Category.main_category_id = '508'
        ) pc
JOIN    Parts
ON      parts.id = pc.parts_id
        AND Parts.status = 'A'
LEFT JOIN
        Image
ON      image.id = parts.image_id

请注意 MySQL 可以使用 Index Merge 并且您可以将查询重写为:

SELECT  Parts.id
FROM    (
        SELECT  DISTINCT parts_id
        FROM    Parts_Category
        WHERE   Parts_Category.category_id = '508'
                OR Parts_Category.main_category_id = '508'
        ) pc
JOIN    Parts
ON      parts.id = pc.parts_id
        AND Parts.status = 'A'
LEFT JOIN
        Image
ON      image.id = parts.image_id

，如果你有以下索引，效率会更高:

Parts_Category (category_id, parts_id)
Parts_Category (main_category_id, parts_id)