php - 使用 for 循环的性能 MySQL 查询

Question

在这种方法中，首先我必须获得两个日期之间的星期日日期，在本例中大约为 1 年。然后我在 for 循环中遍历日期并将它们设置为查询。我使用 prepared statements 来让它更快。

//Get the first day and last day
$dateInitial = strtotime('2018-08-21');
$dateFinal   = strtotime('2019-08-21');
$final       = array();

$sql = "SELECT id_product, product, plant_sowing, plant_production, area_planting, CONCAT(id_product,'_', weeks) AS identity
              FROM (
                    SELECT sw_sowing.id_product, pr_products.product, sw_sowing.type, YEARWEEK(:dates,3) AS weeks, SUM(sw_sowing.quantity) AS plant_sowing,
                           SUM(IF(ROUND(DATEDIFF(TIMESTAMPADD(DAY,(6-WEEKDAY(:dates)), :dates), sw_sowing.date)/7)>=sw_sowing.weeks_prod, sw_sowing.quantity,0)) AS plant_production,
                           ((SUM(sw_sowing.quantity))/pr_products.plant_m2) AS area_planting
                    FROM (
                          SELECT MAX(id) AS id
                          FROM sw_sowing
                          WHERE status != 0
                          AND id_tenant = :id_tenant
                          AND date <= :dates
                          AND multiply != 1
                          AND id_product = 1
                          GROUP BY id_production_unit_detail
                    ) AS sw
                    INNER JOIN sw_sowing ON sw_sowing.id = sw.id
                    INNER JOIN pr_products ON pr_products.id = sw_sowing.id_product
                    INNER JOIN pr_varieties ON pr_varieties.id = sw_sowing.id_variety
                    WHERE pr_varieties.code != 1
                    GROUP BY sw_sowing.id_product, sw_sowing.type
                    HAVING type NOT IN('ER','PR')
              ) AS s";

$statement    = $this->db->prepare($sql);

//get the sunday dates between two dates and bind the variables
for ($i = $dateInitial; $i <= $dateFinal ; $i = strtotime('+1 day', $i)) {
        if (date('N', $i) == 7){
          $values = [
            ':dates'      => date("Y-m-d", $i),
            ':id_tenant'  => 1
          ];

          $types = [
            ':dates'      => Column::BIND_PARAM_STR,
            ':id_tenant'  => Column::BIND_PARAM_INT
          ];

          $result   = $this->db->executePrepared($statement, $values, $types);
          $final[]  = $result->fetchAll(Phalcon\Db::FETCH_ASSOC);
        }
      }
      return $final;

但尽管如此，它并没有那么快。查询持续 5 秒，我希望它更快。

我还为表格编制了索引。我想就如何最好地优化此查询或我执行查询的方式是否合适提出一些意见。

这是我之前做的一道题，关于为什么我使用GROUP BY和MAX(id)

get max ids by group mysql

Answer 1

优化它的最简单方法是创建一个过程:

1. 将开始和结束日期作为参数
2. 遍历日期以找到星期日并将它们放入临时表
3. 使用临时表加入您的查询并立即返回所有

这样您就可以执行一次实际查询，而不是 52 次。应该快得多。

您需要检查的另一件事是内部查询的 GROUP BY 子句。查询仅返回 MAX(id)，因此不需要 GROUP BY。

临时表的使用示例:

create procedure sp_sample( in_start date, in_end date)
begin

declare v_date date;

SELECT in_start + INTERVAL 6 - weekday(in_start) day into v_date;

drop temporary table if exists sundays_tmp;
create temporary table sundays_tmp (
d date
);

while (v_date<in_end) do
  insert into sundays_tmp values (v_date);

  select v_date + INTERVAL 7 day into v_date;
end while;

-- Your query here

end