百万级大表批量查询处理性能优化方案求助

索引精准优化 #

针对你拆分的两个查询场景，创建专属复合索引，避免优化器选错路径：

1. 针对带status='PROCESSED'的场景：

   CREATE INDEX idx_cust_dept_status_type_row ON customers(department_id, status, type, row_number);
   

   把过滤条件（department_id、status、type）放在索引前缀，排序字段row_number放最后，这样查询可以直接通过索引完成过滤+排序，无需回表后再做排序操作。
2. 针对不带status的场景：

   CREATE INDEX idx_cust_dept_type_row ON customers(department_id, type, row_number);
   

   不要复用上面的索引，因为缺少status过滤时，数据库会跳过索引前缀的status字段，导致索引利用率极低。

同时删除单独的department_id单字段索引，复合索引已覆盖其过滤能力，多余索引会占用存储空间，还可能干扰优化器的索引选择。

查询逻辑与Hibernate优化 #

1. 强制指定索引：如果数据库优化器仍未选择目标复合索引，在SQL中强制指定（以PostgreSQL为例）：

   SELECT * FROM customers 
   WHERE department_id = :id AND status = 'PROCESSED' AND type IN ('EXTERNAL', 'INTERNAL')
   AND row_number > :max_row_number
   ORDER BY row_number LIMIT 5000
   /*+ INDEX(customers idx_cust_dept_status_type_row) */;
   

   不同数据库语法不同，MySQL可以用USE INDEX(idx_name)。
2. **避免SELECT ***：只查询处理所需的字段，不要返回全表列。如果需要映射到实体，创建仅包含必要字段的DTO，减少数据传输和磁盘IO开销。
3. 清理Hibernate缓存：每批次处理完成后，调用entityManager.clear()清空一级缓存，避免缓存堆积导致内存占用过高、查询变慢。
4. 调整批次大小：不要固定5000行，测试2000-10000之间的批次值，找到适配数据库性能的最优大小。

数据库层面优化 #

1. 更新统计信息：重新执行ANALYZE customers;（PostgreSQL）或ANALYZE TABLE customers;（MySQL），确保优化器拥有最新的表数据分布统计，生成更优的执行计划。
2. 考虑分区表：如果department_id的取值范围固定且分组清晰，按department_id对表做分区处理，查询时仅扫描目标分区，大幅减少扫描行数。
3. 调整内存参数：增大排序相关的内存参数，比如PostgreSQL的work_mem，MySQL的sort_buffer_size，避免排序时使用磁盘临时表，提升排序效率。

Hibernate配置优化 #

1. 使用原生SQL：放弃HQL，直接用原生SQL配合NativeQuery或JdbcTemplate执行，减少HQL解析带来的开销，更精准控制索引使用。
2. 开启批量更新：如果处理后需要更新数据，在Hibernate配置中开启批量模式：

   hibernate.jdbc.batch_size=5000
   hibernate.order_inserts=true
   hibernate.order_updates=true
   

   减少数据库交互次数，提升批量操作效率。

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