如何优化Spring Batch周批场景下单次耗时1小时的慢SQL查询

核心性能瓶颈定位 #

你当前查询跑1小时，最核心的问题不是数据量大，是连接条件里的OR写法：

• 数据库优化器遇到带OR的连接条件时，基本都会放弃走索引匹配，直接对大表做全表扫描关联。哪怕其他条件建了再多索引，只要OR连接逻辑存在，整个查询的执行计划就会退化成大表笛卡尔积，耗时自然下不来
• 剩下的INNER JOIN都是标准的主键/外键等值匹配，只要表本身有主键索引，这部分关联的耗时几乎可以忽略
• 你最终返回的结果只有1k-7k行，正常执行计划下，哪怕是千万级的大表，查询耗时也不会超过10秒

SQL逻辑等价改写（消除OR扫描） #

把原来带OR的CITY_DEBT关联拆成两个独立的等值连接，用UNION ALL合并，逻辑和原查询完全一致，但能让优化器正常走索引：

WITH base_filtered_city AS (
    -- 先按WHERE条件过滤主表，关联所有不带OR逻辑的表，拿到基础数据
    SELECT 
        PRIME_MINISTER.PRIME_MINISTER_ID,
        PRIME_MINISTER.PRIME_MINISTER_NAME,
        CITY.CITY_ID,
        CITY.CITY_POPULATION,
        CITY.CITY_FOUNDATION_DATE,
        STATE.STATE_ID,
        STATE.STATE_NAME,
        CITY_ACCOUNTANT.CITY_ACCOUNTANT_ID,
        CITY_ACCOUNTANT.CITY_ACCOUNTANT_SOCIAL,
        CITY_ACCOUNTANT.CITY_ACCOUNTANT_NAME,
        CITY_COUNCIL.CITY_COUNCIL_ID,
        CITY_COUNCIL.CITY_COUNCIL_FREQUENCY,
        HEAD_OF_STATE.HEAD_OF_STATE_ID,
        HEAD_OF_STATE.HEAD_OF_STATE_SOCIAL,
        DEPUTY_HEAD_OF_STATE.DEPUTY_HEAD_OF_STATE_ID,
        DEPUTY_HEAD_OF_STATE.DEPUTY_HEAD_OF_STATE_SOCIAL,
        DEPUTY_HEAD_OF_STATE.DEPUTY_HEAD_OF_STATE_NAME
    FROM CITY
    LEFT JOIN CITY_COUNCIL ON CITY.CITY_ID = CITY_COUNCIL.CITY_ID
    INNER JOIN CITY_ACCOUNTANT ON CITY_ACCOUNTANT.CITY_ACCOUNTANT_ID = CITY.CITY_ACCOUNTANT_ID
    INNER JOIN STATE ON STATE.STATE_ID = CITY.STATE_ID
    INNER JOIN HEAD_OF_STATE ON HEAD_OF_STATE.HEAD_OF_STATE_ID = STATE.HEAD_OF_STATE_ID
    INNER JOIN DEPUTY_HEAD_OF_STATE ON DEPUTY_HEAD_OF_STATE.DEPUTY_HEAD_OF_STATE_ID = HEAD_OF_STATE.DEPUTY_HEAD_OF_STATE_ID
    INNER JOIN PRIME_MINISTER ON STATE.STATE_ID = PRIME_MINISTER.STATE_ID
    WHERE CITY.CITY_STATUS = 2
      AND CITY.PRIME_MINISTER_STATUS = 2
      AND CITY.JURISDICTION = '70'
      AND CITY.CITY_ACCOUNTANT_NATURE = 'S'
)
-- 第一部分：匹配符合CITY_COUNCIL_ID关联规则的债务记录
SELECT DISTINCT
    b.PRIME_MINISTER_ID,
    b.PRIME_MINISTER_NAME,
    b.CITY_ID,
    b.CITY_POPULATION,
    b.CITY_FOUNDATION_DATE,
    b.STATE_ID,
    b.STATE_NAME,
    b.CITY_ACCOUNTANT_ID,
    b.CITY_ACCOUNTANT_SOCIAL,
    b.CITY_ACCOUNTANT_NAME,
    b.CITY_COUNCIL_ID,
    b.CITY_COUNCIL_FREQUENCY,
    d.CITY_DEBT_ID,
    d.CITY_DEBT_NATURE,
    d.CITY_DEBT_AMOUNT,
    b.HEAD_OF_STATE_ID,
    b.HEAD_OF_STATE_SOCIAL,
    b.DEPUTY_HEAD_OF_STATE_ID,
    b.DEPUTY_HEAD_OF_STATE_SOCIAL,
    b.DEPUTY_HEAD_OF_STATE_NAME
