如何使用索引优化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
相关产品推荐
相关产品推荐

