零售库存Mysql查询优化

Question

关于零售管理系统的如下表格：

商店：store_id, name

产品：product_id, name, cost

PRODUCT_ENTRIES：key, store_id, date

PRODUCT_ENTRIES_CONTENT: product_entries_key, product_id, quantity

PRODUCT_EXITS：key, store_id, product_id, quantity, status, date

SALES：key, store_id, date

SALES_CONTENT: sales_key, product_id, quantity

返回：key, store_id, date

RETURNS_CONTENT: returns_key, product_id, quantity

为了计算股票价值，我遍历products表的内容，并对每个product_id：

• product_entries_content和returns_content的和量
• 减去product_exits_content (状态=2或3)以及sales_content的数量

为了计算每个商店的库存成本，我正在通过一个PHP循环对每个不同的存储运行以下查询并输出结果：

SELECT

    SUM((((

    (SELECT COALESCE(SUM(product_entries_content.quantity), 0)

    FROM product_entries

    INNER JOIN product_entries_content ON 
product_entries_content.product_entries_key = product_entries.key

    WHERE product_entries_content.product_id = products.id 
    AND product_entries.store_id = '.$row['id'].'   
    AND DATE(product_entries.date) <= DATE(NOW()))


    -

    (SELECT COALESCE(SUM(quantity), 0) 

    FROM sales_content

    INNER JOIN sales ON sales.key  = sales_content.sales_key

    WHERE product_id = products.product_id AND sales.store_id = '.$row['id'].'
    AND DATE(sales_content.date) <= DATE(NOW()))

    +

    (SELECT COALESCE(SUM(quantity), 0) 

    FROM returns_content

    INNER JOIN returns  ON returns.key = returns_content.returns_key

    WHERE product_id = products.product_id AND returns.store_id = '.$row['id'].'
    AND DATE(returns.date) <= DATE(NOW()))

    -

    (SELECT COALESCE(SUM(quantity), 0) 

    FROM product_exits

    WHERE product_id = products.product_id AND (status = 2 OR status = 3) 
AND product_exits.store_id = '.$row['id'].' #store_id
    AND DATE(product_exits.date) <= DATE(NOW()))     

    ) * products.cost) / 100) ) AS "'.$row['key'].'" #store_name

FROM products WHERE 1

所有外键和索引都已正确设置。问题是，由于每个商店的存储和移动量很大，查询变得越来越繁重，而且由于库存是从每一家商店的历史开始计算的，所以它只会随着时间的推移而变慢。

我能做些什么来优化这个方案？

Answer 1

理想情况下，每个表的SHOW CREATE TABLE tablename在任何优化问题上都会有很大帮助。每一列的数据类型对性能非常重要。

尽管如此，从您提供的信息来看，假设列数据类型都是合适的，下面的内容应该是有帮助的。

如果不存在以下索引，则添加它们。注意:对于下列复合索引，单列索引不是有效的替换。你说过

所有外键和索引都已正确设置。

但是，这并没有告诉我们它们是什么，以及它们是否“适合”优化。

新索引

ALTER TABLE sales
CREATE INDEX `aaaa` (`store_id`,`key`)

ALTER TABLE sales_content
CREATE INDEX `bbbb` (`product_id`,`sales_key`,`date`,`quantity`)

ALTER TABLE returns
CREATE INDEX `cccc` (`store_id`,`date`,`sales_key`)

ALTER TABLE returns_content
CREATE INDEX `dddd` (`product_id`,`returns_key`,`quantity`)

ALTER TABLE product_exits
CREATE INDEX `eeee` (`product_id`,`status`,`store_id`,`date`,`quantity`)

ALTER TABLE product_entries
CREATE INDEX `ffff` (`store_id`,`date`,`key`)

ALTER TABLE product_entries_content
CREATE INDEX `gggg` (`product_id`,`product_entries_key`,`quantity`)

(使用比aaaa更合适的名称。我只是用这些来节省时间。)

上述每个索引都将允许数据库只读取每个表的一行。大多数涉及联接的性能问题都来自于所谓的双重查找。

理解索引和双重查找

索引只是表数据的副本。索引中列出的每一列都按照索引中列出的顺序从表中复制，然后将主键追加到索引中的该行。当数据库使用索引查找值时，如果索引中没有包含所有信息，则主键将用于访问表的聚集索引以获取其余信息。这就是双重查找是什么，这是非常糟糕的表现。

示例

上述所有索引都是为了避免双重查找而设计的。让我们看看第二个子查询，看看与该查询相关的索引是如何工作的。

ALTER TABLE sales
CREATE INDEX `aaaa` (`store_id`,`key`)

ALTER TABLE sales_content
CREATE INDEX `bbbb` (`product_id`,`sales_key`,`date`,`quantity`)

Subquery (我添加了别名并调整了日期列的访问方式，但否则它将保持不变)：

