>[info] SQL慢查詢分塊 1. 根據 order_ids <span style='color:red;'><b>注：</b></span>使用 self::find() 時需要加上 asArray() 方可取連表字段。使用 new Query 不用加 asArray() 加了會報錯。  ~~~ /** * 獲取單個訂單某個時間段內的回款情況 * @param $orderIdArr * @param $startTime * @param $endTime * @return array */ public static function getChargeDataByOrderIdArr($orderIdArr, $startTime, $endTime) { sort($orderIdArr); $data = QueryOptimizeTool::queryChunk($orderIdArr, function ($orderIdArr) use ($startTime, $endTime){ return (new Query())->select(' b.sales_id, b.sales_name, b.order_id, b.pay_at as first_pay_at, p.id as pay_log_id, p.trade_no, p.money, p.discount, p.discount_plat, p.has_refund, p.created_at, l.time_start, l.time_end, p.bill_id, l.period_num, o.user_id, f.first_bind_time, b.new_type, o.created_at as order_created_at ')->from('sales_perform_order_bind b') ->leftJoin(V3OrderDao::tableName().' o', 'o.order_id = b.order_id') ->leftJoin('account_pay_log p', 'p.order_id = b.order_id') ->leftJoin('v2_order_bill l', 'p.bill_id = l.id') ->leftJoin('sales_perform_user_firstbind_log f', 'f.user_id = o.user_id') ->where(['between', 'p.created_at', $startTime, $endTime]) ->andWhere(['p.money_type' => 1]) ->andWhere(['in', 'p.order_id', $orderIdArr]) ->orderBy('p.order_id asc') ->all(); }, 500); return $data; } ~~~ ~~~ /** * @param $ids array 需要分割的id集合 * @param $call callable 查詢塊與原來的查詢一致 * @param $limit int 分割塊大小，默認1000 * @return array; */ public static function queryChunk($ids, $call, $limit = 1000) { !is_array($ids) && $ids = [$ids]; // 數據分割優化慢查 $data = []; $chunkIds = array_chunk($ids, $limit); foreach ($chunkIds as $chunkId) { $tmpData = $call($chunkId); // 保留key，重復的直接加入 foreach ($tmpData as $key => $value){ if (!array_key_exists($key, $data)){ $data[$key] = $value; } else { $data[] = $value; } } } return $data; } ~~~ 2. 根據時間拆分 <span style='color:red;'><b>注：</b></span>splitBetweenTime 必須使用 self::find()、不能使用 new Query，并且需加上 asArray() 才能取到關聯表字段。 ~~~ /** * 根據時間區域獲取綁定訂單(普通訂單、續租訂單) * @param $startTime * @param $endTime * @param $saleId * @param $chargeTimeType * @param $orderStatusArr * @return array|ActiveRecord[] */ public static function getOrderDataByTime($startTime, $endTime, $saleId = null, $chargeTimeType = null, $orderStatusArr = null): array { // 數據列表 $dataList = []; // 拆分天數 $step = 1; // 根據時間維度獲取數據集 foreach (Helper::splitBetweenTime($startTime, $endTime, 24 * 3600 * $step) as $time) { $tmp = V3OrderDao::find()->select(' b.sales_id, b.sales_name, b.order_id, b.order_type, b.refund_money, b.new_type, o.rental_need_pay, o.rental_has_refund, o.order_status, b.pay_at, sb.charge_time_type, o.user_id, o.created_at ')->from(self::tableName())->alias('b') ->leftJoin(V3OrderDao::tableName() . ' o', 'b.order_id = o.order_id') ->leftJoin(SalesPerformOrderBillDao::tableName() . ' sb', 'b.first_trade_no=sb.trade_no') ->where(['b.order_type' => [ DicSalesPerformOrderBind::ORDER_TYPE_ORDINARY, DicSalesPerformOrderBind::ORDER_TYPE_RENEWAL, DicSalesPerformOrderBind::ORDER_TYPE_REPAIR_RENT, DicSalesPerformOrderBind::ORDER_TYPE_NEW] ]) ->andWhere(['between', 'b.pay_at', $time['start'], $time['end']]) ->andFilterWhere(['b.sales_id' => $saleId]) ->andFilterWhere(['sb.charge_time_type' => $chargeTimeType]) ->andFilterWhere(['o.order_status' => $orderStatusArr]) ->asArray() ->all(); // 每次查詢合并 $dataList = array_merge($dataList, $tmp); } // 返回 return $dataList; } ~~~ ~~~ /** * 根據步長切分時間段, 一般between時間查詢范圍過大時使用 * @param int $startTime 開始時間 * @param int $endTime 結束時間 * @param int $step 切分時間步長(秒) * @return \Generator 返回時間段 [['start'=> xxx, 'end' => xxx], ['start'=> xxx, 'end' => xxx]] */ public static function splitBetweenTime($startTime, $endTime, $step) { // 不正確的時間 if ($startTime > $endTime) { return; } if ($startTime + $step >= $endTime) { yield ['start' => $startTime, 'end' => $endTime]; return; } // $times = []; // 循環開始時間 $loopStartTime = $startTime; // 標記處理完成 $isBreak = false; while (true) { $loopEndTime = $loopStartTime + $step; if ($loopEndTime >= $endTime) { $item = ['start' => $loopStartTime, 'end' => $endTime]; $isBreak = true; } else { // 結束時間減1, 避免between查詢endTime和startTime重復 $item = ['start' => $loopStartTime, 'end' => $loopEndTime - 1]; } // $times[] = $item; // 避免爆數組 yield $item; if ($isBreak) { break; } $loopStartTime = $loopEndTime; } } ~~~ 3. 條數限制查詢 <span style='color:red;'><b>注：</b></span>queryLimit 必須使用 new Query() 例：在開發服字段存在，上線后丟字段（server_ratio） ~~~ /** * 根據訂單狀態（區間）獲取當前綁定所有訂單 * * @param array $where 條件 * @param null $fields 字段 * @return array * @author 王乾順 * @time 2024-01-22 19:11:21 */ public static function getOrderDataByOrderStatusBetween(array $where, $fields = null): array { // 字段 if (empty($fields)) { $fields = 'b.id,b.order_id,o.order_status'; } // 查詢 $query = (new Query())->from('sales_perform_order_bind as b') ->select($fields) ->alias('b') ->leftJoin(V3OrderDao::tableName() . ' o use index(idx_orderid_orderstatus)', 'b.order_id = o.order_id') ->where(['between', 'o.order_status', $where['start_order_status'], $where['end_order_status']]); // 訂單類型 if (isset($where['order_type'])) { $query->andFilterWhere(['b.order_type' => $where['order_type']]); } // 銷售id if (isset($where['sales_id'])) { $query->andFilterWhere(['b.sales_id' => $where['sales_id']]); } // 排序 $query->orderBy('b.id'); // 分塊查詢 return QueryOptimizeTool::queryLimit($query, function ($model, $data) { $model->andWhere(['>', 'b.id', $data['id']]); }, 500); } /** * 獲取某個銷售人某個月份的回款關聯訂單比例 * @param string $chargeMonth 月份 * @param int $saleId 銷售ID * @return array * @author 魏梓康 * @date 2021-06-08 */ public static function getSettleDataByMonthAndSaleId($chargeMonth, $saleId) { $query = (new Query())->from('sales_perform_order_bill as b') ->select('b.id, b.sales_id, b.sales_name, b.charge_money, b.charge_time_type, b.charge_month, r.server_ratio, b.order_id') ->leftJoin('account_order_ratio as r', 'r.order_id = b.order_id') ->where(['=', 'b.charge_month', $chargeMonth]) ->andWhere(['b.sales_id' => $saleId]) ->andWhere(['b.bill_type' => [ // 普通訂單支付流水 DicSalesPerformOrderBill::BILL_ORDINARY_ORDER, // 補訂單續租支付流水 DicSalesPerformOrderBill::BILL_RENEWAL_ORDER, // 補訂單補租金支付流水 DicSalesPerformOrderBill::BILL_REPAIR_RENT, // 補訂單新訂單支付流水 DicSalesPerformOrderBill::BILL_NEW_ORDER ]]); // 分塊查詢 return QueryOptimizeTool::queryLimit($query, function ($model, $data) { $model->andWhere(['>', 'b.id', $data['id']]); }, 500); } ~~~ ~~~ /** * @param $model * @param $call callable 補充模型起始id條件 * @param $limit int 分割塊大小，默認1000 * @return array; */ public static function queryLimit($model, $call, $limit = 1000) { $data = []; $endData = []; while (true) { $tmpModel = clone $model; $endData && $call($tmpModel, $endData); $tmpData = $tmpModel->limit($limit)->all(); $tmpData && $data = array_merge($data, $tmpData); if (!$tmpData) { return $data; } $endData = end($tmpData); } return $data; } ~~~