You need to enable JavaScript to run this app. 如何使用索引优化MySQL复杂查询 1.1万条记录执行56秒求优化方案
优惠活动
大模型
产品
解决方案
定价
更多

如何使用索引优化MySQL复杂查询 1.1万条记录执行56秒求优化方案

优化方案

现有SQL的核心问题

  • 日期字段上使用YEAR()、MONTH()函数,导致无法命中索引,每次查询都要全表扫描计算日期
  • 筛选条件没有下推,先把两个贷款表全量数据UNION ALL后才做过滤,额外处理了大量无效数据
  • 语法存在括号缺失错误:第一个MONTH(CURRENT_DATE) IN (...)后缺少右括号,导致后续条件逻辑判断错误
  • 原逻辑是满足两个筛选条件其一即可,当前写法会先关联全量数据再做判断,开销极大

索引添加建议

三个表分别添加以下联合索引,覆盖所有筛选、关联字段,实现索引覆盖查询无需回表:

  • topup_or_reapplication_tbl 加联合索引:(ippis, current_loan, status, date_created, date_updated)
  • loan_applications_tbl 加联合索引:(current_loan, loan_status, date_created, date_updated, ippis)
  • loan_applications_tbl_dump 加联合索引:(current_loan, loan_status, date_created, date_updated, ippis)

重写后的SQL

先提前计算好当月的起止日期,避免对每行数据都做重复的日期函数计算,同时把筛选条件下推到子查询,提前过滤无效数据:

-- 提前计算当前月的第一天和下个月第一天,用于范围查询可命中索引
SET @current_month_start = DATE_FORMAT(CURRENT_DATE, '%Y-%m-01');
SET @next_month_start = DATE_ADD(@current_month_start, INTERVAL 1 MONTH);

SELECT 
    t3.*, 
    t.loan_id, 
    t.user_unique_id AS tu_user_unique_id, 
    t.ippis AS tu_ippis, 
    t.top_up_approved, 
    t.loan_type AS tu_loan_type, 
    t.dse, 
    t.status, 
    t.current_loan AS tu_current_loan, 
    t.record_category, 
    t.date_created AS tu_date_created, 
    t.date_updated AS tu_date_updated
FROM (
    -- 下推筛选条件,先只取符合t3侧要求的数据
    SELECT 
        id, user_unique_id, loan_location, ippis, tel_no,
        organisation, branch, loan_agree, loan_type, appr, sold, 
        loan_status, top_up, current_loan, date_created, date_updated 
    FROM loan_applications_tbl 
    UNION ALL
    SELECT 
        loan_id AS id, user_unique_id, loan_location, ippis, tel_no,
        organisation, branch, loan_agree, loan_type, appr, sold, 
        loan_status, top_up, current_loan, date_created, date_updated 
    FROM loan_applications_tbl_dump
) t3
LEFT JOIN topup_or_reapplication_tbl t
ON t3.ippis = t.ippis   
WHERE 
(
    -- 条件1:topup表记录符合要求
    t.status IN ('pending', 'corrected', 'Rejected', 'Processing', 'Captured', 'Reviewed', 'top up')
    AND (
        (t.date_created >= @current_month_start AND t.date_created < @next_month_start)
        OR (t.date_updated >= @current_month_start AND t.date_updated < @next_month_start)
    )
    AND t.current_loan='1'
)
OR 
(
    -- 条件2:t3侧记录符合要求
    t3.loan_status IN ('pending', 'corrected', 'Rejected', 'Processing', 'Captured', 'Reviewed', 'top up')
    AND (
        (t3.date_created >= @current_month_start AND t3.date_created < @next_month_start)
        OR (t3.date_updated >= @current_month_start AND t3.date_updated < @next_month_start)
    )
    AND (t3.current_loan='1' OR (t3.current_loan='0' AND t3.loan_status IN('Approved','Closed')))
)

-- 按需开启分组排序
-- GROUP BY t3.ippis ORDER BY t3.date_updated DESC

如果业务允许拆分查询,也可以把OR的两个条件拆成两个查询用UNION ALL合并,性能会更好。

内容的提问来源于stack exchange,提问作者Peter

相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级,最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分), 限时 9.9 元起

最近更新时间:2026.10.20 09:36:05