时间戳

首页 > 解决方案 > 如何使我的 SQL 查询更高效，以便我的结果处理不会花费很长时间

php - 如何使我的 SQL 查询更高效，以便我的结果处理不会花费很长时间

问题描述

我有以下基本示例：

https://phpize.online/?phpses=045f282ad521841c60cb77150ba7f20d&sqlses=a090bcc3e9d17e4e1dda56920d64765b&php_version=php7&sql_version=mysql57

因此，如示例中所示，我们获取所有行并将它们打印出来，每 5 行我们重置一次，并创建一个“新页面”。实际上，每个页面都有一个表格，在每个页面的底部，我们有字段 durationDay 和 durationNight 的总计。所以这一页的总和，上一页的总和和总计的一行。如示例中所示，我通常会使用 SUM 和 () 中的数组来查询此总计，以获得我想要的总计（这似乎真的效率低下）。现在可以看到，有一个 blankRow 字段应该算作一行，即每页最多 5 行，但如果 1 行作为“2”空白行，则占用 2 个计数。

我的 Live env 中有一些非常复杂的代码用于上述场景，我想让它更高效，最多 100 行我的代码很好，但如果有 5000 行我已经计时了大约需要 30 秒才能完全就这样完成。我想知道是否可以修改上面的 SQL，以便将分页、空白行甚至每个“页面”底部的 3 种总计类型考虑在内。即使每个页面都可以作为自己的数组或其他东西返回，但我真的不知道，我需要这样做更有效率（我注意到，如果我删除每个页面底部的总计查询，处理时间从 8000 行的 30 秒到 2 秒以下

谢谢

SQL

create table trip (
    date Date,
    goid varchar(255),
    backid varchar(255),
    vehicleId int, 
    durationDay int, 
    durationNight int,
    blankRow int
);

create table vehicle (
vehicleId int,
color varchar(255), 
name varchar(255)
);

insert into trip (date, goid, backid, vehicleId, durationDay, durationNight, blankRow) values ('2020-09-20', 'GO1', 'DAC', 22, 2, 3, 0);
insert into trip (date, goid, backid, vehicleId, durationDay, durationNight, blankRow) values ('2020-09-21', 'DAC', 'GO1', 22, 3, 4, 0);
insert into trip (date, goid, backid, vehicleId, durationDay, durationNight, blankRow) values ('2020-09-09', 'DAC', 'GO1', 33, 4, 3, 2);
insert into trip (date, goid, backid, vehicleId, durationDay, durationNight, blankRow) values ('2020-09-22', 'GO1', 'DAC', 22, 4, 4, 0);
insert into trip (date, goid, backid, vehicleId, durationDay, durationNight, blankRow) values ('2020-09-12', 'GO1', 'GO1', 33, 3, 3, 0);
insert into trip (date, goid, backid, vehicleId, durationDay, durationNight, blankRow) values ('2020-09-25', 'DAC', 'GO1', 22, 4, 4, 0);
insert into trip (date, goid, backid, vehicleId, durationDay, durationNight, blankRow) values ('2020-09-22', 'GO1', 'DAC', 22, 4, 4, 0);
insert into trip (date, goid, backid, vehicleId, durationDay, durationNight, blankRow) values ('2020-09-12', 'GO1', 'GO1', 33, 3, 3, 0);
insert into trip (date, goid, backid, vehicleId, durationDay, durationNight, blankRow) values ('2020-09-25', 'DAC', 'GO1', 22, 4, 4, 0);
insert into trip (date, goid, backid, vehicleId, durationDay, durationNight, blankRow) values ('2020-09-22', 'GO1', 'DAC', 22, 4, 4, 0);
insert into trip (date, goid, backid, vehicleId, durationDay, durationNight, blankRow) values ('2020-09-12', 'GO1', 'GO1', 33, 3, 3, 0);
insert into trip (date, goid, backid, vehicleId, durationDay, durationNight, blankRow) values ('2020-09-25', 'DAC', 'GO1', 22, 4, 4, 0);
insert into trip (date, goid, backid, vehicleId, durationDay, durationNight, blankRow) values ('2020-09-22', 'GO1', 'DAC', 22, 4, 4, 0);
insert into trip (date, goid, backid, vehicleId, durationDay, durationNight, blankRow) values ('2020-09-12', 'GO1', 'GO1', 33, 3, 3, 0);
insert into trip (date, goid, backid, vehicleId, durationDay, durationNight, blankRow) values ('2020-09-25', 'DAC', 'GO1', 22, 4, 4, 0);
insert into trip (date, goid, backid, vehicleId, durationDay, durationNight, blankRow) values ('2020-09-22', 'GO1', 'DAC', 22, 4, 4, 0);
insert into trip (date, goid, backid, vehicleId, durationDay, durationNight, blankRow) values ('2020-09-12', 'GO1', 'GO1', 33, 3, 3, 0);
insert into trip (date, goid, backid, vehicleId, durationDay, durationNight, blankRow) values ('2020-09-25', 'DAC', 'GO1', 22, 4, 4, 0);
insert into trip (date, goid, backid, vehicleId, durationDay, durationNight, blankRow) values ('2020-09-22', 'GO1', 'DAC', 22, 4, 4, 0);
insert into trip (date, goid, backid, vehicleId, durationDay, durationNight, blankRow) values ('2020-09-12', 'GO1', 'GO1', 33, 3, 3, 0);
insert into trip (date, goid, backid, vehicleId, durationDay, durationNight, blankRow) values ('2020-09-25', 'DAC', 'GO1', 22, 4, 4, 0);



insert into vehicle (vehicleId, color, name) values (22, 'Red', 'vehicle1');
insert into vehicle (vehicleId, color, name) values (33, 'Green', 'vehicle2');


SELECT trip.*, vehicle.* FROM trip, vehicle WHERE vehicle.vehicleId=trip.vehicleId ORDER BY date ASC

PHP

<?php
$query = "SELECT trip.*, vehicle.* FROM trip, vehicle WHERE vehicle.vehicleId=trip.vehicleId ORDER BY date ASC";
$stmt = $pdo->prepare($query);
$stmt->execute();

$count = 0;
$tripIdArray = [];
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    $count++;
    print_r($row["name"] . " + other data \n");

    if ($count == 5) {
        print_r("Select Sum query prev page total using another array \n");
        print_r("Select Sum query grand total using tripIdArray \n");
        $count = 0;
        
        print_r("New Page -------------------------- \n");

    }
}

标签： phpmysqlsql

解决方案

$count = 0;
$sumdurday = 0.0;
$tripIdArray = [];
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
  $count++;
  $sumdurday += $row["durationDay"];
  print_r($row["name"] . " + other data \n");

  if ($count == 5) {
    print_r("Select Sum query prev page total using another array \n");
    print_r("Select Sum query grand total using tripIdArray \n");
    
    print_r("sumdurday = ");
    print_r($sumdurday);
    print_r("\n");
    $sumdurday = 0.0;
    $count = 0;
    
    
    
    print_r("New Page -------------------------- \n");

  }
}

无需再次查询总和。在查询中包含要求和的列并计算运行总和，如上面的 $sumdurday。

推荐阅读