mysql 循环查询 慢_mysql循环查询优化

最新推荐文章于 2023-03-16 14:38:03 发布
最新推荐文章于 2023-03-16 14:38:03 发布
阅读量420 收藏
点赞数
文章标签： mysql 循环查询 慢
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_28951059/article/details/113228034
版权

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几秒 有什么办法或者思路优化呢？

weixin_28951059
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
Mysql查询优化
wanghang96的博客
09-24 175
一、概述 查询优化是一个非常大的话题，它可以从表设计，索引，查询技巧等多个方面去讨论，对于表设计，索引已经有专门的文章介绍（Mysql高效索引策略, Mysql库表设计优化）。本文将介绍查询优化的思路和一些实用的查询优化策略。 二、如何执行查询 1、整体流程 1.客户端发送查询请求给服务器。 Mysql客户端/服务端通信协议时半双工的，任意一个时间点，只有一方发送数据，发送请求后就只能等待结果；另一方接收数据，只有接受完数据才能响应结果。客户端使用一个单独的数据包发送给服务器，当查询语句超出max_allo
mysql慢查询优化
chenaini119的专栏
05-12 560
如下表： CREATE TABLE "goods_relation_inherit_goods_flow" ( "id" bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键', "mall_flow_id" bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT 'mall_flow_id...
如何解决mysql 查询和更新速度慢
ocjust的专栏
11-25 7452
在做客户关系管理系统的时候遇到联表查询，速度特别慢，导致页面加载时间过长而出现错误。在上网查询后发现建立索引可以优化查询 在没有建立索引的时候 select c.*,s.* from crm_cu_re c join crm_cu_info s on c.CUS_MAIN_ID=s.CUS_MAIN_ID) 查询结果 (526 row(s) returned) Total Time
for循环查询数据库优化（挺好用的）
m0_47944994的博客
07-15 1万+
for循环查询数据库优化（挺好用的）
mysql查询缓慢原因和解决方案
忘川
03-16 1万+
查询速度慢的原因很多，常见如下几种：1、没有索引或者没有用到索引(这是查询慢最常见的问题，是程序设计的缺陷) 2、I/O吞吐量小，形成了瓶颈效应。 3、没有创建计算列导致查询不优化。 4、内存不足 5、网络速度慢 6、查询出的数据量过大（可以采用多次查询，其他的方法降低数据量） 7、锁或者死锁(这也是查询慢最常见的问题，是程序设计的缺陷) 8、sp_lock,sp_who,活动的用户查看,原因是读写竞争资源。 9、返回了不必要的行和列 10、查询语句不好，没有优化 可以通过如下方法来优化查询 : 1、把数据
简单谈谈MySQL优化利器-慢查询
12-15
总的来说，MySQL的慢查询优化是一项系统性的工程，涉及到SQL语句编写、索引设计、服务器配置等多个方面。通过对慢查询日志的深入分析，我们可以逐步找到性能瓶颈，并采取相应措施进行改进。同时，不断学习和掌握新的...
优化MySQL数据库中的查询语句详解
09-10
本文将详细解析如何优化MySQL查询语句，以提高数据库的执行效率。 首先，避免请求不必要的数据。这包括： 1. 限制查询结果的数量。如果只需要10条数据，应避免使用`SELECT *`并加上`LIMIT`限制，而是明确指定所需...
MySQL循环语句之while循环测试
12-15
在MySQL中，有三种标准的循环结构：`WHILE`循环、`REPEAT`循环和`LOOP`循环，而`GOTO`语句虽然可用，但因其可能导致代码难以理解和维护，一般不推荐使用。 **1. WHILE循环** `WHILE`循环基于一个条件来决定是否继续...
MYSQL—查询优化的基本思路和原则
12-14
一、常用优化的原则 永远用小结果集驱动大结果集 尽可能在索引中完成排序 只取出自己需要的列 仅仅使用最有效的过滤条件 尽可能避免复杂的join和子查询 多使用profile 1. 在这里需要严格的说是小结果集驱动大结果集，而不能用人们通常所说的小表驱动大表。因为在where条件过滤后大表数据未必就比小表多，反而适得其反。因为在mysql的join中是通过嵌套循环来实现的，驱动结果集越大，所需要循环的次数就越多。跟就会导致被驱动表的访问次数增多，即使需要的IO很少但是次数多了总量影响也不可能很小更会导致CPU运算量跟着增加。所以在优化Join SQL时最基本的原则就是小结果集驱动大结果集。通过这
mysql关联子查询的一种优化方法分析
09-10
这种查询方式在处理大数据量时可能会变得非常慢，因为每次外部查询的循环都需要执行内部查询。 例如，考虑以下存在性子查询： ```sql SELECT * FROM table1 WHERE EXISTS (SELECT * FROM table2 WHERE id >= 30000...
循环中查询mysql问题_循环查询数据库的性能问题及优化
weixin_33291684的博客
01-28 1465
问题背景：在for循环中，不断访问数据库查询数据。代码如下：优化前数据库查询语句：private static String selectSql = "select * from captain.resourceAccessSetting where backupProId = ?";优化前代码：for(RecordInfo recordInfo : recordInfos) {int id =r...
mysql高级海量数据存储和优化_mysql海量数据的优化
weixin_34921609的博客
01-19 130
转载：https://www.cnblogs.com/trying/archive/2013/08/15/3259126.html下面是一部分比较重要的建议：1、选择正确的存储引擎以 MySQL为例，包括有两个存储引擎 MyISAM 和 InnoDB，每个引擎都有利有弊。MyISAM 适合于一些需要大量查询的应用，但其对于有大量写操作并不是很好。甚至你只是需要update一个字段，整个表都会被锁起...
mysql循环查优化_mysql循环查询优化
weixin_33228769的博客
02-05 554
foreach ($list as $key => $val) {$orderId = $val['ORDER_ID'];//订单业绩归属人$orderPerform = $OrderQueryMod->getOrderPerform($orderId);//订单产品信息$productInfo = M('order_detail_info')->where(array('ORD...
循环查询数据库的性能问题及优化
热门推荐
05-27 1万+
问题背景：在for循环中，不断访问数据库查询数据。代码如下：优化前数据库查询语句：private static String selectSql = "select * from captain.resourceAccessSetting where backupProId = ?";优化前代码：for (RecordInfo recordInfo : recordInfos) { ...
mysql for循环_高性能Mysql-查询优化（3）
weixin_39677106的博客
12-03 375
数据和索引的统计信息1.mysql执行优化器在生成查询执行计划时，需要向存储引擎获取相应的统计信息。mysql如何执行关联查询2.mysql认为任何一个查询都是一次关联(并不是查询需要用到两个表匹配才叫关联)3.mysql对任何关联都执行嵌套循环关联操作。(类似于两个for 循环嵌套逐一进行比较，将h)4.mysql对所有的类型查询都是以相同的方式运行。mysql在from 子句中遇到了子查询，先...
mysql循环查询数据
最新发布
06-07
在 MySQL 中，可以使用循环语句（如 WHILE、REPEAT、LOOP）来进行数据的循环查询。以下是一个简单的示例，使用 WHILE 循环查询表中的数据： ``` DECLARE i INT DEFAULT 0; WHILE i SELECT * FROM my_table WHERE ...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值