使用多个连接优化SQL查询。需要关于技术快速有效地检索数据的建议

问题描述：

我有一个查询从product表（第一选择）获取产品说明，然后将其减去以下子查询语句以获得no。剩余库存的：使用多个连接优化SQL查询。需要关于技术快速有效地检索数据的建议

每个产品的-sum 股在inventory表（第二）

每个产品的-sum在sales_detail表（第三）

每个-sum出售产品转移到另一个分支在stock_transfer表（第4）

-每个产品的总和损坏在damaged_product表（第5个）

问题是每次查询加载时，它会全部搜索所有4个表以获得quantity列的总和。随着时间的推移，更多的记录被存储，查询将变得更慢。有什么建议么？

SELECT p.Id,p.Product_Name Product,p.Description, c.Category_Name Category,sc.Subcategory_Name Subcategory,s.Supplier_Name Supplier, p.Selling_Price `Unit Price`,i.Stocks,s.Sales, i.Stocks - IFNULL(s.Sales, 0) - IFNULL(t.Transfer, 0) - IFNULL(d.Damage, 0) AS Remaining 

FROM (SELECT Id, Product_Name, Description, Selling_Price, Category_Id, Subcategory_Id, Supplier_Id  FROM product WHERE enable_flag = 1) p 

LEFT OUTER JOIN(SELECT product_id, COALESCE(SUM(quantity), 0) AS Stocks 
       FROM inventory 
       WHERE enable_flag = 1 GROUP BY product_id) i ON p.Id = i.product_id 

LEFT OUTER JOIN(SELECT product_id, COALESCE(SUM(quantity), 0) AS Sales 
       FROM sales_detail 
       WHERE enable_flag = 1 GROUP BY product_id) s USING(product_id) 

LEFT OUTER JOIN(SELECT product_id, COALESCE(SUM(transfer_quantity), 0) AS Transfer 
       FROM stock_transfer 
       WHERE enable_flag = 1 GROUP BY product_id) t USING(product_id) 

LEFT OUTER JOIN(SELECT product_id, COALESCE(SUM(damaged_quantity), 0) AS Damage 
       FROM damaged_product 
       WHERE enable_flag = 1 GROUP BY product_id) d USING(product_id) 

JOIN Category c ON p.Category_Id=c.Id 

JOIN Subcategory sc ON p.Subcategory_Id=sc.Id 

JOIN Supplier s ON p.Supplier_Id=s.Id;

索引列有很大的影响关于查询性能。是否在涉及查询的所有表中索引了内部和外部键？ –

我相信MySql中的主键在创建时会自动建立索引，对于外键也一样。我需要检查'JOIN'子句中的所有'WHERE'语句是否都被索引。由于 –

我指的是类似在这个例子中为user_id，没有索引的外键列： CREATE TABLE MyGuests（ \t ID INT（6）UNSIGNED AUTO_INCREMENT PRIMARY KEY， \t USER_ID INT NOT NULL， \t姓VARCHAR（ 30）NOT NULL， \t姓氏VARCHAR（30）NOT NULL， \t电子邮件VARCHAR（50）， \t reg_date TIMESTAMP ）此外，这里是一个类似的帖子，可能会给你一个提示。 http://stackoverflow.com/questions/9330204/optimizing-mysql-query-with-sum-date-range-and-group-by –

答

您正在强制在子表上创建一个完整的groupby，这是不必要的。

SELECT Id, Product_Name Product, Description, Category_Name Category, Subcategory_Name Subcategory, 
Supplier_Name Supplier, Selling_Price 'Unit Price', 
Stocks, Sales, Stocks - Sales - Transfer - Damage AS Remaining 
FROM  
(select p.*, sc.Subcategory_Name, s.Supplier_Name, 
IFNULL((SELECT SUM(quantity) FROM inventory WHERE enable_flag = 1 and product_id = p.Id),0) as Stocks, 
IFNULL((SELECT SUM(quantity) FROM sales_detail WHERE enable_flag = 1 and product_id = p.Id),0) as Sales, 
IFNULL((SELECT SUM(transfer_quantity) FROM stock_transfer WHERE enable_flag = 1 and product_id = p.Id),0) as Transfer, 
IFNULL((SELECT SUM(damaged_quantity) FROM damaged_product WHERE enable_flag = 1 and product_id = p.Id),0) as Damage 
FROM product p 
JOIN Category c ON p.Category_Id=c.Id 
JOIN Subcategory sc ON p.Subcategory_Id=sc.Id 
JOIN Supplier s ON p.Supplier_Id=s.Id 
WHERE enable_flag = 1 
) p

而且ofcourse，你应该确保你有product_id指标上inventory，sales_detail，stock_transfer，damaged_product，加上Category_Id上Category，Subcategory_Id上Subcategory，最后Supplier_Id上Supplier

我想我仍然需要'GROUP BY'来合计每个产品ID。为了澄清，将每个产品的总和分组的工作是由单独的'和product_id = p.Id'完成的？ –

如果您关心每个产品的所有值，您只需要一组。例如如果您想比较目前的产品损坏货物与损坏货物的平均数量。但是在这里你只关心每个单独的产品，因此普通的正常选择子查询就没有问题。（请注意，select子查询总是外连接） – Cine

顺便说一句，您的第一个'IFNULL'缺少一个左括号，并且在最后一个'WHERE'子句之前有分号。非常感谢您的帮助，我会在测试查询后回复您:) –

使用多个连接优化SQL查询。需要关于技术快速有效地检索数据的建议

相关推荐