需要MySQL查询优化帮助

正如@Gordon Linoff已经指出的那样，您需要定义索引。但是，还有一些事情需要进一步处理。当然，最好定义表的顺序，因此我们对此进行了更改

SELECT l.Name, l.Model, l.Engine_Type, m.ID, g.Name  
FROM db.vehicle_part k
JOIN db.vehicle l ON k.ID_Vehicle = l.ID
JOIN db.part m ON k.ID_Part = m.ID

JOIN db.part_generic_part b ON b.ID_Part = m.ID
JOIN db.generic_part c ON c.ID = b.ID_Generic_part
JOIN db.generic_part_name d ON c.ID = d.ID_Generic_Part 
JOIN db.part_category_generic_part e ON c.ID = e.ID_generic_part
JOIN db.part_category f ON e.ID_category = f.ID
JOIN db.part_category_name g ON f.ID = g.ID_Part_Category 
JOIN db.lang h ON g.ID_Lang = h.ID

WHERE l.Name = 'BMW' AND l.Model = '3 (E30)' AND l.Engine_Type= '316 i' AND g.ID_Lang =2

对此

SELECT l.Name, l.Model, l.Engine_Type, m.ID, g.Name  
FROM db.vehicle_part k
JOIN db.vehicle l ON k.ID_Vehicle = l.ID
JOIN db.part m ON k.ID_Part = m.ID

JOIN db.part_generic_part b ON b.ID_Part = m.ID
JOIN db.generic_part c ON c.ID = b.ID_Generic_part
JOIN db.part_category_generic_part e ON c.ID = e.ID_generic_part
JOIN db.part_category f ON e.ID_category = f.ID
JOIN db.part_category_name g ON f.ID = g.ID_Part_Category 
JOIN db.lang h ON g.ID_Lang = h.ID
JOIN db.generic_part_name d ON c.ID = d.ID_Generic_Part 

WHERE l.Name = 'BMW' AND l.Model = '3 (E30)' AND l.Engine_Type= '316 i' AND g.ID_Lang =2

即使不能保证它实际上比最初的代码要快，这也是一种更优雅的代码。但是，有了这个更好的顺序，我们进入下一步。该where子句在join完成后执行，因此您将大型多维记录集加载到内存中，并过滤掉了行。该on条件较早执行，因此下一步是修改where并将其条件迁移到on条件中，如下所示：

SELECT l.Name, l.Model, l.Engine_Type, m.ID, g.Name  
FROM db.vehicle_part k
JOIN db.vehicle l ON k.ID_Vehicle = l.ID AND l.Name = 'BMW' AND l.Model = '3 (E30)' AND l.Engine_Type= '316 i'
JOIN db.part m ON k.ID_Part = m.ID

JOIN db.part_generic_part b ON b.ID_Part = m.ID
JOIN db.generic_part c ON c.ID = b.ID_Generic_part
JOIN db.part_category_generic_part e ON c.ID = e.ID_generic_part
JOIN db.part_category f ON e.ID_category = f.ID
JOIN db.part_category_name g ON f.ID = g.ID_Part_Category and g.ID_Lang =2
JOIN db.lang h ON g.ID_Lang = h.ID
JOIN db.generic_part_name d ON c.ID = d.ID_Generic_Part 

这样，由于不需要的记录join不会被全部加载到内存中然后进行过滤，因此它们会在过滤时被过滤掉，从而减少了时间。仅当查询仍然很慢时，才应执行最后一步，因为这会降低代码的可读性：您可以修改joins，使其具有严格的过滤器（大幅减少行数）或严重的投影（具有很多大的行数）字段，但只需要其中的一小部分）即可命名子选择。例子：

SELECT 'BMW', '3 (E30)', '316 i', m.ID, g.Name  
from (select temp.ID from db.vehicle temp where temp.Name = 'BMW' AND temp.Model = '3 (E30)' AND temp.Engine_Type= '316 i') l
JOIN db.vehicle_part k on l.ID = k.ID_VEHICLE
JOIN db.part m ON k.ID_Part = m.ID

JOIN db.part_generic_part b ON b.ID_Part = m.ID
JOIN db.generic_part c ON c.ID = b.ID_Generic_part
JOIN db.part_category_generic_part e ON c.ID = e.ID_generic_part
JOIN db.part_category f ON e.ID_category = f.ID
JOIN db.part_category_name g ON f.ID = g.ID_Part_Category and g.ID_Lang =2
JOIN db.lang h ON g.ID_Lang = h.ID
JOIN db.generic_part_name d ON c.ID = d.ID_Generic_Part 

最后的修改会预过滤器l，以确保只有很少的记录可以开始join。