猿问

mysql循环查询优化

foreach ($list as $key => $val) {

            $orderId = $val['ORDER_ID'];
            //订单业绩归属人
            $orderPerform = $OrderQueryMod->getOrderPerform($orderId);
            //订单产品信息
            $productInfo = M('order_detail_info')->where(array('ORDER_ID' => $orderId))->select();
            //订单类型：1 正常订单 2 扩容续费 3 压货 4 支付码订单
            switch ($val['ORDER_TYPE']) {
                case 1:
                    $ordertype = '正常订单';
                    break;
                case 2:
                    $ordertype = '扩容续费';
                    break;
                case 3:
                    $ordertype = '压货订单';
                    break;
                case 4:
                    $ordertype = '支付码订单';
                    break;
            }
            foreach ($orderPerform as $k => $v) {
                $num = 0;
                foreach ($productInfo as $kk => $vv) {
                    $num++;
                    $salesOffId = M('consignment_info')->where(array('ORDER_ID' => $orderId))->getField('SALES_OFF');
                    $data[$j]['销售部门'] = D('config_system')->where(array('CONFIG_KEY' => 'SALES_OFF', 'CONFIG_VALUE' => $salesOffId))->getField('CONFIG_DESCRIPTIO');
                    ;
                    $data[$j]['订单号'] = $orderId;
                    $data[$j]['订单标识码'] = $orderId . '-' . $num;
                    $data[$j]['订单金额'] = $vv['INCOME'] / 100;
                    $data[$j]['订单类型'] = $ordertype;
                    $receivedMoney = ($vv['INCOME'] / 100) * (($val['RECEIVED_MONEY'] / 100) / ($val['TOTAL_MONEY'] / 100));
                    $data[$j]['到款金额'] = round($receivedMoney, 2);
                    $data[$j]['成本金额'] = $vv['COST'] / 100;
                    $performBfRebate = ($vv['INCOME'] / 100) * ($val['PERFORM_BF_REBATE'] / ($val['TOTAL_MONEY'] / 100));
                    $data[$j]['返点前业绩'] = round($performBfRebate, 2);
                    $data[$j]['返点比例'] = $val['REBATE_PERCENT'];
                    $data[$j]['是否特价审批单'] = $val['IS_BARGAIN_PRICE'] == 0 ? '非特价' : '特价';
                    $data[$j]['结算方式'] = M('config_system')->where(array('CONFIG_KEY' => 'SETTLEMENT_TYPE', 'CONFIG_VALUE' => $val['PAY_TYPE']))->getField('CONFIG_DESCRIPTIO');
                    $salesmanId = $val['SALESMAN_ID'];
                    $deptId = $v['DEPT_ID'];
                    $data[$j]['部门'] = M('config_dept_info')->where(array('DEPT_ID' => $deptId))->getField('DEPT_NAME');
                    $data[$j]['业绩归属人'] = $v['SALESMAN_NAME'];
                    $data[$j]['业绩分配比例'] = $v['PERCENT'];
                    $saler_info = $OrderQueryMod->GetOrderSaler($val['APPLY_SALER_ID']);
                    $second_saler_info = $OrderQueryMod->GetOrderSaler($val['SECONDARY_SALER_ID']);
                    $data[$j]['一级经销商SAP主数据编号'] = empty($saler_info['SAP_UID']) ? '无' : $saler_info['SAP_UID'];
                    $data[$j]['经销商全称'] = $saler_info['REAL_NAME'];
                    $data[$j]['二级经销商SAP主数据编号'] = empty($second_saler_info['SAP_UID']) ? '无' : $second_saler_info['SAP_UID'];
                    $data[$j]['二级经销商'] = $second_saler_info['REAL_NAME'];
                    $consignerTime = $val['SAP_CONSIGNER_TIME'];
                    $data[$j]['发货日期'] = empty($consignerTime) ? '无' : date('Y-m-d', strtotime($consignerTime));
                    if (empty($consignerTime)) {
                        $data[$j]['季度'] = '';
                        $data[$j]['周起止'] = '';
                    } else {
                        $consignerTimeMonths = date('m', strtotime($consignerTime));
                        if (in_array($consignerTimeMonths, array('01', '02', '03'))) {
                            $data[$j]['季度'] = 1;
                        }
                        if (in_array($consignerTimeMonths, array('04', '05', '06'))) {
                            $data[$j]['季度'] = 2;
                        }
                        if (in_array($consignerTimeMonths, array('07', '08', '09'))) {
                            $data[$j]['季度'] = 3;
                        }
                        if (in_array($consignerTimeMonths, array('10', '11', '12'))) {
                            $data[$j]['季度'] = 4;
                        }
                        $lastday = date('Y-m-d', strtotime("$consignerTime Thursday"));
                        $firstday = date('Y-m-d', strtotime("$lastday -6 days"));
                        $data[$j]['周起止'] = date('m.d', strtotime($firstday)) . '-' . date('m.d', strtotime($lastday));
                    }
                    $finalUser = $OrderQueryMod->GetOrderFinaluser($val['FINAL_USER_ID']);
                    $data[$j]['最终用户名称'] = $finalUser['FINAL_USER_NAME'];
                    $data[$j]['项目类型'] = D('config_system')->where(array('CONFIG_KEY' => 'FINAL_USER_TYPE', 'CONFIG_VALUE' => $finalUser['FINAL_USER_TYPE']))->getField('CONFIG_DESCRIPTIO');;
                    $provinceId = $finalUser['FINALE_USER_PROVINCE'];
                    $areaId = M('config_province_info')->where("PROVINCE_ID = $provinceId")->getField('AREA_ID');
                    $areaName = M('config_area_info')->where("AREA_ID = $areaId")->getField('AREA_NAME');
                    $data[$j]['区域'] = $areaName;
                    $provinceName = M('config_province_info')->where("PROVINCE_ID = $provinceId")->getField('PROVINCE_NAME');
                    $data[$j]['省'] = $provinceName;
                    $cityId = $finalUser['FINALE_USER_CITY'];
                    $cityName = M('config_city_info')->where("CITY_ID = $cityId")->getField('CITY_NAME');
                    $data[$j]['市'] = $cityName;
                    $data[$j]['详细地址'] = $finalUser['FINAL_USER_ADDRESS'];
                    $customType = $finalUser['CUSTOM_TYPE'];
                    $customTypeName = M('customer_type_info')->where("CUSTOMER_TYPE = $customType")->getField('CUSTOMER_TYPE_NAME');
                    $data[$j]['客户类型'] = $customTypeName;
                    $customIndustry = $finalUser['CUSTOMER_INDUSTRY'];
                    $customIndustryName = M('customer_industry_info')->where("CUSTOMER_INDUSTRY_ID = $customIndustry")->getField('CUSTOMER_INDUSTRY_NAME');
                    $data[$j]['客户行业'] = $customIndustryName;
                    $data[$j]['CRM客户编号'] = empty($finalUser['CRM_CUSTOM_ID']) ? '无' : $finalUser['CRM_CUSTOM_ID'];
                    $data[$j]['CRM项目编号'] = empty($val['CRM_PROJECT_ID']) ? '无' : $val['CRM_PROJECT_ID'];
                    $data[$j]['采购联系人'] = $finalUser['PURCHASER'];
                    $data[$j]['采购联系人电话'] = empty($finalUser['PURCHASER_PHONE']) ? '无' : $finalUser['PURCHASER_PHONE'];
                    $data[$j]['采购联系人邮箱'] = $finalUser['PURCHASER_EMAIL'];
                    $data[$j]['IT部/信息部负责人'] = $finalUser['TECHNICIAN'];
                    $data[$j]['IT部/信息部负责人电话'] = empty($finalUser['TECHNICIAN_PHONE']) ? '无' : $finalUser['TECHNICIAN_PHONE'];
                    $data[$j]['IT部/信息部负责人邮箱'] = $finalUser['TECHNICIAN_EMAIL'];
                    $data[$j]['WPS+账号联系人'] = $finalUser['WPS_CONTACTOR'];
                    $data[$j]['WPS+账号联系人电话'] = empty($finalUser['WPS_CONTACTOR_PHONE']) ? '无' : $finalUser['WPS_CONTACTOR_PHONE'];
                    $data[$j]['WPS+账号联系人邮箱'] = $finalUser['WPS_CONTACTOR_EMAIL'];
                    $wpsAccount = M('order_inferior_user')->where(array('ORDER_ID' => $orderId, 'WPS_ACCOUNT_ID' => array('neq', '')))->getField('WPS_ACCOUNT_ID', true);
                    $wpsAccountstr = empty($wpsAccount) ? '' : implode(',', $wpsAccount);
                    $data[$j]['WPS+账号'] = $wpsAccountstr;
                    $data[$j]['支付码'] = M('order_grant_code')->where(array('ORDER_ID' => $orderId))->order('ID DESC')->getField('GRANT_CODE');
                    if ($vv['PRODUCT_ID'] == 60 || $vv['PRODUCT_ID'] == 61 || $vv['PRODUCT_ID'] == 109 || $vv['PRODUCT_ID'] == 110) {
                        $data[$j]['是否赠送服务'] = '否';
                    } else {
                        $data[$j]['是否赠送服务'] = $vv['SERVICE_TYPE'] == '' || $vv['SERVICE_TYPE'] == -1 ? '否' : '是';
                    }
                    $productId = $vv['PRODUCT_ID'];
                    $productCategory = M('config_product_info')->where(array('PRODUCT_ID' => $vv['PRODUCT_ID']))->getField('PRODUCT_CATEGORY');
                    $data[$j]['产品类'] = M('config_system')->where(array('CONFIG_KEY' => 'PRODUCT_CATEGORY', 'CONFIG_VALUE' => $productCategory))->getField('CONFIG_DESCRIPTIO');
                    $data[$j]['物料描述'] = M('config_product_info')->where(array('PRODUCT_ID' => $productId))->getField('PRODUCT_DETAIL_NAME');
                    $productType = M('config_product_info')->where(array('PRODUCT_ID' => $vv['PRODUCT_ID']))->getField('PRODUCT_TYPE');
                    if ($productCategory == 1 && $productType == 1 || $productType == 3) {
                        $serialIds = M('serial_number')->where(array('ORDER_ID' => $orderId, 'SERIAL_NUMBER' => array('neq', '')))->getField('SERIAL_ID', true);
                        $serialTypeStr = '';
                        foreach ($serialIds as $serialId) {
                            $serialTypeInfo = M('serial_number')->where(array('SERIAL_ID' => $serialId))->field('USE_AREA, LANGUAGE_TYPE')->find();
                            $serialTypeStr .= implode(',', $serialTypeInfo) . '|';
                        }
                        $serialTypeStr = str_replace("china", "中国", $serialTypeStr);
                        $serialTypeStr = str_replace("england", "英国", $serialTypeStr);
                        $serialTypeStr = str_replace("japan", "日本", $serialTypeStr);
                        $serialTypeStr = str_replace("chn", "中文", $serialTypeStr);
                        $serialTypeStr = str_replace("enu", "英文", $serialTypeStr);
                        $serialTypeStr = str_replace("jpn", "日文", $serialTypeStr);
                        $data[$j]['序列号类型'] = rtrim($serialTypeStr, '|');
                    } else {
                        $data[$j]['序列号类型'] = '无';
                    }
                    if ($productCategory == 1) {
                        $authorizedType = $vv['AUTHORIZED_TYPE'];
                        $data[$j]['授权类型'] = M('config_system')->where(array('CONFIG_KEY' => 'AUTHORIZED_TYPE', 'CONFIG_VALUE' => $authorizedType))->getField('CONFIG_DESCRIPTIO');
                    } else {
                        $authorizedType = $vv['SPEC'];
                        $data[$j]['授权类型'] = M('config_system')->where(array('CONFIG_KEY' => 'PRODUCT_SPECS', 'CONFIG_VALUE' => $authorizedType))->getField('CONFIG_DESCRIPTIO');
                    }
                    $data[$j]['授权范围'] = $vv['AUTHORIZED_RANGE'];
                    $data[$j]['介质数量'] = $vv['TANN_COUNT'];
                    $data[$j]['授权数量'] = $vv['AMOUNT'];
                    $data[$j]['单价'] = $vv['PRICE'] / 100;
                    $data[$j]['到期后授权数量'] = $vv['EXPIRE_AUTHORIZED_AMOUNT'];
                    $data[$j]['服务年限'] = empty($vv['SERVICE_LENGTH']) ? 0 : $vv['SERVICE_LENGTH'];
                    $data[$j]['服务截止时间'] = empty($vv['SERVICE_LENGTH']) ? date('Y-m-d', strtotime('+0 years 10 days', strtotime($val['APPLY_TIME']))) : date('Y-m-d', strtotime('+' . $vv['SERVICE_LENGTH'] . ' years 10 days', strtotime($val['APPLY_TIME'])));
                    $data[$j]['授权年限'] = empty($vv['AUTHORIZED_YEARS']) ? 0 : $vv['AUTHORIZED_YEARS'];
                    if ($vv['AUTHORIZED_YEARS'] == '永久') {
                        $data[$j]['授权截止时间'] = '永久';
                    } else if ($vv['AUTHORIZED_YEARS'] == '随设备') {
                        $data[$j]['授权截止时间'] = date('Y-m-d', strtotime('+5 years 10 days', strtotime($val['APPLY_TIME'])));
                    } else {
                        $data[$j]['授权截止时间'] = empty($vv['AUTHORIZED_YEARS']) ? date('Y-m-d', strtotime('+0 years 10 days', strtotime($val['APPLY_TIME']))) : date('Y-m-d', strtotime('+' . $vv['AUTHORIZED_YEARS'] . ' years 10 days', strtotime($val['APPLY_TIME'])));
                    }
                    $isFinal = M('saler_info')->where(array('SALER_ID' => $val['APPLY_SALER_ID']))->getField('IS_FINAL');
                    $data[$j]['订方性质'] = $isFinal == 1 ? '最终用户' : '经销商';
                    $data[$j]['供货方'] = M('order_info')->where(array('ORDER_ID' => $orderId))->getField('SALES_ORG');
                    $wuliao = M('config_product_info')->where(array('PRODUCT_ID' => $vv['PRODUCT_ID']))->find();
                    if (empty($wuliao['TAP']) && empty($wuliao['TANN']) && empty($wuliao['TAN'])) {
                        $data[$j]['SAP单号'] = '';
                    } else {
                        $data[$j]['SAP单号'] = empty($val['SAP_SALES_ORDER_ID']) ? '' : (int) $val['SAP_SALES_ORDER_ID'];
                    }
                    $data[$j]['合同编号'] = empty($val['CONTRACT_CODE']) ? '无' : $val['CONTRACT_CODE'];
                    $data[$j]['下单备注'] = $val['DESCRIPTION'];
                    $description = D('order_audit_log')->where(array('AUDIT_TYPE' => 1, 'LOG_STATE' => 1, 'ORDER_ID' => $orderId))->getField('DESCRIPTION');
                    $data[$j]['订单规范检查备注'] = empty($description) ? '' : $description;
                    $data[$j]['订单审核备注'] = D('order_audit_log')->where(array('ORDER_ID' => $orderId, 'AUDIT_TYPE' => 3, 'LOG_STATE' => 1))->getField('DESCRIPTION');
                    $data[$j]['序列号设置备注'] = D('order_audit_log')->where(array('ORDER_ID' => $orderId, 'AUDIT_TYPE' => 14, 'LOG_STATE' => 1))->getField('DESCRIPTION');
                    $data[$j]['打印授权书审核备注'] = D('order_audit_log')->where(array('ORDER_ID' => $orderId, 'AUDIT_TYPE' => 15, 'LOG_STATE' => 1))->getField('DESCRIPTION');
                    $data[$j]['报备审核备注'] = D('order_audit_log')->where(array('ORDER_ID' => $orderId, 'AUDIT_TYPE' => 2, 'LOG_STATE' => 1))->getField('DESCRIPTION');
                    if ($val['INVOICE_OWNER_PROVINCE'] == $val['INVOICE_OWNER_CITY']) {
                        $cityId = $val['INVOICE_OWNER_CITY'];
                        $cityName = M('config_city_info')->where("CITY_ID = $cityId")->getField('CITY_NAME');
                        $data[$j]['发票收件地址'] = $cityName . $val['INVOICE_OWNER_ADDRESS'];
                    } else {
                        $provinceId = $val['INVOICE_OWNER_PROVINCE'];
                        $provinceName = M('config_province_info')->where("PROVINCE_ID = $provinceId")->getField('PROVINCE_NAME');
                        $cityId = $val['INVOICE_OWNER_CITY'];
                        $cityName = M('config_city_info')->where("CITY_ID = $cityId")->getField('CITY_NAME');
                        $data[$j]['发票收件地址'] = $provinceName . $cityName . $val['INVOICE_OWNER_ADDRESS'];
                    }
                    $data[$j]['发票收件人'] = $val['INVOICE_OWNER'];
                    $data[$j]['发票收件人电话'] = empty($val['INVOICE_OWNER_PHONE']) ? '无' : $val['INVOICE_OWNER_PHONE'];
                    $j++;
                }
            }
        }

