减少 SQL 语句数量使性能提升 10 倍

原方案：

/**
     * 累计销量
     */
    public function totalSales()
    {
        $uid = User::getUserId();

        if ($uid === false) {
            return JsonService::successful('error', [
                    'need_login' => 1,
                    'info' => self::LOGIN_TIP
            ]);
        }

        //$total_fee = Cache::get("{$uid}_totalSales_total_fee");
        $total_fee = false;
        if ($total_fee === false) {
            try {
                
                $user_list = sellUser::list($uid);

                if ($user_list === false) {
                    throw new ApiException(self::ERROR_TIP, 0x3e1999dd);
                }

                $total_fee = 0.00;

                foreach ($user_list as $user) {
                    $fee = Fee::paidTotalCount(
                        $user['uid'],
                        0,
                        0
                    );

                    if ($fee === false) {
                        throw new ApiException(self::ERROR_TIP, 0x6f1e0685);
                    }
                    $total_fee = $total_fee + $fee;
                }
                
                //$total_fee = Fee::totalSales($uid);

                /*
                Cache::set(
                    "{$uid}_totalSales_total_fee",
                    sprintf("%.0f", $total_fee),
                    3600+mt_rand(-600, 600)
                );*/
            } catch (\Exception $e) {
                Log::write([
                    'exception_message' => $e->getMessage(),
                    'code' => '0x' . dechex($e->getCode()),
                    'file' => __FILE__,
                    'line' => __LINE__
                ], 'notice');

                return json([
                    'code' => '0x' . dechex($e->getCode()),
                    'msg' => self::ERROR_TIP,
                    'data' => []
                ]);
            }
        }

        return json([
            'code' => 200,
            'msg' => 'success',
            'data' => $total_fee
        ]);
    }

/**
     * 登录用户在线支付的总金额（不含退款）。
     *
     * @param int $uid 付款用户
     * @param int $pay_start_time 开始时间（支付）
     * @param int $pay_end_time 结束时间（支付）
     *
     * @return float|bool 查询成功返回金额合计，否则返回false
     */
    public static function paidTotalCount(
        $uid = 0,
        $pay_start_time = 0,
        $pay_end_time = 0
    ) {
        $circle_sql = '';
        $recharge_sql = '';
        $good_sql = '';
        $yue_sql = '';

        $bind_param = [];
        $bind_param[] = $uid;

        if ($pay_start_time > 0 && $pay_end_time > 0) {
            //$circle_sql = ' AND co_pay_at>=? AND co_pay_at<?';
            //$recharge_sql = ' AND pay_time>=? AND pay_time<?';
            $good_sql = ' AND pay_time>=? AND pay_time<? ';
            //$yue_sql = ' AND yo_pay_at>=? AND yo_pay_at<?';

            $bind_param[] = $pay_start_time;
            $bind_param[] = $pay_end_time;
        }

        try {
            //圈子订单
            /*
            $sum_circle = Db::query("
                SELECT IFNULL(sum(co_actual_pay-co_refund_amount), 0) AS total_amount FROM osx_circle_order
                WHERE co_uid=? $circle_sql AND co_del_at=0 AND co_pay_status=1;
            ", $bind_param);

            if ($sum_circle === false) {
                throw new DbException(self::QUERY_FAIL, [], Db::getLastSql());
            }
            */

            //充值订单
            /*
            $sum_recharge = Db::query("
                SELECT IFNULL(sum(price-refund_price), 0) AS total_amount FROM osx_user_recharge
                WHERE uid=? $recharge_sql and paid=1;
            ", $bind_param);

            if ($sum_recharge === false) {
                throw new DbException(self::QUERY_FAIL, [], Db::getLastSql());
            }
            */

            //商品订单

            //原SQL SELECT IFNULL(sum(pay_price-refund_price), 0) AS total_amount FROM osx_store_order
            $sum_good = Db::query("
                SELECT IFNULL(sum(pay_price), 0) AS total_amount FROM osx_store_order                
                WHERE uid=? $good_sql and paid=1;
            ", $bind_param);

            if ($sum_good === false) {
                throw new DbException(self::QUERY_FAIL, [], Db::getLastSql());
            }

            //悦订单
            /*
            $sum_yue = Db::query("
                SELECT IFNULL(sum(yo_actual_pay-yo_refund_amount), 0) AS total_amount FROM osx_yue_order
                WHERE yo_uid=? $yue_sql and yo_pay_status=1 AND yo_type=1;
            ", $bind_param);

            if ($sum_yue === false) {
                throw new DbException(self::QUERY_FAIL, [], Db::getLastSql());
            }*/
        } catch (\Exception $e) {
            Log::write([
                'exception_message' => $e->getMessage(),
                'last_sql' => Db::getLastSql(),
                'file' => __FILE__,
                'line' => __LINE__
            ], 'notice');
            return false;
        }

        return
            //($sum_circle[0]['total_amount'] >= 0 ? $sum_circle[0]['total_amount'] : 0)
        //+ ($sum_recharge[0]['total_amount'] >= 0 ? $sum_recharge[0]['total_amount'] : 0)
        //+
        ($sum_good[0]['total_amount'] >= 0 ? $sum_good[0]['total_amount'] : 0);
        //+ ($sum_yue[0]['total_amount'] >= 0 ? $sum_yue[0]['total_amount'] : 0);
    }

/**
     * 被介绍人列表
     *
     * @param int $uid 介绍人的uid
     *
     * @return array|bool 查询成功返回二维数组或空数组。否则返回false
     *
     * [
     *    ["uid" => 2],
     *    ["uid" => 13],
     *    ["uid" => 698]
     * ]
     */
    public static function list($uid = 0)
    {
        try {
            $q_list = Db::query("
                SELECT uc_uid AS uid FROM osx_user_character AS o_u_c
                WHERE uc_superior=? AND uc_del_at=0;
            ", [$uid]);

            if ($q_list === false) {
                throw new DbException(self::QUERY_FAIL, [], Db::getLastSql());
            }
        } catch (\Exception $e) {
            Log::write([
                'exception_message' => $e->getMessage(),
                'last_sql' => Db::getLastSql(),
                'file' => __FILE__,
                'line' => __LINE__
            ], 'notice');
            return false;
        }

        return $q_list;
    }

 

接口耗时 453 毫秒。

新方案：

/**
     * 累计销量
     *
     * @param int $uid 登录用户
     *
     * @return string|false 查询成功返回金额，失败返回false
     *
     * @throws \think\Exception\DbException
     */
    public static function totalSales($uid = 0)
    {
        $qTotalSales = Db::query('
            select
	            IFNULL(sum(oso.pay_price-oso.refund_price), 0) as total_amount
            from
	            osx_user_character ouc
            inner join osx_store_order oso on
	            ouc.uc_uid = oso.uid
            where
	            ouc.uc_superior = ?
	        and oso.paid = 1;
        ', [$uid]);

        if ($qTotalSales === false) {
            throw new DbException(self::QUERY_FAIL, [], Db::getLastSql());
        }

        return $qTotalSales[0]['total_amount'] ?? 0;
    }

 接口耗时 46 毫秒