MySQL分页与全量查询耗时一致，求性能优化方案

我执行的MySQL查询中，使用LIMIT 0,5000和无LIMIT查询全量287,795条记录均耗时9.5秒。因业务需求需按(0,5000)、(5000,10000)等范围循环执行该查询57次，耗时问题十分关键。测试时移除与classification_configuration表的INNER JOIN后，查询耗时降至1秒以内，但该关联必须保留，用于判断APDMD.DAYS_TO_DEMAND_ARREARS是否处于CC.FROM_DPD与CC.TO_DPD的区间内。现寻求SQL性能优化或策略调整方案。

原查询语句 #

SELECT   COALESCE ( APDMD.DMS_SOL_ID, '--' ) AS SOL_ID,  
COALESCE ( APDMD.ACID, '--' ) AS ACCOUNT_NO,  
COALESCE ( APDMD.CUST_ID, '--' ) AS CUSTOMER_ID,  
COALESCE ( APDMD.ACCT_NAME, '--' ) AS CUSTOMER_NAME,  
COALESCE ( APDMD.SCHM_CODE, '--' ) AS SCHEME_CODE,  
COALESCE ( APDMD.SANCT_LIM, 0 ) AS SANCTION_LIMIT,  
COALESCE ( APDMD.CLR_BAL_AMT, 0 ) AS OS_BALANCE,  
COALESCE ( APDMD.CAP_OVER_DUE, 0 ) AS CAPITAL_ARREARS,  
COALESCE ( APDMD.INT_OVER_DUE, 0 ) AS INTEREST_ARREARS,  
COALESCE ( APDMD.ACCT_CRNCY_CODE, '--' ) AS CURRANCY_CODE,  
COALESCE ( APDMD.ACCT_MGR_USER_ID, '--' ) AS ACCOUNT_MANAGER,  
COALESCE ( CC.STAGE, '--' ) AS STAGECLASSIFICATION,  
COALESCE ( CC.SUB_CLASSIFICATION, '--' ) AS classification,  
COALESCE ( APDMD.ARRMONTHS, 0 ) AS MONTH_ARREARS,  
COALESCE ( APDMD.ARRDAYS, 0) AS DAYS_ARREARS,  APDMD.NPA_DATE AS NPADATE,  
COALESCE ( APDMD.LOCATION_CODE, '--' ) AS LOCATION_CODE,  
COALESCE ( APDMD.GL_SUB_HEAD_CODE, '--' ) AS GL_SUB_HEAD_CODE,  
COALESCE ( APDMD.IIS_LKR, 0 ) AS IIS_LKR,  
COALESCE ( APDMD.SP_PROVISION, 0 ) AS SP_PROVISION,  
COALESCE ( APDMD.SP_PROVISION_LKR, 0 ) AS SP_PROVISION_LKR,  
COALESCE ( APDMD.BSC_TEAM_LEADER, 0 ) AS BSC_TEAM_LEADER,  
APDMD.ACCT_OPN_DATE AS ACCT_OPN_DATE,  

( SELECT SUM( AA.CLR_BAL_AMT )    FROM app_dms_daily AA WHERE AA.CUST_ID = CUSTOMER_ID )  AS PORTPOLIO,
  
COALESCE (  ( SELECT  TKTH.resolutiondescription  FROM  tickethistory TKTH  WHERE  TKTH.tickethistoryid = (SELECT MAX( TH.TICKETHISTORYID ) FROM tickethistory TH WHERE TH.TICKETID = T.TICKETID )
   ),  '--'   ) AS REMARKS,
  
COALESCE ( DWH_MOBILE_NO, '--' ) AS MOBILENO,   
COALESCE(APDMD.MORATORIUM_GIVEN, '--')      AS MORATORIUM_GRANTED,  
COALESCE(APDMD.MORATORIUM_PERIOD, '--')      AS MORATORIUM_PERIOD 

FROM  app_dms_daily APDMD
 
#添加下面的关联后耗时从1秒内升到9秒，循环57次总耗时约7.5分钟
INNER JOIN classification_configuration CC ON  CASE     
WHEN CC.FROM_DPD IS NULL         
THEN APDMD.DAYS_TO_DEMAND_ARREARS <= CC.TO_DPD     
WHEN CC.FROM_DPD AND CC.TO_DPD IS NOT NULL         
THEN APDMD.DAYS_TO_DEMAND_ARREARS >= CC.FROM_DPD AND APDMD.DAYS_TO_DEMAND_ARREARS <= CC.TO_DPD     
WHEN CC.TO_DPD IS NULL         
THEN APDMD.DAYS_TO_DEMAND_ARREARS >= CC.FROM_DPD     ELSE '' END
  
