时间戳

首页 > 解决方案 > Yii2 优化长时间运行的查询以降低超时

php - Yii2 优化长时间运行的查询以降低超时

问题描述

我试图通过查询获取超过 10000 条记录，但不断超时

   $from ="2018-06-20 23:59";
    $to ="2018-06-28 00:00";
    //generate nairobi plant excell data
    $query = NaiTblTrucks::find()
        ->leftJoin('tbl_truck_history', 'tbl_truck_history.truck_id = tbl_trucks.id')
        //->where(['tbl_trucks.town' => 1])
        ->leftJoin('tbl_security_trucks', 'tbl_security_trucks.truck_id = tbl_trucks.id')
        ->andWhere(['IN', 'tbl_truck_history.status', [1]])
        ->andWhere(['NOT IN', 'tbl_trucks.truck_status', [13,6]])
        ->orderBy(['tbl_security_trucks.time_out' => SORT_DESC]);

    $query->andFilterWhere([
        'OR',
        ['IN',  'tbl_trucks.material',3]
    ]);

    $query->andWhere(['between', 'tbl_security_trucks.time_out', strtotime($from), strtotime($to)]);

现在我使用数据提供者返回结果，比如

 $data = new ActiveDataProvider([
        'query' => $query,
        'sort' => ['defaultOrder' => ['created_at' => SORT_DESC]],
        'pagination' =>false
    ]);

从上面的日期来看，预期的数据可能会非常大，也就是说一天可以有1000多条记录。我也尝试过对请求进行分页

    $totalrecords = $data->getTotals()//dataprovider get totals
    $gotcount = 0;
    $currentpage = 0;
    $fetched = [];

    for ($val= 0; $val<=$totalrecords;){
        $fetchedata = $this->getTrucks($query,10,$currentpage);
        array_push($fetched,$fetchedata);
        $gotcount += count($fetchedata);
        $val +=10;
        $currentpage += 1;
    }

 public function getTrucks($query, $pagesize, $currentPage){
    $data = new ActiveDataProvider([
        'query' => $query,
        'sort' => ['defaultOrder' => ['created_at' => SORT_DESC]],
        'pagination' => [
            'pageSize' => 100,
            'page' => 0
        ],
    ]);

    return $data->getModels();

     }

但上述两次尝试仍然给我超时。通过将日期减少到一天，我可以获得数据。我如何进一步优化它以确保我不会超时。有没有更好的方法来做到这一点，尤其是在处理大型数据集时。

稍后在获取此数据后，我将使用 php 电子表格将其导出到 Excel 中。

标签： phpyii2

解决方案

推荐阅读