本文目录导读：
我来通过几个典型案例,演示如何优化Java中的子查询写法。
案例1：使用子查询查找最大值的记录
原始写法（低效）
// 查询每个部门工资最高的员工
@Repository
public class EmployeeRepository {
@Autowired
private JdbcTemplate jdbcTemplate;
public List<Employee> getTopPaidEmployees() {
String sql = """
SELECT e.* FROM employees e
WHERE e.salary = (
SELECT MAX(salary) FROM employees
WHERE department_id = e.department_id
)
""";
return jdbcTemplate.query(sql, employeeRowMapper);
}
}
优化方案1：使用JOIN代替子查询
public List<Employee> getTopPaidEmployeesOptimized() {
String sql = """
SELECT e.* FROM employees e
INNER JOIN (
SELECT department_id, MAX(salary) as max_salary
FROM employees
GROUP BY department_id
) dept_max ON e.department_id = dept_max.department_id
AND e.salary = dept_max.max_salary
""";
return jdbcTemplate.query(sql, employeeRowMapper);
}
优化方案2：使用窗口函数
public List<Employee> getTopPaidEmployeesWindow() {
String sql = """
SELECT department_id, employee_id, name, salary
FROM (
SELECT *,
RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) as rnk
FROM employees
) ranked
WHERE rnk = 1
""";
return jdbcTemplate.query(sql, employeeRowMapper);
}
案例2：使用IN子查询
原始写法
// 查询有订单的用户
public List<User> getUsersWithOrders() {
String sql = """
SELECT * FROM users
WHERE user_id IN (
SELECT DISTINCT user_id FROM orders WHERE amount > 100
)
""";
return jdbcTemplate.query(sql, userRowMapper);
}
优化方案：使用EXISTS
public List<User> getUsersWithOrdersOptimized() {
String sql = """
SELECT u.* FROM users u
WHERE EXISTS (
SELECT 1 FROM orders o
WHERE o.user_id = u.user_id AND o.amount > 100
)
""";
return jdbcTemplate.query(sql, userRowMapper);
}
案例3：复杂的嵌套子查询
原始写法
// 查询每个分类中销售额最高的商品
public List<Product> getTopSellingProducts() {
String sql = """
SELECT p.* FROM products p
WHERE p.product_id IN (
SELECT product_id FROM (
SELECT product_id,
ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY sales DESC) as rn
FROM sales
) tmp WHERE rn = 1
)
""";
return jdbcTemplate.query(sql, productRowMapper);
}
优化方案：使用JOIN + 派生表
public List<Product> getTopSellingProductsOptimized() {
String sql = """
SELECT p.*, s.sales
FROM products p
INNER JOIN (
SELECT product_id, category_id, sales,
ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY sales DESC) as rn
FROM sales
) s ON p.product_id = s.product_id AND s.rn = 1
""";
return jdbcTemplate.query(sql, productRowMapper);
}
案例4：使用Java代码处理
原始SQL子查询方式
// 查询某个时间段内活跃用户的信息
public List<User> getActiveUsers(Date startDate, Date endDate) {
String sql = """
SELECT * FROM users
WHERE user_id IN (
SELECT user_id FROM user_actions
WHERE action_date BETWEEN ? AND ?
)
""";
return jdbcTemplate.query(sql, userRowMapper, startDate, endDate);
}
优化方案：分步查询 + Java处理
public List<User> getActiveUsersOptimized(Date startDate, Date endDate) {
// 第一步：获取活跃用户ID
String actionSql = """
SELECT DISTINCT user_id
FROM user_actions
WHERE action_date BETWEEN ? AND ?
""";
List<Long> activeUserIds = jdbcTemplate.queryForList(actionSql,
Long.class, startDate, endDate);
if (activeUserIds.isEmpty()) {
return Collections.emptyList();
}
// 第二步：批量查询用户信息
String userSql = buildBatchQuery(activeUserIds.size());
return jdbcTemplate.query(userSql, userRowMapper, activeUserIds.toArray());
}
private String buildBatchQuery(int size) {
// 使用IN查询，但限制数量避免SQL过长
StringBuilder sql = new StringBuilder("SELECT * FROM users WHERE user_id IN (");
for (int i = 0; i < size; i++) {
sql.append("?");
if (i < size - 1) sql.append(",");
}
sql.append(")");
return sql.toString();
}
案例5：使用Stream API优化
原始多查询方式
public List<Order> getLargeOrdersForUser(Long userId) {
// 先查用户信息
User user = userRepository.findById(userId);
// 再查订单
String sql = """
SELECT * FROM orders
WHERE user_id = ? AND amount > (
SELECT AVG(amount) FROM orders WHERE user_id = ?
)
""";
return jdbcTemplate.query(sql, orderRowMapper, userId, userId);
}
优化方案：单次查询
public List<Order> getLargeOrdersForUserOptimized(Long userId) {
String sql = """
SELECT o.*
FROM orders o
CROSS JOIN (
SELECT AVG(amount) as avg_amount
FROM orders
WHERE user_id = ?
) avg_table
WHERE o.user_id = ? AND o.amount > avg_table.avg_amount
""";
return jdbcTemplate.query(sql, orderRowMapper, userId, userId);
}
性能优化建议
添加适当的索引
// 示例：为子查询相关的字段创建索引
@Component
public class DatabaseOptimizer {
public void createOptimizedIndexes() {
// 为关联字段创建索引
String createIndex1 = """
CREATE INDEX idx_orders_user_id ON orders(user_id)
""";
// 为排序和聚合字段创建复合索引
String createIndex2 = """
CREATE INDEX idx_sales_category_amount ON sales(category_id, amount DESC)
""";
// 执行创建索引
jdbcTemplate.execute(createIndex1);
jdbcTemplate.execute(createIndex2);
}
}
使用缓存
@Service
public class CachedSubQueryService {
@Autowired
private RedisTemplate<String, List<Long>> redisTemplate;
public List<Employee> getTopPaidEmployeesWithCache() {
String cacheKey = "top_paid_employees";
// 尝试从缓存获取
List<Employee> cached = (List<Employee>) redisTemplate.opsForValue().get(cacheKey);
if (cached != null) {
return cached;
}
// 缓存未命中，执行查询
List<Employee> result = executeActualQuery();
// 存入缓存，设置过期时间
redisTemplate.opsForValue().set(cacheKey, result, 1, TimeUnit.HOURS);
return result;
}
}
优化子查询的核心策略：
- 使用JOIN代替子查询（特别是关联子查询）
- 使用EXISTS代替IN（当检查存在性时）
- 窗口函数替代（处理排名、分组取Top N等场景）
- 分步查询 + Java处理（复杂逻辑）
- 添加适当索引（关联字段、排序字段）
- 使用缓存（不常变化的数据）
选择哪种优化方式取决于具体场景：
- 数据量大时,优先考虑JOIN+索引
- 存在性检查用EXISTS
- 复杂统计用窗口函数
- 业务逻辑复杂用Java处理
