mysql语句查询时间过长如何优化
select
b_appl_entity_info.main_appl_seq mainApplSeq, -- 订单号
b_mortgage_cert_info.mortgage_cert_seq mortgageCertSeq, -- 抵押流水号
b_appl_asset.asset_seq assetSeq, -- 申请车辆信息流水号
b_mortgage_cert_info.mortgage_status mortgageStatus, -- 抵押状态
b_mortgage_cert_info.register_cert_status registerCertStatus, -- 登记证书状态
b_appl_entity_info.bis_commissioner_name ywzyName, -- 业务员姓名
b_appl_entity_info.customer_name customerName, -- 客户姓名
b_appl_asset.vin vin , -- 车架号
b_mortgage_cert_info.deviant_cert_reason deviantCertReason , -- 登记证书异常原因
b_mortgage_cert_info.deduction deduction, -- 是否已扣款
b_mortgage_cert_info.reissue reissue , -- 是否已补发
b_mortgage_cert_info.mailing_time mailingTime, -- 邮寄时间
b_mortgage_cert_info.recipient recipient, -- 收件人
b_mortgage_cert_info.reg_cert_recover_time regCertRecoverTime, -- 登记证书回收时间
b_mortgage_cert_info.mortgage_handler mortgageHandler, -- 抵押办理人
b_mortgage_cert_info.plate_no plateNo, -- 车牌号
b_mortgage_cert_info.veh_holder vehHolder, -- 机动车所有人
b_mortgage_cert_info.egister egister, -- 登记机关
b_mortgage_cert_info.register_cert_no registerCertNo, -- 登记证书编号
b_mortgage_cert_info.mortgagor mortgagor, -- 抵押方
b_mortgage_cert_info.mortgage_amount mortgageAmount, -- 办理抵押费用
b_mortgage_cert_info.mortgage_remark mortgageRemark, -- 无法办理抵押原因
b_mortgage_cert_info.mortgage_date mortgageDate, -- 抵押日期
b_mortgage_cert_info.release_date signDate, -- 解押日期
DATE_FORMAT(b_mortgage_cert_info.create_datetime,"%Y-%m-%d %H:%i:%s")
entryTime, -- 录入时间
b_appl_entity_info.p1 zfContract,
b_appl_entity_info.customer_phone customerPhone,
baca.detailed_address customerLocation,
b_appl_entity_info.funder_name platformName,
(select dict_name_cn from sys_dictionary where dict_code=bac.sign_mode and dict_type='QYMS') signMode,
CASE WHEN b_appl_entity_info.customer_type = 'Individual' THEN '个人客户' ELSE '企业客户' END customerType,
b_appl_asset.brand_name_cn brandNameCn,
b_appl_asset.series_name_cn modelNameCn,
bap.total_veh_price vehPrice,
bap.downpayment_pct downpaymentPct,
bap.financing_amt financingAmt,
b_appl_asset.company,
(select user_name from b_special_person where main_appl_seq = b_appl_entity_info.main_appl_seq and role_id ='BDZY') reporterName,
b_appl_entity_info.create_datetime incomingDate,
b_pymt_appl_detail.pymt_time loanTime, -- 放款时间
TIMESTAMPDIFF(SECOND, NOW(), date_add(b_pymt_appl_detail.pymt_time, interval 15 day)) remainingDays, -- 登记证书接收剩余天数
(select user_name from b_special_person where main_appl_seq = b_appl_entity_info.main_appl_seq and role_id ='FKZY') riskPerson , -- 风控人员
(CASE WHEN ISNULL(boi.main_appl_seq) THEN '否' else '是' END) AS beOverdue -- 当前是否逾期
from b_appl_entity_info
left join b_appl_info on b_appl_entity_info.main_appl_seq = b_appl_info.main_appl_seq
left join b_appl_info baiif on b_appl_entity_info.main_appl_seq = baiif.main_appl_seq AND baiif.appl_type ='DQSQ'
left join b_appl_asset on b_appl_asset.main_appl_seq = b_appl_entity_info.main_appl_seq
left join b_mortgage_cert_info on b_appl_entity_info.main_appl_seq = b_mortgage_cert_info.main_appl_seq
left join b_pymt_appl_detail on b_pymt_appl_detail.pymt_appl_seq = b_appl_info.appl_seq
LEFT JOIN b_appl_cdd bac on bac.appl_seq = baiif.appl_seq
LEFT JOIN b_appl_plan bap on bap.main_appl_seq = b_appl_entity_info.main_appl_seq
LEFT JOIN b_appl_cust_address baca on baca.main_appl_seq = b_appl_entity_info.main_appl_seq and address_type = 'SFZDZ' and person_type='ZDR'
LEFT JOIN(SELECT
main_appl_seq,
amount_due,
amount_paid,
deduction_date,
actual_date,
overdue_info_seq,
row_number () over (
PARTITION BY main_appl_seq
ORDER BY
deduction_date DESC
) rowid
FROM
b_overdue_info
WHERE
IFNULL(amount_due, 0) - IFNULL(amount_paid, 0) > 0) boi ON boi.main_appl_seq = b_appl_entity_info.main_appl_seq AND boi.rowid = '1'
where 1=1
and b_appl_info.appl_type ='FKSQ'
and b_appl_info.appl_status = 'YFK'这条sql时间27s左右，有没有办法优化（索引已加）
ps：查出来的大概10000条数据
看下执行计划呢
您好，我是问答小助手，你的问题已经有小伙伴为您解答了问题，您看下是否解决了您的问题，可以追评进行沟通哦~
如果有您比较满意的答案 / 帮您提供解决思路的答案，可以点击【采纳】按钮，给回答的小伙伴一些鼓励哦～～
ps:问答VIP仅需29元，即可享受5次/月 有问必答服务，了解详情>>> https://vip.csdn.net/askvip?utm_source=1146287632