记录一个超长sql的优化，从4s到0.0015s
21 浏览量
2020-12-14
12:02:52
上传
评论
收藏 428KB PDF 举报
记录一个超长记录一个超长sql的优化，从的优化，从4s到到0.0015s
TIPS：Mysql版本8.0.19
废话不多说，直入正题，先来看看我所说的超长sql：
SELECT
my.id,
my.date,
my.business_date AS businessDate,
my.CODE,
my.customer_id AS customerId,
cc.NAME AS customerName,
cc.boss_name AS bossName,
my.contact_person_id AS contactPersonId,
ccp.NAME AS contactPersonName,
my.department_id AS departmentId,
dep.NAME AS departmentName,
csopd.NAME AS saleOrderDepartmentName,
parentpd.NAME AS parentDepartmentName,
parentcsopd.NAME AS saleOrderParentDepartmentName,
ca.agent_type AS agentType,
csoca.agent_type AS saleOrderAgentType,
my.achievement_type AS achievementType,
my.audit_status AS auditStatus,
my.type,
my.order_type AS orderType,
my.quote_money AS quoteMoney,
my.offset_money AS offsetMoney,
my.receipt_money AS receiptMoney,
my.amount,
my.create_by AS createBy,
su.NAME AS createByName,
my.counselor,
coun.NAME AS counselorName,
my.course_id AS courseId,
course.NAME AS courseName,
achi.NAME AS achievementDepartmentName,
my.create_time AS createTime,
ifnull(
IF
(
0 > csod.all_money - sum(csod.refund_money) - sum( csod.delivery_money ),
0,
csod.all_money - sum( csod.refund_money ) - sum( csod.delivery_money )),
0
) AS unDeliveryMoney,
ifnull(
IF
(
0 > csod.all_money - sum(csod.refund_money ) - sum( csod.settle_money ),
0,
csod.all_money - sum( csod.refund_money ) - sum( csod.settle_money )),
0
) AS unSettleMoney,
ifnull( sum( csod.delivery_money ), 0 ) AS deliveryMoney,
ifnull( sum( csod.settle_money ), 0 ) AS settleMoney,
sum( csod.amount * csod.money ) AS detailTotalMoney,
cc.market_manage AS marketManage,
mm.NAME AS marketManageName,
cc.server_manage AS serverManage,
sm.NAME AS serverManageName,
cc.invite_manage AS inviteManage,
im.NAME AS inviteManageName,
my.order_market_manage AS orderMarketManage,
omm.NAME AS orderMarketManageName,
my.order_server_manage AS orderServerManage,
osm.NAME AS orderServerManageName,
my.market_director AS marketDirector,
md.NAME AS marketDirectorName,
my.server_director AS serverDirector,
sd.NAME AS serverDirectorName,
au.NAME AS developManageName,
csodm.NAME AS saleOrderDevelopManageName,
IF
( coc.id IS NULL, 1, 2 ) AS orderContractStatus,
coc.audit_status AS orderContractAuditStatus,
my.extend_teacher_id AS extendTeacherId,
(SELECT NAME FROM pub_department WHERE id=my.extend_teacher_id) AS extendTeacherName,
IF
( ecd.sign_status IS NULL, 0, ecd.sign_status ) AS esignContractSignStatus
FROM
crm_sale_order my
LEFT JOIN crm_customer cc ON cc.id = my.customer_id
LEFT JOIN crm_contact_person ccp ON ccp.id = my.contact_person_id
LEFT JOIN pub_department dep ON dep.id = cc.department_id
LEFT JOIN pub_department csopd ON csopd.id = my.department_id
LEFT JOIN pub_department parentpd ON parentpd.id = dep.parent_id
LEFT JOIN pub_department parentcsopd ON parentcsopd.id = csopd.parent_id
LEFT JOIN sys_user su ON su.id = my.create_by
LEFT JOIN sys_user coun ON coun.id = my.counselor
LEFT JOIN crm_course course ON course.id = my.course_id
LEFT JOIN pub_department achi ON achi.id = my.achievement_department
LEFT JOIN crm_sale_order_detail csod ON csod.sale_order_id = my.id
AND csod.deleted = 0
LEFT JOIN sys_user mm ON mm.id = cc.market_manage
LEFT JOIN sys_user sm ON sm.id = cc.server_manage
LEFT JOIN sys_user im ON im.id = cc.invite_manage
LEFT JOIN sys_user omm ON omm.id = my.order_market_manage
LEFT JOIN sys_user osm ON osm.id = my.order_server_manage
LEFT JOIN sys_user md ON md.id = my.market_director
LEFT JOIN sys_user sd ON sd.id = my.server_director
LEFT JOIN crm_agent ca ON ca.department_id = cc.department_id
LEFT JOIN crm_agent csoca ON csoca.department_id = my.department_id
LEFT JOIN sys_user au ON au.id = ca.develop_manage
LEFT JOIN sys_user csodm ON csodm.id = csoca.develop_manage
LEFT JOIN crm_order_contract coc ON coc.sale_order_id = my.id
AND coc.deleted = 0
LEFT JOIN esign_contract ec ON ec.sale_order_id = my.id
LEFT JOIN esign_contract_detail ecd ON ecd.contract_id = ec.id
AND ecd.deleted = 0
AND ecd.record_type = 2
WHERE
my.deleted = 0
GROUP BY
资源评论
weixin_38635449
- 粉丝: 5
- 资源: 971
最新资源
- 2022年各城市PM2.5, PM10, SO2, NO2等环境空气质量数据
- Golang：通过Gin框架+Redis+责任链，实现一个简单的钉钉机器人，进行消息处理 ps:多应用版
- 2021年各城市PM2.5, PM10, SO2, NO2等环境空气质量数据
- CORRUPT.navicat150-premium-cs-x64.exe
- centos7 ssh 升级至 9.6p1
- DriverMax Pro .exe
- PHP端通过modbus协议跟第三方设备进行数据通信
- navicat安装包亲测可用
- 算法部署-使用OpenVINO部署MobileStyleGAN轻量化高保真图像合成算法-项目源码-优质项目实战.zip
- 基于java实现远程采集华为逆变器使用modbus tcp协议进行通讯的设备数据
资源上传下载、课程学习等过程中有任何疑问或建议，欢迎提出宝贵意见哦~我们会及时处理！
点击此处反馈
