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

Question

我有一个从 product 表(第一次选择)中获取产品描述的查询，然后将其减去以下子查询语句以获得编号。剩余库存:

-inventory 表中每个产品stocks 的总和(第 2 个)

-sales_detail 表中售出的每种产品的总和(第 3 个)

-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;

Answer 1

您在子表上强制执行完整的 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

当然，您应该确保在 inventory、sales_detail、stock_transfer、product_id 上有索引，damaged_product，加上 Category 上的 Category_Id，Subcategory 上的 Subcategory_Id，最后是 Supplier

上的 Supplier_Id