$list大约有4000左右条数据 打印日志到超时大约有20M的sql日志 每条sql执行时间都在0.00几秒 有什么办法或者思路优化呢？

浮云间
浏览 988回答 3
3回答

慕容森

4000次的循环本身并不大，如果循环里仅仅是对内存的操作其实很快就应该完成，但是你在循环里做了很多次数据库操作，这应该就是造成性能问题的根本原因。尽管每条sql执行都很快，但是你忽略了每次执行所带来的网络io开销时间。我才想4000次的循环里如此多的数据库操作足以是你的脚本超时了，当你所提到超时时，我认为你的php运行在fast cgi模式下。那么你有两种方法来解决1，将sql操作合并，一次或几次在循环之外一口气得到所有的数据，再在循环中进行分门别类。我相信这样做会立竿见影的提升效率。2， 如果这个操作不是及时性的，那么可以尝试放在cli模式下运行，你不用修改代码，尽管效率同样低，但cli模式下脚本不会超时。 另外如果你所获得到数据总量很大，那么还要考虑php本身为脚本所分配的最大可用内存，如果这个值低于你获取的数据所需要的内存，那么即便在cli模式脚本还是得崩。这个配置好像是在php.ini里一个叫max_memory_size定义的，名字可能不准确，我记不太清了
0

青春有我

我一般查两次数据库，将人的信息和订单的信息全部取出（需要的部分哈），然后用PHP对两个数组进行重新运算、组合。一般很快就完成了。而不必要为了每一条数据去查一次数据库。我看到上面为了获取部门还查了一次数据库，感觉很不必要。在项目中一些常用的数据小的可以用配置文件，大的可以用缓存。
0

梵蒂冈之花

sql查询往往是最慢的IO操作，每次sql操作哪怕只是0.001秒，4000循环，每个循环里面有10几个sql操作，这就40秒了。1.所以尽量不要在循环里面做sql操作，可以考虑合并查询请求 $orderPerform = $OrderQueryMod->getOrderPerform($orderId); 可以在循环外批量查询出来 $orderIdArr = array_column($list,'ORDER_ID'); $orderPerformArr = $OrderQueryMod->getOrderPerformArr($orderIdArr); 循环里面直接从数组里面取值2.一些热点数据，尤其是更新还比较少的数据，可以写入到缓存
0
随时随地看视频慕课网APP

相关分类

PHP
php如何把参数放在Http Request Heade？？？？ 1 回答
我要回答