php - MySQL/PHP，ForEach 循环缓慢

Question

我是 stackoverflow 的新手，对于这么长的问题，请接受我的歉意。我有一个零售店的大型数据库。我们想准备一份报告来获取产品的期初和期末余额，产品有批号，每个批处理都有分配给多个位置的唯一序列号。挑战在于获取每个仓库位置的每批选定产品的期初余额。如果报告过滤器中未指定位置，则报告应给出每个位置的选定产品及其所有批处理的结果。

我使用 PHP/PDO 获取所有位置并循环遍历它，在第一个循环中，我获取针对所选产品的所有批处理，在批处理循环中，我首先获取期初余额(计算方式如下:开始日期过滤器之前的数量)，然后我计算该产品批处理在该位置的接收数量(开始日期和截止日期过滤器中接收的数量总和)，然后销售数量或移动到其他仓库的数量(移动数量总和)到所选起始日期和截止日期过滤器内的其他仓库)。

我现在可以计算期末余额，因为我拥有所有值。这运行良好，但是当查询运行 20 个仓库并且单个产品有近 20 多个批处理时，报告需要很长时间才能处理，并且 MySQL CPU 使用率高达 200%。

我尝试优化查询，我的表已正确编入索引。表有很多数据。数百万条记录。我需要有关如何改进我的代码或方法、我做错了什么以及如何更快地生成报告的建议。

我正在为我的应用程序使用 code igniter，下面是代码。

foreach($locations as $loc){
    //if batch is selected put it in array
    if($query_array['batch_no'] != ''){
        $batches[] = $query_array['batch_no'];
    }else{
    // or get all batches from the inventory table for the location
        $this->db->select('distinct(batch_no)');
        $this->db->from('product_details');
        $this->db->where('product_id',$query_array ['product']);
        $this->db->where('warehouse_id',$loc);
        $query = $this->db->get();
        foreach($query->result() as $row){
            $batches[] =  $row->batch_no;
        }
        $query->free_result();
    }

    foreach($batches as $batch){

        //GIN IN Opening Balance
        $this->db->select('IFNULL(SUM(gi.qty),0) as gin_in',FALSE);
        $this->db->from('gin as g');
        $this->db->join('gin_items as gi','gi.gin_id=g.id');
        $this->db->where('gi.product_id',$query_array ['product']);
        $this->db->where('DATE(g.creation_date) < ',$query_array ['date_from']);
        $this->db->where('g.to_location_id', $loc);
        $this->db->where_in('gi.batch_no',$batch);
        $this->db->where_in('gi.status',2);
        $this->db->where('g.status',3);
        $query = $this->db->get();

        if($query->num_rows()==1)
        {
            $var1=$query->row()->gin_in;
            $query->free_result();
        }

        // SUM (Return Invoices for a specific product, for this particular store, 
        // before this date range) -> $var2
        $this->db->select('IFNULL(SUM(ii.qty),0) as sale_return_in',FALSE);
        $this->db->from('return_sales_invoice rs');
        $this->db->join('return_invoice_items as ii','ii.invoice_id=rs.id');
        $this->db->where('ii.medicine_id',$query_array ['product']);
        $this->db->where_in('ii.batch_no',$batch);
        $this->db->where_in('rs.location_id',$loc);
        $this->db->where('rs.dated < ',$query_array ['date_from']);

        $query = $this->db->get();

        if($query->num_rows()==1){
            $var2=$query->row()->sale_return_in;
            $query->free_result();
        }


        //SUM (Sales Invoices of a specific product, from this particular store, 
        // before this date range) -> $var3
        $this->db->select('IFNULL(SUM(ii.qty),0) as sale_out',FALSE);
        $this->db->from('sales_invoice si');
        $this->db->join('invoice_items as ii','ii.invoice_id=si.id');

        $this->db->where('ii.medicine_id',$query_array ['product']);
        $this->db->where_in('ii.batch_no',$batch);
        $this->db->where_in('si.location_id',$loc);
        $this->db->where('si.dated < ',$query_array ['date_from']);

        $query = $this->db->get();

        if($query->num_rows()==1){
            $var3=$query->row()->sale_out;
            $query->free_result();
        }

        // SUM (GIN of a specific product, from this particular store, 
        // before this date range) -> $var4
        // if from location then minis stock 
        $this->db->select('IFNULL(SUM(gi.qty),0) as gin_out',FALSE);
        $this->db->from('gin as g');
        $this->db->join('gin_items as gi','gi.gin_id=g.id');
        $this->db->where('DATE(g.creation_date) < ',$query_array ['date_from']);
        $this->db->where('gi.product_id',$query_array ['product']);
        $this->db->where_in('gi.batch_no',$batch);
        $this->db->where_in('g.from_location_id',$loc);
        $this->db->where('gi.status',2);
        $this->db->where('g.status',3);
        $query = $this->db->get();

        if($query->num_rows()==1){
            $var4=$query->row()->gin_out;
            $query->free_result();
        }
        $op_bal = ($var1 + $var2) - ($var3 + $var4);

        //---------------------------------------------------------------------------------

        $where_from = "DATE(g.creation_date) >='" . $query_array ['date_from'] . "'";
        $where_to = "DATE(g.creation_date)  <='" . $query_array ['date_to'] . "'";

        $rs_where_from = "DATE(rs.creation_date) >='" . $query_array ['date_from'] . "'";
        $rs_where_to = "DATE(rs.creation_date)  <='" . $query_array ['date_to'] . "'";

        $si_where_from = "DATE(si.creation_date) >='" . $query_array ['date_from'] . "'";
        $si_where_to = "DATE(si.creation_date)  <='" . $query_array ['date_to'] . "'";

        //GIN IN Opening Balance
        $this->db->select('IFNULL(SUM(gi.qty),0) as gin_in',FALSE);
        $this->db->from('gin as g');
        $this->db->join('gin_items as gi','gi.gin_id=g.id');
        $this->db->where($where_from);
        $this->db->where($where_to);
        $this->db->where('gi.product_id',$query_array ['product']);
        $this->db->where_in('gi.batch_no',$batch);
        $this->db->where_in('g.to_location_id', $loc);
        $this->db->where('g.status',3);
        $this->db->where('gi.status',2);
        $query = $this->db->get();

        if($query->num_rows()==1){
            $g_stock_in=$query->row()->gin_in;
            $query->free_result();
        }

        // SUM (Return Invoices for a specific product, for this particular store, 
        // before this date range) -> $var2
        $this->db->select('IFNULL(SUM(ii.qty),0) as sale_return_in',FALSE);
        $this->db->from('return_sales_invoice rs');
        $this->db->join('return_invoice_items as ii','ii.invoice_id=rs.id');
        $this->db->where('ii.medicine_id',$query_array ['product']);
        $this->db->where($rs_where_from);
        $this->db->where($rs_where_to);                 
        $this->db->where_in('ii.batch_no',$batch);
        $this->db->where_in('rs.location_id',$loc);
        $query = $this->db->get();

        if($query->num_rows()==1){
            $s_stock_in=$query->row()->sale_return_in;
            $query->free_result();
        }


        //SUM (Sales Invoices of a specific product, from this particular store, 
        // before this date range) -> $var3
        $this->db->select('IFNULL(SUM(ii.qty),0) as sale_out',FALSE);
        $this->db->from('sales_invoice si');
        $this->db->join('invoice_items as ii','ii.invoice_id=si.id');
        $this->db->where('ii.medicine_id',$query_array ['product']);
        $this->db->where_in('ii.batch_no',$batch);
        $this->db->where_in('si.location_id',$loc);
        $this->db->where($si_where_from);
        $this->db->where($si_where_to);

        $query = $this->db->get();

        if($query->num_rows()==1){
            $s_stock_out=$query->row()->sale_out;
            $query->free_result();
        }

        // SUM (GIN of a specific product, from this particular store, 
        // before this date range) -> $var4
        // if from location then minis stock 
        $this->db->select('IFNULL(SUM(gi.qty),0) as gin_out',FALSE);
        $this->db->from('gin as g');
        $this->db->join('gin_items as gi','gi.gin_id=g.id');
        $this->db->where($where_from);
        $this->db->where($where_to);
        $this->db->where('gi.product_id',$query_array ['product']);
        $this->db->where_in('gi.batch_no',$batch);
        $this->db->where_in('g.from_location_id',$loc);
        $this->db->where('g.status',3);
        $this->db->where('gi.status',2);
        $query = $this->db->get();

        if($query->num_rows()==1){
            $g_stock_out=$query->row()->gin_out;
            $query->free_result();
        }
        $qty_in=$g_stock_in+$s_stock_in;
        $qty_out=$g_stock_out+$s_stock_out;

        $productName=$this->getProductName($query_array ['product']);
        $locationName=$this->getLocationName($loc);

        $data [] = array (
            'location' => $locationName,
            'product' => $productName, 
            'batchno' => $batch , 
            'op_bal' => $res['op_bal'] , 
            'qty_in' => $qty_in, 
            'qty_out' => $qty_out,
            'cl_bal' => ($res['op_bal'] + $qty_in ) - $qty_out
        );      

    }
}
return $data;

