sql - MySQL 对 SELECT FROM 多个表的优化有相当多的 AND 操作？

Question

此查询需要大约 5 - 10 秒才能运行，这对于渲染网页来说是 Not Acceptable :

SELECT part . * , brand . * 
FROM  `part` ,  `model2year2part` ,  `brand2subcategory2part` ,  `brand2subcategory` ,  `brand` 
WHERE  `model2year2part`.`model2year_id` =  '9521'
AND  `model2year2part`.`part_id` =  `part`.`id` 
AND  `brand2subcategory`.`subcategory_id` =  '1'
AND  `brand2subcategory2part`.`brand2subcategory_id` =  `brand2subcategory`.`id` 
AND  `brand2subcategory2part`.`part_id` =  `part`.`id` 
AND  `brand`.`id` =  `part`.`brand_id` 

ANSI化:

SELECT p.*, 
       b.* 
  FROM PART p
  JOIN brand b ON b.id = p.brand_id
  JOIN model2year2part m ON m.part_id = p.id
  JOIN brand2subcategory2part b2p ON b2p.part_id = p.id 
  JOIN brand2subcategory b2 ON b2.id = b2.brand2subcategory_id
 WHERE m.model2year_id = '9521'
   AND b2.subcategory_id = '1'

无论您向 model2year2part.model2year_id 和 brand2subcategory.subcategory_id 作为输入提供什么内容。

解释查询结果:http://i.stack.imgur.com/aYtXl.jpg

我已经为所有 5 个表完成了所有可能的索引/唯一索引。

• 部分:http://i.stack.imgur.com/9z2Mm.jpg
• model2year2part:http://i.stack.imgur.com/jeLMY.jpg
• 品牌2子类别2部分:http://i.stack.imgur.com/ZkI5n.jpg
• 品牌2子类别:http://i.stack.imgur.com/fbygX.jpg
• 品牌:http://i.stack.imgur.com/pfO33.jpg

每个表的总记录数:

• 部分:728,534
• model2year2part:15,012,595(这是罪魁祸首吗？)
• 品牌2子类别2部分:729,030
• 品牌2子类别:8,111
• 品牌:875

罪魁祸首可能是什么？除了硬件升级之外，还有其他方法可以优化此查询吗？

Answer 1

首先，我注意到您的第四个 Join 子句中看起来有一个错误:

ON b2.id = b2.brand2subcategory_id

我假设这应该是:

ON b2.id = b2p.brand2subcategory_id

其次，您可以尝试将过滤连接与输出所需的连接分开。这允许您仅对子查询本身进行解释，以查看可能导致问题的原因:

Select P.*, B.*
From Part As P
    Join Brand As B
        On B.Id = P.brand_Id
Where P.part_id In  (
                        Select M1.part_Id
                        From mode2year2part As M1
                                Join brand2subcategory2part As B2P
                                    On B2P.part_id = M1.part_id
                                Join brand2subcategory As B2
                                    On B2.Id = B2P.brand2subcategory_id
                        Where m1.model2year_id = '9521'
                            And B2.subcategory_id = '1'
                        )