>[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; } ~~~