Answer 1

您认识到您在这个报告程序中做了很多事情。特别是您正在进行大量单结果行查询。与编写较少的为每个项目返回一行的查询相比，这通常被认为对性能有害。

您的问题的一个可能解决方案是:在晚上运行报告程序，不要为糟糕的性能而烦恼。你会完成工作的。这种通宵批处理在现实世界中相当普遍。

另一种解决方案:将数据库视为返回表的机器，而不仅仅是值。重构您的查询以返回多个结果。您最终会得到少得多的查询，并且您将能够利用 DBMS 的查询规划器一次提取大量信息。例如，不是在 php 中循环定位，而是让 MySQL 这样做。

这将为您提供批处理、仓库和产品的列表，每行一行。

    SELECT DISTINCT batch_no, warehouse_id, product
      FROM product_details
     ORDER BY batch_no, warehouse_id, product

接下来，我想这个查询会为您提供所需的结果之一，但针对所有批处理、仓库和产品。 “我猜”是因为您没有透露您的架构或业务规则。此查询来自组合程序中的前两个查询。

SELECT SUM(gi.qty) gin_in, p.batch_no, p.warehouse_id, p.product 
  FROM product_details p
  JOIN gin g        ON g.to_location_id = p.warehouse_id
  JOIN gin_items gi ON gi.gin_id = g.id AND gi.product_id = p.product
 WHERE DATE(g.creation_date) < $date_from
   AND g.status = 3
 GROUP BY p.batch_no, p.warehouse_id, p.product

您明白了:让您的查询返回很多行，而不是运行无数次查询，每次只返回一行。