攻克Java开发者必会的15道SQL实战难题：从建表到调优全解析

当实习生把分页查询写成性能杀手时

上周三凌晨两点，我被生产环境的报警短信惊醒。打开监控一看，某个用户分页查询接口的响应时间从50ms飙升到12秒。追查日志发现，刚入职的小王在实现订单分页查询时，竟然在Java代码里用LIMIT 100000,20这样的写法。这让我想起初学SQL时踩过的那些坑——今天我们就从基础到进阶，拆解Java开发者必须掌握的SQL编程精髓。

基础篇：这些操作决定你的代码下限

题目1：动态建表与约束设计
需要根据产品需求动态创建用户表，包含唯一约束、非空约束、默认值设置。很多新手会忘记在Java中处理DDL异常：

try (Statement stmt = conn.createStatement()) {
    String sql = "CREATE TABLE users (" +
                 "id INT AUTO_INCREMENT PRIMARY KEY," +
                 "username VARCHAR(50) UNIQUE NOT NULL," +
                 "created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP)";
    stmt.executeUpdate(sql);
} catch (SQLException e) {
    if(!e.getMessage().contains("already exists")) {
        throw new RuntimeException("建表失败", e);
    }
}

题目4：分页查询的陷阱
我刚开始做分页查询时，总喜欢在前端直接传页码。直到遇到百万级数据表，才发现OFFSET带来的性能灾难。正确的做法是用WHERE id > ? LIMIT 20实现游标分页，这里有个对比实验：

• 传统分页：SELECT * FROM orders LIMIT 100000,20 → 执行时间1.2s
• 游标分页：SELECT * FROM orders WHERE id > 100000 ORDER BY id LIMIT 20 → 0.03s

进阶篇：写出让DBA眼前一亮的SQL

题目7：窗口函数实战
去年双十一大促时，我们需要实时计算每个品类的销售排名。使用ROW_NUMBER() OVER(PARTITION BY category_id ORDER BY sales DESC)，比在Java中处理快3倍。注意在JDBC中获取窗口函数结果时，别名要与ResultSet字段名对应。

题目9：索引失效的N种死法
某次代码评审，我发现同事写的WHERE YEAR(create_time) = 2023导致索引失效。正确的写法应该是BETWEEN '2023-01-01' AND '2023-12-31'。常见的索引杀手还有：

• 对字段进行运算：WHERE price+100 > 500
• 模糊查询不当：LIKE '%关键字%'
• 隐式类型转换：WHERE user_id = '123'（user_id是整型）

调优篇：从Explain到连接池的全链路优化

最近优化过一个电商系统的物流查询接口，通过三个步骤将响应时间从800ms降到90ms：

1. 用EXPLAIN发现全表扫描问题
2. 添加组合索引（region_code, status）
3. 调整连接池配置：将maxPoolSize从50降到30避免上下文切换

题目15：事务控制的正确姿势
在转账场景中，见过最危险的操作是：先查询余额再更新。正确的做法应该用UPDATE accounts SET balance = balance - 100 WHERE id=1 AND balance >= 100这种原子操作。在Spring事务中要注意@Transactional的传播机制，曾经有个BUG是因为把非事务方法嵌套调用导致的。

你的SQL能力在第几层？

上周面试了个三年经验的开发者，当问到「如何防止商品超卖」时，他说用Java锁控制。我让他看这个SQL方案：

UPDATE products 
SET stock = stock - 1 
WHERE id = ? AND stock > 0

他恍然大悟的样子让我想起曾经的自己。SQL不是过时的技术，而是区分普通开发者和高手的试金石。当你能够写出既高效又优雅的SQL时，会发现很多业务逻辑其实不需要在Java里绕弯子。

（看完这15个案例，不妨试试这个挑战题：设计一个支持模糊搜索、多条件过滤、自定义排序的分页接口，要求响应时间控制在100ms内，你会如何设计SQL和Java代码？）