SELECT COALESCE(SUM(sc.quantity), 0) 
FROM sales_content sc
INNER JOIN sales s 
ON s.key  = sc.sales_key
WHERE sc.product_id = p.product_id 
AND s.store_id = '.$row['id'].'
AND sc.date < DATE_ADD(DATE(NOW()), INTERVAL 1 DAY)

使用aaaa索引，数据库将只能查找与store_id匹配的sales表中的行，因为这是在索引中首先列出的。把它想象成电话簿一样，其中store_id是姓，key是名字。如果你有姓，那就很容易翻到电话簿的那个点，很快就能得到与这个姓氏相符的所有名字。同样，数据库能够非常快地“翻转”到包含给定store_id值的索引部分，并找到所有key值。在这种情况下，我们根本不需要主键(电话簿示例中的电话号码)。

因此，完成了对sales表的处理，我们从那里得到了所需的所有key值。

接下来，数据库移到bbbb索引上。我们已经有来自主查询的product_id，还有来自aaaa索引的sales_key。这就像电话簿上有名字和姓氏一样。唯一需要比较的是日期，它可能就像电话簿上的地址。数据库将按顺序存储所有的日期，因此，通过给它一个截止值，它可以查看所有的日期，直到某个点。

bbbb索引的最后一部分是数量，它在那里，以便数据库能够快速地总结所有这些数量。要了解为什么这是快速，再考虑电话簿。想象一下，除了姓、名和地址信息之外，还有一个数量列(指某物，不管是什么)。如果你想要一个特定的姓氏、名字和所有以数字5或更少开头的地址的数量之和，那是很容易的，不是吗？只需查找第一个地址，并按顺序将它们加起来，直到到达以大于5的数字开头的第一个地址。在以这种方式使用date列时，数据库的好处是相同的(在本例中，date类似于address列)。

日期列

最后，我在前面提到，我更改了访问date列的方式。您永远不希望在与另一个值比较的数据库列上运行函数。原因是:如果你必须将所有的地址转换成罗马数字，在进行比较之前会发生什么？你不可能就像我们之前那样从名单上下来。您必须转换所有的值，然后检查每个值以确保其在限制范围内，因为我们不再知道值是否正确排序才能完成“读取所有值，然后在前面描述的某个值处停止”快捷方式。

您和我可能知道，将日期时间值转换为日期不会更改顺序，但数据库将不知道(可能会优化此转换，但这不是我想假设的事情)。所以，保持柱子的纯净。我所做的更改只是取了NOW()日期，并添加了一天，然后将其改为<而不是<=。毕竟，比较两个值并说日期必须等于或少于今天的日期，就等于说日期必须小于明天的日期。

查询

下面是我对你的最后一个查询。如前所述，除了日期更改和别名之外，没有什么变化。但是，在访问products.id的第一个子查询中有一个错误。我将id修改为product_id，因为这与您所说的products表的列相匹配。

SELECT
SUM(
(
(
(
    (
    SELECT COALESCE(SUM(pec.quantity), 0)
    FROM product_entries pe
    INNER JOIN product_entries_content pec 
    ON pec.product_entries_key = pe.key
    WHERE pec.product_id = p.product_id 
    AND pe.store_id = '.$row['id'].' 
    AND pe.date < DATE_ADD(DATE(NOW()), INTERVAL 1 DAY)
    )
    -
    (
    SELECT COALESCE(SUM(sc.quantity), 0) 
    FROM sales_content sc
    INNER JOIN sales s 
    ON s.key  = sc.sales_key
    WHERE sc.product_id = p.product_id 
    AND s.store_id = '.$row['id'].'
    AND sc.date < DATE_ADD(DATE(NOW()), INTERVAL 1 DAY)
    )
    +
    (
    SELECT COALESCE(SUM(rc.quantity), 0)
    FROM returns_content rc
    INNER JOIN returns r 
    ON r.key = rc.returns_key
    WHERE rc.product_id = p.product_id 
    AND r.store_id = '.$row['id'].'
    AND r.date < DATE_ADD(DATE(NOW()), INTERVAL 1 DAY)
    )
    -
    (
    SELECT COALESCE(SUM(pex.quantity), 0)
    FROM product_exits pex
    WHERE pex.product_id = p.product_id 
    AND (pex.status = 2 OR pex.status = 3)
    AND pex.store_id = '.$row['id'].' #store_id
    AND pex.date < DATE_ADD(DATE(NOW()), INTERVAL 1 DAY)
    )
) 
* p.cost) 
/ 100)
) AS "'.$row['key'].'" #store_name
FROM products p WHERE 1

您可以通过将product_exits表上的子查询拆分为两个单独的子查询来进一步优化这一点，而不是使用OR，这将多次执行不好的操作。最终，您必须对此进行基准测试，以查看数据库对OR本身的优化效果如何。