LEFT OUTER JOIN ticket T ON T.ACCID = APDMD.ACID  
ORDER BY 
DMS_SOL_ID, 
OS_BALANCE ASC  
LIMIT 0,5000;

优化方案 #

1. 重构JOIN条件，避免CASE语句 #

原JOIN中的CASE会导致MySQL无法有效利用索引，将条件拆分为逻辑等价的AND/OR组合：

INNER JOIN classification_configuration CC 
ON (CC.FROM_DPD IS NULL AND APDMD.DAYS_TO_DEMAND_ARREARS <= CC.TO_DPD)
OR (CC.FROM_DPD IS NOT NULL AND CC.TO_DPD IS NOT NULL AND APDMD.DAYS_TO_DEMAND_ARREARS BETWEEN CC.FROM_DPD AND CC.TO_DPD)
OR (CC.TO_DPD IS NULL AND APDMD.DAYS_TO_DEMAND_ARREARS >= CC.FROM_DPD)

这种写法更利于优化器识别并选择合适的索引。

2. 添加针对性索引 #

• 给classification_configuration表的FROM_DPD、TO_DPD字段创建联合索引：

  CREATE INDEX idx_cc_dpd ON classification_configuration(FROM_DPD, TO_DPD);
  

• 给app_dms_daily表的DAYS_TO_DEMAND_ARREARS字段创建索引：

  CREATE INDEX idx_apdmd_dpd ON app_dms_daily(DAYS_TO_DEMAND_ARREARS);
  

• 若classification_configuration表数据量小，可创建覆盖索引减少回表：

  CREATE INDEX idx_cc_dpd_stage_sub ON classification_configuration(FROM_DPD, TO_DPD, STAGE, SUB_CLASSIFICATION);
  

3. 替换子查询为JOIN，减少重复计算 #

原查询中PORTPOLIO的子查询会逐行执行聚合，改为预计算的JOIN：

WITH cust_portfolio AS (
    SELECT CUST_ID, SUM(CLR_BAL_AMT) AS PORTPOLIO
    FROM app_dms_daily
    GROUP BY CUST_ID
)
SELECT 
    -- 保留原SELECT的其他字段
    COALESCE(CP.PORTPOLIO, 0) AS PORTPOLIO,
    -- 保留原SELECT的其他字段
FROM app_dms_daily APDMD
JOIN cust_portfolio CP ON APDMD.CUST_ID = CP.CUST_ID
-- 保留原JOIN逻辑

仅需一次聚合计算，避免重复执行子查询。

4. 优化REMARKS子查询的嵌套结构 #

将嵌套子查询改为JOIN获取最新工单历史：

WITH latest_tickethistory AS (
    SELECT TICKETID, MAX(TICKETHISTORYID) AS MAX_TICKETHISTORYID
    FROM tickethistory
    GROUP BY TICKETID
)
SELECT
    -- 保留原SELECT的其他字段
    COALESCE(TKTH.resolutiondescription, '--') AS REMARKS,
    -- 保留原SELECT的其他字段
FROM app_dms_daily APDMD
LEFT JOIN ticket T ON T.ACCID = APDMD.ACID
LEFT JOIN latest_tickethistory LTH ON T.TICKETID = LTH.TICKETID
LEFT JOIN tickethistory TKTH ON LTH.MAX_TICKETHISTORYID = TKTH.tickethistoryid
-- 保留原JOIN逻辑

5. 调整分页策略，避免循环LIMIT #

循环执行LIMIT会重复扫描前置数据，建议：

• 一次性查询全量数据后在应用端分页；
• 若app_dms_daily有自增主键，使用范围分页：

  SELECT ...
  FROM app_dms_daily APDMD
  -- 保留原JOIN逻辑
  WHERE APDMD.ID > 上一页最大ID
  ORDER BY APDMD.ID, DMS_SOL_ID, OS_BALANCE ASC
  LIMIT 5000;
  

6. 更新表统计信息，清理冗余数据 #

• 执行ANALYZE TABLE app_dms_daily, classification_configuration, ticket, tickethistory;更新统计信息，帮助优化器生成更优执行计划；
• 检查classification_configuration表是否存在重复区间配置，清理冗余数据减少JOIN匹配次数。

内容的提问来源于stack exchange，提问作者Tishan Madushanka