使用 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);但尽管如此，它并没有那么快。查询持续 5 秒，我希望它更快。我还为表格编制了索引。我想要一些关于如何最好地优化此查询的意见，或者我执行查询的方式是否足够。这是我之前做过的一个关于为什么我使用GROUP BY和MAX(id)

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

Answer 2

FLOOR((TO_DAYS(`date` - 737080)/7)

会给你一个“周数”，你可以使用它GROUP BY。使用它应该可以让您一次性获得所有结果，而不需要在其中包含应用程序循环SELECT。

我不明白 的重要性MAX(id)，所以我无法解决其余的问题。