FROM base_filtered_city b
LEFT JOIN CITY_DEBT d 
    ON b.CITY_COUNCIL_ID = d.CITY_COUNCIL_ID

UNION ALL

-- 第二部分：匹配符合CITY_ID直接关联规则的债务记录，排除已经在第一部分关联过的重复债务
SELECT DISTINCT
    b.PRIME_MINISTER_ID,
    b.PRIME_MINISTER_NAME,
    b.CITY_ID,
    b.CITY_POPULATION,
    b.CITY_FOUNDATION_DATE,
    b.STATE_ID,
    b.STATE_NAME,
    b.CITY_ACCOUNTANT_ID,
    b.CITY_ACCOUNTANT_SOCIAL,
    b.CITY_ACCOUNTANT_NAME,
    b.CITY_COUNCIL_ID,
    b.CITY_COUNCIL_FREQUENCY,
    d.CITY_DEBT_ID,
    d.CITY_DEBT_NATURE,
    d.CITY_DEBT_AMOUNT,
    b.HEAD_OF_STATE_ID,
    b.HEAD_OF_STATE_SOCIAL,
    b.DEPUTY_HEAD_OF_STATE_ID,
    b.DEPUTY_HEAD_OF_STATE_SOCIAL,
    b.DEPUTY_HEAD_OF_STATE_NAME
FROM base_filtered_city b
LEFT JOIN CITY_DEBT d 
    ON b.CITY_ID = d.CITY_ID
    AND NOT EXISTS (
        SELECT 1 
        FROM CITY_DEBT dup_check
        WHERE b.CITY_COUNCIL_ID = dup_check.CITY_COUNCIL_ID 
          AND dup_check.CITY_DEBT_ID = d.CITY_DEBT_ID
    )

-- 按原业务逻辑排序
ORDER BY DEPUTY_HEAD_OF_STATE_ID,
         HEAD_OF_STATE_ID,
         STATE_ID,
         CITY_ID,
         CITY_COUNCIL_ID,
         CITY_DEBT_ID,
         CITY_ACCOUNTANT_ID;

索引创建清单（无表结构修改权限下的最高性价比方案） #

按照查询的执行顺序，只需要创建以下几个复合索引，就能把全表扫描完全消掉，不要建多余的单字段索引：

• 主表CITY的过滤索引（最优先创建）：
  CREATE INDEX idx_city_query_filter ON CITY (CITY_STATUS, PRIME_MINISTER_STATUS, JURISDICTION, CITY_ACCOUNTANT_NATURE, CITY_ID, STATE_ID, CITY_ACCOUNTANT_ID);
  这个索引覆盖了所有WHERE过滤条件、以及后续关联需要的字段，数据库直接在索引上就能定位到符合条件的城市记录，不用回表扫全表
• CITY_COUNCIL的关联索引：
  CREATE INDEX idx_council_city_match ON CITY_COUNCIL (CITY_ID, CITY_COUNCIL_ID, CITY_COUNCIL_FREQUENCY);
• CITY_DEBT的两个关联索引（对应拆分后的两个独立关联条件）：
  CREATE INDEX idx_debt_council_match ON CITY_DEBT (CITY_COUNCIL_ID, CITY_DEBT_ID, CITY_DEBT_NATURE, CITY_DEBT_AMOUNT);
CREATE INDEX idx_debt_city_match ON CITY_DEBT (CITY_ID, CITY_DEBT_ID, CITY_DEBT_NATURE, CITY_DEBT_AMOUNT);
• 剩下的关联表（CITY_ACCOUNTANT、STATE、HEAD_OF_STATE、DEPUTY_HEAD_OF_STATE、PRIME_MINISTER）都是主键等值关联，只要表本身的主键索引存在，这部分关联的耗时可以忽略，不用额外建索引

适配Spring Batch读数据的调整技巧 #

• 你最终是要把查询结果写入文件，总数据量才1k-7k行，完全没必要让数据库做全量结果集排序。可以把SQL里的ORDER BY删掉，等数据读到内存后，用Spring Batch的处理器做排序，能省掉数据库大排序的耗时
• 配置JdbcCursorItemReader的时候，把fetch size设为1000，不要用默认的逐行拉取，减少数据库和应用之间的网络往返
• 不要用带分页offset的读取方式，大表上的分页查询会随着偏移量增大越来越慢，直接用流式游标读全量匹配结果就行

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